WCF JSON Rest Service Select Insert Update Delete (CRUD) in C# and VB.Net

Last Reply one month ago By dharmendr

Posted one month ago

hello, 

i am using this snippet for user login

https://www.aspsnippets.com/Articles/Simple-User-Login-Form-example-in-ASPNet.aspx

it works fine, what i want is, i want to convert all methods in user.cs file convert into JSON api so i can use the same logic in android app.

how to make REST API for insert update and delete.

Posted one month ago

Hi nauna,

Refering the below article i have created an example.

Simple WCF JSON Rest Service Tutorial with example in C# and VB.Net

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(function () {
            $("#btnSearch").live("click", function () {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: '<%=ResolveUrl("~/Services/Service.svc/GetCustomers") %>',
                    data: '{"prefix": "' + $("#prefix").val() + '"}',
                    processData: false,
                    dataType: "json",
                    success: function (response) {
                        var customers = eval(response.d);
                        var html = "<table><tr>";
                        html += "<td>Id</td><td>Name</td><td>Country</td></tr>";
                        $.each(customers, function () {
                            html += "<tr><td>" + this.Id + "</td><td>" + this.Name + "</td><td>" + this.Country + "</td></tr>";
                        });
                        html += "</table>";
                        $("#results").html(html == "" ? "No results" : html);
                    },
                    error: function (a, b, c) {
                        alert(a.responseText);
                    }
                });
            });

            $("#btnInsert").live("click", function () {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: '<%=ResolveUrl("~/Services/Service.svc/InsertCustomer") %>',
                    data: '{"name": "' + $("#txtName").val() + '","country": "' + $("#txtCountry").val() + '"}',
                    processData: false,
                    dataType: "json",
                    success: function (response) {
                        alert('Record Inserted successfully');
                    },
                    error: function (a, b, c) {
                        alert(a.responseText);
                    }
                });
            });
            $("#btnUpdate").live("click", function () {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: '<%=ResolveUrl("~/Services/Service.svc/UpdateCustomer") %>',
                    data: '{"id": ' + $("#txtId").val() + ',"name": "' + $("#txtName").val() + '","country": "' + $("#txtCountry").val() + '"}',
                    processData: false,
                    dataType: "json",
                    success: function (response) {
                        alert('Record updated successfully');
                    },
                    error: function (a, b, c) {
                        alert(a.responseText);
                    }
                });
            });

            $("#btnDelete").live("click", function () {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: '<%=ResolveUrl("~/Services/Service.svc/DeleteCustomer") %>',
                    data: '{"id": ' + $("#txtId").val() + '}',
                    processData: false,
                    dataType: "json",
                    success: function (response) {
                        alert('Record deleted successfully');
                    },
                    error: function (a, b, c) {
                        alert(a.responseText);
                    }
                });
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <input type="text" id="prefix" />
    <input id="btnSearch" type="button" value="Search" />
    <div id="results">
    </div>
    <br />
    <table border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td>
                Id
            </td>
            <td>
                <asp:TextBox runat="server" ID="txtId" />
            </td>
        </tr>
        <tr>
            <td>
                Name
            </td>
            <td>
                <asp:TextBox runat="server" ID="txtName" />
            </td>
        </tr>
        <tr>
            <td>
                Country
            </td>
            <td>
                <asp:TextBox runat="server" ID="txtCountry" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Button ID="btnInsert" Text="Insert" runat="server" />
                <asp:Button ID="btnUpdate" Text="Update" runat="server" />
                <asp:Button ID="btnDelete" Text="Delete" runat="server" />
            </td>
        </tr>
    </table>
    </form>
</body>
</html>

IService.cs

using System.ServiceModel;
using System.ServiceModel.Web;

[ServiceContract]
public interface IService
{
    [OperationContract]
    [WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json)]
    string GetCustomers(string prefix);

    [OperationContract]
    [WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json)]
    void InsertCustomer(string name, string country);

    [OperationContract]
    [WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json)]
    void UpdateCustomer(int id, string name, string country);

    [OperationContract]
    [WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json)]
    void DeleteCustomer(int id);
}

Service.cs

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

