GridView CRUD operation using Bootstrap DataTable plugin in ASP.Net

Hi Amol111,

Add another TemplateField in the GridView to display Edit and Delete Button.

Check this example. Now please take its reference and correct your code.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<asp:ScriptManager runat="server" />
<asp:UpdatePanel runat="server">
    <ContentTemplate>
        <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" class="table table-striped"
            ClientIDMode="Static">
            <Columns>
                <asp:BoundField DataField="CustomerId" HeaderText="ID" />
                <asp:BoundField DataField="Name" HeaderText="Name" />
                <asp:BoundField DataField="Country" HeaderText="Country" />
                <asp:TemplateField HeaderText="Action">
                    <ItemTemplate>
                        <asp:Button ID="btnEdit" Text="Edit" runat="server" OnClick="Edit" CssClass="btn btn-primary" />
                        <asp:Button ID="btnDelete" Text="Delete" runat="server" CssClass="btn btn-danger"
                            OnClick="Delete" OnClientClick="return confirm('Are you sure you want to delete?')" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <hr />
        <table align="center">
            <tr>
                <td>Id</td>
                <td><asp:Label ID="lblId" runat="server" CssClass="form-control" /></td>
            </tr>
            <tr>
                <td>Name</td>
                <td><asp:TextBox ID="txtName" runat="server" CssClass="form-control" /></td>
            </tr>
            <tr>
                <td>Country</td>
                <td><asp:TextBox ID="txtCountry" runat="server" CssClass="form-control" /></td>
            </tr>
            <tr>
                <td colspan="2" align="center">
                    <asp:Button ID="btnSave" Text="Save" runat="server" OnClick="Save" CssClass="btn btn-success" />
                </td>
            </tr>
        </table>
    </ContentTemplate>
</asp:UpdatePanel>
<link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/1.10.9/css/dataTables.bootstrap.min.css" />
<link type="text/css" rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" />
<link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/responsive/1.0.7/css/responsive.bootstrap.min.css" />
<script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/responsive/1.0.7/js/dataTables.responsive.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/dataTables.bootstrap.min.js"></script>
<script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script type="text/javascript">
    $(function () {
        $('[id*=gvCustomers]').prepend($("<thead></thead>").append($(this).find("tr:first"))).DataTable({
            "responsive": true,
            "sPaginationType": "full_numbers",
            "bPaginate": true,
            "bSort": true,
            "iDisplayLength": 4,
            "stateSave": true,
            "stateDuration": 60 * 1
        });
    });
    //On UpdatePanel Refresh.
    var prm = Sys.WebForms.PageRequestManager.getInstance();
    if (prm != null) {
        prm.add_endRequest(function (sender, e) {
            if (sender._postBackSettings.panelsToUpdate != null) {
                $('[id*=gvCustomers]').prepend($("<thead></thead>").append($('[id*=gvCustomers]').find("tr:first"))).DataTable({
                    "responsive": true,
                    "sPaginationType": "full_numbers",
                    "bPaginate": true,
                    "bSort": true,
                    "iDisplayLength": 4,
                    "stateSave": true,
                    "stateDuration": 60 * 1
                });
            }
        });
    };
</script>

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;
using System.Data;

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

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

private void BindGridView()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerId,Name,Country FROM Customers", con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                cmd.CommandType = CommandType.Text;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                gvCustomers.DataSource = dt;
                gvCustomers.DataBind();
            }
        }
    }
}

protected void Edit(object sender, EventArgs e)
{
    GridViewRow row = (sender as Button).NamingContainer as GridViewRow;
    lblId.Text = row.Cells[0].Text.Trim();
    txtName.Text = row.Cells[1].Text.Trim();
    txtCountry.Text = row.Cells[2].Text.Trim();
}

protected void Delete(object sender, EventArgs e)
{
    GridViewRow row = (sender as Button).NamingContainer as GridViewRow;
    string customerId = row.Cells[0].Text.Trim();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("Delete From Customers WHERE CustomerId = @Id", con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Id", customerId);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

    BindGridView();
}

protected void Save(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("UPDATE Customers SET Name = @Name,Country = @Country WHERE CustomerId = @Id", con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Id", lblId.Text.Trim());
            cmd.Parameters.AddWithValue("@Name", txtName.Text.Trim());
            cmd.Parameters.AddWithValue("@Country", txtCountry.Text.Trim());
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

    lblId.Text = string.Empty;
    txtName.Text = string.Empty;
    txtCountry.Text = string.Empty;

    BindGridView();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        BindGridView()
    End If
End Sub

Private Sub BindGridView()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId,Name,Country FROM Customers", con)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                cmd.CommandType = CommandType.Text
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                gvCustomers.DataSource = dt
                gvCustomers.DataBind()
            End Using
        End Using
    End Using
End Sub

Protected Sub Edit(ByVal sender As Object, ByVal e As EventArgs)
    Dim row As GridViewRow = TryCast((TryCast(sender, Button)).NamingContainer, GridViewRow)
    lblId.Text = row.Cells(0).Text.Trim()
    txtName.Text = row.Cells(1).Text.Trim()
    txtCountry.Text = row.Cells(2).Text.Trim()
End Sub

Protected Sub Delete(ByVal sender As Object, ByVal e As EventArgs)
    Dim row As GridViewRow = TryCast((TryCast(sender, Button)).NamingContainer, GridViewRow)
    Dim customerId As String = row.Cells(0).Text.Trim()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("Delete From Customers WHERE CustomerId = @Id", con)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Id", customerId)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    BindGridView()
End Sub

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("UPDATE Customers SET Name = @Name,Country = @Country WHERE CustomerId = @Id", con)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Id", lblId.Text.Trim())
            cmd.Parameters.AddWithValue("@Name", txtName.Text.Trim())
            cmd.Parameters.AddWithValue("@Country", txtCountry.Text.Trim())
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    lblId.Text = String.Empty
    txtName.Text = String.Empty
    txtCountry.Text = String.Empty

    BindGridView()
End Sub

Screenshot