ExtJs、ASP.net运用Linq to SQL与SQL储存过程交互
2010-07-29 01:14 夜雨瞳 阅读(2592) 评论(7) 编辑 收藏 举报
1 基本要点1.1 Grid后台分页1.2 Form表单提交1.3 前台更新、删除(store.remove)、增加(store.insert)1.4 有webService、aspx、Handler三种方法，此处使用Handler1.5 使用Linq to SQL进行读取数据和SQL储存过程的操作1.6 文件结构4 建立Json序列化的类库(JsonHelper.cs)5 建立ashx文件，即使用handler传送//分页需要limit,start是mysql里用的（或取当页开始的记录标识编号）//Grid的分页区显示所有记录数增加totalCount信息6 建立Gri_datagrid.js文件
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 数据库文件
代码
createtable tb_user(
use_id intprimarykey,
use_name varchar(20) notnull,
use_sex varchar(2) notnull,
use_address varchar(50) notnull
);
insertinto tb_user values('1','陈小','女','广东省');
insertinto tb_user values('2','钟天','男','四川省');
insertinto tb_user values('3','李四','男','云南省');
insertinto tb_user values('4','叶茂','男','广东省');
insertinto tb_user values('5','吴名','男','黑龙江');
insertinto tb_user values('6','陈少敏','女','广东省');
insertinto tb_user values('7','艾青','男','四川省');
insertinto tb_user values('8','陈国新','男','云南省');
insertinto tb_user values('9','刘宁','男','广东省');
insertinto tb_user values('10','周杰','男','黑龙江');
insertinto tb_user values('11','张娜','女','广东省');
insertinto tb_user values('12','谢娜','女','四川省');
insertinto tb_user values('13','萧青','男','云南省');
insertinto tb_user values('14','叶子','男','广东省');
insertinto tb_user values('15','梅工','男','黑龙江');
insertinto tb_user values('16','卢可','女','广东省');
insertinto tb_user values('17','吴晰','男','四川省');
insertinto tb_user values('18','张天佑','男','云南省');
insertinto tb_user values('19','刘德华','男','广东省');
insertinto tb_user values('20','张颖','男','黑龙江');
createtable tb_score(
use_id int,
sco_subject varchar(20) notnull,
sco_score smallintnotnull,
foreignkey(use_id) references tb_user(use_id)
);
insertinto tb_score values('1','数学','97');
insertinto tb_score values('2','英语','97');
insertinto tb_score values('3','物理','97');
insertinto tb_score values('4','数学','97');
insertinto tb_score values('5','英语','97');
insertinto tb_score values('6','数学','97');
insertinto tb_score values('7','英语','97');
insertinto tb_score values('8','物理','97');
insertinto tb_score values('9','数学','97');
insertinto tb_score values('10','英语','97');
insertinto tb_score values('11','数学','97');
insertinto tb_score values('12','英语','97');
insertinto tb_score values('13','物理','97');
insertinto tb_score values('14','数学','97');
insertinto tb_score values('15','英语','97');
insertinto tb_score values('16','数学','97');
insertinto tb_score values('17','英语','97');
insertinto tb_score values('18','物理','97');
insertinto tb_score values('19','数学','97');
insertinto tb_score values('20','英语','97');
/*********************/
/* 插入 */
/********************/
--drop PROCEDURE pro_Insert_values
CREATEPROCEDURE pro_Insert_values
@inp_use_idint,
@inp_use_namenvarchar(20),
@inp_use_sexnvarchar(2),
@inp_use_addressnvarchar(50),
@inp_sco_subjectnvarchar(20),
@inp_sco_scoresmallint,
@out_messagenvarchar(2) output
AS
BEGINTRAN
SET@out_message=''
BEGIN TRY
INSERTINTO tb_user VALUES(@inp_use_id,@inp_use_name,@inp_use_sex,@inp_use_address)
IF@@rowcount=0
SET@out_message='0'
INSERTINTO 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
ROLLBACKTRAN A
ELSE
if@out_message=''SET@out_message='1'
COMMITTRAN A
go
--DECLARE @out_message nvarchar(2)
--EXEC pro_Insert_values 27,'小帆','男','广东省','语文','a',@out_message output print @out_message
/*********************/
/* 删除 */
/********************/
dropPROCEDURE pro_Delete_values
CREATEPROCEDURE pro_Delete_values
@inp_use_idint,
@out_messagenvarchar(2) output
AS
BEGINTRAN
SET@out_message=''
BEGIN TRY
DELETEFROM tb_score WHERE use_id=@inp_use_id
IF@@rowcount=0
SET@out_message='0'
DELETEFROM 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
ROLLBACKTRAN A
ELSE
if@out_message=''SET@out_message='1'
COMMITTRAN
go
--declare @out_message nvarchar(2)
--EXEC pro_Delete_values 39,@out_message output print @out_message
/*********************/
/* 更新 */
/********************/
--drop PROCEDURE pro_Update_values
CREATEPROCEDURE pro_Update_values
@inp_use_idint,
@inp_use_namenvarchar(20),
@inp_use_sexnvarchar(2),
@inp_use_addressnvarchar(50),
@inp_sco_subjectnvarchar(20),
@inp_sco_scoresmallint,
@out_messagenvarchar(2) output
AS
set@out_message=''
BEGINTRAN A
BEGIN TRY
UPDATE tb_score SET sco_subject=@inp_sco_subject,sco_score=@inp_sco_scoreWHERE 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_addressWHERE 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
ROLLBACKTRAN A
ELSE
if@out_message=''SET@out_message='1'
COMMITTRAN 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
{
publicstaticclass JsonHelper
{
publicstaticstring Jso_ToJSON(thisobject tem_obj)
{
JavaScriptSerializer tem_serializer =new JavaScriptSerializer();
return tem_serializer.Serialize(tem_obj);
}
publicstaticstring Jso_ToJSON(thisobject tem_obj, int tem_recursionDepth)
{
JavaScriptSerializer tem_serializer =new JavaScriptSerializer();
tem_serializer.RecursionLimit = tem_recursionDepth;
return tem_serializer.Serialize(tem_obj);
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Script.Serialization;//添加Json序列化的引用
namespace Service
{
publicstaticclass JsonHelper
{
publicstaticstring Jso_ToJSON(thisobject tem_obj)
{
JavaScriptSerializer tem_serializer =new JavaScriptSerializer();
return tem_serializer.Serialize(tem_obj);
}
publicstaticstring Jso_ToJSON(thisobject 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)]
publicclass Handler1 : IHttpHandler
{
private DataClasses1DataContext db=new DataClasses1DataContext();
publicvoid 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));
}
elseif (tem_str =="Insert")
{
context.Response.Write(Insert(context));
}
elseif (tem_str =="Update")
{
context.Response.Write(Update(context));
}
elseif (tem_str =="Delete")
{
context.Response.Write(Delete(context));
}
return;
}
publicstring 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;
}
publicstring 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}";
}
elseif (tem_out_message =="-1")
{
return"{ success: false, errors:{info: '输入类型不匹配'} }";
}
else
{
return"{ success: false, errors:{info: '插入失败'} }";
}
}
publicstring 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}";
}
elseif (tem_out_message =="-1")
{
return"{ success: false, errors:{info: '输入类型不匹配'} }";
}
else
{
return"{ success: false, errors:{info: '更新失败'} }";
}
}
publicstring 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: '删除失败'} }";
}
}
publicbool IsReusable
{
get
{
returnfalse;
}
}
}
}
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)]
publicclass Handler1 : IHttpHandler
{
private DataClasses1DataContext db=new DataClasses1DataContext();
publicvoid 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));
}
elseif (tem_str =="Insert")
{
context.Response.Write(Insert(context));
}
elseif (tem_str =="Update")
{
context.Response.Write(Update(context));
}
elseif (tem_str =="Delete")
{
context.Response.Write(Delete(context));
}
return;
}
publicstring 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;
}
publicstring 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}";
}
elseif (tem_out_message =="-1")
{
return"{ success: false, errors:{info: '输入类型不匹配'} }";
}
else
{
return"{ success: false, errors:{info: '插入失败'} }";
}
}
publicstring 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}";
}
elseif (tem_out_message =="-1")
{
return"{ success: false, errors:{info: '输入类型不匹配'} }";
}
else
{
return"{ success: false, errors:{info: '更新失败'} }";
}
}
publicstring 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: '删除失败'} }";
}
}
publicbool IsReusable
{
get
{
returnfalse;
}
}
}
}
6 建立Gri_datagrid.js文件
var _start =0
var _limit =5
/**********/
/* 增加 */
/*********/
var formpanel_add =new Ext.FormPanel({
labelWidth: 75,
bodyStyle: 'padding: 10px',
baseCls: 'x-plain',
defauls: { width: 200 },
defaultType: 'textfield',
autoHeight: true,
items: [
{
fieldLabel: '用户帐户',
name: 'use_id',
blankText: '用户帐户不能为空!',
allowBlank: false,
regex: /^[1-9]\d*|0$/,
regexText: '必须为有效的整数!'
}, {
fieldLabel: '用户姓名',
name: 'use_name',
blankText: '用户姓名不能为空!',
maxLength: '10',
maxLengthText:'最大不超过10个字符',
allowBlank: false
}, {
xtype: "panel",
layout: "column",
baseCls: 'x-plain',
fieldLabel: '用户性别',
isFormField: true,
items: [{
columnWidth: .5,
xtype: "radio",
boxLabel: "男",
name: "use_sex",
inputValue: "男",
checked:true,
id: "boy"
}, {
columnWidth: .5,
xtype: "radio",
boxLabel: "女",
name: "use_sex",
inputValue: "女",
id: "girl"
}]
}, {
fieldLabel: '用户地址',
name: 'use_address',
blankText: '用户地址不能为空!',
maxLength: '25',
maxLength:'最大不超过25个字符!',
allowBlank: false
}, {
fieldLabel: '考试科目',
name: 'sco_subject',
blankText: '考试科目不能为空!',
maxLength: '25',
maxLength: '最大不超过25个字符!',
allowBlank: false
}, {
fieldLabel: '考试分数',
name: 'sco_score',
blankText: '考试分数不能为空!',
allowBlank: false,
regex: /^[1-9]\d*|0$/,
regexText:'必须为有效的整数!'
}
]
});
var window_add =new Ext.Window({
title: '学生各科成绩增加',
modal: true,
id:'1',
width: 300,
height: 300,
resizable: false,
plain: true,
iconCls: 'add',
bodyStyle: 'padding:20px;',
buttonAlign: 'center',
closeAction: 'hide',
items: formpanel_add,
listeners: {
"show": function() {
formpanel_add.getForm().reset();
}
},
buttons: [{
text: '保存',
iconCls: 'page_save',
handler: submit
}, {
text: '重置',
iconCls: 'arrow_undo',
handler: function() {
formpanel_add.getForm().reset();
}
}]
});
var TopicRecord = Ext.data.Record.create([
{ name: 'use_id', mapping: 'use_id' },
{ name: 'use_name', mapping: 'use_name' },
{ name: 'use_sex'},
{ name: 'use_address', mapping: 'use_address'},
{ name: 'sco_subject', mapping: 'sco_subject' },
{ name: 'sco_score', mapping: 'sco_score' }
]);
function submit() {
if (!formpanel_add.getForm().isValid()) return;
formpanel_add.getForm().submit({
waitMsg: '正在提交数据',
waitTitle: '提示',
url: 'Handler1.ashx?parm=Insert',
method: 'get',
success: function(form, action) {
var sex ="男";
if (Ext.getCmp("girl").checked ==true)
sex ="女";
var p =new TopicRecord({//创建一个记录源
use_id: formpanel_add.getForm().findField('use_id').getValue(),
use_name: formpanel_add.getForm().findField('use_name').getValue(),
use_sex: sex,
use_address: formpanel_add.getForm().findField('use_address').getValue(),
sco_subject: formpanel_add.getForm().findField('sco_subject').getValue(),
sco_score: formpanel_add.getForm().findField('sco_score').getValue()
});
store.insert(0, p);
Ext.Msg.alert('提示', '保存成功');
},
failure: function(form, action) {
Ext.Msg.alert('错误', '<font color=red>失败：'+ action.result.errors.info +'</font>');
}
});
}
/**********/
/* 更新 */
/*********/
var formpanel_page_edit =new Ext.FormPanel({
labelWidth: 75,
bodyStyle: 'padding: 10px',
baseCls: 'x-plain',
defauls: { width: 200 },
defaultType: 'textfield',
autoHeight: true,
items: [
{
fieldLabel: '<font color=blue>用户帐户</font>',
name: 'use_id',
blankText: '用户帐户不能为空!',
allowBlank: false,
regex: /^[1-9]\d*|0$/,
regexText: '必须为有效的整数!',
readOnly:true
}, {
fieldLabel: '用户姓名',
name: 'use_name',
blankText: '用户姓名不能为空!',
maxLength: '10',
maxLengthText:'最大不超过10个字符!',
allowBlank: false
}, {
xtype: "panel",
layout: "column",
baseCls: 'x-plain',
fieldLabel: '用户性别',
isFormField: true,
items: [{
columnWidth: .5,
xtype: "radio",
boxLabel: "男",
name: "use_sex",
inputValue: "男",
id:"_boy"
}, {
columnWidth: .5,
xtype: "radio",
boxLabel: "女",
name: "use_sex",
inputValue:"女",
id:"_girl"
}]
}, {
fieldLabel: '用户地址',
name: 'use_address',
blankText: '用户地址不能为空!',
maxLength: '25',
maxLengthText: '最大不超过25个字符!',
allowBlank: false
}, {
fieldLabel: '考试科目',
name: 'sco_subject',
blankText: '考试科目不能为空!',
maxLength: '25',
maxLengthText: '最大不超过25个字符!',
allowBlank: false
}, {
fieldLabel: '考试分数',
name: 'sco_score',
blankText: '考试分数不能为空!',
allowBlank: false,
regex: /^[1-9]\d*|0$/,
regexText: '必须为有效的整数!'
}
]
})
var window_page_edit =new Ext.Window({
title: '学生各科成绩更新',
modal: true,
width: 300,
height: 300,
resizable: false,
plain: true,
iconCls: 'page_edit',
bodyStyle: 'padding:20px;',
buttonAlign: 'center',
closeAction: 'hide',
items: formpanel_page_edit,
listeners: {
"show": function() {
row = Ext.getCmp("grid").getSelectionModel().getSelections();
formpanel_page_edit.getForm().reset();
formpanel_page_edit.getForm().loadRecord(row[0]);
}
},
buttons: [{
text: '保存',
iconCls: 'page_save',
handler: Update
}, {
text: '重置',
iconCls: 'arrow_undo',
handler: function() {
formpanel_page_edit.getForm().loadRecord(row[0]);
}
}]
});
function Update() {
if (!formpanel_page_edit.getForm().isValid()) return;
formpanel_page_edit.getForm().submit({
waitMsg: '正在提交数据',
waitTitle: '提示',
url: 'Handler1.ashx?parm=Update',
method: 'get',
success: function(form, action) {
var row_number = Ext.getCmp("grid").getSelectionModel().last;
var row = Ext.getCmp("grid").getSelectionModel().getSelections();
var sex ="男";
if (Ext.getCmp("_girl").checked ==true)
sex ="女";
var p =new TopicRecord({//创建一个记录源
use_id: formpanel_page_edit.getForm().findField('use_id').getValue(),
use_name: formpanel_page_edit.getForm().findField('use_name').getValue(),
use_sex: sex,
use_address: formpanel_page_edit.getForm().findField('use_address').getValue(),
sco_subject: formpanel_page_edit.getForm().findField('sco_subject').getValue(),
sco_score: formpanel_page_edit.getForm().findField('sco_score').getValue()
});
store.remove(row);
store.insert(row_number, p);
Ext.getCmp("grid").getSelectionModel().selectRow(row_number);
Ext.Msg.alert('提示', '保存成功');
},
failure: function(form, action) {
Ext.Msg.alert('错误', '<font color=red>失败：'+ action.result.errors.info +'</font>');
}
});
}
/**********/
/* 删除 */
/*********/
function showResult(btn) {
if (btn =='yes') {
Ext.Msg.wait("请等候", "删除中", "操作进行中......");
var row = Ext.getCmp("grid").getSelectionModel().getSelections();
var jsonData ="";
for (var i =0, len = row.length; i < len; i++) {
var ss = row[i].get("use_id");
if (i ==0)
jsonData = jsonData + ss; //这样处理是为了删除的Lambda语句方便
else
jsonData = jsonData +","+ ss; //这样处理是为了删除的Lambda语句方便
}
//alert(jsonData);
var conn =new Ext.data.Connection();
conn.request({
url: "Handler1.ashx?parm=Delete", //请注意引用的路径
params: { use_id: jsonData },
method: 'post',
scope: this,
callback: function(options, success, response) {
if (success) {
Ext.MessageBox.alert("提示", "所选记录成功删除！");
var row = Ext.getCmp("grid").getSelectionModel().getSelections();
store.remove(row);
}
else
{ Ext.MessageBox.alert("提示", "所选记录删除失败！"); }
}
})
}
};
Ext.onReady(function() {
Ext.QuickTips.init();
store =new Ext.data.JsonStore({
root: 'data',
totalProperty: 'totalCount',
url: 'Handler1.ashx?parm=Get',
fields: [
{ name: 'use_id', type: 'int' },
{ name: 'use_name' },
{ name: 'use_sex' },
{ name: 'use_address' },
{ name: 'sco_subject' },
{ name: 'sco_score' }
]
});
var grid =new Ext.grid.GridPanel({
frame: true,
title: '学生各科成绩表',
stripeRows: true, //斑马线
store: store,
id: 'grid',
applyTo: 'app_grid',
trackMouseOver: true,
height: 300,
width: 500,
loadMask: { msg: '正在加载数据，请稍侯……' },
viewConfig: {
forceFit: true
},
columns: [
new Ext.grid.RowNumberer(), //行号
new Ext.grid.CheckboxSelectionModel({ singleSelect: true }),
{ header: '<font size=2>用户帐户</font>', dataIndex: 'use_id', sortable: true },
{ header: '<font size=2>用户姓名</font>', dataIndex: 'use_name', sortable: true },
{ header: '<font size=2>用户性别</font>', dataIndex: 'use_sex', sortable: true },
{ header: '<font size=2>用户地址</font>', dataIndex: 'use_address', sortable: true },
{ header: '<font size=2>考试科目</font>', dataIndex: 'sco_subject', sortable: true },
{ header: '<font size=2>考试分数</font>', dataIndex: 'sco_score', sortable: true }
],
tbar: new Ext.Toolbar([
'-',
{
text: '<font size=2>增加数据</font>',
iconCls: 'add',
handler: function() {
window_add.show();
}
},
'-',
{
text: '<font size=2>删除数据</font>',
iconCls: 'delete',
handler: function() {
var s = grid.getSelectionModel().getSelections();
if (s.length ==0) {//判断有没有选中行
Ext.Msg.alert('提示', '你还没有选择要操作的记录!');
} elseif (s.length >1) {
Ext.Msg.alert('提示', '不能删除多个操作记录!');
} else {
Ext.MessageBox.confirm('提示', '您确认要删除当前记录吗?', showResult);
};
}
},
'-',
{
text: '<font size=2>更新数据</font>',
iconCls: 'page_edit',
handler: function() {
var s = grid.getSelectionModel().getSelections();
if (s.length ==0) {//判断有没有选中行
Ext.Msg.alert('提示', '你还没有选择要操作的记录!');
} elseif (s.length >1) {
Ext.Msg.alert('提示', '不能更新多个操作记录!');
} else {
window_page_edit.show();
};
}
},
'-'
]),
bbar: new Ext.PagingToolbar({//分页
pageSize: _limit,
store: store,
displayInfo: true, //非要为true，不然不会显示下面的分页按钮
displayMsg: '<font size=2>第 {0} 条到 {1} 条，一共 {2} 条记录</font>',
emptyMsg: "没有记录"
})
})
store.load({ params: { start: _start, limit: _limit} });
})
var _limit =5
/**********/
/* 增加 */
/*********/
var formpanel_add =new Ext.FormPanel({
labelWidth: 75,
bodyStyle: 'padding: 10px',
baseCls: 'x-plain',
defauls: { width: 200 },
defaultType: 'textfield',
autoHeight: true,
items: [
{
fieldLabel: '用户帐户',
name: 'use_id',
blankText: '用户帐户不能为空!',
allowBlank: false,
regex: /^[1-9]\d*|0$/,
regexText: '必须为有效的整数!'
}, {
fieldLabel: '用户姓名',
name: 'use_name',
blankText: '用户姓名不能为空!',
maxLength: '10',
maxLengthText:'最大不超过10个字符',
allowBlank: false
}, {
xtype: "panel",
layout: "column",
baseCls: 'x-plain',
fieldLabel: '用户性别',
isFormField: true,
items: [{
columnWidth: .5,
xtype: "radio",
boxLabel: "男",
name: "use_sex",
inputValue: "男",
checked:true,
id: "boy"
}, {
columnWidth: .5,
xtype: "radio",
boxLabel: "女",
name: "use_sex",
inputValue: "女",
id: "girl"
}]
}, {
fieldLabel: '用户地址',
name: 'use_address',
blankText: '用户地址不能为空!',
maxLength: '25',
maxLength:'最大不超过25个字符!',
allowBlank: false
}, {
fieldLabel: '考试科目',
name: 'sco_subject',
blankText: '考试科目不能为空!',
maxLength: '25',
maxLength: '最大不超过25个字符!',
allowBlank: false
}, {
fieldLabel: '考试分数',
name: 'sco_score',
blankText: '考试分数不能为空!',
allowBlank: false,
regex: /^[1-9]\d*|0$/,
regexText:'必须为有效的整数!'
}
]
});
var window_add =new Ext.Window({
title: '学生各科成绩增加',
modal: true,
id:'1',
width: 300,
height: 300,
resizable: false,
plain: true,
iconCls: 'add',
bodyStyle: 'padding:20px;',
buttonAlign: 'center',
closeAction: 'hide',
items: formpanel_add,
listeners: {
"show": function() {
formpanel_add.getForm().reset();
}
},
buttons: [{
text: '保存',
iconCls: 'page_save',
handler: submit
}, {
text: '重置',
iconCls: 'arrow_undo',
handler: function() {
formpanel_add.getForm().reset();
}
}]
});
var TopicRecord = Ext.data.Record.create([
{ name: 'use_id', mapping: 'use_id' },
{ name: 'use_name', mapping: 'use_name' },
{ name: 'use_sex'},
{ name: 'use_address', mapping: 'use_address'},
{ name: 'sco_subject', mapping: 'sco_subject' },
{ name: 'sco_score', mapping: 'sco_score' }
]);
function submit() {
if (!formpanel_add.getForm().isValid()) return;
formpanel_add.getForm().submit({
waitMsg: '正在提交数据',
waitTitle: '提示',
url: 'Handler1.ashx?parm=Insert',
method: 'get',
success: function(form, action) {
var sex ="男";
if (Ext.getCmp("girl").checked ==true)
sex ="女";
var p =new TopicRecord({//创建一个记录源
use_id: formpanel_add.getForm().findField('use_id').getValue(),
use_name: formpanel_add.getForm().findField('use_name').getValue(),
use_sex: sex,
use_address: formpanel_add.getForm().findField('use_address').getValue(),
sco_subject: formpanel_add.getForm().findField('sco_subject').getValue(),
sco_score: formpanel_add.getForm().findField('sco_score').getValue()
});
store.insert(0, p);
Ext.Msg.alert('提示', '保存成功');
},
failure: function(form, action) {
Ext.Msg.alert('错误', '<font color=red>失败：'+ action.result.errors.info +'</font>');
}
});
}
/**********/
/* 更新 */
/*********/
var formpanel_page_edit =new Ext.FormPanel({
labelWidth: 75,
bodyStyle: 'padding: 10px',
baseCls: 'x-plain',
defauls: { width: 200 },
defaultType: 'textfield',
autoHeight: true,
items: [
{
fieldLabel: '<font color=blue>用户帐户</font>',
name: 'use_id',
blankText: '用户帐户不能为空!',
allowBlank: false,
regex: /^[1-9]\d*|0$/,
regexText: '必须为有效的整数!',
readOnly:true
}, {
fieldLabel: '用户姓名',
name: 'use_name',
blankText: '用户姓名不能为空!',
maxLength: '10',
maxLengthText:'最大不超过10个字符!',
allowBlank: false
}, {
xtype: "panel",
layout: "column",
baseCls: 'x-plain',
fieldLabel: '用户性别',
isFormField: true,
items: [{
columnWidth: .5,
xtype: "radio",
boxLabel: "男",
name: "use_sex",
inputValue: "男",
id:"_boy"
}, {
columnWidth: .5,
xtype: "radio",
boxLabel: "女",
name: "use_sex",
inputValue:"女",
id:"_girl"
}]
}, {
fieldLabel: '用户地址',
name: 'use_address',
blankText: '用户地址不能为空!',
maxLength: '25',
maxLengthText: '最大不超过25个字符!',
allowBlank: false
}, {
fieldLabel: '考试科目',
name: 'sco_subject',
blankText: '考试科目不能为空!',
maxLength: '25',
maxLengthText: '最大不超过25个字符!',
allowBlank: false
}, {
fieldLabel: '考试分数',
name: 'sco_score',
blankText: '考试分数不能为空!',
allowBlank: false,
regex: /^[1-9]\d*|0$/,
regexText: '必须为有效的整数!'
}
]
})
var window_page_edit =new Ext.Window({
title: '学生各科成绩更新',
modal: true,
width: 300,
height: 300,
resizable: false,
plain: true,
iconCls: 'page_edit',
bodyStyle: 'padding:20px;',
buttonAlign: 'center',
closeAction: 'hide',
items: formpanel_page_edit,
listeners: {
"show": function() {
row = Ext.getCmp("grid").getSelectionModel().getSelections();
formpanel_page_edit.getForm().reset();
formpanel_page_edit.getForm().loadRecord(row[0]);
}
},
buttons: [{
text: '保存',
iconCls: 'page_save',
handler: Update
}, {
text: '重置',
iconCls: 'arrow_undo',
handler: function() {
formpanel_page_edit.getForm().loadRecord(row[0]);
}
}]
});
function Update() {
if (!formpanel_page_edit.getForm().isValid()) return;
formpanel_page_edit.getForm().submit({
waitMsg: '正在提交数据',
waitTitle: '提示',
url: 'Handler1.ashx?parm=Update',
method: 'get',
success: function(form, action) {
var row_number = Ext.getCmp("grid").getSelectionModel().last;
var row = Ext.getCmp("grid").getSelectionModel().getSelections();
var sex ="男";
if (Ext.getCmp("_girl").checked ==true)
sex ="女";
var p =new TopicRecord({//创建一个记录源
use_id: formpanel_page_edit.getForm().findField('use_id').getValue(),
use_name: formpanel_page_edit.getForm().findField('use_name').getValue(),
use_sex: sex,
use_address: formpanel_page_edit.getForm().findField('use_address').getValue(),
sco_subject: formpanel_page_edit.getForm().findField('sco_subject').getValue(),
sco_score: formpanel_page_edit.getForm().findField('sco_score').getValue()
});
store.remove(row);
store.insert(row_number, p);
Ext.getCmp("grid").getSelectionModel().selectRow(row_number);
Ext.Msg.alert('提示', '保存成功');
},
failure: function(form, action) {
Ext.Msg.alert('错误', '<font color=red>失败：'+ action.result.errors.info +'</font>');
}
});
}
/**********/
/* 删除 */
/*********/
function showResult(btn) {
if (btn =='yes') {
Ext.Msg.wait("请等候", "删除中", "操作进行中......");
var row = Ext.getCmp("grid").getSelectionModel().getSelections();
var jsonData ="";
for (var i =0, len = row.length; i < len; i++) {
var ss = row[i].get("use_id");
if (i ==0)
jsonData = jsonData + ss; //这样处理是为了删除的Lambda语句方便
else
jsonData = jsonData +","+ ss; //这样处理是为了删除的Lambda语句方便
}
//alert(jsonData);
var conn =new Ext.data.Connection();
conn.request({
url: "Handler1.ashx?parm=Delete", //请注意引用的路径
params: { use_id: jsonData },
method: 'post',
scope: this,
callback: function(options, success, response) {
if (success) {
Ext.MessageBox.alert("提示", "所选记录成功删除！");
var row = Ext.getCmp("grid").getSelectionModel().getSelections();
store.remove(row);
}
else
{ Ext.MessageBox.alert("提示", "所选记录删除失败！"); }
}
})
}
};
Ext.onReady(function() {
Ext.QuickTips.init();
store =new Ext.data.JsonStore({
root: 'data',
totalProperty: 'totalCount',
url: 'Handler1.ashx?parm=Get',
fields: [
{ name: 'use_id', type: 'int' },
{ name: 'use_name' },
{ name: 'use_sex' },
{ name: 'use_address' },
{ name: 'sco_subject' },
{ name: 'sco_score' }
]
});
var grid =new Ext.grid.GridPanel({
frame: true,
title: '学生各科成绩表',
stripeRows: true, //斑马线
store: store,
id: 'grid',
applyTo: 'app_grid',
trackMouseOver: true,
height: 300,
width: 500,
loadMask: { msg: '正在加载数据，请稍侯……' },
viewConfig: {
forceFit: true
},
columns: [
new Ext.grid.RowNumberer(), //行号
new Ext.grid.CheckboxSelectionModel({ singleSelect: true }),
{ header: '<font size=2>用户帐户</font>', dataIndex: 'use_id', sortable: true },
{ header: '<font size=2>用户姓名</font>', dataIndex: 'use_name', sortable: true },
{ header: '<font size=2>用户性别</font>', dataIndex: 'use_sex', sortable: true },
{ header: '<font size=2>用户地址</font>', dataIndex: 'use_address', sortable: true },
{ header: '<font size=2>考试科目</font>', dataIndex: 'sco_subject', sortable: true },
{ header: '<font size=2>考试分数</font>', dataIndex: 'sco_score', sortable: true }
],
tbar: new Ext.Toolbar([
'-',
{
text: '<font size=2>增加数据</font>',
iconCls: 'add',
handler: function() {
window_add.show();
}
},
'-',
{
text: '<font size=2>删除数据</font>',
iconCls: 'delete',
handler: function() {
var s = grid.getSelectionModel().getSelections();
if (s.length ==0) {//判断有没有选中行
Ext.Msg.alert('提示', '你还没有选择要操作的记录!');
} elseif (s.length >1) {
Ext.Msg.alert('提示', '不能删除多个操作记录!');
} else {
Ext.MessageBox.confirm('提示', '您确认要删除当前记录吗?', showResult);
};
}
},
'-',
{
text: '<font size=2>更新数据</font>',
iconCls: 'page_edit',
handler: function() {
var s = grid.getSelectionModel().getSelections();
if (s.length ==0) {//判断有没有选中行
Ext.Msg.alert('提示', '你还没有选择要操作的记录!');
} elseif (s.length >1) {
Ext.Msg.alert('提示', '不能更新多个操作记录!');
} else {
window_page_edit.show();
};
}
},
'-'
]),
bbar: new Ext.PagingToolbar({//分页
pageSize: _limit,
store: store,
displayInfo: true, //非要为true，不然不会显示下面的分页按钮
displayMsg: '<font size=2>第 {0} 条到 {1} 条，一共 {2} 条记录</font>',
emptyMsg: "没有记录"
})
})
store.load({ params: { start: _start, limit: _limit} });
})
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>
<!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
CREATEPROCEDURE pro_Insert_values
@inp_use_idint,
@inp_use_namenvarchar(20),
@inp_use_sexnvarchar(2),
@inp_use_addressnvarchar(50),
@inp_sco_subjectnvarchar(20),
@inp_sco_scoresmallint,
@out_messagenvarchar(2) output
AS
BEGINTRAN
SET@out_message=''
BEGIN TRY
INSERTINTO tb_user VALUES(@inp_use_id,@inp_use_name,@inp_use_sex,@inp_use_address)
IF@@rowcount=0
SET@out_message='0'
INSERTINTO 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
ROLLBACKTRAN A
ELSE
if@out_message=''SET@out_message='1'
COMMITTRAN A
go
/* 插入 */
/********************/
--drop PROCEDURE pro_Insert_values
CREATEPROCEDURE pro_Insert_values
@inp_use_idint,
@inp_use_namenvarchar(20),
@inp_use_sexnvarchar(2),
@inp_use_addressnvarchar(50),
@inp_sco_subjectnvarchar(20),
@inp_sco_scoresmallint,
@out_messagenvarchar(2) output
AS
BEGINTRAN
SET@out_message=''
BEGIN TRY
INSERTINTO tb_user VALUES(@inp_use_id,@inp_use_name,@inp_use_sex,@inp_use_address)
IF@@rowcount=0
SET@out_message='0'
INSERTINTO 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
ROLLBACKTRAN A
ELSE
if@out_message=''SET@out_message='1'
COMMITTRAN A
go
9.3 进行后台分页有利于数据加载 和 单位时间内数据传送的合理性(凑成Json的字符串进行数据传送)
publicstring 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;
}
{
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的储存过程更有利于 程序的正常情况下的执行
publicstring 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}";
}
elseif (tem_out_message =="-1")
{
return"{ success: false, errors:{info: '输入类型不匹配'} }";
}
else
{
return"{ success: false, errors:{info: '插入失败'} }";
}
}
{
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}";
}
elseif (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 下载
https://files.cnblogs.com/yongfeng/ExtJs_ASP_Insert_Delete_Modify_Update.rar
