Several objects and methods of adding and deleting and changing

recently and small partners in the computer room cooperation, I am responsible for the D layer. When SqlHelper is re-written, it is found that the query operation is using the SqlDataAdapter object, and I remember that there is also a SqlDataReader object that performs query operations on the database. There are also methods for adding and pruning, besides ExecuteNonQuery (), what is the difference between the return value of Integer and Boolean. Personal reconstruction did not study well, now a good summary, welcome you treatise.

First, adding and deleting the method of change

We generally use the SqlCommand command object when we are doing a database pruning operation, which has three methods. Non-ExecuteNonQuery (), ExecuteReader (), ExecuteScalar (). where ExecuteNonQuery (); its return value type is int. Many are used to perform additions, deletions, and modifications to data. Returns the number of rows affected.

Specifically, this method is used to perform any SQL Operations command that does not return a result set from the database, including insert INTO, UPDATE, DELETE statements, stored procedures that do not return a value, and DDL statements such as CREATE table and CreateIndex. The ExecuteNonQuery method is for update, INSERT into, and delete operations statements whose return value is the number of rows affected by the command. For all other types of statements, the return value is-1. If a rollback occurs, the return value is also-1. So if you let it execute a query, the return value is-1, meaningless. I returned a Boolean type when I was refactoring. In fact, when the return value is nonzero, the affected rows in the database are not zero, so the execution of additions and deletions is true. When the return value is zero or negative, it indicates that the operation has not been executed successfully.

Second, the method of inquiry

We know that there are 2 query methods in the SqlCommand object, namely ExecuteReader (), ExecuteScalar (), but we generally use SqlDataAdapter objects when we write SqlHelper. What is the difference between these three people?

ExecuteScalar () Returns a variable of type int. If the SQL statement is a select query, only the first column of the first row in the query result set is returned, and the other rows and columns are ignored. If the SQL statement is not a select query, the return result has no effect. Since you do not know what the SQL statement is (possibly int, possibly char, etc.), the ExecuteScalar () method returns a most basic type of object, which is the base class of all types and can be converted to any type, so it is necessary to cast before using.

ExecuteReader returns a DataReader object that, if called in a SqlCommand object, returns SqlDataReader, if called in a OleDbCommand object, returns OleDbDataReader, You can call DataReader methods and properties to iterate over the result set. The ExecuteReader method exists for the purpose of querying the database as quickly as possible and getting results.

There are SqlDataAdapter objects, the following is mainly about the difference between it and Sqldatareaderr.

Three, the difference between SqlDataReader and SqlDataAdapter

we refactor all with Sqldataadapter+dataset. SqlDataReader, recruit who provoked who, why no one use. This is actually a suitable and inappropriate question, like the regular line in the TV series "You are a good person, but we do not fit." What exactly are they for?

1.SqlDataReader//connection-based, read-only access suitable for smaller data volumes. (Connection mode)

SqlDataAdapter//Based on non-connected, suitable for large data volume, can be modified, and finally return the results of the changes to the database. Requires a bit more resources (disconnected mode)

2.SqlDataAdapter data sets are placed into a dataset after reading the data, and the dataset's data exists in local customer service memory.

3.SqlDataReader returned is a data reader, only one of the reading, operation is not flexible, generally in the read-only time to use.

SqlDataAdapter returns a dataset or table that can manipulate the data in it.

4. Different wording:

Sqldatreader the database must be opened before execution, and a command object must be generated. Again by command. The ExecuteReader () method assigns a value. You must close the join manually after completion.

SqlCommand cmd = Newsqlcommand ("Select * from Stu", conn);

Conn. Open ();

SqlDataReader rdr= cmd. ExecuteReader ();

Conn.close ();

SqlDataAdapter execution, automatically hit the database, and do not use the command of the ExecuteReader method to assign value, after completion of automatic disconnection.

sqldataadapteradptr = new SqlDataAdapter (SQL, conn);

DataSet ds = NewDataSet ();

Adptr. Fill (ds, "Stu");

Iv. Summary

looking back on the red Book a year ago, I simply learned about the 7 objects involved in SQL Server database operations, and now has a new harvest for each encounter. Feel that you know very little, sigh its strong.

Several objects and methods of adding and deleting and changing