【转载】微软官方提供的Sqlserver数据库操作帮助类SQLHelper类
程序员文章站
2022-04-08 21:47:22
在.NET平台中，C#语言一般使用ADO.NET组件来操作Sqlserver数据库，通过ADO.NET组件可以实现连接数据库、查询数据集、执行SQL语句以及关闭数据库连接等操作，为此网上有很多开发者自定义封装好的SqlHelper类供使用，其实微软官方自己也提供了一个比较完整的Sqlserver数据 ......
在.net平台中，c#语言一般使用ado.net组件来操作sqlserver数据库，通过ado.net组件可以实现连接数据库、查询数据集、执行sql语句以及关闭数据库连接等操作，为此网上有很多开发者自定义封装好的sqlhelper类供使用，其实微软官方自己也提供了一个比较完整的sqlserver数据库操作帮助类sqlhelper，该帮助类文件中包含了详细的中文注释。
该sqlhelper类比较完整，代码量比较多，因此博主加了个链接提供个读者下载，百度网盘下载地址：sqlhelper类。
备注：此文章转载自微软官方提供的sqlserver数据库操作帮助类sqlhelper类_it技术小趣屋。
具体微软官方帮助类实现如下：
using system;
using system.data;
using system.xml;
using system.data.sqlclient;
using system.collections;
namespace book.dal
{
/// <summary>
/// sqlserver数据访问帮助类
/// </summary>
public sealed class sqlhelper
{
#region 私有构造函数和方法
private sqlhelper() {}
/// <summary>
/// 将sqlparameter参数数组(参数值)分配给sqlcommand命令.
/// 这个方法将给任何一个参数分配dbnull.value;
/// 该操作将阻止默认值的使用.
/// </summary>
/// <param>命令名</param>
/// <param>sqlparameters数组</param>
private static void attachparameters(sqlcommand command, sqlparameter[] commandparameters)
{
if( command == null ) throw new argumentnullexception( "command" );
if( commandparameters != null )
{
foreach (sqlparameter p in commandparameters)
{
if( p != null )
{
// 检查未分配值的输出参数,将其分配以dbnull.value.
if ( ( p.direction == parameterdirection.inputoutput || p.direction == parameterdirection.input ) &&
(p.value == null))
{
p.value = dbnull.value;
}
command.parameters.add(p);
}
}
}
}
/// <summary>
/// 将datarow类型的列值分配到sqlparameter参数数组.
/// </summary>
/// <param>要分配值的sqlparameter参数数组</param>
/// <param>将要分配给存储过程参数的datarow</param>
private static void assignparametervalues(sqlparameter[] commandparameters, datarow datarow)
{
if ((commandparameters == null) || (datarow == null))
{
return;
}
int i = 0;
// 设置参数值
foreach(sqlparameter commandparameter in commandparameters)
{
// 创建参数名称,如果不存在,只抛出一个异常.
if( commandparameter.parametername == null ||
commandparameter.parametername.length <= 1 )
throw new exception(
string.format("请提供参数{0}一个有效的名称{1}.", i, commandparameter.parametername ) );
// 从datarow的表中获取为参数数组中数组名称的列的索引.
// 如果存在和参数名称相同的列,则将列值赋给当前名称的参数.
if (datarow.table.columns.indexof(commandparameter.parametername.substring(1)) != -1)
commandparameter.value = datarow[commandparameter.parametername.substring(1)];
i++;
}
}
/// <summary>
/// 将一个对象数组分配给sqlparameter参数数组.
/// </summary>
/// <param>要分配值的sqlparameter参数数组</param>
/// <param>将要分配给存储过程参数的对象数组</param>
private static void assignparametervalues(sqlparameter[] commandparameters, object[] parametervalues)
{
if ((commandparameters == null) || (parametervalues == null))
{
return;
}
// 确保对象数组个数与参数个数匹配,如果不匹配,抛出一个异常.
if (commandparameters.length != parametervalues.length)
{
throw new argumentexception("参数值个数与参数不匹配.");
}
// 给参数赋值
for (int i = 0, j = commandparameters.length; i < j; i++)
{
// if the current array value derives from idbdataparameter, then assign its value property
if (parametervalues[i] is idbdataparameter)
{
idbdataparameter paraminstance = (idbdataparameter)parametervalues[i];
if( paraminstance.value == null )
{
commandparameters[i].value = dbnull.value;
}
else
{
commandparameters[i].value = paraminstance.value;
}
}
else if (parametervalues[i] == null)
{
commandparameters[i].value = dbnull.value;
}
else
{
commandparameters[i].value = parametervalues[i];
}
}
}
/// <summary>
/// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
/// </summary>
/// <param>要处理的sqlcommand</param>
/// <param>数据库连接</param>
/// <param>一个有效的事务或者是null值</param>
/// <param>命令类型 (存储过程,命令文本, 其它.)</param>
/// <param>存储过程名或都t-sql命令文本</param>
/// <param>和命令相关联的sqlparameter参数数组,如果没有参数为'null'</param>
/// <param><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
private static void preparecommand(sqlcommand command, sqlconnection connection, sqltransaction transaction, commandtype commandtype, string commandtext, sqlparameter[] commandparameters, out bool mustcloseconnection )
{
if( command == null ) throw new argumentnullexception( "command" );
if( commandtext == null || commandtext.length == 0 ) throw new argumentnullexception( "commandtext" );
// if the provided connection is not open, we will open it
if (connection.state != connectionstate.open)
{
mustcloseconnection = true;
connection.open();
}
else
{
mustcloseconnection = false;
}
// 给命令分配一个数据库连接.
command.connection = connection;
// 设置命令文本(存储过程名或sql语句)
command.commandtext = commandtext;
// 分配事务
if (transaction != null)
{
if( transaction.connection == null ) throw new argumentexception( "the transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
command.transaction = transaction;
}
// 设置命令类型.
command.commandtype = commandtype;
// 分配命令参数
if (commandparameters != null)
{
attachparameters(command, commandparameters);
}
return;
}
#endregion 私有构造函数和方法结束
#region executenonquery命令
/// <summary>
/// 执行指定连接字符串,类型的sqlcommand.
/// </summary>
/// <remarks>
/// 示例:
/// int result = executenonquery(connstring, commandtype.storedprocedure, "publishorders");
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>命令类型 (存储过程,命令文本, 其它.)</param>
/// <param>存储过程名称或sql语句</param>
/// <returns>返回命令影响的行数</returns>
public static int executenonquery(string connectionstring, commandtype commandtype, string commandtext)
{
return executenonquery(connectionstring, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行指定连接字符串,类型的sqlcommand.如果没有提供参数,不返回结果.
/// </summary>
/// <remarks>
/// 示例:
/// int result = executenonquery(connstring, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>命令类型 (存储过程,命令文本, 其它.)</param>
/// <param>存储过程名称或sql语句</param>
/// <param>sqlparameter参数数组</param>
/// <returns>返回命令影响的行数</returns>
public static int executenonquery(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
using (sqlconnection connection = new sqlconnection(connectionstring))
{
connection.open();
return executenonquery(connection, commandtype, commandtext, commandparameters);
}
}
/// <summary>
/// 执行指定连接字符串的存储过程,将对象数组的值赋给存储过程参数,
/// 此方法需要在参数缓存方法中探索参数并生成参数.
/// </summary>
/// <remarks>
/// 这个方法没有提供访问输出参数和返回值.
/// 示例:
/// int result = executenonquery(connstring, "publishorders", 24, 36);
/// </remarks>
/// <param>一个有效的数据库连接字符串/param>
/// <param>存储过程名称</param>
/// <param>分配到存储过程输入参数的对象数组</param>
/// <returns>返回受影响的行数</returns>
public static int executenonquery(string connectionstring, string spname, params object[] parametervalues)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// 如果存在参数值
if ((parametervalues != null) && (parametervalues.length > 0))
{
// 从探索存储过程参数(加载到缓存)并分配给存储过程参数数组.
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
// 给存储过程参数赋值
assignparametervalues(commandparameters, parametervalues);
return executenonquery(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// 没有参数情况下
return executenonquery(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// 执行指定数据库连接对象的命令
/// </summary>
/// <remarks>
/// 示例:
/// int result = executenonquery(conn, commandtype.storedprocedure, "publishorders");
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型(存储过程,命令文本或其它.)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <returns>返回影响的行数</returns>
public static int executenonquery(sqlconnection connection, commandtype commandtype, string commandtext)
{
return executenonquery(connection, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行指定数据库连接对象的命令
/// </summary>
/// <remarks>
/// 示例:
/// int result = executenonquery(conn, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型(存储过程,命令文本或其它.)</param>
/// <param>t存储过程名称或t-sql语句</param>
/// <param>sqlparamter参数数组</param>
/// <returns>返回影响的行数</returns>
public static int executenonquery(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connection == null ) throw new argumentnullexception( "connection" );
// 创建sqlcommand命令,并进行预处理
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters, out mustcloseconnection );
// finally, execute the command
int retval = cmd.executenonquery();
// 清除参数,以便再次使用.
cmd.parameters.clear();
if( mustcloseconnection )
connection.close();
return retval;
}
/// <summary>
/// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值
/// 示例:
/// int result = executenonquery(conn, "publishorders", 24, 36);
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>存储过程名</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回影响的行数</returns>
public static int executenonquery(sqlconnection connection, string spname, params object[] parametervalues)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// 如果有参数值
if ((parametervalues != null) && (parametervalues.length > 0))
{
// 从缓存中加载存储过程参数
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// 给存储过程分配参数值
assignparametervalues(commandparameters, parametervalues);
return executenonquery(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return executenonquery(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// 执行带事务的sqlcommand.
/// </summary>
/// <remarks>
/// 示例.:
/// int result = executenonquery(trans, commandtype.storedprocedure, "publishorders");
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型(存储过程,命令文本或其它.)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <returns>返回影响的行数/returns>
public static int executenonquery(sqltransaction transaction, commandtype commandtype, string commandtext)
{
return executenonquery(transaction, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行带事务的sqlcommand(指定参数).
/// </summary>
/// <remarks>
/// 示例:
/// int result = executenonquery(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型(存储过程,命令文本或其它.)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <param>sqlparamter参数数组</param>
/// <returns>返回影响的行数</returns>
public static int executenonquery(sqltransaction transaction, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( transaction == null ) throw new argumentnullexception( "transaction" );
if( transaction != null && transaction.connection == null ) throw new argumentexception( "the transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
// 预处理
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection );
// 执行
int retval = cmd.executenonquery();
// 清除参数集,以便再次使用.
cmd.parameters.clear();
return retval;
}
/// <summary>
/// 执行带事务的sqlcommand(指定参数值).
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值
/// 示例:
/// int result = executenonquery(conn, trans, "publishorders", 24, 36);
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>存储过程名</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回受影响的行数</returns>
public static int executenonquery(sqltransaction transaction, string spname, params object[] parametervalues)
{
if( transaction == null ) throw new argumentnullexception( "transaction" );
if( transaction != null && transaction.connection == null ) throw new argumentexception( "the transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// 如果有参数值
if ((parametervalues != null) && (parametervalues.length > 0))
{
// 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// 给存储过程参数赋值
assignparametervalues(commandparameters, parametervalues);
// 调用重载方法
return executenonquery(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// 没有参数值
return executenonquery(transaction, commandtype.storedprocedure, spname);
}
}
#endregion executenonquery方法结束
#region executedataset方法
/// <summary>
/// 执行指定数据库连接字符串的命令,返回dataset.
/// </summary>
/// <remarks>
/// 示例:
/// dataset ds = executedataset(connstring, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <returns>返回一个包含结果集的dataset</returns>
public static dataset executedataset(string connectionstring, commandtype commandtype, string commandtext)
{
return executedataset(connectionstring, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行指定数据库连接字符串的命令,返回dataset.
/// </summary>
/// <remarks>
/// 示例:
/// dataset ds = executedataset(connstring, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <param>sqlparamters参数数组</param>
/// <returns>返回一个包含结果集的dataset</returns>
public static dataset executedataset(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
// 创建并打开数据库连接对象,操作完成释放对象.
using (sqlconnection connection = new sqlconnection(connectionstring))
{
connection.open();
// 调用指定数据库连接字符串重载方法.
return executedataset(connection, commandtype, commandtext, commandparameters);
}
}
/// <summary>
/// 执行指定数据库连接字符串的命令,直接提供参数值,返回dataset.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值.
/// 示例:
/// dataset ds = executedataset(connstring, "getorders", 24, 36);
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>存储过程名</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回一个包含结果集的dataset</returns>
public static dataset executedataset(string connectionstring, string spname, params object[] parametervalues)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
if ((parametervalues != null) && (parametervalues.length > 0))
{
// 从缓存中检索存储过程参数
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
// 给存储过程参数分配值
assignparametervalues(commandparameters, parametervalues);
return executedataset(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return executedataset(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// 执行指定数据库连接对象的命令,返回dataset.
/// </summary>
/// <remarks>
/// 示例:
/// dataset ds = executedataset(conn, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名或t-sql语句</param>
/// <returns>返回一个包含结果集的dataset</returns>
public static dataset executedataset(sqlconnection connection, commandtype commandtype, string commandtext)
{
return executedataset(connection, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回dataset.
/// </summary>
/// <remarks>
/// 示例:
/// dataset ds = executedataset(conn, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名或t-sql语句</param>
/// <param>sqlparamter参数数组</param>
/// <returns>返回一个包含结果集的dataset</returns>
public static dataset executedataset(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connection == null ) throw new argumentnullexception( "connection" );
// 预处理
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters, out mustcloseconnection );
// 创建sqldataadapter和dataset.
using( sqldataadapter da = new sqldataadapter(cmd) )
{
dataset ds = new dataset();
// 填充dataset.
da.fill(ds);
cmd.parameters.clear();
if( mustcloseconnection )
connection.close();
return ds;
}
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定参数值,返回dataset.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输入参数和返回值.
/// 示例.:
/// dataset ds = executedataset(conn, "getorders", 24, 36);
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>存储过程名</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回一个包含结果集的dataset</returns>
public static dataset executedataset(sqlconnection connection, string spname, params object[] parametervalues)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
if ((parametervalues != null) && (parametervalues.length > 0))
{
// 比缓存中加载存储过程参数
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// 给存储过程参数分配值
assignparametervalues(commandparameters, parametervalues);
return executedataset(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return executedataset(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// 执行指定事务的命令,返回dataset.
/// </summary>
/// <remarks>
/// 示例:
/// dataset ds = executedataset(trans, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param>事务</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名或t-sql语句</param>
/// <returns>返回一个包含结果集的dataset</returns>
public static dataset executedataset(sqltransaction transaction, commandtype commandtype, string commandtext)
{
return executedataset(transaction, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行指定事务的命令,指定参数,返回dataset.
/// </summary>
/// <remarks>
/// 示例:
/// dataset ds = executedataset(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>事务</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名或t-sql语句</param>
/// <param>sqlparamter参数数组</param>
/// <returns>返回一个包含结果集的dataset</returns>
public static dataset executedataset(sqltransaction transaction, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( transaction == null ) throw new argumentnullexception( "transaction" );
if( transaction != null && transaction.connection == null ) throw new argumentexception( "the transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
// 预处理
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection );
// 创建 dataadapter & dataset
using( sqldataadapter da = new sqldataadapter(cmd) )
{
dataset ds = new dataset();
da.fill(ds);
cmd.parameters.clear();
return ds;
}
}
/// <summary>
/// 执行指定事务的命令,指定参数值,返回dataset.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输入参数和返回值.
/// 示例.:
/// dataset ds = executedataset(trans, "getorders", 24, 36);
/// </remarks>
/// <param>事务</param>
/// <param>存储过程名</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回一个包含结果集的dataset</returns>
public static dataset executedataset(sqltransaction transaction, string spname, params object[] parametervalues)
{
if( transaction == null ) throw new argumentnullexception( "transaction" );
if( transaction != null && transaction.connection == null ) throw new argumentexception( "the transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
if ((parametervalues != null) && (parametervalues.length > 0))
{
// 从缓存中加载存储过程参数
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// 给存储过程参数分配值
assignparametervalues(commandparameters, parametervalues);
return executedataset(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return executedataset(transaction, commandtype.storedprocedure, spname);
}
}
#endregion executedataset数据集命令结束
#region executereader 数据阅读器
/// <summary>
/// 枚举,标识数据库连接是由sqlhelper提供还是由调用者提供
/// </summary>
private enum sqlconnectionownership
{
/// <summary>由sqlhelper提供连接</summary>
internal,
/// <summary>由调用者提供连接</summary>
external
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <remarks>
/// 如果是sqlhelper打开连接,当连接关闭datareader也将关闭.
/// 如果是调用都打开连接,datareader由调用都管理.
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>一个有效的事务,或者为 'null'</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名或t-sql语句</param>
/// <param>sqlparameters参数数组,如果没有参数则为'null'</param>
/// <param>标识数据库连接对象是由调用者提供还是由sqlhelper提供</param>
/// <returns>返回包含结果集的sqldatareader</returns>
private static sqldatareader executereader(sqlconnection connection, sqltransaction transaction, commandtype commandtype, string commandtext, sqlparameter[] commandparameters, sqlconnectionownership connectionownership)
{
if( connection == null ) throw new argumentnullexception( "connection" );
bool mustcloseconnection = false;
// 创建命令
sqlcommand cmd = new sqlcommand();
try
{
preparecommand(cmd, connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection );
// 创建数据阅读器
sqldatareader datareader;
if (connectionownership == sqlconnectionownership.external)
{
datareader = cmd.executereader();
}
else
{
datareader = cmd.executereader(commandbehavior.closeconnection);
}
// 清除参数,以便再次使用..
// hack: there is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the sqlreader can磘 set its values.
// when this happen, the parameters can磘 be used again in other command.
bool canclear = true;
foreach(sqlparameter commandparameter in cmd.parameters)
{
if (commandparameter.direction != parameterdirection.input)
canclear = false;
}
if (canclear)
{
cmd.parameters.clear();
}
return datareader;
}
catch
{
if( mustcloseconnection )
connection.close();
throw;
}
}
/// <summary>
/// 执行指定数据库连接字符串的数据阅读器.
/// </summary>
/// <remarks>
/// 示例:
/// sqldatareader dr = executereader(connstring, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名或t-sql语句</param>
/// <returns>返回包含结果集的sqldatareader</returns>
public static sqldatareader executereader(string connectionstring, commandtype commandtype, string commandtext)
{
return executereader(connectionstring, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行指定数据库连接字符串的数据阅读器,指定参数.
/// </summary>
/// <remarks>
/// 示例:
/// sqldatareader dr = executereader(connstring, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名或t-sql语句</param>
/// <param>sqlparamter参数数组(new sqlparameter("@prodid", 24))</param>
/// <returns>返回包含结果集的sqldatareader</returns>
public static sqldatareader executereader(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
sqlconnection connection = null;
try
{
connection = new sqlconnection(connectionstring);
connection.open();
return executereader(connection, null, commandtype, commandtext, commandparameters,sqlconnectionownership.internal);
}
catch
{
// if we fail to return the sqldatreader, we need to close the connection ourselves
if( connection != null ) connection.close();
throw;
}
}
/// <summary>
/// 执行指定数据库连接字符串的数据阅读器,指定参数值.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值参数.
/// 示例:
/// sqldatareader dr = executereader(connstring, "getorders", 24, 36);
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>存储过程名</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回包含结果集的sqldatareader</returns>
public static sqldatareader executereader(string connectionstring, string spname, params object[] parametervalues)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
if ((parametervalues != null) && (parametervalues.length > 0))
{
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
assignparametervalues(commandparameters, parametervalues);
return executereader(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return executereader(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <remarks>
/// 示例:
/// sqldatareader dr = executereader(conn, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名或t-sql语句</param>
/// <returns>返回包含结果集的sqldatareader</returns>
public static sqldatareader executereader(sqlconnection connection, commandtype commandtype, string commandtext)
{
return executereader(connection, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数.
/// </summary>
/// <remarks>
/// 示例:
/// sqldatareader dr = executereader(conn, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>sqlparamter参数数组</param>
/// <returns>返回包含结果集的sqldatareader</returns>
public static sqldatareader executereader(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
return executereader(connection, (sqltransaction)null, commandtype, commandtext, commandparameters, sqlconnectionownership.external);
}
/// <summary>
/// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数值.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值参数.
/// 示例:
/// sqldatareader dr = executereader(conn, "getorders", 24, 36);
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>t存储过程名</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回包含结果集的sqldatareader</returns>
public static sqldatareader executereader(sqlconnection connection, string spname, params object[] parametervalues)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
if ((parametervalues != null) && (parametervalues.length > 0))
{
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
assignparametervalues(commandparameters, parametervalues);
return executereader(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return executereader(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值.
/// </summary>
/// <remarks>
/// 示例:
/// sqldatareader dr = executereader(trans, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param>一个有效的连接事务</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <returns>返回包含结果集的sqldatareader</returns>
public static sqldatareader executereader(sqltransaction transaction, commandtype commandtype, string commandtext)
{
return executereader(transaction, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// [调用者方式]执行指定数据库事务的数据阅读器,指定参数.
/// </summary>
/// <remarks>
/// 示例:
/// sqldatareader dr = executereader(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的连接事务</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <param>分配给命令的sqlparamter参数数组</param>
/// <returns>返回包含结果集的sqldatareader</returns>
public static sqldatareader executereader(sqltransaction transaction, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( transaction == null ) throw new argumentnullexception( "transaction" );
if( transaction != null && transaction.connection == null ) throw new argumentexception( "the transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
return executereader(transaction.connection, transaction, commandtype, commandtext, commandparameters, sqlconnectionownership.external);
}
/// <summary>
/// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值参数.
///
/// 示例:
/// sqldatareader dr = executereader(trans, "getorders", 24, 36);
/// </remarks>
/// <param>一个有效的连接事务</param>
/// <param>存储过程名称</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回包含结果集的sqldatareader</returns>
public static sqldatareader executereader(sqltransaction transaction, string spname, params object[] parametervalues)
{
if( transaction == null ) throw new argumentnullexception( "transaction" );
if( transaction != null && transaction.connection == null ) throw new argumentexception( "the transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// 如果有参数值
if ((parametervalues != null) && (parametervalues.length > 0))
{
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
assignparametervalues(commandparameters, parametervalues);
return executereader(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// 没有参数值
return executereader(transaction, commandtype.storedprocedure, spname);
}
}
#endregion executereader数据阅读器
#region executescalar 返回结果集中的第一行第一列
/// <summary>
/// 执行指定数据库连接字符串的命令,返回结果集中的第一行第一列.
/// </summary>
/// <remarks>
/// 示例:
/// int ordercount = (int)executescalar(connstring, commandtype.storedprocedure, "getordercount");
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object executescalar(string connectionstring, commandtype commandtype, string commandtext)
{
// 执行参数为空的方法
return executescalar(connectionstring, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行指定数据库连接字符串的命令,指定参数,返回结果集中的第一行第一列.
/// </summary>
/// <remarks>
/// 示例:
/// int ordercount = (int)executescalar(connstring, commandtype.storedprocedure, "getordercount", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <param>分配给命令的sqlparamter参数数组</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object executescalar(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
// 创建并打开数据库连接对象,操作完成释放对象.
using (sqlconnection connection = new sqlconnection(connectionstring))
{
connection.open();
// 调用指定数据库连接字符串重载方法.
return executescalar(connection, commandtype, commandtext, commandparameters);
}
}
/// <summary>
/// 执行指定数据库连接字符串的命令,指定参数值,返回结果集中的第一行第一列.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值参数.
///
/// 示例:
/// int ordercount = (int)executescalar(connstring, "getordercount", 24, 36);
/// </remarks>
/// <param>一个有效的数据库连接字符串</param>
/// <param>存储过程名称</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object executescalar(string connectionstring, string spname, params object[] parametervalues)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// 如果有参数值
if ((parametervalues != null) && (parametervalues.length > 0))
{
// 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
// 给存储过程参数赋值
assignparametervalues(commandparameters, parametervalues);
// 调用重载方法
return executescalar(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// 没有参数值
return executescalar(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// 执行指定数据库连接对象的命令,返回结果集中的第一行第一列.
/// </summary>
/// <remarks>
/// 示例:
/// int ordercount = (int)executescalar(conn, commandtype.storedprocedure, "getordercount");
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object executescalar(sqlconnection connection, commandtype commandtype, string commandtext)
{
// 执行参数为空的方法
return executescalar(connection, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
/// </summary>
/// <remarks>
/// 示例:
/// int ordercount = (int)executescalar(conn, commandtype.storedprocedure, "getordercount", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <param>分配给命令的sqlparamter参数数组</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object executescalar(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connection == null ) throw new argumentnullexception( "connection" );
// 创建sqlcommand命令,并进行预处理
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters, out mustcloseconnection );
// 执行sqlcommand命令,并返回结果.
object retval = cmd.executescalar();
// 清除参数,以便再次使用.
cmd.parameters.clear();
if( mustcloseconnection )
connection.close();
return retval;
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定参数值,返回结果集中的第一行第一列.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值参数.
///
/// 示例:
/// int ordercount = (int)executescalar(conn, "getordercount", 24, 36);
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>存储过程名称</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object executescalar(sqlconnection connection, string spname, params object[] parametervalues)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// 如果有参数值
if ((parametervalues != null) && (parametervalues.length > 0))
{
// 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// 给存储过程参数赋值
assignparametervalues(commandparameters, parametervalues);
// 调用重载方法
return executescalar(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// 没有参数值
return executescalar(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// 执行指定数据库事务的命令,返回结果集中的第一行第一列.
/// </summary>
/// <remarks>
/// 示例:
/// int ordercount = (int)executescalar(trans, commandtype.storedprocedure, "getordercount");
/// </remarks>
/// <param>一个有效的连接事务</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object executescalar(sqltransaction transaction, commandtype commandtype, string commandtext)
{
// 执行参数为空的方法
return executescalar(transaction, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行指定数据库事务的命令,指定参数,返回结果集中的第一行第一列.
/// </summary>
/// <remarks>
/// 示例:
/// int ordercount = (int)executescalar(trans, commandtype.storedprocedure, "getordercount", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的连接事务</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句</param>
/// <param>分配给命令的sqlparamter参数数组</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object executescalar(sqltransaction transaction, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( transaction == null ) throw new argumentnullexception( "transaction" );
if( transaction != null && transaction.connection == null ) throw new argumentexception( "the transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
// 创建sqlcommand命令,并进行预处理
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection );
// 执行sqlcommand命令,并返回结果.
object retval = cmd.executescalar();
// 清除参数,以便再次使用.
cmd.parameters.clear();
return retval;
}
/// <summary>
/// 执行指定数据库事务的命令,指定参数值,返回结果集中的第一行第一列.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值参数.
///
/// 示例:
/// int ordercount = (int)executescalar(trans, "getordercount", 24, 36);
/// </remarks>
/// <param>一个有效的连接事务</param>
/// <param>存储过程名称</param>
/// <param>分配给存储过程输入参数的对象数组</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object executescalar(sqltransaction transaction, string spname, params object[] parametervalues)
{
if( transaction == null ) throw new argumentnullexception( "transaction" );
if( transaction != null && transaction.connection == null ) throw new argumentexception( "the transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// 如果有参数值
if ((parametervalues != null) && (parametervalues.length > 0))
{
// ppull the parameters for this stored procedure from the parameter cache ()
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// 给存储过程参数赋值
assignparametervalues(commandparameters, parametervalues);
// 调用重载方法
return executescalar(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// 没有参数值
return executescalar(transaction, commandtype.storedprocedure, spname);
}
}
#endregion executescalar
#region executexmlreader xml阅读器
/// <summary>
/// 执行指定数据库连接对象的sqlcommand命令,并产生一个xmlreader对象做为结果集返回.
/// </summary>
/// <remarks>
/// 示例:
/// xmlreader r = executexmlreader(conn, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名称或t-sql语句 using "for xml auto"</param>
/// <returns>返回xmlreader结果集对象.</returns>
public static xmlreader executexmlreader(sqlconnection connection, commandtype commandtype, string commandtext)
{
// 执行参数为空的方法
return executexmlreader(connection, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// 执行指定数据库连接对象的sqlcommand命令,并产生一个xmlreader对象做为结果集返回,指定参数.
/// </summary>
/// <remarks>
/// 示例:
/// xmlreader r = executexmlreader(conn, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param>一个有效的数据库连接对象</param>
/// <param>命令类型 (存储过程,命令文本或其它)</param>
/// <param>存储过程名
赞 (0)
打赏
微信扫一扫
相关文章:
-
-
nuget引用 "NEST" new一个客户端 源码可查 "ElasticClient.cs" new一个ElasticClient有多种方式 第一... [阅读全文]
-
网爆自媒体企鹅账号被无缘无故盗了2个多月，而2个月内盗号者靠发布粗制滥造的娱乐文天天获得平台补贴收益，足足赚了7万多，其背后不止“做号”简单，作者顺带牵扯出... [阅读全文] -
今天在IIS中部署ASP.NET网站后，访问网站报错，提示信息为：未能加载文件或程序集XXX.dll或它的某一个依赖项，不是有效的Win32应用程序... [阅读全文]
-
1、什么是Mybatis？ （1）Mybatis是一个半ORM（对象关系映射）框架，它内部封装了JDBC，开发时只需要关注SQL语句本身，不需要花费... [阅读全文]
-
版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。
上一篇： 查找文献的常用方法
发表评论