C#調用預存程序詳解

標籤：des   http   io   ar   os   sp   檔案   資料   on   

本文的資料庫用的是sql server內建資料Northwind

連接字串：

string conn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

 

confige檔案：

<connectionStrings>

        <add name="NorthwindConnectionString" connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>

 </connectionStrings>

 

1. 只返回單一記錄集的預存程序

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

         // 設定sql串連 

         cmd.Connection = sqlconn; 

         // 如果執行語句 

         cmd.CommandText = "Categoriestest1"; 

         // 指定執行語句為預存程序 

         cmd.CommandType = CommandType.StoredProcedure; 

  

        SqlDataAdapter dp = new SqlDataAdapter(cmd); 

         DataSet ds = new DataSet(); 

         // 填充dataset 

         dp.Fill(ds); 

         // 以下是顯示效果 

         GridView1.DataSource = ds; 

         GridView1.DataBind();

 

預存程序Categoriestest1

CREATE PROCEDURE Categoriestest1  

 AS

 select *  

 from  Categories 

 GO 

 

 

 

2. 沒有輸入輸出的預存程序

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

  

        cmd.Connection = sqlconn; 

         cmd.CommandText = "Categoriestest2"; 

         cmd.CommandType = CommandType.StoredProcedure; 

         sqlconn.Open(); 

         // 執行並顯示影響行數 

         Label1.Text = cmd.ExecuteNonQuery().ToString(); 

         sqlconn.Close();

預存程序Categoriestest2

CREATE PROCEDURE Categoriestest2  AS

 insert into dbo.Categories  

 (CategoryName,[Description],[Picture]) 

 values (‘test1‘,‘test1‘,null) 

 GO

 

 

3. 有傳回值的預存程序

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

         cmd.Connection = sqlconn; 

         cmd.CommandText = "Categoriestest3"; 

         cmd.CommandType = CommandType.StoredProcedure; 

         // 建立參數 

         IDataParameter[] parameters = { 

                 new SqlParameter("rval", SqlDbType.Int,4) 

             }; 

         // 將參數類型設定為 傳回值類型 

         parameters[0].Direction = ParameterDirection.ReturnValue; 

         // 添加參數 

         cmd.Parameters.Add(parameters[0]); 

  

        sqlconn.Open(); 

         // 執行預存程序並返回影響的行數 

         Label1.Text = cmd.ExecuteNonQuery().ToString(); 

         sqlconn.Close(); 

         // 顯示影響的行數和傳回值 

         Label1.Text += "-" + parameters[0].Value.ToString() ;

預存程序Categoriestest3

CREATE PROCEDURE Categoriestest3 

 AS

 insert into dbo.Categories  

 (CategoryName,[Description],[Picture]) 

 values (‘test1‘,‘test1‘,null) 

 return @@rowcount 

 GO

 

 

4. 有輸入參數和輸出參數的預存程序

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

         cmd.Connection = sqlconn;  

         cmd.CommandText = "Categoriestest4"; 

         cmd.CommandType = CommandType.StoredProcedure; 

         // 建立參數 

         IDataParameter[] parameters = { 

                 new SqlParameter("@Id", SqlDbType.Int,4) , 

                 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) , 

             }; 

         // 設定參數類型 

         parameters[0].Direction = ParameterDirection.Output;  // 設定為輸出參數 

         parameters[1].Value = "testCategoryName"; 

         // 添加參數 

         cmd.Parameters.Add(parameters[0]); 

         cmd.Parameters.Add(parameters[1]); 

  

        sqlconn.Open(); 

         // 執行預存程序並返回影響的行數 

         Label1.Text = cmd.ExecuteNonQuery().ToString(); 

         sqlconn.Close(); 

         // 顯示影響的行數和輸出參數 

         Label1.Text += "-" + parameters[0].Value.ToString() ;

預存程序Categoriestest4

CREATE PROCEDURE Categoriestest4 

 @id int output, 

 @CategoryName nvarchar(15) 

 AS

 insert into dbo.Categories  

 (CategoryName,[Description],[Picture]) 

 values (@CategoryName,‘test1‘,null) 

 set  @id = @@IDENTITY 

 GO

 

 

