C#执行存储过程
2021-06-09 20:05
标签：返回值 命令 fill 关闭 comm mes ndt 时间 nbsp 闲话不多说，直接上代码： C#执行存储过程 标签：返回值 命令 fill 关闭 comm mes ndt 时间 nbsp 原文地址：https://www.cnblogs.com/qiu18359243869/p/10642207.html
/// Sql通用类
///
public class SqlHelper
{
public static string connStr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;//ConnString表示webconfig中的连接字符串
///
/// 调用存储过程
///
/// 连接字符串
/// 存储过程名称
/// 存储过程执行状态
/// 执行存储过程状态描述
/// 存储过程输入参数
///
public static DataSet Sql_GetStoredProcedureFunction(string connStr, string storedProcedureName, out bool ResponseBool, out string ResponseMsg, params ParameterKeyValuesEntity[] paramsObject)
{
DataSet ResponseDs = new DataSet();
ResponseBool = true;
ResponseMsg = "获取成功！";
try
{
using (SqlConnection sqlConn = new SqlConnection(connStr))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand(storedProcedureName, sqlConn))
{
sqlCmd.CommandType = CommandType.StoredProcedure;
if (paramsObject.Count() > 0)
{
for (int i = 0; i {
SqlParameter sqlParameter = new SqlParameter(paramsObject[i].Key, paramsObject[i].Value);
sqlCmd.Parameters.Add(sqlParameter);
}
}
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
sda.Fill(ResponseDs);
}
}
}
catch (Exception e)
{
ResponseBool = false;
ResponseMsg = $"查询存储过程时出现异常，存储过程：【{storedProcedureName}】\n 异常原因：【{e.Message}】\n 异常详细信息：【{e.StackTrace}】！";
}
return ResponseDs;
}
/// 调用存储过程 (自定义超时时间)
///
/// 连接字符串
/// 存储过程名称
/// 执行存储过程请求超时时间(单位：s)
/// 存储过程执行状态
/// 执行存储过程状态描述
/// 存储过程输入参数
///
public static DataSet Sql_GetStoredProcedureFunction(string connStr, string storedProcedureName, int commandOutTime, out bool ResponseBool, out string ResponseMsg, params ParameterKeyValuesEntity[] paramsObject)
{
DataSet ResponseDs = new DataSet();
ResponseBool = true;
ResponseMsg = "获取成功！";
try
{
using (SqlConnection sqlConn = new SqlConnection(connStr))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand(storedProcedureName, sqlConn))
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandTimeout = commandOutTime;
if (paramsObject.Count() > 0)
{
SqlParameter[] sqlParameters = new SqlParameter[paramsObject.Count()];
for (int i = 0; i {
SqlParameter sqlParameter = new SqlParameter(paramsObject[i].Key, paramsObject[i].Value);
sqlCmd.Parameters.Add(sqlParameter);
}
}
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
sda.Fill(ResponseDs);
}
}
}
catch (Exception e)
{
ResponseBool = false;
ResponseMsg = $"查询存储过程时出现异常，存储过程：【{storedProcedureName}】\n 异常原因：【{e.Message}】\n 异常详细信息：【{e.StackTrace}】！";
}
return ResponseDs;
}
/// 增删改数据
///
/// 数据库连接字符串
/// 执行的sql语句
/// 输入参数
///
public static int SQLExecuteData(string sqlConnStr, string sql, params ParameterKeyValuesEntity[] paramsObject)
{
int count = 0;
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn); //定义一个sql操作命令对象
if (paramsObject.Count() > 0)
{
for (int i = 0; i {
SqlParameter sqlParameter = new SqlParameter(paramsObject[i].Key, paramsObject[i].Value);
cmd.Parameters.Add(sqlParameter);
}
}
conn.Close(); //关闭连接
cmd = null;
conn.Dispose(); //释放对象
}
}
///
/// 执行SQL脚本
///
/// 连接字符串
/// SQL脚本
/// 执行状态
/// 状态描述
/// 执行sql语句请求超时时间(单位：s)
/// 输入参数
///
public static DataSet Sql_GetStored(string connStr, string sqlScript, out bool ResponseBool, out string ResponseMsg, int commandOutTime = 500, params ParameterKeyValuesEntity[] paramsObject)
{
DataSet ResponseDs = new DataSet();
ResponseBool = true;
ResponseMsg = "获取成功！";
try
{
using (SqlConnection sqlConn = new SqlConnection(connStr))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand(sqlScript, sqlConn))
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = commandOutTime;
if (paramsObject.Count() > 0)
{
for (int i = 0; i {
SqlParameter sqlParameter = new SqlParameter(paramsObject[i].Key, paramsObject[i].Value);
sqlCmd.Parameters.Add(sqlParameter);
}
}
SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
sda.Fill(ResponseDs);
}
}
}
catch (Exception e)
{
ResponseBool = false;
ResponseMsg = $"查询存储过程时出现异常，SQL脚本：【{sqlScript}】\n 异常原因：【{e.Message}】\n 异常详细信息：【{e.StackTrace}】！";
}
return ResponseDs;
}
///
/// 输入参数实体 参数名称(Key)/参数值(Value)
///
public class ParameterKeyValuesEntity
{
///
/// 参数名称
///
public string Key { get; set; }
///
/// 参数值
///
public object Value { get; set; }
}
{
try
{
//定义输出参数
Result result = new Result();
//存储过程名称
string procName = "存储过程名称";
#region -- 执行存储过程获取数据
//返回值状态
bool responseBool = true;
//返回值状态描述
string responseMsg = string.Empty;
//存储过程输入参数实体
ParameterKeyValuesEntity[] parameterKeyValue = new ParameterKeyValuesEntity[]
{
new ParameterKeyValuesEntity(){Key="@存储过程入参1",Value=赋值1},
new ParameterKeyValuesEntity(){Key="@存储过程入参2",Value=赋值2},
new ParameterKeyValuesEntity(){Key="@存储过程入参3",Value=赋值3},
};
//使用sql通用类的方法执行存储过程
DataSet ds = SqlHelper.Sql_GetStoredProcedureFunction(connStr, procName, out responseBool, out responseMsg, parameterKeyValue);
if (!responseBool)
{
result.code = "204";
result.msg = $"查询存储过程时出现异常，异常信息：{responseMsg}";
ExceptionLogHelper.WriteLog($"业务异常：存储过程名：{procName}---异常信息：{responseMsg}");//项目中的异常日志
return result;
}
DataTable dt = ds.Tables[0];
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
获取存储过程执行后的数据，给实体类赋值
}
#endregion
result.data = loopbackdata;
string json = JsonConvert.SerializeObject(result.data);
result = ResultHelper.ReturnResultSuccess(json, typeof(JObject));
return result;
}
catch (Exception e)
{
ExceptionLogHelper.WriteLog($"业务异常：{e}");
return ResultHelper.ReturnResultError($"异常信息：{e}");
}
}
上一篇：WPF定时刷新UI界面