[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class Service : IService
{
    public string GetCustomers(string prefix)
    {
        List<object> customers = new List<object>();
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "SELECT * FROM Customers WHERE Name = @prefix OR @prefix IS NULL";
                cmd.Parameters.AddWithValue("@prefix", !string.IsNullOrEmpty(prefix) ? prefix : (object)DBNull.Value);
                cmd.Connection = conn;
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        customers.Add(new
                        {
                            Id = sdr["CustomerId"],
                            Name = sdr["Name"],
                            Country = sdr["Country"]
                        });
                    }
                }
                conn.Close();
            }
            return (new JavaScriptSerializer().Serialize(customers));
        }
    }

    public void InsertCustomer(string name, string country)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "INSERT INTO Customers VALUES(@Name,@Country)";
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Country", country);
                cmd.Connection = conn;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

    public void UpdateCustomer(int id, string name, string country)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "UPDATE Customers SET Name=@Name,Country=@Country WHERE CustomerId=@Id";
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Country", country);
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Connection = conn;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

    public void DeleteCustomer(int id)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "DELETE FROM Customers WHERE CustomerId=@Id";
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Connection = conn;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

Service.vb

Imports System.Collections.Generic
Imports System.Runtime.Serialization
Imports System.ServiceModel
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Script.Serialization
Imports System.ServiceModel.Activation
Imports System.ServiceModel.Web
Imports System.Web.Script.Services

<ServiceContract()> _
<AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)> _
Public Class Service
    <OperationContract()> _
    <WebInvoke(Method:="POST", ResponseFormat:=WebMessageFormat.Json)> _
    Public Function GetCustomers(ByVal prefix As String) As String
        Dim customers As List(Of Object) = New List(Of Object)()
        Using conn As SqlConnection = New SqlConnection()
            conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using cmd As SqlCommand = New SqlCommand()
                cmd.CommandText = "SELECT * FROM Customers WHERE Name = @prefix OR @prefix IS NULL"
                cmd.Parameters.AddWithValue("@prefix", If(Not String.IsNullOrEmpty(prefix), prefix, CObj(DBNull.Value)))
                cmd.Connection = conn
                conn.Open()
                Using sdr As SqlDataReader = cmd.ExecuteReader()
                    While sdr.Read()
                        customers.Add(New With { _
                         Key .Id = sdr("CustomerId"), _
                         Key .Name = sdr("Name"), _
                         Key .Country = sdr("Country") _
                        })
                    End While
                End Using
                conn.Close()
            End Using
            Return (New JavaScriptSerializer().Serialize(customers))
        End Using
    End Function

    <OperationContract()> _
    <WebInvoke(Method:="POST", ResponseFormat:=WebMessageFormat.Json)> _
    Public Sub InsertCustomer(ByVal name As String, ByVal country As String)
        Using conn As SqlConnection = New SqlConnection()
            conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using cmd As SqlCommand = New SqlCommand()
                cmd.CommandText = "INSERT INTO Customers VALUES(@Name,@Country)"
                cmd.Parameters.AddWithValue("@Name", name)
                cmd.Parameters.AddWithValue("@Country", country)
                cmd.Connection = conn
                conn.Open()
                cmd.ExecuteNonQuery()
                conn.Close()
            End Using
        End Using
    End Sub

    <OperationContract()> _
    <WebInvoke(Method:="POST", ResponseFormat:=WebMessageFormat.Json)> _
    Public Sub UpdateCustomer(ByVal id As Integer, ByVal name As String, ByVal country As String)
        Using conn As SqlConnection = New SqlConnection()
            conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using cmd As SqlCommand = New SqlCommand()
                cmd.CommandText = "UPDATE Customers SET Name=@Name,Country=@Country WHERE CustomerId=@Id"
                cmd.Parameters.AddWithValue("@Name", name)
                cmd.Parameters.AddWithValue("@Country", country)
                cmd.Parameters.AddWithValue("@Id", id)
                cmd.Connection = conn
                conn.Open()
                cmd.ExecuteNonQuery()
                conn.Close()
            End Using
        End Using
    End Sub

    <OperationContract()> _
    <WebInvoke(Method:="POST", ResponseFormat:=WebMessageFormat.Json)> _
    Public Sub DeleteCustomer(ByVal id As Integer)
        Using conn As SqlConnection = New SqlConnection()
            conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using cmd As SqlCommand = New SqlCommand()
                cmd.CommandText = "DELETE FROM Customers WHERE CustomerId=@Id"
                cmd.Parameters.AddWithValue("@Id", id)
                cmd.Connection = conn
                conn.Open()
                cmd.ExecuteNonQuery()
                conn.Close()
            End Using
        End Using
    End Sub
End Class

Screenshot