在基于 LINQ 的 n 层体系结构中实现 CLR 存储过程
4.85/5 (7投票s)
CLR 存储过程可以有效地实现在 n 层体系结构中。
目录
引言
本文是我上一篇关于“CLR 存储过程及其分步创建”文章的延续。在我之前的文章中，我解释了 CLR 存储过程、它们的优缺点以及如何实现它们。CLR 存储过程功能强大，在执行复杂逻辑、密集字符串操作或字符串处理、加密、访问系统资源和文件管理等方面能提供更好的结果。在 n 层体系结构中开发任何项目都有不同的体验，并各有其优缺点。微软推出了 LINQ（Language Integrated Query），即语言集成查询，随 .NET Framework 3.0 一起发布。LINQ 可以通过多种方式实现，例如，LINQ to SQL、LINQ to Objects、LINQ to DataSet 和 LINQ to XML 等。几年前，我在公司实现了一个简单的 n 层体系结构。它适用于 Web 和桌面应用程序。它是一个集中的 n 层体系结构，即所有对象都驻留在目标系统的同一位置。我不敢说这是最好的，但我实现了这个体系结构，因为我希望它至少能带来以下好处：
- 它必须安全、易于实现且易于部署。
- 它必须是基于层的，至少是 3 层，这样如果更改任何层的代码，只需要部署该层的 DLL。
- Web 和桌面应用程序的单一体系结构，具有标准的应用程序性能
- 必须能够并行开发，即一个开发人员可以处理数据库，另一个可以处理业务逻辑，还有一个可以处理用户界面，等等。
- 没有内联 SQL 查询。
- 它不能过于复杂，以便新开发人员无需长时间培训即可轻松上手开发，并且由于所有项目的基本开发体系结构保持不变，现有开发人员可以轻松地在任何项目之间迁移。
定义应用程序的层
现在我来解释体系结构的各个层。任何好的体系结构都应至少包含三个重要层：
- 用户界面或表示层
- 业务逻辑层
- 数据层
我将要解释的体系结构有五层。任何超过三层的体系结构都称为 n 层体系结构。下图将对此进行说明。
下图显示了我们应用程序中的体系结构。它包含以下四层（见图）和一个数据库，共五层。
层的角色
我将简要解释上述每个层。
- 第一层：表示层：这一层负责用户与应用程序之间的各种活动。所有用户界面相关的逻辑都将驻留在这一层。这意味着 Web 窗体或 WinForms 将驻留在其中。这一层不能直接访问数据库或数据访问层。因此，所有进出表示层的进出数据都只能通过业务逻辑层。此层将添加业务逻辑层的引用。为此层将有一个单独的项目。
- 第二层：业务逻辑层：业务逻辑层将执行应用程序的所有业务逻辑。业务逻辑包括两部分：核心业务逻辑和数据访问逻辑。数据访问逻辑被分离到不同的层，以提供更多的安全性和数据封装。为了更好地管理，此层将为数据库中的每个表创建一个单独的类。该类将有各种方法。业务逻辑将应用于来自表示层和数据访问层的数据，或将发送到这些层的数据。此层将添加数据访问层的引用。为此层将有一个单独的项目。
- 第三层：数据访问层：这一层是业务逻辑的一部分，但与核心业务逻辑分离。应用程序与数据库之间所有与数据相关的操作将在该层执行。该层将使用 LINQ 创建。这里将创建一个 LINQ to SQL 类，并将所有需要的数据库表或存储过程拖放到此处。此层的手动编码将最少。为此层将有一个单独的项目。
- 第四层：CLR 存储过程层：这一层将负责定义各种 CLR 存储过程。为了更好地管理，此层将为数据库中的每个表创建一个单独的类。这一层不直接与表示层、业务逻辑层或数据访问层相关联。CLR 存储过程将部署到 SQL Server。为此层将有一个单独的项目。要了解 CLR 存储过程是什么以及如何创建它，请参阅我的文章“CLR 存储过程及其分步创建”。
- 第五层：数据库：这是核心数据和用于维护和访问它的对象。例如，SQL Server 数据库。核心数据是各种表中的数据，而用于维护和访问这些数据的对象是各种表、存储过程、CLR 存储过程、视图和函数等。
物理上存在五层，但逻辑上只有四层，因为 CLR 存储过程的程序集将在部署时成为数据库的一部分。
向一个项目添加另一个项目的引用非常简单。只需右键单击项目>>单击“添加引用”。将显示一个对话框。选择“项目”选项卡，然后在项目列表中选择适当的项目。单击“确定”按钮。我们不能在项目之间添加循环引用，例如，在本例中，表示层引用业务层，业务层引用数据访问层。现在，我们不能向数据访问层添加表示层的引用，因为这会创建循环引用。
在基于 LINQ 的 n 层体系结构中逐步实现 CLR 存储过程
让我们在基于 LINQ 的 n 层体系结构中实现一个 CLR 存储过程。我已将用于创建数据库、创建表、向表中插入虚拟记录等的所有 SQL 语句列在随本文提供的脚本中。
应用程序开发规范
- IDE：Visual Studio 2008
- .NET Framework：3.5 SP 1
- 语言：C# 3.0
- 数据库：Microsoft SQL Server 2005 Express 版
- 操作系统：Windows XP SP 2
创建体系结构的步骤
1. 创建表示层：打开 Microsoft Visual Studio 2008 >> 创建新项目。将打开一个对话框。在左侧的“项目类型”面板中，选择“Visual C#”>>“Windows”。在右侧的“模板”面板中，选择“Windows 窗体应用程序”。为项目、解决方案命名，并选择一个位置来保存此解决方案。此项目将是我们的表示层。下图将对此进行说明。
项目将创建一个名为“Form1”的默认窗体。将其重命名为“Demo”。按照下图所示设计它。有一个用于标题的Label控件，一个DataGridView控件，以及一个GroupBox控件，用于放置各种按钮，即“获取数据”、“插入随机记录”、“删除选定记录”、“清除网格”和“退出”。下图将对此进行说明。
以下是 Demo 窗体的代码。
using System;
using System.Windows.Forms;
using BusinessLayer;
namespace ClrInNTierPresentationLayer
{
/// <summary>
/// Represents demo form for the application
/// </summary>
public partial class frmDemo : Form
{
#region Constructor
/// <summary>
/// Constructor of the application
/// </summary>
public frmDemo()
{
InitializeComponent();
}
#endregion
#region Event Handler
#region Buttons
/// <summary>
/// Handles Click event of the button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnGetData_Click(object sender, EventArgs e)
{
try
{
FillDataGridView();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Handles Click event of the button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnInsertRandomRecord_Click(object sender, EventArgs e)
{
try
{
CustomerSalesInformation.CustomerSalesInformationInsertRandomRecord();
FillDataGridView();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Deletes selected record from the database
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDeleteSelectedRecord_Click(object sender, EventArgs e)
{
try
{
if (dgvData.Rows.Count > 0)
{
if (MessageBox.Show(
"Are you sure to delete selected record?",
"Confirmation", MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == DialogResult.Yes)
{
CustomerSalesInformation.CustomerSalesInformationDelete(
Convert.ToDouble(
dgvData.CurrentRow.Cells["ID"].Value.ToString()));
FillDataGridView();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Handles Click event of the button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnClearGrid_Click(object sender, EventArgs e)
{
try
{
dgvData.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Handles Click event of the button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExit_Click(object sender, EventArgs e)
{
try
{
Application.Exit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion
#endregion
#region Private Methods
/// <summary>
/// Fills the data in the DataGridView
/// </summary>
private void FillDataGridView()
{
try
{
//Filling the grid with data
dgvData.DataSource =
CustomerSalesInformation.CustomerSalesInformationGet();
//Formating the columns of the grid
dgvData.Columns[0].AutoSizeMode =
DataGridViewAutoSizeColumnMode.AllCells;
dgvData.Columns[1].AutoSizeMode =
DataGridViewAutoSizeColumnMode.Fill;
dgvData.Columns[2].AutoSizeMode =
DataGridViewAutoSizeColumnMode.AllCells;
dgvData.Columns[2].DefaultCellStyle.Alignment =
DataGridViewContentAlignment.TopRight;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion
}
}
2. 创建 CLR 存储过程层：右键单击解决方案，单击“添加”>>“新建项目”。将打开一个对话框。在左侧的“项目类型”面板中，选择“数据库项目”>>“Microsoft SQL Server”>>“SQL CLR”。在右侧的“模板”面板中，选择“SQL Server 项目”。为项目命名，并选择保存此解决方案的位置。此项目将是我们的 CLR 存储过程层。请按照我在我的文章“CLR 存储过程及其分步创建”中解释的步骤，在此项目中添加 CLR 存储过程。下图将对此进行说明。
右键单击项目>>选择“添加”>>“添加新项”。将显示一个对话框，如下面的图像所示。在左侧的“类别”面板中选择“Visual C# 项”。在右侧的“模板”面板中选择“存储过程”。为它命名。
以下是 CustomerInformationClrSP 类的代码。
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
/// <summary>
/// Represents the CustomerInformationClrSP
// module for the CLR Stored Procedure Layer
/// </summary>
public partial class CustomerInformationClrSP
{
#region Pubic Methods
/// <summary>
/// Gets data from Customers Sales Information table
/// </summary>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomerSalesInformationGet()
{
#region Variable Declaration
SqlConnection sqlConnection = new SqlConnection();
SqlCommand sqlCommand = new SqlCommand();
#endregion
try
{
// Establishing connection with SQL
sqlConnection = new SqlConnection("context connection=true");
sqlConnection.Open();
//Creating command
sqlCommand = new SqlCommand("SELECT * FROM CustomerSalesInformation",
sqlConnection);
//Executing command
SqlContext.Pipe.ExecuteAndSend(sqlCommand);
}
catch (Exception)
{
throw;
}
finally
{
sqlConnection.Close();
sqlConnection.Dispose();
sqlCommand.Dispose();
}
}
/// <summary>
/// Inserts random Customer Record in to the Customers Sales Information table
/// </summary>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomerSalesInformationInsertRandomRecord()
{
#region Variable Declaration
SqlConnection sqlConnection = new SqlConnection();
SqlCommand sqlCommand = new SqlCommand();
#endregion
try
{
// Establishing connection with SQL
sqlConnection = new SqlConnection("context connection=true");
sqlConnection.Open();
//Creating command: Inserting new name of random customer based on GUID
sqlCommand = new SqlCommand("INSERT INTO [dbo].[CustomerSalesInformation]" +
"([Name], [Sales]) VALUES ('" + Guid.NewGuid().ToString() +
"', 50000)", sqlConnection);
//Executing command
SqlContext.Pipe.ExecuteAndSend(sqlCommand);
}
catch (Exception)
{
throw;
}
finally
{
sqlConnection.Close();
sqlConnection.Dispose();
sqlCommand.Dispose();
}
}
/// <summary>
/// Deletes record from CustomerSalesInformation table for the supplied ID
/// </summary>
/// <param name="CustomerId">Customer ID for which
/// record is to be deleted</param>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomerSalesInformationDelete(double CustomerId)
{
#region Variable Declaration
SqlConnection sqlConnection = new SqlConnection();
SqlCommand sqlCommand = new SqlCommand();
#endregion
try
{
// Establishing connection with SQL
sqlConnection = new SqlConnection("context connection=true");
sqlConnection.Open();
//Creating command: Deleting record for the customer ID
sqlCommand = new SqlCommand("DELETE FROM [DbForClrDemo]." +
"[dbo].[CustomerSalesInformation] WHERE ID = " +
CustomerId, sqlConnection);
//Executing command
SqlContext.Pipe.ExecuteAndSend(sqlCommand);
}
catch (Exception)
{
throw;
}
finally
{
sqlConnection.Close();
sqlConnection.Dispose();
sqlCommand.Dispose();
}
}
#endregion
};
3. 创建业务层：右键单击解决方案，单击“添加”>>“新建项目”。将打开一个对话框。在左侧的“项目类型”面板中，选择“Visual C#”>>“Windows”。在右侧的“模板”面板中，选择“类库”。为项目命名，并选择一个位置来保存此解决方案。此项目将是我们的业务逻辑层。我已向此项目添加了两个类：
ConvertToDataTableCustomerSalesInformation
ConvertToDataTable 类具有将对象数组转换为 DataTable 或 DataSet 的各种方法。CustomerSalesInformation 类包含用于插入记录、删除记录和从数据库获取记录的各种方法。下图将对此进行说明。
以下是业务层 CustomerSalesInformation 类的代码。
using System;
using System.Data;
using System.Linq;
using DataAccessLayer;
namespace BusinessLayer
{
/// <summary>
/// Represents the ExecuteClr module for the Business Layer
/// </summary>
public class CustomerSalesInformation
{
#region Public Methods
/// <summary>
/// Gets records from Customer Sales Information table
/// </summary>
/// <returns>Datatable</returns>
public static DataTable CustomerSalesInformationGet()
{
try
{
object[] objData =
AccessData.Call.CustomerSalesInformationGet().
ToArray<CustomerSalesInformationGetResult>();
return ConvertToDataTable.ConvertToDatatable(objData);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Inserts random customer in to Customer Sales Information table
/// </summary>
public static void CustomerSalesInformationInsertRandomRecord()
{
try
{
AccessData.Call.CustomerSalesInformationInsertRandomRecord();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Deletes record for the supplied customer id
/// </summary>
/// <param name="CustomerID">Customer ID for which
/// record is to be deleted</param>
public static void CustomerSalesInformationDelete(double CustomerID)
{
try
{
AccessData.Call.CustomerSalesInformationDelete(CustomerID);
}
catch (Exception)
{
throw;
}
}
#endregion
}
}
4. 创建数据访问层：再次右键单击解决方案，单击“添加”>>“新建项目”。将打开一个对话框。在左侧的“项目类型”面板中，选择“Visual C#”>>“Windows”。在右侧的“模板”面板中，选择“类库”（与步骤 3 相同）。为项目命名，并选择一个位置来保存此解决方案。此项目将是我们的数据访问层。
右键单击“DataAccessLayer”项目，从菜单中选择“添加”>>“新建项”选项。将显示下图所示的对话框。在左侧的“类别”面板中选择“Visual C# 项”，在右侧的“模板”面板中选择“LINQ to SQL 类”。
5. 将存储过程添加到 DBML 文件：打开服务器资源管理器，然后单击服务器资源管理器窗口顶部中间的“连接到数据库”按钮。这将启动一个新的向导来连接到数据库。现在，拖放存储过程 CustomerSalesInformationDelete 和 CustomerSalesInformationInsertRandomRecord。不要立即拖放“CustomerSalesInformationGet”，因为我们需要对其进行更改。我添加了 AccessData 类来添加各种有用的方法和变量，供业务逻辑层使用。目前，我已声明了 LinqToSqlDataContext 的私有对象，并通过 Call 属性将其公开。下图将对此进行说明。请更改我在声明 LinqToSqlDataContext 对象时静态写入的连接字符串。
6. 添加存储过程“CustomerSalesInformationGet”：我建议在上一步中不要拖放 CustomerSalesInformationGet，因为它是一个不包含 SELECT 查询的存储过程。因此，如果您将其拖放到 .dbml 文件中，该存储过程方法的返回类型将是 int 而不是 ISingleResult，因此它不会产生我们期望从该存储过程获得的结果。您可以在 DBML 的 .cs 文件中看到这一点。我将要解释的解决方法不是标准方法，但它工作得很好。如果您发现任何更好的选项，请告诉我。以下是解决方法步骤。
- 步骤 1：转到查询分析器，使用以下 SQL 语句删除部署 CLR 存储过程时自动创建的存储过程
CustomerSalesInformationGet。
DROP PROCEDURE [dbo].[CustomerSalesInformationGet]
CREATE PROCEDURE [dbo].[CustomerSalesInformationGet]
AS
BEGIN
SELECT * from CustomerSalesInformation
END
CustomerSalesInformationGet 存储过程拖放到 .dbml 文件中。这将为该存储过程创建一个返回类型为 ISingleResult 的方法。您可以在 DBML 的 .cs 文件中验证这一点。这将按预期返回结果。DROP PROCEDURE [dbo].[CustomerSalesInformationGet]
7. 更改连接：在使用示例源代码时，您需要在两个地方更改连接。
- 在数据访问层的“AccessData.cs”文件中，我们声明了
LinqToSqlDataContext的对象。下图将对此进行说明。 - 在“CLRStoredProcedureLayer”项目中。在解决方案资源管理器中右键单击该项目，然后单击“属性”。将显示类似下图的屏幕。选择“数据库”选项卡，然后在“连接字符串”选项（其中有一个文本框和一个浏览按钮）处更改连接字符串。
private static LinqToSqlDALDataContext objLinqToSqlDALDataContext =
new LinqToSqlDALDataContext("Data Source=OM\\SQLEXPRESS;" +
"Initial Catalog=DbForClrDemo;User ID=sa;Password=airborne");
8. 保存并运行应用程序：保存整个解决方案。现在，您可以通过按 F5 来运行应用程序。将打开一个对话框，如下面的图像所示。单击“获取数据”按钮，它将从数据库检索记录并加载到网格中。“插入随机记录”将在表中插入一条随机记录，然后再次从数据库检索记录并加载到网格中。“删除选定记录”会删除选定的记录。“清除网格”按钮会清除网格中的记录。“退出”按钮将关闭并退出应用程序。以下是演示。
使用代码
- 下载源代码和 SQL 脚本的 Zip 文件并解压缩。
- 使用“Script to create Database and Table.sql”创建数据库和表。用于插入虚拟记录的 SQL 语句也在同一个文件中。
- 我们需要配置应用程序与您的数据库的连接。按照步骤 8 中的说明，在应用程序的两个地方进行连接更改。
- 运行演示。它现在应该可以顺利运行。
- 第一次点击“获取数据”按钮时，从数据库获取数据需要一点时间。现在点击“清除网格”按钮，然后再次点击“获取数据”按钮，您将体会到它的速度。此外，尝试连续多次点击“插入随机记录”按钮。每次点击它都会将记录插入数据库并填充网格，但请注意它的速度。
- 在 Web 应用程序中，这比 Windows 应用程序稍慢。
结论
CLR 存储过程可以有效地实现在 n 层体系结构中。
建议
请参阅我的另一篇文章“CLR 存储过程及其分步创建”。
要点
- 探索 LINQ 中的
IMultipleResult。
历史
- 2009 年 9 月 6 日：初次发布。