|
4.实现CLR存储过程
SQL Server 2005最优雅的功能之一是集成.NET CLR。集成CLR的SQL Server在多个重要的方面扩展了SQL Server功能。利用这种集成可创建数据库对象（例如存储过程），用户定义函数，以及使用现代面向对象语言（例如VB.NET和C#）创建触发器。为达成本实例目的，将说明使用C#创建存储过程的方法。
首先，在Visual Studio 2005中，以Visual Studio 2005作为编程语言创建一个新的名为AdventureWorksDatabaseObjects的SQL Server项目。由于创建的是数据库项目，所以需要关联数据源与项目。在创建项目的同时，Visual Studio将要求开发人员要么选择现有数据库引用，要么添加新数据库引用。选择AdventureWorks作为数据库。一旦创建项目，则在项目菜单中选择添加存储过程选项。在添加新项对话框中，输入StoredProcedures.cs，接着单击添加按钮。在创建类之后，可根据以下内容修改类中代码。
示例1：实现CLR存储过程
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductCategories()
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
string sqlCommand = "Select ProductCategoryID, Name, rowguid, " +
" ModifiedDate from Production.ProductCategory";
SqlCommand command = new SqlCommand(sqlCommand, connection);
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
}
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductSubcategories(int productCategoryID)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
string sqlCommand = "Select ProductSubcategoryID, ProductCategoryID, Name," + "rowguid, ModifiedDate from Production.ProductSubcategory " + "Where ProductCategoryID = " + productCategoryID;
SqlCommand command = new SqlCommand(sqlCommand, connection);
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
}
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProducts(int productSubcategoryID)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
string sqlCommand = "Select ProductID, Name, ProductNumber,MakeFlag, " + "FinishedGoodsFlag, Color, SafetyStockLevel, ReorderPoint,StandardCost, " + "ListPrice, Size, SizeUnitMeasureCode, WeightUnITMeasureCode," + "Weight,DaysToManufacture, ProductLine,Class, Style, " + "ProductSubcategoryID, ProductModelID,SellStartDate,SellEndDate," + "DiscontinuedDate, rowguid, ModifiedDate from Production.Product " + "Where ProductSubcategoryID = " + productSubcategoryID.ToString();
SqlCommand command = new SqlCommand(sqlCommand, connection);
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
}
}
};
|
上一页 [1] [2] [3] [4] 下一页 |
