linq学习笔记(2) -- 调用存储过程进行查询,删除,更新,新增

本来linq是可以调用Sql语句来进行数据库的四步操作的,但是我对Sql语句写在类里面,非常反感,所以不打算写这个体会了,调用Sql语句的方法可以参看webabcd步步为营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 的存储过程,代码如下:

 1---------------------------------------------------------------------------
 2--Procedure Name: [dbo].[selCustomers]
 3--Note               : get Customers
 4--Created By       : Tom
 5--Created Date    : 2008-5-28    
 6---------------------------------------------------------------------------
 7CREATE PROCEDURE [dbo].[selCustomers]
 8(
 9    @ncCoustomerID NVARCHAR(5= NULL,    
10    @nvcCountry NVARCHAR(15= NULL    
11)
12    
13AS
14    IF @ncCoustomerID IS NULL OR @ncCoustomerID = ''
15        SET @ncCoustomerID = NULL
16
17    IF @nvcCountry IS NULL OR @nvcCountry = ''
18        SET @nvcCountry = NULL
19
20    SELECT * 
21    FROM CUSTOMERS 
22    WHERE CUSTOMERID LIKE ISNULL(@ncCoustomerID'%'
23    AND    COUNTRY LIKE ISNULL(@nvcCountry'%')
24RETURN


 1--------------------------------------------------------------------------
 2--Procedure Name : [dbo].[delCustomers]
 3--Note                : delete Customer
 4--Created By        : Tom
 5--Created Date     : 2008-5-28    
 6--------------------------------------------------------------------------
 7CREATE PROCEDURE [dbo].[delCustomers]
 8(
 9    @ncCustomerID NCHAR(5)
10)
11
12AS
13    DELETE CUSTOMERS 
14    WHERE CUSTOMERID = @ncCUSTOMERID
15RETURN


 1---------------------------------------------------------------------------
 2--Procedure Name : [dbo].[updCustomers]
 3--Note                : modify Customer
 4--Created By        : Tom
 5--Created Date     : 2008-5-28    
 6---------------------------------------------------------------------------
 7CREATE PROCEDURE [dbo].[updCustomers]
 8(
 9    @ncCustomerID NCHAR(5),
10    @nvcCompanyName NVARCHAR(40),     
11    @nvcAddress NVARCHAR(60),
12    @intReturnValue INT OUTPUT
13)
14    
15AS
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    
32RETURN
33


 1---------------------------------------------------------------------------
 2--Procedure Name : [dbo].[insCustomers]
 3--Note                : add Customer
 4--Created By        : Tom
 5--Created Date     : 2008-5-28    
 6---------------------------------------------------------------------------
 7CREATE 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)
22AS
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
55RETURN
56

4). 打开Visual Studio2008的服务器资源管理器,展开Northwind数据库的存储过程目录,将上叙四个存储过程依次拖入到打开的Northwind.dbml右侧方法区,查看引入的四个存储过程的属性,修改其Name名为"GetCustomers","DeleteCustomers","UpdateCustomers","InsertCustomers",如下图所示:

5). 新建一个页面:LinqBySP.aspx,页面代码(只列出form部分)如下:

 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                    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
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>

后台代码,如下:

  1public partial class LinqBySP : System.Web.UI.Page
  2    {
  3        /// <summary>
  4        /// Linq to Sql 类:Norwind DataContext 初始化
  5        /// </summary>

  6        NorthwindDataContext northwind = new NorthwindDataContext();        
  7        
  8        /// <summary>
  9        /// 页面初始化
 10        /// </summary>
 11        /// <param name="sender"></param>
 12        /// <param name="e"></param>

 13        protected void Page_Load(object sender, EventArgs e)
 14        {
 15            if (!IsPostBack)
 16            {
 17                BindDDL();
 18                BindGrid();
 19            }

 20        }

 21
 22        /// <summary>
 23        /// 绑定DropDownList
 24        /// </summary>

 25        private void BindDDL()
 26        {
 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
 33            drpCustomerID.Items.Insert(0"");
 34        }

 35
 36        /// <summary>
 37        /// 绑定GridView
 38        /// </summary>

 39        private void BindGrid()
 40        {
 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
 50        private IEnumerable<Customers> getCustomers(string customerID, string country)
 51        {
 52            var c = northwind.GetCustomers(customerID, country);
 53            return (IEnumerable<Customers>)c;
 54        }

 55
 56        /// <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        {
 63            BindGrid();
 64        }

 65
 66        /// <summary>
 67        /// 撤销当前查询条件,页面初始化
 68        /// </summary>
 69        /// <param name="sender"></param>
 70        /// <param name="e"></param>

 71        protected void btnCancel_Click(object sender, EventArgs e)
 72        {
 73            //初始化当前查询条件
 74            drpCustomerID.SelectedValue = string.Empty;
 75            txtCountry.Text = string.Empty;
 76
 77            //重新绑定GridView
 78            BindGrid(); 
 79        }
        
 80
 81        /// <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        {
 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
 92            //声明返回值
 93            int? returnValue = null;
 94
 95            //获取新增的Customers实体的数据
 96            customerID = txtCustomerID.Text.Trim();
 97            companyname = txtCompanyName.Text.Trim();
 98
 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
107        GridView 事件              
187    }


好了,到这里linq第二步,调用存储过程的四步基本操作完成,thx~
后记:在引入存储过程到dbml里面的时候,GetCustomer属性的【Return Type】一定要指明是"Customers",否则它会使用默认属性,有可能会发生问题

请使用指令“using System.Collections.Generic”。

posted @ 2008-10-14 17:06  KiNg.JiOnG  阅读(1011)  评论(0编辑  收藏  举报
查看博客访问人数(点击):