Microsoft . 技术之路...

—— 专注于微软技术, 分享是快乐的源泉......
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

ASP.NET2.0+SQL Server2005构建多层应用(2)

Posted on 2005-12-30 12:46  赣江源  阅读(111)  评论(0编辑  收藏  举报
使用SQL Server 2005 的CLR存储过程

  SQL Server 2005的一大新特性便是整合了.net 的CLR。整合了.net CLR的好处在于,可以很方便地使开发者可以使用自己熟悉的.net 语言来创建存储过程,触发器,自定义函数等。在本文中,将以C#来创建存储过程。为什么不用T-SQL来创建存储过程呢?因为T-SQL发展到目前,已经很长时间了,在某些场合有其局限性,比如T-SQL不是面向对象的,某些语法过于复杂等。而如果使用面向对象的.NET 语言来编写如存储过程等数据对象时,由于.net 语言强大的特性,因此能写出更健壮和更优秀的存储过程。注意,通过SQL Server 2005用.NET编写的存储过程,都是和用.NET语言编写一般应用的程序一样,都是managed code。此外,CLR 编程语言提供了 T-SQL 中所没有的丰富构造(例如数组和列表等)。与 T-SQL(它是一种解释语言)相比,CLR 编程语言之所以具有更好的性能,是因为托管代码是已编译的。对于涉及算术计算、字符串处理、条件逻辑等的操作,托管代码的性能可能要优于 T-SQL 一个数量级。在本文中,虽然可以用T-SQL来编写存储过程,但为了说明问题,还是以C#来写存储过程。步骤如下:

  首先,打开Visual Studio 2005 beta 2,选择c#语言,新建立一个database工程,命名为sqlproject1。此时,Visual Studio 2005 beta 2会询问你,要与什么数据库进行关联。由于我们采用的是pubs这个数据库,因此我们选择机器名是本地机器,设置好sql的验证方式,选择pubs数据库,就可以了。(注意,在SQL Server 2005中,pubs和northwind数据库不再是SQL Server 2005的自带数据库了,需要http://go.microsoft.com/fwlink/?LinkId=31995去下载)。接着,在工程建立完毕后,选择新增项目,选择store procedure存储过程,并以Authors.cs命名,再按确定,并输入以下代码:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public partial class StoredProcedures
{
 [Microsoft.SqlServer.Server.SqlProcedure]

 public static void GetAuthors()
 {
  SqlPipe sp = SqlContext.Pipe;
  using (SqlConnection conn = new SqlConnection("context connection=true"))
  {
   conn.Open();
   SqlCommand cmd = new SqlCommand();
   cmd.CommandType = CommandType.Text;
   cmd.Connection = conn;
   cmd.CommandText = "Select DatePart(second, GetDate()) " + " As timestamp,* from authors";
   SqlDataReader rdr = cmd.ExecuteReader();
   sp.Send(rdr);
  }
 }

 [SqlProcedure]

 public static void GetTitlesByAuthor(string authorID)
 {
  string sql = "select T.title, T.price, T.type, " + "T.pubdate from authors A" +
     " inner join titleauthor TA on A.au_id = TA.au_id " +
     " inner join titles T on TA.title_id = T.title_id " +
     " where A.au_id = ’" + @authorID + "’";
  using (SqlConnection conn = new SqlConnection("context connection=true"))
  {
   conn.Open();
   SqlPipe sp = SqlContext.Pipe;
   SqlCommand cmd = new SqlCommand();
   cmd.CommandType = CommandType.Text;
   cmd.Connection = conn;
   cmd.CommandText = sql;
   SqlParameter paramauthorID = new SqlParameter("@authorID", SqlDbType.VarChar, 11);
   paramauthorID.Direction = ParameterDirection.Input;
   paramauthorID.Value = authorID;
   cmd.Parameters.Add(paramauthorID);
   SqlDataReader rdr = cmd.ExecuteReader();
   sp.Send(rdr);
  }
 }
}

  让我们来看上面的代码。首先声明了Authors类,并且要引入System.Data.Sql;,System.Data.SqlTypes;,Microsoft.SqlServer.Server; System.Data.SqlClient等命名空间。其中,在Microsoft.SqlServer.Server命名空间中,有两个十分重要的类:

  ·SqlContext: 一个SqlContext类包含的方法可以取得一个数据库实例连接、命令,事务等。

  ·SqlPine: 用户实现发送查询结果和消息到客户端,和ADO.NET里面的Response类有很多相似之处。

  在Authors类中包含了两个静态方法,分别是GetAuthors和GetTitlesByAuthor。其中,GetAuthors方法返回pubs数据库中authors表的所有数据,而GetTitlesByAuthor,则返回指定作者的所写的图书。

  在GetAuthors方法中,首先通过调用sqlContext类的pipe属性引用SqlPine对象:

SqlPipe sp = SqlContext.Pipe;

  接着,使用SqlConnection对象连接到数据库。注意,在连接数据库的字符串中,使用"context=true",表示使用已经登陆到数据库的用户进行登陆:

using (SqlConnection conn = new SqlConnection("context connection=true"))
conn.Open();  

  再创建SqlCommand对象的实例,并且设置其属性:

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.CommandText = "Select DatePart(second, GetDate()) " + " As timestamp,* from authors";  

  通过调用SqlCommand对象的ExecuteReader方法来执行SQL语句。

SqlDataReader rdr = cmd.ExecuteReader();  

  最后,使用SqlPipe对象,将结果集返回 到客户端。这可以使用Send方法实现

sp.Send(rdr);  

  要注意的是,我们在创建存储过程后,必须对其进行部署。我们先选择builde菜单中的 build sqlproject1,就会编译我们刚才创建的类。当编译完工程后,就可以进行部署了。部署也十分简单,选择build菜单,然后选择Deploy sqlproject1,这样就会自动将刚写好的存储过程部署到SQL Server 2005中去。

  最后,我们还要在SQL Server management studio中,执行如下语句,以确保在SQL Server 中可以执行托管代码:

EXEC sp_configure ’clr enabled’, 1;
RECONFIGURE WITH OVERRIDE;
GO