《net-存储过程的使用》由会员分享，可在线阅读，更多相关《net-存储过程的使用（7页珍藏版）》请在金锄头文库上搜索。
1、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 Data
2、Set();/ 填充 datasetdp.Fill(ds);/ 以下是显示效果GridView1.DataSource = ds;GridView1.DataBind();存储过程 Categoriestest1CREATE PROCEDURE Categoriestest1 ASselect * from CategoriesGO2. 没有输入输出的存储过程c#代码部分SqlConnection sqlconn = new SqlConnection(conn);SqlCommand cmd = new SqlCommand();cmd.Connection = sqlconn;cmd.Co
3、mmandText = Categoriestest2;cmd.CommandType = CommandType.StoredProcedure;sqlconn.Open(); / 执行并显示影响行数Label1.Text = cmd.ExecuteNonQuery().ToString();sqlconn.Close();存储过程 Categoriestest2CREATE PROCEDURE Categoriestest2 ASinsert into dbo.Categories (CategoryName,Description,Picture)values (test1,test1,
4、null)GO3. 有返回值的存储过程c#代码部分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);/ 将参数类型设置为 返回值
5、类型parameters0.Direction = ParameterDirection.ReturnValue;/ 添加参数cmd.Parameters.Add(parameters0);sqlconn.Open();/ 执行存储过程并返回影响的行数Label1.Text = cmd.ExecuteNonQuery().ToString();sqlconn.Close();/ 显示影响的行数和返回值Label1.Text += - + parameters0.Value.ToString() ;存储过程 Categoriestest3CREATE PROCEDURE Categorieste
6、st3ASinsert into dbo.Categories (CategoryName,Description,Picture)values (test1,test1,null)return rowcountGO4. 有输入参数和输出参数的存储过程c#代码部分SqlConnection sqlconn = new SqlConnection(conn);SqlCommand cmd = new SqlCommand();cmd.Connection = sqlconn;cmd.CommandText = Categoriestest4;cmd.CommandType = CommandTy
7、pe.StoredProcedure;/ 创建参数IDataParameter parameters = new SqlParameter(Id, SqlDbType.Int,4) ,new SqlParameter(CategoryName, SqlDbType.NVarChar,15) ,;/ 设置参数类型parameters0.Direction = ParameterDirection.Output; / 设置为输出参数parameters1.Value = testCategoryName;/ 添加参数cmd.Parameters.Add(parameters0);cmd.Param
8、eters.Add(parameters1);sqlconn.Open();/ 执行存储过程并返回影响的行数Label1.Text = cmd.ExecuteNonQuery().ToString();sqlconn.Close();/ 显示影响的行数和输出参数Label1.Text += - + parameters0.Value.ToString() ;存储过程 Categoriestest4 CREATE PROCEDURE Categoriestest4id int output,CategoryName nvarchar(15)ASinsert into dbo.Categories
9、 (CategoryName,Description,Picture)values (CategoryName,test1,null)set id = IDENTITYGO5. 同时具有返回值、输入参数、输出参数的存储过程c#代码部分SqlConnection sqlconn = new SqlConnection(conn);SqlCommand cmd = new SqlCommand();cmd.Connection = sqlconn;cmd.CommandText = Categoriestest5;cmd.CommandType = CommandType.StoredProced
10、ure;/ 创建参数IDataParameter parameters = new SqlParameter(Id, SqlDbType.Int,4) ,new SqlParameter(CategoryName, SqlDbType.NVarChar,15) ,new SqlParameter(rval, SqlDbType.Int,4);/ 设置参数类型parameters0.Direction = ParameterDirection.Output; / 设置为输出参数parameters1.Value = testCategoryName; / 给输入参数赋值parameters2.D
11、irection = ParameterDirection.ReturnValue; / 设置为返回值/ 添加参数cmd.Parameters.Add(parameters0);cmd.Parameters.Add(parameters1);cmd.Parameters.Add(parameters2);sqlconn.Open();/ 执行存储过程并返回影响的行数Label1.Text = cmd.ExecuteNonQuery().ToString();sqlconn.Close();/ 显示影响的行数，输出参数和返回值Label1.Text += - + parameters0.Valu
12、e.ToString() + - + parameters2.Value.ToString(); 存储过程 Categoriestest5CREATE PROCEDURE Categoriestest5id int output,CategoryName nvarchar(15)ASinsert into dbo.Categories (CategoryName,Description,Picture)values (CategoryName,test1,null)set id = IDENTITYreturn rowcountGO6. 同时返回参数和记录集的存储过程c#代码部分SqlConn
13、ection 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.NVarCh
14、ar,15) ,new SqlParameter(rval, SqlDbType.Int,4) / 返回值;/ 设置参数类型parameters0.Direction = ParameterDirection.Output; / 设置为输出参数parameters1.Value = testCategoryName; / 给输入参数赋值parameters2.Direction = ParameterDirection.ReturnValue; / 设置为返回值/ 添加参数cmd.Parameters.Add(parameters0);cmd.Parameters.Add(parameters
15、1);cmd.Parameters.Add(parameters2);SqlDataAdapter dp = new SqlDataAdapter(cmd); DataSet ds = new DataSet();/ 填充 datasetdp.Fill(ds);/ 显示结果集GridView1.DataSource = ds.Tables0;GridView1.DataBind();Label1.Text = ;/ 显示输出参数和返回值Label1.Text += parameters0.Value.ToString() + - + parameters2.Value.ToString();存储过程 Categoriestest6CREATE PROCEDURE Categoriestest6id int output,CategoryName nvarchar(15)ASinsert into dbo.Categories (CategoryName,Description,Picture)values (CategoryName,
