SQL Server Database Access operations General class five ways

The most common types of operations for SQL Server databases are the following five:

1. Non-connected query, get DataTable.

2. Connect query, get DataReader.

3. The query results are only 1 rows and 1 columns, obtaining a single data.

4. The database to be added, deleted, modified operation, data editing.

5. Determine if the database is connected. Update

For the above scenario, a shared SQL Server database accesses the generic class. This universal class can be used in asp.net and C/s systems.

1. Non-connected query, get DataTable.

Returns the result set of a DataTable using the SqlDataAdapter object's fill ().

View Code

The code is as follows

Copy Code

#region executes a query and returns the result set    ///<summary>    ///executes a query and returns the result set    ///</ Summary>    ///<param name= "SQL" > SQL text command to query </param>    /// <returns> Query result set </RETURNS>     public DataTable executedatatable (String sql)      {         return executedatatable (SQL, CommandType.Text, null);    } <summary> Executes a query and returns the result set </summary> <param name= "SQL" > SQL text command to query </param> <param name= "CommandType" > query statement type, stored procedure or SQL text command </param> <returns> Query Result set </returns> Public DataTable executedatatable (String sql, CommandType commandtype) { return executedatatable (SQL, CommandType, NULL); } &lt;summary&gt; Executes a query and returns the result set &lt;/summary&gt; &lt;param name= "SQL" &gt; SQL text command to query &lt;/param&gt; &lt;param name= "CommandType" &gt; query statement type, stored procedure or SQL text command &lt;/param&gt; &lt;param name= "Parameters" &gt;t-sql statements or parameter groups of stored procedures &lt;/param&gt; &lt;returns&gt; Query Result set &lt;/returns&gt; Public DataTable executedatatable (String sql, CommandType commandtype, sqlparameter[] parameters) { Instantiate a DataTable for loading query result sets DataTable data = new DataTable (); using (SqlConnection connection = new SqlConnection (connectionString)) { using (SqlCommand command = new SqlCommand (SQL, connection)) { Specify CommandType Command.commandtype = CommandType; if (Parameters!= null) { foreach (SqlParameter parameter in parameters) { Command. Parameters.Add (parameter); } } Instantiation of SqlDataAdapter SqlDataAdapter adapter = new SqlDataAdapter (command); Populating a DataTable Adapter. Fill (data); } } return data; } #endregion

2. Connect query, get DataReader.

Returns an DataReader object instance using Sqlreader ().

View Code

The code is as follows

Copy Code

#region Returns a DataReader object instance    ///<summary>    ///Returns a DataReader object instance    /// </summary>    ///<param name= "SQL" > SQL text command to query </param>     ///<returns>datareader Object instance </returns>     public SqlDataReader ExecuteReader ( String sql)     {         return ExecuteReader SQL, CommandType.Text, NULL);    }    ///<summary>    ///Returns a DataReader object instance    /// </summary>    ///<param name= "SQL" > SQL text command to query </param>     ///<param name= "CommandType" > type of query statement to execute, stored procedure, or SQL text command </param>    ///< Returns>datareader object Instance </returns>     public SqlDataReader ExecuteReader (String sql, CommandType commandtype)     {         return ExecuteReader (SQL, CommandType, NULL);    }    ///<summary>    ///Returns a DataReader object instance    /// </summary>    ///<param name= "SQL" > SQL text command to query </param>     ///<param name= "CommandType" > type of query statement to execute, stored procedure, or SQL text command </param>    ///< param name= "parameters" >t-sql statement or stored procedure parameter array </param>    ///<returns> DataReader object Instance </returns>     public SqlDataReader ExecuteReader (String sql, CommandType CommandType, sqlparameter[] parameters)     {         SqlConnection connection = new SqlConnection (connectionString);         SqlCommand command = new SqlCommand (sql, connection); if (Parameters!= null) { foreach (SqlParameter parameter in parameters) { Command. Parameters.Add (parameter); } } Connection. Open (); Parameter commandbehavior.closeconnection that closes the connection object while closing the reader object return command. ExecuteReader (commandbehavior.closeconnection); } #endregion

