C # basic ADO. NET knowledge point (01 ),

C # basic ADO. NET knowledge point (01 ),

ADO. NET

1. ADO. NET is a group of class libraries.

2. namespace: System. Data .*

3. ADO. NET: 1. Data Provider 2. Data Set

Data Providers
1. Connection: used to connect to the database.
2. Command: used to execute SQL statements.
1. ExecuteNonQuery (): Execute add, delete, modify, and delete operations on the database, and return the number of affected rows. Applicable to: insert, delete, and update (-1 is returned for other statements)
2. ExecuteScalar (): executes the query and returns the first column of the first row.
3. ExecuteReader (): executes the query and returns the DataReader object.
3. DataReader: Read-only result set, read data one by one. Read data from the database.

4. DataAdapter: an object that encapsulates the above three objects

Dataset
DataSet: temporary database in memory. Disconnects data operations.
DataSet-related classes:
DataView // View class. The data in the able can be viewed from different perspectives.
Rows in DataRowView // DataView.
DataTable // data table in DataSet
Rows in DataRow // DataTable
DataColumn // columns in the DataTable
DataRealation // relationship between DataTable and DataTable
Constraints created in Constraint // DataTable

Two Connection Methods
1. Connection + Command + DataReader
2. Connection + DataAdapter + DataSet

Connection Pool
1. The connection pool is enabled by default in ADO. NET.
2. How to clear the connection pool? SqlConnection. ClearAllPools (), SqlConnection. ClearPool ()
3. ADO. NET connection pool usage summary:
1. A connection object will be created when a connection is opened for the first time.

2. When the connection is closed (when the Close () method is called), the current connection object is put into the pool.

3. For the next connection object, if the connection string is exactly the same as the connection string of the existing connection object in the pool, the existing connection in the pool will be used instead of creating a new connection.

4. Only when Close () is called by the object will it be placed in the pool. If a connection object is still in use, create another connection object next time and find that there is no connection object in the pool, A new connection object will be created.

5. Connection objects in the pool are automatically destroyed if they are not accessed for a period of time

6. Recommended usage: Open it as soon as possible and close it as early as possible.

Statement parameterization
1. Prevent SQL injection vulnerability attacks

Configuration File
1. Store the database connection string in the configuration file.

Others

1. Transaction: Use transactions in ADO. NET

 

Code example

1. Connection + Command + DataReader example

1 /// <summary> 2 // Connection + Command + DataReader Example 3 /// </summary> 4 public void ConnectionTest () 5 {6 // database connection string 7 var connStr = "Data Source = 10.101.22.74, 8080; Initial Catalog = DataBaseName; User ID = userid; Password = password "; 8 // SQL statement 9 var SQL = "select count (*) from department where dname = 'a2 '"; 10 // link to the database and execute statement 11 using (SqlConnection conn = new SqlConnection (connStr) 12 {13 SqlCommand cmd = new SqlCommand (SQL, conn); 14 // cmd. commandType = System. data. commandType. storedProcedure; // Stored Procedure 15 conn. open (); 16 // cmd. executeNonQuery (); 17 // cmd. executeScalar (); 18 // cmd. executeReader (); 19 int resultOne = cmd. executeNonQuery (); // execute add, delete, and modify operations on the database, and return 20 affected rows. object resultTwo = cmd. executeScalar (); // execute the query and return 21 SqlDataReader resultThree = cmd in the first row. executeReader (); // executes the query and returns the DataReader object 22 while (resultThree. read () 23 {24 resultThree. isDBNull (0); // perform non-empty verification 25 var item = resultThree. getInt32 (0); 26 var item2 = resultThree. getString (1); 27} 28 resultThree. close (); // when you obtain the output parameters by executing the ExecuteReader () method, you need to set reader. 29 resultThree can be obtained after Close. dispose (); 30 // 231 using (SqlDataReader resultFour = cmd. executeReader () 32 {33 while (resultThree. read () 34 {35 resultThree. isDBNull (0); // perform non-empty verification 36 var item = resultThree. getInt32 (0); 37 var item2 = resultThree. getString (1); 38} 39} 40} 41}

2. SQL Injection prevention example

1 /// <summary> 2 // Example 3 of "anti-SQL injection" /// </summary> 4 public void ParameterTest () 5 {6 string name = Console. readLine (); 7 string password = Console. readLine (); 8 // database connection string 9 var connStr = "Data Source = 10.101.22.74, 8080; Initial Catalog = DataBaseName; User ID = userid; Password = password "; 10 // SQL statement 11 var sqlOne = "select * from T_Users where UserName =" + "qp" + "and Password =" + "123456 "; 12 var sqlTwo = "select * from T_Users where UserName =" + "qp" + "and Password =" + "123456 or 1 = 1 "; 13 var sqlThree = "select * from T_Users where UserName = @ name and Password = @ password "; 14 // link to the database and execute the statement 15 using (SqlConnection conn = new SqlConnection (connStr) 16 {17 SqlCommand cmd = new SqlCommand (sqlThree, conn); 18 cmd. parameters. add (new SqlParameter ("@ name", name); 19 cmd. parameters. add (new SqlParameter ("@ password", password); 20 conn. open (); 21 //...... 22} 23}

 

3. Example of Connection + DataAdapter + DataSet

1 /// <summary> 2 // Connection + DataAdapter + DataSet Example 3 /// </summary> 4 public void DataSetTest () 5 {6 // database connection string 7 var connStr = "Data Source = 10.101.22.74, 8080; Initial Catalog = DataBaseName; User ID = userid; Password = password "; 8 // SQL statement 9 var SQL = "select count (*) from department where dname = 'a2 '"; 10 // 111 using (SqlConnection conn = new SqlConnection (connStr )) 12 {13 SqlDataAdapter adapter = new SqlDataAdapter (SQL, conn); 14 DataSet ds = new DataSet (); 15 conn. open (); 16 adapter. fill (ds); // The SQL statement is being executed and the data is put into ds. 17 DataTable result = ds. tables [0]; 18} 19 // 2 20 using (SqlConnection conn = new SqlConnection (connStr) 21 {22 DataSet ds = new DataSet (); 23 SqlCommand cmd = new SqlCommand (SQL, conn); 24 conn. open (); 25 DataTable dt = new DataTable (); 26 using (SqlDataReader reader = cmd. executeReader () 27 {28 dt. load (reader); 29} 30} 31}

4、other