C # SQL Server database operations

Http://blog.csdn.net/notrynogain/archive/2007/10/25/1843974.aspx

 

Note: The following is the most commonly used C # operations SQL Server database category in my development over the past two years. It is very useful for beginners. If you have any questions, contact me QQ: 115216552 on the web. configure the connection string in config.

Using system;
Using system. Collections. Generic;
Using system. text;
Using system. Data;
Using system. Data. sqlclient;

Public class sqlclass
{
Protected sqlconnection connection;
Protected string connectionstring;

Public sqlclass ()
{
Connectionstring = system. configuration. configurationsettings. etettings ["connstring"];
Connection = new sqlconnection (connectionstring );
}

Public sqlclass (string connstring)
{
Connectionstring = connstring;
Connection = new sqlconnection (connectionstring );
}

/* Stored Procedure for executing returned results and not returning results */
/// <Summary>
/// Generate the corresponding SQL command object based on the stored procedure name and Parameters
/// </Summary>
/// <Param name = "storedprocname"> stored procedure name </param>
/// <Param name = "Parameters"> stored procedure parameters </param>
/// <Returns> </returns>
Private sqlcommand builderquerycommand (string storedprocname, sqlparameter [] parameters)
{
Sqlcommand command = new sqlcommand ();
Command. Connection = connection;
Command. commandtext = storedprocname. Trim ();
Command. commandtype = commandtype. storedprocedure;
If (parameters! = NULL)
{
Foreach (sqlparameter P in parameters)
{
Command. Parameters. Add (P );
}
}
Return command;
}
/// <Summary>
/// Stored Procedure of returned results
/// </Summary>
/// <Param name = "storedprocname"> stored procedure name </param>
/// <Param name = "Parameters"> parameter value </param>
/// <Returns> </returns>
Public sqldatareader getdatareader (string storedprocname, sqlparameter [] parameters)
{
Sqldatareader reader;
Sqlcommand cmd = builderquerycommand (storedprocname, parameters );
If (connection. State = connectionstate. Closed)
{
Connection. open ();
}
Reader = cmd. executereader (commandbehavior. closeconnection );
Return reader;
}
/// <Summary>
/// Stored procedure without returning results
/// </Summary>
/// <Param name = "storedprocname"> stored procedure name </param>
/// <Param name = "Parameters"> parameter value </param>
/// <Returns> </returns>
Public int geteffect (string storedprocname, sqlparameter [] parameters)
{
Int result = 0;
Try
{
If (connection. State = connectionstate. Closed)
{
Connection. open ();
}
Sqltransaction trans = connection. begintransaction ();
Try
{
Sqlcommand cmd = builderquerycommand (storedprocname, parameters );
Cmd. Transaction = trans;
Result = cmd. executenonquery ();
Trans. Commit ();
Connection. Close ();
Return result;
}
Catch (exception ex)
{
If (trans! = NULL)
{
Trans. rollback ();
Return result;
}
Throw ex;

}
Finally
{
If (trans! = NULL)
{
Trans. Dispose ();
}
Connection. Close ();
}
}
Catch (exception ex1)
{
Return 0;
// Throw new exception (ex1.message );
}
}
/// <Summary>
/// Returns dateset
/// </Summary>
/// <Param name = "procname"> </param>
/// <Param name = "Parameters"> </param>
/// <Param name = "tablename"> </param>
/// <Returns> </returns>
Public dataset getdataset (string procname, sqlparameter [] parameters, string tablename)
{
Try
{

Dataset DS = new dataset ();
If (connection. State = connectionstate. Closed)
{
Connection. open ();
}

Sqldataadapter myda = new sqldataadapter ();
Myda. selectcommand = builderquerycommand (procname, parameters );
Myda. Fill (DS, tablename );
Return Ds;
}
Catch
{
Return NULL;
}
Finally
{
Connection. Close ();
}
}
/// <Summary>
/// Custom page
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "Parameters"> parameter name </param>
/// <Param name = "start"> start page </param>
/// <Param name = "maxrecord"> Number of records </param>
/// <Param name = "tablename"> table name </param>
/// <Returns> </returns>
Public dataset getdataset (string procname, sqlparameter [] parameters, int start, int maxrecord, string tablename)
{
Try
{
Dataset DS = new dataset ();
If (connection. State = connectionstate. Closed)
{
Connection. open ();
}
Connection. open ();
Sqldataadapter myda = new sqldataadapter ();
Myda. selectcommand = builderquerycommand (procname, parameters );
Myda. Fill (DS, start, maxrecord, tablename );
Return Ds;
}
Catch
{
Connection. Close ();
Return NULL;
}
Finally
{
Connection. Close ();
}
}
/// <Summary>
/// Return the object type, such as the first row and first column
/// </Summary>
/// <Param name = "storedprocname"> stored procedure name </param>
/// <Param name = "Parameters"> parameter list </param>
/// <Returns> </returns>
Public object GetObject (string storedprocname, sqlparameter [] parameters)
{
Object result = NULL;

If (connection. State = connectionstate. Closed)
{
Connection. open ();
}
// Sqltransaction trans = connection. begintransaction ();
Try
{
Sqlcommand cmd = builderquerycommand (storedprocname, parameters );
// Cmd. Transaction = trans;
Result = cmd. executescalar ();
// TRANS. Commit ();
Connection. Close ();
Return result;
}
Catch
{
// TRANS. rollback ();
Return result;
}
Finally
{
Connection. Close ();
}
}
/// <Summary>
/// Operate a record by using the ID number, for example, deleting the record of the ID number
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "ID"> value </param>
/// <Param name = "paraname"> Parameter Name: @ ID </param>
/// <Returns> </returns>
Public int runprocbyid (string procname, int ID, string paraname)
{
Try
{
Sqlparameter [] P = {New sqlparameter (paraname, sqldbtype. INT )};
P [0]. value = ID;
Return geteffect (procname, P );
}
Catch
{
Connection. Close ();
Return 0;
}
Finally
{
Connection. Close ();
}
}
/// <Summary>
/// Modify the value of the paradieldname field of the record whose ID number is keyValue to filedvalue
/// </Summary>
/// <Param name = "procname"> Trust the process name </param>
/// <Param name = "keyValue"> id value </param>
/// <Param name = "parakeyname"> Field name corresponding to the ID number </param>
/// <Param name = "parafieldname"> Field name to be modified </param>
/// <Param name = "fieldvalue"> modify the field value </param>
/// <Param name = "length"> length of a field </param>
/// <Returns> </returns>

Public int updatebyid (string procname, int keyValue, string
Parakeyname, string parafieldname, string fieldvalue, int length)
{
Try
{
Sqlparameter [] P = {New sqlparameter (parafieldname, sqldbtype. varchar, length ),
New sqlparameter (parafieldname, sqldbtype. INT)
};
P [0]. value = fieldvalue;
P [1]. value = keyValue;
Return geteffect (procname, P );
}
Catch
{
Connection. Close ();
Return 0;
}
Finally
{
Connection. Close ();
}
}
/// <Summary>
/// Execute the SQL statement to return the dataset
/// </Summary>
/// <Param name = "strsql"> SQL statement </param>
/// <Param name = "tablename"> table name </param>
/// <Returns> </returns>
Public dataset runsql (string strsql, string tablename)
{
Try
{

Dataset DS = new dataset ();
If (connection. State = connectionstate. Closed)
{
Connection. open ();
}
Sqldataadapter myda = new sqldataadapter ();
Myda. selectcommand = new sqlcommand (strsql, connection );
Myda. Fill (DS, tablename );
Return Ds;
}
Catch
{
Return NULL;
}
Finally
{
Connection. Close ();
}
}
/// <Summary>
/// Execute the SQL statement to return the affected number of rows
/// </Summary>
/// <Param name = "strsql"> SQL statement </param>
/// <Param name = "effect"> Number of output rows </param>
/// <Returns> </returns>
Public int runsql (string strsql, out int effect)
{
Effect = 0;
If (connection. State = connectionstate. Closed)
{
Connection. open ();
}
Sqltransaction trans = connection. begintransaction ();
Try
{
Sqlcommand cmd = new sqlcommand (strsql, connection );
Cmd. Transaction = trans;
Effect = cmd. executenonquery ();
Trans. Commit ();
Return effect;
}
Catch
{
Trans. rollback ();
Connection. Close ();
Return effect;
}
Finally
{
Connection. Close ();
}
}
/// <Summary>
/// SQL statement execution returns sqldatareader
/// </Summary>
/// <Param name = "strsql"> SQL statement </param>
/// <Returns> </returns>
Public sqldatareader runsql (string strsql)
{
If (connection. State = connectionstate. Closed)
{
Connection. open ();
}
Try
{
Sqlcommand cmd = new sqlcommand (strsql, connection );
Return cmd. executereader (commandbehavior. closeconnection );
}
Catch
{
Connection. Close ();
Return NULL;
}

}
/// <Summary>
/// Execute the SQL statement to return the object
/// </Summary>
/// <Param name = "strsql"> SQL statement </param>
/// <Returns> </returns>
Public object getobjectbyrunsql (string strsql)
{
If (connection. State = connectionstate. Closed)
{
Connection. open ();
}
Object OBJ = NULL;
Try
{
Sqlcommand cmd = new sqlcommand (strsql, connection );
OBJ = cmd. executescalar ();
Connection. Close ();
Return OBJ;
}
Catch
{
Connection. Close ();
Return NULL;
}
Finally
{
Connection. Close ();
}
}
}