ExtJs、ASP.net运用Linq to SQL与SQL储存过程交互

最新推荐文章于 2024-07-12 16:42:46 发布

weixin_30500105

最新推荐文章于 2024-07-12 16:42:46 发布

阅读量66

点赞数

文章标签： json xhtml 数据库

原文链接：http://www.cnblogs.com/taoziyy/articles/2981346.html

版权

1 基本要点

　　1.1 Grid后台分页

　　1.2 Form表单提交

　　1.3 前台更新、删除(store.remove)、增加(store.insert)

　　1.4 有webService、aspx、Handler三种方法，此处使用Handler

　　1.5 使用Linq to SQL进行读取数据和 SQL储存过程的操作

　　1.6 文件结构

2 数据库文件

代码

create table tb_user(
use_id int primary key ,
use_name varchar ( 20 ) not null ,
use_sex varchar ( 2 ) not null ,
use_address varchar ( 50 ) not null
);

insert into tb_user values ( ' 1 ' , ' 陈小 ' , ' 女 ' , ' 广东省 ' );
insert into tb_user values ( ' 2 ' , ' 钟天 ' , ' 男 ' , ' 四川省 ' );
insert into tb_user values ( ' 3 ' , ' 李四 ' , ' 男 ' , ' 云南省 ' );
insert into tb_user values ( ' 4 ' , ' 叶茂 ' , ' 男 ' , ' 广东省 ' );
insert into tb_user values ( ' 5 ' , ' 吴名 ' , ' 男 ' , ' 黑龙江 ' );
insert into tb_user values ( ' 6 ' , ' 陈少敏 ' , ' 女 ' , ' 广东省 ' );
insert into tb_user values ( ' 7 ' , ' 艾青 ' , ' 男 ' , ' 四川省 ' );
insert into tb_user values ( ' 8 ' , ' 陈国新 ' , ' 男 ' , ' 云南省 ' );
insert into tb_user values ( ' 9 ' , ' 刘宁 ' , ' 男 ' , ' 广东省 ' );
insert into tb_user values ( ' 10 ' , ' 周杰 ' , ' 男 ' , ' 黑龙江 ' );
insert into tb_user values ( ' 11 ' , ' 张娜 ' , ' 女 ' , ' 广东省 ' );
insert into tb_user values ( ' 12 ' , ' 谢娜 ' , ' 女 ' , ' 四川省 ' );
insert into tb_user values ( ' 13 ' , ' 萧青 ' , ' 男 ' , ' 云南省 ' );
insert into tb_user values ( ' 14 ' , ' 叶子 ' , ' 男 ' , ' 广东省 ' );
insert into tb_user values ( ' 15 ' , ' 梅工 ' , ' 男 ' , ' 黑龙江 ' );
insert into tb_user values ( ' 16 ' , ' 卢可 ' , ' 女 ' , ' 广东省 ' );
insert into tb_user values ( ' 17 ' , ' 吴晰 ' , ' 男 ' , ' 四川省 ' );
insert into tb_user values ( ' 18 ' , ' 张天佑 ' , ' 男 ' , ' 云南省 ' );
insert into tb_user values ( ' 19 ' , ' 刘德华 ' , ' 男 ' , ' 广东省 ' );
insert into tb_user values ( ' 20 ' , ' 张颖 ' , ' 男 ' , ' 黑龙江 ' );

create table tb_score(
use_id int ,
sco_subject varchar ( 20 ) not null ,
sco_score smallint not null ,
foreign key (use_id) references tb_user(use_id)
);

