ADO .NET

1. ADO .NET

2. .NET Framework Data Namespaces • System.Data • Base set of classes and interfaces for ADO .NET • System.Data.Common • Classes shared by the .NET Data Providers • System.Data.OleDb • Classes that make up the .NET Data Provider for OLEDB • System.Data.SqlClient • Classes that make up the .NET Data Provider for SQL Server • System.Data.SqlTypes • Classes that represent the SQL data types • System.XML • Classes for working with XML data

3. .NET Data Providers (1) • A Data Provider supports data access through its own specific implementation that supports a common set of interfaces • Two standard .NET Data Providers • SQL provider – for MS SQL Server 7.0 or later • OLE DB provider – for OLE DB support, e.g. Oracle, OLE DB .NET Data Provider as a wrapper around an OLE DB provider • Each .NET Data Provider is implemented as a group of types that reside in System.Data.SqlClient and System.Data.OleDb namespaces

4. SQL .NET Data Provider SQLServer Client OLE DB.NET Data Provider OLE DBProvider OtherDBMS ODBCProvider OtherDBMS Managed Code .NET Data Providers (2)

5. .NET Data Providers (3) • Some fundamental classes supported by any .NET Data Provider • Connection – allows establishing and releasing connections, and to begin transactions • Command – allows storing and executing a command (SQL query, stored procedure) • DataReader – provides direct, sequential (forward-only), read-only access to data in a database • DataAdapter – built on DataReader, this class creates and populates instances of the class DataSet. DataSets allow more flexible access to data than using just DataReader

6. .NET Data Provider Connection Client Rows Command DBMS DataSet DataReader DataAdapter .NET Data Providers (4) • Clients can access data through a DataReader (straightforward one-row-at-a-time) or by using a DataSet (more complex requirements such as ordering, filtering, sending results across a network etc.)

7. Accessing Data with Reader • Create a Connection object (of class SqlConnection or OleDbConnection) • set ConnectionString property • Create a Command object (of class SqlCommand and OleDbCommand) by Connection object’s CreateCommand method • Set CommandText property • Call the Open() method of the Connection object • Declare a DataReader (if need) • Executing the query by calling methods of the Command object • ExecuteReader(): returns a DataReader, can be accessed one row at a time • ExecuteScalar(): returns a single value, e.g. result of SUM function • ExecuteNonQuery(): returns the number of rows affected • Process the result • Close the DataReader (if using ExecuteReader) • Close the Connection with the Close() method

8. AdoExample1 Sub Main() Dim query As String = "SELECT StudentID, Name, Sex FROM Students" Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=school.mdb" Dim conn As OleDbConnection = New OleDbConnection(connStr) Dim command As OleDbCommand = New OleDbCommand(query, conn) Dim reader As OleDbDataReader Try conn.Open() ' open connection reader = command.ExecuteReader() ' execute the command While reader.Read() ‘ process records Console.WriteLine("ID: {0}, Name: {1}", reader.GetString(0), reader.GetString(1)) End While Catch ex As OleDbException Console.Out.WriteLine(ex.Message) Finally reader.Close() conn.Close() End Try End Sub

9. Accessing Data with DataSets (1) • A DataSet is an in-memory cache for data • Disconnected: manipulate data without connecting the database, allow you to move data across a network • DataSets allow much more flexible access to data, can examine data in an arbitrary way, scrolling back and forth • Datasets are used in conjunction with DataAdapters. DataAdapters populate DataSets with data from data stores. • Useful for combining data from different data sources, and for data transfer across a network (as DataSets are serializable)

10. Accessing Data with DataSets (2) • Each DataSet can contain zero or more DataTable objects. • Each DataTable can contain the result of some query • A DataSet can also maintain relationships among DataTables using DataRelation objects • Each DataSet has a schema, describing the tables, columns, data types DataRelation DataTable DataTable

