In this article I’m going to explain how to call
ASP.NET page method to execute store procedure with multiple parameters using
jQuery ajax.
One of
my blog reader asked me a question that how to execute store procedure directly
using jQuery. Actually we can’t execute store procedure directly by jQuery. But
we can call ASP.NET page method to execute store procedure. I have already
written few article related to call ASP.NET page method using jQuery. Please refer
following articles
Using jQuery AJAX to call ASP.NET
page method
Save data to database without
postback using jQuery ajax in ASP.NET
Bind GridView using jQuery in ASP.NET
Here we
go, Please follow the steps given below
Table
Design:
Create
Store Procedure:
CREATE PROCEDURE [dbo].[Insert_Data]
(@name varchar(50),
@age int,
@sex varchar(10))
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO
Personal_Details (Name,Age,Sex)
VALUES(@name,@age,@sex)
END
GO
Add
jQuery Library:
First we
have to add jQuery library. You can download jQuery library here, or you can
use following jQuery CDN.
<script type="text/javascript"
src="//code.jquery.com/jquery-1.10.2.min.js"></script>
Add
Script:
Here we have to write
jQuery ajax method to call ASP.NET page method.
<script type="text/javascript">
$(document).ready(function () {
$('#btnsubmit').click(function () {
var
name = $('#txtName').val();
var
age = $('#txtAge').val();
var
sex = $('#txtSex').val();
$.ajax({
type: 'POST',
contentType: "application/json; charset=utf-8",
url: 'Default.aspx/Insert_Data',
data: "{'name':'" + name + "','age':'" + age + "','sex':'" + sex + "'}",
async: false,
success: function (response) {
$('#txtName').val('');
$('#txtAge').val('');
$('#txtSex').val('');
alert("Record saved successfully..!!");
},
error: function () {
alert("Error");
}
});
});
});
</script>
Connection
String:
Please add
connection string on web.config file.
<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=localhost; Initial Catalog=Demo; Integrated Security=True"/>
</connectionStrings>
Page
Method:
Here we have to write page
Method which is used to store data to SQL Server database. Method should be
declared as static with the [WebMethod] attribute.
[WebMethod]
public static string
Insert_Data(string name, string age, string
sex)
{
SqlConnection
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
try
{
SqlCommand
cmd = new SqlCommand();
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Insert_Data";
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@age", age);
cmd.Parameters.AddWithValue("@sex", sex);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
return
"success";
}
catch (Exception ex)
{
return
"error";
}
}
Putting
it all together:
HTML
code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<script type="text/javascript" src="//code.jquery.com/jquery-1.10.2.min.js"></script>
<title></title>
<script type="text/javascript">
$(document).ready(function () {
$('#btnsubmit').click(function () {
var
name = $('#txtName').val();
var age
= $('#txtAge').val();
var
sex = $('#txtSex').val();
$.ajax({
type: 'POST',
contentType: "application/json; charset=utf-8",
url: 'Default.aspx/Insert_Data',
data: "{'name':'" + name + "','age':'" + age + "','sex':'" + sex + "'}",
async: false,
success: function (response) {
$('#txtName').val('');
$('#txtAge').val('');
$('#txtSex').val('');
alert("Record saved successfully..!!");
},
error: function () {
alert("Error");
}
});
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table align="center" class="style1">
<tr>
<td> Name </td>
<td> <asp:TextBox ID="txtName"
runat="server"
ClientIDMode="Static"></asp:TextBox> </td>
</tr>
<tr>
<td> Age </td>
<td> <asp:TextBox ID="txtAge" runat="server"
ClientIDMode="Static"></asp:TextBox> </td>
</tr>
<tr>
<td> Sex </td>
<td> <asp:TextBox ID="txtSex" runat="server"
ClientIDMode="Static"></asp:TextBox> </td>
</tr>
<tr>
<td> <input type="button"
id="btnsubmit"
value="Submit"
/> </td>
</tr>
</table>
</div>
</form>
</body>
</html>
C#
code:
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 System.Web.Services;
public partial class _Default :
System.Web.UI.Page
{
protected void Page_Load(object
sender, EventArgs e)
{
}
[WebMethod]
public static string
Insert_Data(string name, string age, string
sex)
{
SqlConnection
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
try
{
SqlCommand
cmd = new SqlCommand();
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Insert_Data";
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@age", age);
cmd.Parameters.AddWithValue("@sex", sex);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
return
"success";
}
catch (Exception ex)
{
return
"error";
}
}
}