insert into tb_score values ( ' 1 ' , ' 数学 ' , ' 97 ' );
insert into tb_score values ( ' 2 ' , ' 英语 ' , ' 97 ' );
insert into tb_score values ( ' 3 ' , ' 物理 ' , ' 97 ' );
insert into tb_score values ( ' 4 ' , ' 数学 ' , ' 97 ' );
insert into tb_score values ( ' 5 ' , ' 英语 ' , ' 97 ' );
insert into tb_score values ( ' 6 ' , ' 数学 ' , ' 97 ' );
insert into tb_score values ( ' 7 ' , ' 英语 ' , ' 97 ' );
insert into tb_score values ( ' 8 ' , ' 物理 ' , ' 97 ' );
insert into tb_score values ( ' 9 ' , ' 数学 ' , ' 97 ' );
insert into tb_score values ( ' 10 ' , ' 英语 ' , ' 97 ' );
insert into tb_score values ( ' 11 ' , ' 数学 ' , ' 97 ' );
insert into tb_score values ( ' 12 ' , ' 英语 ' , ' 97 ' );
insert into tb_score values ( ' 13 ' , ' 物理 ' , ' 97 ' );
insert into tb_score values ( ' 14 ' , ' 数学 ' , ' 97 ' );
insert into tb_score values ( ' 15 ' , ' 英语 ' , ' 97 ' );
insert into tb_score values ( ' 16 ' , ' 数学 ' , ' 97 ' );
insert into tb_score values ( ' 17 ' , ' 英语 ' , ' 97 ' );
insert into tb_score values ( ' 18 ' , ' 物理 ' , ' 97 ' );
insert into tb_score values ( ' 19 ' , ' 数学 ' , ' 97 ' );
insert into tb_score values ( ' 20 ' , ' 英语 ' , ' 97 ' );

/* ******************* */
/* 插入 */
/* ****************** */

-- drop PROCEDURE pro_Insert_values
CREATE PROCEDURE pro_Insert_values
@inp_use_id int ,
@inp_use_name nvarchar ( 20 ),
@inp_use_sex nvarchar ( 2 ),
@inp_use_address nvarchar ( 50 ),
@inp_sco_subject nvarchar ( 20 ),
@inp_sco_score smallint ,
@out_message nvarchar ( 2 ) output
AS
BEGIN TRAN
SET @out_message = ''
BEGIN TRY
INSERT INTO tb_user VALUES ( @inp_use_id , @inp_use_name , @inp_use_sex , @inp_use_address )
IF @@rowcount = 0
SET @out_message = ' 0 '
INSERT INTO tb_score VALUES ( @inp_use_id , @inp_sco_subject , @inp_sco_score )
IF @@rowcount = 0
SET @out_message = ' 0 '
END TRY
BEGIN CATCH
SET @out_message = ' 0 '
END CATCH
IF @@ERROR <> 0
ROLLBACK TRAN A
ELSE
if @out_message = '' SET @out_message = ' 1 '
COMMIT TRAN A
go
-- DECLARE @out_message nvarchar(2)
-- EXEC pro_Insert_values 27,'小帆','男','广东省','语文','a',@out_message output print @out_message

/* ******************* */
/* 删除 */
/* ****************** */

drop PROCEDURE pro_Delete_values
CREATE PROCEDURE pro_Delete_values
@inp_use_id int ,
@out_message nvarchar ( 2 ) output
AS
BEGIN TRAN
SET @out_message = ''
BEGIN TRY
DELETE FROM tb_score WHERE use_id = @inp_use_id
IF @@rowcount = 0
SET @out_message = ' 0 '
DELETE FROM tb_user WHERE use_id = @inp_use_id
IF @@rowcount = 0
SET @out_message = ' 0 '
END TRY
BEGIN CATCH
SET @out_message = ' 0 '
END CATCH
IF @@ERROR <> 0
ROLLBACK TRAN A
ELSE
if @out_message = '' SET @out_message = ' 1 '
COMMIT TRAN
go

-- declare @out_message nvarchar(2)
-- EXEC pro_Delete_values 39,@out_message output print @out_message

/* ******************* */
/* 更新 */
/* ****************** */

