C#二十六 使用Ado.Net呼叫儲存過程

儲存過程是連線式訪問資料庫的一種延伸，主要是通過命令物件呼叫資料庫系統中的儲存過程來完成的。儲存過程可以帶引數，也可以不帶引數，可以返回結果頁可以沒有返回結果。儲存過程執行速度快、允許模組化程式設計並且提高系統安全性，所以是最常用的運算元據庫的技術。使用Ado.Net中的SqlCommand可以呼叫並執行 Sql Server資料庫的儲存過程。

 視訊課堂https://edu.csdn.net/course/play/7621

重點：

Ø      如何定義與使用儲存過程以及儲存過程的重要性

Ø      引數物件

Ø      呼叫有返回值的儲存過程

預習功課：

Ø      SqlCommand如何才能呼叫儲存過程

Ø      SqlParameter類及其使用規則

Ø      引數化物件的使用

Ø      如何才能呼叫有返回值的儲存過程

3.1           引數化物件

引數物件(Parameter)表示命令物件（Command）中的一個引數，我們前面用過的命令物件都是不帶引數的，我們也可以使用帶引數的命令物件，這將給程式帶來更大的靈活性。我們知道儲存過程中存在輸入輸出引數，所以在介紹C#呼叫儲存過程前，需要來看看系統為我們提供的引數引數物件到底有何用處以及如何使用。

測試表：Person

Create table Person

{

   psnNo Nchar(6) PrimaryKey,

   psnName Nvarchar(5) NotNull,

   psnSex  Nchar(1) not null,

   psnAge smallint notnull,

   psnAddress Nvarchar(50)not null

}

go

該表可建立於我們的測試資料庫Test下：

在向資料庫裡插入記錄，我們可以使用下面Sql語句：

Insert into Person values(＇001，＇心酸果凍＇，＇女＇，25，＇北京宣武區＇)

Insert into Person values(＇002，＇芬理希夢＇，＇女＇，24，＇上海浦東區＇)

很顯然這兩個語句非常相似，有沒有一種方法可以使我們避免這種無謂的重複呢？有，就是使用引數化Sql語句。在實現上面的插入操作時，可以使用引數化Sql語句將兩個Sql語句中不同的部分用引數來表示，然後在使用的時候給引數賦予一個具體的值即可，這樣就不用每次都將Sql語句重新寫一遍了。我們使用引數化Sql語句重寫了上面的插入操作如下：

//通過連線字串建立資料庫連線

SqlConnection cn=new SqlConnection(＂server=.;database=Test;uid=sa;pwd=123456＂);

try

{

  cn.Open();

//建立帶引數的Sql語句

  string sql=＂Insert into Personvalues(@Id,@Name,@Sex,@Age,@Address) ＂;

  SqlCommand cmd=newSqlCommand(sql,cn);

//建立引數並加入到Parameters集合裡

  cmd.Parameters.Add(＂@Id＂,SqlDbType.NChar,6);

  cmd.Parameters.Add(＂@Name＂,SqlDbType.Nvarchar,5);

  cmd.Parameters.Add(＂@Sex＂,SqlDbType.Nchar,1);

  cmd.Parameters.Add(＂@Age＂,SqlDbType.SmallInt);

  cmd.Parameters.Add(＂@Address＂,SqlDbType.NvarChar,50);

//設定引數的值，並執行插入

  cmd.Parameters[＂@Id＂].Value=＂004＂;

  cmd.Parameters[＂@Name＂].Value=＂帕瓦羅蒂＂;

  cmd.Parameters[＂@Sex＂].Value=＂男＂;

  cmd.Parameters[＂@Age＂].Value=＂32＂;

  cmd.Parameters[＂@Address＂].Value=＂東部部落＂;

  cmd.ExecuteNonQuery();

//設定帶引數的值，並執行插入

cmd.Parameters[＂@Id＂].Value=＂005＂;

cmd.Parameters[＂@Name＂].Value=＂天使毛毛＂;

cmd.Parameters[＂@Age＂].Value=＂18＂;

cmd.Parameters[＂@Address＂].Value=＂東部部落＂;

//此處設定的值可以為對應的Winform、Webform的文字框、下拉框等使用者輸入的值；

//如：ccmd.Parameters[＂@Id＂].Value=txtId.Text;

cmd.ExecuteNonQuery();

}

catch(SqlExecption ex)

{

  //資料庫出錯報錯資訊

}

finally

{

  cn.Close();

}

這段程式碼執行後資料庫裡會增加兩條記錄。這裡就是使用引數化的Sql語句的方式進行的，要注意引數化Sql語句是和命令物件配合使用的。上面的引數化Sql語句中使用的＂@Id＂等就是引數（引數以@開頭可隨便定義名稱）,在SqlCommand中需要為這些引數建立對應的引數物件，具體說來引數化Sql語句的使用有三步：

