GridView with CRUD

ASP.NET page with given facility :

1. Searching of a substring from table1
2. Adding of data in the table1 .
3. Editing some data after searching .
4. Deleting some data after searching

1. Make a Gridview.aspx page and copy the gridview code into that as given in the beginning.
2. Make GridView.aspx.cs page and copy all the functions required to get the desired result.
3. Table Name = table1

Code of GridView1 : {gridview.aspx}

<asp:GridView ID="GridView1" runat="server"  BorderColor="#CCCCCC" BorderWidth="1px" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating"
OnRowCancelingEdit="GridView1_RowCancelingEdit" AllowPaging="True" AutoGenerateColumns="False"  Width ="90%" PageSize="500"  CssClass="styled-grid"  OnPageIndexChanging="GridView1_PageIndexChanging" OnRowDeleting="GridView1_RowDeleting"> 
 <Columns> 
 <asp:TemplateField HeaderText="Sl" ItemStyle-Width="50"> 
<ItemTemplate><asp:Label ID="sl" Text='<%# Container.DataItemIndex + 1 %>' runat="server" /></ItemTemplate> </asp:TemplateField >

<asp:TemplateField HeaderText="lab" ItemStyle-Width="80"> 
<ItemTemplate> <asp:Label ID="lab" runat="server" Text='<%# Eval("lab") %>'></asp:Label> </ItemTemplate> </asp:TemplateField>

<asp:TemplateField HeaderText="code" ItemStyle-Width="80"> 
<ItemTemplate> <asp:Label ID="code" runat="server" Text='<%# Eval("code") %>'></asp:Label> </ItemTemplate>    </asp:TemplateField>

<asp:TemplateField HeaderText="property" ItemStyle-Width="500"> 
<ItemTemplate> <asp:Label ID="property" runat="server" Text='<%# Eval("property") %>'></asp:Label> </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="txt_property" runat="server" Width="400px" Text='<%# Eval("property") %>'></asp:TextBox>
        </EditItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="method" ItemStyle-Width="500" >
  <ItemTemplate> <asp:Label ID="method" runat="server" Text='<%# Eval("method") %>'></asp:Label> </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="txt_method" runat="server" Width="400px" Text='<%# Eval("method") %>'></asp:TextBox>
        </EditItemTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="unit" ItemStyle-Width="80">
        <ItemTemplate> <asp:Label ID="unit" runat="server" Text='<%# Eval("unit") %>'></asp:Label> </ItemTemplate>
    </asp:TemplateField>

    <asp:CommandField ShowEditButton="True" ButtonType="Button" ItemStyle-Width="200"  />
     <asp:TemplateField HeaderText="Delete" ItemStyle-Width="100"> <ItemTemplate>
        <asp:LinkButton ID="lnkDelete" runat="server" 
            CommandName="Delete" Text="Delete" 
            OnClientClick="return confirm('Press YES for Deletion.');"/>
     </ItemTemplate> </asp:TemplateField>
            </Columns> 
             <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" Height="25px" width="50"/>
         </asp:GridView>

Code Behind : {GridView.aspx.cs}

1. Search System

protected void Button3_Click(object sender, EventArgs e)
{ 
    fetch_old_data();
}


