安德烈·拉托雷,杨明星的照片,warkey1.07
如何使用c# 操作存储过程，执行sql语句？
闲话不多说，直接上代码：
/// <summary>
/// sql通用类
/// </summary>
public class sqlhelper
{
首先配置连接字符串
public static string connstr = configurationmanager.connectionstrings["connstring"].connectionstring;//connstring表示webconfig中的连接字符串
执行存储过程不设置超时时间 /// <summary>
/// 调用存储过程
/// </summary>
/// <param name="connstr">连接字符串</param>
/// <param name="storedprocedurename">存储过程名称</param>
/// <param name="responsebool">存储过程执行状态</param>
/// <param name="responsemsg">执行存储过程状态描述</param>
/// <param name="paramsobject">存储过程输入参数</param>
/// <returns></returns>
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 < paramsobject.count(); 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;
}
当存储过程执行时间太长时，存储过程的默认超时时间是30s，需要设置存储过程执行超时时间
/// <summary>
/// 调用存储过程 (自定义超时时间)
/// </summary>
/// <param name="connstr">连接字符串</param>
/// <param name="storedprocedurename">存储过程名称</param>
/// <param name="commandouttime">执行存储过程请求超时时间(单位：s)</param>
/// <param name="responsebool">存储过程执行状态</param>
/// <param name="responsemsg">执行存储过程状态描述</param>
/// <param name="paramsobject">存储过程输入参数</param>
/// <returns></returns>
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 < paramsobject.count(); 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语句，进行增删改操作
/// <summary>
/// 增删改数据
/// </summary>
/// <param name="sqlconnstr, ">数据库连接字符串</param>
/// <param name="sql">执行的sql语句</param>
/// <param name="paramsobject">输入参数</param>
/// <returns></returns>
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 < paramsobject.count(); i++)
{
sqlparameter sqlparameter = new sqlparameter(paramsobject[i].key, paramsobject[i].value);
cmd.parameters.add(sqlparameter);
}
}
count = cmd.executenonquery(); //执行语句
conn.close(); //关闭连接
cmd = null;
conn.dispose(); //释放对象
}
return count;
}
当数据库中表关系及其复杂，并且数据量特别多的时候(一般情况下用缓存解决问题)，执行sql查询语句相当耗时，需要设置sql语句请求超时时间。
执行sql查询语句，设置sql查询语句超时时间 /// <summary>
/// 执行sql脚本
/// </summary>
/// <param name="connstr">连接字符串</param>
/// <param name="sqlscript">sql脚本</param>
/// <param name="responsebool">执行状态</param>
/// <param name="responsemsg">状态描述</param>
/// <param name="commandouttime">执行sql语句请求超时时间(单位：s)</param>
/// <param name="paramsobject">输入参数</param>
/// <returns></returns>
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 < paramsobject.count(); 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;
}
入参实体建类
/// <summary>
/// 输入参数实体 参数名称(key)/参数值(value)
/// </summary>
public class parameterkeyvaluesentity
{
/// <summary>
/// 参数名称
/// </summary>
public string key { get; set; }
/// <summary>
/// 参数值
/// </summary>
public object value { get; set; }
}
执行存储过程示例：
public result 方法名(string 入参1,string 入参2, string 入参3)
{
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}");
}
}
the end.......................
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！
点击进行留言回复
我要评论
网友评论