ADO.Net

1. ADO.Net A Brief Overview

2. Introduction • There probably isn’t an application of any significance that doesn’t involve access to a database. • In 1992, Open Database Connectivity ODBC was created to provide a common interface for access to proprietary DBMS. • DBMS providers developed ODBC drivers. • This is a very low-level interface.

3. Introduction • Classic ADO (ActiveX components) relied on OLE DB providers to access data. • It was an improvement over ODBC because it was higher level and allowed access to any tabular data (such as a spreadsheet).

4. ADO.Net Providers These implement a .Net wrapper around existing data providers. Managed code SQL Server SQL .Net Data provider OLE DB .Net data Provider Client OLE DB Provider Other DBMS ODBC .Net data Provider ODBC DB Provider Other DBMS

5. Figure 1.2. Data access stack

6. ADO.Net • SQL provider • Connects directly to SQL server with a native communication method • Optimized for SQL, faster than OLE DB • OLE DB provider • Connects to other DBMS such as Oracle and older versions of SQL Server

7. ADO.Net • All .Net data providers support the following objects • Connection • Command • DataReader • DataAdapter

8. using (SqlConnection connection = new sqlConnection(connectionString)) { //Create a SqlDataAdapter for the Suppliers table. SqlDataAdapter adapter = new SqlDataAdapter(); //A table mapping names the DataTable. adapter.TableMappings.Add("Table", "Suppliers"); //Open the connection. connection.Open(); //Create a SqlCommand to retrieve Suppliers data. SqlCommand command = new SqlCommand( "SELECT SupplierID, CompanyName FROM dbo.Suppliers;“, connection); command.CommandType = CommandType.Text; //Set the SqlDataAdapter's SelectCommand. adapter.SelectCommand = command; //Fill the DataSet. DataSet dataSet = new DataSet("Suppliers"); adapter.Fill(dataSet); }

9. ADO.Net • Connection Object • Establishes a connection to a database. • Parameters for the connection string vary by data provider. • These values will be stored in the web.config file

10. ADO.Net • Command Object • Using a connection object, the command object can execute a SQL statement or a stored procedure. • Using a command object to execute a SQL statement that returns a datareader

11. ADO.Net • DataReader • A datareader provides direct, sequential (one row at a time), read-only access to data in a database. • Datareaders are fast • Low memory usage • Why?

12. ADO.Net • DataReader • Random access is not allowed. • A connection is maintained until the last row is retrieved. • Only one row at a time is maintained in memory. • .Net provides a Read() method to retrieve the next record. • The Read() method returns False if there are no more records to read.

13. ADO.Net • DataReader • ExecuteReader • Returns a datareader • ExecuteScalar • Returns a single value. For example, the result of the query SELECT COUNT(*) FROM table • ExecuteNonQuery • Returns the number of rows affected by a DML query (INSERT, UPDATE, DELETE)

14. ADO.Net • ExecuteNonQuery • Returns the number of rows affected by a DML query (INSERT, UPDATE, DELETE) or DDL query (CREATE, ALTER) • Or in SQL ExerciseSystem, to issue the command: • SET SHOWPLAN_ALL { ON | OFF } • Before executing an SQL expression

15. ADO.Net • DataAdapter • A DataAdapter is used to fill a dataset and make changes to data in a database. • Four important properties • SelectCommand • InsertCommand • UpdateCommand • DeleteCommand

16. ADO.Net • DataSet • A DataAdapter is used to fill and manipulate DataSets. • A DataSet is an in-memory collection of data from multiple sources. • A DataSet can contain DataTables and DataRelation objects that describe the relationship between two tables (PK,FK).

17. ADO.Net • DataSet • A DataSet is a read-only, client-side cursor. • A DataSet can contain data from different databases using more than one DataAdapter. • The DataTables are loaded into memory with the DataAdapter Fill() method.

18. Figure 1.1. DataSet object model

19. ADO.Net • DataSet • With the switch from ADO to ADO.Net, the concept of the Recordset and it’s navigation methods went away. • recordset.movenext, .moveprevious… • Now we use: • foreach (string item in collection) • Or directly reference an item in the collection using a subscript • A DataTable is accessed via it’s: • DataColumn collection • DataRow collection