C # sqlhelper

Using system;
Using system. configuration;
Using system. Data;
Using system. Data. sqlclient;
Using system. collections;

Namespace dbutility
{

/// <Summary>
/// The sqlhelper class is intended to encapsulate high performance,
/// Scalable best practices for common uses of sqlclient.
/// </Summary>
Public abstract class sqlhelper
{

// Database connection strings
Public static readonly string connectionstring = @ "Server = database instance name; database = database name; uid = login name"; Pwd = PASSWORD (no password can be left empty );
// Public static readonly string connectionstring = @ "Data Source =." sqlexpress; initial catalog = dfrvdb; Integrated Security = true ";
// Public static readonly string connectionstringlocaltransaction = configurationmanager. connectionstrings ["sqlconnstring1"]. connectionstring;
// Public static readonly string connectionstringinventorydistributedtransaction = configurationmanager. connectionstrings ["sqlconnstring2"]. connectionstring;
// Public static readonly string connectionstringorderdistributedtransaction = configurationmanager. connectionstrings ["sqlconnstring3"]. connectionstring;
// Public static readonly string connectionstringprofile = configurationmanager. connectionstrings ["sqlprofileconnstring"]. connectionstring;

// Hashtable to store cached Parameters
Private Static hashtable parmcache = hashtable. synchronized (New hashtable ());

/// <Summary>
/// Execute a sqlcommand (that returns no resultset) against the database specified in the connection string
/// Using the provided parameters.
/// </Summary>
/// <Remarks>
/// E.g .:
/// Int result = executenonquery (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "connectionstring"> A valid connection string for a sqlconnection </param>
/// <Param name = "commandtype"> the commandtype (stored procedure, text, etc.) </param>
/// <Param name = "commandtext"> the stored procedure name or T-SQL command </param>
/// <Param name = "commandparameters"> an array of sqlparamters used to execute the command </param>
/// <Returns> an int representing the number of rows affected by the command </returns>
Public static int executenonquery (string connectionstring, commandtype parameter type, string parameter text, Params sqlparameter [] commandparameters)
{

Sqlcommand cmd = new sqlcommand ();

Using (sqlconnection conn = new sqlconnection (connectionstring ))
{
Preparecommand (CMD, Conn, null, struct type, plain text, commandparameters );
Int val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
Return val;
}
}

/// <Summary>
/// Execute a sqlcommand (that returns no resultset) against an existing database connection
/// Using the provided parameters.
/// </Summary>
/// <Remarks>
/// E.g .:
/// Int result = executenonquery (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "conn"> an existing database connection </param>
/// <Param name = "commandtype"> the commandtype (stored procedure, text, etc.) </param>
/// <Param name = "commandtext"> the stored procedure name or T-SQL command </param>
/// <Param name = "commandparameters"> an array of sqlparamters used to execute the command </param>
/// <Returns> an int representing the number of rows affected by the command </returns>
Public static int executenonquery (sqlconnection connection, commandtype parameter type, string parameter text, Params sqlparameter [] commandparameters)
{

Sqlcommand cmd = new sqlcommand ();

Preparecommand (CMD, connection, null, argument type, plain text, commandparameters );
Int val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
Return val;
}

/// <Summary>
/// Execute a sqlcommand (that returns no resultset) using an existing SQL transaction
/// Using the provided parameters.
/// </Summary>
/// <Remarks>
/// E.g .:
/// Int result = executenonquery (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "trans"> an existing SQL transaction </param>
/// <Param name = "commandtype"> the commandtype (stored procedure, text, etc.) </param>
/// <Param name = "commandtext"> the stored procedure name or T-SQL command </param>
/// <Param name = "commandparameters"> an array of sqlparamters used to execute the command </param>
/// <Returns> an int representing the number of rows affected by the command </returns>
Public static int executenonquery (sqltransaction trans, commandtype primitive type, string plain text, Params sqlparameter [] commandparameters)
{
Sqlcommand cmd = new sqlcommand ();
Preparecommand (CMD, trans. Connection, trans, argument type, plain text, commandparameters );
Int val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
Return val;
}

/// <Summary>
/// Execute a sqlcommand that returns a resultset against the database specified in the connection string
/// Using the provided parameters.
/// </Summary>
/// <Remarks>
/// E.g .:
/// Sqldatareader r = executereader (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "connectionstring"> A valid connection string for a sqlconnection </param>
/// <Param name = "commandtype"> the commandtype (stored procedure, text, etc.) </param>
/// <Param name = "commandtext"> the stored procedure name or T-SQL command </param>
/// <Param name = "commandparameters"> an array of sqlparamters used to execute the command </param>
/// <Returns> A sqldatareader containing the results </returns>
Public static sqldatareader executereader (string connectionstring, commandtype cmdtype, string cmdtext, Params sqlparameter [] commandparameters)
{
Sqlcommand cmd = new sqlcommand ();
Sqlconnection conn = new sqlconnection (connectionstring );

// We use a try/catch here because if the method throws an exception we want
// Close the connection throw code, because no datareader will exist, hence
// Commandbehaviour. closeconnection will not work
Try
{
Preparecommand (CMD, Conn, null, struct type, plain text, commandparameters );
Sqldatareader RDR = cmd. executereader (commandbehavior. closeconnection );
Cmd. Parameters. Clear ();
Return RDR;
}
Catch
{
Conn. Close ();
Throw;
}
}

Public static datatable getdatatable (string connectionstring, commandtype cmdtype, string cmdtext, Params sqlparameter [] commandparameters)
{
Datatable dt = new datatable ();
Sqlcommand cmd = new sqlcommand ();
Sqldataadapter da = new sqldataadapter (CMD );

Sqlconnection conn = new sqlconnection (connectionstring );

// We use a try/catch here because if the method throws an exception we want
// Close the connection throw code, because no datareader will exist, hence
// Commandbehaviour. closeconnection will not work
Try
{
Preparecommand (CMD, Conn, null, struct type, plain text, commandparameters );
// Sqldatareader RDR = cmd. executereader (commandbehavior. closeconnection );
Cmd. Parameters. Clear ();
Da. Fill (DT );
Return DT;
// Return RDR;
}
Catch
{
// Conn. Close ();
Throw;
}
}

/// <Summary>
/// Execute a sqlcommand that returns the first column of the First record against the database specified in the connection string
/// Using the provided parameters.
/// </Summary>
/// <Remarks>
/// E.g .:
/// Object OBJ = executescalar (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "connectionstring"> A valid connection string for a sqlconnection </param>
/// <Param name = "commandtype"> the commandtype (stored procedure, text, etc.) </param>
/// <Param name = "commandtext"> the stored procedure name or T-SQL command </param>
/// <Param name = "commandparameters"> an array of sqlparamters used to execute the command </param>
/// <Returns> an object that shocould be converted to the expected type using convert. To {type} </returns>
Public static object executescalar (string connectionstring, commandtype parameter type, string parameter text, Params sqlparameter [] commandparameters)
{
Sqlcommand cmd = new sqlcommand ();

Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Preparecommand (CMD, connection, null, argument type, plain text, commandparameters );
Object val = cmd. executescalar ();
Cmd. Parameters. Clear ();
Return val;
}
}

/// <Summary>
/// Execute a sqlcommand that returns the first column of the First record against an existing database connection
/// Using the provided parameters.
/// </Summary>
/// <Remarks>
/// E.g .:
/// Object OBJ = executescalar (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 ));
/// </Remarks>
/// <Param name = "conn"> an existing database connection </param>
/// <Param name = "commandtype"> the commandtype (stored procedure, text, etc.) </param>
/// <Param name = "commandtext"> the stored procedure name or T-SQL command </param>
/// <Param name = "commandparameters"> an array of sqlparamters used to execute the command </param>
/// <Returns> an object that shocould be converted to the expected type using convert. To {type} </returns>
Public static object executescalar (sqlconnection connection, commandtype parameter type, string parameter text, Params sqlparameter [] commandparameters)
{

Sqlcommand cmd = new sqlcommand ();

Preparecommand (CMD, connection, null, argument type, plain text, commandparameters );
Object val = cmd. executescalar ();
Cmd. Parameters. Clear ();
Return val;
}

///

// Add parameter array to the cache
///
// key to the parameter cache
// an array of sqlparamters to be cached
Public static void cacheparameters (string cachekey, params sqlparameter [] commandparameters)
{< br> parmcache [cachekey] = commandparameters;
}

/// <Summary>
/// Retrieve cached Parameters
/// </Summary>
/// <Param name = "cachekey"> key used to lookup parameters </param>
/// <Returns> cached sqlparamters array </returns>
Public static sqlparameter [] getcachedparameters (string cachekey)
{
Sqlparameter [] cachedparms = (sqlparameter []) parmcache [cachekey];

If (cachedparms = NULL)
Return NULL;

Sqlparameter [] clonedparms = new sqlparameter [cachedparms. Length];

For (INT I = 0, j = cachedparms. length; I <j; I ++)
Clonedparms [I] = (sqlparameter) (icloneable) cachedparms [I]). Clone ();

Return clonedparms;
}

/// <Summary>
/// Prepare a command for execution
/// </Summary>
/// <Param name = "cmd"> sqlcommand object </param>
/// <Param name = "conn"> sqlconnection object </param>
/// <Param name = "trans"> sqltransaction object </param>
/// <Param name = "partition type"> cmd type e.g. Stored Procedure or text </param>
/// <Param name = "plain text"> command text, e.g. Select * from products </param>
/// <Param name = "paiparms"> sqlparameters to use in the Command </param>
Private Static void preparecommand (sqlcommand cmd, sqlconnection Conn, sqltransaction trans, commandtype primitive type, string plain text, sqlparameter [] partial parms)
{

If (conn. State! = Connectionstate. open)
Conn. open ();

Cmd. Connection = conn;
Cmd. commandtext = plain text;

If (trans! = NULL)
Cmd. Transaction = trans;

Cmd. commandtype = primitive type;

If (partition parms! = NULL)
{
Foreach (sqlparameter parm in milliseconds parms)
Cmd. Parameters. Add (parm );
}
}
}
}