ADO.NET Detailed Study

/// 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

{

///