演练:仅使用存储过程 (Visual Basic)Walkthrough: Using Only Stored Procedures (Visual Basic)

本演练提供了通过仅使用存储过程来访问数据的 LINQ to SQLLINQ to SQL 基本端对端方案。This walkthrough provides a basic end-to-end LINQ to SQLLINQ to SQL scenario for accessing data by using stored procedures only. 数据库管理员经常使用此方法来限制数据存储的访问方式。This approach is often used by database administrators to limit how the datastore is accessed.

备注

您还可以在 LINQ to SQLLINQ to SQL 应用程序中使用存储过程来重写默认行为,尤其是 CreateUpdateDelete 进程的默认行为。You can also use stored procedures in LINQ to SQLLINQ to SQL applications to override default behavior, especially for Create, Update, and Delete processes. 有关详细信息,请参阅自定义插入、更新和删除操作For more information, see Customizing Insert, Update, and Delete Operations.

出于本演练的目的,您将使用已映射到 Northwind 示例数据库中的存储过程的两个方法:CustOrdersDetail 和 CustOrderHist。For purposes of this walkthrough, you will use two methods that have been mapped to stored procedures in the Northwind sample database: CustOrdersDetail and CustOrderHist. 当你运行 SqlMetal 命令行工具来生成 Visual Basic 文件时,将发生该映射。The mapping occurs when you run the SqlMetal command-line tool to generate a Visual Basic file. 有关更多信息,请参见本演练后面的“先决条件”一节。For more information, see the Prerequisites section later in this walkthrough.

本演练并不依赖于对象关系设计器。This walkthrough does not rely on the Object Relational Designer. 使用 Visual Studio 的开发人员还可以使用 O/R 设计器来实现存储过程功能。Developers using Visual Studio can also use the O/R Designer to implement stored procedure functionality. 请参阅Visual Studio 中的 LINQ to SQL 工具See LINQ to SQL Tools in Visual Studio.

备注

以下说明中的某些 Visual Studio 用户界面元素在计算机上出现的名称或位置可能会不同。Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. 这些元素取决于你所使用的 Visual Studio 版本和你所使用的设置。The Visual Studio edition that you have and the settings that you use determine these elements. 有关详细信息,请参阅个性化设置 IDEFor more information, see Personalizing the IDE.

本演练是使用 Visual Basic 开发设置编写的。This walkthrough was written by using Visual Basic Development Settings.

系统必备Prerequisites

本演练需要如下内容:This walkthrough requires the following:

  • 本演练使用专用文件夹(“c:\linqtest3”)来保存文件。This walkthrough uses a dedicated folder ("c:\linqtest3") to hold files. 请在开始本演练前创建此文件夹。Create this folder before you begin the walkthrough.

  • Northwind 示例数据库。The Northwind sample database.

    如果您的开发计算机上没有此数据库,您可以从 Microsoft 下载网站下载它。If you do not have this database on your development computer, you can download it from the Microsoft download site. 有关说明,请参阅下载示例数据库For instructions, see Downloading Sample Databases. 下载此数据库后,请将 northwnd.mdf 文件复制到 c:\linqtest3 文件夹。After you have downloaded the database, copy the northwnd.mdf file to the c:\linqtest3 folder.

  • 从 Northwind 数据库生成的 Visual Basic 代码文件。A Visual Basic code file generated from the Northwind database.

    本演练是通过使用 SqlMetal 工具以及如下命令行编写的:This walkthrough was written by using the SqlMetal tool with the following command line:

    sqlmetal /code:"c:\linqtest3\northwind.vb" /language:vb "c:\linqtest3\northwnd.mdf" /sprocs /functions /pluralizesqlmetal /code:"c:\linqtest3\northwind.vb" /language:vb "c:\linqtest3\northwnd.mdf" /sprocs /functions /pluralize

    有关详细信息,请参阅 SqlMetal.exe(代码生成工具)For more information, see SqlMetal.exe (Code Generation Tool).

概述Overview

本演练由六项主要任务组成:This walkthrough consists of six main tasks:

  • 在 Visual Studio 中设置解决方案。LINQ to SQLLINQ to SQLSetting up the LINQ to SQLLINQ to SQL solution in Visual Studio.

  • 将 System.Data.Linq 程序集添加到项目中。Adding the System.Data.Linq assembly to the project.

  • 向项目添加数据库代码文件。Adding the database code file to the project.

  • 创建与数据库的连接。Creating a connection to the database.

  • 设置用户界面。Setting up the user interface.

  • 运行和测试应用程序。Running and testing the application.

创建 LINQ to SQL 解决方案Creating a LINQ to SQL Solution

在第一个任务中,您将创建一个 Visual Studio 解决方案,其中包含生成和运行LINQ to SQLLINQ to SQL项目所必需的引用。In this first task, you create a Visual Studio solution that contains the necessary references to build and run a LINQ to SQLLINQ to SQL project.

