Delete jQuery DataTables row using Ajax JSON and WebService in ASP.Net

Last Reply 5 months ago By dharmendr

Posted 5 months ago

hi.

how to delete datatable row using jquery ajax web service

below is the code ...

reference from

Edit Update row in jQuery DataTables using Ajax JSON and WebService in ASP.Net

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
    <script type="text/javascript" src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $.ajax({
                url: '<%= Page.ResolveUrl("~/WebServiceCS.asmx/GetFiles")%>',
                method: 'post',
                dataType: 'json',
                success: function (data) {
                    $('#datatable').dataTable({
                        paging: true,
                        sort: true,
                        pageLength: 3,
                        searching: true,
                        data: data,
                        columns: [
                            { 'data': 'Id' },
                            { 'data': 'Name' },
                            {
                                'data': 'Data',
                                'sortable': false,
                                'searchable': false,
                                'render': function (Data) {
                                    if (!Data) {
                                        return 'N/A';
                                    }
                                    else {
                                        var img = 'data:image/png;base64,' + Data;
                                        return '<img src="' + img + '" height="50px" width="50px" />';
                                    }
                                }
                            },
                            {
                                "data": null,
                                "defaultContent": '<input type="button" id="btnEdit" class="btn btn-primary" value="Edit" />'
                            }
                        ]
                    });
                }, error: function (response) {
                    alert(response.responseText);
                }
            });
 });
    </script>
</head>
<body style="font-family: Arial">
    <form id="form1" runat="server">
    <table id="tbtUpdate" style="display: none;" class="table">
        <tr>
            <td>Id</td>
            <td><asp:TextBox ID="txtId" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Name</td>
            <td><asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Image</td>
            <td><asp:FileUpload ID="fuUpload" runat="server" /></td>
        </tr>
        <tr>
            <td align="center"><asp:Button Text="Update" runat="server" OnClick="Update" class="btn btn-primary" /></td>
            <td></td>
        </tr>
    </table>
    <br />
    <div style="width: 100%; border: 1px solid black; padding: 3px">
        <table id="datatable">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Image</th>
                    <th>Edit</th>
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Image</th>
                    <th>Edit</th>
                </tr>
            </tfoot>
        </table>
    </div>
    </form>
</body>
</html>

 

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
  
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    public class File
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Data { get; set; }
    }
  
    [WebMethod]
    public void GetFiles()
    {
        string cs = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        List<File> files = new List<File>();
        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlCommand cmd = new SqlCommand("SELECT id,Name,Data FROM tblFiles WHERE ContentType = 'image/jpeg'", con);
            cmd.CommandType = CommandType.Text;
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                File file = new File();
                file.Id = Convert.ToInt32(rdr["id"]);
                file.Name = rdr["Name"].ToString();
                file.Data = Convert.ToBase64String((byte[])rdr["Data"]);
                files.Add(file);
            }
        }
        JavaScriptSerializer js = new JavaScriptSerializer();
        Context.Response.Write(js.Serialize(files));
    }
}

 

Posted 5 months ago

Hi jovceka,

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

Database

For this example i have used table named tblFiles whose schema is defined as follows.

For save record in databse refer below article.

Save and Retrieve images from database in ASP.Net

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
    <script type="text/javascript" src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $.ajax({
                url: '<%= Page.ResolveUrl("~/WebService.asmx/GetFiles")%>',
                method: 'post',
                dataType: 'json',
                success: function (data) {
                    $('#datatable').dataTable({
                        paging: true,
                        sort: true,
                        pageLength: 3,
                        searching: true,
                        data: data,
                        columns: [
                            { 'data': 'Id' },
                            { 'data': 'Name' },
                            {
                                'data': 'Data',
                                'sortable': false,
                                'searchable': false,
                                'render': function (Data) {
                                    if (!Data) {
                                        return 'N/A';
                                    }
                                    else {
                                        var img = 'data:image/png;base64,' + Data;
                                        return '<img src="' + img + '" height="50px" width="50px" />';
                                    }
                                }
                            },
                            {
                                "data": null,
                                "defaultContent": '<input type="button" id="btnEdit" class="btn btn-primary" value="Edit" />'
                            },
                            {
                                "data": null,
                                "defaultContent": '<input type="button" id="btnDelete" class="btn btn-danger" value="Delete" />'
                            }
                        ]
                    });
                }, error: function (response) {
                    alert(response.responseText);
                }
            });

            $('body').on('click', '[id*=btnEdit]', function () {
                var data = $(this).parents('tr').find('td');
                $('[id*=tbtUpdate]').show();
                var id = data.eq(0).html();
                var name = data.eq(1).html();
                $('[id*=txtId]').val(id);
                $('[id*=txtName]').val(name);
            });

            $('body').on('click', '[id*=btnDelete]', function () {
                if (confirm("Do you want to delete this record?")) {
                    var row = $(this).parents('tr');
                    var id = $(row).find('td').eq(0).html();
                    $.ajax({
                        type: 'POST',
                        url: '<%= Page.ResolveUrl("~/WebService.asmx/DeleteFile")%>',
                        data: '{id: ' + id + '}',
                        contentType: "application/json; charset=utf-8",
                        dataType: "json",
                        success: function (response) {
                            if (response.d == 1) {
                                $(row).remove();
                            }
                        }
                    });
                }
            });
        });
    </script>
