ADO. NET memory fragments (1)

 

Connection class

The Connection object is mainly used to enable the Connection between the program and the database. You cannot obtain data from the database if you do not use the linked object to open the database. This object is at the bottom layer of ADO. NET. We can generate this object by ourselves or automatically generate it by other objects. There are two methods to construct the Connection class:

String strConn = "..."; // connection string

1. SqlConnection cn = new SqlConnection (strConn );

2. SqlConnection cn = new SqlConnection ();

Cn. ConnectionString = strConn;

Some information in the connection string should be remembered, including: server instance, database, username and password of login server, and others.

Then we simply write a connection string:

String strConn = "Data Source =.; Initial Catalog = Northwind; User ID = lmf; Password = 123456 ";

This is the simplest connection string. It is based on the orchestration in this form: key = value; however, it is not long or short. What should I do if I cannot remember the key in it, you can solve this problem as follows:

SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder ();

Bldr. DataSource = ".";

Bldr. InitialCatalog = "Northwind ";

Bldr. UserId = "lmf ";

Bldr. Password = "123456 ";

The SqlConnectionStringBuilder class is a built-in class of the net class library. The key names in ConnectionString all implement attributes in the SqlConnectionStringBuilder class. You can assign values to these attributes and call bldr. the ConnectionString attribute can be used to obtain the desired connection string.

 

Command class

The Command object can be used to send commands to the database, such as sending query, addition, modification, and deletion commands to the database, as well as calling the stored programs in the database. This object is structured on the Connection object, that is, the Command object is connected to the data source. There are three methods to construct the Command class:

String strConn = "..."; // connection string

String strSql = "select * from tableName ";

SqlConnection cn = new SqlConnection (strConn );

Cn. Open ();

1. SqlCommand cmd = new SqlCommand ();

Cmd. Connection = cn;

Cmd. CommandText = strSql;

2. SqlCommand cmd = new SqlCommand (strSql, cn );

3. SqlCommand cmd = cn. CreateCommand ();

Cmd. CommandText = strSql;

After constructing the Command class, we connect to the server on the database client and write the required SQL statement. However, we have not run the SQL statement. The SQL statement should be executed. Execute the query using SqlCommand. The ExecuteReader () method is as follows:

SqlDataReader rdr = cmd. ExecuteReader ();

The Code shows that the ExecuteReader method returns a SqlDataReader object, which is used to check the query results. SqlDataReader is a stream-based query result. You can view a row in the result at the same time. When you move to the next row, the previous row cannot be accessed again. There are multiple ways to access a specific column, based on the field name or serial number of the query.

Traverse the query results:

While (rdr. Read ())

{

Console. WriteLine ("{0} -- {1}", rdr [0], rdr ["mermername"]);

}

Rdr. Close ();

In the above Code, the Read () method call completes two tasks at the same time: first, place SqlDataReader in the next row of the result set. Second, the method returns a boolean value indicating whether a running row exists. In addition, when the Command class executes the rdr returned by the ExecuteReader () method, it cannot directly access the first row of data in the result set. You must run Read () on rdr () method before accessing the first line of the result set.

Sometimes a result set contains one row and one column. This method can be implemented at a high cost and can be solved in this way. Call the ExecuteScalar () method:

Object obj = new cmd. ExecuteScalar ();

The ExecuteScalar () method returns an object-type value.

ExecuteNonQuery () method is called to execute a query that does not return a result set:

Cmd. ExecuteNonQuery ();

However, it cannot be seen whether the SQL statement is successfully executed. In fact, ExecuteNonQuery () returns an int value.

Generally, there are two types of queries that do not return a result set: www.2cto.com

1. Data Operation Language Query (DML): insert, update, and delete operations do not return result sets.

2. Data Definition Language Query (DDL): create table, alter view, and drop proc

If it is a DML statement, the returned int value is to change the number of rows in the database table.

If it is a DDl statement, the returned int Is-1;

 

From the kiss of the eight gods