.NET 数据访问架构指南

.NETAlexMackman,ChrisBrooks,SteveBusby,EdJezierski200110.NETADO.NET68•ADO.NET•••••BLOBs••.NETMicrosoftADO.NETADO.NETWebWebADO.NETADO.NETSQLServer.NETDataProvider--ADO.NET--MicrosoftSQLServer2000OLEDB.NETOLEDB(DataAccessApplicationBlock).NET.NET.NET.NETActiveX(ADO)/OLEDBSQLServer.NETADO.NET.NET“ADOADO.NET”ADO.NETDataTable,DataSet,DataAdapter,DataReaderADORecordsetDataTableRecordsetDataSetDataTableDataSetXMLDataSetXMLADO.NETDataAdapterDataSetDataAdapterRecorder1DataSet1DataSet.NETADO.NET.NETConnection,Command,DataReader,DataAdapterADO.NET•SQLServer.NETMicrosoftSQLServer7.0SQLServerSQLServerSQLServer•SQLServer7.0SQLServer2000•OLEDB.NET.OLEDBSQLServer.NETDataProviderOLEDBODBCMSDASQLOLEDBODBCODBC.NETADO.NETOLEDB.NET•ODBC.NETBeta1.0ODBCOLEDB.NETOLEDBODBC.NETBeta.•SQLServer2000XMLSQLServerWeb2XMLSQLServer2000XML..NET•System.Data.SqlClient.SQLServer.NET•System.Data.OleDb.OLEDB.NET•System.Data.Odbc.ODBC.NET•System.Data.DataSetDataTableConnection,Command,DataReader,DataAdapterSqlClientSqlOleDbOleDbConnectionSqlClientSqlConnectionOleDbOleDbConnectionDataAdapterSqlDataAdapterOleDbDataAdapterSystem.DataIDbConnection,IDbCommand,IDataReader,IDbDataAdapterConnection,Command,DataReader,DataAdapter.NET“ADO+.NET”SQLSqlCommandSqlCommandSqlDataAdapterSqlCommandSQL•••SQL••SQLADO.NET//UseconstructorargumentstoconfigurecommandobjectSqlCommandcmd=newSqlCommand(SELECT*FROMPRODUCTS,conn);//Theabovelineisfunctionallyequivalenttothefollowing//threelineswhichsetpropertiesexplicitlysqlCommandcmd=newSqlCommand();cmd.Connection=conn;cmd.CommandText=SELECT*FROMPRODUCTS;.NETCOMADO.NETVBDimxAsNew…COMCOM“”.NETWeb•••SQLServerWindows•••ODBCOLEDBOLEDBMDAC:•SQLServer.NET•OLEDB.NETSQLServer.NETSQLServer.NETSQLServer.NET-Server=(local);IntegratedSecurity=SSPI;Database=Northwind;MaxPoolSize=75;MinPoolSize=5SQLServer100•-SqlConnectionconn=newSqlConnection(IntegratedSecurity=SSPI;Database=Northwind);conn.Open();//PoolAiscreatedSqlConmectionconn=newSqlConnection(IntegratedSecurity=SSPI;Database=Northwind);conn.Open();//PoolBiscreated(extraspacesinstring)•.NETBeta.NETRTM•OLEDB.NETOLEDB.NETOLEDB•••OLEDBOLEDBMSDN“OLEDB”19OLEDBWindowsDNAOLEDB/ODBCCOM+•COM+•SQLServer.NETOLEDB.NETCOM+OLEDB“OLEDBServices=-4”SQLServerProfilerWindows2000SQLServerProfiler1.MicrosoftSQLServerProfilerProfiler2.3.4.5.6.1.2.3.SQLServer:4.5..NETRTMADO.NETSQLServer.NETWindowsSQLServerWindows•(Windows)SQLServer•••.NETBeta2WindowsSQLServerWindowsWindows.NETRTMWindowsSQLServerWindowsWindowsWindows•Windows/•SQLServer•WindowsInternetIISSQLServerTCP/IPSQLServer7.0WindowsTCP/IPTCP/IP•ASP.NETWebWeb.config•(UDL)OLEDB.NET)•Windows••COM+()WindowsSQLServerASP.NETWebWeb.configPersistSecurityInfoSqlConnectionOleDbConnectionConnectionStringXMLappSettings-configurationappSettingsaddkey=DBConnStrvalue=server=(local);IntegratedSecurity=SSPI;database=northwind//appSettings/configurationappSettingsconfigurationsystem.web•.NETxcopy•ConfigurationSettingsAppSettings•ASP.NETWeb.configASP.NETInternetISAPIDLL.configNTFSWebSystem.Configuration.ConfigurationSettingsAppSettingsDBConnStrusingSystem.Configuration;privatestringGetDBaseConnectionString(){returnConfigurationSettings.AppSettings[DBConnStr];}.NET“FileName=name.udl.”UDL•UDLs•UDLNFTS.config•SqlClientUDLSQLServer.NETSQLServer7.0•/ASP.NETWebSYSTEMMachine.configWeb.config•WebUDL•ASP.NET•ACLs•.NET•xcopy•••ASP.NETASP.NETCOM+COM+COM+Construct•MMC•COM+COM+•COM+.NETCOM+•ServicedComponent•.NET.NET••••CloseDisposefinallyC#usingDisposefinallyVisualBasic.NETC#VisualBasic.NETpublicvoidDoSomeWork(){SqlConnectionconn=newSqlConnection(connectionString);SqlCommandcmd=newSqlCommand(CommandProc,conn);cmd.CommandType=CommandType.StoredProcedure;try{conn.Open();cmd.ExecuteNonQuery();}catch(Exceptione){//Handleandlogerror}finally{conn.Close();}}C#usingVisualBasic.NETusingpublicvoidDoSomeWork(){//usingguaranteesthatDisposeiscalledonconn,whichwill//closetheconnection.using(SqlConnectionconn=newSqlConnection(connectionString)){SqlCommandcmd=newSqlCommand(CommandProc,conn);fcmd.CommandType=CommandType.StoredProcedure;conn.Open();cmd.ExecuteQuery();}}SqlDataReaderOleDbDataReaderADO.NET.NET.NETSQLServer.NET.NET.NET.NETSystemException.NETSQLServerSQLServer.NETSqlExceptionOLEDB.NETOleDbExceptionOLEDB3.NETOleDbExcepti