在SQL Server Manage Studio中执行sql语句结果通常包括Results和Messages,例如sql中既有select语句,也有update语句时,Results里返回select的结果集,Messages中返回两条Sql语句各自影响的行数。如果用C#编程执行sql语句,怎样同时获取两者?在SqlCommand中,好像都是分开的,ExcuteReader、ExcuteNonQuery……请指教,谢谢!
这个分2步, 第一步,创建存储过程,定义out 参数.create PROCEDURE sp_MultiReturnSample(@outputPara int OUTPUT) AS BEGIN set @outputPara = '1' -- return a value from insert, update or delete select * from table -- return a table END第二步,代码调用.SqlConnection conn = new SqlConnection(); conn.ConnectionString = db ID=user;Password=pwd"; conn.Open(); SqlCommand cmd = new SqlCommand("sp_MultiReturnSample", conn); cmd.CommandType = CommandType.StoredProcedure;SqlParameter para = cmd.Parameters.AddWithValue("@outputPara", 0); ; para.Direction = ParameterDirection.Output;DataSet ds = new DataSet(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = cmd; sqlDA.Fill(ds);
如果你运行的是update,delete等命令,返回的result就是更新的行数
第一步,创建存储过程,定义out 参数.create PROCEDURE sp_MultiReturnSample(@outputPara int OUTPUT) AS
BEGIN
set @outputPara = '1' -- return a value from insert, update or delete
select * from table -- return a table
END第二步,代码调用.SqlConnection conn = new SqlConnection();
conn.ConnectionString = db ID=user;Password=pwd";
conn.Open();
SqlCommand cmd = new SqlCommand("sp_MultiReturnSample", conn);
cmd.CommandType = CommandType.StoredProcedure;SqlParameter para = cmd.Parameters.AddWithValue("@outputPara", 0); ;
para.Direction = ParameterDirection.Output;DataSet ds = new DataSet();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = cmd;
sqlDA.Fill(ds);
conn.Close();//result
int outputPara = Convert.ToInt32(cmd.Parameters["@outputPara"].Value);
DataTable dt = ds.Tables[0];
outputPara 和 dt即为所求.
欢迎使用 CSDN 小秘书
CSDN 小秘书下载
***********************************************************[/align]
@outparm int output
as
update table_a
set a = ''
where id = 9
--把影响行数付给输出参数
set @outparm = @@rowcount select * from table_ago
@outparm int output
as
update table_a
set a = ''
where id = 9
--把影响行数付给输出参数
set @outparm = @@rowcount select * from table_ago
DataTable dt = ds.Tables[0];学习了!
{
using (SqlConnection conn = new SqlConnection(""))
{
using (SqlCommand cmd = new SqlCommand(sql,conn))
{
try
{
connection.Open();
SqlDataReader dr =cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr ;
}
catch(System.Data.SqlClient.SqlException e)
{
conn.Close();
throw new Exception(e.Message);
}
}
}
}
看看数据库操作类sqlhelper
using (SqlConnection sqlCon = new SqlConnection(""))
{
sqlCon.Open();
sqlComm = new SqlCommand("", sqlCon);
sqlComm.CommandType = CommandType.StoredProcedure;sqlComm.Parameters.Add("@tblName ", SqlDbType.VarChar,200);
sqlComm.Parameters.Add("@fldName ", SqlDbType.VarChar, 200);
sqlComm.Parameters.Add("@pageCountint ", SqlDbType.int);
...
sqlComm.Parameters["@pageCountint "].Direction = ParameterDirection.Output;
sqlComm.Parameters["@tblName "].Value = "";
sqlComm.Parameters["@fldName "].Value = "";
...
DataSet ds = new DataSet();
sqlComm.Fill(ds);strng c= sqlComm.Parameters["@pageCountint "].Value.ToString();
}
http://topic.csdn.net/u/20091204/21/722689e1-7824-497c-b709-4b1118264633.html