ASP. NET generic database category for SQLServer

As a programmer, we should learn new knowledge tomorrow.
Previously, I performed database operations on the same page. In this case, the operation is cumbersome and the Code for database operations needs to be repeatedly written. This not only wastes a lot of time, but also makes the Code look messy. To the three-tier architecture, I understand the benefits of layering and the benefits of object-oriented. Today, when I was reading a book, I saw Mr. Zhou Jinqiao's generic class for database access. I tried to write a similar one, and the code was clear and practical, includes all common operations on the database.
 
 
/// <Summary>
/// Common Database Access class
/// </Summary>
Public class SqlHelper
{
Private string connectionString;
 
/// <Summary>
/// Set the database access string
/// </Summary>
Public string ConnectionString
{

Set {connectionString = value ;}
}
 
/// <Summary>
/// Constructor
/// </Summary>
/// <Param name = "connectionString"> database access string </param>
Public SqlHelper (string connectionString)
{
This. connectionString = connectionString;
}
 
/// <Summary>
/// Execute a query and return the query result
/// </Summary>
/// <Param name = "SQL"> SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as a stored procedure or SQL text command </param>
/// <Returns> return the query result set </returns>
Public DataTable ExecuteDataTable (string SQL, CommandType commandType)
{
Return ExecuteDataTable (SQL, commandType, null );
}
 
/// <Summary>
/// Execute a query and return the result set
/// </Summary>
/// <Param name = "SQL"> SQL text command to be executed </param>
/// <Returns> return the query result set </returns>
Public DataTable ExecuteDataTable (string SQL)
{
Return ExecuteDataTable (SQL, CommandType. Text, null );
}
 
 
/// <Summary>
/// Execute a query and return the query result
/// </Summary>
/// <Param name = "SQL"> SQL statement to be executed </param>
/// <Param name = "commandtype"> type of the query statement to be executed, such as stored procedures or SQL text commands </param>
/// <Param name = "parameters"> Transact-SQL statement or stored procedure parameter array </param>
/// <Returns> </returns>
Public DataTable ExecuteDataTable (string SQL, CommandType commandtype, SqlParameter [] parameters)
{
DataTable data = new DataTable (); // instantiate the datatable, used to load the query result set
Using (SqlConnection con = new SqlConnection (connectionString ))
{
Using (SqlCommand cmd = new SqlCommand (SQL, con ))
{
Cmd. CommandType = commandtype; // set the commandType of the command to the specified Commandtype.
// If both parameters are input, add these parameters
If (parameters! = Null)
{
Foreach (SqlParameter parameter in parameters)
{
Cmd. Parameters. Add (parameter );
}
}
 
// Instantiate the sqldataadapter by using the sqlcommand instance that contains the query SQL
SqlDataAdapter adapter = new SqlDataAdapter (cmd );
Adapter. Fill (data); // Fill in the datatable
 
}
}
Return data;
}
 
/// <Summary>
/// Return the instance of a SqlDataReader object
/// </Summary>
/// <Param name = "SQL"> SQL query command to be executed </param>
/// <Returns> </returns>
Public SqlDataReader ExecuteReader (string SQL)
{
Return ExecuteReader (SQL, CommandType. Text, null );
}
 
/// <Summary>
///
/// </Summary>
/// <Param name = "SQL"> SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as stored procedures or SQL text commands </param>
/// <Returns> </returns>
Public SqlDataReader ExecuteReader (string SQL, CommandType commandType)
{
Return ExecuteReader (SQL, commandType, null );
}
 
/// <Summary>
/// Return the instance of a sqldatareader object
/// </Summary>
/// <Param name = "SQL"> </param>
/// <Param name = "commandType"> </param>
/// <Param name = "parameters"> </param>
/// <Returns> </returns>
Public SqlDataReader ExecuteReader (string SQL, CommandType commandType, SqlParameter [] parameters)
{
SqlConnection con = new SqlConnection (connectionString );
SqlCommand cmd = new SqlCommand (SQL, con );
 
If (parameters! = Null)
{
Foreach (SqlParameter parameter in parameters)
{
Cmd. Parameters. Add (parameters );
}
}
Con. Open ();
// CommandBehavior. CloseConnection indicates that the Connection object associated with the reader object is closed when the reader object is closed.
Return cmd. ExecuteReader (CommandBehavior. CloseConnection );
}
 
/// <Summary>
/// Execute a query and return the first column of the first row of the result set. Ignore other rows and other columns
/// </Summary>
/// <Param name = "SQL"> SQL command to be executed </param>
/// <Returns> </returns>
Public Object ExecuteScalar (string SQL)
{
Return ExecuteScalar (SQL, CommandType. Text, null );
}
 
/// <Summary>
///
/// </Summary>
/// <Param name = "SQL"> </param>
/// <Param name = "commandType"> </param>
/// <Returns> </returns>
Public Object ExecuteScalar (string SQL, CommandType commandType)
{
Return ExecuteScalar (SQL, commandType, null );
}
 
 
/// <Summary>
///
/// </Summary>
/// <Param name = "SQL"> </param>
/// <Param name = "commandType"> parameter type </param>
/// <Param name = "parameters"> </param>
/// <Returns> </returns>
Public Object ExecuteScalar (string SQL, CommandType commandType, SqlParameter [] parameters)
{
Object result = null;
SqlConnection con = new SqlConnection (connectionString );
SqlCommand cmd = new SqlCommand (SQL, con );
Cmd. CommandType = commandType;
If (parameters! = Null)
{
Foreach (SqlParameter parapmeter in parameters)
{
Cmd. Parameters. Add (parapmeter );
}
}
 
Con. Open ();
Result = cmd. ExecuteScalar ();
Con. Close ();
Return result;
}
 
/// <Summary>
/// Add, delete, and modify a database
/// </Summary>
/// <Param name = "SQL"> SQL command to be executed </param>
/// <Returns> </returns>
Public int ExecuteNonQuery (string SQL)
{
Return ExecuteNonQuery (SQL, CommandType. Text, null );
}
 
/// <Summary>
/// Add, delete, and modify a database
/// </Summary>
/// <Param name = "SQL"> SQL commands for Database Operations </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as stored procedures or SQL text commands </param>
/// <Returns> </returns>
Public int ExecuteNonQuery (string SQL, CommandType commandType)
{
Return ExecuteNonQuery (SQL, commandType, null );
}
 
/// <Summary>
/// Add, delete, and modify a database
/// </Summary>
/// <Param name = "SQL"> SQL statement to be executed </param>
/// <Param name = "commandType"> type of the query statement to be executed, such as stored procedures or SQL text commands </param>
/// <Param name = "parameters"> parameter array of A Transact-SQL statement or stored procedure </param>
/// <Returns> </returns>
Public int ExecuteNonQuery (string SQL, CommandType commandType, SqlParameter [] parameters)
{
Int count = 0;
SqlConnection con = new SqlConnection (connectionString );
SqlCommand cmd = new SqlCommand (SQL, con );
Cmd. CommandType = commandType;
If (parameters! = Null)
{
Foreach (SqlParameter parameter in parameters)
{
Cmd. Parameters. Add (parameter );
}
}
 
Con. Open ();
Count = cmd. ExecuteNonQuery ();
Con. Close ();
Return count;
}
 
/// <Summary>
/// Return the database created by all users in the currently connected database
/// </Summary>
/// <Returns> </returns>
Public DataTable GetTables ()
{
DataTable table = null;
Using (SqlConnection con = new SqlConnection (connectionString ))
{
Con. Open ();
Table = con. GetSchema ("Tables ");
 
}
Return table;
}
}
If we create a general class for database access, when we operate with the database, we only need to instantiate the object first, and then according to our own needs, you can call the corresponding method to complete all operations on the database. This is the benefit of separating the database access layer from the business logic layer.
This code can greatly reduce the complexity of our code. In addition, the complexity is greatly reduced.