I have recently read the efficiency of Wrox, ADO.NET, I feel, I hope to share with you. For the first time, write an article, not good, please understand.
First: ADO.NET concept
ADO.NET includes the following common classes:
· Connection
· Command
DataAdapter
DataReader
· DataSet
1 Connection class
The database Connection class provides connections to the database. The .NET has an OLEDBConnection class and a SqlConnection class, which are directed to different databases. SqlConnection is for SQL Server 7.0 or above.
2 Command class
The database Command class is the package of the database command. This command can be a SQL statement or a stored procedure. This class also has a prefix associated with the specific provider, such as OLEDB or SQL. All Command classes must implement some properties to change the text and types, parameters, timeouts, and transactions of Command. In addition, Comand must implement some methods to perform Command and return information.
3 DataAdapter class
DataAdarpter is generally used in conjunction with DataSet, the DataSet "Connect" to the data source.
Essentially DataAdapter is a container, contains 4 pre-configured Command instances, which is SelectCommand, INSERTCOMMAND, DELETECMMAND, UpdateCommand. These four Command instances will provide operations between DataSet and databases.
4 DataReader class
Use DataReader to implement data high speed in the data source, only forward access. At the same time, DataReader is an object that relies on the connection, which means that you must keep the database connection when you use it.
5 DataSet class
Relatively complex but powerful classes. The introduction will be described later.
Below will now demonstrate basic operations (we use SQL Server Database).
Using system;
Using system.data;
Using system.data.sqlclient;
Namespace Test101
{
///
/// Class1 summary description. ///
Class class1
{
///
/// The main entry point for the application. ///
[Stathread]
Static void main (string [] args)
{
//
// Todo: Add code here to start the application
//
SqlConnection conn = new sqlconnection ("server = joycode; initial catalog = northwind; user ID = sa; password = 87345587;");
Cn.open ();
// The above two lines of code create a new SQLConnection object Conn, and assign the database connection string to its constructor and open the database connection through the OPEN method.
SQLCommand cmd = conn.createCommand (); // Create a SQLCommand by CREATECMMAND method
cmd.comMandText = "SELECT TOP 5 * from customers"; // Setting the command of the CMD object is to read the database aggregate 5 information
cmd.commandtype = commandType.text; // Set the type of CMD is the SQL statement, that is, the default type
/ / Of course we can use cmd.commandtype = commandType.StoredProcedure to specify the command type as the stored procedure.
// The following code uses the CMD's EXECUTEREADER method to create a SQLDataReader object. // Note: DataReader does not have its own constructor, can only create new CMD's ExecuteReader.
SqlDataReader Reader = cmd.executeReader (Commandbehavior.CloseConnection);
String Output;
While (Reader.Read ())
{
Output = String.Format ("Customer {0}: {1} for {2}",
Reader.getstring (0), Reader.getstring (1), Reader.getstring (2)); // Read information and display. Later we will specifically introduce the DataReader class
Console.writeline (OUTPUT);
}
}
}
}
The interface is as follows:
This time we study DataReader in detail. My personal favorite is DataReader, although it is not as strong as Dataset, but in many cases, we must be flexible to read data instead of a lot of memory cache data. For example, each user has a large amount of Dataset, which is likely to cause insufficient server memory. DataReader is especially suitable for reading a large amount of data because it is not caught in memory.
Due to the following discussion, we are designed to database operations, our virtual small items: Personal Address Book (Single User), which means that we need a Contract database, including admin and friend:
Admin: Aname, APASSWORD
Friend: FName, FPhone, Faddress, FID (Profile)
Of course, you can design the Friend table according to your own, such as adding FSEX, etc., this is not detailed here. Corresponding database established files:
if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [admin]') And ObjectProperty (id, n'susertable ') = 1)
DROP TABLE [DBO]. [admin]
Go
if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [Friend]') And ObjectProperty (ID, n'uSERTABLE ') = 1)
DROP TABLE [DBO]. [Friend]
Go
Create Table [DBO]. [Admin] (
[Aname] [varchar] (8) Collate Chinese_prc_ci_as not null,
[APASSWORD] [VARCHAR] (16) Collate Chinese_PRC_CI_AS Not NULL
) On [primary]
Go
CREATE TABLE [DBO]. [Friend] (
[FID] [INT] Identity (1, 1) Not Null,
[Fname] [varchar] (8) collate chinese_prc_ci_as not null,
[FPhone] [varchar] (12) collate Chinese_prc_ci_as null,
[Faddress] [varchar] (100) collate chinese_prc_ci_as null) on [primary]
Go
We must learn about Connection and Command before discussing DataReader, although we have briefly introduced it in front.
All of the following discussions are for SQL Server2000, the namespace used is System.Data.SqlClient. Of course, if you need to use OLDB, it is very convenient (basically replacing SQL to OLEDB).
1, SQLConnection class
Connecting SQL Server First, you must instantiate a SQLConnection object:
SqlConnection Conn = New SqlConnection (Connectionstring);
Cn.open ();
or
SqlConnection conn = new sqlConnection ();
Conn.connectionstring = connectionstring;
Cn.open ();
I prefer 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 kind of opportunity is very small, generally, a small system can only correspond to one database - individual think ^ _ ^).
SqlConnection conn = new sqlConnection ();
Conn.connectionstring = connectionstring1;
Cn.open ();
// do something
CONN, Close ();
Conn.connectionstring = connectionstring2;
Cn.open ();
// do Something else
CONN, Close ();
Note that additional connections can only be used after closing a connection.
If you don't know the status of the Connection object, you can use the State property, its value is Open or Closed, of course, there are other values such as Executing or Broken, but the current version of SQL Server is not supported.
IF (Conn.State == ConnectionsState.Open)
Cn.colse ();
Always mention Connectionstring, generally connect SQL Server strings:
Data Source = ServerName; Initial Catalog = Contract; User ID = SA; Password = YourPassword;
If your SQL Server uses a Windows integrated password, it is:
Data Source = ServerName; Initial Catalog = Contract; Integrated Security = SSPI;
As for other OLEDB or ODBC connection strings to http://www.connectionstrings .com
After connecting the database, you must remember to close the connection, and the connection does not automatically shut down when the Connection object is out of the range.
Open the database connection After we want to execute the command, so we discuss the Command class.
2 SQLCommand class
After establishing a database connection, we must access and operate the database - CRUD: CREATE, Read, Update, Delete.
In order to execute commands we create a Command object, the Comand object requires the Connection object and the CommandText object.
Sqlcommand cmd = new sqlcommand (); cmd.connection = connectionObject; //, such as our previous Conn object
cmd.commandtext = commandText; //, such as a SELECT statement
String commandText = "SELECT * from friend";
Of course, we can also use the stored procedure, which will be discussed later.
Additional methods:
SQLCommand cmd = new sqlcommand (commandtext);
cmd.connection = connectionObject; //, such as our previous Conn object
or
SQLCommand cmd = new sqlcommand (commandtext, connectionObject);
There is also a constructor containing three parameters, we don't discuss it. Designed to transaction.
After having a Command object, we must perform actions, but please remember to open your database connection before execution, otherwise it will have an exception. The SQLCommand object provides the following four execution methods:
l ExecutenonQuery
l executescalar
l executeReader
l executexmlreader
The executenonquery method performs commands that do not return the results, usually use it to perform insert, delete, update operations.
For example, we will operate on the Contract database:
String Sqlins = "Insert [Friend] (FNAME, FPHONE, FADDRESS) VALUES ('Snow Winter",' 027-87345555 ',' Wuhan University Hongbo Apartment ');
Cmd.comMandText = SQLINS;
cmd.executenonquery ();
String SQLUPDATE = "Update [frined] set faddress = 'Wuhan University' 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 yourself, don't copy and paste, this will exist text error (Chinese and English symbolic questions).
ExecuteScalar method Execute the command to return a single value, for example, we must statistically all contacts in the system, you can do this:
SQLCommand cmd = new SQLCOMMAND ("Select Count (*) from Friend", Conn;
Cn.open ();
INT FriendCount = (int) cmd.executescalar ();
MessageBox.show ("Total" FriendCount.toString () "Contact");
Description: The command can return multiple results. At this time, the ExecuteScalar method returns the value of the first line of the first field, and other values are not accessible, which means that if you get the best performance, you should construct the appropriate SELECT query. In order to query the results of the results contain additional data as possible. This method is the preferred method if you are only interested in a single return value. In addition, the method returns Object type data, all guarantees that proper type conversion is your responsibility, otherwise you will get an exception. The executexmlreader method performs a command to return an XML string. It will return a System.xml.xmlReader object that contains the returned SML. I don't know anything about this method, not discussing ^ _ ^.
DataReader class
1. Create a DataReader object
The front mentioned that there is no constructor to create a DataReader object. Usually we use the COMMAND class ExecuteRader method to create a DataReader object:
SQLCommand cmd = new SQLCOMMAND (CommandText, ConnectionObject)
SqlDataReader DR = cmd.executeRead ();
The most common usage of the DataReader class is to retrieve the records returned by the SQL query or stored procedure. It is the only set set of only forward and read-only, that is, when using it, the database connection must keep the open state, and can only stop the information from going through the information, and cannot stop the data.
Note: DataReader uses the underlying connection, the connection is it proprietary, which means that the DataReader cannot use the corresponding connection to go to him, such as performing additional commands. Before using DataReader, you must remember to turn off the reader and connection.
2. Use command behavior to specify DataReader characteristics
We use cmd.executeReader () instantiate the DataReader object, in fact, this method has overloaded version, accepts command line parameters, these parameters should be Commandbehavior enumeration:
SqlDatarader DR = cmd.executeReader (Commandbehavior.CloseConnection);
Above we use Commandbehavior.CloseConnection, the role is to automatically close the corresponding ConnectionObject when DataReader is turned off. This avoids we close the Connection object after we forget to close the DataReader object. Don't tell me that you don't like this parameter, you can guarantee that you remember to close the connection. What do you forget? Or or you use your partner development to develop it? This component does not necessarily let you have permission to close the connection. Also commandbehavior.singlerow can return the result set back to a single line, and commandbehavior.singleresult returns the result of multiple result sets. Of course, there is other values for the Commandbehavior enumeration, see MSDN.
3. Traverse records in DataReader
When the ExecuteReader method breaks the DataReader object, the first record is in front of the position of the current cursor. The READ method of the data reader must be called to move the cursor to the first record, and then the first record is the current record. If the reader contains more than one, the read method returns a BOOL value true. That is to say, the role of the Read method is to move the cursor position to the next record within the allowable range. Isn't it a bit similar to rsmovenext, isn't it? If the current cursor indicates the last record, call the Read method to get FALSE. We often do this:
While (Dr.Reader ())
{
// Do Something with the current record}
Note that if you take a long time for each record, it means that the reader will open for a long time, then the database connection will also maintain long-term open state. It is better to use a non-connected Dataset at this time.
4. Visit the value of the field
There are two ways. The first is the Item property, this property returns the value of the field index or field name corresponding to the field. The second is a GET method, which returns the value of the field index specified by the field. A bit hard to understand, isn't it? Don't tighten, look at OK.
Item property
Each DataReader class defines an ITEM property. For example, now we have a DataReader instance DR. The corresponding SQL statement is SELECT FID, FNAME FROM Friend, then we can use the following method to get the return 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 find that we use Object to define the ID and name, yes, the value returned by the Item property is Object, but you can force the type conversion.
INT ID = (int) DR ["FID"];
String name = (String) DR ["fname"];
Remember: Make sure the validity of type conversion is your own responsibility, otherwise you will get an exception.
Get method
Start us have used a change in the first article. Each DataReader defines a set of GET methods, such as the GetInt32 method, the returned field value as a .NET CLR 32-bit certificate. As with the above example we use the following ways to access the values of the FID and FNAME:
INT ID = Dr.Getint32 (0);
String name = Dr.getstring (1);
Note Although these methods turn data from the data source type to .NET data type, they do not perform other data conversions, such as they do not convert 16-bit integers to 32-bit. So you must use the correct GET method. In addition, the GET method cannot use the field name to access the field, that is, the above is:
INT ID = Dr.Getint32 ("FID"); // Error
String name = tr.getstring ("fname"); // error
Obviously, this shortcomings are fatal in some occasions. When your fields are many, or you will come to see you after a while, you will feel hard to understand! Of course we can use other ways to try to solve this problem. A viable method is to use const:
Const int fileindex = 0;
Const int nameIndex = 1;
INT ID = Dr.Getint32 (FidIndex);
String name = Dr.getstring (NameIndex);
This approach is not very good, another good way:
INT NameIndex = Dr.Getordinal ("FNAME"); / / get the index value corresponding to the FNAME
String name = Dr.getstring (NameIndex);
This seems a bit trouble, but when it is necessary to traverse the large number of results of the reader, this method is effective because the index only needs to be executed.
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 operation of DataReader. As for some advanced supermaphysome of DataReader, we will discuss it later.
Next time we build a project - personal address book (single user version). In this project we will use all the knowledge discussed earlier, and in this project I will try to match the multi-layer architecture standard in this project.
Previous article address:
http://dev.9cbs.net/develop/Article/26/26246.shtm
http://dev.9cbs.net/develop/Article/26/26480.shtm
http://dev.9cbs.net/develop/Article/26/26481.shtm
This time we use an instance to demonstrate the basic application of DataReader, of course, contains the basic operations of Command and Connection. Through this example, we can handle a general database system.
WinForm's personal address book (vs.net2003 sql server2000)
1. Establish a database (previous article has been discussed)
2. Start VS.NET to establish a Contract project and design the following interface:
The list of important control attributes is as follows:
Control type
TEXT
Name
other
Listview
Listview
The display mode is details, fullrowselect is TURE
Button
determine
BTNOK
Default enable for false
Button
Refill
Btnre
Default enable for false
Button
Add contact information
btnadd
Button
Modify the contact information
btnedit
Button
Delete Check Contact Information
BTNDEL
Textbox
TXTNAME
Default enable for false
Textbox
TXTPHONE
Default enable for false
Textbox
TXTADDRESS
Default enable is false, multiline is true
3. Write code:
First we add a LiestView display data event in the form loading event.
Private Void Form1_Load (Object Sender, System.EventArgs E)
{
GetInfo ();
}
In the GetInfo method we must read the information in the database and display it in ListView. At this time, a feasible method is to read the data directly using DataReader and then display. But I don't want to do this here. I write a special class ContractDB to handle data. There are some other methods in this class to implement the operation of the database.
// Class ContractDB, package application all the operational events for the database
Using system;
Using system.data;
Using system.data.sqlclient;
Namespace Contract
{
///
/// ContractDB summary description. ///
Public class contractdb
{
String connStr = "data source = joycode; initial catalog = contract; user ID = sa; password = 87345587"
// String SQL;
// SQLCommand CMD;
Public contracdb () {
//
// TODO: Add constructor logic here
//
}
///
/// Get all contact information ////
///
All contact information
Public SqlDataReader getReader ()
{
String SQL = "SELECT FID, FNAME, FPHONE, FADDRESS from Friend";
SqlConnection conn = new SqlConnection (connStr);
Cn.open ();
Sqlcommand cmd = new SQLCOMMAND (SQL, CONN);
SqlDataReader Reader = cmd.executeReader (Commandbehavior.CloseConnection);
Return Reader;
}
}
}
My purpose is obvious, I will call the GetReader method to get the DataReader I need, so the getInfo method code in Form1 is as follows:
Private void getInfo ()
{
ContractDB CDB = New ContractDB ();
Try
{
SqldataReader Reader = CDB.GetReader ();
THISTVIEW.ITEMS.CLEAR ();
While (Reader.Read ())
{
String [] Subitems = New String []
{
Reader.GetInt32 (0) .tostring (),
Reader.getstring (1),
Reader.getstring (2),
Reader.getstring (3)
}
This.listview.Items.Add (New ListViewItem (Subitems));
}
Reader.Close ();
}
Catch (Exception EX)
{
Messagebox.show (ex.totring ());
}
}
The above code is simple, not explaining, but pay attention to the abnormality of our data access class, then we must handle an exception that may occur here.
Similar to we add additional methods in the data access class: Add contacts, delete contacts, modify information. The entire class file is as follows:
Using system;
Using system.data;
Using system.data.sqlclient;
Namespace Contract
{
///
/// ContractDB summary description. ///
Public class contractdb
{
String connStr = "Data Source = Bineon; Initial Catalog = Contract; user ID = sa; password = 87345587";
// String SQL;
// SQLCommand CMD;
Public contractDb ()
{
//
// TODO: Add constructor logic here
//
}
///
/// Get all contact information ////
///
All contact information
Public SqlDataReader getReader ()
{
String SQL = "SELECT FID, FNAME, FPHONE, FADDRESS from Friend";
SqlConnection conn = new SqlConnection (connStr);
Cn.open ();
Sqlcommand cmd = new SQLCOMMAND (SQL, CONN);
SqlDataReader Reader = cmd.executeReader (Commandbehavior.CloseConnection); Return Reader;
}
///
/// Add a new contact ///
///
Name
///
phone
///
address
Public void addinfo (String _Name, String _phone, string _address)
{
// You can use Command Parameter to construct a SQL statement to get better efficiency and higher security.
String SQL = "INSERT INTO Friend (F6one, Faddress) VALUES ('"
SQL = _Name "','" _phone ", '" _address ")";
SqlConnection conn = new SqlConnection (connStr);
Sqlcommand cmd = new SQLCOMMAND (SQL, CONN);
Cn.open ();
cmd.executenonquery ();
CONN.CLOSE ();
}
///
// / Modify contact information ///
///
Need to modify the contact ID
///
///
///
Public void changeinfo (int _id, string _name, string _phone, string _address)
{
String SQL = "Update Friend Set FName = '" _name ", fPhone ='" _phone "', faddress ='" _address "'";
SqlConnection conn = new SqlConnection (connStr);
Sqlcommand cmd = new SQLCOMMAND (SQL, CONN);
Cn.open ();
cmd.executenonquery ();
CONN.CLOSE ();
}
///
/// Remove the contact information of the specified FID ///
///
Public void deleteInfo (int Infoid)
{
String SQL = "Delete Friend Where Fid =" InfoID;
SqlConnection conn = new SqlConnection (connStr);
Cn.open ();
Sqlcommand cmd = new SQLCOMMAND (SQL, CONN);
cmd.executenonquery ();
CONN.CLOSE ();
}
}
}
Description of the above code: You can write a base class yourself, then the class is inherited above, the base class contains ExenonQueryString and other methods so you don't have to write the code such as the connection. In addition, the SQL statement constructs are recommended to use Command Parameter, such efficiency and security are relatively high.
In addition, the admin table in the database is not used, this table is used to save login information, you can make a login prompt for this program.
Whole project code download: Click to download
This time we will take DataReader, and some of the skills we mentioned are not related to DataReader but it is very useful. First, parameterized query
In the previous article, many netizens mentioned that the code is not standardized. There is no use parameters for SQL. This is indeed a big vulnerability, so I first talk about the parameterized query problem.
Benefits to use parameterized queries: You can prevent SQL injection attacks, improve program execution efficiency.
For SQL Server .NET Data Provider, we can use @ as a parameter of the prefix tag. such as:
Const string connStr = "data source = bineon; user = sa; password = test; initial catalog = northwind;";
String SQL = "SELECT ProductId, ProductName from Products";
SQL = "where categoryid = @categoryid and productid <@categoryid";
SqlConnection conn = new SqlConnection (connStr);
Sqlcommand cmd = new SQLCOMMAND (SQL, CONN);
CMD.Parameters.Add ("@ categoryid", categoryidvalue;
Cmd.Parameters.Add ("@maxproductid", maxproductidvalue;
Cn.open ();
SqlDataReader Reader = cmd.executeReader ();
The above code segment uses two parameters @categoryID and @categoryID when defining the SQL statement. In order to be a specific value during the execution process, we use the Prarameter object, and the parameters are added to the Command object, so that the parameterized query is obtained.
Of course, the Add method used above has other overload versions, such as we can define the Parameter object yourself and add:
Sqlparameter Para = New Sqlparameter ("@ categoryid", categoryidvalue;
cmd.Parameters.Add (Para);
There are also multiple overload versions of the SQLParameter's constructor. You can view the MSDN.
Note: The above parameters must use the @ prefix, but it is more than just a query to use parameters, and other update databases can be used to use parameters.
Above we give the method for the SQL Server parameterized query, we now discuss the specified parameters in OLEDB and ODBC.
In fact, both of these Provider do not support the specified parameters, but can we use (?) As placeholders in the query, and the specified parameter will appear.
SQL = "SELECT ProductID, ProductName from Products";
SQL = "WHERE CATEGORYID =? And ProductID
Next, we should also add the Parameter object to the parameters collection of Command, but this time you pay attention to the order of parameters. You must communicate with you. This is different from the above SQL Server .NET Data Provider.
OLEDBCOMMAND CMD = New OLEDBCOMMAND (SQL, CONN); cmd.parameters.add ("catid", categoryidValue);
CMD.Parameters.Add ("MaxProductID", MaxProductidValue);
If the order of adding parameters above is reversed, then MaxProductIdValue will be assigned to the first one? There is an error. In addition, the above parameter name Catid and MaxProductID don't matter, how you are naming, or even an empty string is also.
Note: The above parameter name does not matter, but the order of the additional parameters is very important and cannot be reversed. The same other update database operation also supports (?) Placeholders.
Second, retrieve data using output parameters
The premise of this method is to use the stored procedure. In fact, for the DBMS that supports the stored procedures, such as SQL Server, all of them should use stored procedures to achieve better execution efficiency.
For example, I now need to find a name and the same contact with the same ID in my contact database (regarding the contact database, please see my last article), what should I do? One way is to use DataReader, but how efficient? In addition, we can choose better ExecuteScalar (), but if I want to know, the contact's name and phone? EXECUTESCALAR () is really better than DataReader, but it can only return a single value, this time it does not meet the requirements. We use stored procedure output parameters to solve this problem. The stored procedure is as follows:
Create Procedure GetInfo
(
@FiD int,
@Fname varchar (8) Output,
@Fphone varchar (12) OUTPUT
)
AS
SELECT @fname = fname, @ fPhone = fPhone
From friend
Where fid = @fid
Go
The keyword OUTPUT above indicates that the parameter is the output parameter.
Then we write code:
SqlConnection conn = new SqlConnection (connStr);
Sqlcommand cmd = conn.createCommand ();
cmd.commandtext = "getInfo";
cmd.commandtype = commandtype.storedProcedure;
The above code creates a CONN object and the CMD object and specifies the execution command of the CMD object as a stored procedure called GetInfo. Next we need to add a parameter object to the parameters collection of the CMD object.
Sqlparameter param = cmd.parameters.add ("@ fid", 16);
Param = cmd.Parameters.add ("@ fname", sqldbtype.varchar, 8);
Param.direction = parameterdirection.output;
Param = cmd.parameters.add ("@ fPhone", SqldbType.varchar, 8);
Param.direction = parameterdirection.output;
We noticed that the above @fname and @fphone parameters were added to specify the parameter as the output direction, which is consistent with the direction of the parameter in the stored procedure. Below we execute the command to obtain the corresponding value.
Cn.open ();
CMD.executenonQuery (); string fname = cmd.parameters ["@ fname"]. value.toString ();
String fPhone = cmd.parameters ["@ fphone"]. value.toString ();
CONN.CLOSE ();
Third, retrieve multiple unrelated result sets
Sometimes we need to have different tables (may also be the same table, but different inquiry content), such as I want to see all the contacts of all contacts, and then view all the addresses. Of course, this requires us to get a SQL statement, and do not need a so-called multiple record sets, but allow me to demonstrate the operation of multiple records in this need.
Use between multiple query statements; The specific code is as follows:
SqlConnection conn = new SqlConnection (connStr);
Sqlcommand cmd = conn.createCommand ();
String SQLA = "SELECT FNAME from Friend";
String SQLB = "SELECT FPHONE from Friend";
Cmd.comMandtext = SQLA ";" SQLB;
Then we can get DataReader as in the past, but because of multiple records, we read the first recordset if you read the next record set? The answer is a nextResult () method. This method is a Bool type. If the next recordset is returned, otherwise it is false.
Cn.open ();
SqlDataReader Reader = cmd.executeReader ();
INT i = 1;
DO
{
Console.Writeline ("Part i.toToString () " The record set is as follows: / n ");
While (Reader.Read ())
{
Console.writeline (Reader [0] .tostring () "/ t");
}
i ;
} while (reader.nextResult ());
Note: Since the DataReader itself is read only by reading, you cannot compare the contents of multiple records, and you cannot move back and forth in multiple records. The result is a plurality of result sets, and the data reader is positioned on the first recordset, and the READ () method of the data reader is different (the method is before the recordset).
In addition, this example simply demonstrates how many unrelated records, so don't pursue the actual significance of examples. In addition, when we need to retrieve relevant record information, multi-table connection queries are also a good choice, that is, using SQL JOIN queries.
Fourth, other related technologies
Search binary data
When you retrieve binary data, we must pass the CommandBehavior.SequentialAlaccess enumeration value to the ExecuteReader method. Alternatively, pay attention to reading information from the recording set must be read in the order of your SQL statement. such as:
SQL = "SELECT PUB_ID, PR_INFO, LOGO from PUB_INFO WHERE PUB_ID = '0763'"
Then when you read, you must first get Pub_ID first, then PR_INFO, then LOGO, if you read Pr_info, you will want to read Pub_Info, then you will get an exception. In addition, it is important to read a better way to read a large amount of binary data is to use the GetBytes method. The following code demonstrates if the LOGO binary data is read. System.io.MemoryStream Stream = new system.io.MemoryStream ();
System.io.binaryWriter Writer = new system.io.binarywriter (stream);
Int buffersize = 1024;
BYTE [] Buffer = New byte [buffersize];
Long offset = 0;
Long BytesRead = 0;
DO
{
BYTESREAD = Reader.getbytes (2, Offset, Buffer, 0, buffersize);
Writer.writer (buffer, 0, (int) bytesread);
Writer.flush ();
OFFSET = bytesRead;
}
While (bytesRead == buffersize);
Where the GetBytes method parameters are more, please refer to MSDN:
MS-help: //ms.msdnqtr.2003feb.2052/cpref/html/frlrfsystemdatasqlclientsqldataReaderclassgetBytestopic.htm
Retrieval mode information
What if we just want to get the pattern information of the database table? DataReader's GetSchematable method can meet our requirements.
String SQL = "SELECT FID, FNAME, FPHONE from Friend";
cmd.comMandText = SQL;
Cn.open ();
SqlDataReader Reader = cmd.executeReader ();
DataTable Schematable = Reader.getsChematable ();
Then we can traverse DataTable to get all mode information
DataRowCollection Schemacolumns = schematable.Rows;
Datacolumncollection schemaprops = schematable.columns;
Foreach (DataRow Schemacolumn in schema)
{
Foreach (Datacolumn Schemacolumnprop in schemaprops)
{
Console.writeline (Schemacolumnprop.columnname "=" Schemacolumn [Schemacolumnprop.columnname] .tostring ());
}
}
But the above efficiency is not high, because we don't need to read the data set, but the program actually made this job. A viable solution is to pass the commandbehavior.schemaonly enumeration to the Executerader method, and another way is to construct a special SQL command, such as:
SQL = "SELECT FID, FNAME, FPHONE from Friend Where Fid = 0"
DataReader's feature is basically introduced, please check MSDN if you need more detailed information. Next time we will discuss the simple function of DataSet.