5. 同時具有傳回值、輸入參數、輸出參數的預存程序

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

         cmd.Connection = sqlconn; 

         cmd.CommandText = "Categoriestest5"; 

         cmd.CommandType = CommandType.StoredProcedure; 

         // 建立參數 

         IDataParameter[] parameters = { 

                 new SqlParameter("@Id", SqlDbType.Int,4) , 

                 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) , 

                 new SqlParameter("rval", SqlDbType.Int,4) 

             }; 

         // 設定參數類型 

         parameters[0].Direction = ParameterDirection.Output;       // 設定為輸出參數 

         parameters[1].Value = "testCategoryName";                  // 給輸入參數賦值 

         parameters[2].Direction = ParameterDirection.ReturnValue;  // 設定為傳回值 

         // 添加參數 

         cmd.Parameters.Add(parameters[0]); 

         cmd.Parameters.Add(parameters[1]); 

         cmd.Parameters.Add(parameters[2]); 

  

        sqlconn.Open(); 

         // 執行預存程序並返回影響的行數 

         Label1.Text = cmd.ExecuteNonQuery().ToString(); 

         sqlconn.Close(); 

         // 顯示影響的行數，輸出參數和傳回值 

         Label1.Text += "-" + parameters[0].Value.ToString() + "-" + parameters[2].Value.ToString();

預存程序Categoriestest5

CREATE PROCEDURE Categoriestest5 

 @id int output, 

 @CategoryName nvarchar(15) 

 AS

 insert into dbo.Categories  

 (CategoryName,[Description],[Picture]) 

 values (@CategoryName,‘test1‘,null) 

 set  @id = @@IDENTITY 

 return @@rowcount 

 GO

 

 

6. 同時返回參數和記錄集的預存程序

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

         cmd.Connection = sqlconn; 

         cmd.CommandText = "Categoriestest6"; 

         cmd.CommandType = CommandType.StoredProcedure; 

         // 建立參數 

         IDataParameter[] parameters = { 

                 new SqlParameter("@Id", SqlDbType.Int,4) , 

                 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) , 

                 new SqlParameter("rval", SqlDbType.Int,4)                   // 傳回值 

             }; 

         // 設定參數類型 

         parameters[0].Direction = ParameterDirection.Output;        // 設定為輸出參數 

         parameters[1].Value = "testCategoryName";                   // 給輸入參數賦值 

         parameters[2].Direction = ParameterDirection.ReturnValue;   // 設定為傳回值 

         // 添加參數 

         cmd.Parameters.Add(parameters[0]); 

         cmd.Parameters.Add(parameters[1]); 

         cmd.Parameters.Add(parameters[2]); 

  

        SqlDataAdapter dp = new SqlDataAdapter(cmd); 

         DataSet ds = new DataSet(); 

         // 填充dataset 

         dp.Fill(ds); 

         // 顯示結果集 

         GridView1.DataSource = ds.Tables[0]; 

         GridView1.DataBind(); 

  

        Label1.Text = ""; 

         // 顯示輸出參數和傳回值 

         Label1.Text +=  parameters[0].Value.ToString() + "-" + parameters[2].Value.ToString();

預存程序Categoriestest6

CREATE PROCEDURE Categoriestest6 

 @id int output, 

 @CategoryName nvarchar(15) 

 AS

 insert into dbo.Categories  

 (CategoryName,[Description],[Picture]) 

 values (@CategoryName,‘test1‘,null) 

 set  @id = @@IDENTITY 

 select * from Categories 

 return @@rowcount 

 GO

 

 

7. 返回多個記錄集的預存程序

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

          

         cmd.Connection = sqlconn; 

         cmd.CommandText = "Categoriestest7";  

         cmd.CommandType = CommandType.StoredProcedure; 

  

        SqlDataAdapter dp = new SqlDataAdapter(cmd); 

         DataSet ds = new DataSet(); 

         // 填充dataset 

         dp.Fill(ds); 

         // 顯示結果集1 

         GridView1.DataSource = ds.Tables[0]; 

         GridView1.DataBind(); 

         // 顯示結果集2 

         GridView2.DataSource = ds.Tables[1]; 

         GridView2.DataBind();

預存程序Categoriestest7

CREATE PROCEDURE Categoriestest7 

 AS

 select * from Categories 

 select * from Categories 

 GO 

C#調用預存程序詳解