csharp

Retrieve Data from the Database using jQuery/Ajax/JSon in ASP.Net

Here I will explain How to Retrieve Data from the Database using jQuery/Ajax/JSON in ASP.Net.
In this article, I will explain with an example, how to Retrieve Data from the Database using jQuery/Ajax/JSON in ASP.Net.
Key points:Before Retrieve Data from the Database using jQuery/Ajax/JSON in ASP.Net.First Learn How to Insert data from HTML Table to Database Using jQuery Ajax in ASP.Net C#.
Step 1:Before creating the application first create the database and use this database.
First, the multiple rows (records) will be inserted into an HTML Table and then the data from all the rows of the HTML Table will be sent to Web Services Method using jQuery AJAX which will be ultimately saved to the database using.

Create database JqueryDatabase
Use JqueryDatabase

Step 2: After that create a table named “Person” in a database to insert data.

Create table Person(
	Personid int primary key identity(1,1),
	Name varchar(30),
	Address varchar(20),
	Age int
)

Step 3: After creating a table create the stored procedure for insert data into table “Person” and also create a stored procedure for Retrieve data from the database.

	Create proc sp_person_insert
	@name varchar(30),
	@address varchar(20),
	@age int
	as
begin
insert into Person(Name,Address,Age) values(@name,@address,@age)
end
Create proc sp_person_retrieve
as
begin
select * from Person
end

Step 4.Step 1: Create a Web Application
“Start”-“All Program”–>Microsoft VS 2012
“File”–>”New”–>”Project”–>Select Visual C# Web–>ASP.NET Empty website
Give the Project Name like My project Name JqueryDemo1 or whatever you wish
Then Right click on the project then selects “Add New Item” by Default new item name “Person.aspx” Page
Step 5: After that, we need to add the jQuery Library to your project. Install the jquery library Click on “ProjectName”
–>Select “Manage NuGet Packages..”–>Select “nuget.org” from the left menu and write “jquery” on search online(ctrl+E) search box.After successful installation new script folder is generated in your project.

Step 5. Open .aspx page and write this code.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Person.aspx.cs" Inherits="JqueryDemo1.Person" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
      //Here Add Reference jQuery Library
    <script src="jquery.min.js"></script>
    <script type="text/javascript">
          //Create a function for Insert the data
         //In url write your page name like"Person.aspx".
        //Write jQuery Ajax to call WebMethod(InsertData).
       
        $(document).ready(function(){
         GetData();
        });
        function SubmitData() {
            $.ajax({
                url: 'Person.aspx/InsertData',//InsertData is a method name create in ".aspx.cs" page.
                type: 'post',
                contentType: 'application/json;charset=utf-8',
                dataType: 'json',
                data: "{Name:'" +$('#txtname').val()+ "',Address:'" + $('#txtaddress').val() + "',age:'" + $('#txtage').val() + "'}",
                success: function () {
                    alert("Data inserted successfully");
                         GetData(); //Call here GetData Method after click on submit button Data show in grid.
                },
                error:function(){
                    alert("insert errorr");
                }


            });

        }
     //Create a function for Retrieve the data
    ////Write jQuery Ajax to call WebMethod(GetEmpData).

 function GetData() {
            $.ajax({
                url: 'WebForm1.aspx/GetEmpData',
                type: 'post',
                contentType: 'application/json;charset=utf-8',
                dataType: 'json',
                data:"{}",
                success: function (_data) {
                    _data=JSON.parse(_data.d);
                    $("#tbl").find("tr:gt(0)").remove();
                    for(var i=0;i<_data.length;i++){
                  $("#tbl").append('<tr><td>'+_data[i].Name+'</td><td>'+_data[i].Age+'</td> 
    <td>'+_data[i].Address+'</td></tr>');
                    }

                },
                error: function () {
                    alert("Get Error");
                }

            });

        }

    </script>
//Write CSS for Styling Tables
 <style type="text/css">
#tbl {
    font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
    border-collapse: collapse;
    width: 70%;
}

#tbl td, #tbl th {
    border: 1px solid #ddd;
    padding: 8px;
}

#tbl tr:nth-child(even){background-color: #f2f2f2;}

#tbl tr:hover {background-color: #ddd;}

#tbl th {
    padding-top: 12px;
    padding-bottom: 12px;
    text-align: left;
    background-color: #4CAF50;
    color: white;
}
</style>

    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td>Name:</td>
            <td><input type="text" id="txtname" /></td>
        </tr>
        <tr>
            <td>Address:</td>
            <td><input type="text" id="txtaddress" /></td>
        </tr>
        <tr>
            <td>Age:</td>
            <td><input type="text" id="txtage" /></td>
        </tr>
        <tr>
            <td></td>
            <td><input type="button" id="btnsave" value="Submit" onclick="SubmitData()" /></td>
        </tr>
    </table>

 <table id="tbl" >
            <tr>
                <th>Enmployee Name</th>
                <th>Employee Age</th>
                <th>Employee Address</th>
            </tr>

        </table>
    </div>
    </form>
</body>
</html>

Step 6: Open web.config file and Establish a connection with database

Note

  • In initial catalog write Database Name
  • In data source you can simply write (.) /your server name/(local)
  • Integrated security for Authentication
 <connectionStrings>
    <add name="DBCS" connectionString="data source=.; initial catalog=JqueryDatabase;integrated security=true"/>
  </connectionStrings>

Key Point: Before Retrieve Data from the Database using jQuery/Ajax/JSON in ASP.Net.First Install “NewtonSoft.Json” and add your Project . Follow this below given link step by step after successful installation you can see In Reference Folder of your project “Newton.Json” is added.
click this link to install NewtonSoftJson

Step 7: Open “aspx.cs” page.Put This code on “.aspx.cs” Page.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Newtonsoft.Json;
using System.Web.Services;
namespace JqueryDemo1
{
    public partial class Person : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        [WebMethod]
        public static void InsertData(string Name, string Address, int age)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand("sp_person_insert", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@name", Name);
            cmd.Parameters.AddWithValue("@address", Address);
            cmd.Parameters.AddWithValue("@age", age);
            cmd.ExecuteNonQuery();
            con.Close();
        }

     [WebMethod]
        public static string GetEmpData()
        {
            con.Open();
            string _data = "";
            SqlCommand cmd = new SqlCommand("sp_person_retrieve", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            con.Close();
          //Here convert Table data to json and store in String type Variable.
            if (ds.Tables[0].Rows.Count > 0)
            {
                _data = JsonConvert.SerializeObject(ds.Tables[0]);
            }
            return _data;
        }

    }
}

Scrrenshot Describe the output after run the project.

Demo

Any Question and Suggestion related to this article Please Comment Me. Thank You

2 thoughts on “Retrieve Data from the Database using jQuery/Ajax/JSon in ASP.Net

Leave a Reply

Your email address will not be published. Required fields are marked *