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. Since the following discussion is designed to database operation, our virtual small items: Personal Address Book (Single User), which means we need a Contract database, including admin and friend: aname, apassword friend: Fname, aphone, Faddress, FID (primary key) Of course, you can design the Friend table based on your own, such as adding FSEX and other fields, not detailed here. Corresponding database created file: if exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [Admin]') And ObjectProperty (ID, n'uSERTABLE ') = 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; conn.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 == ConnectionsTate.Open) Conn.colse (); I have always mentioned Connectionstring, generally connected 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, ConnecionObject); there is another constructor containing three parameters, we do not discuss. 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. SQLCOMMand object provides the following 4 execution methods: l ExecutenonQuery L ExecuteScal L ExecuteReader L ExecutexmlReader ExecuteNonQuery method Executes commands that do not return results, usually use it to perform insert, delete, and update operations. For example, we will operate on the Contract database: string sqlins = "INSERT [FRIEND] (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 Executes a 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 ^ _ ^.
(Because the article can't operate 64K, I can only separate)