3. The query results are only 1 rows and 1 columns, obtaining a single data.

Using the SqlCommand ExecuteScalar ().

View Code

The code is as follows

Copy Code

#region executes the query results, returning the first column of the first row <summary> Executes the query result, returning the first column of the first row </summary> <param name= "SQL" > SQL text command to query </param> <returns> returns the first column of the first row </returns> public Object ExecuteScalar (String sql) { return executescalar (SQL, CommandType.Text, NULL); } <summary> Executes the query result, returning the first column of the first row </summary> <param name= "SQL" > SQL text command to query </param> <param name= "CommandType" > type of query statement to execute, stored procedure or SQL text command </param> <returns> returns the first column of the first row </returns> public Object ExecuteScalar (String sql, CommandType commandtype) { return executescalar (SQL, CommandType, NULL); } &lt;summary&gt; Executes the query result, returning the first column of the first row &lt;/summary&gt; &lt;param name= "SQL" &gt; SQL text command to query &lt;/param&gt; &lt;param name= "CommandType" &gt; type of query statement to execute, stored procedure or SQL text command &lt;/param&gt; &lt;param name= "parameters" &gt;t-sql statement or stored procedure parameter array &lt;/param&gt; &lt;returns&gt; returns the first column of the first row &lt;/returns&gt; public Object ExecuteScalar (String sql, CommandType commandtype, sqlparameter[] parameters) { object result = NULL; using (SqlConnection connection = new SqlConnection (connectionString)) { using (SqlCommand command = new SqlCommand (SQL, connection)) { Command.commandtype = CommandType;                 if (Parameters!= NULL)                 {                      foreach (SqlParameter parameter in parameters)                      {                          command. Parameters.Add (parameter);                     }                } Connection. Open (); result = command. ExecuteScalar (); } } return result; } #endregion

4. The database to be added, deleted, modified operation, data editing.

Using ExecuteNonQuery ().

View Code

The code is as follows

Copy Code

#region to the database for the additional deletion operation    ///<summary>    ///The database for an incremental deletion operation    ///</ Summary>    ///<param name= "SQL" > SQL text command to execute </param>    /// <returns> return the affected function </returns>     public int ExecuteNonQuery (String sql)      {         return ExecuteNonQuery (SQL, CommandType.Text, null);    } <summary> To add a pruning operation to the database </summary> <param name= "SQL" > SQL text command to execute </param> <param name= "CommandType" > type of query statement to execute, stored procedure or SQL text command </param> <returns> returns the affected function </returns> public int ExecuteNonQuery (String sql, CommandType commandtype) { return ExecuteNonQuery (SQL, CommandType, NULL); } &lt;summary&gt; To add a pruning operation to the database &lt;/summary&gt; &lt;param name= "SQL" &gt; SQL text command to execute &lt;/param&gt; &lt;param name= "CommandType" &gt; type of query statement to execute, stored procedure or SQL text command &lt;/param&gt; &lt;param name= "parameters" &gt;t-sql statement or stored procedure parameter array &lt;/param&gt; &lt;returns&gt; returns the affected function &lt;/returns&gt; public int ExecuteNonQuery (String sql, CommandType commandtype, sqlparameter[] parameters) { int count = 0; using (SqlConnection connection = new SqlConnection (connectionString)) { using (SqlCommand command = new SqlCommand (SQL, connection)) { Command.commandtype = CommandType; if (Parameters!= null) { foreach (SqlParameter parameter in parameters) { Command. Parameters.Add (parameter); } } Connection. Open (); Count = command. ExecuteNonQuery (); } } return count; } #endregion

5. Determine whether to connect with the database (update)

View Code

The code is as follows	Copy Code
#region Determine if the database is connected <summary> Determine if the database is connected </summary> <returns> whether to connect </returns> public bool IsConnected () { SqlConnection connection = new SqlConnection (connectionString); Try { if (connection. State!= ConnectionState.Open) { Connection. Open (); } return true; } Catch { return false; } } #endregion