11. Creating and Using DataSets • Create a Connection object • set ConnectionString property • Create a Command object • Set CommandText property • Create a DataAdapter object (SqlDataAdapter, OleDbDataAdapter) • Set the SelectCommand, InsertCommand, UpdateCommand, DeleteComand property to the Command object created in last step • Create a DataSet object • Call the Open() method of the Connection object • Use the Fill() method of the DataAdapter object to fill the DataSet • Close the Connection with the Close() method • You may then update/delete rows in DataSet and use the Update() method of the DataAdapter to update the database

12. AdoExample3 Sub Main() Dim query As String = "SELECT StudentID, Name, Sex FROM Students" Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=school.mdb" Dim conn As OleDbConnection = New OleDbConnection(connStr) Dim command As OleDbCommand = New OleDbCommand(query, conn) Dim da As OleDbDataAdapter = New OleDbDataAdapter(command) ‘ set the SelectCommand as well Dim ds As DataSet = New DataSet Dim table As DataTable Dim row As DataRow Dim rowIndex As Integer Try conn.Open() ' open connection da.Fill(ds, “Students”) ‘ fill the dataset conn.Close() ‘ connection can be closed (disconnected) table = ds.Tables.Item(“Students”) ‘ select the DataTable For rowIndex = 0 To table.Rows.Count - 1 row = table.Rows.Item(rowIndex) Console.Out.WriteLine("ID: {0}, Name: {1}", _ row("StudentID"), row("Name")) Next Catch ex As OleDbException Console.Out.WriteLine(ex.Message) End Try End Sub

13. DataAdapter Properties • SelectCommand • contains a Command object that can be used to populate a DataTable within DataSet • Command object typically references a SQL SELECT statement • InsertCommand • to insert rows added to a DataTable into an underlying database • Command object typically references a SQL INSERT statement • UpdateCommand • to update a database based on changes made to a DataTable • Command object typically references a SQL UPDATE statement • DeleteCommand • to delete rows in a database based on deletions made to a DataTable • Command object typically references a SQL DELETE statement

14. DataAdapter Methods • Fill • Used to execute a query (in the SelectCommand) and store the result in a DataSet • Da.Fill(Ds, “TableName”) • Note that the connection can be closed once the Fill method is done • Update • Used to modify data in the database based on changes made to the DataTables

15. DataSet Contents • Contents of a DataSet are grouped into collections • DataSet has a Tables property • a collection of DataTable • table = ds.Tables.Item(“Students”) • table = ds.Tables.Item(0) • DataTable has a Rows property • a collection of DataRow • row = table.Rows.Item(rowIndex) • Column values can be obtained by row("StudentID") or row(0) • Relations collection

16. Basic Objects Dim query As String = "SELECT * FROM Students" Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=school.mdb" Dim conn As OleDbConnection = New OleDbConnection(connStr) Dim command As OleDbCommand = New OleDbCommand(query, conn) Dim da As OleDbDataAdapter = New OleDbDataAdapter(command) ' commandBuilder is used create the Commands automatically! Must be for UPDATE Dim autogen As New OleDbCommandBuilder(da)

17. Adding Data Using a DataSet Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Dim ds As DataSet = New DataSet Dim table As DataTable Dim row As DataRow Try da.Fill(ds, "Students") ' fill the dataset table = ds.Tables.Item("Students") ' select the DataTable row = table.NewRow() row("StudentID") = "A90001" row("Name") = "Chan Chan" row("Sex") = "M" row("DateOfBirth") = #10/31/2005# row("PhoneNo") = "12345678" row("Class") = "41111" table.Rows.Add(row) da.Update(ds, "Students") Catch ex As OleDbException Console.Out.WriteLine(ex.Message) End Try End Sub

