C#调用存储过程 返回值，输出参数，返回结果集

System.Data.SqlClient.SqlConnection sqlSvrDbCnt = new System.Data.SqlClient.SqlConnection(); //数据库连接对象
System.Data.SqlClient.SqlDataAdapter sqlSvrDbAdp = null; //数据适配器对象

System.Data.SqlClient.SqlCommand sqlSvrDbCmd = null; //数据命令处理对象
System.Data.SqlClient.SqlDataReader sqlSvrDbReader = null; //只进读取器对象

sqlSvrDbCnt.ConnectionString = "Data Source=LANDSEA-8CC1455//SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
string sProcName = "p_test";
sqlSvrDbCmd = new System.Data.SqlClient.SqlCommand(); //建立数据命令对象的
sqlSvrDbCnt.Open(); //打开数据库连接
string sErrMessage = "";

try
{
sqlSvrDbCmd = new System.Data.SqlClient.SqlCommand(sProcName, sqlSvrDbCnt);//实例化命令对象

sqlSvrDbCmd.CommandType = CommandType.StoredProcedure;

//赋参数值

/*
sampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12);
sampParm.Value = "Sample Value";

sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28);
sampParm.Direction = ParameterDirection.Output;
*/

System.Data.SqlClient.SqlParameter sampParm = null;
sampParm = sqlSvrDbCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
sampParm.Direction = ParameterDirection.ReturnValue;

sqlSvrDbCmd.Parameters.Add("@REPORTYEAR", SqlDbType.Int).Value = year;
sqlSvrDbCmd.Parameters.Add("@REPORTMONTH", SqlDbType.Int).Value = month;
sqlSvrDbCmd.Parameters.Add("@SHIP_ID", SqlDbType.Char).Value = shipid;
sampParm = sqlSvrDbCmd.Parameters.Add("@rt", SqlDbType.VarChar,100);
sampParm.Direction = ParameterDirection.Output;

sqlSvrDbReader = sqlSvrDbCmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(sqlSvrDbReader);

string rt="";
rt = sqlSvrDbCmd.Parameters["@rt"].Value.ToString();
rt = sqlSvrDbCmd.Parameters["RETURN_VALUE"].Value.ToString();

return dt;
}
catch (System.Data.SqlClient.SqlException e)
{
//给错误信息赋值

sErrMessage = getDbError(e);
return null;
}
catch (Exception e)
{
//给错误信息赋值

sErrMessage = e.Message;
return null;
}
finally
{
//释放资源
if (sqlSvrDbCmd != null)
{
sqlSvrDbCmd.Dispose();
}
if (sqlSvrDbCnt.State == ConnectionState.Open)
{
sqlSvrDbCnt.Close();
}
}

if object_id('p_test') is not null drop proc p_test
go
create proc p_test @REPORTYEAR int,@REPORTMONTH int,@SHIP_ID char(36),@rt varchar(100) output
as
begin
set @rt='ok'
if @REPORTYEAR=0 or @REPORTYEAR is null
set @REPORTYEAR=year(getdate())
if @REPORTMONTH=0 or @REPORTMONTH is null
set @REPORTMONTH=month(getdate())
if @SHIP_ID='' or @SHIP_ID is null
begin
set @rt='error'
return -1
end
select a.SMR_47_ID,a.ship_id,a.REPORTYEAR,a.REPORTMONTH,a.REPORTDATE,a.SEQUNCE,a.PROJECTNAME,a.REPAIRCONTENT,a.REPAIRDATE,a.COMPLETEDATE,
a.COMPLETEDBY,a.REMARK,a.CO,a.MASTER,a.EMANAGER, a.VIEWDATE
from T_CT_SMR_47 a
where REPORTYEAR=@REPORTYEAR
and REPORTMONTH=@REPORTMONTH
and upper(SHIP_ID) = @SHIP_ID order by SEQUNCE
end

private string getDbError(System.Data.SqlClient.SqlException sqlException)
{
string errText = "";
string dbErrText = sqlException.Message.ToLower();
switch (sqlException.Number)
{
case -3:
errText = "本次修改内容,被其他用户修改过";
break;
case 2601://唯一索引
errText = "值必须唯一";
break;
case 547://外键约束
//DELETE 语句与 REFERENCE 约束"FK_T_MATOUT_SHIP"冲突。该冲突发生于数据库"Wuhai4500KK"，表"dbo.T_MATERIEL_OUT", column 'SHIP_ID'。

//step 1 find foreign table name
//step 2 instence a table object of foreign table
//step 3 find foreign column name
//step 4 concentrate into one sentence
//string fkeyTablename;
//OurTable theFkeyTable;
////string fkeycoulumnname;
//fkeyTablename = dbErrText.Substring(dbErrText.IndexOf("dbo.t_"), dbErrText.IndexOf("/"", dbErrText.IndexOf("dbo.t_")) - dbErrText.IndexOf("dbo.t_"));
//fkeyTablename = fkeyTablename.Substring(4);
//theFkeyTable = OurTableServices.GetInstance.LoadATableByName(fkeyTablename);
//if (!theFkeyTable.IsWrong)
//{
// fkeyTablename = theFkeyTable.TableChineseName + "[" + fkeyTablename + "]";
//}
//errText = "由于外键约束导致无法进行当前的数据操作，更多描述见以下内容：/n/n"
// + "[" + fkeyTablename + "]中使用了当前操作数据的信息，进行本次操作前必须先删除其引用数据";

break;
case 2627://主键约束
errText = "主键约束";
break;
case 515://字段不可为空
//string tablename;
//OurTable theTable;
//string coulumnname;
//tablename = dbErrText.Substring(dbErrText.IndexOf("dbo.t_"), dbErrText.IndexOf("'", dbErrText.IndexOf("dbo.t_")) - dbErrText.IndexOf("dbo.t_"));
//tablename = tablename.Substring(4);
//theTable = OurTableServices.GetInstance.LoadATableByName(tablename);
//if (!theTable.IsWrong)
//{
// tablename = theTable.TableChineseName + "[" + tablename + "]";
//}
//coulumnname = dbErrText.Substring(dbErrText.IndexOf("'"), dbErrText.IndexOf("'", dbErrText.IndexOf("'") + 1) - dbErrText.IndexOf("'"));
//errText = tablename + "的字段" + coulumnname + "不可为空";
//sqlException.Message不能将值 NULL 插入列 'UNIT_NAME'，表 'LSShipMis_Ship.dbo.T_UNIT'；列不允许有空值。UPDATE 失败
break;
case 242://日期出错误

errText = "日期必须大于1753年1月1日";
break;
case 7391://启动分布式事务

errText = "启动分布式事务";
break;
case 208://检索的表不存在
errText = "检索的表不存在!";
break;
case 241://日期数据不正确

errText = "日期数据不正确!";
break;
case 999://有打印脚本存在

errText = "有打印脚本存在";
break;
case 1205:// DeadLock Victim
errText = "数据库发生死锁，如果是偶然发生此问题，请关闭页面或关闭程序后，重新执行当前操作，如果是经常发生此错误，请将发生此错误的具体情况形成文档并适当截图，发送给管理员！";
break;
case 8114:
errText = "数据类型转化错误，如某字段为必填项，但用户未进行填写而保持其默认空值时，经常会发生此问题！";
break;
case 8152:
errText = "当前提交的数据中某列长度超出原要求!";
break;
default:
errText = "发生未知的数据库执行脚本错误，可能是必填数据未进行填写造成，/n更多错误信息请关注以下内容："
+ sqlException.Message;
break;
}
return errText;
}