1.     構造引數Sql語句，可以是任何Sql語句

2.     為每一個Sql語句中出現的引數定義一個引數物件，並將這些引數加入到命令物件中

3.     給引數設定值，並執行查詢

構造引數Sql語句我們就不說了，定義引數物件比較複雜，上面我們看到的是使用cmd.Parameters.Add方法建立引數物件，實際上我們也可以自己定義引數物件，定義完後要加入到命令物件裡面：

SqlParameter parId=newSqlParameter();

parId.ParameterName=＂@Id＂；  //設定引數的名稱

parId.Size=6;              //設定引數資料的最大值

cmd.Parameters.Add(parId);     //將引數物件加入到命令物件中

這段程式碼和我們上面使用的＂cmd.Parameters.Add(＂@Id＂,SqlDbType.Nchar,6)效果是相同的。像這樣使用自己定義的引數可以更靈活的定製引數，因為SqlParameter類為我們提供許多有用的屬性。

3.2  呼叫無返回值的儲存過程

無返回值的儲存過程可以執行增加記錄、刪除記錄、修改記錄等資料庫操作。使用命令物件執行無返回值儲存過程和無返回值的Sql語句執行方式基本相同，都是使用ExecuteQuery()。下面這個例子，是呼叫儲存過程修改儲存過程修改Person表中我們剛剛插入的“帕瓦羅蒂”的地址，我們首先要在資料庫建立如下儲存過程：

Create Proc ch_Person

as

    Update Person

        Set psnAddress=’乞力馬紮羅’

    Where psnName=’帕瓦羅蒂’

Go

要通過C#執行該儲存過程，需要建立一個SqlCommand類的命令物件，然後修改命令物件的型別屬性CommandType為儲存過程型別，並設定命令物件的CommandText為儲存過程的名字，然後通過ExecuteNonQuery()方法執行儲存過程即可：

核心程式碼:

SqlConnection cn=new SqlConnection(＂server=.;database=test;uid=sa;pwd=123456＂);

try

{

    cn.Open();

    SqlCommandcmd=cn.CreateCommand();

    //設定命令型別為儲存過程

    cmd.CommandType=CommandType.StoreProcedure;

    //設定儲存過程的名字

    cmd.CommandText=＂ch_Person＂;

    //執行儲存過程

    cmd.ExecuteNonQuery();

}

catch(SqlExecption ex)

{

    //資料庫出錯資訊提示

}

finally
    {

    cn.Close();

}

無返回值的儲存過程還可以包含傳入引數，比如下面這個儲存過程，可以按照姓名更新地址。姓名和地址都是儲存過程傳入的引數：

Create Proc ch_Person

    @p_psnName NvarChar(5),

    @p_psnAddress NvarChar(50)

As

   Update Person

       Set psnAddress=@p_psnAddress

   Where psnName=@p_psnName

Go

呼叫這個儲存過程又需要用到命令物件中的引數屬性Parameters,只需要在這個引數集合里加入儲存過程的引數定義並設定其值就可以了，程式碼如下：

SqlConnection cn=new SqlConnection(＂server=.;database=test;uid=sa;pwd=123456＂);

try

{

  cn.Open();

  SqlCommandcmd=new SqlCommand(＂ch_Person＂,cn);

  cmd.CommandType=CommandType.StoredProcedure;

//加入引數物件，並設定其值

  cmd.Parameters.Add(＂@p_psnName＂,SqlDbType.NVarChar).Value=＂帕瓦羅蒂＂;

  cmd.Parameters.Add(＂@p_psnAddress＂,SqlDbType.NVarChar).Value=＂日本廣島＂;

//後面的具體的值，在設定的時候可以變為具體的文字框控制元件等的值；

//如：cmd.Parameters.Add(＂@p_psnName＂,SqlDbType.NVarChar).Value=txtName.Text

//執行儲存過程

  cmd.ExecuteNonQuery();

}

catch(SqlException ex)

{

  //運算元據庫出錯資訊處理

}

finally

{

  cn.Close();

}

3.3  呼叫帶返回值的儲存過程

上面討論了對於沒有返回值的儲存過程的呼叫，那麼對於帶有返回值的儲存過程我們能呼叫並獲得返回資料麼？當然可以，實際上和上面無返回值的實現方式差別不大。我們在介紹SqlParameter的時候曾經提到SqlParameter的一個Direction屬性，這個屬性就可以指定引數是輸入還是輸出，指定了Direction屬性為輸出型別的引數物件，就可以呼叫儲存過程時獲得儲存過程的返回值。Direction屬性是通過ParameterDirection列舉指定的，如：

