Detailed explanation of the usage of executenonquery

Detailed explanation of the usage of executenonquery

#Usage of ExecuteNonQuery for operating database technology in C

#Recently in I just need to catch up on the basic knowledge, and I just caught up on some database operation techniques in C#. Today I learned about ExecuteNonQuery. I looked at the code of my own project maintenance project and online data query. I basically understood the usage of ExecuteNonQuery. Let me make a brief summary. , for future reference.

The ExecuteNonQuery method is mainly used to update data, but of course it can also be used to perform target operations (such as querying the structure of the database or creating database objects such as tables). It is usually used to execute insert, update, and delete statements to change the data in the database without using Dataset. The select statement is not suitable for the ExecuteNonQuery() method.

Recommended "C Video Tutorial"

1. First, let’s take a look at the return value of ExecuteNonQuery:

1. For Update, If insert or Delete statements are executed successfully, the return value is the number of rows affected by the command. If the number of rows affected is 0, the return value is 0;

2. For all other types of statements, the return value is -1;

3. If a rollback occurs, the return value is also -1;

4. We generally judge whether the return value is greater than 0 for update operations. This is no problem. . But for other operations [such as operations on data structures (creating tables, etc.)] if the operation is successful, the return value is -1, but please pay attention. For example, adding a new table to the database, if the creation is successful, -1 will be returned. An exception will occur if it fails. It is best to use Try and Catch statements to catch exceptions when performing such operations.

2. The process of the command object updating the database through the ExecuteNonQuery method is very simple. The steps are as follows:

1. Create a database connection;

2. Create a Command object and specify a SQL Inser, Update, Delete query or stored procedure;

3. Attach the Command object to the database connection;

4. Call the ExecuteNonQuery() method;

5. Close the connection.

3. How to use the code example:

1. First is a very simple class, which provides how to use the command object to communicate with the new database through the ExecuteNonQuery method.

public class ExecuteNonQueryClas
    {
        private static string connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

        //as this method provided static method, set the constructor to priviate to prevent create instance with 'new ExecuteNonQuery()'
        private ExecuteNonQueryClas()
        {

        }

        public static int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, (SqlParameter[])null);
        }

        public static int ExecuteNonQuery(string commandText,SqlParameter[] commandParams)
        {
            //if connectionString is null, then throw exception
            if(connectionString == null || connectionString.Length == 0)
                throw new ArgumentNullException("connectionString");
            
            using(SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(commandText,conn);
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                //check if the commandParams is not null, then attach params to command
                if(commandParams !=null)
                  AttachParameters(cmd,commandParams);

                int recordsAffected = cmd.ExecuteNonQuery();

                return recordsAffected;
            }

        }

        private static void AttachParameters(SqlCommand cmd,SqlParameter[] commandParams)
        {
            if (cmd == null) throw new ArgumentException("command");
            if (commandParams != null)
            {
                foreach (SqlParameter p in commandParams)
                {
                    if (p != null)
                    {
                        //// Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(p);
                    }
                }
            }
        }
    }

2. Call in the main function:

static void Main(string[] args)
        {
            string userName = Console.ReadLine();
            string loginId = "user";
            string sqlString = "update Users set UserName = @name where LoginID= @loginID";
            SqlParameter[] parms ={
                                      new SqlParameter("@name",userName),
                                      new SqlParameter("@loginID",loginId)
                                       
                                  };

            int rlt = ExecuteNonQueryClas.ExecuteNonQuery(sqlString,parms);

            Console.WriteLine(rlt);
            Console.Read();
        }

Okay, the above is the simplest introduction and example of using the ExecuteNonQuery method.

The above is the detailed content of Detailed explanation of the usage of executenonquery. For more information, please follow other related articles on the PHP Chinese website!