-- drop PROCEDURE pro_Update_values
CREATE PROCEDURE pro_Update_values
@inp_use_id int ,
@inp_use_name nvarchar ( 20 ),
@inp_use_sex nvarchar ( 2 ),
@inp_use_address nvarchar ( 50 ),
@inp_sco_subject nvarchar ( 20 ),
@inp_sco_score smallint ,
@out_message nvarchar ( 2 ) output
AS
set @out_message = ''
BEGIN TRAN A
BEGIN TRY
UPDATE tb_score SET sco_subject = @inp_sco_subject ,sco_score = @inp_sco_score WHERE use_id = @inp_use_id
if @@rowcount = 0
set @out_message = ' 0 '
UPDATE tb_user SET use_name = @inp_use_name ,use_sex = @inp_use_sex ,use_address = @inp_use_address WHERE use_id = @inp_use_id
if @@rowcount = 0
set @out_message = ' 0 '
END TRY
BEGIN CATCH
SET @out_message = ' 0 '
END CATCH
IF @@ERROR <> 0
ROLLBACK TRAN A
ELSE
if @out_message = '' SET @out_message = ' 1 '
COMMIT TRAN A
go

-- 数据库存在@use_id=1
-- declare @out_message nvarchar(2)
-- EXEC pro_Update_values 1,'张三','男','广东省','语文',97,@out_message output print @out_message
--
-- 数据库不存在@use_id=10
-- declare @out_message nvarchar(2)
-- EXEC pro_Update_values 10,'李四','男','广东省','语文',97,@out_message output print @out_message

3 建立Linq to SQL类

4 建立Json序列化的类库(JsonHelper.cs)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Web.Script.Serialization; // 添加Json序列化的引用

namespace Service
{
public static class JsonHelper
{
public static string Jso_ToJSON( this object tem_obj)
{
JavaScriptSerializer tem_serializer = new JavaScriptSerializer();
return tem_serializer.Serialize(tem_obj);
}

public static string Jso_ToJSON( this object tem_obj, int tem_recursionDepth)
{
JavaScriptSerializer tem_serializer = new JavaScriptSerializer();
tem_serializer.RecursionLimit = tem_recursionDepth;
return tem_serializer.Serialize(tem_obj);
}
}
}

5 建立ashx文件，即使用handler传送

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Service;

