當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

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

發布時間：2025/3/21 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 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 數據庫文件

代碼

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);
}
}
}

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;
}
}
}
}

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} });
})

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
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;
}

　　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: '插入失敗'} }";
}
}

　　9.5 相當資料幫助

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

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

10 下載

http://files.cnblogs.com/yongfeng/ExtJs_ASP_Insert_Delete_Modify_Update.rar

from:?http://www.cnblogs.com/yongfeng/archive/2010/07/29/1787458.html

總結

以上是生活随笔為你收集整理的ExtJs、ASP.net运用Linq to SQL与SQL储存过程交互的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： C#远程访问linux(ubuntu)或
下一篇： eclipse配置Struts2、Hib