| Createcommand CREATE command # region createcommand CREATE Command /** // <Summary>
/// Create a Command provided by the Stored Procedure
/// </Summary>
/// <Remarks>
/// E.g .:
/// Sqlcommand command = createcommand (Conn, "addcustomer", "customerid", "customername ");
/// </Remarks>
/// <Param name = "connection"> A valid connection </param>
/// <Param name = "spname"> name of the stored procedure </param>
/// <Param name = "sourcecolumns"> source column name array </param>
/// <Returns> A valid command </returns>
Internal static sqlcommand createcommand (sqlconnection connection, string spname, Params string [] sourcecolumns)
{
If (connection = NULL) throw new argumentexception ("connection ");
If (spname = NULL | spname. Length = 0) throw new argumentexception ("spname "); // Create a command
Sqlcommand cmd = new sqlcommand (spname, connection );
Cmd. commandtype = commandtype. storedprocedure; // Process a parameter if it is accepted
If (sourcecolumns! = NULL) & (sourcecolumns. length> 0 ))
{
// Extract stored procedure parameters
Sqlparameter [] commandparameters = sqlhelperparametercache. getspparameterset (connection, spname ); // Set the name of the source column
For (INT Index = 0; index <sourcecolumns. length; index ++)
Commandparameters [Index]. sourcecolumn = sourcecolumns [Index]; // Attaches the parameter to the command
Attachparameters (CMD, commandparameters );
}
Return cmd;
} # Endregion /** // <Summary>
/// This method assigns an array value to a sqlparameter array.
/// </Summary>
/// <Param name = "commandparameters"> sqlparameter array to be assigned a value </param>
/// <Param name = "parametervalues"> an array of objects containing parameter values </param>
Private Static void assignparametervalues (sqlparameter [] commandparameters, object [] parametervalues)
{
If (commandparameters = NULL) | parametervalues = NULL)
{
// If no data exists, return
Return;
} // The number of parameters must match the value.
If (commandparameters. length! = Parametervalues. length)
{
Throw new argumentexception ("the number of parameters cannot match the number of parameter values ");
} // Iterate the parameter array and assign the value of the object array to the corresponding parameter
For (INT I = 0, j = commandparameters. length; I <j; I ++)
{
// If the value of the array inherits from idbdataparameter, This is the attribute value assigned to it.
If (parametervalues [I] Is idbdataparameter)
{
Idbdataparameter parainstance = (idbdataparameter) parametervalues [I];
If (parainstance. value = NULL)
{
Commandparameters [I]. value = dbnull. value;
}
Else
{
Commandparameters [I]. value = parainstance. value;
}
}
Else if (parametervalues [I] = NULL)
{
Commandparameters [I]. value = dbnull. value;
}
Else
{
Commandparameters [I]. value = parametervalues [I];
}
}
} // -- The above is also good. -- I have analyzed the other items clearly, but I feel dizzy. Why ~~ This class has a lot of reloads.--I don't like it, so I don't want to write it out -- Sqlhelperparametercache--generally, it is not used. It is a waste of memory, but it is not as good as it is in some special cases. When the data entity is automatically generated, the-waiser plug is easy to use, --You can use this guy to generate the entities of all stored procedures.-Some programs do this.-Let's take a look. /** // <Summary>
/// Sqlhelperparametercache provides some functions for discovering stored procedure parameters.
/// </Summary>
Internal sealed class sqlhelperparametercache
{
Private methods, variables, and constructors # region private methods, variables, and constructors // This class only provides static methods and uses a private constructor to prevent the creation of an instantiated object.
Private sqlhelperparametercache (){} Private Static hashtable paramcache = hashtable. synchronized (New hashtable ()); /** // <Summary>
/// Found the stored procedure at runtime
/// </Summary>
/// <Param name = "connection"> A valid connection object </param>
/// <Param name = "spname"> name of the stored procedure </param>
/// <Param name = "includereturnvalueparameter"> whether to include the returned parameter </param>
/// <Returns> List of detected parameters </returns>
Private Static sqlparameter [] discoverspparameterset (sqlconnection connection, string spname, bool extends dereturnvalueparameter)
{
If (connection = NULL) throw new argumentnullexception ("connection ");
If (spname = NULL | spname. Length = 0) throw new argumentnullexception ("spname "); Sqlcommand cmd = new sqlcommand (spname, connection );
Cmd. commandtype = commandtype. storedprocedure; Connection. open ();
Sqlcommandbuilder. deriveparameters (CMD );
Connection. Close (); If (! Extends dereturnvalueparameter)
{
Cmd. Parameters. removeat (0 );
} Sqlparameter [] discoveredparameters = new sqlparameter [cmd. Parameters. Count]; Cmd. Parameters. copyto (discoveredparameters, 0 ); // Set the parameter to dbnull. Value
Foreach (sqlparameter discoveredparameter in discoveredparameters)
{
Discoveredparameter. value = dbnull. value;
}
Return discoveredparameters;
} /** // <Summary>
/// Depth copy parameter Array
/// </Summary>
/// <Param name = "originalparameters"> </param>
/// <Returns> </returns>
Private Static sqlparameter [] cloneparameters (sqlparameter [] originalparameters)
{
Sqlparameter [] clonedparameters = new sqlparameter [originalparameters. Length]; For (INT I = 0, j = originalparameters. length; I <j; I ++)
{
Clonedparameters [I] = (sqlparameter) (icloneable) originalparameters [I]). Clone ();
} Return clonedparameters;
} # Endregion private methods, variables, and constructors Caching functions # region caching Functions /** // <Summary>
/// Add the parameter array to the cache
/// </Summary>
/// <Param name = "connectionstring"> A valid connection string </param>
/// <Param name = "commandtext"> command text </param>
/// <Param name = "commandparameters"> cached parameter array </param>
Internal static void cacheparameterset (string connectionstring, string commandtext, Params sqlparameter [] commandparameters)
{
If (connectionstring = NULL | connectionstring. Length = 0) throw new argumentnullexception ("connectionstring ");
If (commandtext = NULL | commandtext. Length = 0) throw new argumentnullexception ("commandtext "); String hashkey = connectionstring + ":" + commandtext; Paramcache [hashkey] = commandparameters;
} /** // <Summary>
/// Extract parameters from the cache
/// </Summary>
/// <Param name = "connectionstring"> A valid connection string </param>
/// <Param name = "commandtext"> command text </param>
/// <Returns> parameter array </returns>
Internal static sqlparameter [] getcachedparameterset (string connectionstring, string commandtext)
{
If (connectionstring = NULL | connectionstring. Length = 0) throw new argumentnullexception ("connectionstring ");
If (commandtext = NULL | commandtext. Length = 0) throw new argumentnullexception ("commandtext "); String hashkey = connectionstring + ":" + commandtext; Sqlparameter [] cachedparameters = paramcache [hashkey] As sqlparameter [];
If (cachedparameters = NULL)
{
Return NULL;
}
Else
{
// Why do we need to clone
Return cloneparameters (cachedparameters );
}
} # Endregion caching Functions Parameter discovery functions # region parameter discovery functions /** // <Summary>
/// Obtain the stored procedure parameters
/// </Summary>
/// <Remarks>
/// This method queries the database and places it in the cache
/// </Remarks>
/// <Param name = "connectionstring"> A valid connection </param>
/// <Param name = "spname"> name of the stored procedure </param>
/// <Returns> array parameters </returns>
Internal static sqlparameter [] getspparameterset (string connectionstring, string spname)
{
Return getspparameterset (connectionstring, spname, false );
} /** // <Summary>
/// Obtain the stored procedure parameters
/// </Summary>
/// <Remarks>
/// This method queries the database and places it in the cache
/// </Remarks>
/// <Param name = "connectionstring"> A valid connection </param>
/// <Param name = "spname"> name of the stored procedure </param>
/// <Param name = "includereturnvalueparameter"> whether to return the parameter value in the result </param>
/// <Returns> parameter array </returns>
Internal static sqlparameter [] getspparameterset (string connectionstring, string spname, bool extends dereturnvalueparameter)
{
If (connectionstring = NULL | connectionstring. Length = 0) throw new argumentnullexception ("connectionstring ");
If (spname = NULL | spname. Length = 0) throw new argumentnullexception ("spname "); Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Return getspparametersetinternal (connection, spname, includereturnvalueparameter );
}
} /** // <Summary>
/// Obtain the stored procedure parameters
/// </Summary>
/// <Remarks>
/// This method queries the database and places it in the cache
/// </Remarks>
/// <Param name = "connection"> A valid connection object </param>
/// <Param name = "spname"> name of the stored procedure </param>
/// <Returns> parameter array </returns>
Internal static sqlparameter [] getspparameterset (sqlconnection connection, string spname)
{
Return getspparameterset (connection, spname, false );
} /** // <Summary>
/// Obtain the stored procedure parameters
/// </Summary>
/// <Remarks>
/// This method queries the database and places it in the cache
/// </Remarks>
/// <Param name = "connection"> A valid connection object </param>
/// <Param name = "spname"> name of the stored procedure </param>
/// <Param name = "includereturnvalueparameter"> whether to return the parameter value in the result </param>
/// <Returns> parameter array </returns>
Internal static sqlparameter [] getspparameterset (sqlconnection connection, string spname, bool extends dereturnvalueparameter)
{
If (connection = NULL) throw new argumentnullexception ("connection ");
Using (sqlconnection clonedconnection = (sqlconnection) (icloneable) connection). Clone ())
{
Return getspparametersetinternal (clonedconnection, spname, includereturnvalueparameter );
}
} /** // <Summary>
/// This method queries the database and places it in the cache
/// </Summary>
/// <Param name = "connection"> A valid connection object </param>
/// <Param name = "spname"> name of the stored procedure </param>
/// <Param name = "includereturnvalueparameter"> whether to return the parameter value in the result </param>
/// <Returns> parameter array </returns>
Private Static sqlparameter [] getspparametersetinternal (sqlconnection connection, string spname, bool extends dereturnvalueparameter)
{
If (connection = NULL) throw new argumentnullexception ("connection ");
If (spname = NULL | spname. Length = 0) throw new argumentnullexception ("spname "); String hashkey = connection. connectionstring + ":" + spname + (includereturnvalueparameter? ": Include returnvalue parameter ":""); Sqlparameter [] cachedparameters; Cachedparameters = paramcache [hashkey] As sqlparameter [];
If (cachedparameters = NULL)
{
Sqlparameter [] spparameters = discoverspparameterset (connection, spname, includereturnvalueparameter );
Paramcache [hashkey] = spparameters;
Cachedparameters = spparameters;
} Return cloneparameters (cachedparameters );
} # Endregion parameter discovery functions } |
