.net調(diào)用存儲過程詳解小眼睛老鼠本文的數(shù)據(jù)庫用的是sql server自帶數(shù)據(jù)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; // 如果執(zhí)行語句 cmd.CommandText = "Categoriestest1"; // 指定執(zhí)行語句為存儲過程 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. 沒有輸入輸出的存儲過程 c#代碼部分
SqlConnection sqlconn = new SqlConnection(conn); SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlconn; cmd.CommandText = "Categoriestest2"; cmd.CommandType = CommandType.StoredProcedure; sqlconn.Open(); // 執(zhí)行并顯示影響行數(shù) 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. 有返回值的存儲過程 c#代碼部分
SqlConnection sqlconn = new SqlConnection(conn); SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlconn; cmd.CommandText = "Categoriestest3"; cmd.CommandType = CommandType.StoredProcedure; // 創(chuàng)建參數(shù) IDataParameter[] parameters = { new SqlParameter("rval", SqlDbType.Int,4) }; // 將參數(shù)類型設置為 返回值類型 parameters[0].Direction = ParameterDirection.ReturnValue; // 添加參數(shù) cmd.Parameters.Add(parameters[0]); sqlconn.Open(); // 執(zhí)行存儲過程并返回影響的行數(shù) Label1.Text = cmd.ExecuteNonQuery().ToString(); sqlconn.Close(); // 顯示影響的行數(shù)和返回值 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. 有輸入?yún)?shù)和輸出參數(shù)的存儲過程 c#代碼部分
SqlConnection sqlconn = new SqlConnection(conn); SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlconn; cmd.CommandText = "Categoriestest4"; cmd.CommandType = CommandType.StoredProcedure; // 創(chuàng)建參數(shù) IDataParameter[] parameters = { new SqlParameter("@Id", SqlDbType.Int,4) , new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) , }; // 設置參數(shù)類型 parameters[0].Direction = ParameterDirection.Output; // 設置為輸出參數(shù) parameters[1].Value = "testCategoryName"; // 添加參數(shù) cmd.Parameters.Add(parameters[0]); cmd.Parameters.Add(parameters[1]); sqlconn.Open(); // 執(zhí)行存儲過程并返回影響的行數(shù) Label1.Text = cmd.ExecuteNonQuery().ToString(); sqlconn.Close(); // 顯示影響的行數(shù)和輸出參數(shù) 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. 同時具有返回值、輸入?yún)?shù),、輸出參數(shù)的存儲過程 c#代碼部分
SqlConnection sqlconn = new SqlConnection(conn); SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlconn; cmd.CommandText = "Categoriestest5"; cmd.CommandType = CommandType.StoredProcedure; // 創(chuàng)建參數(shù) IDataParameter[] parameters = { new SqlParameter("@Id", SqlDbType.Int,4) , new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) , new SqlParameter("rval", SqlDbType.Int,4) }; // 設置參數(shù)類型 parameters[0].Direction = ParameterDirection.Output; // 設置為輸出參數(shù) parameters[1].Value = "testCategoryName"; // 給輸入?yún)?shù)賦值 parameters[2].Direction = ParameterDirection.ReturnValue; // 設置為返回值 // 添加參數(shù) cmd.Parameters.Add(parameters[0]); cmd.Parameters.Add(parameters[1]); cmd.Parameters.Add(parameters[2]); sqlconn.Open(); // 執(zhí)行存儲過程并返回影響的行數(shù) Label1.Text = cmd.ExecuteNonQuery().ToString(); sqlconn.Close(); // 顯示影響的行數(shù)，輸出參數(shù)和返回值 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. 同時返回參數(shù)和記錄集的存儲過程 c#代碼部分
SqlConnection sqlconn = new SqlConnection(conn); SqlCommand cmd = new SqlCommand(); cmd.Connection = sqlconn; cmd.CommandText = "Categoriestest6"; cmd.CommandType = CommandType.StoredProcedure; // 創(chuàng)建參數(shù) IDataParameter[] parameters = { new SqlParameter("@Id", SqlDbType.Int,4) , new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) , new SqlParameter("rval", SqlDbType.Int,4) // 返回值 }; // 設置參數(shù)類型 parameters[0].Direction = ParameterDirection.Output; // 設置為輸出參數(shù) parameters[1].Value = "testCategoryName"; // 給輸入?yún)?shù)賦值 parameters[2].Direction = ParameterDirection.ReturnValue; // 設置為返回值 // 添加參數(shù) 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); // 顯示結(jié)果集 GridView1.DataSource = ds.Tables[0]; GridView1.DataBind(); Label1.Text = ""; // 顯示輸出參數(shù)和返回值 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. 返回多個記錄集的存儲過程 c#代碼部分
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); // 顯示結(jié)果集1 GridView1.DataSource = ds.Tables[0]; GridView1.DataBind(); // 顯示結(jié)果集2 GridView2.DataSource = ds.Tables[1]; GridView2.DataBind();
存儲過程Categoriestest7
CREATE PROCEDURE Categoriestest7 AS select * from Categories select * from Categories GO |
|
|
