一、简单实例 1.首先来一个最简单的存储过程吧 CREATE PROCEDURE dbo.testProcedure_AX AS select userID from USERS order by userid desc 注:dbo.testProcedure_AX是你创建的存储过程名,可以改为:AXzhz等,别跟关键字冲突就行了,AS下面就是一条SQL语句. 2.如何在ASP.NET中调用这个存储过程? public static string GetCustomerCName(ref ArrayList arrayCName,ref ArrayList arrayID) { SqlConnection con=ADConnection.createConnection(); SqlCommand cmd=new SqlCommand("testProcedure_AX",con); cmd.CommandType=CommandType.StoredProcedure; con.Open(); try { SqlDataReader dr=cmd.ExecuteReader(); while(dr.Read()) { if(dr[0].ToString()=="") { arrayCName.Add(dr[1].ToString()); } } con.Close(); return "OK!"; } catch(Exception ex) { con.Close(); return ex.ToString(); } } 注:其实就是把以前 SqlCommand cmd=new SqlCommand("select userID from USERS order by userid desc",con); 中的SQL语句替换为存储过程名,再把cmd的类型标注为CommandType.StoredProcedure(存储过程) 3.再来个带参数的存储过程吧. CREATE PROCEDURE dbo.AXzhz /* 这里写注释 */ @startDate varchar(16), @endDate varchar(16) AS select id from table_AX where commentDateTime>@startDate and commentDateTime<@endDate order by contentownerid DESC 注:@startDate varchar(16)是声明@startDate 这个变量,多个变量名间用【,】隔开.后面的SQL就可以使用这个变量了. 4.如何在ASP.NET中调用这个带参数的存储过程? public static string GetCustomerCNameCount(string startDate,string endDate,ref DataSet ds) { SqlConnection con=ADConnection.createConnection(); //-----------------------注意这一段-------------------------------------------------------------------------- SqlDataAdapter da=new SqlDataAdapter("AXzhz",con); para0=new SqlParameter("@startDate",startDate); para1=new SqlParameter("@endDate",endDate); da.SelectCommand.Parameters.Add(para0); da.SelectCommand.Parameters.Add(para1); da.SelectCommand.CommandType=CommandType.StoredProcedure; //----------------------------------------------------------------------------------------------------------- try { con.Open(); da.Fill(ds); con.Close(); return "OK"; } catch(Exception ex) { return ex.ToString(); } } 注:把命令的参数添加进去,就OK了. 5.如何查看SQL命令执行成功了没有. CREATE PROCEDURE dbo.AXzhz /* @parameter1 用户名 @parameter2 新密码 */ @password nvarchar(20), @userName nvarchar(20) AS declare @err0 int update WL_user set password=@password where UserName=@userName set @err0=@@error select @err0 as err0 注:先声明一个整型变量@err0,再给其赋值为@@error(这个是系统自动给出的语句是否执行成功,0为成功,其它为失败),最后通过select把它选择出来. 6.如何从后台获得这个执行成功与否的值? 下面这段代码可以告诉你答案: public static string GetCustomerCName() { SqlConnection con=ADConnection.createConnection(); SqlCommand cmd=new SqlCommand("AXzhz",con); cmd.CommandType=CommandType.StoredProcedure; para0=new SqlParameter("@startDate","2006-9-10"); para1=new SqlParameter("@endDate","2006-9-20"); da.SelectCommand.Parameters.Add(para0); da.SelectCommand.Parameters.Add(para1); con.Open(); try { Int32 re=(int32)cmd.ExecuteScalar(); con.Close(); if (re==0) return "OK!"; else return "false"; } catch(Exception ex) { con.Close(); return ex.ToString(); } } 7.如何根据传入的参数判断执行哪条SQL语句? ALTER PROCEDURE dbo.selectCustomerCNameCount @customerID int AS if @customerID=-1 begin select contentownerid ,userCName,count(*) as countAll from view_usercomment group by contentownerid,userCName order by contentownerid DESC end else begin select contentownerid ,userCName,count(*) as countAll from view_usercomment where contentownerid=@customerID group by contentownerid,userCName order by contentownerid DESC end 二、C#中使用带返回值的存储过程 例如在向数据库添加新数据时,需要检测是否有重复 本例介绍如何把这个检测的过程放在存储过程中,并用程序调用检测的结果做出反应。 存储过程如下: CREATE PROCEDURE DInstitute_Insert @InstituteNO nvarchar(6),@InstituteName nvarchar(40) AS declare @return int,@count int if(ltrim(rtrim(@InstituteName))='' or ltrim(rtrim(@InstituteNO))='') select @return=3--返回3表示提交的数据有空值 else begin select @count=count(1) from DInstitute where InstituteNO=@InstituteNO if(@count>0) select @return=1--返回1表示编号有重复 else begin insert into DInstitute (InstituteNO,InstituteName) values (@InstituteNO,@InstituteName) if(@@error>0) select @return=2--返回2表示数据操作错误 else select @return=0--返回0表示数据操作成功 end end return @return GO 其中DInstitute 是一个学院信息表。只有InstituteNO(学院编号)、InstituteName(学院名称)两个字段。 在C#中调用本存储过程的代码如下: //执行插入操作 SqlCommand com1 = new SqlCommand("DInstitute_Insert", DBcon); if (com1.Connection.State == ConnectionState.Closed) com1.Connection.Open(); com1.CommandType = CommandType.StoredProcedure; com1.Parameters.Add(new SqlParameter("@InstituteNO",SqlDbType.NVarChar,6)); com1.Parameters.Add(new SqlParameter("@InstituteName", SqlDbType.NVarChar, 40)); com1.Parameters.Add(new SqlParameter("@return", SqlDbType.Int)); com1.Parameters["@return"].Direction = ParameterDirection.ReturnValue; com1.Parameters["@InstituteNO"].Value = t_NO.Text; com1.Parameters["@InstituteName"].Value = t_name.Text; try { com1.ExecuteScalar(); } catch(SqlException ee) { DB.msgbox("操作失败!"+ee.Message.ToString()); return; } finally { com1.Connection.Close(); } string temp = com1.Parameters["@return"].Value.ToString(); //返回0表示数据操作成功 //返回1表示编号有重复 //返回2表示数据操作错误 //返回3表示提交的数据有空值 switch (temp) { case "0": DB.msgbox("添加成功!"); break; case "1": DB.msgbox("编号有重复!"); break; case "2": DB.msgbox("数据操作错误!"); break; case "3": DB.msgbox("提交的数据有空值!"); break; } Binding(); //刷新datagrid 三、SqlServer存储过程的事务处理 方法一: --测试的表 create table tb( id int not null constraint PK_sys_zj_fielddict primary key ,aa int) --事务处理 begin tran insert into tb values(1,1) if @@error<>0 goto lb_rollback insert into tb values(1,1) if @@error<>0 goto lb_rollback insert into tb values(2,1) if @@error<>0 goto lb_rollback insert into tb values(2,1) if @@error<>0 goto lb_rollback insert into tb values(3,1) if @@error<>0 goto lb_rollback lb_commit: commit tran goto lb_ok lb_rollback: rollback tran --显示结果 lb_ok: select * from tb drop table tb www.2cto.com 方法二: --创建测试表 create table tb(id int not null constraint PK_sys_zj_fielddict primary key ,aa int) --设置选项 SET XACT_ABORT on --事务处理 begin tran insert into tb values(1,1) insert into tb values(1,1) insert into tb values(2,1) commit tran --显示结果 /*--------注意 如果这样写的话,后面的语句不会被执行,如果要执行后面的语句,要在这句后面加上GO,仅查询分析分析器支持,所以如果是在存储过程中,要保证commit tran后面没有其他语句,否则出错时,其他语句不会被执行 -----------*/ select * from tb drop table tb 四、.Net中使用事务处理 SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"); myConnection.Open(); SqlTransaction myTrans = myConnection.BeginTransaction(); //使用New新生成一个事务 SqlCommand myCommand = new SqlCommand(); myCommand.Transaction = myTrans; try { myCommand.CommandText = "Update Address set location='23 rain street' where userid='0001'"; myCommand.ExecuteNonQuery(); myCommand.CommandText = "Update table2 set dd='23 rain street' where userid='0001'"; myCommand.ExecuteNonQuery(); myTrans.Commit(); Console.WriteLine("Record is udated."); } catch(Exception e) { myTrans.Rollback(); Console.WriteLine(e.ToString()); Console.WriteLine("Sorry, Record can not be updated."); } finally { myConnection.Close(); } 说明:在SqlServer中,每条Sql语句都作为一个事务来执行,所以无论在存储过程,还是在.net代码中使用,执行单条Sql语句没有必要使用事务处理。