Common Operations in. net

Operatedatabase. CS file code:

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

Namespace gogofly. operatesqlserver
{
Public class operatedatabase
{
# Region defined attributes

/// <Summary>
/// Defines the value of the name attribute that identifies the connection string in Web. config.
/// </Summary>
Private Static string connectionstringnameinwebconfig = "sqlserverconnectionstring ";
Public static string connectionstringnameinwebconfig
{
Get
{
Return connectionstringnameinwebconfig;
}
Set
{// The string cannot be empty or referenced.
If (! String. isnullorempty (value ))
{
Connectionstringnameinwebconfig = value;
}
}
}

/// <Summary>
/// The parameter ID that stores the return value of the database operation.
/// </Summary>
Private Static string returnvaluestring = "returnvaluestring ";
Public static string returnvaluestring
{
Get
{
Return returnvaluestring;
}
}

# Endregion

# Region creation Parameters

/// <Summary>
/// Create parameters
/// </Summary>
/// <Param name = "paramname"> stored procedure name </param>
/// <Param name = "dbtype"> parameter type </param>
/// <Param name = "size"> parameter size </param>
/// <Param name = "direction"> parameter direction </param>
/// <Param name = "value"> parameter value </param>
/// <Returns> New Parameter object </returns>
Private Static sqlparameter createparam (string paramname, sqldbtype dbtype,
Int32 size, parameterdirection direction, object value)
{
Sqlparameter Param;
If (size> 0)
{// Use size to create a parameter with the size not 0
Param = new sqlparameter (paramname, dbtype, size );
}
Else
{
Param = new sqlparameter (paramname, dbtype );
}
/// Create output type parameters
Param. Direction = direction;
If (! (Direction = parameterdirection. Output & value = NULL ))
{
Param. value = value;
}
/// Response parameters
Return Param;
}

/// <Summary>
/// Create input type parameters
/// </Summary>
/// <Param name = "paramname"> stored procedure name </param>
/// <Param name = "dbtype"> parameter type </param>
/// <Param name = "size"> parameter size </param>
/// <Param name = "value"> parameter value </param>
/// <Returns> New Parameter object </returns>
Public static sqlparameter createinparam (string paramname,
Sqldbtype dbtype, int size, object value)
{
Return createparam (paramname, dbtype, size, parameterdirection. Input, value );
}

/// <Summary>
/// Create output type parameters
/// </Summary>
/// <Param name = "paramname"> stored procedure name </param>
/// <Param name = "dbtype"> parameter type </param>
/// <Param name = "size"> parameter size </param>
/// <Returns> New Parameter object </returns>
Public static sqlparameter createoutparam (string paramname,
Sqldbtype dbtype, int size)
{
Return createparam (paramname, dbtype, size, parameterdirection. Output, null );
}

/// <Summary>
/// Create return type parameters
/// </Summary>
/// <Param name = "paramname"> stored procedure name </param>
/// <Param name = "dbtype"> parameter type </param>
/// <Param name = "size"> parameter size </param>
/// <Returns> New Parameter object </returns>
Public static sqlparameter createreturnparam (string paramname,
Sqldbtype dbtype, int size)
{
Return createparam (paramname, dbtype, size, parameterdirection. returnvalue, null );
}

# Endregion

# Region create sqlcommand and sqldataadapter

/// <Summary>
/// Create a sqlcommand object
/// </Summary>
/// <Param name = "procname"> name of the stored procedure </param>
/// <Param name = "prams"> parameters required for Stored Procedures </param>
/// <Returns> returns the sqlcommand object </returns>
Private Static sqlcommand createsqlcommand (string procname,
Params sqlparameter [] prams)
{// Create a database connection
Sqlconnection sqlcon = createsqlconnection ();
/// Open the database connection
If (sqlcon = NULL) return NULL;
If (sqlcon. State = connectionstate. Closed)
{
Sqlcon. open ();
}
/// Set sqlcommand attributes
Sqlcommand cmd = new sqlcommand (procname, sqlcon );
Cmd. commandtype = commandtype. storedprocedure;

/// Add stored procedure parameters
If (prams! = NULL)
{
Foreach (sqlparameter parameter in prams)
{
Cmd. Parameters. Add (parameter );
}
}

/// Add response parameters
Cmd. Parameters. Add (New sqlparameter (returnvaluestring,
Sqldbtype. Int, 4, parameterdirection. returnvalue,
False, 0, 0, String. Empty, datarowversion. Default, null ));

/// Return the sqlcommand object
Return cmd;
}

/// <Summary>
/// Create a sqldataadapter object
/// </Summary>
/// <Param name = "procname"> name of the stored procedure </param>
/// <Param name = "prams"> parameters required for Stored Procedures </param>
/// <Returns> returns the sqldataadapter object </returns>
Private Static sqldataadapter createsqldataadapter (string procname,
Params sqlparameter [] prams)
{// Create a database connection
Sqlconnection sqlcon = createsqlconnection ();
/// Open the database connection
If (sqlcon = NULL) return NULL;
If (sqlcon. State = connectionstate. Closed)
{
Sqlcon. open ();
}
/// Set attributes of sqldataadapter
Sqldataadapter da = new sqldataadapter (procname, sqlcon );
Da. selectcommand. commandtype = commandtype. storedprocedure;

/// Add stored procedure parameters
If (prams! = NULL)
{
Foreach (sqlparameter parameter in prams)
{
Da. selectcommand. Parameters. Add (parameter );
}
}

/// Add response parameters
Da. selectcommand. Parameters. Add (New sqlparameter (returnvaluestring,
Sqldbtype. Int, 4, parameterdirection. returnvalue,
False, 0, 0, String. Empty, datarowversion. Default, null ));

/// Return the sqldataadapter object
Return da;
}
/// <Summary>
/// Prevent SQL injection attacks
/// </Summary>
/// <Returns> Returns Integer Data </returns>
Public static int checklogin (string loginname, string loginpwd)
{
Int result;
/// Create a database connection
Sqlconnection con = createsqlconnection ();
Sqlcommand mycommand = new sqlcommand ("select count (*) from [user] Where username = @ loginname and Password = @ loginpwd and roleid = @ roleid", con );
Mycommand. Parameters. Add (New sqlparameter ("@ loginname", sqldbtype. varchar, 50 ));
Mycommand. Parameters ["@ loginname"]. value = loginname;
Mycommand. Parameters. Add (New sqlparameter ("@ loginpwd", sqldbtype. varchar, 225 ));
Mycommand. Parameters ["@ loginpwd"]. value = loginpwd;
Mycommand. Parameters. Add (New sqlparameter ("@ roleid", sqldbtype. INT ));
Mycommand. Parameters ["@ roleid"]. value = 1;
Mycommand. Connection. open ();
Result = (INT) mycommand. executescalar ();
Mycommand. Connection. Close ();
Return result;
}

# Endregion

# Region management connection

/// <Summary>
/// Create a connection.
/// </Summary>
Private Static sqlconnection createsqlconnection ()
{// Obtain the connection string
String constr = (string) cache. getdata (connectionstringnameinwebconfig );

If (string. isnullorempty (constr ))
{
Try
{// If the connection string is null, obtain the connection string from the configuration file
Constr = configurationmanager. connectionstrings [connectionstringnameinwebconfig]. connectionstring;
}
Catch (exception ex)
{
Throw new exception (ex. Message, ex );
}
/// Add the string to the cache
Cache. cachingdata (connectionstringnameinwebconfig, constr );
}
If (! String. isnullorempty (constr ))
{// Create a database connection
Return (New sqlconnection (constr ));
}
Return NULL;
}

# Endregion

/// <Summary>
/// Description: getdataset dataset, which returns the dataset of the data source.
/// Return value: DataSet Dataset
/// Parameter: squerystring SQL string, tablename data table name
/// </Summary>
Public static dataset getdataset (string squerystring, string tablename)
{
/// Create a database connection
Sqlconnection con = createsqlconnection ();
Con. open ();
Sqldataadapter dbadapter = new sqldataadapter (squerystring, con );
Dataset dataset = new dataset ();
Dbadapter. Fill (dataset, tablename );
Con. Close ();
Return dataset;
}

# Region execution Stored Procedure

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procname"> name of the stored procedure </param>
/// <Returns> </returns>
Public static int runproc (string procname)
{// Create a sqlcommand object
Sqlcommand cmd = createsqlcommand (procname, null );
If (cmd = NULL) Return-1;
Try
{// Execute the Stored Procedure
Cmd. executenonquery ();
}
Catch (exception ex)
{
Throw new exception (ex. Message, ex );
}
Finally
{// Close the connection
If (CMD. Connection. State = connectionstate. open)
{
Cmd. Connection. Close ();
}
}

/// Return the execution result
Return (INT) cmd. Parameters [returnvaluestring]. value;
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "prams"> stored procedure parameters </param>
/// <Returns> </returns>
Public static int runproc (string procname, Params sqlparameter [] prams)
{// Create a sqlcommand object
Sqlcommand cmd = createsqlcommand (procname, prams );
If (cmd = NULL) Return-1;
Try
{// Execute the Stored Procedure
Cmd. executenonquery ();
}
Catch (exception ex)
{
Throw new exception (ex. Message, ex );
}
Finally
{// Close the connection
If (CMD. Connection. State = connectionstate. open)
{
Cmd. Connection. Close ();
}
}

/// Return the execution result
Return (INT) cmd. Parameters [returnvaluestring]. value;
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "Dr"> returns the sqldatareader object. </param>
Public static void runproc (string procname, out sqldatareader Dr)
{// Create a sqlcommand object
Sqlcommand cmd = createsqlcommand (procname, null );
If (cmd = NULL)
{
Dr = NULL;
Return;
}
Try
{// Read data
Dr = cmd. executereader (commandbehavior. closeconnection );
}
Catch (exception ex)
{
Dr = NULL;
Throw new exception (ex. Message, ex );
}
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "Dr"> returns the sqldatareader object. </param>
/// <Param name = "prams"> stored procedure parameters </param>
Public static void runproc (string procname, out sqldatareader DR, Params sqlparameter [] prams)
{// Create a sqlcommand object
Sqlcommand cmd = createsqlcommand (procname, prams );
If (cmd = NULL)
{
Dr = NULL;
Return;
}
Try
{// Read data
Dr = cmd. executereader (commandbehavior. closeconnection );
}
Catch (exception ex)
{
Dr = NULL;
Throw new exception (ex. Message, ex );
}
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "ds"> return the DataSet object </param>
Public static void runproc (string procname, ref dataset DS)
{// Initialize DS
If (null = DS) ds = new dataset ();
/// Create sqldataadapter
Sqldataadapter da = createsqldataadapter (procname, null );
If (da = NULL) return;
Try
{// Fill in data
Da. Fill (DS );
}
Catch (exception ex)
{
Throw new exception (ex. Message, ex );
}
Finally
{// Close the connection
If (DA. selectcommand. Connection. State = connectionstate. open)
{
Da. selectcommand. Connection. Close ();
}
}
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "ds"> return the DataSet object </param>
/// <Param name = "prams"> stored procedure parameters </param>
Public static void runproc (string procname, ref dataset ds, Params sqlparameter [] prams)
{// Initialize DS
If (null = DS) ds = new dataset ();
/// Create sqldataadapter
Sqldataadapter da = createsqldataadapter (procname, prams );
If (da = NULL) return;
Try
{// Fill in data
Da. Fill (DS );
}
Catch (exception ex)
{
Throw new exception (ex. Message, ex );
}
Finally
{// Close the connection
If (DA. selectcommand. Connection. State = connectionstate. open)
{
Da. selectcommand. Connection. Close ();
}
}
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "ds"> return the DataSet object </param>
/// <Param name = "start"> Start record </param>
/// <Param name = "Max"> maximum number of records </param>
Public static void runproc (string procname, ref dataset ds,
Int start, int max)
{// Initialize DS
If (null = DS) ds = new dataset ();
/// Create sqldataadapter
Sqldataadapter da = createsqldataadapter (procname, null );
If (da = NULL) return;
Try
{// Fill in data
Da. Fill (DS, start, Max, "tablename ");
}
Catch (exception ex)
{
Throw new exception (ex. Message, ex );
}
Finally
{// Close the connection
If (DA. selectcommand. Connection. State = connectionstate. open)
{
Da. selectcommand. Connection. Close ();
}
}
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "ds"> return the DataSet object </param>
/// <Param name = "start"> Start record </param>
/// <Param name = "Max"> maximum number of records </param>
/// <Param name = "prams"> stored procedure parameters </param>
Public static void runproc (string procname, ref dataset ds,
Int start, int Max, Params sqlparameter [] prams)
{// Initialize DS
If (null = DS) ds = new dataset ();
/// Create sqldataadapter
Sqldataadapter da = createsqldataadapter (procname, prams );
If (da = NULL) return;
Try
{// Fill in data
Da. Fill (DS, start, Max, "tablename ");
}
Catch (exception ex)
{
Throw new exception (ex. Message, ex );
}
Finally
{// Close the connection
If (DA. selectcommand. Connection. State = connectionstate. open)
{
Da. selectcommand. Connection. Close ();
}
}
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Returns> return the Stored Procedure return value </returns>
Public static int runprocscalar (string procname)
{// Create a sqlcommand object
Sqlcommand cmd = createsqlcommand (procname, null );
If (cmd = NULL) Return-1;
Int result;
Try
{
/// Execute the Stored Procedure
Result = (INT) cmd. executescalar ();
}
Catch (exception ex)
{
Throw new exception (ex. Message, ex );
}
Finally
{// Close the connection
If (CMD. Connection. State = connectionstate. open)
{
Cmd. Connection. Close ();
}
}
/// Return the query result
Return result;
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "prams"> parameters required for Stored Procedures </param>
/// <Returns> return the Stored Procedure return value </returns>
Public static int runprocscalar (string procname, Params sqlparameter [] prams)
{// Create a sqlcommand object
Sqlcommand cmd = createsqlcommand (procname, prams );
If (cmd = NULL) Return-1;
Int result;
Try
{
/// Execute the Stored Procedure
Result = (INT) cmd. executescalar ();
}
Catch (exception ex)
{
Throw new exception (ex. Message, ex );
}
Finally
{// Close the connection
If (CMD. Connection. State = connectionstate. open)
{
Cmd. Connection. Close ();
}
}
/// Return the query result
Return result;
}

# Endregion
}

Internal class Cache
{
# Region cache data

/// <Summary>
/// Define the cache area
/// </Summary>
Private Static hashtable cache = hashtable. synchronized (New hashtable ());

/// <Summary>
/// Cache data to the cache. If a cache item already exists, the cached data is overwritten.
/// </Summary>
/// <Param name = "key"> </param>
/// <Param name = "value"> </param>
Public static void cachingdata (string key, object value)
{
Cache [Key] = value;
}

/// <Summary>
/// Obtain the cached data
/// </Summary>
/// <Param name = "key"> </param>
/// <Returns> </returns>
Public static object getdata (string key)
{
Return (object) cache [Key];
}

# Endregion
}
}