All code for this class

View Code

The code is as follows

Copy Code

Using System; Using System.Data; Using System.Data.SqlClient; Using System.Configuration; <summary> Sqldbhelper: Common class for manipulating SQL Server databases Author: Li Baohen Date: 2012-03-15 version:1.0 </summary> public class Sqldbhelper { #region Field <summary> Private field </summary> private string connectionString; #endregion #region Properties <summary> Public Property Database Connection string </summary> public string connectiongstring { set {connectionString = value;} } #endregion #region Constructors <summary> Constructors </summary> Public Sqldbhelper () { Modify ConnectionString to a database connection string in a project Connectionstring= ""; } <summary> Constructors </summary> <param name= "strConnectionString" > Database connection string </param> Public Sqldbhelper (String strconnectionstring) { connectionString = strConnectionString; } #endregion #region Determine if the database is connected &lt;summary&gt; Determine if the database is connected &lt;/summary&gt; &lt;returns&gt; whether to connect &lt;/returns&gt; public bool IsConnected () { SqlConnection connection = new SqlConnection (connectionString); Try { if (connection. State!= ConnectionState.Open) { Connection. Open (); } return true; } Catch { return false; } } #endregion #region executes a query and returns the result set    ///<summary>    ///executes a query and returns the result set    ///</ Summary>    ///<param name= "SQL" > SQL text command to query </param>    /// <returns> Query result set </RETURNS>     public DataTable executedatatable (String sql)      {         return executedatatable (SQL, CommandType.Text, null);    } <summary> Executes a query and returns the result set </summary> <param name= "SQL" > SQL text command to query </param> <param name= "CommandType" > query statement type, stored procedure or SQL text command </param> <returns> Query Result set </returns> Public DataTable executedatatable (String sql, CommandType commandtype) { return executedatatable (SQL, CommandType, NULL); } &lt;summary&gt; Executes a query and returns the result set &lt;/summary&gt; &lt;param name= "SQL" &gt; SQL text command to query &lt;/param&gt; &lt;param name= "CommandType" &gt; query statement type, stored procedure or SQL text command &lt;/param&gt; &lt;param name= "Parameters" &gt;t-sql statements or parameter groups of stored procedures &lt;/param&gt; &lt;returns&gt; Query Result set &lt;/returns&gt; Public DataTable executedatatable (String sql, CommandType commandtype, sqlparameter[] parameters) { Instantiate a DataTable for loading query result sets DataTable data = new DataTable (); using (SqlConnection connection = new SqlConnection (connectionString)) { using (SqlCommand command = new SqlCommand (SQL, connection)) { Specify CommandType Command.commandtype = CommandType; if (Parameters!= null) { foreach (SqlParameter parameter in parameters) { Command. Parameters.Add (parameter); } } Instantiation of SqlDataAdapter SqlDataAdapter adapter = new SqlDataAdapter (command); Populating a DataTable Adapter. Fill (data); } } return data; } #endregion #region Returns a DataReader object instance    ///<summary>    ///Returns a DataReader object instance    /// </summary>    ///<param name= "SQL" > SQL text command to query </param>     ///<returns>datareader Object instance </returns>     public SqlDataReader ExecuteReader ( String sql)     {         return ExecuteReader SQL, CommandType.Text, NULL);    }    ///<summary>    ///Returns a DataReader object instance    /// </summary>    ///<param name= "SQL" > SQL text command to query </param>     ///<param name= "CommandType" > type of query statement to execute, stored procedure, or SQL text command </param>    ///< Returns>datareader object Instance </returns>     public SqlDataReader ExecuteReader (String sql, CommandType commandtype)     {         return ExecuteReader (SQL, CommandType, NULL);    }    ///<summary>    ///Returns a DataReader object instance    /// </summary>    ///<param name= "SQL" > SQL text command to query </param>     ///<param name= "CommandType" > type of query statement to execute, stored procedure, or SQL text command </param>    ///< param name= "parameters" >t-sql statement or stored procedure parameter array </param>    ///<returns> DataReader object Instance </returns>     public SqlDataReader ExecuteReader (String sql, CommandType CommandType, sqlparameter[] parameters)     {         SqlConnection connection = new SqlConnection (connectionString);         SqlCommand command = new SqlCommand (sql, connection); if (Parameters!= null) { foreach (SqlParameter parameter in parameters) { Command. Parameters.Add (parameter); } } Connection. Open (); Parameter commandbehavior.closeconnection that closes the connection object while closing the reader object return command. ExecuteReader (commandbehavior.closeconnection); } #endregion #region executes the query results, returning the first column of the first row    ///<summary>    ///Executes the query results, returning the first column of the first row    ///</ Summary>    ///<param name= "SQL" > SQL text command to query </param>    /// <returns> returns the first column of the first row </returns>     public Object ExecuteScalar (String sql)      {         return executescalar (SQL, CommandType.Text, null);    } <summary> Executes the query result, returning the first column of the first row </summary> <param name= "SQL" > SQL text command to query </param> <param name= "CommandType" > type of query statement to execute, stored procedure or SQL text command </param> <returns> returns the first column of the first row </returns> public Object ExecuteScalar (String sql, CommandType commandtype) { return executescalar (SQL, CommandType, NULL); } &lt;summary&gt; Executes the query result, returning the first column of the first row &lt;/summary&gt; &lt;param name= "SQL" &gt; SQL text command to query &lt;/param&gt; &lt;param name= "CommandType" &gt; type of query statement to execute, stored procedure or SQL text command &lt;/param&gt; &lt;param name= "parameters" &gt;t-sql statement or stored procedure parameter array &lt;/param&gt; &lt;returns&gt; returns the first column of the first row &lt;/returns&gt; public Object ExecuteScalar (String sql, CommandType commandtype, sqlparameter[] parameters) { object result = NULL; using (SqlConnection connection = new SqlConnection (connectionString)) { using (SqlCommand command = new SqlCommand (SQL, connection)) { Command.commandtype = CommandType;                 if (Parameters!= NULL)                 {                      foreach (SqlParameter parameter in parameters)                      {                          command. Parameters.Add (parameter);                     }                } Connection. Open (); result = command. ExecuteScalar (); } } return result; } #endregion #region to the database for the additional deletion operation    ///<summary>    ///The database for an incremental deletion operation    ///</ Summary>    ///<param name= "SQL" > SQL text command to execute </param>    /// <returns> return the affected function </returns>     public int ExecuteNonQuery (String sql)      {         return ExecuteNonQuery (SQL, CommandType.Text, null);    } <summary> To add a pruning operation to the database </summary> <param name= "SQL" > SQL text command to execute </param> <param name= "CommandType" > type of query statement to execute, stored procedure or SQL text command </param> <returns> returns the affected function </returns> public int ExecuteNonQuery (String sql, CommandType commandtype) { return ExecuteNonQuery (SQL, CommandType, NULL); } &lt;summary&gt; To add a pruning operation to the database &lt;/summary&gt; &lt;param name= "SQL" &gt; SQL text command to execute &lt;/param&gt; &lt;param name= "CommandType" &gt; type of query statement to execute, stored procedure or SQL text command &lt;/param&gt; &lt;param name= "parameters" &gt;t-sql statement or stored procedure parameter array &lt;/param&gt; &lt;returns&gt; returns the affected function &lt;/returns&gt; public int ExecuteNonQuery (String sql, CommandType commandtype, sqlparameter[] parameters) { int count = 0; using (SqlConnection connection = new SqlConnection (connectionString)) { using (SqlCommand command = new SqlCommand (SQL, connection)) { Command.commandtype = CommandType; if (Parameters!= null) { foreach (SqlParameter parameter in parameters) { Command. Parameters.Add (parameter); } } Connection. Open (); Count = command. ExecuteNonQuery (); } } return count; } #endregion }

Summarize
About the database connection access class is commonly known as the database read, connect, delete, modify, and other conventional operations, nothing but the database performance will be helpful, there is a need for friends to see.