Using system;
Using system. Web;
Using system. Data;
Using system. Data. sqlclient;
Namespace dataaccess
{
/// <Summary>
/// Summary of dataaccess. Used to access the database
/// Data layer access base class, defining common variables for data layer access, Method
/// </Summary>
Public class data
{
Private sqlconnection conn;
Private system. Data. sqlclient. sqltransaction transaction;
/// <Summary>
/// Constructor
/// </Summary>
Public Data ()
{
If (this. Conn = NULL)
This. Conn = new sqlconnection (dbpath );
}
Private const string dbpath = "**********";
Public String constr // database connection string
{
Get
{
Return dbpath;
}
}
/// <Summary>
/// Determine whether the connection pool is enabled
/// </Summary>
Public bool isopen
{
Get
{
Return this. Conn. State = system. Data. connectionstate. open;
}
}
/// <Summary>
/// Open the database
/// </Summary>
Public void open ()
{
If (this. Conn. State! = System. Data. connectionstate. open)
This. Conn. open ();
}
/// <Summary>
/// Close the database
/// </Summary>
Public void close ()
{
If (this. Conn. State = system. Data. connectionstate. open)
This. Conn. Close ();
}
/// <Summary>
/// Determine whether a record exists
/// </Summary>
/// <Param name = "sqlcommand"> </param>
/// <Returns> </returns>
Public bool datareader (string sqlcommand)
{
System. Data. datatable table = This. querytable (sqlcommand );
Return table. Rows. Count> 0;
}
/// <Summary>
/// Add an update record
/// </Summary>
/// <Param name = "sqlcommand"> </param>
/// <Returns> </returns>
Public int moddata (string sqlcommand)
{
Int COUNT = 0;
Bool open = This. isopen;
If (! Open)
This. open ();
Sqlcommand cmd = new sqlcommand (sqlcommand, this. Conn );
If (transaction! = NULL)
Cmd. Transaction = This. transaction;
Count = cmd. executenonquery ();
If (! Open)
This. Close ();
Return count;
}
/// <Summary>
/// Execute the data query and return a dataset.
/// </Summary>
/// <Param name = "sqlcommand"> query database commands. </Param>
/// <Returns> return the dataset containing data </returns>
Public System. Data. datatable querytable (string sqlcommand)
{
Return querydataset (sqlcommand). Tables [0];
}
/// <Summary>
/// Execute the data query and return a dataset.
/// </Summary>
/// <Param name = "sqlcommand"> query database commands. </Param>
/// <Returns> return the dataset containing data </returns>
Public dataset querydataset (string sqlcommand) // binds a dataset.
{
Bool open = This. isopen;
If (! Open)
This. open ();
Sqlcommand cmd = new sqlcommand (sqlcommand, this. Conn );
Sqldataadapter adapter = new sqldataadapter (CMD );
Dataset dataset = new dataset ();
Adapter. Fill (dataset, "mytable ");
If (! Open)
This. Close ();
Return dataset;
}
/// <Summary>
/// Query, return a datareader reader, object.
/// </Summary>
/// <Param name = "sqlcommand"> query the processed sq command. </Param>
/// <Returns> Returns a sqldatareader reader. </Returns>
Public sqldatareader queryreader (string sqlcommand)
{
Bool open = This. isopen;
If (! Open)
This. open ();
Sqlcommand cmd = new sqlcommand (sqlcommand, this. Conn );
Sqldatareader reader;
Reader = cmd. executereader ();
If (! Open)
This. Close ();
Return reader;
}
/// <Summary>
/// Data Query: reads the value of a single row, that is, the value of column 1st in the first row of the query result.
/// </Summary>
/// <Param name = "sqlcommand"> query the SQL statement. </Param>
/// <Returns> </returns>
Public String queryvalue (string sqlcommand)
{
Try
{
Return this. querytable (sqlcommand). Rows [0] [0]. tostring ();
}
Catch
{
Return "";
}
}
/// <Summary>
/// Data Query: reads the value of a single row, that is, the value of column X in the first row of the query result.
/// </Summary>
/// <Param name = "sqlcommand"> query the SQL statement. </Param>
/// <Param name = "colname"> name of the column to be read </param>
/// <Returns> </returns>
Public String queryvalue (string sqlcommand, string colname)
{
Try
{
Return this. querytable (sqlcommand). Rows [0] [colname]. tostring ();
}
Catch
{
Return "";
}
}
/// <Summary>
/// Data Query: reads the value of a single row, that is, the value of column X in the first row of the query result.
/// </Summary>
/// <Param name = "sqlcommand"> query the SQL statement. </Param>
/// <Param name = "rownumber"> to read the column index </param>
/// <Returns> </returns>
Public String readout (string sqlcommand, int rownumber)
{
Try
{
Return this. querytable (sqlcommand). Rows [0] [rownumber]. tostring ();
}
Catch
{
Return "";
}
}
/// <Summary>
/// Start a database transaction.
/// </Summary>
/// <Exception CREF = "invalidoperationexception"> the data access environment is not enabled or released. </Exception>
/// <Remarks> the transaction to be started must end with the committransaction method. </Remarks>
Public void begintransaction ()
{
If (! Isopen)
Throw new system. invalidoperationexception ("the data connection is not enabled or has been closed. ");
This. Transaction = This. Conn. begintransaction ();
}
/// <Summary>
/// Submit a transaction that has been started using the begintransaction method.
/// </Summary>
/// <Remarks> If the transaction has not been started, it will not produce any effect or throw an exception. </Remarks>
Public void committransaction ()
{
If (this. Transaction = NULL)
Return;
This. transaction. Commit ();
This. Transaction = NULL;
}
/// <Summary>
/// Roll back the transaction that has been started through the begintransaction method.
/// </Summary>
/// <Remarks> If the transaction has not been started, it will not produce any effect or throw an exception. </Remarks>
Public void rollbacktransaction ()
{
If (this. Transaction = NULL)
Return;
This. transaction. rollback ();
This. Transaction = NULL;
}
}
}