Add, delete, modify, and query objects and Methods

Add, delete, modify, and query objects and Methods

Recently, I have been working with my friends on data centers. I am responsible for layer D. When I re-write sqlhelper, I found that the query operation uses the SqlDataAdapter object. I remember that another SqlDataReader object is also used to query the database. There are also addition, deletion, and modification methods except ExecuteNonQuery (). There are no other methods. What are the differences between the return value integer and boolean. I did not make a good research on personal restructuring. I would like to summarize it now. Thank you for your patience.

 

 

I. add, delete, and modify Methods

 

We generally use the sqlcommand object when adding, deleting, and modifying databases. There are three methods for this object. Other than ExecuteNonQuery (), ExecuteReader (), and ExecuteScalar (). ExecuteNonQuery (); its return value type is int type. It is used to add, delete, and modify data. Returns the number of affected rows.

 

Specifically, this method is used to execute any SQL operation commands that do not return result sets from the database, including insert into, UPDATE, DELETE statements, stored procedures without returned values, DDL statements such as create table and CREATEINDEX. The ExecuteNonQuery method returns the number of rows affected by the command for the UPDATE, insert into, and DELETE statements. For all other types of statements, the return value is-1. If a rollback occurs, the returned value is-1. Therefore, if you want it to execute a query statement, the returned value is-1, which is meaningless. I personally returned a Boolean type during refactoring. It is actually the same. When the returned value is non-zero, it indicates that the affected rows in the database are not zero, so the add, delete, and modify operation is True. When the returned value is zero or negative, it indicates that the operation was not successful.

 

Ii. Query Method

We know that the sqlcommand object has two query methods: ExecuteReader () and ExecuteScalar (). However, we generally use the SqlDataAdapter object when writing sqlhelper, what are the differences between the three?

 

ExecuteScalar () returns an int variable. If the SQL statement is a Select query, only the first column of the First row in the query result set is returned, while other rows and columns are ignored. If the SQL statement is not a Select query, the returned results are useless. The ExecuteScalar () method returns an Object of the most basic type because it does not know the structure of the SQL statement (which may be int or Char or other types, this type is the base class of all types and can be converted to any type. Therefore, force conversion is required before use.

 

ExecuteReader returns a DataReader object. If it is called in the SqlCommand object, SqlDataReader is returned. If it is called in the OledbCommand object, OledbDataReader is returned. You can call the DataReader method and attribute iteration processing result set. The ExecuteReader method aims to query the database as quickly as possible and obtain the results.

 

There is also a SqlDataAdapter object. The difference between it and SqlDataReaderr is described below.

 

Iii. Differences between SqlDataReader and SqlDataAdapter

 

We use SqlDataAdapter + DataSet for refactoring. What's wrong with SqlDataReader? Why is it not used. This is an inappropriate question, just like the line that often appears in a TV series: "You are a good guy, but we are not ". Which situations are they suitable.

 

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

SqlDataAdapter // Based on Non-connection. It is suitable for large data volumes and can be modified separately. Finally, the modification result is returned to the database. The required resources are larger (disconnected Mode)

2. After the SqlDataAdapter reads data, it puts the DataSet into DataSet. The DataSet data is stored in the memory of the local customer service machine.

3. SqlDataReader returns a data reader, which can only be read one by one. The operation is not flexible and is generally used only when read-only.

SqlDataAdapter returns a dataset or table. You can perform any operation on the data in the dataset.

4. Different writing methods:

Before executing SqlDatReader, you must open the database and generate a command object. The value is assigned by the command. ExecuteReader () method. After the connection is completed, manually close the connection.

SqlCommand cmd = newSqlCommand ("select * from stu", conn );

Conn. Open ();

SqlDataReader rdr = cmd. ExecuteReader ();

Conn. close ();

When the SqlDataAdapter is executed, it automatically hits the database and does not use the ExecuteReader method of Command to assign values. After the execution, the connection is automatically disconnected.

SqlDataAdapteradptr = new SqlDataAdapter (SQL, conn );

DataSet ds = newDataSet ();

Adptr. Fill (ds, "stu ");

 

 

Iv. Summary

Looking back at the redbooks I read a year ago, I simply learned about the seven objects involved in SQL Server database operations, and I have a new harvest each time I encounter them. I feel that I know little about it and lament its strength.