</head>
<body style="font-family: Arial">
    <form id="form1" runat="server">
    <table id="tbtUpdate" style="display: none;" class="table">
        <tr>
            <td>Id</td>
            <td><asp:TextBox ID="txtId" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Name</td>
            <td><asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Image</td>
            <td><asp:FileUpload ID="fuUpload" runat="server" /></td>
        </tr>
        <tr>
            <td align="center"><asp:Button ID="Button1" Text="Update" runat="server" OnClick="Update" class="btn btn-primary" /></td>
            <td></td>
        </tr>
    </table>
    <br />
    <div style="width: 100%; border: 1px solid black; padding: 3px">
        <table id="datatable">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Image</th>
                    <th>Edit</th>
                    <th>Delete</th>
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Image</th>
                    <th>Edit</th>
                    <th>Delete</th>
                </tr>
            </tfoot>
        </table>
    </div>
    </form>
</body>
</html>

WebService

C#

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    public class File
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Data { get; set; }
    }

    [WebMethod]
    public void GetFiles()
    {
        string cs = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        List<File> files = new List<File>();
        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlCommand cmd = new SqlCommand("SELECT id,Name,Data FROM tblFiles WHERE ContentType = 'image/jpeg'", con);
            cmd.CommandType = CommandType.Text;
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                File file = new File();
                file.Id = Convert.ToInt32(rdr["id"]);
                file.Name = rdr["Name"].ToString();
                file.Data = Convert.ToBase64String((byte[])rdr["Data"]);
                files.Add(file);
            }
            con.Close();
        }
        JavaScriptSerializer js = new JavaScriptSerializer();
        Context.Response.Write(js.Serialize(files));
    }

    [WebMethod]
    public int DeleteFile(int id)
    {
        string cs = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        List<File> files = new List<File>();
        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlCommand cmd = new SqlCommand("DELETE FROM tblFiles WHERE id = @Id", con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Id", id);
            con.Open();
            int i = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();

            return i;
        }
    }
}

VB.Net

Imports System.Web
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization
Imports System.Web.Services
Imports System.Web.Services.Protocols

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebService
    Inherits System.Web.Services.WebService

    Public Class File
        Public Property Id As Integer
        Public Property Name As String
        Public Property Data As String
    End Class

    <WebMethod()> _
    Public Sub GetFiles()
        Dim cs As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim files As List(Of File) = New List(Of File)()
        Using con As SqlConnection = New SqlConnection(cs)
            Dim cmd As SqlCommand = New SqlCommand("SELECT id,Name,Data FROM tblFiles WHERE ContentType = 'image/jpeg'", con)
            cmd.CommandType = CommandType.Text
            con.Open()
            Dim rdr As SqlDataReader = cmd.ExecuteReader()
            While rdr.Read()
                Dim file As File = New File()
                file.Id = Convert.ToInt32(rdr("id"))
                file.Name = rdr("Name").ToString()
                file.Data = Convert.ToBase64String(CType(rdr("Data"), Byte()))
                files.Add(file)
            End While
        End Using
        Dim js As JavaScriptSerializer = New JavaScriptSerializer()
        Context.Response.Write(js.Serialize(files))
    End Sub

    <WebMethod()>
    Public Function DeleteFile(ByVal id As Integer) As Integer
        Dim cs As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim files As List(Of File) = New List(Of File)()
        Using con As SqlConnection = New SqlConnection(cs)
            Dim cmd As SqlCommand = New SqlCommand("DELETE FROM tblFiles WHERE id = @Id", con)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Id", id)
            con.Open()
            Dim i As Integer = Convert.ToInt32(cmd.ExecuteScalar())
            con.Close()
            Return i
        End Using
    End Function
End Class

Namespaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.IO

Code

C#

protected void Update(object sender, EventArgs e)
{
    string contentType = fuUpload.PostedFile.ContentType;
    using (Stream fs = fuUpload.PostedFile.InputStream)
    {
        using (BinaryReader br = new BinaryReader(fs))
        {
            byte[] bytes = br.ReadBytes((Int32)fs.Length);
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("UPDATE tblFiles SET Name=@Name, ContentType=@ContentType, Data=@Data WHERE id=@Id"))
                {
                    cmd.Parameters.AddWithValue("@Id", txtId.Text.Trim());
                    cmd.Parameters.AddWithValue("@Name", txtName.Text.Trim());
                    cmd.Parameters.AddWithValue("@ContentType", contentType);
                    cmd.Parameters.AddWithValue("@Data", bytes);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
    }
}

VB.Net

Protected Sub Update(ByVal sender As Object, ByVal e As EventArgs)
    Dim contentType As String = fuUpload.PostedFile.ContentType
    Using fs As Stream = fuUpload.PostedFile.InputStream
        Using br As BinaryReader = New BinaryReader(fs)
            Dim bytes As Byte() = br.ReadBytes(CType(fs.Length, Int32))
            Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
                Using cmd As SqlCommand = New SqlCommand("UPDATE tblFiles SET Name=@Name, ContentType=@ContentType, Data=@Data WHERE id=@Id")
                    cmd.Parameters.AddWithValue("@Id", txtId.Text.Trim())
                    cmd.Parameters.AddWithValue("@Name", txtName.Text.Trim())
                    cmd.Parameters.AddWithValue("@ContentType", contentType)
                    cmd.Parameters.AddWithValue("@Data", bytes)
                    cmd.Connection = con
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using
        End Using
    End Using
End Sub

Screenshot