Overview of CRUD operations using ASP.NET GridView

Crud operation ( create, read, update, and delete) is another term of the Select, Insert, Update, and Delete actions. In this example, we will use a simple database table to perform these operations from an ASP.NET GridView.

Let’s get started by creating the database and the table.

The Database

Now create a simple table called customers with the following structure.

CustomerId (int, not null)
CustomerName (nvarchar(50), null)
CustomerEmail (nvarchar(50), null)

Or simply run the query below:

SET ansi_nulls ON 

go 

SET quoted_identifier ON 

go 

CREATE TABLE [dbo].[customers] 
  ( 
     [customerid]    [INT] IDENTITY(1, 1) NOT NULL, 
     [customername]  [NVARCHAR](50) NULL, 
     [customeremail] [NVARCHAR](50) NULL 
  ) 
ON [PRIMARY] 

go

HTML

For the view part, create an Asp.Net GridView with necessary buttons for insert, update, cancel, and delete operations.

    <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" CssClass="gv" HeaderStyle-CssClass="gvHeader" FooterStyle-CssClass="gvHeader">

        <Columns>

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

                </ItemTemplate>
            </asp:TemplateField>

            <asp:TemplateField HeaderText="Name" ItemStyle-Width="150px" ItemStyle-CssClass="Name">
                <ItemTemplate>
                    <asp:Label Text='<%# Eval("CustomerName") %>' runat="server" />
                    <asp:TextBox Text='<%# Eval("CustomerName") %>' runat="server" Style="display: none" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Email" ItemStyle-Width="150px" ItemStyle-CssClass="Email">
                <ItemTemplate>
                    <asp:Label Text='<%# Eval("CustomerEmail") %>' runat="server" />
                    <asp:TextBox Text='<%# Eval("CustomerEmail") %>' runat="server" Style="display: none" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:LinkButton Text="Edit" ID="Edit" runat="server" CssClass="Edit" />
                    <asp:LinkButton Text="Update" ID="Update" runat="server" CssClass="Update" Style="display: none" />
                    <asp:LinkButton Text="Cancel" ID="Cancel" runat="server" CssClass="Cancel" Style="display: none" />
                    <asp:LinkButton Text="Delete" ID="Delete" runat="server" CssClass="Delete" />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <RowStyle CssClass="gvItem" />
    </asp:GridView>
    <br />
    <label>Add new customer</label>
    <table border="1" style="margin: 2px">
        <tr>
            <td style="width: 150px;"><b>Name</b><br />
                <asp:TextBox ID="txtName" runat="server" Width="140" />
            </td>
            <td style="width: 150px;"><b>Email</b><br />
                <asp:TextBox ID="txtEmail" runat="server" Width="140" />
            </td>
            <td style="width: 100px">
                <br />
                <asp:Button ID="btnAdd" runat="server" Text="Add" />
            </td>
        </tr>
    </table>

    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

The CSS

    <style>
        .gv {
            border: solid 2px black;
        }

        .header {
            background-color: #646464;
            font-family: Arial;
            color: White;
            border: none 0px transparent;
            height: 25px;
            text-align: center;
            font-size: 16px;
        }

        .rows {
            background-color: #fff;
            font-family: Arial;
            font-size: 14px;
            color: #000;
            min-height: 25px;
            text-align: left;
            border: none 0px transparent;
        }

        .selectedrow {
            background-color: #ff8000;
            font-family: Arial;
            color: #fff;
            font-weight: bold;
            text-align: left;
        }

        .pager {
            background-color: #646464;
            font-family: Arial;
            color: White;
            height: 30px;
            text-align: left;
        }

        .gv td {
            padding: 5px;
        }

        .gv th {
            padding: 5px;
        }
    </style>

Methods to handle Data

Load the Data

Below is the method we are going to use to load the data into the GridView.

    private void BindCustomers()
    {
        string conn_str = "Data Source=ServerName;Initial Catalog=customersdb;Integrated Security=True";
        using (SqlConnection con = new SqlConnection(conn_str))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "SELECT * FROM Customers";
                cmd.Connection = con;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                }
            }
        }
    }

Now call this method from the page_load method

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindCustomers();
        }
    }

And add a method to handle the Paging

    protected void OnPaging(object sender, GridViewPageEventArgs e)
    {
        gvCustomers.PageIndex = e.NewPageIndex;
        BindCustomers();
    }

The page should look like this after you run the application:

InsertCustomer

