Add Delete Row from jQuery DataTable on Client Side using AJAX in ASP.Net

Hi amars,

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

<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
<link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/1.10.13/css/jquery.dataTables.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.13/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
<script type="text/javascript">
    var table;
    $(function () {
        // Bind Record from Database.
        $.ajax({
            type: "POST",
            url: "Default.aspx/PopulateCusromers",
            data: '{}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                var xmlDoc = $.parseXML(response.d);
                var xml = $(xmlDoc);
                var customers = xml.find("Table");
                var rows = '';
                $('#tblCustomers tbody').empty();
                $.each(customers, function () {
                    var id = $(this).find("CustomerId").text();
                    var name = $(this).find("Name").text();
                    var country = $(this).find("Country").text();
                    rows += "<tr><td>" + id +
                    "</td><td>" + name + ' ' +
                    "</td><td>" + country + "</td>" +
                    "<td><input type='button' id='btnDelete' value='Delete' class='btn btn-danger' /></td></tr>";
                });
                $('#tblCustomers tbody').append(rows);
                // Apply DataTable Plugin.
                table = $('[id*=tblCustomers]').DataTable({
                    "order": [[0, "asc"]],
                    dom: 'Bfrtip',
                    iDisplayLength: 4
                });
            },
            error: function (response) {
                var r = jQuery.parseJSON(response.responseText);
                alert("Message: " + r.Message);
            }
        });

        // Add Record to Database.
        $('[id*=btnAdd]').on('click', function () {
            var id = table.rows()[0].length + 1;
            var name = $('[id*=txtName]').val();
            var country = $('[id*=txtCountry]').val();
            var customer = {};
            customer.Name = name;
            customer.Country = country;
            $.ajax({
                type: "POST",
                url: "Default.aspx/InsertCustomers",
                data: '{ customer :' + JSON.stringify(customer) + '}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                async: false,
                success: function (r) {
                    if (r.d > 0) {
                        table.row.add([r.d, name, country, "<input type='button' id='btnDelete' value='Delete' class='btn btn-danger' />"]).draw();
                    }
                }
            });
        });

        // Delete Record from Database.
        $('#tblCustomers tbody').on('click', '[id*=btnDelete]', function () {
            if (confirm('Are you sure delete this record?')) {
                var data = table.row($(this).parents('tr')).data();
                table.row($(this).parents('tr')).remove().draw();
                $.ajax({
                    type: "POST",
                    url: "Default.aspx/DeleteCustomer",
                    data: '{ id :' + data[0] + '}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json"
                });
            }
        });
    });
</script>
<table class="table table-bordered table-striped table-responsive">
    <thead>
        <tr>
            <td>Name</td>
            <td><input type="text" id="txtName" class="form-control" /></td>
        </tr>
        <tr>
            <td>Country</td>
            <td><input type="text" id="txtCountry" class="form-control" /></td>
        </tr>
        <tr>
            <td colspan="2" align="center">
                <input type="button" id="btnAdd" value="Add" class="btn btn-success" />
            </td>
        </tr>
    </thead>
</table>
<br />
<table id="tblCustomers" class="table table-bordered table-striped table-responsive">
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Country</th>
            <th>Action</th>
        </tr>
    </thead>
    <tbody>
    </tbody>
</table>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}

[WebMethod]
public static string PopulateCusromers()
{
    return GetCustomers().GetXml();
}

private static DataSet GetCustomers()
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()))
    {
        SqlCommand cmd = new SqlCommand("SELECT CustomerId,Name,Country FROM Customers");
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                sda.Fill(ds);

                return ds;
            }
        }
    }
}

[WebMethod]
public static int InsertCustomers(Customer customer)
{
    int id = 0;
    string query = "INSERT INTO Customers VALUES(@Name, @Country);SELECT SCOPE_IDENTITY()";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@Name", customer.Name);
            cmd.Parameters.AddWithValue("@Country", customer.Country);
            cmd.Connection = con;
            con.Open();
            id = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
        }
    }

    return id;
}

[WebMethod]
public static int DeleteCustomer(int id)
{
    int status = 0;
    string query = "Delete FROM Customers WHERE CustomerId = @Id";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Connection = con;
            con.Open();
            status = cmd.ExecuteNonQuery();
            con.Close();
        }
    }

    return status;
}

VB.Net

Public Class Customer
    Public Property Id As Integer
    Public Property Name As String
    Public Property Country As String
End Class

<WebMethod()>
Public Shared Function PopulateCusromers() As String
    Return GetCustomers().GetXml()
End Function

Private Shared Function GetCustomers() As DataSet
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ToString())
        Dim cmd As SqlCommand = New SqlCommand("SELECT CustomerId,Name,Country FROM Customers")
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using ds As DataSet = New DataSet()
                sda.Fill(ds)
                Return ds
            End Using
        End Using
    End Using
End Function

<WebMethod()>
Public Shared Function InsertCustomers(ByVal customer As Customer) As Integer
    Dim id As Integer = 0
    Dim query As String = "INSERT INTO Customers VALUES(@Name, @Country);SELECT SCOPE_IDENTITY()"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Parameters.AddWithValue("@Name", customer.Name)
            cmd.Parameters.AddWithValue("@Country", customer.Country)
            cmd.Connection = con
            con.Open()
            id = Convert.ToInt32(cmd.ExecuteScalar())
            con.Close()
        End Using
    End Using

    Return id
End Function

<WebMethod()>
Public Shared Function DeleteCustomer(ByVal id As Integer) As Integer
    Dim status As Integer = 0
    Dim query As String = "Delete FROM Customers WHERE CustomerId = @Id"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Parameters.AddWithValue("@Id", id)
            cmd.Connection = con
            con.Open()
            status = cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    Return status
End Function

Screenshot