Detailed explanation of the use of writing SqlHelper class in C#

This article mainly introduces the use of C# to write the SqlHelper class. The editor thinks it is quite good. Now I will share it with you and give you a reference. Let’s follow the editor and take a look.

Boring weekend, unable to study and code. I wanted to find something to do but didn't know what to do. I suddenly found that I had almost forgotten the SqlHelper I had learned. Then the tiger's body trembled and he thought how could he sink so low. He immediately turned on the computer and touched the keyboard with both hands. I wrote this article as a review during the learning process and to share the knowledge (by the way to kill time -^.^-).

Start the text below

The console program is used as an example here. First we need to configure the connection string. We need to add the following nodes to the app.config file:

<connectionStrings>
   <add name="Sql" connectionString="server=数据库地址;uid=用户名;pwd=密码;database=数据库名"/>
  </connectionStrings>

1. Then we need to create a file named SqlHepler class and then create a method to get the connection string configured in the app.config file.

public static string GetSqlConnectionString()
 {
   return ConfigurationManager.
     ConnectionStrings["Sql"].ConnectionString;
 }

2. Let’s encapsulate the first SqlHepler method, encapsulate an executed sql and return the number of affected rows.

 public static int ExecuteNonQuery(string sqlText,params SqlParameter[] parameters)
{
   using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))　　
   {
     using (SqlCommand cmd=conn.CreateCommand())
     {
       conn.Open();　　//打开数据库
       cmd.CommandText = sqlText;　　//对CommandText进行赋值
       cmd.Parameters.AddRange(parameters);　　//对数据库使用参数进行赋值
       return cmd.ExecuteNonQuery();
     }
   }
}

Parameter description: sqlText: the sql script that needs to be executed, parameters: the required parameter set

This method is mainly used for execution. For delete, update, and insert operations, return the number of rows affected.

3. Continue to encapsulate a query operation and return the value of the first row and first column in the query result

public static object ExecuteScalar(string sqlText, params SqlParameter[] parameters)
 {
  using (SqlConnection conn=new SqlConnection(GetSqlConnectionString()))
  {
   using (SqlCommand cmd=conn.CreateCommand())
   {
     conn.Open();
     cmd.CommandText = sqlText;
     cmd.Parameters.AddRange(parameters);
     return cmd.ExecuteScalar();
   }
  }
}

Parameter description: as above .

The return value of this method is object, so we can use this class when we don’t know what type of data we are querying.

4. Encapsulate a common query method and return a DataTable

public static DataTable ExecuteDataTable(string sqlText, params SqlParameter[] parameters) 
 {
  using (SqlDataAdapter adapter =new SqlDataAdapter(sqlText,GetSqlConnectionString()))
  {
    DataTable dt = new DataTable();
    adapter.SelectCommand.Parameters.AddRange(parameters);
    adapter.Fill(dt);
    return dt;
   }
}

Parameter description: as above.

This method is mainly used for some query data. dt will be filled with the queried data and then the data will be returned.

5. Finally, write and encapsulate a query method, which returns a SqlDataReader type

public static SqlDataReader ExecuteReader(string sqlText, params SqlParameter[] parameters)
{
   //SqlDataReader要求，它读取数据的时候有，它独占它的SqlConnection对象，而且SqlConnection必须是Open状态
   SqlConnection conn = new SqlConnection(GetSqlConnectionString());//不要释放连接，因为后面还需要连接打开状态
   SqlCommand cmd = conn.CreateCommand();
   conn.Open();
   cmd.CommandText = sqlText;
   cmd.Parameters.AddRange(parameters);
   //CommandBehavior.CloseConnection当SqlDataReader释放的时候，顺便把SqlConnection对象也释放掉
   return cmd.ExecuteReader(CommandBehavior.CloseConnection); 
}

Parameter description: Still as above.

The SqlDataReader type object returned by this method needs to always use the SqlConnection object, so it cannot be released. This type of data is read line by line. Reading uses the Read() method of this class. The return value is bool to determine whether the data is empty (that is, whether the last row has been read). This method will automatically read the next record.

As a beginner, this time I just give a brief introduction and review the SqlHepler class.

Attach all codes:

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace UserInfoMgr
{
  class SqlHelper
  {
    /// <summary>
    /// 获取连接字符串
    /// </summary>
    /// <returns>连接字符串</returns>
    public static string GetSqlConnectionString()
    {
      return ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    }

    /// <summary>
    /// 封装一个执行的sql 返回受影响的行数
    /// </summary>
    /// <param name="sqlText">执行的sql脚本</param>
    /// <param name="parameters">参数集合</param>
    /// <returns>受影响的行数</returns>
    public static int ExecuteNonQuery(string sqlText,params SqlParameter[] parameters)
    {
      using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))
      {
        using (SqlCommand cmd=conn.CreateCommand())
        {
          conn.Open();
          cmd.CommandText = sqlText;
          cmd.Parameters.AddRange(parameters);
          return cmd.ExecuteNonQuery();
        }
      }
    }

    /// <summary>
    /// 执行sql，返回查询结果中的第一行第一列的值
    /// </summary>
    /// <param name="sqlText">执行的sql脚本</param>
    /// <param name="parameters">参数集合</param>
    /// <returns>查询结果中的第一行第一列的值</returns>
    public static object ExecuteScalar(string sqlText, params SqlParameter[] parameters)
    {
      using (SqlConnection conn=new SqlConnection(GetSqlConnectionString()))
      {
        using (SqlCommand cmd=conn.CreateCommand())
        {
          conn.Open();
          cmd.CommandText = sqlText;
          cmd.Parameters.AddRange(parameters);
          return cmd.ExecuteScalar();
        }
      }
    }

    /// <summary>
    /// 执行sql 返回一个DataTable
    /// </summary>
    /// <param name="sqlText">执行的sql脚本</param>
    /// <param name="parameters">参数集合</param>
    /// <returns>返回一个DataTable</returns>
    public static DataTable ExecuteDataTable(string sqlText, params SqlParameter[] parameters) 
    {
      using (SqlDataAdapter adapter =new SqlDataAdapter(sqlText,GetSqlConnectionString()))
      {
        DataTable dt = new DataTable();
        adapter.SelectCommand.Parameters.AddRange(parameters);
        adapter.Fill(dt);
        return dt;
      }
    }

    /// <summary>
    /// 执行sql脚本
    /// </summary>
    /// <param name="sqlText">执行的sql脚本</param>
    /// <param name="parameters">参数集合</param>
    /// <returns>返回一个SqlDataReader</returns>
    public static SqlDataReader ExecuteReader(string sqlText, params SqlParameter[] parameters)
    {
      //SqlDataReader要求，它读取数据的时候有，它独占它的SqlConnection对象，而且SqlConnection必须是Open状态
      SqlConnection conn = new SqlConnection(GetSqlConnectionString());//不要释放连接，因为后面还需要连接打开状态
      SqlCommand cmd = conn.CreateCommand();
      conn.Open();
      cmd.CommandText = sqlText;
      cmd.Parameters.AddRange(parameters);
      //CommandBehavior.CloseConnection当SqlDataReader释放的时候，顺便把SqlConnection对象也释放掉
      return cmd.ExecuteReader(CommandBehavior.CloseConnection); 
    }
  }
}

The above is the detailed content of Detailed explanation of the use of writing SqlHelper class in C#. For more information, please follow other related articles on the PHP Chinese website!