ADO. NET Summary

1. Remember to manually close DbConnection
When I accidentally used DbConnection last time, I forgot to manually close it. I thought it was the same as joining Stream. It will be closed after the lifecycle ends. But it turns out that this is not the case, according to MSDN:
See also
The Close method rolls back any pending transactions. Then, it releases the connection to the connection pool, or closes the connection when the connection pool is disabled. The application can call Close multiple times. No exception is generated. If DbConnection is out of the range, it will not be closed. Therefore, you must explicitly Close the connection by calling the equivalent Close or Dispose function. If you set the connection pool value Pooling to true or yes, the physical connection is also released. Www.2cto.com
2. Use DbProviderFactories and expand compatibility with mysql
ADO. NET2.0 introduces a provider factory model and a general base class for various ADO. NET classes.
Restrictions on ADO. NET universal interfaces:
• The interface is not easy to expand ADO. NET1.1. You cannot create instances of certain classes.
• ADO. NET1.1 cannot determine available. NET data providers.
How to solve the above restrictions by providing the factory Model
• Expand ADO and NET models through abstract Accumulation
• Use the DbProviderFactory class to create objects
Restrictions on the provider factory Model
• Many query structures are unique to databases
• When you set CommandText for parameterized queries, you may need to provide code specific to the program.
• Specifying the parameter data type may require code specific to the program
To make the development code generic, it is not limited to a specific database. In this development, we decided to use DbProviderFactory + standard SQL to develop an encapsulation suitable for mysql and sqlserver, however, DbProviderFactories does not support DbProviderFactory of mysql. Therefore, you need to expand the DbProviderFactories class to be compatible with mysql. In ADO.net 2.0, mysql and paramehave bugs in ParameterMarkerFormat, so the extension class solves this bug.
Public static class DbProviderFactoryEx {public static DbProviderFactoryGetFactory (string providerName) {if (providerName = null) throw newArgumentNullException ("providerName"); switch (providerName) {case "MySql. data. mySqlClient ": return new MySqlClientFactory (); default: returnDbProviderFactories. getFactory (providerName) ;}} public static stringGetParameterMarkerFormat (DbConnection connect ){ If (connect = null) throw newArgumentNullException ("connect"); Type type = connect. getType (); if (type = typeof (MySql. data. mySqlClient. mySqlConnection) returnMySqlParameterMarkerFormat; // mysql bug else if (type = typeof (System. data. sqlClient. sqlConnection) returnSqlServerParameterMarkerFormat; // MS bug connect. open (); string result = connect. getSchema ("performanceinformation "). rows [0] ["ParameterM ArkerFormat "]. ToString (); connect. Close (); return result;} public staticreadonly string SqlServerParameterMarkerFormat =" @ {0} "; public static readonlystring MySqlParameterMarkerFormat = "? {0 }";}
Iii. Enable sql2005 remote connection
When you connect to SQL Server 2005, you are not allowed to remotely connect to SQL Server in the default settings. You must manually enable the connection as follows: start> All Programs> Microsoft SQL Server 2005> Configuration Tools> SQL Server Surface Area Configuration> Surface Area Configuration for Services and Connections> Database Engine nodes under the MSSQLSERVER Node -> Remote Connections-> Local and remote connections-> select Using TCP/IP only or Using both TCP/IP and named pipes
Start> All Programs> Microsoft SQL Server 2005> Configuration Tools> SQL Server configuration Manager> restart the database service.
4. Chinese garbled characters using MySql
1. modify my. ini
[Mysqld] default-character-set = utf8
2. You are not authorized to modify my. ini.
Assuming that the database uses the default code latin1, the study found that:
1. Select data needs to be converted from Latin to GBK
2. The passed SQL statement needs to be converted from GBK to Latin.
Public static class DBHelper {public static T Get <T> (DbDataRecord record, string field) {int num = record. getOrdinal (field); if (record. isDBNull (num) return default (T); return (T) record [num];} publicstatic int? ToInt32 (object value) {if (value = null) return null; return (IConvertible) value ). toInt32 (null);} public static voidAddParameter (string name, object value, DbCommand cmd) {DbParameter para = cmd. createParameter (); para. parameterName = string. format (ParameterMarkerFormat, name); if (value = null) para. value = DBNull. value; else para. value = value; cmd. parameters. add (para);} publicstatic String latequalgbk (String str) {try {byte [] bytesStr = Encoding. getEncoding ("latin1 "). getBytes (str); returnEncoding. getEncoding ("GB2312 "). getString (bytesStr);} catch {return str;} public static String GBKToLatin (String str) {try {byte [] bytesStr = Encoding. getEncoding ("GB2312 "). getBytes (str); returnEncoding. getEncoding ("latin1 "). getString (bytesStr);} catch {return str;} public static string ParameterMarkerFormat = DbProviderFactoryEx. sqlServerParameterMarkerFormat ;}
Application
// Process DbDataReader using (DbDataReader reader = cmd. executeReader () {foreach (DbDataRecord record in reader) {role. ID = DBHelper. get <uint> (record, "ID"); role. name = DBHelper. latequalgbk (DBHelper. get <string> (record, "RoleName"); role. account = DBHelper. latequalgbk (DBHelper. get <string> (record, "Account"); return true ;}// process the foreable foreach (DataRow row in table. rows) {row ["OperationType"] = dbel Per. latequalgbk (row ["OperationType"]. toString (); row ["OperationReason"] = DBHelper. latinToGBK (row ["OperationReason"]. toString ();} // input SQL statement DbCommand cmd = conn. createCommand (); cmd. commandText = "select * from role where rolename =? Rolename "; DBHelper. AddParameter (" rolename ", DBHelper. GBK2Latin (name), cmd );



Author: CppExample.com