public void fetch_old_data(){
if (!string.IsNullOrEmpty(TextBox2.Text))
{
    string searchText = TextBox2.Text.Trim();
    string query = "SELECT code, lab, property, method, unit FROM table1 WHERE UPPER(property) LIKE ? ORDER BY LAB ASC";

    string mainconn = ConfigurationManager.ConnectionStrings["userconn"].ConnectionString;
    using (OdbcConnection con = new OdbcConnection(mainconn))
    {
        using (OdbcCommand command = new OdbcCommand(query, con))
        {
            command.Parameters.AddWithValue("?", "%" + searchText.ToUpper() + "%");
            OdbcDataAdapter adapter = new OdbcDataAdapter(command);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            //  Store in ViewState
            ViewState["FilteredData"] = dataTable;
            GridView1.DataSource = dataTable;
            GridView1.DataBind();
        }
    }
}

2. Adding function {Codebehind}

protected void Add_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(TextBox3.Text.Trim()))
{
using (OdbcConnection con = new OdbcConnection(mainconn))
{
string ipAddress = Request.UserHostAddress;
var hostEntry = Dns.GetHostEntry(ipAddress);
con.Open();
OdbcCommand add_cmd = new OdbcCommand("insert into table1 (CODE,LAB,PROPERTY,METHOD,UNIT,UPDATED_DT,UPDATED_BY)VALUES((SELECT MAX(code) + 1 FROM tbl_tsd_common_code),?,?,?,?,sysdate,?)", con);
add_cmd.Parameters.AddWithValue("@lab", DropDownList1.SelectedValue);
add_cmd.Parameters.AddWithValue("@property", TextBox3.Text.Trim());
add_cmd.Parameters.AddWithValue("@method", TextBox4.Text.Trim());
add_cmd.Parameters.AddWithValue("@host", TextBox5.Text.Trim());
add_cmd.Parameters.AddWithValue("@code", hostEntry.HostName);
add_cmd.ExecuteNonQuery();
con.Close();
Label6.Text = "Value Inserted "~ LAB : " + DropDownList1.SelectedValue ;
Label6.ForeColor = Color.DarkGreen;
} }else Label6.Text = "Please Enter Property.";}

3. Updating Function {Codebehind}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
// Fetch row
GridViewRow row = GridView1.Rows[e.RowIndex];
string v_lab = ((System.Web.UI.WebControls.Label)row.FindControl("lab")).Text;
string v_code = ((System.Web.UI.WebControls.Label)row.FindControl("code")).Text;
string v_property = ((TextBox)row.FindControl("txt_property")).Text;
string v_method = ((TextBox)row.FindControl("txt_method")).Text;
string ipAddress = Request.UserHostAddress;
var hostEntry = Dns.GetHostEntry(ipAddress);

using (OdbcConnection con = new OdbcConnection(mainconn))
{
    con.Open();
    using (OdbcCommand updateCmd = new OdbcCommand("UPDATE table1 SET property= ? ,method = ? , updated_by = ? , updated_dt = sysdate where lab = ? and code =?", con))
    {
        updateCmd.Parameters.AddWithValue("@property", v_property.Trim());
        updateCmd.Parameters.AddWithValue("@method", v_method.Trim());
        updateCmd.Parameters.AddWithValue("@host", hostEntry.HostName);
        updateCmd.Parameters.AddWithValue("@lab", v_lab.Trim());
        updateCmd.Parameters.AddWithValue("@code", v_code.Trim());
        updateCmd.ExecuteNonQuery();
        con.Close();
        Label6.Text = "Last Updated ~ LAB : " + v_lab + " / Code : " + v_code;
        Label6.ForeColor = Color.OrangeRed;

    }
}
GridView1.EditIndex = -1;
fetch_old_data(); }

4. Deleting Function {Codebehind}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
GridViewRow row = GridView1.Rows[e.RowIndex];
string v_lab = ((System.Web.UI.WebControls.Label)row.FindControl("lab")).Text;
string v_code = ((System.Web.UI.WebControls.Label)row.FindControl("code")).Text;
using (OdbcConnection con = new OdbcConnection(mainconn))
{
    con.Open();
    OdbcCommand delete_cmd = new OdbcCommand("DELETE FROM table1 WHERE  lab = ? and code =?", con);
    delete_cmd.Parameters.AddWithValue("@lab", v_lab.Trim());
    delete_cmd.Parameters.AddWithValue("@code", v_code.Trim());
    delete_cmd.ExecuteNonQuery();
    con.Close();
}

// Rebind GridView
fetch_old_data(); // Make sure this method reloads data into GridView
}

5. Other Important functions for Page Indexing , row_updating and row_deleting .

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            fetch_old_data();
        }

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        fetch_old_data();

    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        fetch_old_data();
    }

For better learning visit official page .