Classic class of database operations by encapsulated ADO. net

Using system;
Using system. collections;
Using system. Collections. Specialized;
Using system. runtime. remoting. messaging;
Using system. Data;
Using system. Data. sqlclient;
Using system. configuration;
Namespace LTP. sqlserverdal
{
/// <Summary>
/// Basic class for ADO. NET database operations.
/// </Summary>
Public abstract class dbmanagersql
{
// Database connection string
Protected static string connectionstring = configurationsettings. etettings ["connectionstring"];
Public dbmanagersql ()
{
//
// Todo: add the constructor logic here
//
}
/// <Summary>
/// Execute the SQL statement and return the number of affected records
/// </Summary>
/// <Param name = "sqlstring"> </param>
/// <Returns> </returns>
Public static int executesql (string sqlstring)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Using (sqlcommand cmd = new sqlcommand (sqlstring, connection ))
{
Try
{
Connection. open ();
Int rows = cmd. executenonquery ();
Return rows;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
}
}
}
/// <Summary>
/// Execute two SQL statements to implement database transactions.
/// </Summary>
/// <Param name = "sqlstring1"> </param>
/// <Param name = "sqlstring2"> </param>
Public static void executesqltran (string sqlstring1, string sqlstring2)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Connection. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = connection;
Sqltransaction Tx = connection. begintransaction ();
Cmd. Transaction = TX;
Try
{
Cmd. commandtext = sqlstring1;
Cmd. executenonquery ();
Cmd. commandtext = sqlstring2;
Cmd. executenonquery ();
TX. Commit ();
}
Catch (system. Data. sqlclient. sqlexception E)
{
TX. rollback ();
Throw new exception (E. Message );
}
Finally
{
Cmd. Dispose ();
Connection. Close ();
}
}
}
/// <Summary>
/// Execute multiple SQL statements to implement database transactions. Each statement is separated.
/// </Summary>
/// <Param name = "sqlstringlist"> </param>
Public static void executesqltran (string sqlstringlist)
{
Using (odbcconnection conn = new odbcconnection (connectionstring ))
{
Conn. open ();
Odbccommand cmd = new odbccommand ();
Cmd. Connection = conn;
Odbctransaction Tx = conn. begintransaction ();
Cmd. Transaction = TX;
Try
{
String [] split = sqlstringlist. Split (New char [] {';'});
Foreach (string strsql in Split)
{
If (strsql. Trim ()! = "")
{
Cmd. commandtext = strsql;
Cmd. executenonquery ();
}
}
TX. Commit ();
}
Catch (system. Data. ODBC. odbcexception E)
{
TX. rollback ();
Throw new exception (E. Message );
}
}
}
/// <Summary>
/// Execute the SQL statement with a stored procedure parameter.
/// </Summary>
/// <Param name = "sqlstring"> </param>
/// <Param name = "content"> </param>
/// <Returns> </returns>
Public static int executesql (string sqlstring, string content)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Sqlcommand cmd = new sqlcommand (sqlstring, connection );
System. Data. sqlclient. sqlparameter myparameter = new system. Data. sqlclient. sqlparameter ("@ content", sqldbtype. ntext );
Myparameter. value = content;
Cmd. Parameters. Add (myparameter );
Try
{
Connection. open ();
Int rows = cmd. executenonquery ();
Return rows;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Cmd. Dispose ();
Connection. Close ();
}
}
}
/// <Summary>
/// Insert an image format field into the database
/// </Summary>
/// <Param name = "strsql"> </param>
/// <Param name = "FS"> </param>
/// <Returns> </returns>
Public static int executesqlinsertimg (string strsql, byte [] FS)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Sqlcommand cmd = new sqlcommand (strsql, connection );
System. Data. sqlclient. sqlparameter myparameter = new system. Data. sqlclient. sqlparameter ("@ FS", sqldbtype. Image );
Myparameter. value = FS;
Cmd. Parameters. Add (myparameter );
Try
{
Connection. open ();
Int rows = cmd. executenonquery ();
Return rows;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Cmd. Dispose ();
Connection. Close ();
}

}
}
/// <Summary>
/// Execute a query result statement and return the query result (integer ).
/// </Summary>
/// <Param name = "strsql"> </param>
/// <Returns> </returns>
Public static int getcount (string strsql)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Sqlcommand cmd = new sqlcommand (strsql, connection );
Try
{
Connection. open ();
Sqldatareader result = cmd. executereader ();
Int I = 0;
While (result. Read ())
{
I = result. getint32 (0 );
}
Result. Close ();
Return I;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Cmd. Dispose ();
Connection. Close ();
}
}
}
/// <Summary>
/// Execute a query result statement and return the query result (object ).
/// </Summary>
/// <Param name = "sqlstring"> </param>
/// <Returns> </returns>
Public static object getsingle (string sqlstring)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Sqlcommand cmd = new sqlcommand (sqlstring, connection );
Try
{
Connection. open ();
Object OBJ = cmd. executescalar ();
If (object. Equals (OBJ, null) | (object. Equals (OBJ, system. dbnull. Value )))
{
Return NULL;
}
Else
{
Return OBJ;
}
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Cmd. Dispose ();
Connection. Close ();
}
}
}
/// <Summary>
/// Execute the query statement and return sqldatareader
/// </Summary>
/// <Param name = "strsql"> </param>
/// <Returns> </returns>
Public static sqldatareader executereader (string strsql)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Sqlcommand cmd = new sqlcommand (strsql, connection );
Sqldatareader myreader;
Try
{
Connection. open ();
Myreader = cmd. executereader ();
Return myreader;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
Cmd. Dispose ();
Connection. Close ();
}
}
}
/// <Summary>
/// Execute the query statement and return Dataset
/// </Summary>
/// <Param name = "sqlstring"> </param>
/// <Returns> </returns>
Public static dataset query (string sqlstring)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Dataset DS = new dataset ();
Try
{
Connection. open ();
Sqldataadapter command = new sqldataadapter (sqlstring, connection );
Command. Fill (DS, "ds ");
}
Catch (system. Data. sqlclient. sqlexception ex)
{
Throw new exception (ex. Message );
}
Return Ds;
}

}

# Region Stored Procedure operations

/// <Summary>
/// Run the Stored Procedure
/// </Summary>
/// <Param name = "storedprocname"> </param>
/// <Param name = "Parameters"> </param>
/// <Returns> </returns>
Public static sqldatareader runprocedure (string storedprocname, idataparameter [] parameters)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Sqldatareader returnreader;
Connection. open ();
Sqlcommand command = buildquerycommand (connection, storedprocname, parameters );
Command. commandtype = commandtype. storedprocedure;

Returnreader = command. executereader ();
// Connection. Close ();
Return returnreader;
}
}
Private Static sqlcommand buildquerycommand (sqlconnection connection, string storedprocname, idataparameter [] parameters)
{

Sqlcommand command = new sqlcommand (storedprocname, connection );
Command. commandtype = commandtype. storedprocedure;
Foreach (sqlparameter parameter in parameters)
{
Command. Parameters. Add (parameter );
}
Return command;

}
Public static dataset runprocedure (string storedprocname, idataparameter [] parameters, string tablename)
{
Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Dataset dataset = new dataset ();
Connection. open ();
Sqldataadapter sqlda = new sqldataadapter ();
Sqlda. selectcommand = buildquerycommand (connection, storedprocname, parameters );
Sqlda. Fill (dataset, tablename );
Connection. Close ();

Return dataset;
}
}

# Endregion
}
}