Add the function below to the code behind to handle the inserting of a new customer to the Database.

    [WebMethod]
    public static int InsertCustomer(string customerName, string customerEmail)
    {
        string conn_str = "Data Source=ServerName;Initial Catalog=customersdb;Integrated Security=True";
        using (SqlConnection con = new SqlConnection(conn_str))
        {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers VALUES(@customerName, @customerEmail)"))
            {
                cmd.Parameters.AddWithValue("@customerName", customerName);
                cmd.Parameters.AddWithValue("@customerEmail", customerEmail);
                cmd.Connection = con;
                con.Open();
                int customerId = Convert.ToInt32(cmd.ExecuteScalar());
                con.Close();
                return customerId;
            }
        }
    }

UpdateCustomer

Add the function below to the code behind to handle updating an existing customer.

    [WebMethod]
    public static void UpdateCustomer(int customerId, string customerName, string customerEmail)
    {
        string connStr = "Data Source=ServerName;Initial Catalog=customersdb;Integrated Security=True";
        using (SqlConnection con = new SqlConnection(connStr))
        {
            using (SqlCommand cmd = new SqlCommand("UPDATE Customers SET CustomerName = @customerName, CustomerEmail = @customerEmail WHERE CustomerId = @CustomerId"))
            {
                cmd.Parameters.AddWithValue("@CustomerId", customerId);
                cmd.Parameters.AddWithValue("@customerName", customerName);
                cmd.Parameters.AddWithValue("@customerEmail", customerEmail);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

DeleteCustomer

Now add one more function to the code behind to handle deleting a customer.

        [WebMethod]
        public static void DeleteCustomer(int customerId)
        {
            string connStr = "Data Source=ServerName;Initial Catalog=customersdb;Integrated Security=True";
            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerId = @CustomerId"))
                {
                    cmd.Parameters.AddWithValue("@CustomerId", customerId);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();

                }
            }
        }

Add the Ajax Calls

Now add the Ajax Calls from the ASPX file to handle the client-side events:

    <script>
 

    $("[id*=gvCustomers]").find("[id*=Cancel]").click(function () {

        var row = $(this).closest("tr");
        $("td", row).each(function () {
            if ($(this).find("input").length > 0) {
                var span = $(this).find("span");
                var input = $(this).find("input");
                input.val(span.html());
                span.show();
                input.hide();
            }
        });
        row.find(".Edit").show();
        row.find(".Delete").show();
        row.find(".Update").hide();
        $(this).hide();
        return false;
    });


    $("[id*=gvCustomers]").find("[id*=Delete]").click(function () {

        if (confirm("Do you want to delete this customer?")) {
            var row = $(this).closest("tr");
            var customerId = row.find("span").html();
            $.ajax({
                type: "POST",
                url: "Default.aspx/DeleteCustomer",
                data: '{customerId: ' + customerId + '}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    alert('deleted')
                    row.remove();
                }
            });
        }
    });

    $("[id*=gvCustomers]").find("[id*=Edit]").click(function () {

        var row = $(this).closest("tr");

        $("td", row).each(function () {
            if ($(this).find("input").length > 0) {
                $(this).find("input").show();
                $(this).find("span").hide();
            }
        });
        row.find(".Update").show();
        row.find(".Cancel").show();
        row.find(".Delete").hide();
        $(this).hide();
        return false;
    });


    $("[id*=gvCustomers]").find("[id*=Update]").click(function () {
        var row = $(this).closest("tr");

        $("td", row).each(function () {
            if ($(this).find("input").length > 0) {
                var span = $(this).find("span");
                var input = $(this).find("input");
                span.html(input.val());
                span.show();
                input.hide();
            }
        });
        row.find(".Edit").show();
        row.find(".Delete").show();
        row.find(".Cancel").hide();
        $(this).hide();

        var customerId = row.find("td").eq(0).find("span").html();
        var customerName = row.find("td").eq(1).find("span").html();
        var customerEmail = row.find("td").eq(2).find("span").html();
 
        $.ajax({
            type: "POST",
            url: "Default.aspx/UpdateCustomer",
            data: '{customerId: ' + customerId + ', customerName: "' + customerName + '", customerEmail: "' + customerEmail + '" }',
            contentType: "application/json; charset=utf-8",
            dataType: "json"
        });

        return false;
    });

    $("[id*=btnAdd]").click(function () {

        var txtName = $("[id*=txtName]");
        var txtEmail = $("[id*=txtEmail]");
        if (txtName.val() == '' || txtEmail.val() == '') {
            alert('Name and Email are required')
            return;
        }
        $.ajax({
            type: "POST",
            url: "Default.aspx/InsertCustomer",
            data: '{customerName: "' + txtName.val() + '", customerEmail: "' + txtEmail.val() + '" }',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {

                txtName.val("");
                txtEmail.val("");
            }
        });
        return false;
    });
    </script>

Run the application.

Happy coding!!!

Related Articles