6-16 ADO. NET basics and Connection Pool

================================== ADO. NET ================================== 1. Create a connection object Connection1. create a connection string: string constr = @ "Data Source =. \ SQLEXPRESS; Initial CataLog = ccdb; user id = sa; password = sa; "; view-server volunteer Manager-data connection-right-click-find name-Database password-advanced 2. connect to the database SqlConnection con = new SqlConnection (constr); 3. open the database; con. open (); can only be opened once. It cannot be opened again. Because SqlConnection inherits the DbConnection class, DbConnection implements the IDisposable interface, so con is required. close (); con. dispose (); can be closed repeatedly ==== the above method is more difficult to use usingusing (SqlConnection con = new SqlConnection (constr) 1. the connection string cannot be opened repeatedly, so you need to determine

if (con.State == System.Data.ConnectionState.Closed){con.Open();}

　　

2. When the status changes, print the View Code of the function that has an event on con.

1 con.StateChange += new System.Data.StateChangeEventHandler(con_StateChange);2 static void con_StateChange(object sender, System.Data.StateChangeEventArgs e)3 {4 Console.WriteLine(e.CurrentState.ToString());5 }

 

4. The connection string is generated through this (most of the time you can see this)

1             SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();2             scsb.DataSource =@".\SQLEXPRESS";3             scsb.UserID = "sa";4             scsb.Password = "sa";5             scsb.InitialCatalog = "ccdb";6             scsb.ConnectTimeout = 30;7             Console.WriteLine(scsb.ConnectionString);8             Console.ReadKey();

 

5. manually create

 1  private void button1_Click(object sender, EventArgs e) 2 { 3 SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(); 4 propertyGrid1.SelectedObject = sqlBuilder; 5 } 6   7 private void button2_Click(object sender, EventArgs e) 8 { 9 SqlConnectionStringBuilder sb = (SqlConnectionStringBuilder)propertyGrid1.SelectedObject;10 MessageBox.Show(sb.ConnectionString.ToString());11 }

 

---- SqlDataReader1. read-only, the previous record will be destroyed every time you move forward, so DataReader only enters. 2. read-only, only data can be read, and data cannot be modified. during use of Reader, the connection must be enabled. value (0) = reader [0] reader ["column name"] internally implements string colunmName = "EmpGender"; retrieves an index based on the column name, and then calls GetValue () based on the index (); int index = reader. getOrdinal (colunmName); reader. value (index); generally, we recommend that you use indexes to obtain the value. Do not use column names that are less efficient. getOrdinal ("column 1"); int i2 = reader. getOrdinal ("column 2"); Note: Do not write the column name ==== in the loop to read a result set in the while loop for (int I = 0; I <reader. fieldCount; I ++) {console. write (reader. getValue (0);} console. writeLine () ;=== get multiple result sets

1 do 2 {3 if (reader. hasRows) 4 {5 while (reader. read () 6 {7 // loop Column 8 for (int I = 0; I <reader. fieldCount; I ++) 9 {10 11 // obtain the column name 12 // reader based on the index. getName (I); 13 // obtain the data type of the current column 14 string dbType = reader. getDataTypeName (I); 15 switch (dbType) 16 {17 case "varchar": 18 case "nvarchar": 19 case "char": 20 case "nchar": 21 Console. write (reader. getString (I) + "\ t"); 22 break; 23 case "int": 24 Console. write (reader. getInt32 (I) + "\ t"); 25 break; 26} 27} 28 Console. writeLine (); 29} 30} 31} while (reader. nextResult ());

 

// When multiple result sets are available, you can call NextResult = dataReader to exclusively connect to DataReader. A Connection must be exclusive. (Unless MARS is set to allow, multi-activity result set, in the connection string) must be set in the connection string, set the multi-activity result set to true IsDbNull // if the current column is null, an error is returned when a strongly typed method is called to obtain data ., You need to make a judgment before getting data. Verify string obj3 = reader. IsDBNull (index) through reader. IsDBNull (2 )? "Null": reader. getString (2); ---- Exception Handling may occur from open in reader, but it is already open at this time, therefore, in order to avoid a crash, write it in catch, but disable it in any case if an exception occurs. Therefore, you must disable con in finally. close (); con. dispose () ---- the returned result is the automatic number insert into Tblclass output inserted tclassid values ('hippo ', 'Hot moves .. ') Execute any SQL statement. In fact, ExecuteNonQuery () and ExecuteScalar () ExecuteReader () of the SqlCommand object can be called, but only corresponding statements are executed, if the correct method is used, it is better not to use (int) obj (unboxing. If it is not int type, an error is returned.) convert is recommended. toint32 (obj); =========================== ADO. NET connection pool =============================== connection pool // 1. why is the performance gap so high between enabling and disabling connection pools? Pooling = false // 1. when the connection pool is enabled, it seems that the connection pool is opened and logged out for 2000 times (the connection is opened and closed) only once (the connection will be closed after the program is closed ). So high performance // 2. After the connection pool is disabled, it is actually disabled 2000 times. When the Close method is used, the current connection object is put into the connection pool. When another connection object is created next time, if the connection string used by the connection object is exactly the same as the connection string of the session connection object (must be exactly the same, including case and space), then, in fact, it will not really create a connection to the database, but will use the existing connection in the connection pool; ADO. NET connection pool usage Summary 1. the first time a connection is opened, a connection object is created. 2. when the connection is closed (when the Close () method is called), the current connection object is put into the pool. 3. 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 another 4. only when Close () is called is put into the pool. If a connection object is still in use, create another connection object next time. If no connection object exists in the pool, create another connection object. 5. in the connection pool, if you haven't accessed it for a while, the reflection will be automatically destroyed: PropertyInfo pinfo = typeof (SqlConnection ). getProperty ("InnerConnection", BindingFlags. nonPublic | BindingFlags. instance); get the value after reflection object obj1 = null; object obj2 = null; // get the InnerConnection obj2 = pinfo of the second object. getValue (con2, null); each user uses a different connection string. We recommend that you disable the connection pool to clear the connection pool: SqlConnection. clearAllPools (); SqlConnection. clearPool ();