ASP. NET generic Database Audit class for SQLServer, asp. netsqlserver

ASP. NET generic Database Audit class for SQLServer, asp. netsqlserver

This article imitates common classes for database access, and the code is clear and practical, including 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;} // <summa Ry> // execute a query, 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, for example, stored procedures or SQL text commands </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> Returned query result set </returns> public DataTable ExecuteDataTable (string SQL) {return ExecuteDataTable (SQL, CommandType. text, null) ;}/// <summary> /// execute a query, 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, for example, stored procedures or SQL text commands </param> /// <param name = "parameters"> Transact-SQL statements or stored procedure parameter arrays </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 command to the specified Commandtype. // if parameters are input at the same time, add these parameters if (parameters! = Null) {foreach (SqlParameter parameter in parameters) {cmd. parameters. add (parameter) ;}}// instantiate sqldataadapter SqlDataAdapter adapter = new SqlDataAdapter (cmd) by using the sqlcommand instance that contains the query SQL statement; adapter. fill (data); // Fill 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 (strin G 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, for example, 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. the CloseConnection parameter indicates to close the Connection object return cmd associated with the reader object when the reader object is closed. executeReader (CommandBehavior. closeConnection) ;}/// <summary> /// executes a query and returns the first row and column of the result set. Ignore other rows, 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 (sq L, 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, modify, and delete 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 the database /// </Summary> /// <param name = "SQL"> SQL command for Database Operations </param> /// <param name = "commandType"> type of the query statement to be executed, for example, stored procedures or SQL text commands </param> // <returns> </returns> public int ExecuteNonQuery (string SQL, CommandType commandType) {return ExecuteNonQuery (SQL, commandType, null );} /// <summary> /// add, delete, modify, and delete 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 sq L text command </param> /// <param name = "parameters"> parameter array of Transact-SQL statements or stored procedures </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 database currently connected /// </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.
The code written in this way can greatly reduce the complexity of our code and greatly reduce the complexity.

The above is all the content of this article, hoping to help you learn.

Articles you may be interested in:

• Mysql database category
• SQL Database Connector class encapsulated by ASP. NET
• Use nodejs to Access ActiveX objects.
• Guide to enabling external access settings for linux mysql Databases
• C # how to access the PostGreSQL database
• Analysis of C # web access to mysql database-Summary
• How to solve the problem that the SQL Server database becomes inaccessible to a single user
• Summary of how to set remote access permissions for MySQL Databases
• When attaching a database to SQL Server 2005, an error occurs, indicating that the operating system is incorrect. 5 (Access denied). 5120 Solution
• C # Write high-concurrency Database Control Access Code