以下阐述在net程序设计中，如何调用带有输入输出参数的SQL存储过程实例；
一、SQL存储过程代码实例
create procedure itemCodes
@itemName1 varchar(50),
@sizeName1 varchar(50),
@itemCode varchar(5) output
as
select @itemCode=itemmap.ItemCode from itemmap inner join item on itemmap.ItemCode=item.ItemCode where(item.ItemName1=@itemName1 and item.SizeName1=@sizeName1)
go
二、Net代码
①代码实例
string strConn = "server=localhost;uid=sa;pwd=sa;database=PAT";
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlCommand cmd = new SqlCommand("getItemCode", conn);
cmd.CommandType = CommandType.StoredProcedure;
//输入参数
SqlParameter param1 = new SqlParameter("@itemName1", SqlDbType.VarChar, 50);
param1.Value = itemName1;
cmd.Parameters.Add(param1);
//输入参数
SqlParameter param2 = new SqlParameter("@sizeName1", SqlDbType.VarChar, 50);
param2.Value = sizeName1;
cmd.Parameters.Add(param2);
//输出参数
SqlParameter param = new SqlParameter("@itemCode", SqlDbType.VarChar, 50);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
//执行存储过程
cmd.ExecuteNonQuery();
conn.Close();
//输出值
Console.WriteLine(param.Value);
②代码实例
string strConn = "server=localhost;uid=sa;pwd=sa;database=PAT";
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlCommand cmd = new SqlCommand("getItemCode", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] parameters =
{
new SqlParameter("@itemName1",SqlDbType.VarChar,50),
new SqlParameter("@sizeName1",SqlDbType.VarChar,50),
new SqlParameter("@itemCode",SqlDbType.VarChar,50)
};
parameters[0].Value = "Chai";
parameters[1].Value = "500g";
parameters[2].Direction = ParameterDirection.Output;
foreach (var p in parameters)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
conn.Close();
//输出值
Console.WriteLine(parameters[2].Value);