求C#+SQLServer代码:如何使用输出参数?如何获得顾储过程返回的结果集?
我已有了如下代码,代码只演示了输入参数,没演示输出参数和如何获得结果休,请大家帮助改下,实现我上述要求。
SqlConnection myCon = CreateConn.conn();
SqlDataAdapter sda = new SqlDataAdapter("ImageMessage", myCon);//ImageMessage为存储过程名
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter spt = new SqlParameter("@mvName", SqlDbType.VarChar, 20);
spt.Direction = ParameterDirection.Input;
spt.Value = mvName;
sda.SelectCommand.Parameters.Add(spt);
DataSet ds = new DataSet();
sda.Fill(ds, "ffc");
return ds;
我已有了如下代码,代码只演示了输入参数,没演示输出参数和如何获得结果休,请大家帮助改下,实现我上述要求。
SqlConnection myCon = CreateConn.conn();
SqlDataAdapter sda = new SqlDataAdapter("ImageMessage", myCon);//ImageMessage为存储过程名
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter spt = new SqlParameter("@mvName", SqlDbType.VarChar, 20);
spt.Direction = ParameterDirection.Input;
spt.Value = mvName;
sda.SelectCommand.Parameters.Add(spt);
DataSet ds = new DataSet();
sda.Fill(ds, "ffc");
return ds;
(name varchar(20),
old varchar(20),
re text) create function getOld(@old varchar(20))
returns varchar(20)
as
begin
select @old = old from a where name = 'zhang'
return @old
end 回答第1,2,3:
try
{
string old="";
System.Data.SqlClient.SqlConnection _con = new System.Data.SqlClient.SqlConnection("server=(local);uid=sa;pwd=;database=zyzhangA");
string _returnOld="";
_con.Open();
System.Data.SqlClient.SqlCommand _cmd = new System.Data.SqlClient.SqlCommand("select dbo.getOld('"+old+"')",_con);
_returnOld = (string)_cmd.ExecuteScalar();
_con.Close();
MessageBox.Show(_returnOld);
}
catch(System.Data.SqlClient.SqlException ex)
{
MessageBox.Show(ex.Message);
}
回答第四:
try
{
string old="";
System.Data.SqlClient.SqlConnection _con = new System.Data.SqlClient.SqlConnection("server=(local);uid=sa;pwd=p7zx5dez;database=zyzhangA");
System.Data.SqlClient.SqlDataAdapter _sqlada = new System.Data.SqlClient.SqlDataAdapter("select dbo.getOld('"+old+"')",_con);
//这边假设dbo.getOld函数返回一个数据集
System.Data.DataSet _ds = new System.Data.DataSet();
_sqlada.Fill(_ds);
MessageBox.Show(_ds.Tables[0].Rows[0][0].ToString());
}
catch(System.Data.SqlClient.SqlException ex)
{
MessageBox.Show(ex.Message);
}
C#获取存储过程的Return返回值和Output输出参数值
作者:dnawo 日期:2008-04-10
字体大小: 小 中 大
1.获取Return返回值
程序代码*/
--存储过程
Create PROCEDURE MYSQL
@a int,
@b int
AS
return @a + @b
GO
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
conn.Open();
SqlCommand MyCommand = new SqlCommand("MYSQL", conn);
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));
MyCommand.Parameters["@a"].Value = 10;
MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.Int));
MyCommand.Parameters["@b"].Value = 20;
MyCommand.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
MyCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
MyCommand.ExecuteNonQuery();
Response.Write(MyCommand.Parameters["@return"].Value.ToString()); //2.获取Output输出参数值
//程序代码
--存储过程
Create PROCEDURE MYSQL
@a int,
@b int,
@c int output
AS
Set @c = @a + @b
GO
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
conn.Open();
SqlCommand MyCommand = new SqlCommand("MYSQL", conn);
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));
MyCommand.Parameters["@a"].Value = 20;
MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.Int));
MyCommand.Parameters["@b"].Value = 20;
MyCommand.Parameters.Add(new SqlParameter("@c", SqlDbType.Int));
MyCommand.Parameters["@c"].Direction = ParameterDirection.Output;
MyCommand.ExecuteNonQuery();
Response.Write(MyCommand.Parameters["@c"].Value.ToString());