[Go to msdn] C #. NET Common Database Class (ms SQL Server)

Below is a general class for C # To operate the ms SQL Server database. This class can be used to perform any operations on the database, including SQL statements and stored procedures. The following is a detailed implementation process. We hope you can modify and optimize it together. We will introduce how to use it to implement N-LayerProgramDesign.

 

Configure the link parameters of the web. config file

 

<Deleetask>
<! --
Connstr parameter settings. Example:
(1) SQL Server database, such as "Server = Local; database = test; uid = sa; Pwd = ;"
(2) Access database, such as "data \ ex. mdb; user id = 'admin'; Jet oledb: Database Password = 'admin ';"
-->
<Add key = "connstr" value = "Server = 127.0.0.1; database = dbname; uid = sa; Pwd =;"/>
</Appsettings>

 

C #Code

 

Using system;
Using system. Data;
Using system. Data. sqlclient;

Namespace com. lxj. Database
{
/// <Summary>
/// Conndb summary.
/// </Summary>
Public class conndb
{
Protected sqlconnection connection;
Private string connectionstring;

/// <Summary>
/// Default constructor
/// </Summary>
Public conndb ()
{
String connstr;
Connstr = system. configuration. configurationsettings. receivettings ["connstr"]. tostring ();

Connectionstring = connstr;
Connection = new sqlconnection (connectionstring );
}

///

// constructor with parameters
///
/// database connection string
Public conndb (string newconnectionstring)
{< br> connectionstring = newconnectionstring;
connection = new sqlconnection (connectionstring);
}

///

// instantiate the sqlcommand object
///
//
//
//
private sqlcommand buildcommand (string storedprocname, idataparameter [] parameters)
{< br> sqlcommand command = buildquerycommand (storedprocname, parameters);
command. parameters. add (New sqlparameter ("returnvalue", sqldbtype. int, 4, parameterdirection. returnvalue, false, 0, 0, String. empty, datarowversion. default, null);
return command;
}

///

// create a new SQL command object (Stored Procedure)
///
///
//
//
private sqlcommand buildquerycommand (string storedprocname, idataparameter [] parameters)
{< br> sqlcommand command = new sqlcommand (storedprocname, connection);
command. commandtype = commandtype. storedprocedure;
foreach (sqlparameter parameter in parameters)
{< br> command. parameters. add (parameter);
}< br> return command;
}

//

// executes the stored procedure, no return value
///
//
//
Public void executeprocedure (string storedprocname, idataparameter [] parameters)
{< br> connection. open ();
sqlcommand command;
command = buildquerycommand (storedprocname, parameters);
command. executenonquery ();
connection. close ();
}

/// <Summary>
/// Executes the stored procedure and returns the number of rows affected by the execution.
/// </Summary>
/// <Param name = "storedprocname"> </param>
/// <Param name = "Parameters"> </param>
/// <Param name = "rowsaffected"> </param>
/// <Returns> </returns>
Public int runprocedure (string storedprocname, idataparameter [] parameters, out int rowsaffected)
{
Int result;
Connection. open ();
Sqlcommand command = buildcommand (storedprocname, parameters );
Rowsaffected = command. executenonquery ();
Result = (INT) command. Parameters ["returnvalue"]. value;
Connection. Close ();

Return result;
}

/// <Summary>
/// Reload runprocedure to put the result of executing the stored procedure in sqldatareader
/// </Summary>
/// <Param name = "storedprocname"> </param>
/// <Param name = "Parameters"> </param>
/// <Returns> </returns>
Public sqldatareader runprocedure (string storedprocname, idataparameter [] parameters)
{
Sqldatareader returnreader;
Connection. open ();
Sqlcommand command = buildquerycommand (storedprocname, parameters );
Command. commandtype = commandtype. storedprocedure;
Returnreader = command. executereader (commandbehavior. closeconnection );
Return returnreader;
}

/// <Summary>
/// Reload runprocedure to store the results of the stored procedure in dataset and the table tablename is optional.
/// </Summary>
/// <Param name = "storedprocname"> </param>
/// <Param name = "Parameters"> </param>
/// <Param name = "tablename"> </param>
/// <Returns> </returns>
Public dataset runprocedure (string storedprocname, idataparameter [] parameters, Params string [] tablename)
{
Dataset dataset = new dataset ();
Connection. open ();
Sqldataadapter sqlda = new sqldataadapter ();
Sqlda. selectcommand = buildquerycommand (storedprocname, parameters );
String flag;
Flag = "";
For (INT I = 0; I <tablename. length; I ++)
Flag = tablename [I];
If (flag! = "")
Sqlda. Fill (dataset, tablename [0]);
Else
Sqlda. Fill (Dataset );
Connection. Close ();
Return dataset;
}

///

// run the SQL statement, return data to dataset
///
///
//
Public dataset returndataset (string SQL)
{< br> dataset = new dataset ();
connection. open ();
sqldataadapter sqlda = new sqldataadapter (SQL, connection);
sqlda. fill (dataset, "objdataset");
connection. close ();
return dataset;
}< br>

/// <Summary>
/// Execute the SQL statement and return the datareader
/// </Summary>
/// <Param name = "SQL"> </param>
/// <Returns> </returns>
Public sqldatareader returndatareader (string SQL)
{
Connection. open ();
Sqlcommand command = new sqlcommand (SQL, connection );
Sqldatareader datareader = command. executereader ();

Return datareader;
}

/// <Summary>
/// Execute the SQL statement and return the number of records
/// </Summary>
/// <Param name = "SQL"> </param>
/// <Returns> </returns>
Public int returnrecordcount (string SQL)
{
Int recordcount = 0;

Connection. open ();
Sqlcommand command = new sqlcommand (SQL, connection );
Sqldatareader datareader = command. executereader ();

While (datareader. Read ())
{
Recordcount ++;
}
Datareader. Close ();
Connection. Close ();

Return recordcount;
}

/// <Summary>
/// Execute the SQL statement
/// </Summary>
/// <Param name = "SQL"> </param>
/// <Returns> </returns>
Public bool editdatabase (string SQL)
{
Bool successstate = false;

Connection. open ();
Sqltransaction mytrans = connection. begintransaction ();
Sqlcommand command = new sqlcommand (SQL, connection, mytrans );
Try
{
Command. executenonquery ();
Mytrans. Commit ();
Successstate = true;
}
Catch
{
Mytrans. rollback ();
}
Finally
{
Connection. Close ();
}

Return successstate;
}

/// <Summary>
/// Close the database connection
/// </Summary>
Public void close ()
{
Connection. Close ();
}

} // End class
} // End namespace

 

I will introduce the example of using it to implement the n-layer design later. Before that, you need to be familiar with the stored procedure.