数据库操作类真的没有必要自己去写，因为成熟的类库真的非常完善了，拿来直接用就好，省时省力。
本文就为大家介绍微软官方的程序petshop4.0中的sqlhelper类，先来做一下简单的介绍，petshop是一个范例，微软用它来展示.net企业系统开发的能力。
那sqlhelper中封装了哪些方法呢？
里面的函数一堆，常用的就那几个，无非就是增删改查嘛，来看下几种常用的函数：
1.executenonquery 执行增删改
2.executereader 执行查询
3.executescalar 返回首行首列
使用方法介绍
web.config配置
<connectionstrings> <add name="connectionstring" connectionstring="server=127.0.0.1;uid=sa;pwd=ok;database=petshop;max pool size =512; min pool size=0; connection lifetime = 300;packet size=1000;" providername="system.data.sqlclient" /> </connectionstrings>
调用函数的写法
sql = "update student set name = @name where id = @id"; sqlhelper.executenonquery(commandtype.text, sql, new sqlparameter[]{ new sqlparameter("@name", name), new sqlparameter("@id", id) });
这样调用就比较简化，而且比较灵活
源码呈上
/// <summary> /// the sqlhelper class is intended to encapsulate high performance, /// scalable best practices for common uses of sqlclient. /// </summary> public abstract class sqlhelper { //数据库连接字符串 public static readonly string connectionstring = configurationmanager.connectionstrings["sqlconnstring"].connectionstring; #region 私有函数和方法 /// <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 public static int executenonquery(commandtype cmdtype, string cmdtext) { return executenonquery(connectionstring, cmdtype, cmdtext); } public static int executenonquery(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparameters) { return executenonquery(connectionstring, cmdtype, cmdtext, commandparameters); } /// <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 public static dataset executedataset(commandtype commandtype, string commandtext) { return executedataset(connectionstring, commandtype, commandtext); } public static dataset executedataset(commandtype commandtype, string commandtext, params sqlparameter[] commandparameters) { return executedataset(connectionstring, commandtype, commandtext, commandparameters); } /// <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 } public static sqldatareader executereader(commandtype cmdtype, string cmdtext) { return executereader(connectionstring, cmdtype, cmdtext); } public static sqldatareader executereader(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparameters) { return executereader(connectionstring, cmdtype, cmdtext, commandparameters); } /// <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 public static object executescalar(commandtype cmdtype, string cmdtext) { return executescalar(connectionstring, cmdtype, cmdtext); } public static object executescalar(commandtype cmdtype, string cmdtext, params sqlparameter[] commandparameters) { return executescalar(connectionstring, cmdtype, cmdtext, commandparameters); } /// <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 }
更多微软官方sqlhelper类 数据库辅助操作类 。