ASP.NET 中的 JQUERY AJAX JSON 示例,无需回发即可将数据插入 SQL SERVER 数据库
简介
在本文中,分享如何使用 jQuery AJAX JSON 和 WebMethod 验证数据并将其保存到 asp.net 中的 SQL SERVER 数据库表中,而无需任何回发(异步)。
描述
jQuery ajax 允许我们从客户端调用声明为 WebMethod 的服务器端 ASP.NET 页面方法/函数,而无需任何回发。通过使用 jQuery 向包含将数据存储在数据库中的代码的 web 方法发送 ajax 请求,可以将数据插入到 sql server 表中。
实例
创建一个示例页面来演示这个概念。
首先在 Sql Server 中创建数据库并将其命名为“BooksDB”或任何您想要的名称。使用下面的脚本创建一个表 (BookDetails)。
CREATE TABLE BookDetails(
[BookId] [int] IDENTITY(1,1) NOT NULL,
[BookName] [varchar](100) NULL,
[Author] [varchar](100) NULL,
[BookTypeId] [int] NULL,
[Price] [decimal](18, 2) NULL,
CONSTRAINT [PK_BookDetails] PRIMARY KEY CLUSTERED
(
[BookId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
注意: 根据您的应用程序替换数据源和初始目录
现在创建一个存储过程来保存书籍的详细信息
CREATE PROCEDURE Sp_SaveBookDetails
@BookName VARCHAR(100),
@Author VARCHAR(100),
@BookTypeId INT,
@Price DECIMAL(18,2)
AS
BEGIN
INSERT INTO BookDetails (BookName,Author,BookTypeId,Price)
VALUES (@BookName,@Author,@BookTypeId,@Price)
END
HTML Source Code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script type="text/javascript">
function SaveRecord() {
//Get control's values
var bookName = $.trim($('#<%=txtName.ClientID %>').val());
var author = $.trim($('#<%=txtAuthor.ClientID %>').val());
var type = $('#<%=ddlType.ClientID %>').val();
var price = $.trim($('#<%=txtPrice.ClientID %>').val());
var msg = "";
//check for validation
if (bookName == '') {
msg += "<li>Please enter book name</li>";
}
if (author == '') {
msg += "<li>Please enter author name</li>";
}
if (type == 0) {
msg += "<li>Please select book type</li>";
}
if (price == '') {
msg += "<li>Please enter book price</li>";
}
if (msg.length == 0) {
//Jquery ajax call to server side method
$.ajax({
type: "POST",
dataType: "json",
contentType: "application/json; charset=utf-8",
//Url is the path of our web method (Page name/function name)
url: "MyPageName.aspx/SaveBookDetails",
//Pass paramenters to the server side function
data: "{'BookName':'" + bookName + "', 'Author':'" + author + "','BookTypeId':'" + type + "','Price':'" + price + "'}",
success: function (response) {
//Success or failure message e.g. Record saved or not saved successfully
if (response.d == true) {
//Set message
$('#dvResult').text("Record saved successfully");
//Reset controls
$('#txtName').val('');
$('#txtAuthor').val('');
$('#ddlType').val("0");
$('#txtPrice').val('');
}
else {
$('#dvResult').text("Record could't be saved");
}
//Fade Out to disappear message after 6 seconds
$('#dvResult').fadeOut(6000);
},
error: function (xhr, textStatus, error) {
//Show error message(if occured)
$('#dvResult').text("Error: " + error);
}
});
}
else {
//Validation failure message
$('#dvResult').html('');
$('#dvResult').html(msg);
}
$('#dvResult').fadeIn();
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>Book Name: </td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Author: </td>
<td>
<asp:TextBox ID="txtAuthor" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Type: </td>
<td>
<asp:DropDownList ID="ddlType" runat="server">
<asp:ListItem Text="--Select--" Value="0"></asp:ListItem>
<asp:ListItem Text="MVC" Value="1"></asp:ListItem>
<asp:ListItem Text="ASP.NET" Value="2"></asp:ListItem>
<asp:ListItem Text="SQL SERVER" Value="3"></asp:ListItem>
</asp:DropDownList></td>
</tr>
<tr>
<td>Price: </td>
<td>
<asp:TextBox ID="txtPrice" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td></td>
<td>
<button type="submit" onclick="SaveRecord();return false">Submit</button>
</td>
</tr>
<tr>
<td></td>
<td>
<div id="dvResult"></div>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Asp.Net C
First add following namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;
then write the code as:
[WebMethod]
public static bool SaveBookDetails(string BookName, string Author, Int32 BookTypeId, decimal Price)
{
bool status;
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("Sp_SaveBookDetails", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@BookName", BookName);
cmd.Parameters.AddWithValue("@Author", Author);
cmd.Parameters.AddWithValue("@BookTypeId", BookTypeId);
cmd.Parameters.AddWithValue("@Price", Price);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
Int32 retVal = cmd.ExecuteNonQuery();
if (retVal > 0)
{
status = true;
}
else
{
status = false;
}
return status;
}
}
}
Asp.Net VB
First add following namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services
Then write the code as:
<WebMethod> _
Public Shared Function SaveBookDetails(BookName As String, Author As String, BookTypeId As Int32, Price As Decimal) As Boolean
Dim status As Boolean
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
Using cmd As New SqlCommand("Sp_SaveBookDetails", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@BookName", BookName)
cmd.Parameters.AddWithValue("@Author", Author)
cmd.Parameters.AddWithValue("@BookTypeId", BookTypeId)
cmd.Parameters.AddWithValue("@Price", Price)
If con.State = ConnectionState.Closed Then
con.Open()
End If
Dim retVal As Int32 = cmd.ExecuteNonQuery()
If retVal > 0 Then
status = True
Else
status = False
End If
Return status
End Using
End Using
End Function