using System.Web.Services;
namespace ExtJs_ASP_Insert_Delete_Modify_Update
{
/// <summary>
/// $codebehindclassname$ 的摘要说明
/// </summary>
[WebService(Namespace = " http://tempuri.org/ " )]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Handler1 : IHttpHandler
{
private DataClasses1DataContext db = new DataClasses1DataContext();

public void ProcessRequest(HttpContext context)
{
string tem_response = string .Empty;
string tem_str = context.Request.QueryString[ " parm " ];
if ( string .IsNullOrEmpty(tem_str))
{
context.Response.Write( " error! " );
return ;
}

context.Response.ContentType = " text/json " ;

if (tem_str == " Get " )
{
context.Response.Write(Get(context));
}
else if (tem_str == " Insert " )
{
context.Response.Write(Insert(context));
}
else if (tem_str == " Update " )
{
context.Response.Write(Update(context));
}
else if (tem_str == " Delete " )
{
context.Response.Write(Delete(context));
}

return ;
}

public string Get(HttpContext context)
{
int tem_start = int .Parse(context.Request.Form[ " start " ] + "" ); // 分页需要limit,start是mysql里用的（或取当页开始的记录标识编号）
int tem_limit = int .Parse(context.Request.Form[ " limit " ] + "" ); // 或取每页记录数

var tem_query = from a in db.tb_user
from b in db.tb_score
where a.use_id == b.use_id
orderby a.use_id descending
select new { a.use_id, a.use_name, a.use_sex, a.use_address, b.sco_subject, b.sco_score };

int tem_Count = tem_query.Count(); // 所要记录数
int tem_PageNum = tem_start / tem_limit; // 共有页数
int tem_PageSize = tem_limit;
tem_query = tem_query.Skip(tem_PageSize * tem_PageNum).Take(tem_PageSize); // 当前页记录

string JsonSource = JsonHelper.Jso_ToJSON(tem_query); // 当前页记录转成JSON格式
string strJsonSource = @" {""totalCount"":"" " + tem_Count + "" ;
strJsonSource = strJsonSource + @" "",""data"": " + JsonSource + " } " ; // Grid的分页区显示所有记录数增加totalCount信息
return strJsonSource;
}

public string Insert(HttpContext context)
{

string tem_out_message = null ;
try
{
db.pro_Insert_values( int .Parse(context.Request.QueryString[ " use_id " ]), context.Request.QueryString[ " use_name " ].ToString(), context.Request.QueryString[ " use_sex " ].ToString(),
context.Request.QueryString[ " use_address " ].ToString(), context.Request.QueryString[ " sco_subject " ].ToString(), short .Parse(context.Request.QueryString[ " sco_score " ]), ref tem_out_message);
}
catch
{
tem_out_message = " -1 " ;
}
if (tem_out_message == " 1 " )
{
return " {success:true} " ;
}
else if (tem_out_message == " -1 " )
{
return " { success: false, errors:{info: '输入类型不匹配'} } " ;
}
else
{
return " { success: false, errors:{info: '插入失败'} } " ;
}
}

public string Update(HttpContext context)
{

string tem_out_message = null ;
try
{
db.pro_Update_values( int .Parse(context.Request.QueryString[ " use_id " ]), context.Request.QueryString[ " use_name " ].ToString(), context.Request.QueryString[ " use_sex " ].ToString(),
context.Request.QueryString[ " use_address " ].ToString(), context.Request.QueryString[ " sco_subject " ].ToString(), short .Parse(context.Request.QueryString[ " sco_score " ]), ref tem_out_message);
}
catch
{
tem_out_message = " -1 " ;
}
if (tem_out_message == " 1 " )
{
return " {success:true} " ;
}
else if (tem_out_message == " -1 " )
{
return " { success: false, errors:{info: '输入类型不匹配'} } " ;
}
else
{
return " { success: false, errors:{info: '更新失败'} } " ;
}
}

public string Delete(HttpContext context)
{
string tem_out_message = null ;
db.pro_Delete_values( int .Parse(context.Request.Form[ " use_id " ]), ref tem_out_message);

if (tem_out_message == " 1 " )
{
return " {success:true} " ;
}
else
{
return " { success: false, errors:{info: '删除失败'} } " ;
}

}

public bool IsReusable
{
get
{
return false ;
}
}
}
}

6 建立Gri_datagrid.js文件

7 前台aspx文件

<% @ Page Language = " C# " AutoEventWireup = " true " CodeBehind = " Grid_Operating.aspx.cs " Inherits = " ExtJs_ASP_Insert_Delete_Modify_Update._Default " %>

<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >

< html xmlns ="http://www.w3.org/1999/xhtml" >
< head runat ="server" >
< link href ="Stylesheet1.css" rel ="stylesheet" type ="text/css" />
< link href ="ExtJs/resources/css/ext-all.css" rel ="stylesheet" type ="text/css" />
< script src ="ExtJs/adapter/ext/ext-base.js" type ="text/javascript" ></ script >
< script src ="ExtJs/ext-all.js" type ="text/javascript" ></ script >

< script src ="myJs/Gri_datagrid.js" type ="text/javascript" ></ script >
< title > 表格操作 </ title >
</ head >
< body >
< form id ="form1" runat ="server" >
< div id ="app_grid" ></ div >
</ form >
</ body >
</ html >

8 运行结果

===========================================================================

9 总结

　　9.1 Linq to SQL序列化过程

　　9.2 储存过程创建有利于数据的传送和维护

/* ******************* */
/* 插入 */
/* ****************** */

