What is the sqlhelper class?

The sqlhelper class refers to a database operation component based on the ".NET Framework". The component contains database operation methods; SqlHelper is used to simplify your repeated writing of those database connections SqlCommand or SqlDataReader, etc.

SqlHelper is a database operation component based on .NET Framework. The component contains database operation methods. SqlHelper is used to simplify your repetitive writing of database connections (SqlConnection), SqlCommand, SqlDataReader, etc. After SqlHelper is encapsulated, you usually only need to pass in some parameters to the method, such as database connection string, SQL parameters, etc., and then you can access the database, which is very convenient.

Recommended course: C# tutorial.

The SQLHelper.cs file contains two classes, namely SqlHelper and SqlHelperParameterCache.

//
public sealed class SqlHelper{}
public sealed class SqlHelperParameterCache{}

These two classes are modified with the sealed modifier in front, indicating that it is a sealed class and cannot be used as a base class and cannot be inherited by other classes.

The SqlHelper class provides a set of static methods that can be used to issue many different types of commands to a SQL Server database.

The SqlHelperParameterCache class provides command parameter caching function, which can be used to improve performance. This class is used internally by many of the Execute methods (especially those overridden methods that just run stored procedures). Data access clients can also use it directly to cache specific sets of parameters for specific commands.

This time we put SqlHelperParameterCache aside and analyze the SqlHelper class.

Method

SqlHelper

There are four private methods, and the AssignParameterValues ​​method has an overload:

AttachParameters: Add parameter array To the specified SqlCommand

AssignParameterValues: Assign values ​​to the SqlParameters (parameters) array

PrepareCommand: Used to initialize the properties of the SqlCommand (command) (such as connection, transaction environment, etc.).

There are thirteen public methods: each method of querying the database uses a large number of overloads, and each method uses about eight overloads.

ExecuteNonQuery

This method is used to execute a command that does not return any rows or values. These commands are typically used to perform database updates, but can also be used to return output parameters from a stored procedure.

ExecuteDataset

This method returns a DataSet object that contains the result set returned by a command.

ExecuteReader

This method is used to return a SqlDataReader object, which contains the result set returned by a certain command.

ExecuteScalar

This method returns a value. This value is always the first column of the first row returned by this command.

ExecuteXmlReader

This method returns the XML fragment of the FOR XML query.

FillDataset

This method fills data into the DataSet.

UpdateDataset

This method is used to execute commands to add, delete, and modify DataSet.

CreateCommand

This method is used to create a SqlCommand.

Analysis

Let’s take the ExecuteNonQuery() method as an example to analyze:

What we see above is ExecuteNonQuery() Of the nine overloaded methods, the first three are for preparing queries, the middle three are for executing queries, and the last three are for SQL transaction queries. To break it down further, 2-1 executes a SQL statement without parameters, while 2-2 executes a SQL statement containing a parameter array (the parameter array can be empty), and 2-3 executes a stored procedure without a return value. . In fact, the 1-1 and 1-3 overloads are to facilitate parameter-less SQL statements, while the 1-3 and 2-3 overloads are just to facilitate calling stored procedures. Their essence is still the same as 1-2 and 2-2. Same. Here are some simple uses for overloaded methods.

Below we will use ExecuteNonQuery to execute SQL statements with parameters. You can use F11 to view the execution process statement by statement, which will help us understand the use of the SQLHelper class.

#region RegisterUser 注册用户
        /// <summary>
        /// 注册用户
        /// </summary>
        /// <param>userinfo实体
        /// <returns>注册成功返回True，失败返回False</returns>
        public bool RegisterUser(JFEntity.UserInfo userinfo)
        {
            string sql = @"INSERT INTO T_User VALUES(@userID,@userName,@userSex,@userPassword,@userLevel)";
            SqlParameter[] sqlparamter = new SqlParameter[] 
            {
                new SqlParameter("@userID", SqlDbType.Int),
                new SqlParameter("@userName", SqlDbType.Char,30),
                new SqlParameter("@userSex", SqlDbType.Char,6),
                new SqlParameter("@userPassword", SqlDbType.Char,16),
                new SqlParameter("@userLevel", SqlDbType.Char,8),
            };
            sqlparamter[0].Value = userinfo.userID;
            sqlparamter[1].Value = userinfo.userName;
            sqlparamter[2].Value = userinfo.userSex;
            sqlparamter[3].Value = userinfo.userPassword;
            sqlparamter[4].Value = userinfo.userLevel;
            int result = SqlHelper.ExecuteNonQuery(SqlHelper.connectionString, CommandType.Text, sql, sqlparamter);
            if (result != 0)
            {
                return true;//注册成功
            }
            else
            {
                return false;//注册失败
            }
        }
        #endregion

The above is the detailed content of What is the sqlhelper class?. For more information, please follow other related articles on the PHP Chinese website!