创建 LINQ to SQL 解决方案To create a LINQ to SQL solution

  1. 在 Visual Studio 的 "文件" 菜单上,单击 "新建项目"。On the Visual Studio File menu, click New Project.

  2. “新建项目” 对话框中的 “项目类型” 窗格中,展开 “Visual Basic” ,然后单击 “Windows”In the Project types pane in the New Project dialog box, expand Visual Basic, and then click Windows.

  3. “模板” 窗格中,单击 “Windows 窗体应用程序”In the Templates pane, click Windows Forms Application.

  4. 在 "名称" 框中,键入SprocOnlyAppIn the Name box, type SprocOnlyApp.

  5. 单击 “确定”Click OK.

    Windows 窗体设计器即会打开。The Windows Forms Designer opens.

添加 LINQ to SQL 程序集引用Adding the LINQ to SQL Assembly Reference

LINQ to SQLLINQ to SQL 程序集未包含在标准的 Windows 窗体应用程序模板中。The LINQ to SQLLINQ to SQL assembly is not included in the standard Windows Forms Application template. 您将需要按照以下步骤中的说明自行添加此程序集:You will have to add the assembly yourself, as explained in the following steps:

添加 System.Data.Linq.dllTo add System.Data.Linq.dll

  1. 解决方案资源管理器中,单击 "显示所有文件"。In Solution Explorer, click Show All Files.

  2. 解决方案资源管理器中,右键单击 "引用",然后单击 "添加引用"。In Solution Explorer, right-click References, and then click Add Reference.

  3. 在 "添加引用" 对话框中,单击 " .net",单击 "system.web" 程序集,然后单击 "确定"In the Add Reference dialog box, click .NET, click the System.Data.Linq assembly, and then click OK.

    此程序集即被添加到项目中。The assembly is added to the project.

将 Northwind 代码文件添加到项目Adding the Northwind Code File to the Project

此步骤假定你已使用 SqlMetal 工具从 Northwind 示例数据库生成代码文件。This step assumes that you have used the SqlMetal tool to generate a code file from the Northwind sample database. 有关更多信息,请参见本演练前面部分的“先决条件”一节。For more information, see the Prerequisites section earlier in this walkthrough.

将 northwind 代码文件添加到项目To add the northwind code file to the project

  1. 在 "项目" 菜单上,单击 "添加现有项"。On the Project menu, click Add Existing Item.

  2. 在 "添加现有项" 对话框中,转到 "c:\linqtest3\northwind.vb",然后单击 "添加"。In the Add Existing Item dialog box, move to c:\linqtest3\northwind.vb, and then click Add.

    northwind.vb 文件即被添加到项目中。The northwind.vb file is added to the project.

创建数据库连接Creating a Database Connection

在此步骤中,您要定义与 Northwind 示例数据库的连接。In this step, you define the connection to the Northwind sample database. 本演练使用“c:\linqtest3\northwnd.mdf”作为路径。This walkthrough uses "c:\linqtest3\northwnd.mdf" as the path.

创建数据库连接To create the database connection

  1. 解决方案资源管理器中,右键单击 " Form1",然后单击 "查看代码"。In Solution Explorer, right-click Form1.vb, and then click View Code.

    Class Form1 将显示在代码编辑器中。Class Form1 appears in the code editor.

  2. Form1 代码块中键入如下代码:Type the following code into the Form1 code block:

    Dim db As New Northwnd("c:\linqtest3\northwnd.mdf")
    

设置用户界面Setting up the User Interface

在此任务中,你要创建用户界面,供用户执行存储过程以访问数据库中的数据之用。In this task you create an interface so that users can execute stored procedures to access data in the database. 在您按照本演练开发的应用程序中,用户可以只使用嵌入在此应用程序中的存储过程来访问数据库中的数据。In the application that you are developing with this walkthrough, users can access data in the database only by using the stored procedures embedded in the application.

设置用户界面To set up the user interface

  1. 返回 Windows 窗体设计器("Form1 [Design] ")。Return to the Windows Forms Designer (Form1.vb[Design]).

  2. “视图” 菜单上单击 “工具箱”On the View menu, click Toolbox.

    工具箱即会打开。The toolbox opens.

    备注

    单击 "自动隐藏" 图钉,使工具箱保持打开状态。Click the AutoHide pushpin to keep the toolbox open while you perform the remaining steps in this section.

  3. 将两个按钮、两个文本框和两个标签从工具箱拖到Form1上。Drag two buttons, two text boxes, and two labels from the toolbox onto Form1.

    按照附图排列这些控件。Arrange the controls as in the accompanying illustration. 展开 " Form1 ",使控件更容易。Expand Form1 so that the controls fit easily.

  4. 右键单击 " Label1",然后单击 "属性"。Right-click Label1, and then click Properties.

  5. 将 " Text " 属性从 " Label1 " 更改为 "输入订单 id: "。Change the Text property from Label1 to Enter OrderID:.

  6. Label2相同的方式,将 " Text " 属性从 " Label2 " 更改为 "输入 CustomerID: "。In the same way for Label2, change the Text property from Label2 to Enter CustomerID:.

  7. 同样,将Button1Text属性更改为 "订单详细信息"。In the same way, change the Text property for Button1 to Order Details.

  8. Button2Text属性更改为Order HistoryChange the Text property for Button2 to Order History.

    将这些按钮控件加宽,以使所有文本均可见。Widen the button controls so that all the text is visible.

