C# --SqlServer--调用SQLServer存储过程
以下是学习笔记：
一，一般存储过程的调用
1.1 在SQLHelper中添加方法
/// <summary>
/// 执行通用的增，删，改操作
/// </summary>
/// <param name="cmdText">slq语句或存储过程名称</param>
/// <param name="param">参数数组</param>
/// <param name="isProcedure">是否是存储过程</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, SqlParameter[] param = null,bool isProcedure=false)
{
//创建链接对象
SqlConnection conn = new SqlConnection(connString);
//创建一个命令执行对象
SqlCommand cmd = new SqlCommand(cmdText, conn); //建议直接用构造方法
try
{
conn.Open();
//添加参数
if (param != null)
{
cmd.Parameters.AddRange(param);
}
if (isProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;//这个枚举的设置表示当前cmdText是存储过程名称
}
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//如果有必要可以在这个的记录日志....
//注意：我们通用的数据访问类是“底层”地方方法，我们捕获到异常，必须还得告诉调用者具体的异常。
string errorMsge = "调用ExecuteNonQuery方法发生异常，具体异常信息：" + ex.Message;
// throw ex; //可以直接把ex对象抛出去，也可以做二次封装
throw new Exception(errorMsge);//也可以这样用，创建一个异常对象
}
finally //表示前面不管是否发生异常，都会执行的代码段
{
conn.Close();
}
}
1.2，在SQLServer中编写存储过程
--存储过程编写
if exists(select * from sysobjects where name='usp_AddProduct')
drop procedure usp_AddProduct --如果if后面有多行，必须用begin end。跟C#的{}一行
go
create procedure usp_AddProduct
--存储过程输入参数的定义
@ProductId varchar(50), --前面加“@”，后面加 数据类型
@ProductName varchar(50),
@UnitPrice numeric(18,2),
@Unit varchar(50),
@CategoryId int,
@MinCount int,
@MaxCount int
as
--在这里编写具体的存储过程内容
--在商品信息表中，添加数据
insert into Products(ProductId, ProductName, UnitPrice, Unit, CategoryId)
values(@ProductId, @ProductName, @UnitPrice, @Unit, @CategoryId)
--同时在商品库存表中，添加数据（因为ProductId是一对一的关系）
insert into ProductInventory(ProductId, MinCount, MaxCount, TotalCount,StatusId)
values(@ProductId, @MinCount, @MaxCount,0, -2)
go
--存储过程调用（测试）
exec usp_AddProduct '6005004003099','测试商品1',50,'箱',10,100,200
1.3 调用
/// <summary>
/// 一般存储过程的调用
/// </summary>
private static void InsertByProcedure1()
{
SqlParameter[] param=new SqlParameter[]
{
new SqlParameter("@ProductId", "6005004003023"),
new SqlParameter("@ProductName", "特制绿茶"),
new SqlParameter("@UnitPrice", 45),
new SqlParameter("@Unit", "箱"),
new SqlParameter("@MinCount", 100),
new SqlParameter("@MaxCount", 200),
new SqlParameter("@CategoryId", 3),
};
int result = SQLHelper.ExecuteNonQuery("usp_AddProduct", param, true);
Console.WriteLine("调用存储过程添加商品信息返回受影响的行数："+result);
}
二，带默认参数的存储过程的调用
2.1，在SQLHelper中添加方法（跟上面方法一样）
2.2 在SQLServer中编写带默认参数的存储过程
--创建一个存储过程，带参数默认值 if exists(select * from sysobjects where name='usp_AddProduct2') drop procedure usp_AddProduct2 go create procedure usp_AddProduct2 --存储过程输入参数的定义 @ProductId varchar(50), @ProductName varchar(50), @UnitPrice numeric(18,2), @Unit varchar(50), @CategoryId int, @MinCount int, @MaxCount int, @TotalCount int=0, --可以给参数添加默认值，有默认值的参数必须放到后面，方便调用 @StatusId int=-2 --参数默认值 as --在这里编写具体的存储过程内容 --在这个地方使用declare声明你要使用的变量... --在商品信息表中，添加数据 insert into Products(ProductId, ProductName, UnitPrice, Unit, CategoryId) values(@ProductId, @ProductName, @UnitPrice, @Unit, @CategoryId) --同时在商品库存表中，添加数据 insert into ProductInventory(ProductId, MinCount, MaxCount, TotalCount,StatusId) values(@ProductId, @MinCount, @MaxCount,@TotalCount,@StatusId) --如果你声明了自己的参数，可以在这个地方根据你的业务使用...(这个就相当于C#方法中的局部变量) go --调用带参数默认值的存储过程（测试） exec usp_AddProduct2 '7005004003099','测试商品2',60,'箱',10,100,200 --后面两个参数全部使用默认值 exec usp_AddProduct2 '8005004003099','测试商品3',65,'箱',10,100,200,150,1 --都不使用默认值 exec usp_AddProduct2 '9005004003099','测试商品4',65,'箱',10,100,200,150 --第2个使用默认值 exec usp_AddProduct2 '9005004003199','测试商品5',65,'箱',10,100,200, @StatusId=1 --第1个使用默认值
2.3 调用
/// <summary>
/// 带默认参数的存储过程的调用
/// </summary>
private static void InsertByProcedure2()
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@ProductId", "6005004003024"),
new SqlParameter("@ProductName", "特制红茶"),
new SqlParameter("@UnitPrice", 45),
new SqlParameter("@Unit", "箱"),
new SqlParameter("@MinCount", 100),
new SqlParameter("@MaxCount", 200),
new SqlParameter("@CategoryId", 3),
new SqlParameter("@TotalCount", 2),//第一个不使用默认值
};
int result = SQLHelper.ExecuteNonQuery("usp_AddProduct2", param, true);
Console.WriteLine("调用存储过程添加商品信息返回受影响的行数：" + result);
}
三，带输出参数的存储过程的调用
3.1，在SQLHelper中添加方法（跟上面方法一样）
3.2 在SQLServer中编写输出认参数的存储过程
------------------------------带上输入、输出参数的存储过程--------------------- if exists(select * from sysobjects where name='usp_AddProduct3') drop procedure usp_AddProduct3 go create procedure usp_AddProduct3 --存储过程输入参数的定义 @ProductId varchar(50), @ProductName varchar(50), @UnitPrice numeric(18,2), @Unit varchar(50), @CategoryId int, @ProductCount int output, --定义输出参数 @MinCount int=100, --默认值参数要放在后面 @MaxCount int=200 as --在商品信息表中，添加数据 insert into Products(ProductId, ProductName, UnitPrice, Unit, CategoryId) values(@ProductId, @ProductName, @UnitPrice, @Unit, @CategoryId) --同时在商品库存表中，添加数据 insert into ProductInventory(ProductId, MinCount, MaxCount, TotalCount,StatusId) values(@ProductId, @MinCount, @MaxCount,0, -2) --获得输出参数 select @ProductCount=count(*) from Products go
3.3 调用
/// <summary>
/// 带输出参数的存储过程的调用
/// </summary>
private static void InsertByProcedure3()
{
//首先要定义输出参数
SqlParameter outprParam = new SqlParameter("@ProductCount",SqlDbType.Int);//输出参数名称 数据类型
outprParam.Direction = ParameterDirection.Output;//表示参数是输出参数
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@ProductId", "6005004003025"),
new SqlParameter("@ProductName", "特制苦茶"),
new SqlParameter("@UnitPrice", 60),
new SqlParameter("@Unit", "箱"),
//new SqlParameter("@MinCount", 100), //使用默认值
//new SqlParameter("@MaxCount", 200), //使用默认值
new SqlParameter("@CategoryId", 3),
outprParam
};
int result = SQLHelper.ExecuteNonQuery("usp_AddProduct3", param, true);
Console.WriteLine("调用存储过程添加商品信息返回受影响的行数：" + result);
//获取输出参数
int outResult = (int) outprParam.Value;
Console.WriteLine("当前商品列表总数（输出参数）：" + outResult);
}
