Notes for learning ADO. NET

In those years, I was still learning ADO. NET.
After learning ASP. NET in those years, I began to learn new ASP. NET knowledge. ADO. NET is used to access the database and can generally be divided into connection mode and non-connection mode. The Connection Mode means that the database is disconnected only after the data is accessed. The connection mode mainly uses ADO. NET objects include Connection, Command, and DataReader. Connection Mode refers to operations on the database by means of data sets, and data is read to the memory to complete data operations, data assembly is automatically updated to the database, mainly using ADO. NET objects are DataAdapter and DataSet. Let's take a look at the code below.
In this example, the Code adopts the factory mode, so that only a small amount of code can be changed to complete the switching between databases. The factory mode requires the following objects: DbProviderFactory, DbConnection, DbTransaction
DbCommand, DbDataReader, DbDataAdapter, DbCommandBuilder, etc.

1. Common connection string

Copy codeThe Code is as follows:
String ProviderName = "System. Data. SqlClient ";
String ConnStr = "Data Source =.; Initial Catalog = Northind; Integrated Security = True ";
String sqlStr = "select * from dbo. Categories ";


2. The non-connection mode code is as follows:

Copy codeThe Code is as follows:
Public void getSqlConnection ()
{
// Obtain a data provider based on the data provider object
DbProviderFactory dbf = DbProviderFactories. GetFactory (ProviderName );
// Create a connection
DbConnection conn = dbf. CreateConnection ();
// Connection string
Conn. ConnectionString = ConnStr;
Conn. Open ();
DbTransaction ts = conn. BeginTransaction ();
DbCommand dbcmd = null;
Try
{
Dbcmd = dbf. CreateCommand ();
Dbcmd. CommandText = sqlStr;
Dbcmd. Connection = conn;
Dbcmd. Transaction = ts;
DbDataReader dr = dbcmd. ExecuteReader ();
While (dr. Read ())
{
Console. WriteLine (dr [1]. ToString ());
}
Dr. Close ();
Ts. Commit ();
}
Catch (Exception e)
{
Ts. Rollback ();
}
Finally
{
Conn. Close ();
If (dbcmd! = Null)
{
Dbcmd. Dispose ();
}
}
}

Effect:

3. Connection mode code:
Copy codeThe Code is as follows:
Public void getDataSetConnection ()
{
// Obtain a data provider based on the data provider object
DbProviderFactory dbf = DbProviderFactories. GetFactory (ProviderName );
// Create a connection
DbConnection conn = dbf. CreateConnection ();
// Connection string
Conn. ConnectionString = ConnStr;
// Create a DataAdapter object
DbDataAdapter da = dbf. CreateDataAdapter ();
// Create an automatically generated SQL statement object
DbCommandBuilder dbCmdb = dbf. CreateCommandBuilder ();
Using (DbCommand dbcmd = dbf. CreateCommand ())
{
Dbcmd. CommandText = sqlStr;
Dbcmd. Connection = conn;
// Specify the DbDataAdapter command
Da. SelectCommand = dbcmd;
// DbCommandBuilder specifies dataAdpter
DbCmdb. DataAdapter = da;
DataSet ds = new DataSet ();
Da. Fill (ds );
// Ds. Tables [0]. Rows [0]. Delete ();
Da. Update (ds );
DataTable dt = ds. Tables [0];
DataRow dr;
For (int I = 0; I <dt. Rows. Count; I ++)
{
Dr = dt. Rows [I];
Console. WriteLine (dr [1] + "" + dr [2]);
}
}
}

Effect:

The above is a simple example. Under normal circumstances, the connection string will not be written as a string and should be placed in the config file. Similarly, the SQL statement will be changed to a stored procedure, this makes it easier to change.

Summary

Those years of learning ADO. NET. NET has some new methods, such as using Linq and DbContext. In this article, we will recall those years of study.