存储过程可以通过SQL Server Management Studio创建,也可以使用.NET框架通过编程实现.SQL Server Management Studio创建存储过程比较方便,右击【对象资源管理器】中的相应的数据库,在下拉菜单中选择【可编程性】选项并选择【存储过程】选项。单击右键,选择【新建存储过程】选项,系统会自动创建一个新的标签(tab)窗口,以提供输入存储过程语句。
在tab窗口中输入存储过程,代码如下所示。
CREATE PROC myproc
(
@id int,
@title varchar(50) OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @newscount int
SELECT @title=mynews.title,@newscount=COUNT(mynews.id)
FROM mynews
WHERE (
id=@id)
GROUP BY mynews.title
RETURN @newscount
上述存储过程返回了数据库中新闻的标题内容。
“@id”表示新闻的id,@title表示新闻的标题,此存储过程将返回
“@title”的值,并且返回新闻的总数。在C#中同样可以使用编程实现存储过程的创建,示例代码如下所示。
string str = "CREATE PROC myproc" +
"(" +
"@id int," +
"@title varchar(50) OUTPUT" +
")" +
"AS" +
"SET NOCOUNT ON" +
"DECLARE @newscount int" +
"SELECT @title=mynews.title,@newscount=COUNT(mynews.id)" +
"FROM mynews" +
"WHERE (
id=@id)" +
"GROUP BY mynews.title" +
"RETURN @newscount";
SqlCommand cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery(); //使用cmd的ExecuteNonQuery方法创建存储过程
上述代码通过使用SqlCommand对象的ExecuteNonQuery()方法在数据库中创建了一个存储过程,该存储过程用于返回了数据库中新闻的标题内容。
调用存储过程
创建存储过程之后,可以在.NET应用程序中使用存储过程。存储过程可以看成是一个函数,可以对存储过程进行调用,传递参数,接受返回值。在调用存储过程前,首先要与数据库建立连接,示例代码如如下所示。
string str = "server='(local)';database='mytable';uid='sa';pwd='Sa'";
SqlConnection con = new SqlConnection(str);
con.Open(); //打开连接
建立与数据库连接后,需要使用Command对象使用存储过程,Command对象接受的两个参数分别为SQL语句和Connection对象,在使用存储过程时,其中表示SQL语句的参数可以直接编写为存储过程名,代码如下所示。
SqlCommand cmd = new SqlCommand("getdetail", con); //使用存储过程
默认情况下,Command对象的类型是SQL语句,必须将Command对象的CommandType属性设置为存储过程,系统才会调用存储过程,示例代码如下所示。
cmd.CommandType = CommandType.StoredProcedure; //设置Command对象的类型
设置执行类型后,需要为存储过程增加参数,示例代码如下所示。
SqlParameter spr; //表示执行一个存储过程
spr = cmd.Parameters.Add("@id", SqlDbType.Int); //增加参数id
spr = cmd.Parameters.Add("@title", SqlDbType.NChar,50); //增加参数title
spr.Direction = ParameterDirection.Output; //该参数是输出参数
spr = cmd.Parameters.Add("@count", SqlDbType.Int); //增加count参数
spr.Direction = ParameterDirection.ReturnValue; //该参数是返回值
cmd.Parameters["@id"].Value = 1; //为参数初始化
cmd.Parameters["@title"].Value = null; //为参数初始化
参数设置完毕后,执行ExecuteNonQuery方法能够执行存储过程,就相当于开始调用函数,示例代码如下所示。
cmd.ExecuteNonQuery(); //执行存储过程
当存储过程执行完毕后,能够获取参数和返回值,示例代码如下所示。
Label1.Text = cmd.Parameters["@count"].Value.ToString(); //获取返回值
使用SQL Server Management Studio同样能够执行存储过程,单击存储过程,单击右键,选择执行存储过程,系统会提示输入参数,如图9-4所示。输入相应的参数,单击确定,系统会执行存储过程并返回相应的值。
使用SQL Server Management Studio能够快速的创建和使用存储过程,同样,能够通过编程的方法实现存储过程的创建、参数的传递以及执行。存储过程的优点就在于速度比较快,能够控制过程、减少网络通信和模块化,熟练的使用存储过程能够提高应用程序的性能和复用性。