The ExecuteNonQuery method of C # and database Access Technology Summary (eight)

ExecuteNonQuery Method

The ExecuteNonQuery method is primarily used to update data.

It is typically used to execute Update,Insert , and Delete statements.

The return value of the method has the following meanings:

For Update,Insert , and Delete statements, the return value is the number of rows affected by the command.

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

The process of updating a database with the Command object through the ExecuteNonQuery method is straightforward and requires the following steps:

(1) Create a database connection.

(2) Create a Command object and specify a SQL Insert,Update,Delete query, or stored procedure.

(3) attaching the Command object to the database connection.

(4) call the ExecuteNonQuery method.

(5) close the connection.

Here's a look at the update, add, and delete operations.

Update Record

The following code shows a simple database update operation that modifies student information with the number "20013150" :

stringupdatequery="Update studentinfo set sname= ' Xiao Li '"+"Where id= ' 200131500145 '";//New ConnectionSqlConnection Conn=NewSqlConnection (); Conn. Connectionstring=connectionString;//new Command ObjectSqlCommand cmd=NewSqlCommand (updatequery,conn);//to invoke the ExecuteNonQuery method of a Command objectConn. Open ();intRecordsaffected=cmd. ExecuteNonQuery (); Conn. Close ();

The code itself is very simple.

But it's important to note that ExecuteNonQuery The return value of the method that returns the number of records affected by the command.

For example, if the command is SQL UPDATE statement, the number of records that are updated is returned.

Similarly, when performing INSERT command returns the number of records inserted into the database.

If you expect the command to update the record, but ExecuteNonQuery method returns the value of the 0 , the update operation failed.

(1) string stitching method

Perhaps the reader has noticed that in the above code updatequery is defined in the program, its operation is fixed in the program, the user cannot interact with the application,

In practice, the command should be processed according to the information entered by the user, for example, when the user enters new user information in the text box and then clicks Update.

The program then updates the data entered by the user to the database.

To achieve this, the command must be constructed based on the data entered by the user. Construction commands can take many forms.

Suppose you have now saved the data entered by the user into a variable:

String username= " Xiao Li ";

String userid= "200131500145";

At this time the userName and user ID variables are saved with the student name and number, and the name can be constructed by stitching the command string as follows:

String updatequery= "Update student Set sname='" +username+ "' "+"Where id='" +user Id+ "' "

This concatenation of strings constructs commands in the most straightforward and straightforward way, but is also the least secure .

You can use parameterization to achieve the same functionality.

(2) parametric method

The SQL server.net data provider and OLE db.net data provider differ greatly when specifying parameters, as described below.

① specifying parameters in a SQL Server. NET Data Provider

The SQL Server. NET Data provider supports the specified parameters.

When the command text specifies a specific command, it must indicate which part is set at run time, that is, which part of the parameter must be indicated.

Those mutable parts are parameters, and they must all have an @ prefix.

Update Student Set [email protected] where Id=[email protected]

In this command,@userName and @userid are arguments, and their values are variable at run time.

When you have parameters in the command, the method that constructs the command object is not the same as before:

String updatequery= "Update student Set [email protected]" + "Where [email protected]";

SqlConnection conn=new SqlConnection (connectionString);

SqlCommand cmd=new SqlCommand (UpdateQuery, conn);

Now you have the Command object that contains the parameter.

All you need to do now is create a Parameter object for each parameter in the command.

The SqlCommand class provides a Parameters Collection property that holds all parameters for the command.

Add a new parameter to the collection by calling the add method of the Parameters collection.

Crud. Parameters.Add ("@userName", userName);

cmd. Parameters.Add ("@userid", UserID);

The first parameter in the Add method above is the parameter name in the command, and the following userName is the variable used for the definition, saving the information entered by the user.

In addition, you can create Parameter objects in other ways, and then add them to the collection.

SqlParameter paramusername= New SqlParameter ("@userName", sqldbtype.nvarchar,50);

Paramusername.value=username;

cmd. Parameters.Add (Paramusername);

The above code

A new SqlParameter object is first created, named paramusername, which corresponds to the @userName parameter in the command, in SqlParameter The constructor specifies the type SqlDbType.NVarCharfor the parameter, and the length is .

The Value property is then specified for paramusername , which means that it will be used in place of the @userNamein the command at run time.

Finally, call The Add method to add a parameter to the command's parameter collection, which is easily ignored by beginners and should be taken extra care.

Commands with parameters are set so that you can execute the ExecuteNonQuery method as usual, which is not any different.

In addition to using SQL statements directly as commands, you can use stored procedures as command content .

In order to execute stored procedures in an ADO application, the name of the stored procedure needs to be assigned to the command text, and the CommandType property of the command is set to the stored procedure.

If the stored procedure returns a value, or if there are parameters, you must also create the parameter and add the created parameter to the Parameters collection of the command.

Add the following stored procedure named updatestudentinfo in the database Student .

The code is as follows:

CREATE PROCEDUREUpdatestudentinfo (@userName nvarchar( -),@userIdnvarchar( -);) as 　　 UpdateStudentinfoSetSName=@userName WhereId=@userIDGO

In order to execute the stored procedure, you must create a Command object and pass the name of the stored procedure to its constructor.

Sqlconnectionconn=new SqlConnection (connectionString);

Sqlcommandcmd=new SqlCommand ("Updatestudentinfo", conn);

The next step is to set the CommandType property of the command to StoredProcedure.

Cmd.commandtype=commandtype.storedprocedure;

The next steps and parameterized commands are the same, set the parameters first and then execute the corresponding commands.

The ExecuteNonQuery method of C # and database Access Technology Summary (eight)