-- drop PROCEDURE pro_Insert_values
CREATE PROCEDURE pro_Insert_values
@inp_use_id int ,
@inp_use_name nvarchar ( 20 ),
@inp_use_sex nvarchar ( 2 ),
@inp_use_address nvarchar ( 50 ),
@inp_sco_subject nvarchar ( 20 ),
@inp_sco_score smallint ,
@out_message nvarchar ( 2 ) output
AS
BEGIN TRAN
SET @out_message = ''
BEGIN TRY
INSERT INTO tb_user VALUES ( @inp_use_id , @inp_use_name , @inp_use_sex , @inp_use_address )
IF @@rowcount = 0
SET @out_message = ' 0 '
INSERT INTO tb_score VALUES ( @inp_use_id , @inp_sco_subject , @inp_sco_score )
IF @@rowcount = 0
SET @out_message = ' 0 '
END TRY
BEGIN CATCH
SET @out_message = ' 0 '
END CATCH
IF @@ERROR <> 0
ROLLBACK TRAN A
ELSE
if @out_message = '' SET @out_message = ' 1 '
COMMIT TRAN A
go

　　9.3 进行后台分页有利于数据加载和单位时间内数据传送的合理性(凑成Json的字符串进行数据传送)

public string Get(HttpContext context)
{
int tem_start = int .Parse(context.Request.Form[ " start " ] + "" ); // 分页需要limit,start是mysql里用的（或取当页开始的记录标识编号）
int tem_limit = int .Parse(context.Request.Form[ " limit " ] + "" ); // 或取每页记录数

var tem_query = from a in db.tb_user
from b in db.tb_score
where a.use_id == b.use_id
orderby a.use_id descending
select new { a.use_id, a.use_name, a.use_sex, a.use_address, b.sco_subject, b.sco_score };

int tem_Count = tem_query.Count(); // 所要记录数
int tem_PageNum = tem_start / tem_limit; // 共有页数
int tem_PageSize = tem_limit;
tem_query = tem_query.Skip(tem_PageSize * tem_PageNum).Take(tem_PageSize); // 当前页记录

string JsonSource = JsonHelper.Jso_ToJSON(tem_query); // 当前页记录转成JSON格式
string strJsonSource = @" {""totalCount"":"" " + tem_Count + "" ;
strJsonSource = strJsonSource + @" "",""data"": " + JsonSource + " } " ; // Grid的分页区显示所有记录数增加totalCount信息
return strJsonSource;
}

　　9.4 用Linq to SQL调用SQL的储存过程更有利于程序的正常情况下的执行

public string Insert(HttpContext context)
{

string tem_out_message = null ;
try
{
db.pro_Insert_values( int .Parse(context.Request.QueryString[ " use_id " ]), context.Request.QueryString[ " use_name " ].ToString(), context.Request.QueryString[ " use_sex " ].ToString(),
context.Request.QueryString[ " use_address " ].ToString(), context.Request.QueryString[ " sco_subject " ].ToString(), short .Parse(context.Request.QueryString[ " sco_score " ]), ref tem_out_message);
}
catch
{
tem_out_message = " -1 " ;
}
if (tem_out_message == " 1 " )
{
return " {success:true} " ;
}
else if (tem_out_message == " -1 " )
{
return " { success: false, errors:{info: '输入类型不匹配'} } " ;
}
else
{
return " { success: false, errors:{info: '插入失败'} } " ;
}
}

　　9.5 相当资料帮助

　　中文：http://msdn.microsoft.com/zh-cn/library

　　英文：http://msdn.microsoft.com/en-us/library

10 下载

http://img.fengfly.com/uploads/file/201009/20100907115814.rar (仅支持直接点击下载)

转载于:https://www.cnblogs.com/taoziyy/articles/2981346.html

weixin_30500105

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ExtJs、ASP.net运用Linq to SQL与SQL储存过程交互

1 基本要点　　1.1 Grid后台分页　　1.2 Form表单提交　　1.3前台更新、删除(store.remove)、增加(store.insert)　　1.4 有webService、aspx、Handler三种方法，此处使用Handler　　1.5 使用Linq to SQL进行读取数据和 SQL储存过程的操作　　1.6 文件结构　　2 数据库文件...
复制链接

扫一扫