ADO.NET Data Provider and Data Connections - ADO.NET Learning & Application Notes

Database operations similar to the ADODB connection mode are ADO.NET data providers. The ADO.NET of the .NET Framework version 1.1 provides four managed data providers.

1. ADO.NET's data provider ADO.NET supporting four data providers, which are SQLClient data providers for SQL Server7.0 and higher, corresponding namespaces are system.data.sqlclient; The OLEDB data provider of the OLE DB data source is system.data.OLDB; the ODBC data provider for ODBC data sources, the corresponding namespace is system.data.odbc; for Oracle data sources OracleClient data provider, the corresponding namespace is System.Data.OracleClient. (Note: .NET FRAMEWORK1.0 version does not include ODBC and OracleClient data providers, you need to download installation from Microsoft official site)

ADO.NET's SQL Server Data Provider has more excellent performance than the OLE DB data provider. The former is dedicated to SQL Server, and has targeted, through the TDS packets and SQL Server direct conversations, the latter must communicate with the data source through a COM component set, ie the data source OLE DB provider and OLE DB service components. . Therefore, for SQL Server access, it is recommended to use the SQL Server data provider. The figure below shows the data access comparison of the two:

Second, using the Connection object to connect the database to access the data source, first connect to the Connection object, connect to the specified data source, the Connection class of the FCL data provider is an universal interface System.Data.idbConnection. Connection constructor usually has a connection string as a parameter, and the connection string can also be set using the CONNCTION.CONNECTIONSTRING property. If you use C #, they create connection statements usually as follows:

IDBConnection conn = new xxxConnection ("Connection String"); conn.open ();

Connection's Close method is capable of shutting down data connections, but sometimes Close is unable to interrupt to the database. In addition to the Close method, there is a Dispose method that invokes the Close method, which specifies whether the non-hosting resource should be released (including the COM interface pointer, ODBC handle, etc.) by setting a boolean parameter. After the method is called, the Connection object is completely released. It cannot be reused.

(1) The difference between the connection string is different for different data providers, and there is a difference in connection strings. SqlConnection database connection string is generally as follows: "Server = mySQLServer; Database = northwind; User ID = sa; Password = mypwd;" OleDbConnection database connection string as follows: "Provider = SQLOLEDB; Data Source = mySqlServer; Initial Catalog = northwind User ID = sa; password = mypwd; "The database connection string of ODBCConnection is as follows:" Driver = {SQL Server}; server = localhost; database = northwind "or" DSN = DSNNAME "OracleConnection database connection string is as follows:" data source = Oracle8i; User ID = sa; pwd = mypwd "are commonly used attributes can refer to the following table: SqlConnectionOleDbConnectionOdbcConnectionOracleConnection Description data source / ServerData SourceServerData source / Server data source name or network address of the Initial Catalog / DatabaseInitial CatalogDatabase database name User IDUser IDUser IDUser ID Service Agent User PasswordPasswordPasswordPasswordWord User Password Provider OLEDEROLE DB Data Provider, OLEDBConnection Connection Connection Server Required DSNODBCConnection dedicated connection string parameters, DSN name

More connection string parameters of more connection objects can refer to the FCL SDK. SqlConnection.ConnectionString Property Reference: http: //msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassConnectionStringTopic.asp OleDbConnection.ConnectionString Property Reference: http: //msdn.microsoft.com/library/en-us /cpref/html/frlrfSystemDataOleDbOleDbConnectionClassConnectionStringTopic.asp OdbcConnection.ConnectionString property reference: http: //msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataOdbcOdbcConnectionClassConnectionStringTopic.asp OracleConnection.ConnectionString property reference: http: //msdn.microsoft .com / library / en-US / CPREF / HTML / FRLRFSYSTEMDATAOracleClientOracleConnectionClassConnectionstringtopic.asp

(2) The storage of the connection string in the application is used to facilitate administrative data connections, and usually the data connection string is not written as a hard code, but stored outside the application. ".NET Data Access Architecture Guide" One article discusses the storage of the link string in one section. The storage of the link string can be used below: 1. Application configuration files such as web.config files for ASP.NET web applications. 2, Universal Data Link File (UDL) (supported by OLE DB .NET Data Supply) 3, Windows Registration Table 4, Custom File 5, COM Directory, By using constructor (only for service components) The way has advantages and disadvantages, and the most common in ASP.NET is to use the web.config storage connection string, which is included in an ADD node in the element Appsettings. AppSettings System.Configuration.ConfigurationSettings using ASP.NET application class which Static properties, you can get custom settings for your application. (3) Connection pool connecting the pool to allow databases to share different sessions of the same account to avoid frequent opening and closing connections, which can significantly improve the performance of the application. The connection pool using the SQL Server Data Provider requires attention, whenever the application opens a connection, .NET Framework will create a pool, each connection pool, and a different connection string associated. That is, if the connection string used by the newly created connection is matched to the existing pool, the new pool will be created. Note: The poolization mechanism is sensitive to spacespace-specific spaces. The three connections below have created three different pools: SqlConnection conn = new sqlconnection ("Integrated Security = SSPI; Initial Catalog = Pubs"); conn.open (); // Create pool A

SqlConnection Conn = New SqlConnection ("Integrated Security = SSPI; Initial Catalog = Northwind"); conn.open (); // Creating pool B, and the front connection string is different

SqlConnection Conn = New SqlConnection ("Integrated Security = SSPI; Initial Catalog = Northwind"); conn.open (); // Create pool C, SSPI more a space

Reference Resources: 1, Bob Beauchemin "Essential ADO.NET" 2, .NET Framework SDK "Developer Guide" 3, DINO Esposito "analysis. NET hosting provider" 4, Alex Mackman ".NET Data Access Schema Guide"