ExecuteNonQuery usage of Operational database technology in C #

Recently in the basic knowledge, just to fill in C # in the database operation of some technology, today learned executenonquery things, look at their project maintenance project code and online information query, basically understand the use of executenonquery, small to do a summary, for later review.

The ExecuteNonQuery method is primarily used to update data, but can also be used to perform target operations (such as querying the structure of a database or creating database objects such as tables). It is typically used to execute an INSERT, UPDATE, DELETE statement that changes data in a database without using a dataset. The SELECT statement is not suitable for the ExecuteNonQuery () method.

First, let's take a look at the return value of ExecuteNonQuery:

1. For update, INSERT, DELETE statement success is the number of rows affected by the return value for the command, if the number of rows affected is 0, then 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 for the update operation, by judging whether the return value is greater than 0, this is no problem. However, for other operations such as manipulating data structures (building tables, etc.), if the operation succeeds in returning a value of 1, be aware that, for example, adding a new table to the database, creating a successful return of 1, and if the operation fails, an exception occurs, and it is best to use the Try,catch statement to catch the exception.

Second, the command object to update 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.

Third, the code example uses the method:

1. The first is a very simple class that provides a command object for using the ExecuteNonQuery method with the new database.

 Public classExecutenonqueryclas {Private Static stringconnectionString = configurationmanager.connectionstrings["connectionString"].        ConnectionString; //as this method provided static method, set the constructor to Priviate to prevent create instance with ' new Executenon Query () '        PrivateExecutenonqueryclas () {} Public Static intExecuteNonQuery (stringCommandText) {            returnExecuteNonQuery (CommandText, (sqlparameter[])NULL); }         Public Static intExecuteNonQuery (stringcommandtext,sqlparameter[] commandparams) {            //if connectionString is null and then throw exception            if(ConnectionString = =NULL|| Connectionstring.length = =0)                Throw NewArgumentNullException ("connectionString"); using(SqlConnection conn =NewSqlConnection (connectionString)) {SqlCommand cmd=NewSqlCommand (Commandtext,conn); if(Conn. State! =ConnectionState.Open) Conn.                Open (); //Check if the commandparams is not null and then attach params to command                if(Commandparams! =NULL) attachparameters (cmd,commandparams); intrecordsAffected =cmd.                ExecuteNonQuery (); returnrecordsAffected; }        }        Private Static voidattachparameters (SqlCommand cmd,sqlparameter[] commandparams) {if(cmd = =NULL)Throw NewArgumentException ("Command"); if(Commandparams! =NULL)            {                foreach(SqlParameter Pinchcommandparams) {                    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); }                }            }        }    }

View Code

2. Calls in the main function:

Static voidMain (string[] args) {            stringUserName =Console.ReadLine (); stringLoginId ="User"; stringSqlString ="Update Users Set UserName = @name where loginid= @loginID"; Sqlparameter[] Parms={                                      NewSqlParameter ("@name", UserName),NewSqlParameter ("@loginID", LoginId)}; intRLT =executenonqueryclas.executenonquery (sqlstring,parms);            Console.WriteLine (RLT);        Console.read (); }

View Code

Well, that's the simplest introduction and example of using the ExecuteNonQuery method. Then follow how to invoke execute stored procedures in ExecuteNonQuery, and how to pass parameters of the DataRow type to parameters of the SqlCommand type, and so on.

ExecuteNonQuery usage of Operational database technology in C #