1 / 31

ADO.NET

ADO.NET. Accessing Data using ADO.NET. ADO.NET Architecture Connecting to Data Sources Performing Connected Database Operations Building Datasets Reading and Writing XML with ADO.NET Building Datasets from Existing Data Sources. ADO.NET Architecture.

jarah
Download Presentation

ADO.NET

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. Accessing Data using ADO.NET • ADO.NET Architecture • Connecting to Data Sources • Performing Connected Database Operations • Building Datasets • Reading and Writing XML with ADO.NET • Building Datasets from Existing Data Sources

  3. ADO.NET Architecture • Advantages/Disadvantages of a Connected/Disconnected Environment • ADO.NET Architecture • ADO.NET and XML

  4. Advantages/Disadvantages of a Connected Environment • A connected environment is one in which users are constantly connected to a data source • Advantages: • Environment is easier to secure • Concurrency is more easily controlled • Data is more likely to be current than in other scenarios • Disadvantages: • Must have a constant network connection • Scalability

  5. Advantages/Disadvantages of a Disconnected Environment • In a disconnected environment, a subset of data from a central data store can be copied and modified independently, and the changes merged back into the central data store • Advantages • You can work at any time that is convenient for you, and can connect to a data source at any time to process requests • Other users can use the connection • A disconnected environment improves the scalability and performance of applications • Disadvantages • Data is not always up to date • Change conflicts can occur and must be resolved

  6. “Thin”Client XML Web service Client logic Business logic Data storage “Fat”Client 1-Tier (monolithic) 2-Tier 3-Tier N-Tier Internet Evolution of data access

  7. ADO.Net Namespaces • System.Data • System.Data.Common • System.Data.SqlClient • System.Data.OleDb • System.Data.XXX • System.Data.SqlTypes • System.Xml

  8. In a connected scenario, resources are held on the server until the connection is closed Open connection Execute command Process rows in reader Close reader Close connection Using ADO.NET Classes in aConnected Scenario xxxDataReader xxxCommand xxxConnection database

  9. Using ADO.NET Classes in a Disconnected Scenario • In a disconnected scenario, resources are not held on the server while the data is processed • Open connection (optional) • Fill the DataSet • Close connection (optional) • Process the DataSet • Open connection (optional) • Update the data source • Close connection (optional) DataSet xxxDataAdapter xxxConnection database

  10. XML Web Services Client Data Source Request data SQL query 1 2 Results XML 4 3 SQL updates UpdatedXML 5 6 ADO.NET and XML • ADO.NET is tightly integrated with XML DataSet DataSet

  11. Connecting to Data Sources • Choosing a .NET Data Provider • Defining a Connection • Managing a Connection • Handling Connection Exceptions • Connection Pooling

  12. Choosing a .NET Data Provider • SQL Server .NET Data Provider • SQL Server version 7.0 or later • OLE DB .NET Data Provider • SQL Server 6.5, Microsoft Access, Oracle, other data sources with OLE DB providers • ODBC .NET Data Provider • Legacy data sources that only have ODBC drivers • Oracle.NET Data Provider • Oracle

  13. How to Set a Connection String • Example: MS Access connection using OleDb: Using System.Data.OleDb; OleDbConnection MyConn = new OleDbConnection() MyConn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:\data\nw1.mdb;“

  14. How to Set a Connection String • You can set the ConnectionString property only when the connection is closed • To reset a connection string, you must close and reopen the connection • Connection string for MS Access using System.Data.OleDb.OleDbConnection:provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\NW1.mdb; • Connection string for MS SQL using System.Data.SqlClient.SqlConnectionserver=(local);database=northwind;uid=sa;pwd=1234; server=(local);database=northwind;trusted connection=SSPI; • Connection string for MS SQL using System.Data.OleDb.OleDbConnectionprovider=SQLOLEDB; Data Source = (local); initial catalog = northwind; uid=sa; pwd=1234;

  15. Performing Connected Database Operations • Working in a Connected Environment • Building Command Objects • Executing Command Objects That Return a Single Value • Executing Commands That Return Rows • Executing Commands That Do Not Return Rows • Using Transactions

  16. XxxDataReader XmlReader XxxConnection XxxParameter XxxParameter XxxParameter Working in a Connected Environment Data Source XxxCommand

  17. Building Command Objects • A command object is a reference to a SQL statement or stored procedure • Properties • (Name), Connection, CommandType, CommandText, Parameters • Methods • ExecuteScalar, ExecuteReader, ExecuteNonQuery • ExecuteXmlReader (SqlCommand only)

  18. Executing Command Objects That Return a Single Value • Call the ExecuteScalar method • ExecuteScalar returns a value of the type Object • Use convert or a cast, to convert into appropriate type

  19. Executing Commands That Return Rows • SqlCommandcmProducts = New SqlCommand( “ SELECT ProductName, UnitsInStock FROM Products", MyConn); • MyConn.Open(); • SqlDataReaderrdrProducts = cmProducts.ExecuteReader(); • While rdrProducts.Read(){ • Console.WriteLine(rdrProducts.GetString(0)) • } • rdrProducts.Close() • MyConn.Close();

  20. Executing Commands That Do Not Return Rows • SqlCommandcmProducts = New SqlCommand( “ UPDATE Products SET price = price * 1.1", MyConn); • MyConn.Open(); • int intItemsUpdated = cmProducts. ExecuteNonQuery(); • MyConn.Close();

  21. Building DataSets • Working in a Disconnected Environment • Building DataSets and DataTables • Binding and Saving a DataSet • Defining Data Relationships • Modifying Data in a DataTable • Sorting and Filtering

  22. Working in a Disconnected Environment SQL Server 2000 Access Categories Products Customers Orders SqlDataAdapter OleDbDataAdapter DataSet Categories Products Employees Customers Orders XmlDataDocument XML File XML Web service

  23. Building DataSets and DataTables DataSet Client DataTable DataTable Connection Database Server

  24. Sample - One way of building a DataSetDeclare // Students Table DataTable tblStudents; // Students table columns DataColumn clmStudentID; DataColumn clmStudentName; DataColumn clmStudentClass; //Classes Table DataTable tblClasses; // Classes Table Columns DataColumn clmClassID; DataColumn clmClassMajor; // School DataSet DataSet dsSchool;

  25. Sample - One way of building a DataSet The StudentsTable // build the Students table tblStudents = new DataTable("Students"); // build and add columns for the students table clmStudentID= new DataColumn("StudentID",typeof(int)); tblStudents.Columns.Add(clmStudentID); clmStudentName = new DataColumn("StudentName", typeof(string)); tblStudents.Columns.Add(clmStudentName); clmStudentClass = new DataColumn("StudentClass", typeof(int)); tblStudents.Columns.Add(clmStudentClass); // Sst StudentID as a Primay Key tblStudents.PrimaryKey = clmStudentID;

  26. Sample - One way of building a DataSet The ClassesTable // build the Classes table tblClasses = new DataTable("Classes"); // build and add columns for the Classes table clmClassID = new DataColumn("ClasssID", typeof(int)); tblClasses.Columns.Add(clmClassID); clmClassMajor = new DataColumn("ClassMajor", typeof(string)); tblClasses.Columns.Add(clmClassMajor); // Sst ClasssID as a Primay Key tblClasses.PrimaryKey = clmClassID;

  27. Sample - One way of building a DataSet The DataSet // build the DataSet and add the two tables into it dsSchool = new DataSet(); dsSchool.Tables.Add(tblStudents); dsSchool.Tables.Add(tblClasses); //optional - set the relationship dsSchool.Relations.Add("fk_ClassesStudents", clmClassID, clmStudentClass);

  28. Modifying Data in a DataTable public void UpdateStudentClass(int StudentID, int StudentClass) { DataRow row = dsSchool.Tables["Students"].Rows.Find(StudentID); row["StudentClass"] = StudentClass; }

  29. Reading and Writing XML with ADO.NET • Creating XSD Schemas using XSD.EXE • Loading Schemas and Data into DataSets • Writing XML from a DataSet • Demo

  30. Building Datasets from Existing Data Sources • Configuring a DataAdapter to Retrieve Information • Populating a DataSet Using a DataAdapter • Configuring a DataAdapter to Update the Underlying Data Source • Persisting Changes to a Data Source • How to Handle Conflicts • Demo

  31. Review • Data-Centric Applications and ADO.NET • Connecting to Data Sources • Performing Connected Database Operations • Building Datasets • Reading and Writing XML with ADO.NET • Building Datasets from Existing Data Sources

More Related