本来linq是可以调用Sql语句来进行数据库的四步操作的,但是我对Sql语句写在类里面,非常反感,所以不打算写这个体会了,调用Sql语句的方法可以参看
的
步步为营VS 2008 + .NET 3.5(9) - DLINQ(LINQ to SQL)之执行SQL语句的添加、查询、更新和删除 这篇。
本篇,关注的是linq调用存储过程来完成数据库的四步操作(
查询,删除,更新,新增)。
数据库仍然是Northwind,数据源是表Customers
1). 首先创建一个网站,添加Linq to Sql类,名称为Northwind.dbml;
2). 打开Visual Studio的服务器资源管理器,选择数据连接-添加连接,引入Northwind数据库,展开,将Customers表拖到Northwind.dbml,这样就完成了创建Customers对象;
3). 创建
查询,删除,更新,新增 Customers 的存储过程,代码如下:
查询 Customers
1
---------------------------------------------------------------------------
2
--Procedure Name: [dbo].[selCustomers]
3
--Note : get Customers
4
--Created By : Tom
5
--Created Date : 2008-5-28
6
---------------------------------------------------------------------------
7
CREATE PROCEDURE [dbo].[selCustomers]
8
(
9
@ncCoustomerID NVARCHAR(5) = NULL,
10
@nvcCountry NVARCHAR(15) = NULL
11
)
12
13
AS
14
IF @ncCoustomerID IS NULL OR @ncCoustomerID = ''
15
SET @ncCoustomerID = NULL
16![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
IF @nvcCountry IS NULL OR @nvcCountry = ''
18
SET @nvcCountry = NULL
19![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
SELECT *
21
FROM CUSTOMERS
22
WHERE CUSTOMERID LIKE ISNULL(@ncCoustomerID, '%')
23
AND COUNTRY LIKE ISNULL(@nvcCountry, '%')
24
RETURN
删除 Customers
1
--------------------------------------------------------------------------
2
--Procedure Name : [dbo].[delCustomers]
3
--Note : delete Customer
4
--Created By : Tom
5
--Created Date : 2008-5-28
6
--------------------------------------------------------------------------
7
CREATE PROCEDURE [dbo].[delCustomers]
8
(
9
@ncCustomerID NCHAR(5)
10
)
11![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
AS
13
DELETE CUSTOMERS
14
WHERE CUSTOMERID = @ncCUSTOMERID
15
RETURN
更新 Customer
1
---------------------------------------------------------------------------
2
--Procedure Name : [dbo].[updCustomers]
3
--Note : modify Customer
4
--Created By : Tom
5
--Created Date : 2008-5-28
6
---------------------------------------------------------------------------
7
CREATE PROCEDURE [dbo].[updCustomers]
8
(
9
@ncCustomerID NCHAR(5),
10
@nvcCompanyName NVARCHAR(40),
11
@nvcAddress NVARCHAR(60),
12
@intReturnValue INT OUTPUT
13
)
14
15
AS
16
--不返回受影响的行
17
SET NOCOUNT ON
18
19
--更新指定的Customers
20
UPDATE CUSTOMERS
21
SET COMPANYNAME = @nvcCompanyName,
22
ADDRESS = @nvcAddress
23
WHERE CUSTOMERID = @ncCustomerID
24
25
IF @@ERROR <> 0
26
BEGIN
27
--如果更新发生异常,返回-1
28
SET @intReturnValue = -1
29
RETURN
30
END
31
32
RETURN
33
新增 Customer
1
---------------------------------------------------------------------------
2
--Procedure Name : [dbo].[insCustomers]
3
--Note : add Customer
4
--Created By : Tom
5
--Created Date : 2008-5-28
6
---------------------------------------------------------------------------
7
CREATE PROCEDURE [dbo].[insCustomers]
8
(
9
@ncCustomerID NCHAR(5),
10
@nvcCompanyName NVARCHAR(40),
11
@nvcContactName NVARCHAR(30) = NULL,
12
@nvcContactTitle NVARCHAR(30) = NULL,
13
@nvcAddress NVARCHAR(60) = NULL,
14
@nvcCity NVARCHAR(15) = NULL,
15
@nvcRegion NVARCHAR(15) = NULL,
16
@nvcPostalCode NVARCHAR(10) = NULL,
17
@nvcCountry NVARCHAR(15) = NULL,
18
@nvcPhone NVARCHAR(24) = NULL,
19
@nvcFax NVARCHAR(24) = NULL,
20
@intReturnValue INT OUTPUT
21
)
22
AS
23
--新增Customers行
24
INSERT INTO [dbo].[CUSTOMERS]
25
([CUSTOMERID],
26
[COMPANYNAME],
27
[CONTACTNAME],
28
[CONTACTTITLE],
29
[ADDRESS],
30
[CITY],
31
[REGION],
32
[POSTALCODE],
33
[COUNTRY],
34
[PHONE],
35
[FAX])
36
VALUES
37
(@ncCustomerID,
38
@nvcCompanyName,
39
@nvcContactName,
40
@nvcContactTitle,
41
@nvcAddress,
42
@nvcCity,
43
@nvcRegion,
44
@nvcPostalCode,
45
@nvcCountry,
46
@nvcPhone,
47
@nvcFax)
48
49
IF @@ERROR <> 0
50
BEGIN
51
--如果更新发生异常,返回-1
52
SET @intReturnValue = -1
53
RETURN
54
END
55
RETURN
56
4). 打开Visual Studio2008的服务器资源管理器,展开Northwind数据库的存储过程目录,将上叙四个存储过程依次拖入到打开的Northwind.dbml右侧方法区,查看引入的四个存储过程的属性,修改其Name名为"GetCustomers","DeleteCustomers","UpdateCustomers","InsertCustomers",如下图所示:
5). 新建一个页面:LinqBySP.aspx,页面代码(只列出form部分)如下:
LinqBySP
1
<form id="form1" runat="server">
2
<div>
3
<h4>
4
查询</h4>
5
<table>
6
<tr>
7
<td>
8
Customer ID:
9
</td>
10
<td>
11
<asp:DropDownList ID="drpCustomerID" runat="server">
12
</asp:DropDownList>
13
</td>
14
<td>
15
Country:
16
</td>
17
<td>
18
<asp:TextBox ID="txtCountry" runat="server">
19
</asp:TextBox>
20
</td>
21
</tr>
22
<tr>
23
<td colspan="2">
24
<asp:Button ID="btnSelect" runat="server" Text="Search" OnClick="btnSelect_Click" />
25
26
<asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" />
27
28
</td>
29
</tr>
30
</table>
31
</div>
32
<div>
33
<h4>
34
新增</h4>
35
<table>
36
<tr>
37
<td>
38
Customer ID:
39
</td>
40
<td>
41
<asp:TextBox ID="txtCustomerID" runat="server">
42
</asp:TextBox>
43
</td>
44
</tr>
45
<tr>
46
<td>
47
Company Name:
48
</td>
49
<td>
50
<asp:TextBox ID="txtCompanyName" runat="server">
51
</asp:TextBox>
52
</td>
53
</tr>
54
<tr>
55
<td colspan="2">
56
<asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="btnAdd_Click" />
57
</td>
58
</tr>
59
</table>
60
</div>
61
<div>
62
<h4>
63
显示 更新 删除</h4>
64
<asp:GridView ID="gvSelLinQ" runat="server" AllowPaging="True" OnPageIndexChanging="gvSelLinq_PageIndexChanging"
65
OnRowDeleting="gvSelLinQ_RowDeleting" OnRowUpdating="gvSelLinQ_RowUpdating" BackColor="White"
66
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" OnRowEditing="gvSelLinQ_RowEditing"
67
OnRowCancelingEdit="gvSelLinQ_RowCancelingEdit" DataKeyNames="CustomerID">
68
<PagerSettings Position="TopAndBottom" />
69
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
70
<RowStyle BackColor="White" ForeColor="#003399" />
71
<Columns>
72
<asp:CommandField ShowEditButton="True" />
73
<asp:CommandField ShowDeleteButton="True" />
74
</Columns>
75
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
76
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
77
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
78
</asp:GridView>
79
</div>
80
</form>
后台代码,如下:
LinqBySP 主要代码
1
public partial class LinqBySP : System.Web.UI.Page
2![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
3![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
4
/// Linq to Sql 类:Norwind DataContext 初始化
5
/// </summary>
6
NorthwindDataContext northwind = new NorthwindDataContext();
7
8![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
9
/// 页面初始化
10
/// </summary>
11
/// <param name="sender"></param>
12
/// <param name="e"></param>
13
protected void Page_Load(object sender, EventArgs e)
14![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
15
if (!IsPostBack)
16![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
17
BindDDL();
18
BindGrid();
19
}
20
}
21![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
23
/// 绑定DropDownList
24
/// </summary>
25
private void BindDDL()
26![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
27
string customerID = string.Empty, country = string.Empty;
28
var c = getCustomers(customerID, country).Select(t => t.CustomerID);
29
30
drpCustomerID.DataSource = c;
31
drpCustomerID.DataBind();
32![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
drpCustomerID.Items.Insert(0, "");
34
}
35![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
37
/// 绑定GridView
38
/// </summary>
39
private void BindGrid()
40![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
41
string customerID = string.Empty, country = string.Empty;
42
customerID = drpCustomerID.SelectedValue.Trim();
43
country = txtCountry.Text.Trim();
44
var c = getCustomers(customerID, country);
45
46
gvSelLinQ.DataSource = c;
47
gvSelLinQ.DataBind();
48
}
49![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
50
private IEnumerable<Customers> getCustomers(string customerID, string country)
51![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
52
var c = northwind.GetCustomers(customerID, country);
53
return (IEnumerable<Customers>)c;
54
}
55![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
56![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
57
/// 根据指定的查询条件获取Customers实体集对象指定的实体
58
/// </summary>
59
/// <param name="sender"></param>
60
/// <param name="e"></param>
61
protected void btnSelect_Click(object sender, EventArgs e)
62![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
63
BindGrid();
64
}
65![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
66![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
67
/// 撤销当前查询条件,页面初始化
68
/// </summary>
69
/// <param name="sender"></param>
70
/// <param name="e"></param>
71
protected void btnCancel_Click(object sender, EventArgs e)
72![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
73
//初始化当前查询条件
74
drpCustomerID.SelectedValue = string.Empty;
75
txtCountry.Text = string.Empty;
76![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
77
//重新绑定GridView
78
BindGrid();
79
}
80![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
81![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
82
/// 新增Customers
83
/// </summary>
84
/// <param name="sender"></param>
85
/// <param name="e"></param>
86
protected void btnAdd_Click(object sender, EventArgs e)
87![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
88
//声明变量
89
string customerID = string.Empty, companyname = string.Empty, contactname = string.Empty, contacttitle = string.Empty, address = string.Empty, city = string.Empty;
90
string region = string.Empty, postalcode = string.Empty, country = string.Empty, phone = string.Empty, fax = string.Empty;
91![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
92
//声明返回值
93
int? returnValue = null;
94![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
95
//获取新增的Customers实体的数据
96
customerID = txtCustomerID.Text.Trim();
97
companyname = txtCompanyName.Text.Trim();
98![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
99
//调用Linq to sql类的InsertCustomers方法来新增Customers实体
100
northwind.InsertCustomers(customerID, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, fax, ref returnValue);
101
102
//重新绑定DropDownList和GridView
103
BindDDL();
104
BindGrid();
105
}
106![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
107![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
GridView 事件#region GridView 事件
108![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
109
/// GridView分页
110
/// </summary>
111
/// <param name="sender"></param>
112
/// <param name="e"></param>
113
protected void gvSelLinq_PageIndexChanging(object sender, GridViewPageEventArgs e)
114![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
115
gvSelLinQ.PageIndex = e.NewPageIndex;
116
BindGrid();
117
}
118![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
119![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
120
/// 编辑 GridView
121
/// </summary>
122
/// <param name="sender"></param>
123
/// <param name="e"></param>
124
protected void gvSelLinQ_RowEditing(object sender, GridViewEditEventArgs e)
125![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
126
gvSelLinQ.EditIndex = e.NewEditIndex;
127
BindGrid();
128
}
129![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
130![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
131
/// 编辑 GridView 撤销
132
/// </summary>
133
/// <param name="sender"></param>
134
/// <param name="e"></param>
135
protected void gvSelLinQ_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
136![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
137
gvSelLinQ.EditIndex = -1;
138
BindGrid();
139
}
140![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
141![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
142
/// 更新 Customers
143
/// </summary>
144
/// <param name="sender"></param>
145
/// <param name="e"></param>
146
protected void gvSelLinQ_RowUpdating(object sender, GridViewUpdateEventArgs e)
147![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
148
//变量
149
string customerID = string.Empty, companyname = string.Empty, address = string.Empty;
150
151
//返回值
152
int? returnValue = null; //为了数据操作异常而定义,这里我就不写了,不然又会写一大堆
153![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
154
//变量赋值
155
customerID = gvSelLinQ.DataKeys[e.RowIndex].Value.ToString().Trim();
156
companyname = ((TextBox)gvSelLinQ.Rows[e.RowIndex].Cells[3].Controls[0]).Text.Trim();
157
address = ((TextBox)gvSelLinQ.Rows[e.RowIndex].Cells[6].Controls[0]).Text.Trim();
158![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
159
//调用Norwind DataContext类的UpdateCustomers方法,更新指定的Customers对象
160
northwind.UpdateCustomers(customerID, companyname, address, ref returnValue);
161![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
162
//重新绑定GridView
163
gvSelLinQ.EditIndex = -1;
164
BindGrid();
165
}
166![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
167![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
168
/// 删除 Customers
169
/// </summary>
170
/// <param name="sender"></param>
171
/// <param name="e"></param>
172
protected void gvSelLinQ_RowDeleting(object sender, GridViewDeleteEventArgs e)
173![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
174
string customerID = string.Empty;
175
176
//获取GridView的主键索引
177
customerID = gvSelLinQ.DataKeys[e.RowIndex].Value.ToString().Trim();
178![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
179
//调用Norwind DataContext类的DeleteCustomers方法,删除指定的Customers对象
180
northwind.DeleteCustomers(customerID);
181![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
182
//重新绑定DropDownList和GridView
183
BindDDL();
184
BindGrid();
185
}
186
#endregion
187
}
好了,到这里linq第二步,调用存储过程的四步基本操作完成,thx~
后记:在引入存储过程到dbml里面的时候,GetCustomer属性的【Return Type】一定要指明是"Customers",
否则它会使用默认属性,有可能会发生问题