CRUD with JSON data without using jQuery AJAX in ASP.Net

Hi Rancho079,

Check this example. Now please take its reference and modify the code as per your requirement.

Using the DLL from here http://james.newtonking.com/json/help/index.html

SQL

CREATE TABLE CustomerJSON
(
	Id INT PRIMARY KEY IDENTITY,
	JsonData NVARCHAR(MAX)
)

HTML

<div>
    <table>
        <tr>
            <td>
                Id
            </td>
            <td>
                <asp:TextBox ID="txtId" runat="server" />
            </td>
        </tr>
        <tr>
            <td>
                Name
            </td>
            <td>
                <asp:TextBox ID="txtName" runat="server" />
            </td>
        </tr>
        <tr>
            <td>
                Country
            </td>
            <td>
                <asp:TextBox ID="txtCountry" runat="server" />
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center">
                <asp:Button ID="btnInsert" Text="Insert" runat="server" OnClick="Insert" />
            </td>
        </tr>
    </table>
    <br />
    <asp:GridView runat="server" ID="gvCustomers" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="CustomerId" HeaderText="Id" />
            <asp:BoundField DataField="Name" HeaderText="Name" />
            <asp:BoundField DataField="Country" HeaderText="Country" />
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Button ID="btnEdit" Text="Edit" runat="server" OnClick="Edit" />&nbsp;
                    <asp:Button ID="btnDelete" Text="Delete" runat="server" OnClick="Delete" />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
</div>

Namespaces

C#

using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Newtonsoft.Json;

VB.Net

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports Newtonsoft.Json

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        BindGrid(GetJsonData());
    }
}
private void BindGrid(string json)
{
    DataTable dt = JsonConvert.DeserializeObject<DataTable>(json);
    ViewState["Data"] = dt;
    this.gvCustomers.DataSource = dt;
    this.gvCustomers.DataBind();
}
private string GetJsonData()
{
    string json = "";
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        SqlCommand cmd = new SqlCommand("SELECT JsonData FROM CustomerJSON", con);
        con.Open();
        json = Convert.ToString(cmd.ExecuteScalar());
        con.Close();
    }

    return json;
}
protected void Insert(object sender, EventArgs e)
{
    DataTable dt = ViewState["Data"] as DataTable;
    DataRow[] dataRow = null;
    if (dt != null)
    {
        dataRow = dt.Select("CustomerId = " + Convert.ToInt32(txtId.Text.Trim()));
    }
    if (dataRow != null)
    {
        foreach (DataRow row in dt.Rows)
        {
            if (row["CustomerId"].ToString() == txtId.Text.Trim())
            {
                row["Name"] = txtName.Text.Trim();
                row["Country"] = txtCountry.Text.Trim();
            }
        }
        string jsonString = JsonConvert.SerializeObject(dt, Formatting.Indented);
        ExecuteNonQuery(jsonString, "UPDATE CustomerJSON SET JsonData = @JsonData");
    }
    else
    {
        Customer customer = new Customer();
        customer.CustomerId = Convert.ToInt32(txtId.Text.Trim());
        customer.Name = txtName.Text.Trim();
        customer.Country = txtCountry.Text.Trim();
        string jsonString = "[" + JsonConvert.SerializeObject(customer, Formatting.Indented) + "]";
        ExecuteNonQuery(jsonString, "INSERT INTO CustomerJSON VALUES(@JsonData)");
    }

    btnInsert.Text = "Insert";
    BindGrid(GetJsonData());
}
private void ExecuteNonQuery(string jsonString, string query)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.Parameters.AddWithValue("@JsonData", jsonString);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}
protected void Edit(object sender, EventArgs e)
{
    GridViewRow row = (sender as Button).NamingContainer as GridViewRow;
    txtId.Text = row.Cells[0].Text;
    txtId.ReadOnly = true;
    txtName.Text = row.Cells[1].Text;
    txtCountry.Text = row.Cells[2].Text;
    btnInsert.Text = "Update";
    BindGrid(GetJsonData());
}
protected void Delete(object sender, EventArgs e)
{
    GridViewRow row = (sender as Button).NamingContainer as GridViewRow;
    string id = row.Cells[0].Text;
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        SqlCommand cmd = new SqlCommand("DELETE FROM CustomerJSON WHERE Id = @Id", con);
        cmd.Parameters.AddWithValue("@Id", id);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}
public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}

VB.Net

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

Private Sub BindGrid(ByVal json As String)
    Dim dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(json)
    ViewState("Data") = dt
    Me.gvCustomers.DataSource = dt
    Me.gvCustomers.DataBind()
End Sub

Private Function GetJsonData() As String
    Dim json As String = ""
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Using con As SqlConnection = New SqlConnection(conString)
        Dim cmd As SqlCommand = New SqlCommand("SELECT JsonData FROM CustomerJSON", con)
        con.Open()
        json = Convert.ToString(cmd.ExecuteScalar())
        con.Close()
    End Using

    Return json
End Function

Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = TryCast(ViewState("Data"), DataTable)
    Dim dataRow As DataRow() = Nothing

    If dt IsNot Nothing Then
        dataRow = dt.[Select]("CustomerId = " & Convert.ToInt32(txtId.Text.Trim()))
    End If

    If dataRow IsNot Nothing Then

        For Each row As DataRow In dt.Rows

            If row("CustomerId").ToString() = txtId.Text.Trim() Then
                row("Name") = txtName.Text.Trim()
                row("Country") = txtCountry.Text.Trim()
            End If
        Next

        Dim jsonString As String = JsonConvert.SerializeObject(dt, Formatting.Indented)
        ExecuteNonQuery(jsonString, "UPDATE CustomerJSON SET JsonData = @JsonData")
    Else
        Dim customer As Customer = New Customer()
        customer.CustomerId = Convert.ToInt32(txtId.Text.Trim())
        customer.Name = txtName.Text.Trim()
        customer.Country = txtCountry.Text.Trim()
        Dim jsonString As String = "[" & JsonConvert.SerializeObject(customer, Formatting.Indented) & "]"
        ExecuteNonQuery(jsonString, "INSERT INTO CustomerJSON VALUES(@JsonData)")
    End If

    btnInsert.Text = "Insert"
    BindGrid(GetJsonData())
End Sub

Private Sub ExecuteNonQuery(ByVal jsonString As String, ByVal query As String)
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Using con As SqlConnection = New SqlConnection(conString)
        Dim cmd As SqlCommand = New SqlCommand(query, con)
        cmd.Parameters.AddWithValue("@JsonData", jsonString)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    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)
    txtId.Text = row.Cells(0).Text
    txtId.[ReadOnly] = True
    txtName.Text = row.Cells(1).Text
    txtCountry.Text = row.Cells(2).Text
    btnInsert.Text = "Update"
    BindGrid(GetJsonData())
End Sub

Protected Sub Delete(ByVal sender As Object, ByVal e As EventArgs)
    Dim row As GridViewRow = TryCast((TryCast(sender, Button)).NamingContainer, GridViewRow)
    Dim id As String = row.Cells(0).Text
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Using con As SqlConnection = New SqlConnection(conString)
        Dim cmd As SqlCommand = New SqlCommand("DELETE FROM CustomerJSON WHERE Id = @Id", con)
        cmd.Parameters.AddWithValue("@Id", id)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Using
End Sub

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

Screenshot