存储过程与事务处理

存储过程

语法：Create Procedure sp_name

        @param1 type1,

        @param2 type2,

       　……

　　　　@paramn typen

     AS

       SQL语句

*可以用返回码表示存储过和的执行状态。它类似于输出参数。但有三个区别1、返回码只能返回SQL数值；2、返回码不能在存储过程内部声明3、它使用transact-SQL的Return语句回；当使用具有返回码的程储过程时，在Asp.NET程序中第一个建并添加的参数必须用于保存返回码的参数。

CREATE PROCEDURE dbo.sample

@graSchool varchar(50) OUTPUT,

@id int

AS

      select @graSchool=graSchool from ch6_4   where id=@id

     IF @graSchool IS NULL

           RETURN 0

     ELSE

           RETURN 1

GO

 

protected void Page_Load(Object src, EventArgs E)

{    cn = new SqlConnection("Data Source=(local); database=column; Trusted_Connection=yes");

     SqlCommand cm = new SqlCommand("sample",cn);   cm.CommandType = CommandType.StoredProcedure;

    

     cm.Parameters.Add("ReturnValue", SqlDbType.Int);

     cm.Parameters["ReturnValue"].Direction = ParameterDirection.ReturnValue;

    

     cm.Parameters.Add("@graSchool", SqlDbType.VarChar, 50);

     cm.Parameters["@graSchool"].Direction = ParameterDirection.Output;

    

     cm.Parameters.Add("@id", SqlDbType.Int);        cm.Parameters["@id"].Value = "1";  

 

     cn.Open();

     SqlDataReader dr = cm.ExecuteReader();   

     Message.Text = "<b>用户编号（输入参数）&nbsp; :&nbsp; </b>" + cm.Parameters["@id"].Value + "<br>";

     Message.Text += "<b>是否为空（返回码）&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :&nbsp; </b>" + cm.Parameters["ReturnValue"].Value + "<br>";

     Message.Text += "<b>毕业院校（输出参数）&nbsp; :&nbsp; </b>" + cm.Parameters["@graSchool"].Value + "<br>";

    

     cn.Close();

}

 

 

一段SQL事务处理多条SQL语句片段

protected static int ExecuteSqls(string[] strSQLs)

         {

              SqlConnection myCn = new SqlConnection(strConn);   SqlCommand myCmd = new SqlCommand();          

              int j=strSQLs.Length;

              try

              {             myCn.Open();                         }

              catch(System.Data.SqlClient.SqlException e)

              {        throw new Exception(e.Message);           }

              SqlTransaction myTrans = myCn.BeginTransaction();

              try

              {                                                 

                   myCmd.Connection = myCn;                  myCmd.Transaction = myTrans;

                   foreach(string str in strSQLs)

                   {             myCmd.CommandText = str;         myCmd.ExecuteNonQuery();                  }

                   myTrans.Commit();   //执行完字符串数据组中的所有命令后提交

                   return 0;

              }

              catch(System.Data.SqlClient.SqlException e)

              {        myTrans.Rollback();         throw new Exception(e.Message);      }

              finally

              {    myCmd.Dispose();                 myCn.Close();          }

         }