调用时如下:
/// <param name="newtreeview">树型控件名称</param>
/// <param name="treeviewname">一层的功能名称</param>
/// <param name="tablename">数据库中的表名</param>
/// <param name="parentnamefield">父节点的字段名</param>
/// <param name="currentnamefield">节点的字段名</param>
/// <param name="currentdatafield">节点的数据</param>
newtreeinfobll.inittreedata(this.treeview1,"系统业务表","t_s_systemtableindex","pid000","name00","id0000");
有什么不懂的或不明白的地方请大家给我发email,谢谢,希望大家一起进步
---------------------------------
treeinfo.cs = 数据控制层
using system;
using system.windows.forms;
using system.data;
using system.data.sqlclient;
namespace dal
{
/// <summary>
/// treeinfo
/// 树型初使化数据库的所有信息
/// 乔高峰 2005-04-26
/// 功能:实现所有树型的初使化
/// </summary>
public class treeinfo
{
// private string parm_tablename;
/// <summary>
/// 取某表的所有数据(缺少表名)
/// </summary>
private string sql_select_tree = "select * from ";
/// <summary>
/// 保存结果的数据集
/// scf
/// </summary>
private dataset newdataset;
/// <summary>
/// 为过滤方便的视图
/// </summary>
private dataview newdataview;
/// <summary>
/// 无参数的构造函数
/// </summary>
public treeinfo()
{
}
/// <summary>
/// 初使化树型
/// 乔高峰 2005-04-28
/// </summary>
/// <param name="newtreeview">树型控件名称</param>
/// <param name="treeviewname">一层的功能名称</param>
/// <param name="tablename">数据库中的表名</param>
/// <param name="parentnamefield">父节点的字段名</param>
/// <param name="currentnamefield">节点的字段名</param>
/// <param name="currentdatafield">节点的数据</param>
public void inittreedata(treeview newtreeview,string treeviewname,string tablename,string parentnamefield,string currentnamefield,string currentdatafield)
{
//增加第一层节点的名称,为该树的功能名称
treenode newtreeviewname = new treenode();
//设置该节点的显示文本
newtreeviewname.text = treeviewname;
////树型的图标
//newtreeviewname.imageindex = ;
////选择时的图标
//newtreeviewname.selectedimageindex = ;
newtreeview.nodes.add(newtreeviewname);
//增加第二层数据库里最高层的数据
//从数据库中取数据
try
{
this.newdataset = new dataset();
this.sql_select_tree = this.sql_select_tree + tablename;
sqlhelper.filldataset(sqlhelper.conn_string,commandtype.text,this.sql_select_tree,this.newdataset,new string[] {tablename});
this.newdataview = new dataview();
this.newdataview.table = this.newdataset.tables[tablename];
}
catch(exception ee)
{
messagebox.show(ee.message);
}
createtreenodes(newtreeviewname,parentnamefield,"0",currentnamefield,currentdatafield);
}
/// <summary>
/// 用递归的方法,生成树型
/// 乔高峰 2005-04-28
/// </summary>
/// <param name="newtreeviewname">一层的节点索引</param>
/// <param name="parentnamefield">父节点的字段名</param>
/// <param name="parentnamevalue">父节点的字段值</param>
/// <param name="currentnamefield">节点的字段名</param>
/// <param name="currentdatafield">节点的数据</param>
public void createtreenodes(treenode newtreeviewname,string parentnamefield,string parentnamevalue,string currentnamefield,string currentdatafield)
{
try
{
//规定父节点为0的为第一层节点
this.newdataview.rowfilter = parentnamefield + " = '" + parentnamevalue+"'";
//判断是否有记录
if (this.newdataview.count != 0)
{
//messagebox.show(this.newdataview.count.tostring()+ "运行");
//递归运算
foreach(datarowview newdatarowview in this.newdataview)
{
//在循环外有一条这个语句
//这条语句是为了找回递归时动态失去的数据
this.newdataview.rowfilter = parentnamefield + " = '" + parentnamevalue+"'";
//新增一个节点
treenode newtreenode = new treenode();
//设置该节点的显示文本
newtreenode.text = newdatarowview[currentnamefield].tostring().trim();
//保存该节点的数据 id
newtreenode.tag = newdatarowview[currentdatafield].tostring().trim();
// //树型的图标
// newtreenode.imageindex = ;
// //选择时的图标
// newtreenode.selectedimageindex = ;
//增回节点
newtreeviewname.nodes.add(newtreenode);
//递归运算
createtreenodes(newtreenode,parentnamefield,newdatarowview[currentdatafield].tostring().trim(),currentnamefield,currentdatafield);
}
}
}
catch(exception ee)
{
messagebox.show(ee.message);
}
}
}
-----------------------------------数据访问层----------------------------
//===============================================================================
//数据访问层中最基本的应用程序块(来自微软microsoft application blocks for .net)
//提供所有有关操作sql server操作数据库的功能
//乔高峰 2005-04-27
//===============================================================================
using system;
using system.data;
using system.xml;
using system.data.sqlclient;
using system.collections;
using system.configuration;
using system.windows.forms;
namespace dal
{
/// <summary>
/// the sqlhelper class is intended to encapsulate high performance, scalable best practices for
/// common uses of sqlclient
/// </summary>
public sealed class sqlhelper
{
//联接字符串
public static readonly string conn_string = configurationsettings.appsettings["sqlconnstring"] ;
#region private utility methods & constructors
// since this class provides only static methods, make the default constructor private to prevent
// instances from being created with "new sqlhelper()"
private sqlhelper()
{
}
/// <summary>
/// this method is used to attach array of sqlparameters to a sqlcommand.
///
/// this method will assign a value of dbnull to any parameter with a direction of
/// inputoutput and a value of null.
///
/// this behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as inputoutput)
/// where the user provided no input value.
/// </summary>
/// <param name="command">the command to which the parameters will be added</param>
/// <param name="commandparameters">an array of sqlparameters to be added to command</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 )
{
// check for derived output value with no value assigned
if ( ( p.direction == parameterdirection.inputoutput ||
p.direction == parameterdirection.input ) &&
(p.value == null))
{
p.value = dbnull.value;
}
command.parameters.add(p);
}
}
}
}
/// <summary>
/// this method assigns datarow column values to an array of sqlparameters
/// </summary>
/// <param name="commandparameters">array of sqlparameters to be assigned values</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values</param>
private static void assignparametervalues(sqlparameter[] commandparameters, datarow datarow)
{
if ((commandparameters == null) || (datarow == null))
{
// do nothing if we get no data
return;
}
int i = 0;
// set the parameters values
foreach(sqlparameter commandparameter in commandparameters)
{
// check the parameter name
if( commandparameter.parametername == null ||
commandparameter.parametername.length <= 1 )
throw new exception(
string.format(
"please provide a valid parameter name on the parameter #{0}, the parametername property has the following value: '{1}'.",
i, commandparameter.parametername ) );
if (datarow.table.columns.indexof(commandparameter.parametername.substring(1)) != -1)
commandparameter.value = datarow[commandparameter.parametername.substring(1)];
i++;
}
}
/// <summary>
/// this method assigns an array of values to an array of sqlparameters
/// </summary>
/// <param name="commandparameters">array of sqlparameters to be assigned values</param>
/// <param name="parametervalues">array of objects holding the values to be assigned</param>
private static void assignparametervalues(sqlparameter[] commandparameters, object[] parametervalues)
{
if ((commandparameters == null) || (parametervalues == null))
{
// do nothing if we get no data
return;
}
// we must have the same number of values as we pave parameters to put them in
if (commandparameters.length != parametervalues.length)
{
throw new argumentexception("parameter count does not match parameter value count.");
}
// iterate through the sqlparameters, assigning the values from the corresponding position in the
// value array
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>
/// this method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command
/// </summary>
/// <param name="command">the sqlcommand to be prepared</param>
/// <param name="connection">a valid sqlconnection, on which to execute this command</param>
/// <param name="transaction">a valid sqltransaction, or 'null'</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="mustcloseconnection"><c>true</c> if the connection was opened by the method, otherwose is 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;
}
// associate the connection with the command
command.connection = connection;
// set the command text (stored procedure name or sql statement)
command.commandtext = commandtext;
// if we were provided a transaction, assign it
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;
}
// set the command type
command.commandtype = commandtype;
// attach the command parameters if they are provided
if (commandparameters != null)
{
attachparameters(command, commandparameters);
}
return;
}
#endregion private utility methods & constructors
#region executenonquery
/// <summary>
/// execute a sqlcommand (that returns no resultset and takes no parameters) against the database specified in
/// the connection string
/// </summary>
/// <remarks>
/// e.g.:
/// int result = executenonquery(connstring, commandtype.storedprocedure, "publishorders");
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int executenonquery(string connectionstring, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executenonquery(connectionstring, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters
/// </summary>
/// <remarks>
/// e.g.:
/// int result = executenonquery(connstring, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int executenonquery(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
// create & open a sqlconnection, and dispose of it after we are done
using (sqlconnection connection = new sqlconnection(connectionstring))
{
connection.open();
// call the overload that takes a connection in place of the connection string
return executenonquery(connection, commandtype, commandtext, commandparameters);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns no resultset) against the database specified in
/// the connection string using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = executenonquery(connstring, "publishorders", 24, 36);
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored prcedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an int representing the number of rows affected by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executenonquery(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executenonquery(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a sqlcommand (that returns no resultset and takes no parameters) against the provided sqlconnection.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = executenonquery(conn, commandtype.storedprocedure, "publishorders");
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int executenonquery(sqlconnection connection, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executenonquery(connection, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns no resultset) against the specified sqlconnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = executenonquery(conn, commandtype.storedprocedure, "publishorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int executenonquery(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connection == null ) throw new argumentnullexception( "connection" );
// create a command and prepare it for execution
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();
// detach the sqlparameters from the command object, so they can be used again
cmd.parameters.clear();
if( mustcloseconnection )
connection.close();
return retval;
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns no resultset) against the specified sqlconnection
/// using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = executenonquery(conn, "publishorders", 24, 36);
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an int representing the number of rows affected by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executenonquery(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executenonquery(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a sqlcommand (that returns no resultset and takes no parameters) against the provided sqltransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = executenonquery(trans, commandtype.storedprocedure, "publishorders");
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int executenonquery(sqltransaction transaction, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executenonquery(transaction, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns no resultset) against the specified sqltransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = executenonquery(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</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" );
// create a command and prepare it for execution
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection );
// finally, execute the command
int retval = cmd.executenonquery();
// detach the sqlparameters from the command object, so they can be used again
cmd.parameters.clear();
return retval;
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns no resultset) against the specified
/// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = executenonquery(conn, trans, "publishorders", 24, 36);
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an int representing the number of rows affected by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executenonquery(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executenonquery(transaction, commandtype.storedprocedure, spname);
}
}
#endregion executenonquery
#region executedataset
/// <summary>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// dataset ds = executedataset(connstring, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static dataset executedataset(string connectionstring, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executedataset(connectionstring, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// dataset ds = executedataset(connstring, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static dataset executedataset(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
// create & open a sqlconnection, and dispose of it after we are done
using (sqlconnection connection = new sqlconnection(connectionstring))
{
connection.open();
// call the overload that takes a connection in place of the connection string
return executedataset(connection, commandtype, commandtext, commandparameters);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the database specified in
/// the connection string using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// dataset ds = executedataset(connstring, "getorders", 24, 36);
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a dataset containing the resultset generated by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executedataset(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executedataset(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqlconnection.
/// </summary>
/// <remarks>
/// e.g.:
/// dataset ds = executedataset(conn, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static dataset executedataset(sqlconnection connection, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executedataset(connection, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// dataset ds = executedataset(conn, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static dataset executedataset(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connection == null ) throw new argumentnullexception( "connection" );
// create a command and prepare it for execution
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters, out mustcloseconnection );
// create the dataadapter & dataset
using( sqldataadapter da = new sqldataadapter(cmd) )
{
dataset ds = new dataset();
// fill the dataset using default values for datatable names, etc
da.fill(ds);
// detach the sqlparameters from the command object, so they can be used again
cmd.parameters.clear();
if( mustcloseconnection )
connection.close();
// return the dataset
return ds;
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// dataset ds = executedataset(conn, "getorders", 24, 36);
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a dataset containing the resultset generated by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executedataset(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executedataset(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqltransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// dataset ds = executedataset(trans, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static dataset executedataset(sqltransaction transaction, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executedataset(transaction, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the specified sqltransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// dataset ds = executedataset(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</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" );
// create a command and prepare it for execution
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection );
// create the dataadapter & dataset
using( sqldataadapter da = new sqldataadapter(cmd) )
{
dataset ds = new dataset();
// fill the dataset using default values for datatable names, etc
da.fill(ds);
// detach the sqlparameters from the command object, so they can be used again
cmd.parameters.clear();
// return the dataset
return ds;
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified
/// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// dataset ds = executedataset(trans, "getorders", 24, 36);
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a dataset containing the resultset generated by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executedataset(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executedataset(transaction, commandtype.storedprocedure, spname);
}
}
#endregion executedataset
#region executereader
/// <summary>
/// this enum is used to indicate whether the connection was provided by the caller, or created by sqlhelper, so that
/// we can set the appropriate commandbehavior when calling executereader()
/// </summary>
private enum sqlconnectionownership
{
/// <summary>connection is owned and managed by sqlhelper</summary>
internal,
/// <summary>connection is owned and managed by the caller</summary>
external
}
/// <summary>
/// create and prepare a sqlcommand, and call executereader with the appropriate commandbehavior.
/// </summary>
/// <remarks>
/// if we created and opened the connection, we want the connection to be closed when the datareader is closed.
///
/// if the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">a valid sqlconnection, on which to execute this command</param>
/// <param name="transaction">a valid sqltransaction, or 'null'</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionownership">indicates whether the connection parameter was provided by the caller, or created by sqlhelper</param>
/// <returns>sqldatareader containing the results of the command</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;
// create a command and prepare it for execution
sqlcommand cmd = new sqlcommand();
try
{
preparecommand(cmd, connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection );
// create a reader
sqldatareader datareader;
// call executereader with the appropriate commandbehavior
if (connectionownership == sqlconnectionownership.external)
{
datareader = cmd.executereader();
}
else
{
datareader = cmd.executereader(commandbehavior.closeconnection);
}
// detach the sqlparameters from the command object, so they can be used again.
// 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>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// sqldatareader dr = executereader(connstring, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>a sqldatareader containing the resultset generated by the command</returns>
public static sqldatareader executereader(string connectionstring, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executereader(connectionstring, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// sqldatareader dr = executereader(connstring, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>a sqldatareader containing the resultset generated by the command</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();
// call the private overload that takes an internally owned connection in place of the connection string
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>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the database specified in
/// the connection string using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// sqldatareader dr = executereader(connstring, "getorders", 24, 36);
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a sqldatareader containing the resultset generated by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
assignparametervalues(commandparameters, parametervalues);
return executereader(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executereader(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqlconnection.
/// </summary>
/// <remarks>
/// e.g.:
/// sqldatareader dr = executereader(conn, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>a sqldatareader containing the resultset generated by the command</returns>
public static sqldatareader executereader(sqlconnection connection, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executereader(connection, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// sqldatareader dr = executereader(conn, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>a sqldatareader containing the resultset generated by the command</returns>
public static sqldatareader executereader(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
// pass through the call to the private overload using a null transaction value and an externally owned connection
return executereader(connection, (sqltransaction)null, commandtype, commandtext, commandparameters, sqlconnectionownership.external);
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// sqldatareader dr = executereader(conn, "getorders", 24, 36);
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a sqldatareader containing the resultset generated by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
assignparametervalues(commandparameters, parametervalues);
return executereader(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executereader(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqltransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// sqldatareader dr = executereader(trans, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>a sqldatareader containing the resultset generated by the command</returns>
public static sqldatareader executereader(sqltransaction transaction, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executereader(transaction, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the specified sqltransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// sqldatareader dr = executereader(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>a sqldatareader containing the resultset generated by the command</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" );
// pass through to private overload, indicating that the connection is owned by the caller
return executereader(transaction.connection, transaction, commandtype, commandtext, commandparameters, sqlconnectionownership.external);
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified
/// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// sqldatareader dr = executereader(trans, "getorders", 24, 36);
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a sqldatareader containing the resultset generated by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
assignparametervalues(commandparameters, parametervalues);
return executereader(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executereader(transaction, commandtype.storedprocedure, spname);
}
}
#endregion executereader
#region executescalar
/// <summary>
/// execute a sqlcommand (that returns a 1x1 resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// int ordercount = (int)executescalar(connstring, commandtype.storedprocedure, "getordercount");
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object executescalar(string connectionstring, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executescalar(connectionstring, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a 1x1 resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int ordercount = (int)executescalar(connstring, commandtype.storedprocedure, "getordercount", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object executescalar(string connectionstring, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
// create & open a sqlconnection, and dispose of it after we are done
using (sqlconnection connection = new sqlconnection(connectionstring))
{
connection.open();
// call the overload that takes a connection in place of the connection string
return executescalar(connection, commandtype, commandtext, commandparameters);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the database specified in
/// the connection string using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int ordercount = (int)executescalar(connstring, "getordercount", 24, 36);
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executescalar(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executescalar(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a sqlcommand (that returns a 1x1 resultset and takes no parameters) against the provided sqlconnection.
/// </summary>
/// <remarks>
/// e.g.:
/// int ordercount = (int)executescalar(conn, commandtype.storedprocedure, "getordercount");
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object executescalar(sqlconnection connection, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executescalar(connection, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a 1x1 resultset) against the specified sqlconnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int ordercount = (int)executescalar(conn, commandtype.storedprocedure, "getordercount", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object executescalar(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connection == null ) throw new argumentnullexception( "connection" );
// create a command and prepare it for execution
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters, out mustcloseconnection );
// execute the command & return the results
object retval = cmd.executescalar();
// detach the sqlparameters from the command object, so they can be used again
cmd.parameters.clear();
if( mustcloseconnection )
connection.close();
return retval;
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the specified sqlconnection
/// using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int ordercount = (int)executescalar(conn, "getordercount", 24, 36);
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executescalar(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executescalar(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a sqlcommand (that returns a 1x1 resultset and takes no parameters) against the provided sqltransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// int ordercount = (int)executescalar(trans, commandtype.storedprocedure, "getordercount");
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object executescalar(sqltransaction transaction, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executescalar(transaction, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a 1x1 resultset) against the specified sqltransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int ordercount = (int)executescalar(trans, commandtype.storedprocedure, "getordercount", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</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" );
// create a command and prepare it for execution
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection );
// execute the command & return the results
object retval = cmd.executescalar();
// detach the sqlparameters from the command object, so they can be used again
cmd.parameters.clear();
return retval;
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the specified
/// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int ordercount = (int)executescalar(trans, "getordercount", 24, 36);
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// ppull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executescalar(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executescalar(transaction, commandtype.storedprocedure, spname);
}
}
#endregion executescalar
#region executexmlreader
/// <summary>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqlconnection.
/// </summary>
/// <remarks>
/// e.g.:
/// xmlreader r = executexmlreader(conn, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command using "for xml auto"</param>
/// <returns>an xmlreader containing the resultset generated by the command</returns>
public static xmlreader executexmlreader(sqlconnection connection, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executexmlreader(connection, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// xmlreader r = executexmlreader(conn, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command using "for xml auto"</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>an xmlreader containing the resultset generated by the command</returns>
public static xmlreader executexmlreader(sqlconnection connection, commandtype commandtype, string commandtext, params sqlparameter[] commandparameters)
{
if( connection == null ) throw new argumentnullexception( "connection" );
bool mustcloseconnection = false;
// create a command and prepare it for execution
sqlcommand cmd = new sqlcommand();
try
{
preparecommand(cmd, connection, (sqltransaction)null, commandtype, commandtext, commandparameters, out mustcloseconnection );
// create the dataadapter & dataset
xmlreader retval = cmd.executexmlreader();
// detach the sqlparameters from the command object, so they can be used again
cmd.parameters.clear();
return retval;
}
catch
{
if( mustcloseconnection )
connection.close();
throw;
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// xmlreader r = executexmlreader(conn, "getorders", 24, 36);
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="spname">the name of the stored procedure using "for xml auto"</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an xmlreader containing the resultset generated by the command</returns>
public static xmlreader executexmlreader(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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executexmlreader(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executexmlreader(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqltransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// xmlreader r = executexmlreader(trans, commandtype.storedprocedure, "getorders");
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command using "for xml auto"</param>
/// <returns>an xmlreader containing the resultset generated by the command</returns>
public static xmlreader executexmlreader(sqltransaction transaction, commandtype commandtype, string commandtext)
{
// pass through the call providing null for the set of sqlparameters
return executexmlreader(transaction, commandtype, commandtext, (sqlparameter[])null);
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the specified sqltransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// xmlreader r = executexmlreader(trans, commandtype.storedprocedure, "getorders", new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command using "for xml auto"</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <returns>an xmlreader containing the resultset generated by the command</returns>
public static xmlreader executexmlreader(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" );
// create a command and prepare it for execution
sqlcommand cmd = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(cmd, transaction.connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection );
// create the dataadapter & dataset
xmlreader retval = cmd.executexmlreader();
// detach the sqlparameters from the command object, so they can be used again
cmd.parameters.clear();
return retval;
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified
/// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// xmlreader r = executexmlreader(trans, "getorders", 24, 36);
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static xmlreader executexmlreader(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 we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
return executexmlreader(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
// otherwise we can just call the sp without params
return executexmlreader(transaction, commandtype.storedprocedure, spname);
}
}
#endregion executexmlreader
#region filldataset
/// <summary>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// filldataset(connstring, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"});
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
/// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
/// by a user defined name (probably the actual table name)</param>
public static void filldataset(string connectionstring, commandtype commandtype, string commandtext, dataset dataset, string[] tablenames)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( dataset == null ) throw new argumentnullexception( "dataset" );
// create & open a sqlconnection, and dispose of it after we are done
using (sqlconnection connection = new sqlconnection(connectionstring))
{
connection.open();
// call the overload that takes a connection in place of the connection string
filldataset(connection, commandtype, commandtext, dataset, tablenames);
}
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// filldataset(connstring, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"}, new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
/// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
/// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
public static void filldataset(string connectionstring, commandtype commandtype,
string commandtext, dataset dataset, string[] tablenames,
params sqlparameter[] commandparameters)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( dataset == null ) throw new argumentnullexception( "dataset" );
// create & open a sqlconnection, and dispose of it after we are done
using (sqlconnection connection = new sqlconnection(connectionstring))
{
connection.open();
// call the overload that takes a connection in place of the connection string
filldataset(connection, commandtype, commandtext, dataset, tablenames, commandparameters);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the database specified in
/// the connection string using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// filldataset(connstring, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"}, 24);
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
/// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
public static void filldataset(string connectionstring, string spname,
dataset dataset, string[] tablenames,
params object[] parametervalues)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( dataset == null ) throw new argumentnullexception( "dataset" );
// create & open a sqlconnection, and dispose of it after we are done
using (sqlconnection connection = new sqlconnection(connectionstring))
{
connection.open();
// call the overload that takes a connection in place of the connection string
filldataset (connection, spname, dataset, tablenames, parametervalues);
}
}
/// <summary>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqlconnection.
/// </summary>
/// <remarks>
/// e.g.:
/// filldataset(conn, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"});
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
/// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
public static void filldataset(sqlconnection connection, commandtype commandtype,
string commandtext, dataset dataset, string[] tablenames)
{
filldataset(connection, commandtype, commandtext, dataset, tablenames, null);
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// filldataset(conn, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"}, new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
/// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
public static void filldataset(sqlconnection connection, commandtype commandtype,
string commandtext, dataset dataset, string[] tablenames,
params sqlparameter[] commandparameters)
{
filldataset(connection, null, commandtype, commandtext, dataset, tablenames, commandparameters);
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// filldataset(conn, "getorders", ds, new string[] {"orders"}, 24, 36);
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
/// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
public static void filldataset(sqlconnection connection, string spname,
dataset dataset, string[] tablenames,
params object[] parametervalues)
{
if ( connection == null ) throw new argumentnullexception( "connection" );
if (dataset == null ) throw new argumentnullexception( "dataset" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// if we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
filldataset(connection, commandtype.storedprocedure, spname, dataset, tablenames, commandparameters);
}
else
{
// otherwise we can just call the sp without params
filldataset(connection, commandtype.storedprocedure, spname, dataset, tablenames);
}
}
/// <summary>
/// execute a sqlcommand (that returns a resultset and takes no parameters) against the provided sqltransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// filldataset(trans, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"});
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
/// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
public static void filldataset(sqltransaction transaction, commandtype commandtype,
string commandtext,
dataset dataset, string[] tablenames)
{
filldataset (transaction, commandtype, commandtext, dataset, tablenames, null);
}
/// <summary>
/// execute a sqlcommand (that returns a resultset) against the specified sqltransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// filldataset(trans, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"}, new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
/// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
public static void filldataset(sqltransaction transaction, commandtype commandtype,
string commandtext, dataset dataset, string[] tablenames,
params sqlparameter[] commandparameters)
{
filldataset(transaction.connection, transaction, commandtype, commandtext, dataset, tablenames, commandparameters);
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified
/// sqltransaction using the provided parameter values. this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// this method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// filldataset(trans, "getorders", ds, new string[]{"orders"}, 24, 36);
/// </remarks>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
/// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
/// <param name="parametervalues">an array of objects to be assigned as the input values of the stored procedure</param>
public static void filldataset(sqltransaction transaction, string spname,
dataset dataset, string[] tablenames,
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( dataset == null ) throw new argumentnullexception( "dataset" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// if we receive parameter values, we need to figure out where they go
if ((parametervalues != null) && (parametervalues.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// assign the provided values to these parameters based on parameter order
assignparametervalues(commandparameters, parametervalues);
// call the overload that takes an array of sqlparameters
filldataset(transaction, commandtype.storedprocedure, spname, dataset, tablenames, commandparameters);
}
else
{
// otherwise we can just call the sp without params
filldataset(transaction, commandtype.storedprocedure, spname, dataset, tablenames);
}
}
/// <summary>
/// private helper method that execute a sqlcommand (that returns a resultset) against the specified sqltransaction and sqlconnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// filldataset(conn, trans, commandtype.storedprocedure, "getorders", ds, new string[] {"orders"}, new sqlparameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid sqlconnection</param>
/// <param name="transaction">a valid sqltransaction</param>
/// <param name="commandtype">the commandtype (stored procedure, text, etc.)</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="dataset">a dataset wich will contain the resultset generated by the command</param>
/// <param name="tablenames">this array will be used to create table mappings allowing the datatables to be referenced
/// by a user defined name (probably the actual table name)
/// </param>
/// <param name="commandparameters">an array of sqlparamters used to execute the command</param>
private static void filldataset(sqlconnection connection, sqltransaction transaction, commandtype commandtype,
string commandtext, dataset dataset, string[] tablenames,
params sqlparameter[] commandparameters)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( dataset == null ) throw new argumentnullexception( "dataset" );
// create a command and prepare it for execution
sqlcommand command = new sqlcommand();
bool mustcloseconnection = false;
preparecommand(command, connection, transaction, commandtype, commandtext, commandparameters, out mustcloseconnection );
// create the dataadapter & dataset
using( sqldataadapter dataadapter = new sqldataadapter(command) )
{
// add the table mappings specified by the user
if (tablenames != null && tablenames.length > 0)
{
string tablename = "table";
for (int index=0; index < tablenames.length; index++)
{
if( tablenames[index] == null || tablenames[index].length == 0 ) throw new argumentexception( "the tablenames parameter must contain a list of tables, a value was provided as null or empty string.", "tablenames" );
dataadapter.tablemappings.add(tablename, tablenames[index]);
tablename += (index + 1).tostring();
}
}
// fill the dataset using default values for datatable names, etc
dataadapter.fill(dataset);
// detach the sqlparameters from the command object, so they can be used again
command.parameters.clear();
}
if( mustcloseconnection )
connection.close();
}
#endregion
#region updatedataset
/// <summary>
/// executes the respective command for each inserted, updated, or deleted row in the dataset.
/// </summary>
/// <remarks>
/// e.g.:
/// updatedataset(conn, insertcommand, deletecommand, updatecommand, dataset, "order");
/// </remarks>
/// <param name="insertcommand">a valid transact-sql statement or stored procedure to insert new records into the data source</param>
/// <param name="deletecommand">a valid transact-sql statement or stored procedure to delete records from the data source</param>
/// <param name="updatecommand">a valid transact-sql statement or stored procedure used to update records in the data source</param>
/// <param name="dataset">the dataset used to update the data source</param>
/// <param name="tablename">the datatable used to update the data source.</param>
public static void updatedataset(sqlcommand insertcommand, sqlcommand deletecommand, sqlcommand updatecommand, dataset dataset, string tablename)
{
if( insertcommand == null ) throw new argumentnullexception( "insertcommand" );
if( deletecommand == null ) throw new argumentnullexception( "deletecommand" );
if( updatecommand == null ) throw new argumentnullexception( "updatecommand" );
if( tablename == null || tablename.length == 0 ) throw new argumentnullexception( "tablename" );
// create a sqldataadapter, and dispose of it after we are done
using (sqldataadapter dataadapter = new sqldataadapter())
{
// set the data adapter commands
dataadapter.updatecommand = updatecommand;
dataadapter.insertcommand = insertcommand;
dataadapter.deletecommand = deletecommand;
// update the dataset changes in the data source
dataadapter.update (dataset, tablename);
// commit all the changes made to the dataset
dataset.acceptchanges();
}
}
#endregion
#region createcommand
/// <summary>
/// simplify the creation of a sql command object by allowing
/// a stored procedure and optional parameters to be provided
/// </summary>
/// <remarks>
/// e.g.:
/// sqlcommand command = createcommand(conn, "addcustomer", "customerid", "customername");
/// </remarks>
/// <param name="connection">a valid sqlconnection object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="sourcecolumns">an array of string to be assigned as the source columns of the stored procedure parameters</param>
/// <returns>a valid sqlcommand object</returns>
public static sqlcommand createcommand(sqlconnection connection, string spname, params string[] sourcecolumns)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// create a sqlcommand
sqlcommand cmd = new sqlcommand( spname, connection );
cmd.commandtype = commandtype.storedprocedure;
// if we receive parameter values, we need to figure out where they go
if ((sourcecolumns != null) && (sourcecolumns.length > 0))
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// assign the provided source columns to these parameters based on parameter order
for (int index=0; index < sourcecolumns.length; index++)
commandparameters[index].sourcecolumn = sourcecolumns[index];
// attach the discovered parameters to the sqlcommand object
attachparameters (cmd, commandparameters);
}
return cmd;
}
#endregion
#region executenonquerytypedparams
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns no resultset) against the database specified in
/// the connection string using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
/// </summary>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int executenonquerytypedparams(string connectionstring, string spname, datarow datarow)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// if the row has values, the store procedure parameters must be initialized
if (datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executenonquery(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executenonquery(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns no resultset) against the specified sqlconnection
/// using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
/// </summary>
/// <param name="connection">a valid sqlconnection object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int executenonquerytypedparams(sqlconnection connection, string spname, datarow datarow)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// if the row has values, the store procedure parameters must be initialized
if (datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executenonquery(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executenonquery(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns no resultset) against the specified
/// sqltransaction using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
/// </summary>
/// <param name="transaction">a valid sqltransaction object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int executenonquerytypedparams(sqltransaction transaction, string spname, datarow datarow)
{
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" );
// sf the row has values, the store procedure parameters must be initialized
if (datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executenonquery(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executenonquery(transaction, commandtype.storedprocedure, spname);
}
}
#endregion
#region executedatasettypedparams
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the database specified in
/// the connection string using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
/// </summary>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static dataset executedatasettypedparams(string connectionstring, string spname, datarow datarow)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
//if the row has values, the store procedure parameters must be initialized
if ( datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executedataset(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executedataset(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the datarow column values as the store procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
/// </summary>
/// <param name="connection">a valid sqlconnection object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static dataset executedatasettypedparams(sqlconnection connection, string spname, datarow datarow)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// if the row has values, the store procedure parameters must be initialized
if( datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executedataset(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executedataset(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqltransaction
/// using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
/// </summary>
/// <param name="transaction">a valid sqltransaction object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static dataset executedatasettypedparams(sqltransaction transaction, string spname, datarow datarow)
{
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 the row has values, the store procedure parameters must be initialized
if( datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executedataset(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executedataset(transaction, commandtype.storedprocedure, spname);
}
}
#endregion
#region executereadertypedparams
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the database specified in
/// the connection string using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>a sqldatareader containing the resultset generated by the command</returns>
public static sqldatareader executereadertypedparams(string connectionstring, string spname, datarow datarow)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// if the row has values, the store procedure parameters must be initialized
if ( datarow != null && datarow.itemarray.length > 0 )
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executereader(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executereader(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <param name="connection">a valid sqlconnection object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>a sqldatareader containing the resultset generated by the command</returns>
public static sqldatareader executereadertypedparams(sqlconnection connection, string spname, datarow datarow)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// if the row has values, the store procedure parameters must be initialized
if( datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executereader(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executereader(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqltransaction
/// using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <param name="transaction">a valid sqltransaction object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>a sqldatareader containing the resultset generated by the command</returns>
public static sqldatareader executereadertypedparams(sqltransaction transaction, string spname, datarow datarow)
{
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 the row has values, the store procedure parameters must be initialized
if( datarow != null && datarow.itemarray.length > 0 )
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executereader(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executereader(transaction, commandtype.storedprocedure, spname);
}
}
#endregion
#region executescalartypedparams
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the database specified in
/// the connection string using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object executescalartypedparams(string connectionstring, string spname, datarow datarow)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// if the row has values, the store procedure parameters must be initialized
if( datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connectionstring, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executescalar(connectionstring, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executescalar(connectionstring, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the specified sqlconnection
/// using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <param name="connection">a valid sqlconnection object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object executescalartypedparams(sqlconnection connection, string spname, datarow datarow)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// if the row has values, the store procedure parameters must be initialized
if( datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executescalar(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executescalar(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a 1x1 resultset) against the specified sqltransaction
/// using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <param name="transaction">a valid sqltransaction object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object executescalartypedparams(sqltransaction transaction, string spname, datarow datarow)
{
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 the row has values, the store procedure parameters must be initialized
if( datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executescalar(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executescalar(transaction, commandtype.storedprocedure, spname);
}
}
#endregion
#region executexmlreadertypedparams
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqlconnection
/// using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <param name="connection">a valid sqlconnection object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>an xmlreader containing the resultset generated by the command</returns>
public static xmlreader executexmlreadertypedparams(sqlconnection connection, string spname, datarow datarow)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
// if the row has values, the store procedure parameters must be initialized
if( datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(connection, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executexmlreader(connection, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executexmlreader(connection, commandtype.storedprocedure, spname);
}
}
/// <summary>
/// execute a stored procedure via a sqlcommand (that returns a resultset) against the specified sqltransaction
/// using the datarow column values as the stored procedure's parameters values.
/// this method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <param name="transaction">a valid sqltransaction object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="datarow">the datarow used to hold the stored procedure's parameter values.</param>
/// <returns>an xmlreader containing the resultset generated by the command</returns>
public static xmlreader executexmlreadertypedparams(sqltransaction transaction, string spname, datarow datarow)
{
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 the row has values, the store procedure parameters must be initialized
if( datarow != null && datarow.itemarray.length > 0)
{
// pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
sqlparameter[] commandparameters = sqlhelperparametercache.getspparameterset(transaction.connection, spname);
// set the parameters values
assignparametervalues(commandparameters, datarow);
return sqlhelper.executexmlreader(transaction, commandtype.storedprocedure, spname, commandparameters);
}
else
{
return sqlhelper.executexmlreader(transaction, commandtype.storedprocedure, spname);
}
}
#endregion
}
/// <summary>
/// sqlhelperparametercache provides functions to leverage a static cache of procedure parameters, and the
/// ability to discover parameters for stored procedures at run-time.
/// </summary>
public sealed class sqlhelperparametercache
{
#region private methods, variables, and constructors
//since this class provides only static methods, make the default constructor private to prevent
//instances from being created with "new sqlhelperparametercache()"
private sqlhelperparametercache() {}
private static hashtable paramcache = hashtable.synchronized(new hashtable());
/// <summary>
/// resolve at run time the appropriate set of sqlparameters for a stored procedure
/// </summary>
/// <param name="connection">a valid sqlconnection object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="includereturnvalueparameter">whether or not to include their return value parameter</param>
/// <returns>the parameter array discovered.</returns>
private static sqlparameter[] discoverspparameterset(sqlconnection connection, string spname, bool includereturnvalueparameter)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
sqlcommand cmd = new sqlcommand(spname, connection);
cmd.commandtype = commandtype.storedprocedure;
connection.open();
sqlcommandbuilder.deriveparameters(cmd);
connection.close();
if (!includereturnvalueparameter)
{
cmd.parameters.removeat(0);
}
sqlparameter[] discoveredparameters = new sqlparameter[cmd.parameters.count];
cmd.parameters.copyto(discoveredparameters, 0);
// init the parameters with a dbnull value
foreach (sqlparameter discoveredparameter in discoveredparameters)
{
discoveredparameter.value = dbnull.value;
}
return discoveredparameters;
}
/// <summary>
/// deep copy of cached sqlparameter array
/// </summary>
/// <param name="originalparameters"></param>
/// <returns></returns>
private static sqlparameter[] cloneparameters(sqlparameter[] originalparameters)
{
sqlparameter[] clonedparameters = new sqlparameter[originalparameters.length];
for (int i = 0, j = originalparameters.length; i < j; i++)
{
clonedparameters[i] = (sqlparameter)((icloneable)originalparameters[i]).clone();
}
return clonedparameters;
}
#endregion private methods, variables, and constructors
#region caching functions
/// <summary>
/// add parameter array to the cache
/// </summary>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <param name="commandparameters">an array of sqlparamters to be cached</param>
public static void cacheparameterset(string connectionstring, string commandtext, params sqlparameter[] commandparameters)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( commandtext == null || commandtext.length == 0 ) throw new argumentnullexception( "commandtext" );
string hashkey = connectionstring + ":" + commandtext;
paramcache[hashkey] = commandparameters;
}
/// <summary>
/// retrieve a parameter array from the cache
/// </summary>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="commandtext">the stored procedure name or t-sql command</param>
/// <returns>an array of sqlparamters</returns>
public static sqlparameter[] getcachedparameterset(string connectionstring, string commandtext)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( commandtext == null || commandtext.length == 0 ) throw new argumentnullexception( "commandtext" );
string hashkey = connectionstring + ":" + commandtext;
sqlparameter[] cachedparameters = paramcache[hashkey] as sqlparameter[];
if (cachedparameters == null)
{
return null;
}
else
{
return cloneparameters(cachedparameters);
}
}
#endregion caching functions
#region parameter discovery functions
/// <summary>
/// retrieves the set of sqlparameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// this method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <returns>an array of sqlparameters</returns>
public static sqlparameter[] getspparameterset(string connectionstring, string spname)
{
return getspparameterset(connectionstring, spname, false);
}
/// <summary>
/// retrieves the set of sqlparameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// this method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connectionstring">a valid connection string for a sqlconnection</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="includereturnvalueparameter">a bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>an array of sqlparameters</returns>
public static sqlparameter[] getspparameterset(string connectionstring, string spname, bool includereturnvalueparameter)
{
if( connectionstring == null || connectionstring.length == 0 ) throw new argumentnullexception( "connectionstring" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
using(sqlconnection connection = new sqlconnection(connectionstring))
{
return getspparametersetinternal(connection, spname, includereturnvalueparameter);
}
}
/// <summary>
/// retrieves the set of sqlparameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// this method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connection">a valid sqlconnection object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <returns>an array of sqlparameters</returns>
internal static sqlparameter[] getspparameterset(sqlconnection connection, string spname)
{
return getspparameterset(connection, spname, false);
}
/// <summary>
/// retrieves the set of sqlparameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// this method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connection">a valid sqlconnection object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="includereturnvalueparameter">a bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>an array of sqlparameters</returns>
internal static sqlparameter[] getspparameterset(sqlconnection connection, string spname, bool includereturnvalueparameter)
{
if( connection == null ) throw new argumentnullexception( "connection" );
using (sqlconnection clonedconnection = (sqlconnection)((icloneable)connection).clone())
{
return getspparametersetinternal(clonedconnection, spname, includereturnvalueparameter);
}
}
/// <summary>
/// retrieves the set of sqlparameters appropriate for the stored procedure
/// </summary>
/// <param name="connection">a valid sqlconnection object</param>
/// <param name="spname">the name of the stored procedure</param>
/// <param name="includereturnvalueparameter">a bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>an array of sqlparameters</returns>
private static sqlparameter[] getspparametersetinternal(sqlconnection connection, string spname, bool includereturnvalueparameter)
{
if( connection == null ) throw new argumentnullexception( "connection" );
if( spname == null || spname.length == 0 ) throw new argumentnullexception( "spname" );
string hashkey = connection.connectionstring + ":" + spname + (includereturnvalueparameter ? ":include returnvalue parameter":"");
sqlparameter[] cachedparameters;
cachedparameters = paramcache[hashkey] as sqlparameter[];
if (cachedparameters == null)
{
sqlparameter[] spparameters = discoverspparameterset(connection, spname, includereturnvalueparameter);
paramcache[hashkey] = spparameters;
cachedparameters = spparameters;
}
return cloneparameters(cachedparameters);
}
#endregion parameter discovery functions
}
}
新闻热点
疑难解答
