Newly organized a sqlhelper.v3.1.cs standby! (Pure plagiarism) // data access application block 3.1 // http://www.gotdotnet.com/Workspaces/Workspace.aspx?id=c20d12b0-af52-402b -9b7c-aaeb21d1f431 // sqlhelper.v3.1.cs // csc.exe sqlhelper.v3.1.cs / t: library /r:c:/winnt/microsoft.net/framework/v1.1.4322/system.data . OracleClient.dll
namespace Microshaoft.Data {using System; using System.IO; using System.Data; using System.Xml; using System.Data.SqlClient; using System.Data.Odbc; using System.Data.OleDb; using System.Data.OracleClient Using system.data.common; using system.configuration; using system.text; using system.text.regularexpressions; using system.reflection; using system.diagnostics;
/// /// The AdoHelper class is intended to encapsulate high performance, scalable best practices for /// common data access uses. It uses the Abstract Factory pattern to be easily extensible /// to any ADO.NET provider ...................................................................... was provided by the caller, or created by AdoHelper, so that /// we can set the appropriate CommandBehavior when calling ExecuteReader () /// protected enum AdoConnectionOwnership {/// Connection is owned and managed by Adohelper infneal, /// Connection is Owned and management by the caller external}
#Region Declare MEMBERS
// necessary for handling the general case of needing event handlers for RowUpdating / ed events /// /// Internal handler used for bubbling up the event to the user /// protected RowUpdatingHandler m_rowUpdating; // / /// Internal Handler Used for Bubbling Up The Event To The User /// Protected RowupdatedHandler M_RowUpdated;
#ndregion
#Region Provider Specific Abstract Methods
/// /// Returns an idbconnection Object for the given connection string /// /// The connection string to be used to create the connection /// An IDbConnection object /// Thrown if connectionString is null public abstract IDbConnection GetConnection (string connectionString);
/// /// Returns an idbdataadapter object /// /// The idbdataadapter public abstract idbdataadapter getDataAdapter ();
/// /// Calls the CommandBuilder.DeriveParameters method for the specified provider, doing any setup and cleanup necessary /// /// The IDbCommand referencing the stored procedure From which the parameter information is to be deived. The derived parameters are added to the parameters collection of the idbcommand. Public Abstract Void DeriveParameters (iDBCommand CMD);
/// /// Returns An iDataParameter Object /// /// The iDataParameter Object public abstract iDataParameter getParameter (); /// /// Execute An idbcommand (That Returns a resultset) against the provided idbconnection. /// /// /// /// xmlreader r = helper.executexmlreader (Command); /// /// The idbcommand to execute /// Thrown if Command is Null. Public Abstract XmlReader ExecutexmlReader (IDBCommand CMD);
/// /// provider specific code to set up the updating / ed Event handlers buy by updateDataSet /// /// DataAptapter to attach the Event Handlers To < / param> /// The Handler to Be Called When a Row Is Updating /// The Handler to Be Called When a Row Is Updated < / Param> protected abstract void addupdateEventhandlers (idbdataadapter data); rowupdatinghandler rowupdateldler;
/// /// Returns an Array of IdataParameters of the specified size /// /// size of the array //// The array of idataparameters protected Abstract iDataParameter [] getDataParameters (int size);
/// /// Handle Any Provider-Specific Issues with blobs Here by "Washing" The iDataParameter and return a new one "set up appropriately for the provider. /// /// The idbconnection to use in cleansing the parameter /// The parameter before cleaning /// The parameter after it's been cleansed. protected Abstract iDataParameter getBlobParameter (idbconnection connection, iDataParameter P); # endregion
#Region delegates
// also used in incer case of rowupdating / ed events /// /// delegate for creating a rowupdatingevent Handler /// /// the object what Published The event /// The RowupDatingeventargs for the Event Public Delegate Void RowUpdatingHandler (Object Obj, RowupDatingeventArgs E);
/// /// delegate for create a rowupdateDevent Handler /// /// The Object what published the event /// The RowupdatedEventargs for the Event Public Delegate Void RowUpdatedHandler (Object Obj, RowUpdatedEventArgs E);
#ndregion
#Region Factory
/// /// Create An adohelper for Working with a specific provider (IE SQL, ODBC, OLEDB, ORACLE) /// /// Assembly Containing The Specified Helper subclass /// Specific Type of the provider /// an adohelper instance of the specified type /// < code> /// AdoHelper helper = AdoHelper.CreateHelper ( "GotDotNet.ApplicationBlocks.Data", "GotDotNet.ApplicationBlocks.Data.OleDb"); /// public static AdoHelper CreateHelper (string providerAssembly, string providerType) {Assembly assembly = Assembly.Load (providerAssembly); object provider = assembly.CreateInstance (providerType); if (provider is AdoHelper) {return provider as AdoHelper;} else {throw new InvalidOperationException ( "The provider specified does not extend ");}}
/// /// Create An adohelper instance for Working with a specific provider by using a providiaas specified in the app.config file. /// /// the Alias to look up /// an adohelper instance of the specified type /// /// adohelper helper = adohelper.createhelper ("oraclehelper"); / // public static AdoHelper CreateHelper (string providerAlias) {IDictionary dict; try {dict = ConfigurationSettings.GetConfig ( "daabProviders") as IDictionary;} catch (Exception e) {throw new InvalidOperationException ( "If the section is not defined on the configuration file this method can not be used to create an AdoHelper instance ", e);.} ProviderAlias providerConfig = dict [providerAlias] as ProviderAlias; string providerAssembly = providerConfig.AssemblyName; string providerType = providerConfig. TypeName;
Assembly assembly = Assembly.Load (providerAssembly); object provider = assembly.CreateInstance (providerType); if (provider is AdoHelper) {return provider as AdoHelper;} else {throw new InvalidOperationException ( "The provider specified does not extends the AdoHelper abstract class "}}
#ndregion
#Region getParameter
/// /// Get An iDataParameter for use in a sql command /// /// the name of the parameter to create /// The value of the specified parameter /// An IDataParameter object public virtual IDataParameter GetParameter (string name, object value) {IDataParameter parameter = GetParameter (); Parameter.ParameterName = name; parameter.value = value; return parameter;
/// /// Get An iDataParameter for use in a sql command /// /// the name of the parameter to create /// The system.data.dbtype of the parameter /// The size of the parameter /// The System.Data.ParameterDirection of the parameter /// An IDataParameter object public virtual IDataParameter GetParameter (string name, dbType dbType, int size, ParameterDirection direction) {IDataParameter dataParameter = GetParameter (); DataParameter.dbType = dbtype; dataparameter.direction = direction; dataparameter.ParameterName = name;
IF (Size> 0 && DataParameter Is IDBDataParameter) {idbdataParameter DBDataParameter = (idbdataParameter) DataParameter; dbdataparameter.size = size;} Return DataParameter;
/// /// Get An iDataParameter for use in a sql command /// /// the name of the parameter to create /// The system.data.dbtype of the parameter /// The size of the parameter /// The source column of the parameter /// The system.data.datarowversion of the parameter /// an iDataParameter Object public virtual IDataParameter GetParameter (string name, dbType dbType, int size, string sourceColumn, DataRowVersion sourceVersion) {IDataParameter dataParameter = GetParameter (); dataParameter.DbType = dbType; dataParameter.ParameterName = name; dataParameter.SourceColumn = sourceColumn; dataParameter.SourceVersion = sourceVersion ; if (Size> 0 && DataParameter Is IDBDataParameter) {IdbdataParameter DBDataParameter = (iDBDataParameter) DataParameter; dbdataparameter.size = size; Return DataParameter;
#ndregion
#Region Private Utility Methods
/// /// this method is used to attach array of idataparameters to an idbcommand. ////////////////////////////////////////////////////////////////////////////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. /// The command to which the parameters will be added /// Thrown if command is null. protected virtual void AttachParameters (IDbCommand command, IDataParameter [] commandParameters) {if (command == null Throw new argumentnullexception ("Command"); if (CommandParameters! = null) {Foreach (iDataParameter P IN commandparameters) { IF (p! = null) {// check for deact output value with no value assigned if ((p.direction == parameterDirection.inputoutput || p.Direction == parameterdirection.input) && (p.value == null) ) {P.Value = dbnull.value;} f (p.dbtype == dbtype.binary) {// special handling for blobs command.parameters.add (getBlobParameter (Command.Connection, P));} else {Command. Parameters.Add (p);}}}}}
/// /// This method assigns dataRow column values to an IDataParameterCollection /// /// The IDataParameterCollection to be assigned values /// < param name = "dataRow"> The dataRow used to hold the stored procedure's parameter values /// Thrown if any of the parameter names are invalid. protected internal void AssignParameterValues (IDataParameterCollection commandParameters, DataRow dataRow) {if (commandParameters == null || dataRow == null) {// Do nothing if we get no data return;} DataColumnCollection columns = dataRow.Table.Columns;
int i = 0; // Set the parameters values foreach (IDataParameter commandParameter in commandParameters) {// Check the parameter name if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1) throw new InvalidOperationException (string. Format ("Please provide a valid parameter name on the parameter # {0}, the parametername", {1} '. ", I, commandparameter.ParameterName);
if (columns.Contains (commandParameter.ParameterName)) commandParameter.Value = dataRow [commandParameter.ParameterName]; else if (columns.Contains (commandParameter.ParameterName.Substring (1))) commandParameter.Value = dataRow [commandParameter.ParameterName.Substring (1)];
i ;}}
/// /// this method assigns datarow column values to an array of idataparameters /// /// Array of iDataParameters to be assigned value / // The dataRow used to hold the stored procedure's parameter values /// Thrown if any of the parameter names are invalid. protected void AssignParameterValues (IDataParameter [] commandParameters, DataRow dataRow) {if ((commandParameters == null) || (dataRow == null)) {// Do nothing if we get no data return;} DataColumnCollection columns = dataRow.Table .Columns;
int i = 0; // Set the parameters values foreach (IDataParameter commandParameter in commandParameters) {// Check the parameter name if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1) throw new InvalidOperationException (string. Format ("Please provide a valid parameter name on the parameter # {0}, the parametername", {1} '. ", I, commandparameter.ParameterName);
if (columns.Contains (commandParameter.ParameterName)) commandParameter.Value = dataRow [commandParameter.ParameterName]; else if (columns.Contains (commandParameter.ParameterName.Substring (1))) commandParameter.Value = dataRow [commandParameter.ParameterName.Substring (1)];
i ;}}
/// ///////// /// Array of iDataParameters to be assigned value /// Array of objects holding the values to be assigned /// Thrown if an incorrect number of parameters are passed. protected void AssignParameterValues (IDataParameter [] 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.");}
// iprate through the idataparameters, assigning the value from the corresponding position in the corresponding position for (int i = 0, j = circarandparameters.length, k = 0; i /// this method cleans up the parameter syntax for the provider /// /// The idbcommand Containing the parameters to clean up. public virtual void cleanparametersyntax (idbcommand command) {// do nothing by default}
/// /// this method Opens (if Necessary) and assigns a connection, transaction, command type and parameters /// to the provided command /// /// a valid idbconnection, on which to execute this command /// a valid idbtransaction , or 'null' /// The CommandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name or SQL command /// An array of IDataParameters to be associated with the command or 'null' if no parameters are required /// True if The connection is false. /// /// Thrown if CommandText IS null. protected virtual void PrepareCommand (IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDataParameter [] commandParameters, out bool mustCloseConnection) {if (command == null) throw new ArgumentNullException ( "command" ); If (commandtext == null || comMMANDText.Length == 0) throw new argumentnull == ("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 stat) Command.comMandText = CommandText;
// If we were provided a transaction, assign it if (transaction! = Null) {if (transaction.Connection == null) throw new ArgumentException ( "The transaction was rolled back or commited, please provide an open transaction.", " Command.Transaction = Transaction;
// set the command type = commandType;
// attach the command parameters if the isy area! = Null) {attachparameters (command, commandparameters);} return;
/// /// this method clears (if Necessary) The connection, transaction, command type and parameters ///////////////// /// not userned here because the behavior of this method differs on each data provider. /// /// The IDbCommand to be cleared protected virtual void ClearCommand (IDbCommand command) {/ / do nothing by default}
#ndregion private utility methods
#REGON EXECUTEDASET
/// /// execute an idbcommand (That Returns a resultset) against the database specified in /// /// The idbcommand object to use /// A DataSet containing the resultset generated by the command /// Thrown if command is null. public Virtual DataSet Executetaset (IDBCommand Command) {BOOL MUSTCLOSECONNECONNECTION = false; // clean Up parameter Syntax Cleanparametersyntax (Command);
IF (Command.Connection.State! = connectionState.Open) {command.connection.open (); mustcloseConnection = true;
// Create The DataAdapter & Dataset IDBDataAdapter Da = NULL; TRY {DA = getDataAdapter (); da.selectcommand = command;
DataSet DS = New Dataset ();
Try {// Fill The Dataset Using Default Values for DataTable Names, etc DA.FILL (DS);} catch (Exception EX) {// Don't Just Throw EX. It Changes The Call Stack. But Want The Exped For Debugging, So ... Debug.writeline (ex); throw;}
// Detach The IdataParaseters from the command Object, so. Can do this ... screen ... screws up output params - cjb //command.parameters.clear ();
// Return The DataSet Return DS;} Finally {if (MustCloseConnection) {Command.connection.Close ();} if (da! = Null) {idisposable id = da as idisposable; if (id! = Null) id.dispose ()}}}
/// /// Execute An idbcommand (That Returns A ResultSet and takes no parameters) against the database specified in //// /// /// /// DataSet DS = helper.executedataset (connString, CommandType.StoredProcedure); /// /// a valid connection string For An idbconnection /// The commandType (Stored Procedure, Text, ETC.) /// The stored procedure name or sql command /// A DataSet containing the resultset generated by the command /// Thrown if connectionString is null /// < exception cref = "System.ArgumentNullException"> Thrown if commandText is null /// A DataSet containing the resultset generated by the command public virtual DataSet ExecuteDataset (string connectionString, C ommandType commandType, string commandText) {// Pass through the call providing null for the set of IDataParameters return ExecuteDataset (connectionString, commandType, commandText, (IDataParameter []) null);}
/// /// Execute An idbcommand (That Returns a resultset) against the database specified in the connection String /////// /// /// /// DataSet DS = helper.executedataset (Connstring, CommandType.StoredProcedure), "Getorders", New IdbParameter ("@PRODID", 24)); /// /// < Param name = "connectionstring"> a valid connection string for an idbconnection /// The CommandType (Stored Procedure, Text, ETC.) /// The stored procedure name or SQL command /// An array of IDbParamters used to execute the command /// A DataSet containing the resultset generated by the command /// Thrown if connectionString is null /// Thrown if any of the IDataParameters .Parametersnames are null, or if the parameter count does not match the number of values supplied /// Thrown if commandText is null /// Thrown if the parameter count does not match the number of values supplied
public virtual DataSet ExecuteDataset (string connectionString, CommandType commandType, string commandText, params IDataParameter [] commandParameters) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); // Create & open An IDBCONNECTION, AND DISPOSE OF ITAFTER WE ARE DONE Using (IDBConnection Connection = GetConnection) {connection.open ();
// Call the overload Takes a connection in place of the connection string return executedataset (connection, commandtype, commandtext, commandparameters);}}
/// /// Execute a stored procedure via an IDbCommand (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. /// /// /// This method provides no access to output parameters Or the stored procedure's return value parameter. /// /// /// /// DataSet DS = helper.executedataset (Connstring, "GetORDERS", 24, 36); // / /// a valid connection string for an idbconnection /// The name of the stored procedure /// an array of objects to be assigned as the input value of the stored procedure /// a dataset containing the resultset generated by the command /// Thrown if connectionString is null /// Thrown if spName is null public virtual 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)) {IDataParameter [] iDataParameterValues = GetDataParameters (ParameterValues.Length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteDataset (connectionString, CommandType.StoredProcedure, spName, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (connectionString, spName, includeReturnValue);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters return ExecuteDataset (connectionString, CommandType.StoredProcedure, spName, commandParameters);}} else {// Otherwise we can just call the SP without params return ExecuteDataset (connectionString, CommandType.StoredProcedure, spName }}
/// /// execute An idbcommand (That Returns a resultset and takes no parameters) against the provided idbconnection. /// /// /// /// dataset DS = helper.executedata (conn, commandtype.storedProcedure); /// /// a valid idbconnection /// < Param name = "CommandType"> The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or sql command /// A DataSet containing the resultset generated by the command /// Thrown if commandText is null /// Thrown if connection is null public virtual DataSet ExecuteDataset (IDbConnection connection, commandType commandType, string commandText) {// Pass through the call providing null for the set of IDataParameters return ExecuteDataset (connectio N, CommandType, CommandText, (iDataParameter [] NULL);}
/// /// Execute An idbcommand (That Returns a resultset) against the specified idbconnection //// //// /// / // DataSet DS = helper.executedataset (conn, commandtype.storedProcedure), "GetRDERS", New IdataParameter ("@ products", 24)); /// /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or SQL command /// An array of IDataParameters used to execute the command /// A DataSet containing the resultset generated by the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// throw n if commandText is null /// Thrown if the parameter count does not match the number of values supplied /// Thrown if connection is null public virtual DataSet ExecuteDataset (IDbConnection connection, commandType commandType, string commandText, params IDataParameter [] commandParameters) {if (connection ==
null) throw new ArgumentNullException ( "connection"); // Create a command and prepare it for execution IDbCommand cmd = connection.CreateCommand (); bool mustCloseConnection = false; PrepareCommand (cmd, connection, (IDbTransaction) null, commandType, commandText, CommandParameters, Out MustCloseConnection; Cleanparametersyntax (CMD);
DataSet DS = ExecuteTedataset (CMD);
IF (MustCloseConnection "Connection.Close ();
// Return The DataSet Return DS;
/// /// Execute a stored procedure via an IDbCommand (that returns a resultset) against the specified IDbConnection /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return value parameter. /// /// /// /// DataSet DS = helper.executedataset (conn, "getorders", 24, 36); /// /// a valid idbconnection /// The name of the stored procedure /// An array of objects to be assigned as the input values of the stored procedure /// A DataSet containing the resultset generated by the command /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values supplied /// Thrown if connection is null public virtual DataSet ExecuteDataset (IDbConnection 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 )) {IDataParameter [] iDataParameterValues = getDataParameters (parametervalues.length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteDataset (connection, CommandType.StoredProcedure, spName, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (connection, spName, includeReturnValue);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters return ExecuteDataset (connection, CommandType.StoredProcedure, spName, commandParameters);}} else {// Otherwise we can just call the SP without params return ExecuteDataset (connection, CommandType.StoredProcedure, spName }}
/// //////// //// /// DataSet DS = HELPER .Executedataset (trans, "getordty"); /// /// a valid idbtransaction /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR SQL Command /// a Dataset Containg the resultset generated by the command /// Thrown if commandText is null /// Thrown if transaction is null /// Thrown if transaction.Connection is null public virtual DataSet ExecuteDataset (IDbTransaction transaction, commandType commandType, string commandText) { // Pass Through The Call Providing Null for the Set of iDataParameters Return Executedata (Transaction, CommandType, CommandText, (iDataParameter [] NULL);}
/// /// Execute an idbcommand (That Returns a resultset) against the specified idbtransaction /// > /// /// / // DataSet DS = helper.executedataset (Trans, CommandType.StoredProcedure, "GetRDERS", New IdataParameter ("@PRODID", 24)); /// /// a valid idbtransaction /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or SQL command /// An array of IDataParameters used to execute the command /// A DataSet containing the resultset generated by the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// TH rown if commandText is null /// Thrown if the parameter count does not match the number of values supplied /// Thrown if transaction is null /// thrown if transaction.connection is null
public virtual DataSet ExecuteDataset (IDbTransaction transaction, CommandType commandType, string commandText, params IDataParameter [] commandParameters) {if (transaction == null) throw new ArgumentNullException ( "transaction");! if (transaction = null && transaction.Connection == null ) throw new ArgumentException ( ". The transaction was rolled back or commited, please provide an open transaction", "transaction"); // Create a command and prepare it for execution IDbCommand cmd = transaction.Connection.CreateCommand (); bool mustCloseConnection = false; PrepareCommand (CMD, Transaction.Connection, Transaction, CommandType, CommandText, CommandParameters, Out MustCloseConnection); Cleanparametersyntax (CMD);
Return EXECUTEDASET (CMD);
}
/// /// Execute a stored procedure via an IDbCommand (that returns a resultset) against the specified /// IDbTransaction 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// /// /// DataSet DS = Helper.executedataset (TRAN, "GetRDERS", 24, 36); /// /// a valid idbtransaction /// The name of the stored procedure /// An array of objects to be assigned as the input values of the stored procedure /// A DataSet containing the resultset generated by the command /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values supplied /// thrown if Transaction is null /// thrown if transaction.connection is null
public virtual DataSet ExecuteDataset (IDbTransaction 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 rolled back 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)!) {IDataParameter [] iDataParameterValues = GetDataParameters (parameterValues.Length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteDataset (transaction, CommandType.StoredProcedure, spName, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (transaction.Connection, spName, includeReturnValue);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters return ExecuteDataset (transaction, CommandType.StoredProcedure, spName, commandParameters);}} else {// Otherwise we can just call the SP without params return ExecuteDataset (transaction, CommandType.StoredProcedure, spName );} #ENDREGION EXECUTEDASET
#Region ExecutenonQuery
/// /// Execute An idbcommand (That Returns no results) against the database /// /// The idbcommand to execute /////////////// An int representing the number of rows affected by the command /// Thrown if command is null. public virtual int ExecuteNonQuery (IDbCommand command) { Bool MustCloseConnection = false;
// Clean Up Parameter Syntax Cleanparametersyntax; Command;
IF (Command.Connection.State! = connectionState.Open) {command.connection.open (); mustcloseConnection = true;
IF (command == null) throw new argumentnullexception ("Command");
int ReturnVal;
Returnval = Command.executenonQuery ();
IF (MustCloseConnection) {Command.connection.Close ();
Return returnval;}
/// /// Execute an IDbCommand (that returns no resultset and takes no parameters) against the database specified in /// the connection string /// /// A Valid connection string for an idbconnection /// The commandtype (Stored Procedure, Text, etc.) /// The stored procedure name or SQL command /// An int representing the number of rows affected by the command /// Thrown if connectionString is null < / exception> /// Thrown if commandText is null public virtual int ExecuteNonQuery (string connectionString, commandType commandType, string commandText) {// Pass through the call providing null for the Set of iDataParameters Return ExecutenonQuery (Connectionstring, CommandType, CommandText, (iDataParameter [] NULL);}
/// /// Execute an IDbCommand (that returns no resultset) against the database specified in the connection string /// using the provided parameters /// /// A Valid connection string for an idbconnection /// The commandtype (Stored Procedure, Text, etc.) /// The stored procedure name or SQL command /// An array of IDataParameters used to execute the command /// An int representing the number of rows affected by the command /// Thrown if connectionString is null /// Thrown if any of the IDataParameters.ParameterNames are null , or if the parameter count does not match the number of values supply /// Thrown if CommandText is N ull /// Thrown if the parameter count does not match the number of values supplied public virtual int ExecuteNonQuery (string connectionString, CommandType commandType, string commandText, params IDataParameter [] CommandParameters) {if (Connectionstring == Null || Connectionstring.Length == 0) Throw new argumentnullexception ("Connectionstring");
// Create & open an IDbConnection, and dispose of it after we are done using (IDbConnection connection = GetConnection (connectionString)) {connection.Open (); // Call the overload that takes a connection in place of the connection string return ExecuteNonQuery (Connection, CommandType, CommandText, CommandParameters);}}
/// /// Execute a stored procedure via an IDbCommand (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. /// /// /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// /// a valid connection string for an idbconnection /// The name of the stored prcedure /// An array of objects to be assigned as the input values of the stored procedure /// An int representing The number of rows affected by the command /// Thrown if connectionString is Null /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values supplied public virtual 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)) {IDataParameter [] iDataParameterValues = GetDataParameters (ParameterValues.Length);
// if we've been passed IDataParameters, do not do parameter discoveryu if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteNonQuery (connectionString, CommandType.StoredProcedure, spName, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (connectionString, spName, includeReturnValue);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters return ExecuteNonQuery (connectionString, CommandType.StoredProcedure, spName, commandParameters);}} else {// Otherwise we can just call the SP without params return ExecuteNonQuery (connectionString, CommandType.StoredProcedure, spName }}
/// /// ////// /// a valid idbconnection /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR SQL Command /// An int representing the number of rows affected by the command /// Thrown if commandText is null /// Thrown if connection is null public virtual int ExecuteNonQuery (IDbConnection connection, commandType commandType, string commandText) {// Pass through the call providing null for the set of IDataParameters return ExecuteNonQuery (connection, commandType, Commandtext, (iDataParameter [] NULL);}
/// /// Execute An idbcommand (That Returns no results) against the specified idbconnection /// @ /// /// /// a valid idbconnection /// The CommandType (Stored Procedure, Text, ETC.) /// The stored procedure name or SQL command /// An array of IDbParamters used to execute the command /// An int representing the number of rows affected by the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thrown if commandText is null /// Thrown if the parameter count does not match the number o f values supplied /// Thrown if connection is null public virtual int ExecuteNonQuery (IDbConnection connection, CommandType commandType, string commandText, params IDataParameter [] commandParameters) { IF (Connection == NULL) Throw new Argumentnullexception ("Connection");
// Create a command and prepare it for execution IDbCommand cmd = connection.CreateCommand (); bool mustCloseConnection = false; PrepareCommand (cmd, connection, (IDbTransaction) null, commandType, commandText, commandParameters, out mustCloseConnection); CleanParameterSyntax (cmd); // finally, execute the command int RetVal = ExecutenonQuery (cmd);
// Detach the iDataParaseters from the command Object, so. Can do this ... screen ... screws up output parameters - cjbreisch // cmd.parameters.clear (); if (MustCloseConnection) Connection.Close (); Return retrom;
/// /// Execute a stored procedure via an IDbCommand (that returns no resultset) against the specified IDbConnection /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// // /// a valid idbconnection /// The name of the storedure < / param> /// An array of objects to be assigned as the input values of the stored procedure /// An int representing the number of rows affected by the command /// thrown if spname is null /// THR own if the parameter count does not match the number of values supplied /// Thrown if connection is null public virtual int ExecuteNonQuery (IDbConnection 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)!) {IDataParameter [] iDataParameterValues = GetDataParameters (parameterValues.Length); // if we 've been passed IDataParameters, do not do parameter discoveryu if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteNonQuery (connection, CommandType.StoredProcedure, spName, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (connection, spName, includeReturnValue);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters return ExecuteNonQuery (connection, CommandType.StoredProcedure, spName, commandParameters);}} else {// Otherwise we can just call the SP without params return ExecuteNonQuery (connection, CommandType.StoredProcedure, spName }}
/// //////// /////// /// a valid idbtaction /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR SQL Command /// An int representing the number of rows affected by the command /// Thrown if commandText is null /// Thrown if transaction is null /// Thrown if transaction.Connection is null public virtual int ExecuteNonQuery (IDbTransaction transaction, commandType commandType, String commandtext) {// pass through the call providing null for the set of iDataParameters Return ExecutenonQuery (Transaction, CommandType, Commandte XT, (iDataParameter [] NULL);
/// /////////////////////////////////// a Valid IDBTRANSACTION /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or sql commist < / param> /// An array of IDataParameters used to execute the command /// An int representing the number of rows affected by the command // / Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thrown if commandText is null /// thrown if the parameter count does not match the number of values support /// Thrown if transaction is null /// Thrown if transaction.Connection is null public virtual int ExecuteNonQuery (IDbTransaction transaction, commandType commandType, string commandText, params IDataParameter [] commandParameters) {if (transaction == null) throw new ArgumentNullException ( "transaction");! if (transaction = null &&
transaction.Connection == null) throw new ArgumentException ( "The transaction was rolled back or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution IDbCommand cmd = transaction.Connection. CreateCommand (); bool mustCloseConnection = false; PrepareCommand (cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); CleanParameterSyntax (cmd);
// finally, execute the command int RetVal = ExecutenonQuery (cmd);
// DETACH The IdataParaseters from the Command Object, So The CAN Be Used Again // Don't Do this ... Screws Up Output Parameters - CJBREISCH / / CMD.Parameters.clear (); Return RetVal;
/// /// Execute a stored procedure via an IDbCommand (that returns no resultset) against the specified /// IDbTransaction 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. // /// a valid idbtransaction /// The name of the storedure /// An array of objects to be assigned as the input values of the stored procedure /// An int representing the number of rows affected by the command /// thrown if spname is null /// thrown if the parameter count does not match the number of values supplied /// Thrown if transaction is null /// Thrown if transaction.Connection is null public virtual int ExecuteNonQuery (IDbTransaction 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 rolled back 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)!) {IDataParameter [] iDataParameterValues = GetDataParameters (parameterValues. Length);
// if we've been passed IDataParameters, do not do parameter discoveryu if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteNonQuery (transaction, CommandType.StoredProcedure, spName, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (transaction.Connection, spName, includeReturnValue);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDbParameters return ExecuteNonQuery (transaction, CommandType.StoredProcedure, spName, commandParameters);}} else {// Otherwise we can just call the SP without params return ExecuteNonQuery (transaction, CommandType.StoredProcedure, spName }}
#ENDREGON EXECUTENONQUERY
#Region ExecuteReader
/// /// execute an idbcommand (That Returns a resultset) against the database specified in /// /// The idbcommand object to use /// A IDataReader containing the resultset generated by the command /// Thrown if command is null. public virtual IDataReader ExecuteReader (IDbCommand command) {return ExecuteReader (command, AdoConnectionOwnership.External);} /// /// Execute an IDbCommand (that returns a resultset) against the database specified in /// the connection string /. // /// The idbcommand object to use /// ENUM INDICATING WHETER THE Connection Was create Param> /// a iDataReader containing the resultset generated by the command /// Thrown if command is null protected virtual IDataReader ExecuteReader (IDbCommand command, AdoConnectionOwnership connectionOwnership) {// Clean Up Parameter Syntax CleanParameterSyntax (command).;
IF (Command.connection.State! = connectionState.Open) {command.connection.open (); connectionownership = adoconnectionownership.internal;}
// CREATE A Reader iDataReader DataReader;
// Call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == AdoConnectionOwnership.External) {dataReader = command.ExecuteReader ();} else {try {dataReader = command.ExecuteReader (CommandBehavior.CloseConnection);} catch (Exception ex) {/ / Don't Just Throw EX. It Changes The Call Stack. But Want The Exped for Debugging, So ... Debug.Writeline (ex); throw;}} clearcommand;
Return DataReader;}
/// /// create and prepare an idbcommand, and call executereader with the appropriate commandbehavior. // /// /// if we created and opened the connection, we want the connection, WAwant THE Connection to Be Closed. ///// i i t c l i i t = m l i i i = "connection" > A valid idbconnection, on which to execute this command /// a valid idbtransaction, or 'null' /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR SQL Command /// an array of IDataParameters to be associated with the command or 'null' if no parameters are required /// Indicates whether the connection parameter was provided by the caller, or created by AdoHelper /// i DataReader containing the results of the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thrown if commandText is null /// Thrown if the parameter count does not match the number of values supplied <
/ Exception> /// Thrown if connection is null private IDataReader ExecuteReader (IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDataParameter [] commandParameters, AdoConnectionOwnership connectionOwnership) { if (connection == null) throw new ArgumentNullException ( "connection"); bool mustCloseConnection = false; // Create a command and prepare it for execution IDbCommand cmd = connection.CreateCommand (); try {PrepareCommand (cmd, connection, transaction, CommandType, CommandText, CommandParameters, Out MustCloseConnection; Cleanparametersyntax (CMD);
// Override connctionownership if we created The connection in prepareCommand - CJBREisch if (MustCloseConnection) {ConnectionOwnership = adoconnectionownership.internal;
// CREATE A Reader iDataReader DataReader;
DataReader = ExecuteReader (CMD, ConnectionOwnership);
ClearCommand (CMD);
Return DataReader;} Catch {if (MustCloseConnection) connection.close (); throw;}}
/// /// Execute an IDbCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// /// A valid connection string for an idbconnection /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or SQL command /// A IDataReader containing the resultset generated by the command /// Thrown if connectionString is null /// Thrown if commandText is null public virtual IDataReader ExecuteReader (string connectionString, commandType commandType, string commandText) {// Pass through the call providing null for the set Of iDataParameters Return ExecuteReader (Connectionstring, CommandType, CommandText, (iDataParameter [] NULL);}
/// /// Execute an IDbCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// /// A valid connection string for an idbconnection /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or SQL command /// An array of IDataParameters used to execute the command /// A IDataReader containing the resultset generated by the command /// Thrown if connectionString is null /// Thrown if any of the IDataParameters.ParameterNames are null, OR iF the parameter count does not match the number of values supply /// thrown if commandtext is null /// Thrown if the parameter count does not match the number of values supplied public virtual IDataReader ExecuteReader (string connectionString, CommandType commandType, string commandText, params IDataParameter [] commandParameters) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString"); IDbConnection connection = null; try {connection =
GetConnection (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, AdoConnectionOwnership.Internal);} catch { // if we fail to return the iDataReader, We need to close the connection uerselves if (connection! = Null) connection.close (); throw;}
}
/// /// Execute a stored procedure via an IDbCommand (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. /// /// /// This method provides no access to output parameters Or the stored propedure's return value parameter. /// /// /// /// iDataReader Dr = Helper.executeReader (Connstring, "GetRDERS", 24, 36); // / /// a valid connection string for an idbconnection /// The name of the stored procedure /// an array of objects to be assigned as the input value of the stored procedure /// an idatarader containing the resultset ge nerated by the command /// Thrown if connectionString is null /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values supplied public virtual IDataReader 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 The Go IF ((ParameterValues! = Null) && (parameterValues.length> 0) {iDataParameter [] iDataParameterValues = getDataParameters (ParameterValues.length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteReader (connectionString, CommandType.StoredProcedure, spName, iDataParameterValues);} else {bool includeReturnValue = CheckForReturnValueParameter (parameterValues) IDataParameter [] CommandParameters = GetSpparameters (Connectionstring, SPNAME, IncludereturnValue);
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);}}
/// /// Execute An idbcommand (That Returns A ResultSet and takes no parameters) against the provided idbconnection. /// /// /// /// iDataReader Dr = helper.executeReader (conn, commandtype.storedproceder); /// /// a valid idbconnection /// < Param name = "CommandType"> The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or sql command /// an IDataReader containing the resultset generated by the command /// Thrown if commandText is null public virtual IDataReader ExecuteReader (IDbConnection connection, commandType commandType, string commandText) { // Pass Through The Call Providing Null for the Set of iDataParameters Return ExecuteRead (Connection, CommandType, CommandText, (iDataParameter [] null);}
/// /// Execute An idbcommand (That Returns a resultset) against the specified idbconnection //// //// /// / // iDataReader Dr = Helper.executeReader (CONN, CommandType.StoredProcedure), "GetRDERS", New IdataParameter ("@PRODID", 24)); /// /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or SQL command /// An array of IDataParameters used to execute the command /// an IDataReader containing the resultset generated by the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thrown if commandText is null /// Thrown if the parameter count does not match the number of values supplied /// thrown if connection is null
public virtual IDataReader ExecuteReader (IDbConnection connection, CommandType commandType, string commandText, params IDataParameter [] commandParameters) {// Pass through the call to the private overload using a null transaction value and an externally owned connection return ExecuteReader (connection, (IDbTransaction) null CommandType, CommandText, CommandParameters, Adoconnectionownership.external;
/// /// Execute a stored procedure via an IDbCommand (that returns a resultset) against the specified IDbConnection /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// /// /// iDataReader DR = Helper.executeReader (CONN, "GetRDERS", 24, 36); /// /// a valid idbconnection /// The name of the stored procedure /// An array of objects to be assigned as the input values of the stored procedure /// an IDataReader containing the resultset generated by the command /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values supplied /// Thrown if connection is null public virtual IDataReader ExecuteReader (IDbConnection 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 )) {IDataParameter [] iDataParameterValues = getDataParameters (parametervalues.length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteReader (connection, CommandType.StoredProcedure, spName, iDataParameterValues);} else {bool includeReturnValue = CheckForReturnValueParameter (parameterValues) IDataParameter [] CommandParameters = GetSpparameters (Connection, SPNAME, INCLUDERETURNVALUE);
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);}}
/// //////// //////////////////////////////////////////// d iaterer dr = helper . Execute, "getordtype.storedprocedure," getorders "); /// /// a valid idbtransaction /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR SQL Command /// a iDataReader Containing the resultset generated by the command /// Thrown if commandText is null public virtual IDataReader ExecuteReader (IDbTransaction transaction, commandType commandType, string commandText) {// Pass THROUGH THE CALL PROVIDING NULL for the set of iDataParameters Return ExecuteRead (Transaction, CommandType, CommandText, (iDataParameter [] null;}
/// /// Execute an idbcommand (That Returns a resultset) against the specified idbtransaction /// > /// /// / // iDataReader Dr = helper.executeReader (Trans, CommandType.StoredProcedure, "GetRDERS", New IdataParameter ("@PRODID", 24)); /// /// a valid idbtransaction /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or SQL command /// An array of IDataParameters used to execute the command /// A IDataReader containing the resultset generated by the command public virtual IDataReader ExecuteReader (IDbTransaction transaction, commandType commandType, string commandText, params IDataParameter [] commandParameters) {if (transaction == null) throw new ArgumentNullException ( "transaction"); if (transa ! Ction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rolled back 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, CommandType, CommandText, CommandParameters, Adoconnectionownership.external);
/// /// Execute a stored procedure via an IDbCommand (that returns a resultset) against the specified /// IDbTransaction 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. // /// /// /// iDataReader DR = Helper.executeReader (TRAN, "GetRDERS", 24, 36); /// /// a valid idbtransaction /// The name of the stored procedure /// An array of objects to be assigned as the input values of the stored procedure /// an IDataReader containing the resultset generated by the command /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values supplied /// thrown if transaction is null ///
public virtual IDataReader ExecuteReader (IDbTransaction 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 rolled back 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)!) {IDataParameter [] iDataParameterValues = GetDataParameters (parameterValues.Length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteReader (transaction, CommandType.StoredProcedure, spName, iDataParameterValues);} else {bool includeReturnValue = CheckForReturnValueParameter (parameterValues) IDataParameter [] CommandParameters = GetSpparameters (TRANSACTION.CONNECTION, SPNAME, INCLUDERETURNVALUE);
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);}}
#ENDREGON EXECUTEREADER
#Region ExecuteScalar
/// /// ///////////////// /// The IDbCommand to execute /// An object containing the value in the 1x1 resultset generated by the command /// Thrown if command is null. < / Exception> Public Virtual Object ExecuteScalar (IDBCommand Command) {BOOL MUSTCLOSECONNECONNECTION = false; // Clean Up Parameter Syntax CleanparametersyNTax (Command);
IF (Command.Connection.State! = connectionState.Open) {command.connection.open (); mustcloseConnection = true;
// Execute the command & retURN The results object retval = command.executescalar ();
// Detach the IdataParameters from the command Object, so. Can do this ... screws up output params - cjbreisch // command.Parameters.clear ();
IF (MustCloseConnection) {Command.connection.Close ();
Return RetVal;
/// /// execute an idbcommand (That Returns a 1x1 Resultset and takes no parameters) Against the database specified in /////// Summary> /// // / /// int ordercount = (int) helper.executescalar (connString, commandtype.storedProcedure); /// /// A valid connection string for an idbconnection /// The CommandType (Stored Procedure, Text, etc.) /// The store procedure name or SQL command /// An object containing the value in the 1x1 resultset generated by the command /// Thrown if connectionString is null /// Thrown if commandText is null public virtual object ExecuteScalar (string connectionString, commandType commandType, string commandText) {// P ASS THROUGH THE CALL PROVIDING NULL for the set of iDataParaseters Return Executescalar (Connectionstring, CommandType, CommandText, (iDataParameter [] NULL);}
/// /// Execute An idbcommand (That Returns a 1x1 results) against the database specified in the connections String /// using the provided parameters. /// /// a valid connection string for an idbconnection /// The commandtype (stored procedure, text, etc.) /// The stored procedure name or SQL command /// An array of IDataParameters used to execute the command /// An object containing the value in the 1x1 resultset generated by the command /// Thrown if connectionString is null /// Thrown if any of the IDataParameters .Parameternames area null, or if the parameter count does not match the number of values support /// thrown i f commandText is null /// Thrown if the parameter count does not match the number of values supplied public virtual object ExecuteScalar (string connectionString, CommandType commandType, string Commandtext, params idataparameter [] commandparameters) {if (connectionstring == null || Connectionstring.Length == 0) throw new argumentnullexception ("Connectionstring"); // Create &
open an IDbConnection, and dispose of it after we are done IDbConnection connection = null; try {connection = GetConnection (connectionString); connection.Open (); // Call the overload that takes a connection in place of the connection string return ExecuteScalar ( Connection, CommandType, CommandText, CommandParameters;} finally {idisposable id = connections as idisposable; if (id! = null) id.dispose ();}}
/// /// Execute a stored procedure via an IDbCommand (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 /// stiled procedure (The First Time Each Stored Procedure (The First Time Each Stored Procedure), And Assign The VALUES BASED ON Parameter ORDER. /// /// // this method provides no access to output Parameters or the storeter. /// /// /// /// int = (int) Helper.executescalar (ConnString, "GetOrdercount", 24, 36 ); /// a valid connection string for an idbconnection /// the name of the Stored procedure /// an array of objects to be assigned as the input value of the stiled procedure /// an Object Containing the VALU e in the 1x1 resultset generated by the command /// Thrown if connectionString is null /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values supplied public virtual 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 The Go IF ((ParameterValues! = Null) && (parameterValues.length> 0) {iDataParameter [] iDataParameterValues = getDataParameters (ParameterValues.length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteScalar (connectionString, CommandType.StoredProcedure, spName, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (connectionString, spName, includeReturnValue);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters return ExecuteScalar (connectionString, CommandType.StoredProcedure, spName, commandParameters);}} else {// Otherwise we can just call the SP without params return ExecuteScalar (connectionString, CommandType.StoredProcedure, spName }}
/// /// Execute An IDBCOMMAND (That Returns a 1x1 ResultSet and takes no parameters) against the provided idbconnection. /// /// /// /// Int ORDERCOUNT = (int) helper.executescalar (conn, commandtype.storedProcedure); /// /// a valid idbconnection /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR SQL Command // / An object containing the value in the 1x1 resultset generated by the command /// Thrown if commandText is null public virtual object ExecuteScalar (IDbConnection connection , commandType commandType, string commandText) {// Pass through the call providing null for the set of IDbParameters return ExecuteScalar (connection, commandType, commandText, (IDataParameter []) null);}
/// /// Execute an idbcommand (That Returns a 1x1 results) against the specified idbconnection //// /// a Valid idbconnection /// The commandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name OR SQL Command /// An array of IDataParameters used to execute the command /// An object containing the value in the 1x1 resultset generated by the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// thrown if commandtext is null /// thrown if the parameter count does not match the number of values su pplied /// Thrown if connection is null public virtual object ExecuteScalar (IDbConnection connection, CommandType commandType, string commandText, params IDataParameter [] commandParameters) {if ( Connection == NULL) Throw new argumentnullexception ("connection"); // create a command and prepare it for execution idbcommand cmd = connection.createcommand ();
bool mustCloseConnection = false; PrepareCommand (cmd, connection, (IDbTransaction) null, commandType, commandText, commandParameters, out mustCloseConnection); CleanParameterSyntax (cmd); // Execute the command & return the results object retval = ExecuteScalar (cmd);
// DETACH The IdataParaseters from The Command Object, So The CAN Be Used Again // Don't Do this ... Screws Up Output Parameters - CJBREISCH / / CMD.Parameters.clear ();
IF (MustCloseConnection "Connection.Close ();
Return RetVal;
/// /// Execute a stored procedure via an IDbCommand (that returns a 1x1 resultset) against the specified IDbConnection /// 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. /// /// /// This method provides no access to output parameters or the stored Procedure's return value parameter. /// /// /// /// int ordercount = (int) Helper.executescalar (conn, "getordercount", 24, 36); // / /// a valid idbconnection /// The name of the stored procedure // / An array of objects to be assigned as the input values of the stored procedure /// An object containing the value in the 1x1 resultset generated by the command /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values supplied /// Thrown if connection is null public virtual object ExecuteScalar (IDbConnection 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 ut Where the Go if ((ParameterValues! = NULL) && Parametervalues.length> 0)) {iDataParameter [] iDataParameterValues = getDataParameters (parametervalues.length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteScalar (connection, CommandType.StoredProcedure, spName, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (connection, spName, includeReturnValue);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters return ExecuteScalar (connection, CommandType.StoredProcedure, spName, commandParameters);}} else {// Otherwise we can just call the SP without params return ExecuteScalar (connection, CommandType.StoredProcedure, spName }}
/// /// Execute An idbcommand (That Returns a 1x1 Resultset and takes no parameters) against the provided idbtransaction. /// /// /// /// Int ORDERCOUNT = (int) helper.executescalar (TRAN, CommandType.StoredProcedure); /// /// a valid idbtransaction /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR SQL Command // / An object containing the value in the 1x1 resultset generated by the command /// Thrown if commandText is null public virtual object ExecuteScalar (IDbTransaction transaction , commandType commandType, string commandText) {// Pass through the call providing null for the set of IDataParameters return ExecuteScalar (transaction, commandType, commandText, (IDataParameter []) null);}
/// /// execute an idbcommand (That Returns a 1x1 resultset) against the specified idbtransaction //// /// a Valid idbtransaction /// The commandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name OR SQL COMMAND /// An array of IDbParamters used to execute the command /// An object containing the value in the 1x1 resultset generated by the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// thrown if commandText is null /// thrown if the parameter count does not match the number of values SUP plied /// Thrown if transaction is null /// Thrown if transaction.Connection is null public virtual object ExecuteScalar (IDbTransaction transaction, commandType commandType, string commandText, params IDataParameter [] commandParameters) {if (transaction == null) throw new ArgumentNullException ( "transaction");! if (transaction = null &&
transaction.Connection == null) throw new ArgumentException ( "The transaction was rolled back or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution IDbCommand cmd = transaction.Connection. CreateCommand (); bool mustCloseConnection = false; PrepareCommand (cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); CleanParameterSyntax (cmd);
// Execute the command & return the results object return; executescalar (cmd);
// DETACH The IdataParaseters from the Command Object, So The CAN Be Used Again // Don't Do this ... Screws Up Output Parameters - CJBREISCH / / CMD.Parameters.clear (); Return RetVal;
/// /// Execute a stored procedure via an IDbCommand (that returns a 1x1 resultset) against the specified /// IDbTransaction 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. /// /// /// This method provides no access to output parameters or the stored Procedure's return value parameter. /// /// /// /// int ordercount = (int) Helper.executescalar (TRAN, "GetOrdercount", 24, 36); // / /// a valid idbtransaction /// The name of the stored procedure // / An array of objects to be assigned as the input values of the stored procedure /// An object containing the value in the 1x1 resultset generated by the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thrown if commandText is null /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values support
/ Exception> /// Thrown if transaction is null /// Thrown if transaction.Connection is null / // Thrown if the transaction is rolled back or commmitted public virtual object ExecuteScalar (IDbTransaction 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 rolled back 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) {iDataParameter [] iDataParameterValues = getDataParameters (paramet Ervalues.Length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {return ExecuteScalar (transaction, CommandType.StoredProcedure, spName, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (transaction.Connection, spName, includeReturnValue); // Assign the provided values to these parameters based on Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters return ExecuteScalar (transaction, CommandType.StoredProcedure, spName, commandParameters);}} else {// Otherwise we can just call the SP without params return ExecuteScalar (transaction, CommandType.StoredProcedure, spName }}
#ndregion executescalar
#Region ExecutexmlReader
/// /// execute an idbcommand (That Returns a resultset) against the provided idbconnection. /// /// /// /// Xmlreader R = helper.executexmlreader (conn, commandtype.storedprocedure); /// /// a valid idbconnection /// < Param name = "commandtype"> The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or sql command us "for xml auto /// An XmlReader containing the resultset generated by the command public XmlReader ExecuteXmlReader (IDbConnection connection, commandType commandType, string commandText) {// Pass through the call providing null for the set of IDataParameters return ExecuteXmlReader (connection , CommandType, CommandText, (iDataParameter [] NULL);}
/// /// Execute An idbcommand (That Returns a resultset) against the specified idbconnection //// //// /// / // xmlreader r = helper.executexmlreader (conn, commandtype.storedproceder), "@PRODID", GetParameter ("@ products", 24)); /// /// A valid idbconnection /// The CommandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name or SQL command using "FOR XML AUTO" /// An array of IDataParameters used to execute the command /// An XmlReader containing the resultset generated by the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thrown if commandText is null /// Thrown if the parameter count does not match the number of values supplied /// Thrown if connection is null public XmlReader ExecuteXmlReader (IDbConnection connection, commandType commandType, string commandText, params IDataParameter [] commandParameters) {if (connection ==
null) throw new ArgumentNullException ( "connection"); bool mustCloseConnection = false; // Create a command and prepare it for execution IDbCommand cmd = connection.CreateCommand (); try {PrepareCommand (cmd, connection, (IDbTransaction) null, commandType, CommandText, CommandParameters, Out MustCloseConnection; Cleanparametersyntax (CMD);
Return ExecutexmlReader (CMD);} catch (exception ex) {if (MustCloseConnection) Connection.Close (); // Don't Just Throw EX. It Changes The Call Stack. But Want The Exped for Debugging, SO .. . Debug.writeLine (ex); throw;}}
/// /// Execute a stored procedure via an IDbCommand (that returns a resultset) against the specified IDbConnection /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// /// /// xmlreader r = helper.executexmlreader (conn, "getorders", 24, 36); /// /// a valid idbconnection /// The name of the stored procedure Using "for xml auto" /// An array of objects to be assigned as the input values of the stored procedure /// An XmlReader containing the resultset generated by the command / // Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values supplied /// Thrown if connection is null <
/ Exception> public XmlReader ExecuteXmlReader (IDbConnection 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) ArrayList tempParameter = new ArrayList (); foreach (IDataParameter parameter in GetSpParameterSet (connection, spName)) {tempParameter.Add (parameter);} IDataParameter [] commandParameters = (IDataParameter []) Tempparameter.toArray (IdataParameter);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters return ExecuteXmlReader (connection, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteXmlReader (connection, CommandType.StoredProcedure, spName) }}
/// /// execute an idbcommand (That Returns A ResultSet and takes no parameters) against the provided idbtransaction. /// /// /// /// xmlreader R = helper.executexmlreader (TRAN, CommandType.StoredProcedure); /// /// a valid idbtransaction /// < Param name = "commandtype"> The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or sql command us "for xml auto /// An XmlReader containing the resultset generated by the command public XmlReader ExecuteXmlReader (IDbTransaction transaction, commandType commandType, string commandText) {// Pass through the call providing null for the set of IDataParameters return ExecuteXmlReader (transaction , CommandType, CommandText, (iDataParameter [] NULL);}
/// /// Execute an idbcommand (That Returns a resultset) against the specified idbtransaction /// > /// /// / // XmlReader R = helper.executexmlreader (TRAN), "GetRDERS", getParameter ("@PRODID", 24)); /// /// A valid idbtransaction /// The CommandType (Stored Procedure, Text, ETC.) /// The stored procedure name or SQL command using "FOR XML AUTO" /// An array of IDataParameters used to execute the command /// An XmlReader containing the resultset generated by the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thrown if commandText is null /// Thrown if the parameter count does not match the number of values supplied /// thrown if Transaction is null /// thrown if transaction.connection is null
public XmlReader ExecuteXmlReader (IDbTransaction transaction, CommandType commandType, string commandText, params IDataParameter [] commandParameters) {if (transaction == null) throw new ArgumentNullException ( "transaction"); if (! transaction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rolled back or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution IDbCommand cmd = transaction.Connection.CreateCommand (); bool mustCloseConnection = False; PrepareCommand (CMD, Transaction.Connection, Transaction, CommandType, CommandText, CommandParameters, Out MustCloseConnection); Cleanparametersyntax (CMD);
// Create The DataAdapter & Dataset Xmlreader Retval = ExecutexmlReader (CMD);
// DETACH The IdataParameters from the command Object, so. Can do this ... screws up output params - cjbreisch // cmd.Parameters.clear (); return retrom}
/// /// Execute a stored procedure via an IDbCommand (that returns a resultset) against the specified /// IDbTransaction 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// /// /// xmlreader r = helper.executexmlreader (Trans, "GetRDERS", 24, 36); /// /// a valid idbtransaction /// The name of the stored procedure Using "for xml auto /// An array of objects to be assigned as the input values of the stored procedure /// An XmlReader containing the resultset generated by the command /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thrown if spName is null /// Thrown if the parameter count does not match the number of values supplied ///
Thrown if transaction is null /// Thrown if transaction.Connection is null public XmlReader ExecuteXmlReader (IDbTransaction transaction, string spName, params object [] parameterValues) { if (transaction == null) throw new ArgumentNullException ( "transaction"); ". The transaction was rolled back or commited, please provide an open transaction" if (! transaction = null && transaction.Connection == null) throw new ArgumentException ( , "transaction"); if (spname == null ||) throw new argumentnullexception ("spname"); // if We received Parameter Values, We need to Figure ut Where the Go IF ((ParameterValues ! = null) && (parameterValues.Length> 0)) {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) ArrayList tempParameter = new ArrayList (); foreach (IDataParameter parameter in GetSpParameterSet (Transaction.Connection, SPNA ME)) {Tempparameter.Add (parameter);} iDataParameter [] CommandParameters = (iDataParameter []) Tempparameter.Toarray (TypeOf (iDataParameter);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters return ExecuteXmlReader (transaction, CommandType.StoredProcedure, spName, commandParameters);} else {// Otherwise we can just call the SP without params return ExecuteXmlReader (transaction, CommandType.StoredProcedure, spName) }}
#ENDREGON EXECUTEXMLReader # Region ExecutexmlReadertypedParams
/// /// Execute a stored procedure via an IDbCommand (that returns a resultset) against the specified IDbConnection /// using the dataRow column values as the stored procedure's parameters values. /// This method will assign the parameter Values based on parameter ORDER. /// /// The idbcommand to execute /// The datarow buy to hold the store procedure's parameter values. /// An XmlReader containing the resultset generated by the command /// Thrown if command is null. Public XMLReader ExecutexmlReadertypedParams (IDBCommand Command, DataRow DataRow) {if (Command == Null) Throw new ArgumentnullException ("Command");
// If the row has values, the store procedure parameters must be initialized if (dataRow = null && dataRow.ItemArray.Length> 0!) {// Set the parameters values AssignParameterValues (command.Parameters, dataRow);
Return EXECUTEXMLREADER (Command);} else {return executexmlreader (Command);}}
/// /// Execute a procedure via an stored IDbCommand (that returns a resultset) against the specified IDbConnection /// using the dataRow column values as the stored procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (The First Time Each Stored Procedure (The First Time Each Stored Procedure IS Called), And Assign The VALUES BASED On Parameter ORDER. /// /// a valid IDbConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An XmlReader containing the resultset generated by the command /// Thrown if spName is null /// Thrown if connection is null Public XMLReader ExecutexmlReadertypedParams (IDBConnection Connection, S tring 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) ArrayList tempParameter = new ArrayList (); foreach (IDataParameter parameter in GetSpParameterSet (connection, spName)) {tempParameter.Add (parameter);} IDataParameter [] commandParameters = (IDataParameter []) tempParameter.ToArray (typeof (IDataParameter) ); // set the parameters Values AssignParameterValues (CommandParameters, DataRow);
Return Executexmlreader (Connection, CommandType.StoredProcedure);} else {return executexmlreader (connection, commandtype.storedprocedure);}}
/// /// Execute a procedure via an stored IDbCommand (that returns a resultset) against the specified IDbTransaction /// using the dataRow column values as the stored procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (the first time each storedure), and assign the values based on parameter ORDER. /// /// a valid IDbTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An XmlReader containing the resultset generated by the command /// Thrown if spName is null /// thrown if transaction is null /// Thrown if Transac tion.Connection is null public XmlReader ExecuteXmlReaderTypedParams (IDbTransaction transaction, String spName, DataRow dataRow) {if (transaction == null) throw new ArgumentNullException ( "transaction");! if (transaction = null && transaction.Connection = = null) throw new ArgumentException ( "The transaction was rolled back 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) ArrayList tempParameter = new ArrayList (); foreach (IDataParameter parameter in GetSpParameterSet (transaction.Connection, spName)) {tempParameter.Add (parameter);} IDataParameter [] commandParameters = (IDataParameter []) tempParameter.ToArray (typeof ( IDataParameter); // set the parameters value assignparameterValues (CommandParameters, DataRow);
Return Executexmlreader (Transaction, CommandType.StoredProcedure);} else {return executexmlreader (Transaction, CommandType.StoredProcedure, spname);}}
#ndregion
#Region FillDataSet
/// /// execute an idbcommand (That Returns a resultset) against the database specified in /// /// The idbcommand To Execute /// a Dataset Wich Will Contain the resultset generated by the strand /// this Array Will Be buy to create table mappings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// Thrown if command is null. Public Virtual Void FillDataSet (IDBCommand Command, String [] Tablenames) {BOOL MUSTCLOSECONNECTION = FALSE
// Clean Up Parameter Syntax Cleanparametersyntax (Command); IF (Command.Connection.State! = ConnectionsTate.Open) {Command.connection.Open (); MustCloseConnection = true;
// Create The DataAdapter & Dataset IdbdataAdapter DataAdapter = NULL; TRY {DataApter = getDataAdapter (); dataadapter.selectCommand = Command;
// Add the table mappings specified by the user if (tableNames = null && tableNames.Length> 0!) {String tableName = "Table"; for (int index = 0; index
// Fill The Dataset Using Default Values for DataTable Names, etc DataAdapter.Fill (Dataset);
IF (MustCloseConnection) {Command.connection.Close ();
// Detach the iDataParasers from the command Object, so. Can do this ... screen ... screws up output params --cjb // command.Parameters.clear ();} finally {idisposable id = dataAdapter As IDisposable; if (id! = null) id.dispose ();
}
/// /// Execute An idbcommand (That Returns A ResultSet and takes no parameters) against the database specified in //// /// /// /// helper.filldataset (Connstring, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"Orders"}); /// /// a valid connection string for an idbconnection //// The CommandType (Stored Procedure, Text, ETC.) /// the storedure name or sql command /// a Dataset Wich Will Contain The ResultSet generated by the command /// This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name /// Thrown if Connectionstring is null /// Thrown if commandText is null public virtual 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 an IDbConnection, and dispose of it after we are done IDbConnection connection = null; try {connection = GetConnection (connectionString); connection.Open (); // Call the overload that takes a connection in place of the connection String FillDataSet (Connection, CommandType, CommandText, DataSet, Tablenames);} finally {idisposable id = connection as idisposable; if (id! = null) id.dispose ();}}
/// /// Execute an IDbCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// /// A valid connection string for an idbconnection /// The CommandType (Stored Procedure, Text, etc.) /// THE Stored Procedure Name or SQL Command /// An array of idataparameters use to execute the command /// a Dataset Wich Will contain the resultset generated by the command /// 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) /// /// thrown if connectionString is null /// thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thrown if commandText is null /// Thrown if the parameter count does not match the number of values supplied
public virtual void FillDataset (string connectionString, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames, params IDataParameter [] commandParameters) {if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException ( "connectionString "); if (dataSet == null) throw new ArgumentNullException (" dataSet "); // Create & open an IDbConnection, and dispose of it after we are done IDbConnection connection = null; try {connection = GetConnection (connectionString); connection .Open (); // Call the overload that takes a connection in place of the connection string FillDataset (connection, commandType, commandText, dataSet, tableNames, commandParameters);} finally {IDisposable id = connection as IDisposable; if (id =! NULL) ID.DISPOSE ();}}
/// /// Execute a stored procedure via an IDbCommand (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. /// /// /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// /// /// Helper.FillDataSet (ConnString, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"ORDERS"}, 24); /// /// a valid connection string for an idbconnection //// The name of the storedure /// a Dataset Wich Will Contain The ResultSet generated by the command /// 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) /// /// An array of objects to be assigned as the input values of the stored procedure /// Thrown if connectionString is null /// Thrown if spname is null /// Thrown if the parameter count does not match the number of values supplied public virtual 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 an IDbConnection, and dispose of it After WE Are Done IDBConnection Connection = NULL; TRY {Connection = getConnection (ConnectionString); Connection.Open ();
// Call the overload that takes a connection in place of the connection string FillDataset (connection, spName, dataSet, tableNames, parameterValues);} finally {IDisposable id = connection as IDisposable; if (! Id = null) id.Dispose () }}
/// /// Execute An IDBCOMMAND (That Returns A ResultSet and takes no parameters) against the provided idbconnection. /// /// /// /// Helper .Filldataset (conn, commandtype.storedProcedure, "GetRDERS", DS, New String [] {"ORDERS"}); /// /// a Valid IDBConnection /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or sql commist < / parame> /// a dataset wich will contact the resultset generated by the command /// this array will be used to create Table mapings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// /// Thrown if commandText is null / // Thrown if connection is null public virtual void FillDataset (IDbConnection connection, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames) {FillDataset (connection, commandType, commandText, dataSet, tableNames, null);}
/// /// execute an idbcommand (That Returns a resultset) against the specified idbconnection //// /// a Valid IDBConnection /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or sql commist < / parame> /// a dataset wich will contact the resultset generated by the command /// this array will be used to create Table mapings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// /// An array of IDataParameters used to execute the command /// Thrown if commandText is null /// Thrown if connection is null public virtual void FillDataset (IDbConnection connection, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames, params IDataParameter [] commandParameters) {FillDataset (connection, null, commandType, commandText, dataSet, tableNames, commandParameters);}
/// /// Execute a stored procedure via an IDbCommand (that returns a resultset) against the specified IDbConnection /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// /// /// Helper.FillDataSet (conn, "getorders", DS, new string [] {"Orders"}, 24, 36); /// a valid idbconnection // The name of the storedure < / parame> /// a dataset wich will contact the resultset generated by the command /// this array will be used to create Table mapings allowing The dataatables to be reasoned // / by a user defined name /// /// an array of objects to be assigned as the input value of the stored procedure /// thrown if spname is null /// 0)) {iDataParameter [] iDataParameterValues = getDataParameters (ParameterValues.length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {FillDataset (connection, CommandType.StoredProcedure, spName, dataSet, tableNames, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (connection, spName, includeReturnValue);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters 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;}}
/// /// Execute an idbcommand (That Returns a resultset and takes no parameters) against the provided idbtransaction. /// /// /// /// Helper .Filldataset (TRAN, COMMANDTYPE.STOREDPROCEDURE, "GetORDERS", DS, New String [] {"Orders"}); /// /// a Valid IDBTRANSACTION /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or sql commist < / parame> /// a dataset wich will contact the resultset generated by the command /// this array will be used to create Table mapings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// /// Thrown if commandText is null / // thrown if transaction is nu ll /// Thrown if transaction.Connection is null public virtual void FillDataset (IDbTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames ) {FillDataSet (Transaction, CommandType, CommandText, Dataset, Tablenames, NULL);
/// ////////////////////////////////// /// a Valid IDBTRANSACTION /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or sql commist < / parame> /// a dataset wich will contact the resultset generated by the command /// this array will be used to create Table mapings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// /// An array of IDataParameters used to execute the command /// Thrown if commandText is null /// Thrown if transaction is null /// Thrown if transaction.Connection is null public virtual void FillDataset (IDbTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames, params IDataParameter [] commandParameters) {FillDataset (transaction.Connection, transaction , CommandType, CommandText, Dataset, Tablenames, CommandParameters;
/// /// Execute a stored procedure via an IDbCommand (that returns a resultset) against the specified /// IDbTransaction 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// /// /// Helper.FillDataSet (TRAN, "GetRDERS", DS, New String [] {"Orders"}, 24, 36); /// /// a valid idbtransaction /// The name of the storedure < / parame> /// a dataset wich will contact the resultset generated by the command /// this array will be used to create Table mapings allowing The DataTables to be Referenced /// by a user defined name /// /// an array of objects to be assigned as the input value of the stored procedure /// Thrown if spName is null /// Thrown if transaction is null /// < Exception cref = "system.Argumentnullexception"> thrown if transaction.connection is null
public virtual void FillDataset (IDbTransaction 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 rolled back 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)) {iDataParameter [] iDataParameterValues = getDataParameters (ParameterValues.length);
// if we've been passed IDataParameters, do not do parameter discovery if (AreParameterValuesIDataParameters (parameterValues, iDataParameterValues)) {FillDataset (transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, iDataParameterValues);} else {// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) bool includeReturnValue = CheckForReturnValueParameter (parameterValues); IDataParameter [] commandParameters = GetSpParameterSet (transaction.Connection, spName, includeReturnValue);
// Assign The Provided Values To The Parameters Based On Parameter Order AssignParameterValues (CommandParameters, ParameterValues);
// Call the overload that takes an array of IDataParameters 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;}}
/// /// Private helper method that execute an IDbCommand (that returns a resultset) against the specified IDbTransaction and IDbConnection /// using the provided parameters. /// /// a valid idbconnection /// a valid idbtransaction /// The CommandType (Stored Procedure, Text, ETC. /// The storedure name or sql command /// a Dataset Wich Will Contain The ResultSet generated by the Command < / param> /// 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) /// /// An array of IDataParameters used to execute the command private void FillDataset (IDbConnection connection, IDbTransaction transaction, commandType commandType, string commandText, DataSet dataSet, string [] tableNames, params IDataParameter [] commandParameters) {if (connection == null) throw new ArgumentNullException ( "connection"); if (dataSet == null) throw new ArgumentNullException ( "dataSet");
// Create a command and prepare it for execution IDbCommand command = connection.CreateCommand (); bool mustCloseConnection = false; PrepareCommand (command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); CleanParameterSyntax (command); FillDataset (command , Dataset, TableNames;
IF (MustCloseConnection) connection.close ();
#ndregion
#REGON UpdatedataSet
/// /////////// /// The object that generated the object That Generated The Object That Generated THE event /// The System.Data.Common.RowUpdatingEventArgs protected void RowUpdating (object obj, System.Data.Common.RowUpdatingEventArgs e) {if (this.m_rowUpdating ! = null) M_RowUpdating (Obj, E);
/// ////////////////////////////////////////////////////////////////// The object this generated event /// The System.Data.Common.RowUpdatingEventArgs protected void RowUpdated (object obj, System.Data.Common.RowUpdatedEventArgs e) {if (this.m_rowUpdated ! = null) M_RowUpdated (Obj, E);
/// /// SET UP A Command for Updating A Dataset. /// Command Object to prepare /// output parameter specifying whether the connection used should be closed by the DAAB /// An IDbCommand object protected virtual IDbCommand SetCommand (IDbCommand command, out bool mustCloseConnection) {mustCloseConnection = false; if (command = null!) {IDataParameter [] commandParameters = new IDataParameter [command.Parameters.Count]; command.Parameters.CopyTo (commandParameters, 0); command.Parameters.Clear (); this.PrepareCommand (command, Command.connection, null, command.commandtype, command.commandtext, commandparameters, out mustcloseconnection; cleanparametersyntax (Command);} Return Command;
/// /// /////////////// /// Helper. /// Helper. UpdateDataset (conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); /// /// A valid SQL statement or stored procedure to insert new records INTO the DATA Source /// a Valid SQL Statement or Stored Procedure to Delete Records from the data source /// The dataset used to update the data source /// The DataTable used to update the data source. public virtual void UpdateDataset (IDbCommand insertCommand, IDbCommand deleteCommand, IDbCommand updateCommand, DataSet dataSet, string tableName) {UpdateDataset (insertCommand, deleteComman D, UpdateCommand, DataSet, TableName, Null, NULL);
/// /// Executes The IDBCommand for Each Inserted, Updated, OR DELETED ROW in The Dataset Also Implementing Rowupdating and RowUpdated Event Handlers /// /// /// /// RowUpdatingEventHandler rowUpdatingHandler = new RowUpdatingEventHandler (OnRowUpdating); /// RowUpdatedEventHandler rowUpdatedHandler = new RowUpdatedEventHandler (OnRowUpdated); /// helper.UpdateDataSet (sqlInsertCommand, sqlDeleteCommand, sqlUpdateCommand, dataSet, "Order", rowUpdatingHandler, rowUpdatedHandler); // / /// a valid sql statement or stored procedure to insert new records inteo the data source /// A valid SQL statement or stored procedure to delete records from the data source /// A valid SQL statement or stored procedure used to update records in the data source // / The Dataset Used to Update The Data Sou RCE /// The datable used to update the data source. /// RowUpdatedEventHandler public void UpdateDataset (IDbCommand insertCommand, IDbCommand deleteCommand, IDbCommand updateCommand, DataSet dataSet, string tableName, RowUpdatingHandler rowUpdatingHandler, rowUpdatedHandler rowUpdatedHandler) {int rowsAffected = 0;
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException ( "tableName"); // Create an IDbDataAdapter, and dispose of it after we are done IDbDataAdapter dataAdapter = null; try {bool mustCloseUpdateConnection = false; Bool MustCloseInsertConnection = false; Bool MustclosedeleteConnection = false;
DataAdapter = getDataAdapter ();
// Set the data adapter commands dataAdapter.UpdateCommand = SetCommand (updateCommand, out mustCloseUpdateConnection); dataAdapter.InsertCommand = SetCommand (insertCommand, out mustCloseInsertConnection); dataAdapter.DeleteCommand = SetCommand (deleteCommand, out mustCloseDeleteConnection);
AddupdateEventHandlers (DataAdapter, RowupDatingHandler, RowupdatedHandler);
if (dataAdapter is DbDataAdapter) {// Update the DataSet changes in the data source try {rowsAffected = ((DbDataAdapter) dataAdapter) .Update (dataSet, tableName);} catch (Exception ex) {// Do not just throw ex . But we want debugging, so ... debug.writeline (ex); throw;}} else {dataadapter.tablemappings.add (TableName, "Table");
// update the dataset changes in the data source rowsaffected = dataadapter.Update (dataset);
// Commit all the change.tables [tablename] .acceptchanges ();
if (mustCloseUpdateConnection) {updateCommand.Connection.Close ();} if (mustCloseInsertConnection) {insertCommand.Connection.Close ();} if (mustCloseDeleteConnection) {deleteCommand.Connection.Close ();}} finally {IDisposable id = dataAdapter as Idisposable; if (id! = Null) id.dispose ();}} #ENDREGON
#Region CreateCommand
/// /// sIMPLIFY THE CREATION OF An IDBCommand Object By Allowing /// A Stored Procedure and Optional Parameters to Be provides /// /// /// / // idbcommand command = helper.createCommand (conn, "addcustomer", "customerid", "customername"); /// /// a valid connection string For an idbconnection the name of the stored procedure /// an array of string to be assigned as the source columns of the stored procedure parameters /// A valid IDbCommand object /// Thrown if connectionString is null /// Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied /// Thr own if spName is null /// Thrown if the parameter count does not match the number of values supplied public virtual IDbCommand CreateCommand (string connectionString, string spName, Params String [] SourceColumns) {Return CreateCommand (THISTCONNECIN), SPNAME, SOURCECOLUMNS;
/// /// sIMPLIFY THE CREATION OF An IDBCommand Object By Allowing /// A Stored Procedure and Optional Parameters to Be provides /// /// /// / // idbcommand command = helper.createCommand (CONN, "AddCustomer", "Customerid", "CustomerName"); /// /// a valid idbconnection object d /// The name of the stored procedure /// an array of string to be assigned as the source columns of the store procedure parameters /// A valid IDbCommand object /// Thrown if spName is null /// Thrown if connection is null public virtual IDbCommand CreateCommand (IDbConnection connection, string spName, params string [] sourceColumns) {if (connection == null) throw new ArgumentNullException (" connection "); if ( SP Name == null || spName.Length == 0) throw new ArgumentNullException ( "spName"); // Create an IDbCommand IDbCommand cmd = connection.CreateCommand (); cmd.CommandText = spName; 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) IDataParameter [] commandParameters = GetSpParameterSet (connection, spName); // Assign the provided source columns to these parameters based on parameter order for (int index = 0; index
// attach the discovered parameters to the idbcommand object attachparameters;}
Return cmd;}
/// /// sIMPLIFY THE CREATION OF An IDBCOMMAND Object By Allowing /// A Stored Procedure and Optional Parameters To Be provides /// /// a Valid Connection string for an idbconnection /// a valid sql statement /// a system.data.commandtype / // The parameters for the SQL statement /// A valid IDbCommand object public virtual IDbCommand CreateCommand (string connectionString, string commandText, commandType commandType, params IDataParameter [] CommandParameters) {Return CreateCommand (this.getConnection (Connectionstring), CommandText, CommandType, CommandParameters;
/// ////// A Stored Procedure and Optional Parameters To Be provides /// /// /// idbcommand Command = helper.createCommand (CONN, "AddCustomer", "Customerid", "Customername"); /// /// a valid idbconnection Object /// a valid sql statement /// a system.data.commandtype /// The parameters for the SQL statement /// A valid IDbCommand object public virtual IDbCommand CreateCommand (IDbConnection connection, string commandText, commandType commandType, params IDataParameter [] commandParameters) {if (connection == null) throw new ArgumentNullException ( "connection"); if (commandText == null || commandText.Length == 0) throw new ArgumentNullException ( "commandText"); // Create an IDbCommand IDbCommand cmd = c Onnection.createCommand (); cmd.commandtext = CommandText; cmd.commandtype = CommandType;
// If we receive parameter values, we need to figure out where they go if ((commandParameters! = Null) && (commandParameters.Length> 0)) {// Assign the provided source columns to these parameters based on parameter order for ( int index = 0; index
Return cmd;}
#ndregion
#Region ExecuteNonQuerytypedParams
/// /// Execute a procedure via an stored IDbCommand (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 Assign THE Parameter Values Based on Row Values. /// /// The idbcommand to execute /// The dataRow used to hold the stored procedure's parameter values. /// An int representing the number of rows affected by the command /// Thrown If Command is Null. Public Virtual Int ExecutenonQueryTypedParams (IDBCommand Command, DataRow DataRow) {int RetVal = 0;
// Clean Up Parameter Syntax Cleanparametersyntax; Command;
// If the row has values, the store procedure parameters must be initialized if (dataRow = null && dataRow.ItemArray.Length> 0!) {// Set the parameters values AssignParameterValues (command.Parameters, dataRow); retVal = ExecuteNonQuery ( Command;} else {retval = executenonQuery (Command);
Return RetVal;
/// /// Execute a stored procedure via an IDbCommand (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. /// /// a valid connection string for an idbconnection /// the name of the stored procedure /// The DataRow Used To hold the stored procedure's parameter values. /// An int representing the number of rows affected by the command /// Thrown if connectionString is null /// thrown if spname is null Public Virtual Int Execute NonQueryTypedParams (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 IdataParameter [] CommandParameters = GetSpparameters (CONNECTIONSTRING, SPNAME); // set the parameters Values AssignParameterValues (CommandParameters, DataRow);
return ExecuteNonQuery (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {return ExecuteNonQuery (connectionString, CommandType.StoredProcedure, spName);}}
/// /// Execute a procedure via an stored IDbCommand (that returns no resultset) against the specified IDbConnection /// using the dataRow column values as the stored procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (the first time each storedure (the first time each storedure), and assign the values based on row value. /// /// a valid IDbConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An int represenging the number of rows affected by the command /// thrown if spname is null /////////////////////////// thrown if connection is null Public Virtual Int ExecutenonQueryTypedParams (IDBConnection Connection, ST ring 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) IdataParameter [] CommandParameters = GetSpparameters (Connection, SPNAME);
// Set the parameters values AssignParameterValues (commandParameters, dataRow); return ExecuteNonQuery (connection, CommandType.StoredProcedure, spName, commandParameters);} else {return ExecuteNonQuery (connection, CommandType.StoredProcedure, spName);}}
/// /// Execute a procedure via an stored IDbCommand (that returns no resultset) against the specified /// IDbTransaction using the dataRow column values as the stored procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (The First Time Each Stored Procedure IS Called), And Assign The VALUES BASED On Row Values. /// /// a valid IDbTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An int represenging the number of rows affected by the command /// thrown if spname is null /////////////////////////// thrown if transaction is null /// throwniff.> throwniff. Connection is null public virtual int ExecuteNonQueryTypedParams (IDbTransaction transaction, String spName, DataRow dataRow) {if (transaction == null) throw new ArgumentNullException ( "transaction");! If (transaction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rolled back 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 IdataParameter [] CommandParameters = GetSpparameterSet (TRANSAMETERSET (SPNAME); // set the parameters Values AssignParameterValues (CommandParameters, DataRow);
Return ExecutenonQuery (Transaction, CommandType.StoredProcedure);} else {return executenonury (Transaction, CommandType.StoredProcedure);}}
#ndregion
#Region ExecuteTaseTtypedParams
/// /// Execute a stored procedure via an IDbCommand (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 Assign The Paraemter Values Based On Row Values. /// /// The idbcommand to execute /// The datarow used to hold the stored procedure's parameter values. /// A DataSet containing the resultset generated by the command /// Thrown if command is null. Public Virtual Dataset ExecuteDataSetTypedParams (IDBCommand Command, DataRow Datarow) {DataSet DS = NULL
// Clean Up Parameter Syntax Cleanparametersyntax; Command;
// If the row has values, the store procedure parameters must be initialized if (dataRow = null && dataRow.ItemArray.Length> 0!) {// Set the parameters values AssignParameterValues (command.Parameters, dataRow); ds = ExecuteDataset ( Command;} else {ds = executeataset (Command);
Return DS;
/// /// Execute a stored procedure via an IDbCommand (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. /// /// a valid connection string for an idbconnection /// the name of the stored procedure /// The DataRow Used To hold the stored procedure's parameter values. /// A DataSet containing the resultset generated by the command /// Thrown if connectionString is null // thrown if spname is null Public Virtual DataSet Execute DatasetTypedParams (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 IdataParameter [] CommandParameters = GetSpparameters (CONNECTIONSTRING, SPNAME); // set the parameters Values AssignParameterValues (CommandParameters, DataRow);
return ExecuteDataset (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {return ExecuteDataset (connectionString, CommandType.StoredProcedure, spName);}}
/// /// Execute a procedure via an stored IDbCommand (that returns a resultset) against the specified IDbConnection /// using the dataRow column values as the store procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (the first time each storedure (the first time each storedure), and assign the values based on row value. /// /// a valid IDbConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// a dataset containing the resultset generated by the command /// thrown if spname is null /// Thrown if connection is null Public Virtual DataSet ExecuteDataSettypedParams (IDBConnection 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) IDataParameter [] CommandParameters = GetSpparameterSet (Connection, SPNAME);
// Set the parameters values AssignParameterValues (commandParameters, dataRow); return ExecuteDataset (connection, CommandType.StoredProcedure, spName, commandParameters);} else {return ExecuteDataset (connection, CommandType.StoredProcedure, spName);}}
/// /// Execute a procedure via an stored IDbCommand (that returns a resultset) against the specified IDbTransaction /// using the dataRow column values as the stored procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (The First Time Each Stored Procedure IS Called), And Assign The VALUES BASED On Row Values. /// /// a valid IDbTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// a dataset containing the resultset generated by the command /// thrown if spname is null /// thrown if transaction is null /// thrown if transaction.con nection is null public virtual DataSet ExecuteDatasetTypedParams (IDbTransaction transaction, String spName, DataRow dataRow) {if (transaction == null) throw new ArgumentNullException ( "transaction"); if (transaction = null && transaction.Connection ==! null) throw new ArgumentException ( "The transaction was rolled back 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 IdataParameter [] CommandParameters = GetSpparameterSet (TRANSAMETERSET (SPNAME); // set the parameters Values AssignParameterValues (CommandParameters, DataRow);
Return EXECUTEDASET (Transaction, CommandType.StoredProcedure);} else {return executetate (transaction); transaction, commandtype.storedProcedure, spname
#ndregion
#Region ExecuteReadertypedParams
/// /// Execute a stored procedure via an IDbCommand (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 Assign The Parameter Values Based on Parameter ORDER. /// /// The idbcommand to execute /// The datarow used to hold the stored procedure's parameter values. /// A IDataReader containing the resultset generated by the command /// Thrown if command is null. Public Virtual IdataReader ExecuteReadertypedParams (IDBCommand Command, DataRow DataRow) {iDataReader Reader = NULL;
// Clean Up Parameter Syntax Cleanparametersyntax; Command;
// If the row has values, the store procedure parameters must be initialized if (dataRow = null && dataRow.ItemArray.Length> 0!) {// Set the parameters values AssignParameterValues (command.Parameters, dataRow); reader = ExecuteReader ( Command;} else {reader = executeReader (Command);
Return Reader;}
/// /// Execute a stored procedure via an IDbCommand (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. /// /// a valid connection string for an idbconnection /// the name of the stored procedure /// The DataRow Used To hold the stored procedure's parameter values. /// A IDataReader containing the resultset generated by the command /// Thrown if connectionString is null /// thrown if spname is null Public Virtual Idatar eader 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 IdataParameter [] CommandParameters = GetSpparameters (CONNECTIONSTRING, SPNAME); // set the parameters Values AssignParameterValues (CommandParameters, DataRow);
return ExecuteReader (connectionString, CommandType.StoredProcedure, spName, commandParameters);} else {return ExecuteReader (connectionString, CommandType.StoredProcedure, spName);}}
/// /// Execute a procedure via an stored IDbCommand (that returns a resultset) against the specified IDbConnection /// using the dataRow column values as the stored procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (The First Time Each Stored Procedure (The First Time Each Stored Procedure IS Called), And Assign The VALUES BASED On Parameter ORDER. /// /// a valid IDbConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// A IDataReader containing the resultset generated by the command /// Thrown if spName is null /// thrown if connection is null Public Virtual IdataReader ExecuteReadertypedParams (IDBConnection Conne ction, 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) iDataParameter [] CommandParameters = GetSpparameterSet (Connection, SPNAME);
// Set the parameters values AssignParameterValues (commandParameters, dataRow); return ExecuteReader (connection, CommandType.StoredProcedure, spName, commandParameters);} else {return ExecuteReader (connection, CommandType.StoredProcedure, spName);}}
/// /// Execute a procedure via an stored IDbCommand (that returns a resultset) against the specified IDbTransaction /// using the dataRow column values as the stored procedure's parameters values. /// This method will query the database To Discover THE Parameters for the /// stiled procedure (the first time each storedure), and assign the values based on parameter ORDER. /// /// a valid IDbTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// A IDataReader containing the resultset generated by the command /// Thrown if spName is null /// thrown if transaction is null /// thrown if transa ction.Connection is null public virtual IDataReader ExecuteReaderTypedParams (IDbTransaction transaction, String spName, DataRow dataRow) {if (transaction == null) throw new ArgumentNullException ( "transaction");! if (transaction = null && transaction.Connection == null) throw new ArgumentException ( "The transaction was rolled back 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 the & populate the cache) iDataParameter [] CommandParameters = GetSpparameters (Transaction.Connection, SPNAME);
// set the parameters Values AssignParameterValers (CommandParameters, DataRow);
Return ExecuteReader (Transaction, CommandType.storedProcedure);} else {return executeRead (Transaction, CommandType.StoredProcedure);}
#ndregion
#Region ExecuteScalrtypedParams
/// /// Execute a procedure via an stored IDbCommand (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 Assign THE Parameter Values Based on Parameter ORDER. /// /// The idbcommand to execute /// The DataRow used to hold the stored procedure's parameter values. /// An object containing the value in the 1x1 resultset generated by the command /// Thrown If Command Is Null. Public Virtual Object ExecuteScalrtypedParams (IDBCommand Command, DataRow DataRow) {Object Retval = NULL
// Clean Up Parameter Syntax Cleanparametersyntax; Command;
// If the row has values, the store procedure parameters must be initialized if (dataRow = null && dataRow.ItemArray.Length> 0!) {// Set the parameters values AssignParameterValues (command.Parameters, dataRow); retVal = ExecuteScalar ( Command;} else {retval = executescalar (commman);
Return RetVal;
/// /// Execute a procedure via an stored IDbCommand (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. /// /// a valid connection string for an idbconnection /// the name of the stored procedure /// The datarow used to hold the stored procedure's parameter values. /// An object containing the value in the 1x1 resultset generated by the command /// Thrown if Connectionstring is null /// thrown if spname is null Publ ic virtual 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 IdataParameter [] CommandParameters = GetSpparameters (CONNECTIONSTRING, SPNAME); // set the parameters Values AssignParameterValues (CommandParameters, DataRow);
Return ExecuteScalar (Connectionstring, CommandType.storedProcedure, SPNAME, CommandParameters);} else {return executescalar (connectionstring, commandtype.storedprocedure);}}
/// /// Execute a procedure via an stored IDbCommand (that returns a 1x1 resultset) against the specified IDbConnection /// 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. /// /// A valid IDbConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values . /// An object containing the value in the 1x1 resultset generated by the command /// Thrown if spName is null /// Thrown if connection is null Public Virtual Object ExecuteScalrtypedParams (idbconn ection 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) iDataParameter [] CommandParameters = GetSpparameterSet (CONNECTION, SPNAME); // set the parameters valuezing; CommandParameters, DataRow;
Return ExecuteScalar (Connection, CommandType.storedProcedure, SPName, CommandParameters);} else {return executescalar (connection, commandtype.storedprocedure);}}
/// /// Execute a procedure via an stored IDbCommand (that returns a 1x1 resultset) against the specified IDbTransaction /// 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. /// /// A valid IDbTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values . /// An object containing the value in the 1x1 resultset generated by the command /// Thrown if spName is null /// thrown if transaction is null /// Thrown if transaction.Connection is null public virtual object ExecuteScalarTypedParams (IDbTransaction transaction, String spName, DataRow dataRow) {if (transaction == null) throw new ArgumentNullException ( "transaction");! If (transaction = null && transaction .Connection == NULL) Throw new ArgumentExce ("THE Transaction Was Rolled Back or Commited," 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 thermally the cache) iDataParameter [] CommandParameters = GetSpparameters (Transaction.Connection, SPNAME);
// set the parameters Values AssignParameterValers (CommandParameters, DataRow);
Return ExecuteScalar (Transaction, CommandType.StoredProcedure, SPName, CommandParameters);} else {return executescalar (Transaction, CommandType.StoredProcedure, spname);}}
#ndregion
#Region Parameter Discovery Functions
/// /// Checks for the existence of a return value parameter in parametervalues /// /// an array of objects to be assigned as the input values of the stored procedure /// true if the parameterValues contains a return value parameter, false otherwise private bool CheckForReturnValueParameter (object [] parameterValues) {bool hasReturnValue = false; foreach (object paramObject in parameterValues) {if (paramObject is IDataParameter) {IDataParameter paramInstance = (IDataParameter) paramObject; if (paramInstance.Direction == ParameterDirection.ReturnValue) {hasReturnValue = true; break;}}} return hasReturnValue;}
/// /// Check to see if the parameter value passed to the helper area, in fact, idataparameters. /// /// array of parameter Values Pass to helper /// new array of IDataParameters built from parameter values /// True if the parameter values are IDataParameters private bool AreParameterValuesIDataParameters ( object [] parameterValues, IDataParameter [] iDataParameterValues) {bool areIDataParameters = true; for (int i = 0; i
/// /// Retrieves the set of idataparameters appropriate for the storedure /// /// /// this method will query the Database for this information, and the store ket a cache for future requests. /// /// a valid connection string for an idbconnection /// Thrown if connectionString is null /// Thrown if spName is null public virtual IDataParameter [] GetSpParameterSet (string connectionString, string spName) {return GetSpParameterSet (connectionString, spName, false);}
/// /// Retrieves the set of idataparameters appropriate for the storedure /// /// /// this method will query the Database for this information, and the store ket a cache for future requests. /// /// a valid connection string for an idbconnection /// A bool value indicating whether the return value parameter should be included in the results /// An array of IDataParameters /// Thrown if connectionString is null /// Thrown if spName is null public virtual IDataParameter [] GetSpParameterSet (String SPNAME, BOOL INCLUDERETURNVALUEPAREPAREPARETER) {IF (Connectionstring == Null || Connectionstring.Length == 0) throw new ArgumentNullException ( "connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException ( "spName"); using (IDbConnection connection = GetConnection (connectionString)) {return GetSpParameterSetInternal (connection, spName, includeReturnValueParameter }}
/// /// Retrieves the set of idataparameters appropriate for the storedure /// /// /// this method will query the Database for this information, and the store ket a cache for future requests. /// /// a valid iDataConnection Object /// The name of the stored procedure < / param> /// An array of IDataParameters /// Thrown if spName is null /// Thrown if connection is null Public Virtual IdataParameter [] getSpparameterset (IDBConnection Connection, String SpName) {Return GetSpparameterSet (Connection, SPNAME, FALSE);
/// /// Retrieves the set of idataparameters appropriate for the storedure /// /// /// this method will query the Database for this information, and the store ket a cache for future requests. /// /// a valid idbconnection object /// The name of the stored procedure < / param> /// A bool value indicating whether the return value parameter should be included in the results /// An array of IDataParameters /// Thrown if spName is null /// Thrown if connection is null public virtual IDataParameter [] GetSpParameterSet (IDbConnection connection , String SpName, Bool IncludereturnValueParameter) {if (Connection == Null) Throw new argumentnullexception ("connection"); if (Connection As IcloneAbl e == null) throw new ArgumentException ( "can Yao discover parameters if the connection doesn Yao implement the ICloneable interface", "connection"); IDbConnection clonedConnection = (IDbConnection) ((ICloneable) connection) .Clone (); return GetSpParameterSetInternal ( CloneyConnection, SpName, IncludereturnValueParameter;
/// /// Retrieves the set of idataparameters appropriate for the stored procedure /// /// a valid idbconnection Object /// The name of the stored procedure /// A bool value indicating whether the return value parameter should be included in the results /// < returns> An array of IDataParameters /// Thrown if spName is null /// Thrown if connection is null private IDataParameter [] GetSpParameterSetInternal (IDbConnection 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 (IncludereturnValuepa Rameter? ": Include ReturnValue Parameter": "");
Idataparameter [] cachedparameters;
CachedParameters = GetCachedParameterSet (Connection, SPNAME (IncludereturnValueParater ":": ");
if (cachedParameters == null) {IDataParameter [] spParameters = DiscoverSpParameterSet (connection, spName, includeReturnValueParameter); CacheParameterSet (connection, spName (includeReturnValueParameter? ": include ReturnValue Parameter": ""), spParameters); cachedParameters = ADOHelperParameterCache.CloneParameters (spParameters);
Return Cachedparameters;
/// /// Retrieve a parameter array from the cache /// /// a valid connection string for an idbconnection /// The stored procedure name or SQL command /// An array of IDataParameters /// Thrown if connectionString is null < / exception> /// Thrown if commandText is null public IDataParameter [] GetCachedParameterSet (string connectionString, string commandText) {using (IDbConnection connection = GetConnection (connectionString)) {return GetCachedParametersetInternal (Connection, CommandText);}}
/// /// Retrieve a parameter array from the cache /// /// a valid idbconnection object /// The stored procedure name or SQL command /// An array of IDataParameters /// Thrown if commandText is null /// Thrown if connection is null public IDataParameter [] GetCachedParameterSet (IDbConnection connection, string commandText) {return GetCachedParameterSetInternal (connection, commandText);} /// /// RETRIEVE A Parameter Array from the cache /// /// a valid idbconnection object /// The stored procedure Name or SQL Command /// an array of idataparameters private idataParameter [] getCachedParametersetinternal (IDBConnection Connection, String Commandte) xt) {bool mustCloseConnection = false; // this way we control the connection, and therefore the connection string that gets saved as a hash key if (connection.State = ConnectionState.Open) {connection.Open ();! mustCloseConnection = true }
IdataParameter [] parameters = adohelperparametercache.getCachedParametersTring, CommandText), Download, COMMANDTEXT
IF (MustCloseConnection) {connect.close ();
Return Parameters;
/// /// add parameter array to the cache /// /// a valid connection string for an idbconnection /// The stored procedure name or SQL command /// An array of IDataParameters to be cached public void CacheParameterSet (string connectionString, string commandText, params IDataParameter [] commandParameters) {using (IDbConnection connection = GetConnection (connectionString)) {CacheParameterSetInternal (connection, commandText, commandParameters);}} /// /// Add parameter array to the cache /// / // a valid idbconnection /// The stored procedure name or sql command /// An Array OdataParameters to Be Cached Public Void CacheParameterSet (IDBConnection Connection, String CommandText, Params IdataParameter [] COMMA ndParameters) {if (connection is ICloneable) {using (IDbConnection clonedConnection = (IDbConnection) ((ICloneable) connection) .Clone ()) {CacheParameterSetInternal (clonedConnection, commandText, commandParameters);}} else {throw new InvalidCastException ();} }
/// /// add parameter array to the cache /// /// a valid idbconnection /// The stored procedure name or SQL command /// An array of IDataParameters to be cached private void CacheParameterSetInternal (IDbConnection connection, string commandText, params IDataParameter [] commandParameters) {// this way we control the connection, and therefore the connection string that gets saved as a hask key connection.Open (); ADOHelperParameterCache.CacheParameterSet (connection.ConnectionString, commandText, commandParameters); connection.Close ();}
/// /// resolve at run time the appropriate set of iDataParameters for a stiled procedure /// /// a valid idbconnection Object // / The name of the stored procedure /// Whether or not to include their return value parameter /// The parameter array discovered. /// Thrown if spName is null /// Thrown if connection is null private IDataParameter [] DiscoverSpParameterSet (IDbConnection connection, string spName, bool includeReturnValueParameter) {if (connection == null) throw new ArgumentNullException ( "connection"); if (spName == null || spName.Length == 0) throw New argumentnullexception ("spname"); idbcommand cmd = connection.createCommand (); cmd.commandtext = SPNAME; cmd.commandtype = commandtype.storedProc Edure;
Connection.open (); DeriveParameters (CMD); connection.close ();
if (! includeReturnValueParameter) {// not all providers have return value parameters ... do not just remove this parameter indiscriminately if (cmd.Parameters.Count> 0 && ((IDataParameter) cmd.Parameters [0]). Direction = = ParameterDirection.ReturnValue) {cmd.parameters.removeat (0);}}
Idataparameter [] discoveredparameters = new iDataParameter [cmd.parameters.count];
Cmd.Parameters.copyTo (DiscoveredParameters, 0);
// Init the parameters with a DBNull value foreach (IDataParameter discoveredParameter in discoveredParameters) {discoveredParameter.Value = DBNull.Value;} return discoveredParameters;} #endregion Parameter Discovery Functions}
#Region parametercache
/// /// ADOHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the /// ability to discover parameters for stored procedures at run-time. /// public sealed class ADOHelperParameterCache { Private static hashtable paramcache = hashtable.synchronized (new hashtable ());
/// /// Deep Copy of Cached IdataParameter Array /// /// /// Internal Static IdataParameter [] CloneParameters (iDataParameter) {iDataParameter [] clonedparameters = new iDataParameter [OriginalParameters.Length];
For (int i = 0, j = originalParameters.lendth; i
Return clonedparameters;
#Region Caching Functions
/// /// add parameter array to the cache /// /// a valid connection string for an idbconnection /// The stored procedure name or SQL command /// An array of IDataParameters to be cached /// Thrown if commandText is null /// Thrown if connectionString is null internal static void CacheParameterSet (string connectionString, string commandText, params IDataParameter [] 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;
/// /// Retrieve a parameter array from the cache /// /// a valid connection string for an idbconnection /// The stored procedure name or SQL command /// An array of IDataParameters /// Thrown if commandText is null < / exception> /// Thrown if connectionString is null internal static IDataParameter [] 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;
IdataParameter [] CachedParameters = paramcache [hashkey] as idataparameter []; if (cachedparameters == null) {Return Null;} else {return cloneparameters;}
#ndregion caching functions}
#ndregion
Public class daabsectionhandler: iConfigurationSectionHandler {#Region iConfigurationSectionHandler Members
/// /// Evaluates the given xml section and returns a Hashtable That Contains the results of the evalership. /// /// The Configuration settings in a Corresponding parent configuration section. /// An HttpConfigurationContext when Create is called from the ASP.NET configuration system. Otherwise, this parameter is reserved and is a null reference (Nothing in Visual Basic) . /// The xmlnode That Contains The Configuration Information To Be Handled. Provides Direct Access To The XML Contents of the Configuration Section. /// A Hashtable that contains the section's configuration settings public object Create (object parent, object configContext, XmlNode section) {Hashtable ht = new Hashtable ();. XmlNodeList list = section.SelectNodes ( "daabProvider"); foreach (XmlNode prov IN list) {if (provosttributes ["alias"] == null) throw new in validOperationException ( "The 'daabProvider' node must contain an attribute named 'alias' with the alias name for the provider."); if (prov.Attributes [ "assembly"] == null) throw new InvalidOperationException ( "The 'daabProvider' node must contain an attribute named 'assembly' with the name of the assembly containing the provider. "); if (prov.Attributes [" type "] == null) throw new InvalidOperationException (" The 'daabProvider' node must contain an attribute Named 'Type'
WITH FULL Name of the Type for the provider. "); HT [provRibutes [" alias "]. Value] = new providalias (provs.attributes [" assembly "]. value, prov.attributes [" type "] .Value);} Return HT;}
#ndregion}
/// /// t Class is forreading the 'providalias' tag from the 'daabProviders' section of the app.config file /// public class providealias {#Region Member Variables
Private string_assembly_asemblyname; private string _typename
#ndregion
#Region Constructionor
/// /// Constructor Required by iconfigurationSectionHandler /// /// The assembly where this provder can be true /// The Type of the provider public provoderalias (string asmblyname) {_assemblyName = askename; _typename = typeename
#ndregion
#Region Properties
/// /// Returns the Assembly name for this provider /// /// The Assembly name for the specified provider public string AssemblyName {get {return _assemblyName;} }
/// /// Returns the Type Name of this provider /// /// The type name of the specified provider public string typename {get {return_typename;} }
#ndregion}
/// /// The SqlServer class is intended to encapsulate high performance, scalable best practices for /// common uses of the SqlClient ADO.NET provider. It is created using the abstract factory in AdoHelper. /// < / summary> public class sqlserver: adohelper {/// /// Create a SQL Helper. Needs to be a default constructor so the the factory can create it /// public sqlserver () {} # Region Overrides
/// /// Returns an Array of Sqlparameters of the specified size /// /// size of the array /// The array of sqlparameters protected override idataparameter [] getDataParameters (int size) {return new sqlparameter [size];
/// /// Returns a SqlConnection Object for the given connection string /// /// The connection string to be used to create the connection /// a SqlConnection Object public override idbconnection getConnection (String Connectionstring) {Return New SqlConnection (Connectionstring);
/// /// Returns a SqlDataAdapter object /// /// The SqlDataAdapter public override IDbDataAdapter GetDataAdapter () {return new SqlDataAdapter ();}
/// /// Calls the CommandBuilder.DeriveParameters method for the specified provider, doing any setup and cleanup necessary /// /// The IDbCommand referencing the stored procedure . from which the parameter information is to be derived The derived parameters are added to the Parameters collection of the IDbCommand public override void DeriveParameters (IDbCommand cmd) {bool mustCloseConnection = false;. if ((cmd is SqlCommand)!) Throw new ArgumentException ("The Command Provided is Not a Sqlcommand Instance.", "CMD");
IF (cmd.connection.State! = connectionState.open) {cmd.connection.open (); mustcloseConnection = true;
SqlderiveParameters.deriveParameters ((SQLCommand) CMD);
IF (MustCloseConnection) {cmd.connection.close ();}}
/// /// Returns a SQLParameter Object /// /// The sqlparameter Object public override iDataParameter getParameter () {return new sqlparameter ();
/// /// DETACH The IdataParameters from The Command Object, So The CAN be Used Again. /// /// Command Object to clear protected override void ClearCommand (IDbCommand command) {// 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 IDataReader can set Yao ITS Values. // When this happen, The parameters can be used Again in Other Command. Bool CANCLEAR = TRUE
"iDATAPARAMETER COMMANDPARETER IN Command.Parameters) {if (CommandParameter.direction! = parameterDirection.input) can = false;}} (can) {command.Parameters.clear ();}}
/// /// this cleans up the parameter syntax for an SQL Server Call. This Was Split Out from prepareCommand SO That IT Could Be called independently. /// /// an idbcommand object containing the commandtext to clean. public override void cleanparametersyntax (idbcommand command) {// do nothing for sql}
/// /// Execute A Sqlcommand (That Returns A Resultset) against the provided sqlconnection. /// /// /// /// xmlreader r = helper. Executexmlreader (Command); /// /// The idbcommand to execute /// an Xmlreader Containing The ResultSet generated by the Command public override xmlreader executexmlreader (idbcommand command) {BOOL MUSTCLOSECONNECTION = FALSE
IF (Command.Connection.State! = connectionState.Open) {command.connection.open (); mustcloseConnection = true;
Cleanparametersyntax (Command); // Create The DataAdapter & Dataset XmlReader RetVal = ((SqlCommand) Command .ExecutexmlReader ();
// Detach The Sqlparameters from The Command Object, So The CAN Be Used Again // Don't Do this ... Screws Up Output Parameters - CJBREISCH / / CMD.Parameters.clear ();
IF (MustCloseConnection) {Command.connection.Close ();
Return RetVal;
/// /// provider specific code to set up the updating / ed Event handlers buy by updateDataSet /// /// DataAptapter to attach the Event Handlers To < / param> /// The Handler to Be Called When a Row Is Updating /// The Handler to Be Called When a Row Is Updated < / param> protected override void AddUpdateEventHandlers (IDbDataAdapter dataAdapter, rowUpdatingHandler rowUpdatingHandler, RowUpdatedHandler rowUpdatedHandler) {if (rowUpdatingHandler = null!) {this.m_rowUpdating = rowUpdatingHandler; ((SqlDataAdapter) dataAdapter) .RowUpdating = new SqlRowUpdatingEventHandler (RowUpdating);} if (RowupdatedHandler! = null) {this.m_rowupdated = rowupdatedhandler; ((SqlDataAdapter) DataAdapter) .roWUpdated = New SqlrowUpdatedEventHandler (RowUpdated);}
/// /// Handles the rowupdating Event /// /// The Object what published the evenet /// The sqlrowupdatingeventargs protected (Object Obj, SqlrowUpdatingeventArgs E) {base.rowupdating (Obj, e);
/// /// Handles the Rowupdated Event /// /// The Object That Published The Event /// The sqlrowupdatedEventArgs protected (Object Obj, SqlrowuPdatedEventArgs E) {base.rowupdated (Obj, e);
/// /// Handle Any Provider-Specific Issues with blobs Here by "Washing" The iDataParameter and return a new one "set up appropriately for the provider. /// /// The idbconnection to use in cleansing the parameter /// The parameter before cleaning /// The parameter after it's been cleansed. Protected Override iDataParameter getBlobParameter (iDbconnection connection, iDataParameter P) {// do nothing special for blobs ... as far as we know now. Return P;} #ENDREGON}
#Region derive parameters
// We create our own class to do this because the existing ADO.NET 1.1 implementation is broken internal class SqlDeriveParameters {internal static void DeriveParameters (SqlCommand cmd) {string cmdText;. SqlCommand newCommand; SqlDataReader reader; ArrayList parameterList; SqlParameter sqlParam; CommandType CMDTYPE; STRING ProcedureEname; int groupnumber; sqltransaction trnsql = cmd.transaction;
CMDTYPE = cmd.commandtype;
if ((cmdType == CommandType.Text)) {throw new InvalidOperationException ();} else if ((cmdType == CommandType.TableDirect)) {throw new InvalidOperationException ();}! else if ((cmdType = CommandType.StoredProcedure) ) {Throw new invalidopertyleException ();
Procedurename = cmd.comMandtext; string server = null; string database = null; procedureschema = null;
// split out the procedure name to get the server, database, etc. GetProcedureTokens (ref procedureName, ref server, ref database, ref procedureSchema); // look for group numbers groupNumber = ParseGroupNumber (ref procedureName);
Newcommand = NULL;
// set up the command string We use sp_procuedure_params_rowset to get the parameters if (database = null!) {CmdText = string.Concat ( "[", database, "] .. sp_procedure_params_rowset");. If (! Server = null) {Cmdtext = String.concat (Server, ".", Cmdtext);
// be careful of transactions if (trnSql = null!) {NewCommand = new SqlCommand (cmdText, cmd.Connection, trnSql);} else {newCommand = new SqlCommand (cmdText, cmd.Connection);}} else {// be careful of transactions if (trnSql = null!) {newCommand = new SqlCommand ( "sp_procedure_params_rowset", cmd.Connection, trnSql);} else {newCommand = new SqlCommand ( "sp_procedure_params_rowset", cmd.Connection);}}
newCommand.CommandType = CommandType.StoredProcedure; newCommand.Parameters.Add (new SqlParameter ( "@ procedure_name", SqlDbType.NVarChar, 255)); newCommand.Parameters [0] .Value = procedureName;
// make sure we specify if (IsEmptyString (procedureSchema)!) {NewCommand.Parameters.Add (new SqlParameter ( "@ procedure_schema", SqlDbType.NVarChar, 255)); newCommand.Parameters [1] .Value = procedureSchema;}
// Make Sure We Specify The GroupNumber IF WERE GIVEN One IF (Groupnumber! = 0) {newcommand.parameters.add (New Sqlparameter ("@ group_number", groupnumber);}
Reader = null; parameterlist (); try {// Get a reader full of {// Get a reader full of outcommand.executeReader (); sqlparam = null;
while (reader.Read ()) {// get all the parameter properties that we can get, Name, type, length, direction, precision sqlParam = new SqlParameter (); sqlParam.ParameterName = (string) (reader [ "PARAMETER_NAME" ]); Sqlparam.sqldbtype = getSqldbType ("Reader [" Type_name "]);
IF (Reader ["Character_Maximum_length"]! = dbnull.value) {SQLParam.Size = (int) ("Character_maximum_length"]);}
SqlParam.direction = getParameterDirection ((Short) (Reader ["parameter_type"]);
IF ((SqlParam.sqldbType == SqlDbType.Decimal) {sqlparam.scale = (Byte) (Reader ["Numeric_Scale"]) & 255); SQLParam.Precision = (Byte) ((Short) (Reader ["Numeric_Precision"] & 255));} parameterlist.add (sqlparam);}} finally {// close Our Reader! = null) {reader.close ( NEWCOMMAND.CONNECTION = NULL;}
// We Didn't get any parameters if (parameterlist.count == 0)) {throw new invalidopertyException ();
CMD.Parameters.clear ();
// add the parameters to the command object
Foreach (Object Parameter In Parameterlist) {cmd.parameters.add (parameter);}}
/// /// Checks to See eti the stiled procedure being called is part of a group . procedure being called This method may change this parameter by removing the group number if it exists /// the group number private static int ParseGroupNumber (ref string procedure) {string newProcName;. int groupPos = procedure.IndexOf ( ';'); int groupIndex = 0; if (groupPos> 0) {newProcName = procedure.Substring (0, groupPos); try {groupIndex = int.Parse (procedure.Substring (groupPos 1) );} Catch {throw new invalidOperationException ();}} else {newprocname = 0;
Procedure = newprocname; return groupindex;}
/// ///// /// the procedure name /// the Server name /// The database name /// The Owner Name Private static void getProceduretoKens (Ref string procedure , Ref string server, ref string {string [] spnameToKens; int Arrindex; int nextpos; int curpos; int tokencount;
Server = null; database = null; owner = NULL;
SpNameToKens = New string [4];
if (! iSemptystring (Procedure)) {ArrIndex = 0; NextPOS = 0; Currpos = 0;
While ((ArrIndex <4)) {currpos = procedure.indexof ('.', nextpos); if ((-1 == currpos)) {spnametoKens [arrIndex] = procedure.substring (nextpos); Break;} spnametokens [ Arrindex] = procedure.substring (blacks, currpos - nextpos); NextPOS = (Currpos 1); if (procedure.length <= nextpos) {Break;} arrIndex = (ArrIndex 1);} tokencount = Arrindex 1;
// Based, We know (token) {casse 1: procedure = spnametoke; case 2: procedure = spnametoKens [1]; oowner = spnametokeks [0 ]; break; case 3: procedure = spNameTokens [2]; owner = spNameTokens [1]; database = spNameTokens [0]; break; case 4: procedure = spNameTokens [3]; owner = spNameTokens [2]; database = spNameTokens [1]; server = spnametokens [0]; Break;}}}
/// /// Checks for an Empty string /// /// string to check /// Boolean Value Indicating WHether String IS Empty Private static bool ipTystring (String Str) {if (str! = null) {Return (0 == str.length);} return true;
/// ///convert oledbtype to sqldbtype /// /// The OLEDBTYPE TO Convert /// The typeName to convert for items such as Money and SmallMoney which both map to OleDbType.Currency /// The converted SqlDbType private static SqlDbType GetSqlDbType (short paramType, string typeName) {SqlDbType cmdType; OLEDBTYPE OLEDBTYPE; cmdtype = sqldbtype.variant; OLEDBTYPE = (OLEDBTYPE) (PARAMTYPE);
switch (oleDbType) {case OleDbType.SmallInt: cmdType = SqlDbType.SmallInt; break; case OleDbType.Integer: cmdType = SqlDbType.Int; break; case OleDbType.Single: cmdType = SqlDbType.Real; break; case OleDbType.Double: cmdType = SqlDbType.Float; break; case OleDbType.Currency: cmdType = (typeName == "money") SqlDbType.Money: SqlDbType.SmallMoney; break; case OleDbType.Date:?? cmdType = (typeName == "datetime") SqlDbType .DateTime: SqlDbType.SmallDateTime; break; case OleDbType.BSTR: cmdType = (typeName == "nchar") SqlDbType.NChar: SqlDbType.NVarChar; break; case OleDbType.Boolean:? cmdType = SqlDbType.Bit; break; case OleDbType .Variant: cmdType = SqlDbType.Variant; break; case OleDbType.Decimal: cmdType = SqlDbType.Decimal; break; case OleDbType.TinyInt: cmdType = SqlDbType.TinyInt; break; case OleDbType.UnsignedTinyInt: cmdType = SqlDbType.TinyInt; break; case OleDbType.UnsignedSmallInt: cmdType = SqlDbType.SmallInt; break; case OleDbType.BigInt: cmdType = SqlDbType.BigInt; break; case OleDbType.Filetime: cmdType = (typeName == "datetime") SqlDbType.DateTime: SqlDbType?. SmallDateTime; break; case OleDbType.Guid: cmdType = SqlDbType.UniqueIdentifier; break; case OleDbType.Binary: cmdType = (typeName == "binary") SqlDbType.Binary: SqlDbType.VarBinary; break; case OleDbType.Char:? cmdType = (TypenAme == "char")? Sqldbtype.char: sqldbtype.varchar; Break;
? Case OleDbType.WChar: cmdType = (typeName == "nchar") SqlDbType.NChar: SqlDbType.NVarChar; break; case OleDbType.Numeric: cmdType = SqlDbType.Decimal; break; case OleDbType.DBDate: cmdType = (typeName == ? "datetime") SqlDbType.DateTime: SqlDbType.SmallDateTime; break; case OleDbType.DBTime: cmdType = (typeName == "datetime") SqlDbType.DateTime: SqlDbType.SmallDateTime; break; case OleDbType.DBTimeStamp:? cmdType = (typeName ? == "datetime") SqlDbType.DateTime: SqlDbType.SmallDateTime; break; case OleDbType.VarChar: cmdType = (typeName == "char") SqlDbType.Char: SqlDbType.VarChar; break; case OleDbType.LongVarChar:? cmdType = SqlDbType.Text; break; case OleDbType.VarWChar: cmdType = (typeName == "nchar") SqlDbType.NChar: SqlDbType.NVarChar; break; case OleDbType.LongVarWChar:? cmdType = SqlDbType.NText; break; case OleDbType.VarBinary: CMDTYPE = (TypenAme == "binary")? SQL DBTYPE.BINARY: SQLDBTYPE.VARBINARY; BREAK; CASE OLEDBTYPE.LONGVARBINARY: CMDTYPE = SqldbType.Image; Break;} Return CMDTYPE
/// /// Converts the OLEDB parameter direction /// /// The integer parameter direction /// a parameterdirection private static ParameterDirection GetParameterDirection (short oledbDirection) {ParameterDirection pd; switch (oledbDirection) {case 1: pd = ParameterDirection.Input; break; case 2: // or comment out the entire case of pd = ParameterDirection.Output 2; / / Is the problem of here goto default; // I added this sentence // Break; // I commented this sentence case 4: Pd = parameterDirection.RtrurnValue; break; default: pd = parameterdirection.inputoutput; break; } Return Pd;}} #ENDREGION
/// ///// public seales sqlhelper {///// public seales sqlhelper {//// // / Calls the SqlCommandBuilder.DeriveParameters, doing any setup and cleanup necessary /// /// The SqlCommand referencing the stored procedure from which the parameter information is to be derived. The derived parameters Are Added to the parameters collection of the sqlcommand. public static void deriveparameters (sqlcommand cmd) {new sqlserver (). deriveparameters (cmd);
#Region Private Constructionor
// Since this class provides Only Static Methods, make the default constructor private to prevent // instances from being created with "new sqlhelper ()" private sqlhelper () {}
#ENDREGION PRIVATE CONSTRUCTOR
#Region getParameter
/// /// Get a SQLParameter for use in a summand /// /// The name of the parameter to create //////////////////////////////////////////////////////> The value of the specified parameter /// A SqlParameter object public static SqlParameter GetParameter (string name, object value) {return (SqlParameter) (new SqlServer () .GetParameter (Name, Value);} /// /// Get a SQLParameter for use in a sql command /// /// the name Of the parameter to create /// The system.data.dbtype of the parameter /// The size of the parameter < / param> /// The System.Data.ParameterDirection of the parameter /// A SqlParameter object public static SqlParameter GetParameter (string name, dbType dbType , int size, parameterdirection direction) {return (new sqlserver (). getParameter (Name, DBTYPE, S Ize, direction));
/// /// Get a SQLParameter for use in a summand /// /// The name of the parameter to create //////////////////////////////////////////////////////> The system.data.dbtype of the parameter /// The size of the parameter /// The system.data.datarowversion of the parameter /// a sqlparameter Object public static SqlParameter GetParameter (string name, dbType dbType, int size, string sourceColumn, DataRowVersion sourceVersion) {return (SqlParameter) new SqlServer () GetParameter (name, dbType, size, sourceColumn, sourceVersion);.} #endregion
#Region ExecutenonQuery
/// /// Execute a Sqlcommand (That Returns no results) against the database specified in ///////////// /// EG: //// Int result = executenonQuery (command); /// /// The sqlcommand to execute /// An int reresting the number of rows affected by the Number of rows affected by the number Command public static int executenonquery (SQLCOMMAND Command) {Return New SQLServer (). ExecutenonQuery (Command);
/// /// Execute a Sqlcommand (That Returns no results) against the database specified in //////// /// EG : /// int result = ExecuteNonQuery (connString, CommandType.StoredProcedure, "PublishOrders"); /// /// A valid connection string for a SqlConnection / // The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command / // An int representing the number of rows affected by the command public static int ExecuteNonQuery (string connectionString, commandType commandType, string commandText) {return new SqlServer () ExecuteNonQuery (connectionString, commandType, commandText).; }
/// /// Execute a Sqlcommand (That Returns no results) against the database specified in the connection String ////// /// /// EG : /// int result = executenonQuery (connString, CommandType.StoredProcedure, "PublishORDERS", New SQLParameter ("@PRODID", 24)); /// /// a valid connection string for a SqlConnection /// The commandType (stored procedure, text, etc.) /// The stored procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// An int representing the number of rows affected by the command public static int ExecuteNonQuery (string connectionString, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer (). ExecuteNonQuery (connectionString, co MmandType, CommandText, CommandParameters;
/// /// 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. /// /// /// This method provides no access to output parameters Or the stored propedure's return value parameter. /// /// Eg: /// int result = ExecutenonQuery (Connstring, "PublishRDERS", 24, 36); /// /// a valid connection string for a sqlconnection /// the name of the stored prracture /// an Array Of Objects To be assigned as the input value of the stored procedure /// An int reresting the number of rows affected by the comman Publi C static int ExecutenonQuery (String SPNAME) {Return New SQLServer (). ExecutenonQuery (Connectionstring, Spname, ParameterValues);}
/// /// Execute a Sqlcommand (That Returns no results) against the provided sqlconnection. /// //// /// EG: /// int result = ExecuteNonQuery (conn, commandtype.storedprocedure); /// /// a valid sqlconnection /// THE CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// An Int Reprresenting The Number of rows affected by the command public static int ExecuteNonQuery (SqlConnection connection, commandType commandType, string commandText) {return new SqlServer () ExecuteNonQuery (connection, commandType, commandText);.}
/// /// Execute a Sqlcommand (That Returns no results) against the specified SqlConnection //// /// /// EG: // / Int result = executenonQuery (conn, commandtype.storedProcedure), "PublishORDERS", New SQLParameter ("@ productID", 24)); /// /// a Valid SqlConnection < / param> /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command < / param> /// An array of SqlParamters used to execute the command /// An int representing the number of rows affected by the command public static int ExecuteNonQuery (SqlConnection connection, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer () ExecuteNonQuery (connection, commandType, commandText, commandParameters);.}
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. ////// Eg: /// Int Result = ExecutenonQuery (CONN, "PublishORDERS", 24, 36); /// /// a Valid SqlConnection /// The name of the stored procedure /// an array of objects to be assigned as the input value THE Stored Procedure /// An int reresting the number of rows affected by the command public static int ExecutenonQuery (SqlConnection Connection, String spname, params object [] parametervalues) {Return New SQLServer (). ExecutenonQuery (connection, spname, parametervalues);}
/// //////// //// /// Eg: /// int result> /// EG: /// int result = ExecutenonQuery (Trans, CommandType.StoredProcedure); /// /// a valid sqltransaction /// THE CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// An Int Reprresenting The Number of rows affected by the command public static int ExecuteNonQuery (SqlTransaction transaction, commandType commandType, string commandText) {return new SqlServer () ExecuteNonQuery (transaction, commandType, commandText);.}
/// /// Execute a Sqlcommand (That Returns no results) against the specified sqltransaction ///// /// /// EG: // / Int result = executenonQuery (Trans, CommandType.StoredProcedure, "Getorders", New Sqlparameter ("@PRODID", 24)); /// /// a Valid Sqltransaction < / param> /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command < / param> /// An array of SqlParamters used to execute the command /// An int representing the number of rows affected by the command public static int ExecuteNonQuery (SqlTransaction transaction, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer () ExecuteNonQuery (transaction, commandType, commandText, commandParameters);.}
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. ////// EG: /// Int Result = ExecutenonQuery (conn, trans, "publishORDERS", 24, 36); /// /// A valid sqltransaction /// The name of the stored procedure /// an array of objects to be assigned as the input Values of the stored procedure /// An int reresting the number of rows affected by the command public static int executenonquery (SqlTransaction T Ransaction, string spname, params object [] parameterValues) {Return New SQLServer (). ExecutenonQuery (Transaction, SpName, Parameter);} #ENDREGON EXECUTENQUERY
#REGON EXECUTEDASET
/// /// Execute a Sqlcommand (That Returns a resultset) against the database specified in /// /// EG: // / DataSet DS = Executedata (Command); /// public static DataSet ExecuteDataset (SqlCommand command) {return new SqlServer () ExecuteDataset (command);.} /// /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database Specified in ///// /// /// EG: /// DataSet DS = EXECUTEDASET (Connstring, CommandType.StoredProcedure, "GetRDERS"); /// < / remarks> /// a valid connection string for a SqlConnection /// The CommandType (Stored Procedure, Text, ETC.) /// The stored procedure name or T-SQL command /// A dataset containing the resultset generated by the command public static DataSet ExecuteDataset (string connectionString, CommandType commandType, string commandText) { Return New Sql Server (). ExecuteTed, CommandType, CommandText);
/// /// Execute a Sqlcommand (That Returns a resultset) Against the Database specified in the connection string /// using the provided parameters. /// /// /// Eg: /// DataSet DS = EXECUted, CommandType.StoredProcedure, "GetRDERS", New SqlParameter ("@PRODID", 24)); /// /// A Valid Connection String for a SqlConnection /// The CommandType (Stored Procedure, Text, etc.) /// The store procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// A dataset containing the resultset generated by the command public static DataSet ExecuteDataset (string connectionString, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer (). ExecuteDataset (connectionString, commandT Ype, CommandText, CommandParameters;
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// Eg: /// DataSet DS = ExecutetAset (Connstring, "GetRDERS", 24, 36); /// /// A valid connection string for a SqlConnection /// The name of the stored procedure /// An array of objects To be assigned as the input value of the storeing the resultset generated by the commlic public static Public Static By THE COMMAND
/// /// ///// ////// DataSet DS Against the provided sqlconnection. /// /// /// EG: /// DataSet DS = ExecuteDataSet (CONN, CommandType.StoredProcedure, "getorders"); /// /// a valid sqlconnection /// THE CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// a DataSet Containing The Resultset generated by the command public static DataSet ExecuteDataset (SqlConnection connection, commandType commandType, string commandText) {return new SqlServer () ExecuteDataset (connection, commandType, commandText);.}
/// /// Execute a Sqlcommand (That Returns a resultset) against the specified sqlconnection //// /// /// EG: // / DataSet DS = EXECUTEDATASET (CONN, CommandType.StoredProcedure, "GetRDERS", New Sqlparameter ("@PRODID", 24)); /// /// a valid sqlconnection < / param> /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command < / param> /// An array of SqlParamters used to execute the command /// A dataset containing the resultset generated by the command public static DataSet ExecuteDataset (SqlConnection connection, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer () ExecuteDataset (connection, commandType, commandText, commandParameters);.}
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// EG: /// DataSet DS = ExecutetAset (CONN, "GetRDERS", 24, 36); /// /// a Valid SqlConnection /// The name of the stored procedure /// an array of objects to be assigned as the input value The Stored Procedure /// a dataset containing the resultset generated by the command public static dataset executetaset (SQLConnection Connection, String SpName, Params Object [] ParameterValues) {Return New SQLServer (). ExecuteTASet (Connection, SPName, ParameterValues);
/// ///////// ////// DataSet DS Against the project = Executedataset (Trans, CommandType.StoredProcedure, "GetRDERS"); /// /// a valid sqltransaction /// THE CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// a DataSet Containing The Resultset generated by the command public static DataSet ExecuteDataset (SqlTransaction transaction, commandType commandType, string commandText) {return new SqlServer () ExecuteDataset (transaction, commandType, commandText);.}
/// /// Execute A SqlCommand (That Returns A Resultset) against the specified sqltransaction ///// /// //// EG: // / DataSet DS = ExecutedataSet (Trans, CommandType.StoredProcedure, "GetRDERS", New Sqlparameter ("@PRODID", 24)); /// /// a Valid Sqltaction < / param> /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command < / param> /// An array of SqlParamters used to execute the command /// A dataset containing the resultset generated by the command public static DataSet ExecuteDataset (SqlTransaction transaction, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer () ExecuteDataset (transaction, commandType, commandText, commandParameters);.}
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// EG: /// DataSet DS = Executedataset (Trans, "GetRDERS", 24, 36); /// /// a Valid sqltransaction /// The name of the stored urgedure /// an array of objects to be assigned as the input value The Stored Procedure /// a dataset containing the resultset generated by the command public static dataset executetaset (Sqltransaction Transaction) String spname, params object [] parametervalues) {return new SQLServer (). Executedataset (Transaction, SpName, Parameter);} #ENDREGON EXECUTEDASET
#Region ExecuteReader
/// /// Execute a Sqlcommand (That Returns a resultset) against the database specified in /// /// EG: // / SqlDataReader dr = ExecuteReader (command); /// /// The SqlCommand to execute /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (SqlCommand command) {return new SqlServer () ExecuteReader (command) as SqlDataReader;.} /// /// Execute a SqlCommand (that returns a resultset and takes no parameters) against The Database Specified In //// /// /// EG: /// SqldataReader DR = ExecuteReader (ConnString, CommandType.StoredProcedure); // / /// a valid connection string for a sqlConnection /// The CommandType (Stored Procedure, Text, ETC. /// The Stored Procedure Name OR T-SQL Command /// a sqldatareader containing the resultset generated by the command public static SqlDataReader ExecuteReader (string connectionString, commandType commandType, string commandText) {return new SqlServer () ExecuteReader (connectionString, commandType, commandText) as SqlDataReader;.}
/// /// Execute a Sqlcommand (That Returns a resultset) Against the Database specified in the connection string /// using the provided parameters. /// /// /// Eg: /// SqldataReader Dr = ExecuteReader (Connstring, CommandType.StoredProcedure), "GetRDERS", New SqlParameter ("@PRODID", 24)); /// /// A Valid Connection String for a SqlConnection /// The CommandType (Stored Procedure, Text, etc.) /// The store procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (string connectionString, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer (). ExecuteReader (connectionS TRING, CommandType, CommandText, CommandParameters) As SqlDataRead;}
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// Eg: /// SqlDataReader DR = ExecuteReader (Connstring, "GetRDERS", 24, 36); /// /// A valid connection string for a SqlConnection /// The name of the stored procedure /// An array of objects To be assigned as the input value of the stored procedure /// a sqldataareader containing the resultset generated by the command pu blic static SqlDataReader ExecuteReader (string connectionString, string spName, params object [] parameterValues) {return new SqlServer () ExecuteReader (connectionString, spName, parameterValues) as SqlDataReader;.}
/// /// Execute a SqlCommand (That Returns A ResultSet and takes no parameters) against the provided sqlconnection. /// /// /// EG: /// SqldataReader DR = ExecuteReader (CONN, CommandType.StoredProcedure, "getorders"); /// /// a valid sqlconnection /// The CommandType (stored procedure, text, etc.) /// The stored procedure name or T-SQL command /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (SqlConnection connection, commandType commandType, string commandText) {return new SqlServer () ExecuteReader (connection, commandType, commandText) as SqlDataReader;.}
/// /// Execute a Sqlcommand (That Returns a resultset) against the specified sqlconnection //// /// /// EG: // / SqlDataReader Dr = ExecuteReader (Conn, CommandType.StoredProcedure, "GetRDERS", New Sqlparameter ("@PRODID", 24)); /// /// a valid sqlconnection < / param> /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command < / param> /// An array of SqlParamters used to execute the command /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (SqlConnection connection, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer (). ExecuteReader (connection, commandType, commandText, commandParameters) as SqlDat AReader;
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// EG: /// SqlDataReader DR = ExecuteReader (CONN, "GetRDERS", 24, 36); /// /// a Valid SqlConnection /// The name of the stored procedure /// an array of objects to be assigned as the input value The Stored Procedure /// a SqldataReader Containing The ResultSet generated by the command public static SqlDataReader ExecuteReader (SqlConnection Co Nnection, string spname, params object [] parametervalues) {Return New SQLServer (). ExecuteReader (Connection, SPName, ParameterValues) as sqldatarader;
/// /// Execute A Sqlcommand (That Returns A ResultSet and takes no parameters) against the provided sqltransaction. /// /// /// EG: /// SqldataReader DR = ExecuteReader (Trans, CommandType.StoredProcedure); /// /// a valid sqltransaction /// The CommandType (stored procedure, text, etc.) /// The stored procedure name or T-SQL command /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (SqlTransaction transaction, commandType commandType, string commandText) {return new SqlServer () ExecuteReader (transaction, commandType, commandText) as SqlDataReader;.}
/// /// Execute A SqlCommand (That Returns A Resultset) against the specified sqltransaction ///// /// //// EG: // / SqlDataReader Dr = ExecuteReader (Trans, CommandType.StoredProcedure, "Getorders", New Sqlparameter ("@PRODID", 24)); /// /// a valid sqltransaction < / param> /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command < / param> /// An array of SqlParamters used to execute the command /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReader (SqlTransaction transaction, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer (). ExecuteReader (transaction, commandType, commandText, commandParameters) as Sqldatarader;}
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// Eg: /// SqldataReader Dr = ExecuteReader (Trans, "GetRDERS", 24, 36); /// /// A Valid sqltransaction /// The name of the stored urgedure /// an array of objects to be assigned as the input value The Stored Procedure /// a sqldatareader containing the resultset generated by the command public static SqlDataReader ExecuteReader (Sqltransactio) N transaction, string spname, params object [] parametervalues) {return new sqlserver (). ExecuteReader (Transaction, SpName, ParameterValues) As SqlDataReader;} #ENDREGON EXECUTEREADER
#Region ExecuteScalar
/// /// Execute A Sqlcommand (That Returns a 1x1 Resultset) Against the Database Specified In ////// /// /// EG: / // int ordercount = (int) ExecuteScalar (Command); /// /// The sqlcommand to execute /// an Object Containing The Value in the 1x1 resultset generated by the command public static object ExecuteScalar (SqlCommand command) {// Pass through the call providing null for the set of SqlParameters return new SqlServer () ExecuteScalar (command);.} /// < Summary> /// Execute A Sqlcommand (That Returns a 1x1 ResultSet and takes no parameters) against the database specified in /// /// /// EG: / // int orderCount = (int) ExecuteScalar (connString, CommandType.StoredProcedure, "GetOrderCount"); /// /// A valid connection string for a SqlConnection /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL COMMAND /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar (string connectionString, commandType commandType, string commandText) {// Pass through the call providing null for the set of SQLParameters Return New SQLServer (). ExecuteScalar (Connectionstring, CommandType, CommandText);
/// /// Execute A Sqlcommand (That Returns a 1x1 Resultset) Against the Database Specified in The Commoded Parameters. /// /// // / eg: /// int ordercount = (int) ExecuteScalar (connString, CommandType.StoredProcedure, "GetOrdercount", New Sqlparameter ("@PRODID", 24)); /// /// a Valid connection string for a sqlconnection /// The CommandType (stored procedure, text, etc.) /// The stored procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar (string connectionString, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer (). ExecuteSca LAR (Connectionstring, CommandType, CommandText, CommandParameters);
/// /// 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 /// stiled procedure (The First Time Each Stored Procedure (The First Time Each Stored Procedure), And Assign The VALUES BASED ON Parameter ORDER. /// /// // this method provides no access to output Parameters or the stiled procedure's return value parameter. ////// Eg: /// int ordercount = (int) ExecuteScalar (Connstring, "GetOrdercount", 24, 36); /// /// < Param name = "connectionstring"> a valid connection string for a sqlconnection /// The name of the stored procedure /// An Array Objects To Be Assigned As The Input Values of The Stored Procedure /// An Object Containing The Value in The 1x1 Resultset Generated by The 1x1 Resultset command public static object ExecuteScalar (string connectionString, string spName, params object [] parameterValues) {return new SqlServer () ExecuteScalar (connectionString, spName, parameterValues);.}
/// /// Execute A Sqlcommand (That Returns a 1x1 ResultSet and takes no parameters) against the provided sqlconnection. /// //// /// EG: /// int ORDERCOUNT = (int) ExecuteScalar (conn, commandtype.storedprocedure); /// /// a valid sqlConnection /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar (SqlConnection connection, commandType commandType, string commandText) {return new SqlServer () ExecuteScalar (connection, commandType, commandText);.}
/// /// /////// ///// /// /// EG: / // int ordercount = (int) ExecuteScalar (conn, commandtype.storedprocedure, "getordercount", new sqlparameter ("@ productID", 24)); /// /// A Valid SqlConnection /// The CommandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name OR t -SQL command /// An array of SqlParamters used to execute the command /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar (SqlConnection connection, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer (). ExecuteScalar (connection, commandType, commandText, commandParame TERS);
/// /// 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. /// /// /// This method provides no access to output parameters or the stored Procedure's return value parameter. /// /// Eg: /// int ordercount = (int) ExecuteScalar (conn, "getordercount", 24, 36); /// /// The name of the stored procedure /// an array of objects to be assigned as The Input Values of the Stored Procedure /// An Object Containing The value in the 1x1 resultset generated by The Command public static Object ExecuteScalar (S QlConnection Connection, String SpName, Params Object [] ParameterValues) {Return New SQLServer (). ExecuteScalar (Connection, SPName, Parameter);}
/// /// Execute A Sqlcommand (That Returns a 1x1 ResultSet and takes no parameters) against the provided sqltransaction. /// /// /// EG: /// int ORDERCOUNT = (int) ExecuteScalar (Trans, CommandType.storedProcedure, "GetOrdercount"); /// /// a valid sqltransaction /// The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command //// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar (SqlTransaction transaction, commandType commandType, string commandText) {return new SqlServer () ExecuteScalar (transaction, commandType, commandText);.}
/// /// Execute A Sqlcommand (That Returns a 1x1 Resultset) against the specified sqltransaction ///// /// /// EG: / // int ordercount = (int) ExecuteScalar (Trans, CommandType.StoredProcedure, "GetOrdercount", New Sqlparameter ("@PRODID", 24)); /// /// A Valid SqlTransaction /// The CommandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name OR t -SQL command /// An array of SqlParamters used to execute the command /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalar (SqlTransaction transaction, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer (). ExecuteScalar (transaction, commandType, commandText, command Parameters);
/// /// 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. /// /// /// This method provides no access to output parameters or the stored Procedure's return value parameter. /// /// Eg: /// int = (int) ExecuteScalar (Trans, "GetOrdercount", 24, 36); /// /// a valid sqltransaction /// the name of the stored procedure /// an array of objects to be assigned AS THE INPUES OF THE Stored Procedure /// An Object Containing The value in the 1x1 resultset generated by The Command public static object executescala R (SqlTransaction Transaction, String SpName, Params Object [] ParameterValues) {Return New SQLServer (). ExecuteScalar (Transaction, SPName, ParameterValues);} #ENDREGON EXECUTESCALARARARARARARARARAR
#Region ExecutexmlReader
/// /// Execute A SqlCommand (That Returns a resultset) against the provided sqlconnection. /// /// /// EG: /// Xmlreader R = ExecuteExmlreader (SqlCommand) Command; /// /// The sqlcommand to execute /// an Xmlreader Containing The ResultSet generated by the commit public static XmlReader ExecuteXmlReader (SqlCommand command) {return new SqlServer () ExecuteXmlReader (command);.} /// /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection /// <. / summary> /// /// EG: /// Xmlreader R = ExecuteExmlReader (conn, commandtype.storedProcedure); /// /// A Valid SqlConnection /// The commandType (Stored Procedure, Text, etc.) /// The stored procedure name OR T-SQL Command Using "for XML au TO " /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReader (SqlConnection connection, CommandType commandType, string commandText) {return new SqlServer (). ExecuteXmlReader (connection, CommandType, CommandText);
/// /// Execute a Sqlcommand (That Returns a resultset) against the specified sqlconnection //// /// /// EG: // / Xmlreader r = executexmlreader (conn, commandtype.storedprocedure), "getorders", new sqlparameter ("@ productID", 24)); /// /// a Valid SqlConnection < / param> /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or t-sql command "FOR XML AUTO" /// An array of SqlParamters used to execute the command /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReader (SqlConnection connection, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer (). ExecuteXmlReader (connection, commandType, commandText, commandPar ameters;
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. /// /// Eg: /// Xmlreader R = Executexmlreader (conn, "getorders", 24, 36); /// /// a Valid sqlconnection /// the name of the stored procedure Using "for xml auto" /// an array of objects to becomee Assigned as the input value of the storedure /// An Xmlreader Containing The ResultSet Generated by The Command Public Static XmlReader Executexmlreader (S QlConnection Connection, String SpName, Params Object [] ParameterValues) {Return New SQL Server (). ExecutexmlReader (Connection, SPName, Parameter);}
/// ///////// ///// xik> /// EG: /// XmlReader r = Executexmlreader (Trans, CommandType.StoredProcedure); /// /// a valid sqltransaction /// THE CommandType (Stored Procedure, Text, etc.) /// The storedure name or t-sql command us "for xml auto" /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReader (SqlTransaction transaction, commandType commandType, string commandText) {return new SqlServer () ExecuteXmlReader (transaction, commandType, commandText);.}
/// /// Execute A SqlCommand (That Returns A Resultset) against the specified sqltransaction ///// /// //// EG: // / XmlReader R = Executexmlreader (Trans, CommandType.StoredProcedure, "GetRDERS", New Sqlparameter ("@PRODID", 24)); /// /// a valid sqltransaction < / param> /// The CommandType (Stored Procedure, Text, etc.) /// The stored procedure name or t-sql commist using "FOR XML AUTO" /// An array of SqlParamters used to execute the command /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReader (SqlTransaction transaction, commandType commandType, string commandText, params SqlParameter [] commandParameters) {return new SqlServer (). ExecuteXmlReader (transaction, commandType, commandText, comm Andparameters;
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. ////// Eg: /// Xmlreader R = ExecuteExmlreader (Trans, "GetRDERS", 24, 36); /// /// a Valid sqltransaction /// The name of the stored urgedure /// an array of objects to be assigned as the input value The Stored Procedure /// a dataset containing the resultset generated by the command public static xmlreader Executexmlreader (SqlTransaction TransA Ction, string spname, params object [] parametervalues) {return new sqlserver (). Executexmlreader (Transaction, SpName, Parameter);} #ENDREGON EXECUTEXMLREADER
#Region FillDataSet
/// /// Execute a Sqlcommand (That Returns a resultset) against the database specified in /// /// EG: // / FillDataSet (Connstring, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"Orders"}; /// /// The sqlcommand to Execute /// a Dataset Wich Will Contain the resultset generated by the command /// this array will be used to create Table mapings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) public static void FillDataset (SqlCommand command, DataSet dataSet, string [] tableNames) {new SqlServer (). FillDataset (command, dataSet Tablenames;
/// /// Execute A Sqlcommand (That Returns A ResultSet and takes no parameters) against the database specified in ///// /// /// Eg: /// FillDataSet (Connstring, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"Orders"}; /// /// a Valid connection string for a SqlConnection /// The commandType (stored procedure, text, etc.) /// The stored procedure name OR T-SQL Command /// a Dataset Wich Will Contain The ResultSet generated by the command /// 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) public static void FillDataset (string connectionString, commandType commandType, string commandText, DataSet dataSet, string [] TABLEN Ames) {new SQLServer (). FillDataSt (Connectionstring, CommandType, CommandText, Dataset, Tablenames);
/// /// Execute a Sqlcommand (That Returns a resultset) Against the Database specified in the connection string /// using the provided parameters. /// /// /// Eg: /// FillDataSet (Connstring, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"ORDERS"}, New SqlParameter ("@PRODID", 24)); /// // / a valid connection string for a sqlconnection /// The CommandType (Stored Procedure, Text, ETC.) /// < param name = "commandText"> The stored procedure name or T-SQL command /// An array of SqlParamters used to execute the command /// A dataset wich will contain the resultset generated by the command /// This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name /// < / Param> public static void FillDataset (string connectionString, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames, params SqlParameter [] commandParameters) {new SqlServer (). FillDataset (connectionString, commandType, commandText, dataSet, tableNames, commandParameters }
/// /// 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. /// /// /// This method provides no access to output parameters or the stored urgedure's return value parameter. /// /// EG: /// FillDataSet (Connstring, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"Orders"}, 24); // < / remarks> /// a valid connection string for a sqlconnection /// The name of the stored procedure /// < Param name = "dataset"> a Dataset Wich Will Contain the resultset generated by the command /// this array will be used to create Table Mappings A llowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// /// An array of objects to be assigned as the input values of the stored procedure public static void FillDataset (string connectionString, string spName, DataSet dataSet, string [] tableNames, params object [] parameterValues) {new SqlServer (). FillDataset (connectionString, spName, dataSet, tableNames, parameterValues }
/// /// Execute a SqlCommand (That Returns A ResultSet and takes no parameters) against the provided sqlconnection. /// //// /// EG: /// FillDataSet ( CONN, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"Orders"}; /// /// a valid sqlconnection // / The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command ///// / A dataset wich will contain the resultset generated by the command /// 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) public static void FillDataset (SqlConnection connection, commandType commandType, string commandText, DataSet dataSet, string [] tableNames) {new SqlSe RVER (). FillDataSet (Connection, CommandType, CommandText, Dataset, Tablenames);
/// /// Execute a Sqlcommand (That Returns a resultset) against the specified sqlconnection //// /// /// EG: // / FillDataSet (CONN, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"ORDERS"}, New SqlParameter ("@ productID", 24)); /// /// a Valid SqlConnection /// The CommandType (Stored Procedure, Text, etc.) /// The Stored Procedure Name OR T-SQL Command /// a Dataset Wich Will Contain The ResultSet generated by the command /// 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) /// /// An array of SqlParamters Used to Execute The Command Public Static Void FillDataSet (SqlConnection Co nnection, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames, params SqlParameter [] commandParameters) {new SqlServer () FillDataset (connection, commandType, commandText, dataSet, tableNames, commandParameters);.}
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. ////// Eg: /// FillDataSet (CONN, "GetRDERS", DS, New String [] {"Orders"}, 24, 36); /// //// a valid sqlconnection /// the name of the stored procedure // a Dataset Wich will contain the resultset generated by the command /// 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) /// /// An array of objects to be assigned as the input values of the stored procedure public static void FillDataset (SqlConnection connection, string SPName, DataSet DataSet, String [] TableNames, params object [] parametervalues) {new sqlserver (). FillDataSet (Connection, SPName, Dataset, Tablenames, parametervalues);}
/// /// Execute A Sqlcommand (That Returns A ResultSet and takes no parameters) against the provided sqltransaction. /// /// /// EG: /// FillDataSet ( TRANS, CommandType.StoredProcedure, "GetRDERDERS", DS, New String [] {"Orders"}; /// /// a valid sqltransaction // / The CommandType (Stored Procedure, Text, ETC.) /// The Stored Procedure Name OR T-SQL Command ///// / A dataset wich will contain the resultset generated by the command /// 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) public static void FillDataset (SqlTransaction transaction, commandType commandType, string commandText, DataSet dataSet, string [] tableNames) {new Sql Server (). FillDataSet (Transaction, CommandType, CommandText, Dataset, Tablenames);
/// /// Execute A SqlCommand (That Returns A Resultset) against the specified sqltransaction ///// /// //// EG: // / FillDataSet (Trans, CommandType.StoredProcedure, "GetRDERS", DS, New String [] {"ORDERS"}, New SqlParameter ("@PRODID", 24)); /// /// a valid sqltransaction /// The CommandType (Stored Procedure, Text, ETC.) /// THE Stored Procedure Name OR T-SQL Command /// a Dataset Wich Will Contain The ResultSet generated by the command /// 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) /// /// An array of Sqlparamters use to execute the command public static void fixdataset (Sqltransactio n transaction, CommandType commandType, string commandText, DataSet dataSet, string [] tableNames, params SqlParameter [] commandParameters) {new SqlServer () FillDataset (transaction, commandType, commandText, dataSet, tableNames, commandParameters);.}
/// /// 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. /// /// /// This method provides no access to output parameters or the stored procedure's Return Value Parameter. ////// Eg: /// FillDataSet (Trans, "GetRDERS", DS, New String [] {"Orders"}, 24, 36); /// //// a valid sqltransaction /// the name of the stored procedure /// a dataset wich will contain the resultset generated by the command /// 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) /// /// An array of objects to be assigned as the input values of the stored procedure public static void FillDataset (SqlTransaction transaction, String SpName, DataSet DataSet, String [] Tablenames, params object [] parametervalues) {new sqlserver (). FillDataset (Transaction, SPName, Dataset, Tablenames, ParameterValues);} #ENDREGION
#REGON UpdatedataSet
. /// /// Executes the respective command for each inserted, updated, or deleted row in the DataSet /// /// /// eg: /// UpdateDataset (conn , insertCommand, deleteCommand, updateCommand, dataSet, "Order"); /// /// A valid transact-SQL statement or stored procedure to insert new records into the data source < / param> /// a valid transact-sql statement or storedure to delete records from the data source /// The dataset used to update the data source /// The DataTable used to update the data source. public static void UpdateDataset (SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName) {new SqlServer (). UpdateDatas ET (InsertCommand, DeleteCommand, UpdateCommand, DataSet, Tablename);
/// /// Executes the System.Data.SqlClient.SqlCommand for each inserted, updated, or deleted row in the DataSet also implementing RowUpdating and RowUpdated Event Handlers /// /// /// eg: /// SqlRowUpdatingEventHandler rowUpdating = new SqlRowUpdatingEventHandler (OnRowUpdating); /// SqlRowUpdatedEventHandler rowUpdated = new SqlRowUpdatedEventHandler (OnRowUpdated); /// adoHelper.UpdateDataSet (sqlInsertCommand, sqlDeleteCommand, sqlUpdateCommand, dataSet, "Order", rowUpdating, Rowupdated); /// /// a valid transact-sql statement or storedure to insert new records inTo the data source /// A valid transact-SQL statement or stored procedure to delete records from the data source /// A valid transact-SQL statement or stored procedure used to update records in the data Source /// The Datas et used to update the data source /// The DataTable used to update the data source. /// The AdoHelper.RowUpdatingEventHandler OR NULL /// The adohelper.rowupdateDeventhandler or null
public static void UpdateDataset (IDbCommand insertCommand, IDbCommand deleteCommand, IDbCommand updateCommand, DataSet dataSet, string tableName, AdoHelper.RowUpdatingHandler rowUpdating, AdoHelper.RowUpdatedHandler rowUpdated) {new SqlServer (). UpdateDataset (insertCommand, deleteCommand, updateCommand, dataSet, tableName, rowUpdating, Rowupdated;} #ENDREGION
#Region CreateCommand
/// /// sIMPLIFY THE CREATION OF A SQL Command Object By Allowing /// A Stored Procedure and Optional Parameters to Be provides /// /// /// EG: / // SqlCommand command = CreateCommand (connenctionString, "AddCustomer", "CustomerID", "CustomerName"); /// /// A valid connection string for a SqlConnection /// The name of the stored procedure /// An array of string to be assigned as the source columns of the stored procedure parameters < / param> /// A valid SqlCommand object public static SqlCommand CreateCommand (string connectionString, string spName, params string [] sourceColumns) {return new SqlServer (). CreateCommand (connectionString, spName, sourceColumns) as Sqlcommand;
/// /// sIMPLIFY THE CREATION OF A SQL Command Object By Allowing /// A Stored Procedure and Optional Parameters to Be provides /// /// /// EG: / // sqlcommand command = CreateCommand (CONN, "AddCustomer", "Customerid", "Customername"); /// /// a valid sqlconnection Object // / The name of the stored procedure /// An array of string to be assigned as the source columns of the stored procedure parameters /// A valid SqlCommand object public static SqlCommand CreateCommand (SqlConnection connection, string spName, params string [] sourceColumns) {return new SqlServer () CreateCommand (connection, spName, sourceColumns) as SqlCommand;.}
/// /// sIMPLIFY THE CREATION OF A SQL Command Object By Allowing /// A Stored Procedure and Optional Parameters to Be provides /// /// /// EG: / // SqlCommand command = CreateCommand (connenctionString, "AddCustomer", "CustomerID", "CustomerName"); /// /// A valid connection string for a SqlConnection /// a Valid SQL STRING to EXECUTE /// The CommandType to Execute (IE StoredProcedure, Text) //// The SqlParameters to pass to the command /// A valid SqlCommand object public static SqlCommand CreateCommand (string connectionString, string commandText, commandType commandType, params SqlParameter [ ] CommandParameters) {Return New SQLServer (). CreateCommand (Connectionstring, CommandText, CommandType, CommandParameters) as sqlcommand;
/// /// sIMPLIFY THE CREATION OF A SQL Command Object By Allowing /// A Stored Procedure and Optional Parameters to Be provides /// /// /// EG: / // sqlcommand command = CreateCommand (CONN, "AddCustomer", "Customerid", "Customername"); /// /// a valid sqlconnection Object // / a valid sql string to execute /// The commandType to Execute (IE StoredProcedure, Text) /// The SqlParameters to pass to the command /// A valid SqlCommand object public static SqlCommand CreateCommand (SqlConnection connection, string commandText, commandType commandType, params SqlParameter [] commandParameters) {Return New Sql Server (). CreateCommand (Connection, CommandText, CommandType, CommandParameters) as sqlcommand;} #ENDREGION
#Region ExecuteNonQuerytypedParams
/// /// 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 Assign The Parameter Values Based On Row Values. /// /// The sqlcommand to execute /// The datarow used to hold the stored procedure's parameter values. /// An int representing the number of rows affected by the command public static int ExecuteNonQueryTypedParams (SqlCommand command, DataRow dataRow) {return new SqlServer () .ExecutenonquerytypedParams (Command, DataRow);
/// /// 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. /// /// a Valid Connection String for a SqlConnection /// The name of the stored procedure /// The DataRow Used To hold the stored procedure's parameter values. /// An int representing the number of rows affected by the command public static int ExecuteNonQueryTypedParams (String connectionString, String spName, DataRow dataRow) {return new SqlServer () .ExecutenonQuerytypedParams (Connectionstring, SPNAME, DATAROW);
/// /// 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 /// stiled procedure (the first time each storedure (the first time each storedure), and assign the values based on row value. /// /// a valid SqlConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An int representing the number of rows affected by the command public static int ExecuteNonQueryTypedParams (SqlConnection connection, String spName, DataRow dataRow) {return new SqlServer (). ExecuteNonQueryTypedParams (connection, SpName, DATAROW);
/// /// 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 /// stiled procedure (The First Time Each Stored Procedure IS Called), And Assign The VALUES BASED On Row Values. /// /// a valid SqlTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An int representing the number of rows affected by the command public static int ExecuteNonQueryTypedParams (SqlTransaction transaction, String spName, DataRow dataRow) {return new SqlServer (). ExecuteNonQueryTypedParams (transaction, SPNAME, DATAROW);} #ENDREGION
#Region ExecuteTaseTtypedParams
/// /// 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 Assign The Parameter Values Based On Row Values. /// /// The sqlcommand to execute /// The datarow used to hold the stored procedure's parameter values. /// A dataset containing the resultset generated by the command public static DataSet ExecuteDatasetTypedParams (SqlCommand command, DataRow dataRow) {return new SqlServer (). ExecuteDatasetTypedParams Command, DataRow;
/// /// 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. /// /// a Valid Connection String for a SqlConnection /// The name of the stored procedure /// The DataRow Used To hold the stored procedure's parameter values. /// A dataset containing the resultset generated by the command public static DataSet ExecuteDatasetTypedParams (string connectionString, String spName, DataRow dataRow) {return new SqlServer () .ExecutedataTypedParams (Connectionstring, SPNAME, DATAROW);
/// /// 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 /// stiled procedure (the first time each storedure (the first time each storedure), and assign the values based on row value. /// /// a valid SqlConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// A dataset containing the resultset generated by the command public static DataSet ExecuteDatasetTypedParams (SqlConnection connection, String spName, DataRow dataRow) {return new SqlServer (). ExecuteDatasetTypedParams (connection, spName, Datarow);
/// /// 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 /// stiled procedure (The First Time Each Stored Procedure IS Called), And Assign The VALUES BASED On Row Values. /// /// a valid SqlTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// A dataset containing the resultset generated by the command public static DataSet ExecuteDatasetTypedParams (SqlTransaction transaction, String spName, DataRow dataRow) {return new SqlServer (). ExecuteDatasetTypedParams (transaction, spName, DataRow);} #ENDREGION
#Region ExecuteReadertypedParams
/// /// 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 Assign The Parameter Values Based on Parameter ORDER. /// /// The sqlcommand TOE EXECUTE /// The datarow used to hold the stored procedure's parameter values. /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReaderTypedParams (SqlCommand command, DataRow dataRow) {return new SqlServer (). ExecuteReaderTypedParams (Command, Datarow) as sqldatarader;}
/// /// 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. /// /// a Valid Connection String for a SqlConnection /// The name of the stored procedure /// The DataRow Used To hold the stored procedure's parameter values. /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReaderTypedParams (String connectionString, String spName, DataRow dataRow) {return new SqlServer () .Executereadertypedparams (Connectionstring, SPNAME, DATAROW) AS SQLDATAR Eader;}
/// /// 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 /// stiled procedure (The First Time Each Stored Procedure (The First Time Each Stored Procedure IS Called), And Assign The VALUES BASED On Parameter ORDER. /// /// a valid SqlConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReaderTypedParams (SqlConnection connection, String spName, DataRow dataRow) {return new SqlServer (). ExecuteReaderTypedParams (connection, spName, Datarow) as sqldatarader;}
/// /// 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 /// stiled procedure (the first time each storedure), and assign the values based on parameter ORDER. /// /// a valid SqlTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// A SqlDataReader containing the resultset generated by the command public static SqlDataReader ExecuteReaderTypedParams (SqlTransaction transaction, String spName, DataRow dataRow) {return new SqlServer (). ExecuteReaderTypedParams (transaction, spName, Datarow) as sqldatarader;} #ENDREGION
#Region ExecuteScalrtypedParams
/// /// 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 Assign The Parameter Values Based on Parameter ORDER. /// /// The sqlcommand to execute /// The DataRow used to hold the stored procedure's parameter values. /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalarTypedParams (SqlCommand command, DataRow dataRow) {return new SQLServer (). ExecuteScalartypedParams (Command, DataRow);
/// /// 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. /// /// a Valid connection string for a sqlconnection /// the name of the stored procedure /// The datarow used to hold the stored procedure's parameter values. /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalarTypedParams (String connectionString, String spName, DataRow dataRow) { Return New Sql Server (). ExecuteScalrtypedParams (Connectionstring, SPNAME, DATAROW);
/// /// 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. /// /// A valid SqlConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values . /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalarTypedParams (SqlConnection connection, String spName, DataRow dataRow) {return new SqlServer (). ExecuteScalarTypedParams Connection, SPNAME (DATAROW);
/// /// 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. /// /// A valid SqlTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values . /// An object containing the value in the 1x1 resultset generated by the command public static object ExecuteScalarTypedParams (SqlTransaction transaction, String spName, DataRow dataRow) {return new SqlServer (). ExecuteScalarTypedParams Transaction, SPName, DataOw;} #ENDREGION
#Region ExecutexmlReadertypedParams
/// /// 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 assign the parameter Values based on parameter ORDER. /// /// The sqlcommand to execute /// The datarow buy to hold the store procedure's parameter values. /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReaderTypedParams (SqlCommand command, DataRow dataRow) {return new SqlServer (). ExecuteXmlReaderTypedParams (command, dataRow }
/// /// 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 /// stiled procedure (The First Time Each Stored Procedure (The First Time Each Stored Procedure IS Called), And Assign The VALUES BASED On Parameter ORDER. /// /// a valid SqlConnection object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReaderTypedParams (SqlConnection connection, String spName, DataRow dataRow) {return new SqlServer (). ExecuteXmlReaderTypedParams (connection, spName, Datarow);
/// /// 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 /// stiled procedure (the first time each storedure), and assign the values based on parameter ORDER. /// /// a valid SqlTransaction object /// The name of the stored procedure /// The dataRow used to hold the stored procedure's parameter values. /// An XmlReader containing the resultset generated by the command public static XmlReader ExecuteXmlReaderTypedParams (SqlTransaction transaction, String spName, DataRow dataRow) {return new SqlServer (). ExecuteXmlReaderTypedParams (transaction, spName, DataRow);} #endregion}
/// /// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the /// ability to discover parameters for stored procedures at run-time. /// public sealed class SqlHelperParameterCache { #Region Private Constructionor
// Since this class provides only static methods, make the default constructor private to prevent // instances from being created with "new SqlHelperParameterCache ()" private SqlHelperParameterCache () {}
#ndregion constructor
#Region Caching Functions
/// /// add parameter array to the cache /// /// a valid connection string for a sqlconnection /// The stored procedure name or T-SQL command /// An array of SqlParamters to be cached public static void CacheParameterSet (string connectionString, string commandText , params SqlParameter [] commandParameters) {new SqlServer () CacheParameterSet (connectionString, commandText, commandParameters);.} /// /// Retrieve a parameter array from the cache /// /// < Param name = "connectionstring"> a valid connection string for a sqlconnection /// The storedure name or t-sql command //// an Array Of SqlParamters Public Static SqlParameter [] getCachedParameterset (string commandtext) {ArrayList Tempvalue = new arraylist (); iDataParameter [] SQL P = new SqlServer () GetCachedParameterSet (connectionString, commandText);. Foreach (IDataParameter parameter in sqlP) {tempValue.Add (parameter);} return (SqlParameter []) tempValue.ToArray (typeof (SqlParameter));}
#ndregion caching functions
#Region Parameter Discovery Functions
/// /// Retrieves the set of sqlparameters appropriate for the storedure /// /// /// this method will query the database for this information, and then store IT IN a cache for future requests. /// /// a valid connection string for a SqlConnection /// The name of the stored procedure /// An array of SqlParameters public static SqlParameter [] GetSpParameterSet (string connectionString, string spName) {ArrayList tempValue = new ArrayList (); foreach (IDataParameter parameter in new SqlServer ( ) .Getspparameters (CONNECTIONSTRING, SPNAME)) {TempValue.add (parameter);} return (SqlParameter []) TempValue.toArray (Typeof (Sqlparameter);}
/// /// Retrieves the set of sqlparameters appropriate for the storedure /// /// /// this method will query the database for this information, and then store IT IN a cache for future requests. /// /// a valid connection string for a SqlConnection /// The name of the stored procedure /// A bool value indicating whether the return value parameter should be included in the results /// An array of SqlParameters public static SqlParameter [] GetSpParameterSet (string connectionString, string spName, bool includeReturnValueParameter) {ArrayList tempValue = new ArrayList (); foreach (. IDataParameter parameter in new SqlServer () GetSpParameterSet (connectionString, spName, includeReturnValueParameter)) {tempValue.Add (parameter } Return (SqlParameter []) TempValue.Toarray (Typeof (Sqlparamet ER);
/// /// Retrieves the set of sqlparameters appropriate for the storedure /// /// /// this method will query the database for this information, and then store IT IN a cache for future requests. /// /// a valid sqlconnection object /// The name of the stored procedure < / param> /// An array of SqlParameters /// Thrown if spName is null /// Thrown if connection is null public static sqlparameter [] getsparameterset (idbconnection connection, string spname) {Return getSpparameterSet (CONNECTION, SPNAME, FALSE);}
/// /// Retrieves the set of sqlparameters appropriate for the storedure /// /// /// this method will query the database for this information, and then store IT IN a cache for future requests. /// /// a valid sqlconnection object /// The name of the stored procedure < / param> /// A bool value indicating whether the return value parameter should be included in the results /// An array of SqlParameters /// Thrown if spName is null /// Thrown if connection is null public static SqlParameter [] GetSpParameterSet (IDbConnection connection , String SpName, Bool IncludereturnValueParameter) {ArrayList Tempvalue = New ArrayList (); Foreach (iDataParameter Parameter in new sqlserver (). GetSpparamete rSet (connection, spName, includeReturnValueParameter)) {tempValue.Add (parameter);} return (SqlParameter []) tempValue.ToArray (typeof (SqlParameter));} #endregion Parameter Discovery Functions}
/// /// The Odbc class is intended to encapsulate high performance, scalable best practices for /// common uses of the Odbc ADO.NET provider. It is created using the abstract factory in AdoHelper /// public class Odbc: AdoHelper {// used for correcting Call syntax for stored procedures in ODBC private static Regex _regExpr = new Regex (@ "/{.* call | CALL / s / w *.}", RegexOptions.Compiled) ; /// /// Create An ODBC Helper. Needs to Be a Default Construction So That The Factory CAN CREATE IT / / / Public ODBC () {}
#REGON overrides
/// /// Returns an array of odbcparameters of the specified size /// /// size of the array //// The array of odbcparameters protected override idataparameter [] getDataParameters (int size) {return new odbcparameter [size];
/// /// Returns an odbcconnection Object for the given connection string /// /// The connection string to be used to create the connection /// an odbcConnection Object public override idbconnection getConnection (String Connectionstring) {Return New Odbcconnection (Connectionstring);
/// /// Returns an OdbcDataAdapter object /// /// The OdbcDataAdapter public override IDbDataAdapter GetDataAdapter () {return new OdbcDataAdapter ();}
/// /// Calls the CommandBuilder.DeriveParameters method for the specified provider, doing any setup and cleanup necessary /// /// The IDbCommand referencing the stored procedure . from which the parameter information is to be derived The derived parameters are added to the Parameters collection of the IDbCommand public override void DeriveParameters (IDbCommand cmd) {bool mustCloseConnection = false;. if ((cmd is OdbcCommand)!) throw new ArgumentException ( "The command provided is not a OdbcCommand instance.", "cmd"); if (cmd.Connection.State = ConnectionState.Open!) {cmd.Connection.Open (); mustCloseConnection = true;}
Odbccommandbuilder.deriveParameters ((ODBCCommand) CMD);
IF (MustCloseConnection) {cmd.connection.close ();}}
/// /// Returns an odbcparameter Object /// /// The odbcparameter Object public override idataParameter getParameter () {return new odbcparameter ();}
/// /// this cleans up the parameter syntax for an odbc call. this Was split out from prepareCommand SO That It could be caled independently. /// /// An idbcommand object containing the commandtext to clean. public override void cleanparametersyntax (idbcommand command) {string call =" call ";
if (command.CommandType == CommandType.StoredProcedure) {if (! _regExpr.Match (command.CommandText) .Success && // It does not like like {call sp_name ()} command.CommandText.Trim (). IndexOf ( " ") == -1) // If there's only a stored procedure name {// If there's only a stored procedure name StringBuilder par = new StringBuilder ();! if (command.Parameters.Count = 0) {bool isFirst = true Bool Hasparameters = false; for (int i = 0; i
/// /// Execute An IDBCommand (That Returns A Resultset) against the provided idbconnection. /// /// /// /// xmlreader r = helper. Executexmlreader (Command); /// /// The idbcommand to execute /// an Xmlreader Containing The ResultSet generated by the Command public override XmlReader ExecuteXmlReader (IDbCommand command) {bool mustCloseConnection = false; if (! command.Connection.State = ConnectionState.Open) {command.Connection.Open (); mustCloseConnection = true;}
CleanparameterSyntax; Command;
Odbcdataadapter Da = New OdbcdataAdapter (odbccommand) Command; DataSet DS = New Dataset ();
Da.missingschemaAction = missingschemaAction.addwithkey; da.fill (ds);
StringReader stream = new stringReader (DS.GETXML ());
IF (MustCloseConnection) {Command.connection.Close ();
Return New XMLTextReader (stream);
/// /// provider specific code to set up the updating / ed Event handlers buy by updateDataSet /// /// DataAptapter to attach the Event Handlers To < / param> /// The Handler to Be Called When a Row Is Updating /// The Handler to Be Called When a Row Is Updated < / param> protected override void AddUpdateEventHandlers (IDbDataAdapter dataAdapter, rowUpdatingHandler rowUpdatingHandler, rowUpdatedHandler rowUpdatedHandler) {if (rowUpdatingHandler = null!) {this.m_rowUpdating = rowUpdatingHandler; ((OdbcDataAdapter) dataAdapter) .RowUpdating = new OdbcRowUpdatingEventHandler (RowUpdating);} if (RowupdatedHandler! = null) {this.m_rowupdated = rowupdatedhandler; (odbcdataadapter) DataAdapter) .roWUpdated = new odbcrowupdatedEventHandler (Rowupdated);}
/// /// Handles the rowupdating Event /// /// The Object what published the evenet /// The odbcrowupdatingeventargs protected" {base.rowupdating (obj, e);} (Obj, e);
/// /// Handles the Rowupdated Event /// /// The Object That Published The Event /// The odbcrowupdateDeventargs protected (Object Obj, OdbcrowUpdatedEventArgs e) {base.rowupdated (Obj, e);
/// /// Handle Any Provider-Specific Issues with blobs Here by "Washing" The iDataParameter and return a new one "set up appropriately for the provider. /// /// The idbconnection to use in cleansing the parameter /// The parameter before cleaning /// The parameter after it's been cleansed. Protected Override iDataParameter getBlobParameter (IDbconnection connection, iDataParameter P) {// Nothing Special Needed for ODBC ... SO FAR AS We know now. Return P;} #ENDREGON}
/// /// The OleDb class is intended to encapsulate high performance, scalable best practices for /// common uses of the OleDb ADO.NET provider. It is created using the abstract factory in AdoHelper /// public class oledb: adohelper {/// /// Create an OLEDB HELPER. NEEDS to Be a default constructor so That The factory can create it /// public oledb ()}
#REGON overrides
/// /// Returns an array of oledbparameters of the specified size /// /// size of the array //// The array of odbcparameters protected override idataparameter [] getDataparameters (int size) {return new oledbparameter [size];}
/// /// Returns An Oledbconnection Object for the given connection string /// /// The connection string to be used to create the connection /// An OleDbConnection object public override IDbConnection GetConnection (string connectionString) {return new OleDbConnection (connectionString);} /// /// Returns an OleDbDataAdapter object /// /// The oledbdataadapter public override idbdataadapter getdataadapter () {return new oledbdataadapter ();
/// /// Calls the CommandBuilder.DeriveParameters method for the specified provider, doing any setup and cleanup necessary /// /// The IDbCommand referencing the stored procedure . from which the parameter information is to be derived The derived parameters are added to the Parameters collection of the IDbCommand public override void DeriveParameters (IDbCommand cmd) {bool mustCloseConnection = false.;
IF (! (cmd is oledbcommand) throw new argumentexception ("The Command Provided is not a oledbcommand instance.", "cmd");
IF (cmd.connection.State! = connectionState.open) {cmd.connection.open (); mustcloseConnection = true;
OLEDBCommandbuilder.deriveParameters ((OLEDBCommand) CMD);
IF (MustCloseConnection) {cmd.connection.close ();}}
/// /// Returns an OLEDBPARETER Object /// /// The OLEDBPARETER Object public override idataParameter getParameter () {return new oledbparameter ();} /// /// this cleans up the parameter syntax for an oledb call. This Was split out from prepareCommand so it at could be called independently. /// /// an IDBCommand Object Containing The Commandtext to Clean. public override void cleanparametersyntax (idbcommand command) {}
/// /// Execute An IDBCommand (That Returns A Resultset) against the provided idbconnection. /// /// /// /// xmlreader r = helper. Executexmlreader (Command); /// /// The idbcommand to execute /// an Xmlreader Containing The ResultSet generated by the Command public override xmlreader executexmlreader (idbcommand command) {BOOL MUSTCLOSECONNECTION = FALSE
IF (Command.Connection.State! = connectionState.Open) {command.connection.open (); mustcloseConnection = true;
CleanparameterSyntax; Command;
OLEDBDATADAPTER Da = New OLEDBDataAdapter (OLEDBCommand) Command; DataSet DS = New DataSet ();
Da.missingschemaAction = missingschemaAction.addwithkey; da.fill (ds);
StringReader stream = new stringReader (DS.GETXML ()); if (MustCloseConnection) {Command.Connection.Close ();
Return New XMLTextReader (stream);
/// /// provider specific code to set up the updating / ed Event handlers buy by updateDataSet /// /// DataAptapter to attach the Event Handlers To < / param> /// The Handler to Be Called When a Row Is Updating /// The Handler to Be Called When a Row Is Updated < / param> protected override void AddUpdateEventHandlers (IDbDataAdapter dataAdapter, rowUpdatingHandler rowUpdatingHandler, rowUpdatedHandler rowUpdatedHandler) {if (rowUpdatingHandler = null!) {this.m_rowUpdating = rowUpdatingHandler; ((OleDbDataAdapter) dataAdapter) .RowUpdating = new OleDbRowUpdatingEventHandler (RowUpdating);} if (RowupdatedHandler! = null) {this.m_rowupdated = rowupdatedhandler; (oledbdataadapter) DataAdapter ).Rowupdated = new OLEDBROWUPDATEDHANDLER (Rowupdated);}}
/// /// Handles the rowupdating Event /// /// The Object what published the evenet /// The OLEDBROWUPDANGEVENTARGS Protected Void RowupDating (Object OLEDBROWUPDANGEVENTARGS E) {base.rowupdating (Obj, e);
/// /// Handles the Rowupdated Event /// /// The Object That Published The Event /// The OLEDBROWUPDATEVENTARGS protected (Object Obj, OLEDBROWUPDATEDEVENTARGS E) {base.rowupdated (Obj, e);}
/// /// Handle Any Provider-Specific Issues with blobs Here by "Washing" The iDataParameter and return a new one "set up appropriately for the provider. /// /// The idbconnection to use in cleansing the parameter /// The parameter before cleaning /// The parameter after it's been cleansed. Protected Override IdataParameter getBlobParameter (iDATAPARETER P) {// Nothing Special Needled for OLEDB ... as far as we know now reburn p;} #ENDREGON}
/// /// The Oracle class is intended to encapsulate high performance, scalable best practices for /// common uses of the Oracle ADO.NET provider. It is created using the abstract factory in AdoHelper. /// < / summary> public class oracle: adohelper {/// /// Create An Oracle Helper. Needs to be a default constructor so thing the factory can create it /// public oracle () {}
#REGON overrides
/// /// Returns an Array OracleParameters of the specified size /// /// size of the array /// The array of oracleparameters protected override idataparameter [] getDataparameters (int size) {return new oracleparameter [size];
/// /// Returns An OracleConnection Object for the Given Connection String /// /// The connection string to be used to create the connection /// An OracleConnection object public override IDbConnection GetConnection (string connectionString) {return new OracleConnection (connectionString);} /// /// Returns an OracleDataAdapter object /// /// The oracleDataadapter public override idbdataadapter getdataadapter () {return new oracleDataadapter ();
/// /// Calls the CommandBuilder.DeriveParameters method for the specified provider, doing any setup and cleanup necessary /// /// The IDbCommand referencing the stored procedure . from which the parameter information is to be derived The derived parameters are added to the Parameters collection of the IDbCommand public override void DeriveParameters (IDbCommand cmd) {bool mustCloseConnection = false.;
IF (! (cmd is oracleCommand) Throw new ArgumentException ("The Command Provided is not an oracleCommand Instance.", "cmd");
IF (cmd.connection.State! = connectionState.open) {cmd.connection.open (); mustcloseConnection = true;
OracleCommandbuilder.deriveParameters ((OracleCommand) CMD);
IF (MustCloseConnection) {cmd.connection.close ();}}
/// /// Returns an OracleParameter object /// /// The OracleParameter object public override IDataParameter GetParameter () {OracleParameter parameter = new OracleParameter (); parameter. Size = 255; return parameter;} /// /// Get An iDataParameter for use in a sql command /// /// The name of the parameter to create /// The value of the specified parameter /// An IDataParameter object public override IDataParameter GetParameter (string parameterName, object value) {OracleParameter Parameter = New OracleParameter (); parameter.ParameterName = parameterName; parameter.value = value; parameter.size = getParametersize (parametername); return parameter;}
/// //////////// /// The parameter name /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////> / The size private int getParametersize (string name) {int size = 255; return size;}
/// /// this cleans up the parameter syntax for an oracle call. this Was split out from prepareCommand SO That It could be called independently. /// /// An IDbCommand object containing the CommandText to clean. public override void CleanParameterSyntax (IDbCommand command) {if (command.CommandType == CommandType.Text) {command.CommandText = command.CommandText.Replace (" @ ", ":");} F (Command.Parameters.count> 0) {Foreach (OracleParameter Parameter In Command.Parameters) {parameter.ParameterName = parameter.ParameterName.Replace ("@", ":");}}}
/// /// Execute An IDBCommand (That Returns A Resultset) against the provided idbconnection. /// /// /// /// xmlreader r = helper. Executexmlreader (Command); /// /// The idbcommand to execute /// an Xmlreader Containing The ResultSet generated by the Command public override xmlreader executexmlreader (idbcommand command) {BOOL MUSTCLOSECONNECTION = FALSE
IF (Command.Connection.State! = connectionState.Open) {command.connection.open (); mustcloseConnection = true;
CleanparameterSyntax; Command;
OracleDataAdapter Da = New OracleDataAdapter ((OracleCommand) Command; DataSet DS = New DataSet ();
Da.missingschemaAction = missingschemaAction.addwithkey; da.fill (ds);
StringReader stream = new stringReader (DS.GETXML ()); if (MustCloseConnection) {Command.Connection.Close ();} return new xmltextreader (station);}
/// /// provider specific code to set up the updating / ed Event handlers buy by updateDataSet /// /// DataAptapter to attach the Event Handlers To < / param> /// The Handler to Be Called When a Row Is Updating /// The Handler to Be Called When a Row Is Updated < / param> protected override void AddUpdateEventHandlers (IDbDataAdapter dataAdapter, rowUpdatingHandler rowUpdatingHandler, rowUpdatedHandler rowUpdatedHandler) {if (rowUpdatingHandler = null!) {this.m_rowUpdating = rowUpdatingHandler; ((OracleDataAdapter) dataAdapter) .RowUpdating = new OracleRowUpdatingEventHandler (RowUpdating);}
IF (RowupdatedHandler! = NULL) {this.m_rowupdated = RowUpdatedHandler; (ORACLEDATADAPTER) DataAdapter ).Rowupdated = New OracleroPdatedEventHandler (Rowupdated);}}
/// /// Handles the rowupdating Event /// /// The Object what published the evenet /// > The OraclelowUpdatingEventArgs Protected Void Rowupdating (Object Obj, ORACLEROWUPDANGEVENTARGS E) {base.rowupdating (Obj, e);
/// /// Handles the Rowupdated Event /// /// The Object That Published The Event /// The OracleRowUpdatedEventArgs protected void RowUpdated (object obj, OracleRowUpdatedEventArgs e) {base.RowUpdated (obj, e);} /// /// Handle any provider-specific issues with BLOBs here by" washing "The IdataParameter and Returning a New One" IS set up appropriately for the provider. /// See MS KnowledgeBase Article: http://support.microsoft.com/default.aspx?scid=kb;n-us; 322796 // / /// The idbconnection to use in cleansing the parameter /// The parameter before cleaning /// The parameter after it's been cleansed protected override IDataParameter GetBlobParameter (IDbConnection connection, IDataParameter p) {OracleConnection clonedConnection = (OracleConnection) (((ICloneable) connection) .Clone ()).;
ClonedConnection.open ();
OracleCommand cmd = clonedConnection.CreateCommand (); cmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary (xx, false, 0) ;: tempblob: = xx; end;"; cmd.Parameters.Add (new OracleParameter ( " Tempblob ", ORACLETYPE.BLOB). Direction = parameterDirection.output; cmd.executenonQuery ();
OracleLob tempLob; tempLob = (OracleLob) (cmd.Parameters [0] .Value); tempLob.BeginBatch (OracleLobOpenMode.ReadWrite); tempLob.Write ((byte []) (p.Value), 0, System.Runtime.InteropServices .Marshal.sizeof (p.Value); Templob.Endbatch ();
OracleParameter Op = New OracleParameter (P.ParameterNameter (ORACLETYPE.BLOB); Op.Value = Templob; CloneDConnection.Close ();
Return Op;}
#ndregion}}}
