1 / 35

ADO.NET 2.0

ADO.NET 2.0. Captator Tlf: 8620 4242 www.captator.dk Henrik Lykke Nielsen Softwarearkitekt, Microsoft Regional Director for Denmark lykke@captator.dk Mobil: 2237 3311. Agenda. Providermodellen Basis klasser og interfaces Provider factories, Connectionstrenge Schema API

bryga
Download Presentation

ADO.NET 2.0

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADO.NET 2.0 CaptatorTlf: 8620 4242www.captator.dkHenrik Lykke NielsenSoftwarearkitekt, Microsoft Regional Director for Denmarklykke@captator.dkMobil: 2237 3311

  2. Agenda • Providermodellen • Basis klasser og interfaces • Provider factories, Connectionstrenge • Schema API • DataSet, DataTable, DataView • Indexeringsmekanisme, serialisering • SqlBulkCopy • Asynkrone kald • DataBinding

  3. Web/Win Form Controls VS.NET Class Generator XML Parser Kode Cust OLEDB SQL … ADO.NET arkitekturen VS.NET Designers DataSet .NET Data Provider DataAdapter DataReader Item Command Order Connection <xml>…</xml>

  4. Basis klasser og interfaces i 1.1 Bemærk: Læses som at SqlDataAdapter arver fra DbDataAdapter SqlClient • SqlConnection • IDbConnection • SqlCommand • IDbCommand • SqlDataAdapter • DbDataAdapter • DataAdapter • IDataAdapter • IDbDataAdapter • IDataAdapter • SqlDataReader • IDataReader • IDataRecord OleDb • OleDbConnection • IDbConnection • OleDbCommand • IDbCommand • OleDbDataAdapter • DbDataAdapter • DataAdapter • IDataAdapter • IDbDataAdapter • IDataAdapter • OleDbDataReader • IDataReader • IDataRecord

  5. Basis klasser og interfaces i 2.0 SqlClient • SqlConnection • DbConnection • IDbConnection • ISqlConnection • IDbConnection • SqlCommand • DbCommand • IDbCommand • ISqlCommand • IDbCommand • SqlDataAdapter • DbDataAdapter • DataAdapter • IDataAdapter • IDbDataAdapter • IDataAdapter • SqlDataReader • DbDataReader • IDataReader • IDataRecord • ISqlReader • ISqlRecord • IDataRecord OleDb • OleDbConnection • DbConnection • IDbConnection • OleDbCommand • DbCommand • IDbCommand • OleDbDataAdapter • DbDataAdapter • DataAdapter • IDataAdapter • IDbDataAdapter • IDataAdapter • OleDbDataReader • DbDataReader • IDataReader • IDataRecord DbDataAdapter implementerer nu IDbDataAdapter

  6. Interfaces og basis klasse i 1.1

  7. Interfaces og basis klasser i 2.0 • De abstrakte basis klasser befinder sig i System.Data.Common namespacet

  8. Provider factories • System.Data.Common.DbProviderFactory • En abstrakt klasse der er basisklasse for de konkrete factories: SqlClientFactory, OleDbFactory, ... • Metoder: • CreateConnection As DbConnection • CreateCommand As DbCommand • CreateCommandBuilder, CreateConnectionStringBuilder, CreateDataAdapter, CreateParameter, CreatePermission, CreateDataSourceEnumerator, CanCreateDataSourceEnumerator • De konkrete factories har ingen konstruktører, men i stedet en Instance-metode VB Public ReadOnly SharedInstanceAsSqlClientFactory public readonly staticSqlClientFactory Instance C#

  9. Provider factories • System.Data.Common.DbProviderFactories • Util klasse • Metoder: Public Shared Function GetFactory(ByVal providerInvariantName As String) _As System.Data.Common.DbProviderFactory Public Shared Function GetFactory(ByVal providerRow As System.Data.DataRow) _As System.Data.Common.DbProviderFactory Public Shared Function GetFactoryClasses() As System.Data.DataTable VB public static System.Data.Common.DbProviderFactory GetFactory(string providerInvariantName) public static System.Data.Common.DbProviderFactory GetFactory( System.Data.DataRow providerRow) public static System.Data.DataTable GetFactoryClasses() C#

  10. Provider factories • DbProviderFactories er registreret i machine.config GetFactoryClasses giver en DataTable med ovenstående info <system.data> <DbProviderFactories> <addname="Odbc Data Provider" invariant="System.Data.Odbc" support="BF" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <addname="OleDb Data Provider" ... /> <addname="OracleClient Data Provider" ... /> <addname="SqlClient Data Provider" ... /> <addname="SQL Server CE Data Provider" ... /> </DbProviderFactories> </system.data>

  11. ConnectionStrenge • System.Data.Common.DbConnectionStringBuilderer et svagt-typet dictionary Dim factory As System.Data.Common.DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient") Dim connBuilder As System.Data.Common.DbConnectionStringBuilder connBuilder = factory.CreateConnectionStringBuilder() connBuilder.Add("Data Source", "localhost") connBuilder.Add("User Id", "DemoUser") connBuilder.Add("Password", "hemmeligt") VB System.Data.Common.DbProviderFactory factory; factory = DbProviderFactories.GetFactory("System.Data.SqlClient"); System.Data.Common.DbConnectionStringBuilder connBuilder; connBuilder = factory.CreateConnectionStringBuilder(); connBuilder.Add("Data Source", "localhost"); connBuilder.Add("User Id", "DemoUser"); connBuilder.Add("Password", "hemmeligt"); C#

  12. ConnectionStrenge • Nedarves af (mere eller mindre) typestærke klasser • OdbcConnectionStringBuilder • Driver, Dsn • OleDbConnectionStringBuilder • DataSource, FileName, Provider, ... • OracleClientConnectionStringBuilder • DataSource, IntegratedSecurity, LoadBalanceTimeout, MaxPoolSize, MinPoolSize, Password, PersistSecurityInfo, Pooling, Unicode, UserID, ... • SqlConnectionStringBuilder • ApplicationName, AsynchronousProcessing, ConnectTimeout, DataSource, Encrypt, FailOverPartner, InitialCatalog, IntegratedSecurity, LoadBalanceTimeout, MaxPoolSize, MinPoolSize, MultipleActiveResultSets, NetworkLibrary, Password, PersistSecurityInfo, Pooling, Replication, UserID, ...

  13. Enumerering af data sources • GetDataSources på en DbDataSourceEnumerator finder datasources (MS SQL) på nettet • System.Data.Sql.SqlDataSourceEnumerator er den eneste konkrete implementation Dim factory As System.Data.Common.DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient") Dim enumerator As System.Data.Common.DbDataSourceEnumerator enumerator = factory.CreateDataSourceEnumerator() Dim dataSourceTable As System.Data.DataTable = enumerator.GetDataSources() VB System.Data.Common.DbProviderFactory factory; factory = DbProviderFactories.GetFactory("System.Data.SqlClient"); System.Data.Common.DbDataSourceEnumerator enumerator; enumerator = factory.CreateDataSourceEnumerator(); System.Data.DataTable dataSourceTable = enumerator.GetDataSources(); C#

  14. Fælles programmeringsmodel • Lettere for Microsoft (og andre) at udvide providere • Mere ensartet programmeringsmodel • Provider factories løser instansieringsproblemet • Gem providerens InvariantName i f.eks. konfiguationsfil • GetFactory der returnerer den enkelte provider factory instans bruger reflektion – så genbrug den! • Brug af basisklasser giver ikke performance overhead • GetFactoryClasses() god til udvikler/admin tools • ConnectionStringBuilders gør det lettere at lave connectionstrenge – men de enkelte er (desværre) ret forskellige

  15. Schema APIet • API til at få schema-oplysninger om en DataSource • DbConnections GetSchema-metode i tre former Public Function conn.GetSchema() As DataTable Public Function conn.GetSchema(collectionName As String) As DataTable Public Function conn.GetSchema(collectionName As String, _ restrictionValues As String()) As DataTable VB public DataTable conn.GetSchema() public DataTable conn.GetSchema(string collectionName) public DataTable conn.GetSchema(string collectionName, string[] restrictionValues) C#

  16. Schema - MetaDataCollections • Få en liste over metadatacollections • Metadata collections: • MetaDataCollections, DataSourceInformation, DataTypes, Restrictions, Users, Databases, Tables, Columns, Views, ViewColumns, ProcedureParameters, Procedures, ForeignKeys, IndexColumns, Indexes, UserDefinesTypes VB Dim tbl As DataTable = conn.GetSchema() ' eller tbl = conn.GetSchema("MetaDataCollections") DataTable tbl = conn.GetSchema(); // eller DataTable tbl = conn.GetSchema("MetaDataCollections"); C#

  17. Schema - DataSourceInformation • Få informationer om datasourcen • Indeholder én row • Muligvis forskellige felter – ens for MS’s providere • DataSourceProductName, DataSourceProductVersion, IdentifierPattern, ParameterMarkerFormat, ParameterMarkerPattern, ParameterNameMaxLength, ParameterNamePattern, QuotedIdentifierPattern, StatementSeparatorPattern, StringLiteralPattern, SupportedJoinOperators, ... VB Dim tbl As DataTable = conn.GetSchema("DataSourceInformation") DataTable tbl = conn.GetSchema("DataSourceInformation"); C#

  18. Schema - restriktioner • Restriktioner er simple filtre for, hvilke entiteter man ønsker metadata for • Restriktioner angives via værdier i et streng array • Restriktioner er defineret i ”Restrictions”-collectionen • Eksempel på brug af restrictioner • Konkrete informationer varierer mellem providere Dim res AsString(3) res(1) = "Person" res(2) = "Contact" Dim tbl As System.Data.DataTable = conn.GetSchema("Columns", res) VB string[] res = newstring[4]; res[1] = "Person"; res[2] = "Contact"; System.Data.DataTable tbl = conn.GetSchema("Columns", res); C#

  19. DataSet, DataTable - performance • Ny indexeringsmekanisme giver markant forbedret performance ved for eksempel Fill og Merge public System.Data.DataTable CreateDataTable(int numberOfRows) { System.Random rnd = new System.Random(); System.Data.DataTable tbl = new System.Data.DataTable(); tbl.Columns.Add("Id", typeof(System.Guid)); tbl.Columns["Id"].Unique = true; tbl.Columns.Add("RandomValue", typeof(int)); for (int n = 0; n < numberOfRows; n++) { System.Data.DataRow row = tbl.NewRow(); row["Id"] = System.Guid.NewGuid(); row["RandomValue"] = rnd.Next(); tbl.Rows.Add(row); } return tbl; } C#

  20. DataSet, DataTable - performance • Resultater

  21. Dataset, DataTable - serialisering • DataSets og DataTables serialiserer sig selv som XML uanset man bruger en BinaryFormatter • Kan nu fortælle, at der skal serialiseres binært • RemotingFormat-propertyen kan sættes til • System.Data.SerializationFormat.Xml (default) • System.Data.SerializationFormat.Binary

  22. Dataset, DataTable - serialisering tbl.RemotingFormat = System.Data.SerializationFormat.Binary Dim formatter AsNew BinaryFormatter() Dim st AsNew FileStream(fileName, FileMode.Create) formatter.Serialize(st, tbl) st.Close() • Eksempel med 100.000 simple rows skrevet til fil • XML-format: 16.813 KB – 19.851 KB • Binært format: 835 KB – 3.374 KB VB tbl.RemotingFormat = System.Data.SerializationFormat.Binary; BinaryFormatter formatter = newBinaryFormatter(); FileStream st = newFileStream(fileName, FileMode.Create); formatter.Serialize(st, tbl); st.Close(); C#

  23. DataTable - XML • XML understøttelse som for DataSets:ReadXml, ReadXmlSchemaWriteXml, WriteXmlSchema

  24. DataSet, DataTable, DataReader • Populér DataTable/DataSet ud fra DataReader • Generer DataReader ud fra DataTable/DataSet • Hvis CreateDataReader kaldes på et DataSet med flere tabeller vil DataReaderen indeholde multiple resultset 'Tag fat i en database … Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader() Dim tbl As New System.Data.DataTable() tbl.Load(dr) VB //Tag fat i en database … System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); System.Data.DataTable tbl = new System.Data.DataTable(); tbl.Load(dr); C# VB Dim tbl As System.Data.DataTable = LavEnDataTable() Dim dr As System.Data.DataTableReader = tbl.CreateDataReader() System.Data.DataTable tbl = LavEnDataTable(); System.Data.DataTableReader dr = tbl.CreateDataReader(); C#

  25. DataTable, DataView • Generer en DataTable ud fra et DataView Dim tbl As System.Data.DataTable = LavEnDataTable() Dim view As New System.Data.DataView(tbl) view.Sort = "FirstName ASC, LastName DESC" view.RowFilter = "FirstName>'C'" Dim columnNames AsString() = { "FirstName", "LastName" } Dim tblFiltered As System.Data.DataTabletblFiltered = view.ToTable("MinFiltreredeTabel", False, columnNames) VB System.Data.DataTable tbl = LavEnDataTable(); System.Data.DataView view = new System.Data.DataView(tbl); view.Sort = "FirstName ASC, LastName DESC"; view.RowFilter = "FirstName>'C'"; string[] columnNames = { "FirstName", "LastName" }; System.Data.DataTable tblFiltered;tblFiltered = view.ToTable("MinFiltreredeTabel", false, columnNames); C#

  26. Connection statistik - SqlClient • StatisticsEnabled, RetrieveStatistics conn.StatisticsEnabled = true; System.Collections.Hashtable statistics = (System.Collections.Hashtable)_conn.RetrieveStatistics(); txtStatistics.Text = ""; foreach (string key in statistics.Keys) { txtStatistics.Text += key.PadRight(20, ' ') + "= " + statistics[key].ToString() + "\r\n"; } C# NetworkServerTime = 50 BytesReceived = 20989728 UnpreparedExecs = 12 SumResultSets = 12 SelectCount = 12 PreparedExecs = 0 ConnectionTime = 113202 ExecutionTime = 3194 Prepares = 0 BuffersSent = 12 SelectRows = 239664 ServerRoundtrips = 12 CursorOpens = 0 Transactions = 0 BytesSent = 1968 BuffersReceived = 2628 IduRows = 0 IduCount = 0

  27. Multiple Active ResultSets (MARS) • SQL Server 2005 feature • Man kan nu have flere resultsets åbne på samme tid– flere DataReadere om en connection (hver sin command) SqlClient.SqlConnection conn = new SqlClient.SqlConnection(connString); conn.Open(); SqlClient.SqlCommand cmd1 = new SqlClient.SqlCommand(sql1, conn); SqlClient.SqlDataReader rd1 = cmd1.ExecuteReader(); rd1.Read(); rd1.Read(); rd1.Read(); rd1.Read(); rd1.Read(); rd1.Read(); SqlClient.SqlCommand cmd2 = new SqlClient.SqlCommand(sql2, conn); SqlClient.SqlDataReader rd2 = cmd2.ExecuteReader(); rd2.Read(); rd1.Read(); rd2.Read(); rd2.Close(); rd1.Close(); conn.Close(); C# Op til 9 poolede commands per connection

  28. UpdateBatchSize og SqlBulkCopy • DbDataAdapter.UpdateBatchSize property påvirker DbDataAdapter.Update • System.Data.SqlClient.SqlBulkCopy påvirker performance • 100.000 rows i en DataTable • DbDataAdapter.Update tog 70 sekunder (BatchSize=100) • SqlBulkCopy.WriteToServer tog 3 sekunder System.Data.DataTable tbl = CreateDataTable(100000); System.Data.SqlClient.SqlBulkCopy bulk = new System.Data.SqlClient.SqlBulkCopy(connString); bulk.DestinationTableName = "RandomTable"; bulk.WriteToServer(tbl); bulk.Close(); C#

  29. Asynkrone kald • Asynkrone kommandoer i ADO.NET 1.1 og ADO.NET 2.0 • I 1.1 er det muligt at lave asynkrone kald ved at bruge ThreadPool eller asynkrone delegates – en baggrundstråd blokeres • Asynkrone kommandoer i 2.0 blokerer ingen baggrundstråde • Brugen af asynkrone kommandoer til ADO.NET (SqlClient) • I scenarier hvor det er vigtigt ikke at blokere tråde (fx. Web apps) • Designet som resten af de asynkrone arkitekturer i .NET • Xxx => BeginXxx med input-parms og EndXxx med output-parms og retur-værdi

  30. Asynkrone kald • Opsætning • ”Asynchronous Processing=true” eller ”async=true” i Connectionstring • Brug forskellige connections til synkrone og asynkrone connections (af hensyn til performance) • Der findes 4 måder at afslutte et asynkront kald på: • Callback – angiv metode der skal kaldes når arbejde udført • Synkroniseringsobjekt – koordinering af asynkrone kald via WaitHandle • Polling – check på IAsyncResults property IsCompleted • Kald EndXxx som vil blokere indtil det asynkrone kald er færdigt • Vær opmærksom på: • Kald EndXxx, når et asynkront kald er færdigt (for at undgå ressource-leaks) • Der kan kastes exceptions ved både BeginXxx og EndXxx

  31. DataBinding • System.Windows.Forms.BindingSource fungerer • som en datakilde for kontroller • som en mekanisme for opdatering af data • som et datalager (datasource) i sig selv • Kan bindes til forskellige datasources • Simple objekter (benytter properties) • Lister såsom ArrayList • Komplekse datakilder såsom DataTables

  32. DataBinding • Sæt BindingSources DataSource-property til datakilden • DataSource kan også sættes til en System.Type • Informationen videregives til kontroller der bindes Dim binding As System.Windows.Forms.BindingSource binding = New System.Windows.Forms.BindingSource() grdPerson.DataSource = binding Dim p As Person p = New Person("Mr", "Anders", "And", "a.and@andeby.com") binding.Add(p) p = New Person("Ms", "Andersine", "And", "a2.and@andeby.com") binding.Add(p) p = New Person("Mr", "Mikkel", "Mus", "m.mus@andeby.com") binding.Add(p) VB

  33. DataBinding • Events såsom: • AddingNew, CurrentItemChanged, PositionChanged • ListChanged(ListChangedType: Added, Changed, Removed, Moved, ...) • Properties såsom: • Current, Filter, Position, Sort, ListCount • Metoder såsom: • Move*, Add, Clear, Remove, RemoveFIlter, RemoveSort • ResetBindings-metoden fortæller kontrollerne, at data har ændret sig

  34. DataBinding- BindingNavigator • En video kontrol der kan bruges til navigering og redigering • Sæt BindingNavigator-komponentens BindingSource-property til et BindingSource objekt • ToolStripButtons kan let tilføjes og fjernes

  35. Spørgsmål www.captator.dknyheder, artikler, information, ...

More Related