ExecuteNonQuery method of C # and database access technology __ Database

The ExecuteNonQuery method is primarily used to update data.

It is commonly used to execute update, INSERT, and DELETE statements.

The method returns the value as follows:

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 procedure for the Command object to update the database through the ExecuteNonQuery method is simple, and the following steps are required:

(1) Create a database connection.

(2) Create a Command object and specify an 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.

Take a look at the update, add, and delete operations in turn.

Update records

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

1 string updatequery= "Update studentinfo set sname= ' Xiao Li '" + "Where id= ' 200131500145 '";
 2 
 3//New Connection
 4 
 5 SqlConnection conn=new SqlConnection ();
 6 
 7 Conn. connectionstring=connectionstring;
 8 
 9//New Command object 
SqlCommand cmd=new SqlCommand (updatequery,conn); 
13//Call the ExecuteNonQuery method of the command
object 
Conn. Open (); 
ExecuteNonQuery int recordsaffected=cmd. (); 
Conn. Close ();

The code itself is very simple.

But note the return value of the ExecuteNonQuery method, which returns the number of records affected by the command.

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

Similarly, the number of records inserted into the database is returned when the Insert command is executed.

If you expect the command to update the record, but the ExecuteNonQuery method returns a value of 0, the update operation fails. (1) string concatenation 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 commands should be processed according to the information entered by the user, for example, the user enters the new user information in the text box, and then clicks the update.

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

To do this, you must construct the command based on the data entered by the user. Construction commands can take many forms.

Suppose that the data entered by the user is now saved to a variable:

String username= "Xiao Li";

String userid= "200131500145";

At this point, the username and user ID variables hold the student's name and number, and the name can be constructed in the form of the following concatenation command string:

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

This concatenation of string construction commands is the most straightforward, but also the least secure.

You can use parameterization to achieve the same functionality. (2) parametric mode

SQL server.net Data Provider and OLE DB. NET data provider is very different when specifying parameters, which are described separately below. ① in SQL Server. NET data provider, specify parameters in the

SQL Server. NET data provider supports the specified parameters.

When the command text specifies a specific command, you must indicate which part is set at run time, that is, which part of the argument you must indicate.

The variable parts, which are parameters, must have a @ prefix.

Update student set Sname= @userName where id= @userid

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

When you have parameters in a command, the method to construct the command object does not differ from the preceding:

String updatequery= "Update student Set sname= @username" + "Where id= @userid";

SqlConnection conn=new SqlConnection (ConnectionString);

SqlCommand cmd=new SqlCommand (UpdateQuery, conn);

Now you have the command object that contains the parameters.

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

The SqlCommand class provides a parameters collection property to hold all parameters for a command.

Adds 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 name of the parameter in the command, and the following username is the variable that is 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 code above

The first new SqlParameter object, named Paramusername, corresponds to the @username parameter in the command. A type of SqlDbType.NVarChar is specified for the parameter in the SqlParameter constructor, with a length of 50.

The Value property is then specified for Paramusername, which means that the value is substituted for the @username in the command at run time.

Finally, call the Add method to add the parameters to the command's parameter collection, which is easy for beginners to ignore, and take extra notice.

Commands with parameters are set so that you can perform the ExecuteNonQuery method as usual, which is no different.

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

To execute a stored procedure in an Ado.net application, the name of the stored procedure needs to be assigned to the command text, while the command's CommandType property is set to the stored procedure.

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

Add the following stored procedure named Updatestudentinfo to the database student.

The code is as follows:

1 CREATE PROCEDURE updatestudentinfo
 2 
 3 (
 4 
 5 @userName nvarchar (),
 6 
 7 @user ID nvarchar (2 0);
 8 
 9)
studentinfo as 
Update 
sname= @userName Where id= @user id
  16 Go 

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

SqlConnection conn=new SqlConnection (ConnectionString);

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

Next, 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 and then execute the corresponding commands.