处理按钮单击To handle button clicks

  1. 双击 " Form1 " 上的 " Button1 订单详细信息",创建事件处理程序并打开代码编辑器。Double-click Order Details on Form1 to create the Button1 event handler and open the code editor.

  2. 将如下代码键入到 Button1 处理程序中:Type the following code into the Button1 handler:

    ' Declare a variable to hold the contents of
    ' TextBox1 as an argument for the stored
    ' procedure.
    Dim parm As String = TextBox1.Text
    
    ' Declare a variable to hold the results returned
    ' by the stored procedure.
    Dim custQuery = db.CustOrdersDetail(parm)
    
    ' Clear the message box of previous results.
    Dim msg As String = ""
    Dim response As MsgBoxResult
    
    ' Execute the stored procedure and store the results.
    For Each custOrdersDetail As CustOrdersDetailResult In custQuery
        msg &= custOrdersDetail.ProductName & vbCrLf
    Next
    
    ' Display the results.
    If msg = "" Then
        msg = "No results."
    End If
    response = MsgBox(msg)
    
    ' Clear the variables before continuing.
    parm = ""
    TextBox1.Text = ""
    
  3. 现在,双击 Form1 上的Button2 ,创建Button2事件处理程序并打开代码编辑器。Now double-click Button2 on Form1 to create the Button2 event handler and open the code editor.

  4. 将如下代码键入到 Button2 处理程序中:Type the following code into the Button2 handler:

    ' Comments in the code for Button2 are the same
    ' as for Button1.
    Dim parm As String = TextBox2.Text
    
    Dim custQuery2 = db.CustOrderHist(parm)
    Dim msg As String = ""
    Dim response As MsgBoxResult
    
    For Each custOrdHist As CustOrderHistResult In custQuery2
        msg &= custOrdHist.ProductName & vbCrLf
    Next
    
    If msg = "" Then
        msg = "No results."
    End If
    
    response = MsgBox(msg)
    parm = ""
    TextBox2.Text = ""
    

测试应用程序Testing the Application

现在,可以开始测试您的应用程序了。Now it is time to test your application. 请注意,您可对数据存储施加的影响仅限于这两个存储过程能够执行的操作。Note that your contact with the datastore is limited to whatever actions the two stored procedures can take. 这些操作是要根据您输入的所有 orderID 返回相应订单包括的产品,或根据您输入的所有 CustomerID 返回相应客户的产品订购历史记录。Those actions are to return the products included for any orderID you enter, or to return a history of products ordered for any CustomerID you enter.

测试应用程序To test the application

  1. 按 F5 开始调试。Press F5 to start debugging.

    此时将显示 Form1。Form1 appears.

  2. 在 "输入订单 id " 框中,键入10249 ,然后单击 "订单详细信息"。In the Enter OrderID box, type 10249 and then click Order Details.

    随即会显示一个消息框,其中列出了 10249 号订单中所包括的产品。A message box lists the products included in order 10249.

    单击 "确定" 关闭消息框。Click OK to close the message box.

  3. 在 "输入 CustomerID " 框中ALFKI键入,然后单击 "订单历史记录"。In the Enter CustomerID box, type ALFKI, and then click Order History.

    随即会显示一个消息框,其中列出了 ALFKI 客户的订单历史记录。A message box lists the order history for customer ALFKI.

    单击 "确定" 关闭消息框。Click OK to close the message box.

  4. 在 "输入订单 id " 框123中,键入,然后单击 "订单详细信息"。In the Enter OrderID box, type 123, and then click Order Details.

    随即会显示一个消息框,其中显示“无结果”。A message box displays "No results."

    单击 "确定" 关闭消息框。Click OK to close the message box.

  5. 在 "调试" 菜单上单击 "停止调试"。On the Debug menu, click Stop debugging.

    调试会话即会关闭。The debug session closes.

  6. 如果已完成试验,则可以单击 "文件" 菜单上的 "关闭项目",并在出现提示时保存项目。If you have finished experimenting, you can click Close Project on the File menu, and save your project when you are prompted.

后续步骤Next Steps

您可以通过做一些更改来增强此项目的功能。You can enhance this project by making some changes. 例如,您可以在列表框中列出可用的存储过程,供用户选择要执行哪些过程。For example, you could list available stored procedures in a list box and have the user select which procedures to execute. 您还可以将报告的输出以流的方式传输到文本文件。You could also stream the output of the reports to a text file.

请参阅See also