Warming and then knowing new--some explanations of common objects in ADO

The general steps for getting data using the ADO-link database are:

1. Set the Web. config//To set the address of the server database and password of the login name

2. Create a Connection object//To create a connection to access the database

3. Create command object//To send commands (write SQL statements)

4. Get data with DataReader//Get Database data

5. Data/containers obtained using dataset storage

6. Close Connection object and Datareader/dataadapter connection//For performance, do not close may error

(where 3, 42 steps can be replaced with DataAdapter objects)

I. Setting up the Web. config

Open Web. config and join directly at the root node:

<connectionStrings>
<add name= "Try" connectionstring= "Data source= database address; initial catalog= database name; persist Security Info=true;user id= login name; password= password; " />
</connectionStrings>

Become

<Configuration>//Root node  <system.web>    <compilationDebug= "true"targetframework= "4.5.1" />    <HttpRuntimetargetframework= "4.5.1" />  </system.web>  <connectionStrings>    <Addname= "Try"connectionString= "Data source= database address; initial catalog= database name; persist security Info=true;user id= login; password= password;"/>  </connectionStrings></Configuration>

This adds a connection string, named try

Second, Connection object

Now to connect to the database through the connection string you just added, get the connection string in Web. config and create a new Connection object:

using System.Configuration; using System.Data.SqlClient; string connectionString = configurationmanager.connectionstrings["Try"//  Take the connection string named try new// Use this connection string to create an connection object

Third, Command object

Before you use the command object, you must ensure that our connection object is already in the open state:

Conn. Open ();

The creation of the Commond object, regardless of the form, is only guaranteed to be connected to the Connection object and the SQL statement we wrote in it:

// created by the previous connection object " Select Top Ten * from Project ";  // Write SQL statements // or the form of a direct constructor below  NewSqlCommand("select top * from Project", Conn );

It has three common ways of

1.ExecuteNonQuery (); This method applies to non-query statements, returning the number of rows affected (int)

" Update Project set name= ' Tom ' where id=3 " ; int // returns 1, where one data is modified

2.ExecuteScalar (); The method returns the first row of the first column of data (object) of the query result

" Select COUNT (*) from Project " ; int int // returns the total number of rows in the project table because the return type is object and therefore needs to be converted to int

3.ExecuteReader (); The method returns a DataReader object that gets the results of the query, followed by

Iv. DataReader objects and DataAdapter objects

Usage of 1.DataReader

The DataReader can be returned by the command's ExecuteReader () method, whose core method is read ():

SqlDataReader dr = cmd. ExecuteReader ();  while (Dr. Read ())  ///The Read method returns a bool value, returns True if there is a value, and advances to the next line, returning false  {        Response.Write () when the entire table read is finished Dr. GetInt32 (0));  // This assumes that the first column of the table is of type int, the GetInt32 () method needs to pass an int parameter, the index value of the column starting from 0 }    

This will read the first column of the query results, it is suitable for fast access to some information

Remember to close DataReader when you are finished reading:

Dr. Close ();d R. Dispose ();

Usage of 2.DataAdapter

The DataAdapter object is also known as a data adapter object, which is used to populate a dataset container:

The dataset a=new DataSet("ds1"new SqlDataAdapter (" Select Top Ten * from Project " , conn); Ada. Fill (a);

Note:

When you're done, remember to close connection and Datareader/dataadapter:

Ada. Dispose ();d R. Dispose ();
Conn. Close (); Conn. Dispose ();

This is actually very troublesome, it has to be closed, in order to diagram simple, you can use the using syntax.

Its complete read data and return DataSet example is as follows:

PrivateDataSet GetDataSet (stringSQL) {    stringconnectionString = configurationmanager.connectionstrings["Try"]. ConnectionString;//take the connection string named tryDataSet ds =NewDataSet ("DS1"); using(SqlConnection conn =NewSqlConnection (connectionString)) {       using(SqlDataAdapter ada =NewSqlDataAdapter (SQL, conn)) {Ada.         Fill (DS); }    }    returnds;}

Warming and then knowing new--some explanations of common objects in ADO