[Posting & Correction] Detailed study of ado.net (2)

Datareader class

This time we will study datareader in detail. My personal favorite is datareader. Although it is not as powerful as dataset, in many cases, we need to read data flexibly instead of caching a large amount of data in the memory. For example, each user on the network caches a large number of dataset, which may lead to insufficient server memory. In addition, datareader is especially suitable for reading large amounts of data because it does not cache data in the memory.
Since the following discussions are all designed for database operations, we virtualize a small project: Personal Address Book (single user), which means we need a contract database, including admin and fridend:
Admin: aname, apassword
Friend: fname, fphone, faddress, FID (primary key)
Before discussing datareader, we must understand connection and command, although we have briefly introduced it before.
All of the following discussions are aimed at SQL Server2000 and use the namespace system. Data. sqlclient. Of course, if you need to use oledb, It is very convenient (basically replace SQL with oledb ).
1. sqlconnection class
To connect to SQL Server, you must first instantiate a sqlconnection object:
Sqlconnection conn = new sqlconnection (connectionstring );
Conn. open ();
Or
Sqlconnection conn = new sqlconnection ();
Conn. connectionstring = connectionstring;
Conn. open ();
I like the former, but when you need to re-use the connection object to connect to another database, the second method is very effective (but this opportunity is rare, generally, a small system corresponds to only one database-I personally think ^_^ ).
Sqlconnection conn = new sqlconnection ();
Conn. connectionstring = connectionstring1;
Conn. open ();
// Do something
Conn, close ();
Conn. connectionstring = connectionstring2;
Conn. open ();
// Do something else
Conn, close ();
Note that only one connection is closed before another connection can be used.
If you do not know the status of the connection object, you can use the state attribute. Its value is open or closed, and of course there are other values such as executing or broken, however, SQL Server and other current versions are not supported.
If (conn. State = connectionstate. open)
Conn. colse ();
Connectionstring has been mentioned above. Generally, the string connecting to SQL Server is:
Data Source = servername; initial catalog = Contract; user id = sa; Password = yourpassword;
If your SQL server uses the Windows integrated password, it is:
Data Source = servername; initial catalog = Contract; Integrated Security = sspi;
For other oledb or ODBC connection strings, you can go to the http://www.connectionstrings.com.
After connecting to the database, remember to close the connection. In ado.net, when the connection object is out of range, the connection will not be automatically closed.
After opening the database connection, we need to execute the command, so let's discuss the command class.
2 sqlcommand class
After establishing a database connection, we need to access and operate the database-Crud: Create, read, update, and delete.
To execute commands, we create a command object. The Comand object must execute the connection object and commandtext object.
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = connectionobject; // For example, the conn object we created earlier
Cmd. commandtext = commandtext; // For example, a SELECT statement
String commandtext = "select * from friend ";
Of course, we can also use stored procedures, which will be discussed later.
Other methods:
Sqlcommand cmd = new sqlcommand (commandtext );
Cmd. Connection = connectionobject; // For example, the conn object we created earlier
Or
Sqlcommand cmd = new sqlcommand (commandtext, connecionobject );
There is also a constructor that contains three parameters, which we will not discuss. Transaction processing is designed.
With the command object, we need to execute the operation, but please remember to open your database connection before execution, otherwise there will be an exception. The sqlcommand object provides the following four execution methods:
? Executenonquery
? Executescalar
? Executereader
? Executexmlreader
Executenonquery is usually used to execute insert, delete, and update operations without returning results.
For example, we operate the contract database:
String sqloud = "insert [Friend] (fname, fphone, faddress) values ('snow and winter cold ', '027-87345555', 'wuhan University Hongbo apartment ');
Cmd. commandtext = sqlins;
Cmd. executenonquery ();
String sqlupdate = "Update [frined] Set faddress = 'wuhan Emy 'Where FID = 1 ";
Cmd. commandtext = sqlupdate;
Cmd. executenonquery ();
String sqldel = "Delete from [Friend] Where FID = 1;
Cmd. commandtext = sqldel;
Cmd. executenonquery ();
Note: If you want to test the above Code, please write it by yourself. Do not copy or paste it. This will cause text errors (Chinese and English symbols ).
Execute the executescalar method to return a single value. For example, to count the number of all contacts in the system, you can:
Sqlcommand cmd = new sqlcommand ("select count (*) from friend", Conn );
Conn. open ();
Int friendcount = (INT) cmd. executescalar ();
MessageBox. Show ("Total" + friendcount. tostring () + "contacts ");
Note: The command can return multiple results. At this time, the executescalar method returns the value of the first field in the first line, and all other values are not accessible. This means that if you obtain the best performance, you should construct an appropriate SELECT query so that the query result set contains as little additional data as possible. If you are only interested in a single return value, this method is the preferred method. In addition, this method returns object-type data. It is your responsibility to ensure proper type conversion. Otherwise, you will get an exception.
Execute the executexmlreader method to return an XML string. It returns a system. xml. xmlreader object containing the returned SML. I have no idea about this method and will not discuss it.

