Ado.net detailed Study (ii)--datareader (i)

ADO This time we study DataReader in detail. My personal favorite is DataReader, although it is not as strong as the dataset, but in many cases we need to read the data flexibly rather than a large number of cached data in memory. For example, a large number of datasets are cached by each user on the network, which is likely to cause insufficient memory on the server. In addition, DataReader is especially good at reading large amounts of data because it does not cache data in memory.

Since the following discussion is designed for database operations, we hypothesized a small project: a Personal Address Book (single user), which means that we need a contract database that includes admin and Fridend:

Admin:aname,apassword

Friend:fname,fphone,faddress,fid (primary key)

Of course, you can design a friend table according to your own needs, such as adding fsex and other fields, not in detail here. The corresponding database establishes the file:

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Admin] and OBJECTPROPERTY (ID, N ' isusertable ') = 1)

drop table [dbo]. [Admin]

Go



if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Friend] and OBJECTPROPERTY (ID, N ' isusertable ') = 1)

drop table [dbo]. [Friend]

Go



CREATE TABLE [dbo]. [Admin] (

[Aname] [varchar] (8) COLLATE chinese_prc_ci_as not NULL,

[Apassword] [varchar] () 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] (a) COLLATE chinese_prc_ci_as NULL,

[Faddress] [varchar] (MB) COLLATE chinese_prc_ci_as NULL

) on [PRIMARY]

Go

We have to understand connection and command before we discuss DataReader, although we have briefly introduced them earlier.

All of the following discussions are for SQL Server2000, and the namespaces used are System.Data.SqlClient. Of course, if you need to use OLE DB, that's very handy (basically replacing SQL with OLE DB is OK).

1, SqlConnection class

Connecting SQL Server must first instantiate a SqlConnection object:

SqlConnection Conn = new SqlConnection (ConnectionString);

Conn.Open ();

Or

SqlConnection Conn = new SqlConnection ();

conn.connectionstring = ConnectionString;

Conn.Open ();

I prefer the former, but the second is very effective when you need to reuse the connection object to connect to another database (though this is a rare opportunity, in general, a small system corresponds to only one database-the individual thinks ^_^).

SqlConnection Conn = new SqlConnection ();

conn.connectionstring = ConnectionString1;

Conn.Open ();

Do something

Conn,close ();

conn.connectionstring = ConnectionString2;

Conn.Open ();

Do something else

Conn,close ();

Note You cannot use a different connection until you close a connection.

If you do not know the state of the connection object, you can use the states property, whose value is open or closed, and of course there are other values such as executing or broken, but none of the current versions of SQL Server are supported.

If (conn.state = = ConnectionState.Open)

Conn.colse ();

As mentioned above, ConnectionString, the general connection to SQL Server string is:

Data Source = servername;initial Catalog =contract;user ID =sa;password= yourpassword;

If your SQL Server is using the Windows integrated password, it is:

Data Source = servername;initial Catalog = contract;integrated Security=sspi;

As for other OLE DB or ODBC connection strings can go to http://www.connectionstrings.com

When you connect to a database, you must remember to close the connection, and the connection will not automatically close when the connection object is out of range in ado.net.

We're going to execute the command after we open the database connection, so let's talk about the command class

2 SqlCommand class

After establishing the database connection, we need to access and manipulate the database--crud:create, Read, Update, Delete.

In order to execute the command we create an Comand object that requires the execution of connection objects and CommandText objects.

SqlCommand cmd = new SqlCommand ();

Cmd. Connection = connectionobject;//such as the Conn object we established earlier

Cmd.commandtext = commandtext;//such as a SELECT statement

String commandtext = "select * from friend";

Of course we can also use stored procedures, which we'll discuss later.

The other way:

SqlCommand cmd = new SqlCommand (commandtext);

Cmd. Connection = connectionobject;//such as the Conn object we established earlier

Or

SqlCommand cmd = new SqlCommand (commandtext,connecionobject);

There is also a constructor that contains three parameters, which we do not discuss. The design is for transaction processing.

After we have the command object we need to perform the operation, but please be sure to open your database connection before execution, otherwise there will be an exception. The SqlCommand object provides the following 4 execution methods:

L ExecuteNonQuery

L ExecuteScalar

L ExecuteReader

L ExecuteXmlReader

The ExecuteNonQuery method executes a command that does not return results, and typically uses it to perform inserts, deletes, and updates.

For example, we do operations on the contract database:

String sqlins = "Insert [friend] (fname,fphone,faddress) VALUES (' Snow chills ', ' 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 yourself, do not copy and paste, there will be text errors (Chinese and English symbol problems).

The ExecuteScalar method performs a command that returns a single value, such as we need to count all the contacts in the system:

SqlCommand cmd = new SqlCommand ("SELECT count (*) from friend", Conn);

Conn.Open ();

int friendcount = (int) cmd. ExecuteScalar ();

MessageBox.Show ("Total" + friendcount.tostring () + "contact");

Description: The command can return multiple results, at which point the ExecuteScalar method returns the value of the first field in the first row, while all other values are inaccessible, which means that if the best performance is achieved, you should construct the appropriate select query so that the query's result set contains as little additional data as possible. This is the preferred method if you are only interested in a single return value. In addition, the method returns object type data, and it is your responsibility to ensure that the appropriate type conversions are made, otherwise you will get an exception.

The ExecuteXmlReader method executes a command that returns an XML string. It returns a System.Xml.XmlReader object that contains the returned SML. I know nothing about this method, no discussion ^_^.

(because the article can not operate 64k, I can only separate paste)