18. Updating Data Using a DataSet Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click Dim ds As DataSet = New DataSet Dim table As DataTable Dim row As DataRow Dim rowIndex As Integer Try da.Fill(ds, "Students") ' fill the dataset table = ds.Tables.Item("Students") ' select the DataTable For rowIndex = 0 To table.Rows.Count - 1 row = table.Rows.Item(rowIndex) If row("StudentID") = "A90001" Then row("Sex") = "F" End If Next Catch ex As OleDbException Console.Out.WriteLine(ex.Message) End Try da.Update(ds, "Students") End Sub

19. Deleting Data Using a DataSet Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click Dim ds As DataSet = New DataSet Dim table As DataTable Dim row As DataRow Dim rowIndex As Integer Try da.Fill(ds, "Students") ' fill the dataset table = ds.Tables.Item("Students") ' select the DataTable For rowIndex = 0 To table.Rows.Count - 1 row = table.Rows.Item(rowIndex) If row("StudentID") = "A90001" Then row.Delete() End If Next Catch ex As OleDbException Console.Out.WriteLine(ex.Message) End Try da.Update(ds, "Students") End Sub

20. AdoExample4 • Add/Update/Delete

21. Binding a DataSet to a Control • Setup the connection and dataadapter • Select the DataAdapter, generate the DataSet • Set the DataSource property of the control • e.g. datagridview1.DataSource = myDataTable • AdoExample5

22. Using SQL to Query Data (1) ‘ Assume connection object is setup already Dim sql As String = “SELECT * FROM STUDENTS” Try conn.Open() Dim da As New OleDbDataAdapter(sql, conn) Dim ds As New DataSet da.Fill(ds, "table") DataGrid1.DataSource = ds DataGrid1.DataMember = "table" Catch e As Exception MsgBox("Error in executing the following SQL statement:" & vbCrLf & vbCrLf & sql & vbCrLf & vbCrLf & e.Message) Finally conn.Close() End Try

23. Using SQL to Query Data (2) Dim sql As String = "SELECT StudentID, Name, Sex FROM Students“ Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=school.mdb" Dim conn As OleDbConnection = New OleDbConnection(connStr) Dim command As OleDbCommand = New OleDbCommand(query, conn) Dim reader As OleDbDataReader Try conn.Open() ' open connection reader = command.ExecuteReader() ' execute the command While reader.Read() ‘ process records Console.WriteLine("ID: {0}, Name: {1}", reader.GetString(0), reader.GetString(1)) End While Catch ex As OleDbException Console.Out.WriteLine(ex.Message) Finally reader.Close() conn.Close() End Try

24. Using SQL to Update Data ‘ Assume connection object is setup already Dim sql As String = “UPDATE STUDENTS SET Sex = “F” Dim command As OleDbCommand = New OleDbCommand(sql, conn) Dim rowAffected As Integer Try conn.Open() rowAffected = command.ExecuteNonQuery() MsgBox("Query executed, " & rowAffected & " rows affected") Catch e As Exception MsgBox("Error in executing the following SQL statement:" & vbCrLf & vbCrLf & sql & vbCrLf & vbCrLf & e.Message) Finally conn.Close() End Try

25. Using SQL Aggregate Functions Dim sql As String = "SELECT COUNT(*) FROM Students“ Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=school.mdb" Dim conn As OleDbConnection = New OleDbConnection(connStr) Dim command As OleDbCommand = New OleDbCommand(query, conn) Dim result As Integer Try conn.Open() ' open connection result = command.ExecuteScalar() ' execute the command Console.WriteLine(“The count is {0} ", result) Catch ex As OleDbException Console.Out.WriteLine(ex.Message) Finally conn.Close() End Try

26. DataReader Vs DataSet • DataReader • Less memory consumed, fast access • Simple • One row at a time (forward sequential access) • Connection cannot be closed before finishing access • DataAdapter + DataSet • More flexible, can examine data in an arbitrary way, scrolling back and forth • Connection can be closed and accessing the DataSet afterwards (i.e. DataSet can be de-linked with the connection)