1. Create a datareader object

As mentioned above, no constructor has created a datareader object. Generally, we use the executerader method of the command class to create a datareader object:

Sqlcommand cmd = new sqlcommand (commandtext, connectionobject)

Sqldatareader DR = cmd. executereader ();

The most common usage of the datareader class is to retrieve records returned by SQL queries or stored procedures. It is a result set that connects only forward and read-only. When using it, the database connection must be enabled. In addition, you can only traverse the information from the past to the next, and cannot stop to modify the data.

Note: datareader uses the underlying connection, which is proprietary to datareader. This means that datareader cannot use the corresponding connection to perform other operations, such as executing another command. After using datareader, remember to close the reader and connection.

2. Use the command to specify the datareader features

Previously, we used cmd. executereader () to instantiate the datareader object. In fact, this method has an overloaded version and accepts command line parameters. commandbehavior enumeration should be used for these parameters:

Sqldatarader DR = cmd. executereader (commandbehavior. closeconnection );

In the preceding example, commandbehavior. closeconnection is used to automatically disable connectionobject when datareader is disabled. This prevents us from closing the connection object after we forget to close the datareader object. Don't tell me you don't like this parameter. You can ensure that you remember to close the connection. What if you forget it? Or are you using components developed by your partner for development? This component does not necessarily give you the permission to close the connection. In addition, commandbehavior. singlerow allows the result set to return a single row. commandbehavior. singleresult returns the first result set of multiple result sets. Of course, commandbehavior enumeration has other values. For details, see msdn.

3. Traverse records in datareader

When the executereader method is a datareader object, the current cursor is located before the first record. You must call the read method of the data reader to move the cursor to the first record, and the first record is the current record. If the reader contains more than one record, the read method returns a Boolean value of true. That is to say, the read method is used to move the cursor position within the permitted range to the next record, a bit like Rs. movenext, isn't it? If the current cursor indicates the last record, call the read method to obtain false. We often do this:

While (dr. Reader ())

{

// Do something with the current record

}

Note: If you spend a long time on each record operation, it means that the reader will open for a long time, and the database connection will remain open for a long time. In this case, it may be better to use a non-connected dataset.

4. Access Field Value

There are two methods. The first type is the item attribute, which returns the value of the field corresponding to the field index or field name. The second method is the get method, which returns the value of the field specified by the field index. A little hard to understand, isn't it? It doesn't matter. Let's see the example.

Item attribute

Each datareader class defines an item attribute. For example, if we have a datareader instance DR and the corresponding SQL statement is select FID and fname from friend, we can use the following method to obtain the returned value:

Object ID = Dr ["FID"];

Object Name = Dr ["fname"];

Or:

Object ID = Dr [0];

Object Name = Dr [0];

Note that the index always starts from 0. In addition, you may find that we use the object to define the ID and name. Yes, the value returned by the item attribute is of the object type, but you can force type conversion.

Int id = (INT) Dr ["FID"];

String name = (string) Dr ["fname"];

Remember: it is your responsibility to ensure the validity of the type conversion, otherwise you will get an exception.

Get Method

We have already used the modification method in the first article. Each datareader defines a set of get methods. For example, the getint32 method uses the returned field value as the. net clr 32-bit certificate. As in the example above, we use the following method to access the values of FID and fname:

Int id = dr. getint32 (0 );

String name = dr. getstring (1 );

Note that although these methods convert data from the data source type to. NET data type, they do not perform other data conversions. For example, they do not convert 16-bit Integers to 32-bit integers. Therefore, you must use the correct get method. In addition, the get method cannot use field names to access fields, that is, the above does not:

Int id = dr. getint32 ("FID"); // Error

String name = dr. getstring ("fname"); // Error

Obviously, the above disadvantage is fatal in some cases. When you have many fields, or you will read your code after a while, you will find it hard to understand! Of course, we can use other methods to solve this problem as much as possible. One feasible method is to use const:

Const int fidindex = 0;

Const int nameindex = 1;

Int id = dr. getint32 (fidindex );

String name = dr. getstring (nameindex );

This method is not very good. Another better method is as follows:

Int nameindex = dr. getordinal ("fname"); // obtain the index value corresponding to fname

String name = dr. getstring (nameindex );

This seems a little troublesome, but this method is very effective when you need to traverse a large number of result sets of the reader, because the index only needs to be executed once.

Int fidindex = dr. getordinal ("FID ");

Int nameindex = dr. getordinal ("fname ");

While (dr. Read ())

{

Int id = dr. getint32 (fidindex );

String name = dr. getint32 (nameindex );

}

 

So far, we have discussed the basic operations of datareader. We will discuss some advanced superjobs of datareader later.

Next we will build a project-Personal Address Book (single-user version ). In this project, we will use all the knowledge discussed above, and in this project, I will try my best to make this project conform to the multi-tier architecture standards.