l       Input:表示該引數為輸入引數

l       InputOutput:表示該引數既能輸入，也能輸出

l       Output：表示該引數為輸出引數

l       ReturnValue:獲取儲存過程的返回值

另外，我們知道儲存過程的返回值有兩種方式，一種是通過設定引數為Output屬性而返回，一種是直接在儲存過程裡使用Return關鍵字來返回值。下面這個儲存過程可以通過姓名查詢其地址，上面說的兩種返回方式都使用了：

Create Proc gt_Address

   @p_psnName NvarChar(5)  //請輸入姓名

   @g_psnAddress NvarChar(5)OutPut  //返回此人的地址

As

   Select@g_psnAddress=psnAddress From Person

   WherepsnName=@p_psnName

   If@@Error<>0

       Return-1   //如果查詢語句出錯返回-1

   Else

       Return 0

Go

我們呼叫這個儲存過程的部分程式碼如下：

SqlConnection cn=new SqlConnection(＂server=.;database=test;uid=sa;pwd=123456＂);

try

{

   cn.Open();

   SqlCommandcmd=new SqlCommand(＂gt_Address＂,cn);

   cmd.CommandType=CommandType.StoredProcedure;

   //加入引數，並設定引數的值和Direction屬性

   cmd.Parameters.Add(＂@p_psnName＂,SqlDbType.NvarChar).Value=＂帕瓦羅蒂＂;

   SqlParametercpar=cmd.Parameters.Add(＂@g_psnAddress＂,SqlDbType.NvarChar,50);

   cpar.Direction=ParameterDirection.Output;

   SqlParametercres=cmd.Parameters.Add(＂@return＂,SqlDbType.Int);

   cres.Direction=ParameterDirection.ReturnValue;

   //執行儲存過程

   cmd.ExecuteNonQuery();

   //獲得引數的值

   intres=(int)cres.Value;

   stringaddress=(string)cpar.Value;

   Console.WriteLine（＂返回值：{0},地址：{1}＂，res，address）;  

}

catch(SqlException ex)

{

   //資料庫出錯資訊報告

}

finally

{

   cn.Close();

}

執行結果：

返回值：0,地址：日本廣島

在上面的這段程式碼中，我們給命令物件定製了三個引數，其中有一個輸入引數：@p_psnName(引數的Direction屬性如果沒有指定，那麼預設是輸入引數)，兩個返回引數：@g_psnAddress和@return。如果檢視儲存過程你就會發現儲存過程裡並沒有@return引數，事實上在這裡,@return是一個臨時引數，我們通過這個引數來獲取儲存過程的返回值，這個引數可以是任何名稱，如@Re、@R等。儲存過程的返回值是整型的，所以@return型別也應該是整型。另外，注意對於要返回的引數，如果是字串型(NvarChar、Nchar或Char)則必須指定長度，如上面的@g_psnAddress引數我們指定了長度為50。

    對於帶引數的儲存過程，不管是輸入引數還是輸出引數，實際上有一種簡單的方式建立引數，就是使用系統類SqlCommandBuilder的靜態方法DeriveParameters自動生成引數。使用DeriveParameters方法可以從SqlCommand中指定的儲存過程中檢索引數資訊並填充到該SqlCommand物件的Parameters集合裡。我們使用DeriveParameters方法重新實現上面的儲存過程呼叫如下：

SqlConnection cn=new SqlConnection(＂server=.;database=test;uid=sa;pwd=123456＂);

try

{

   cn.Open();

   SqlCommandcmd=new SqlCommand(＂gt_Address＂,cn);

   cmd.CommandType=CommandType.StoredProcedure;

   //為命令物件生成引數

   SqlCommandBuilder.DeriveParameters(cmd);

   //設定輸入引數的值

   cmd.Parameters.Add(＂@p_psnName＂,SqlDbType.NvarChar).Value=＂帕瓦羅蒂＂;

   //執行儲存過程

   cmd.ExecuteNonQuery();

   intres=(int)cmd.Parameters[＂@Return＂].Value;

   string address=(string)cmd.Parameters[＂@p_psnAddress＂].Value;

   Console.WriteLine（＂返回值：{0},地址：{1}＂，res，address）;  

}

catch(SqlException ex)

{

   //資料庫出錯資訊報告

}

finally

{

   cn.Close();

}

    這個程式的執行方式和執行結果和上面那個完全一樣。不難看出，使用DeriveParameters只是將引數建立的過程交給了系統，我們省略了引數建立的過程，但給引數傳值還有獲得引數的值還是需要自己操作，即使如此，這樣還是使程式簡單了很多。