Database Operation class (C #)

Using system;
Using system. Data;
Using system. Data. sqlclient;
Using Microsoft. Win32;
Using system. reflection;
Using system. IO;
Using system. Web;
Using system. Web. sessionstate;
Using system. Web. UI;
Using system. runtime. interopservices;
Using system. text;
Namespace IMs. Class
{
/// <Summary>
/// Summary of linkdatabase.
/// </Summary>
Public class linkdatabase
{
Private string strsql = "";
// Private string connectionstring = "Data Source = persist Security info = false; initial catalog =; Integrated Security = sspi ";
Private sqlconnection myconnection;

Private sqlcommandbuilder sqlcmdbld;
Private dataset DS = new dataset ();
Private sqldataadapter da;

Public String db_conn = "";
Public linkdatabase ()
{
// Todo: add the constructor logic here
Db_conn = "Persist Security info = false; Data Source =; initial catalog =; user id = sa; Password = sa"; // configurationsettings. deleettings ["connstr"];
}
Public linkdatabase (string Str)
{
Try
{
This. db_conn = STR;
}
Catch (exception ex)
{
Throw ex;
}
}
Public bool judgeserver ()
{
This. open ();
If (this. myconnection. State = connectionstate. open)
{
This. myconnection. Close ();
Return true;
}
Else
Return false;
}
Public void open ()
{
// If this sentence is not provided during debugging, the running prompt is displayed: the object is not referenced to the instance.
This. myconnection = new sqlconnection (this. db_conn );
If (this. myconnection. State = connectionstate. open)
{

Return;
}
Else
Try
{
This. myconnection. open ();
}
Catch (system. Data. sqlclient. sqlexception ex)
{
Throw new exception ("" + ex. Message + "");
}
 
}

/// <Summary>
/// Retrieve Database Data Based on Input SQL statements
/// </Summary>
/// <Param name = "tempstrsql"> query SQL statements </param>
/// <Param name = "temptablename"> name of the ing table </param>
/// <Returns> </returns>
Public dataset selectdatabase (string tempstrsql, string temptablename)
{
This. strsql = tempstrsql;
This. myconnection = new sqlconnection (db_conn );
This. da = new sqldataadapter (this. strsql, this. myconnection );
This. Ds. Clear ();
Try
{
This. Da. Fill (DS, temptablename );
}
Catch (exception E)
{
Throw new exception ("" + E. Message + "");
}
Return Ds; // return the dataset filled with data. The data table is named after the string given by temptablename.
}

/// <Summary>
/// Database data update (the object for transmitting dataset and datatable)
/// </Summary>
/// <Param name = "changeddataset"> changed dataset </param>
/// <Param name = "tablename"> name of the ing source table </param>
/// <Returns> returns the updated database table </returns>
Public dataset updatedatabase (Dataset changeddataset, string tablename, string Str)
{
Try
{
This. myconnection = new sqlconnection (db_conn );
This. da = new sqldataadapter (STR, this. myconnection );
This. Da. selectcommand = new sqlcommand (STR, this. myconnection );
This. sqlcmdbld = new sqlcommandbuilder (DA );
This. Da. Update (changeddataset, tablename );
Return changeddataset; // return the updated database table.
}
Catch (exception ex)
{
Throw new exception ("" + ex. Message + "");
}
}
//// // Directly operate on the database (this class is not created)) //////////////////////////////////////// /////////////

/// <Summary>
/// Retrieve database data (passing strings and directly operating on the database)
/// </Summary>
/// <Param name = "tempstrsql"> query SQL statements </param>
/// <Returns> the query result is stored in a able. </returns>
Public datatable selectdatabase (string tempstrsql)
{
This. myconnection = new sqlconnection (db_conn );
Dataset tempdataset = new dataset ();
This. da = new sqldataadapter (tempstrsql, this. myconnection );
This. Da. Fill (tempdataset );
Return tempdataset. Tables [0];
}

/// <Summary>
/// Update database data (pass strings and directly operate on the database)
/// </Summary>
/// <Param name = "tempstrsql"> query SQL statements </param>
/// <Returns> return the number of affected rows in the database </returns>
Public int updatedatabase (string tempstrsql)
{
This. myconnection = new sqlconnection (db_conn );
// Before using command, you must first open the connection and then close the connection, while dataadapter will automatically open and close the connection.
Myconnection. open ();
Sqlcommand tempsqlcommand = new sqlcommand (tempstrsql, this. myconnection );
Int intnumber = tempsqlcommand. executenonquery (); // returns the number of affected rows in the database.
Return intnumber;
}

/// <Summary>
/// Close the database
/// </Summary>
Public void closedatabase ()
{
This. myconnection. Close ();
This. myconnection. Dispose ();
This. Ds. Clear ();
This. Ds. Dispose ();
GC. Collect ();
}
/// Return the first column of the first row of the SQL statement execution result
/// </Summary>
/// <Returns> string </returns>
Public String returnvalue (string SQL)
{
This. myconnection = new sqlconnection (db_conn );
Myconnection. open ();
String result;
Sqldatareader Dr;
Try
{
Sqlcommand cmd = new sqlcommand (SQL, this. myconnection );
Dr = cmd. executereader ();
If (dr. Read ())
{
Result = Dr [0]. tostring ();
Dr. Close ();
}
Else
{
Result = "";
Dr. Close ();
}
}
Catch
{
Throw new exception (SQL );
}
Dispose (this. myconnection );
Return result;
}
/// Run the stored procedure and return dataset.
/// </Summary>
/// <Param name = "procname"> name of the stored procedure. </param>
/// <Param name = "prams"> the stored procedure is added to the parameter group. </param>
/// <Returns> DataSet object. </returns>
Public dataset runproc (string procname, sqlparameter [] prams, dataset DS)
{
This. myconnection = new sqlconnection (db_conn );
Myconnection. open ();
Sqlcommand cmd = new sqlcommand (procname, this. myconnection );
Cmd. commandtype = commandtype. storedprocedure;
If (prams! = NULL)
{
Foreach (sqlparameter parameter in prams)
{
If (parameter! = NULL)
{
Cmd. Parameters. Add (parameter );
}
}
}
Sqldataadapter da = new sqldataadapter (CMD );
Try
{
Da. Fill (DS );
}
Catch (exception ex)
{
Throw ex;
}
Return Ds;
}
/// Return the first column of the SQL statement, column columni,
/// </Summary>
/// <Returns> string </returns>
Public String returnvalue (string SQL, int columni)
{
This. myconnection = new sqlconnection (db_conn );
Myconnection. open ();
String result;
Sqldatareader Dr;
Try
{
Sqlcommand cmd = new sqlcommand (SQL, this. myconnection );
Dr = cmd. executereader ();
}
Catch
{
Throw new exception (SQL );
}
If (dr. Read ())
{
Result = Dr [columni]. tostring ();
}
Else
{
Result = "";
}
Dr. Close ();
Dispose (this. myconnection );
Return result;
}
Public void dispose (sqlconnection conn)
{
If (Conn! = NULL)
{
Conn. Close ();
Conn. Dispose ();
}
GC. Collect ();
}
}
}