Database Operations of Self-encapsulated MSSQL

Code

/*
* Author: Niu Yu
* Creation Time: 10:43:14
* Email: 164423073@qq.com
* Description: encapsulation class for addition, query, modification, and deletion operations on the MSSQL database
  */

UsingSystem;
UsingSystem. Data;
UsingSystem. Data. sqlclient;
UsingSystem. Web. configuration;

Namespace Niunan. tg029.hr. Utility
{
Public   Class Mssqlhelper
{
Private Sqlconnection Conn =   Null ;
Private Sqlcommand cmd =   Null ;
Private Sqldatareader SDR =   Null ;

Public Mssqlhelper ()
{
String Connstr = Webconfigurationmanager. connectionstrings [ " Connstr " ]. Tostring ();
Conn =   New Sqlconnection (connstr );
}

///   <Summary> CREATE command object
/// The default is an SQL statement.
///   </Summary>
///   <Param name = "SQL"> SQL statement </Param>
Public   Void Createcommand ( String SQL)
{
Conn. open ();
CMD =   New Sqlcommand (SQL, Conn );
}

///   <Summary> Create a command object for a stored procedure
///  
///   </Summary>
///   <Param name = "procname"> Stored Procedure name </Param>
Public   Void Createstoredcommand ( String Procname)
{
Conn. open ();
CMD =   New Sqlcommand (procname, Conn );
Cmd. commandtype = Commandtype. storedprocedure;
}

///   <Summary> Add Parameters
/// Input parameters by default
///   </Summary>
///   <Param name = "paramname"> Parameter Name </Param>
///   <Param name = "value"> Value </Param>
Public   Void Addparameter ( String Paramname, Object Value)
{
Sqlparameter P =   New Sqlparameter (paramname, value );
Cmd. Parameters. Add (P );
}

///   <Summary> Add output parameters
/// Stored Procedure
///   </Summary>
///   <Param name = "paramname"> Parameter Name </Param>
///   <Param name = "value"> Value </Param>
Public   Void Addoutputparameter ( String Paramname ){
Sqlparameter P =   New Sqlparameter ();
P. parametername = Paramname;
P. Direction = Parameterdirection. output;
P. Size =   20 ;
Cmd. Parameters. Add (P );
}

///   <Summary> Get the output parameter value
///  
///   </Summary>
///   <Param name = "paramname"> Output parameter name </Param>
///   <Returns> </returns>
Public   String Getoutputparameter ( String Paramname ){
Return Cmd. Parameters [paramname]. value. tostring ();
}

///   <Summary> Execute add, delete, and modify SQL statements or stored procedures
///
///   </Summary>
///   <Returns> </returns>
Public   Bool Executenonquery ()
{
Int Res;
Try
{
Res = Cmd. executenonquery ();
If (Res >   0 )
{
Return   True ;
}
}
Catch (Exception ex)
{
Throw Ex;
}
Finally
{
If (Conn. State = Connectionstate. open)
{
Conn. Close ();
}
}
Return   False ;
}

///   <Summary> Execute the query SQL statement or stored procedure
///
///   </Summary>
///   <Returns> </returns>
Public Datatable executequery ()
{
Datatable dt =   New Datatable ();
Using (SDR = Cmd. executereader (commandbehavior. closeconnection ))
{
DT. Load (SDR );
}
Return DT;
}

///   <Summary> Returns the value of the first column of the first row of the query result of an SQL statement or stored procedure.
///  
///   </Summary>
///   <Returns> </returns>
Public   String Executescalar ()
{
String Res =   "" ;
Try
{
Object OBJ = Cmd. executescalar ();
If (OBJ ! =   Null )
{
Res = OBJ. tostring ();
}
}
Catch (Exception ex)
{
Throw Ex;
}
Finally
{
If (Conn. State = Connectionstate. open)
{
Conn. Close ();
}
}
Return Res;
}

}
}

First, write it down for future use !!!