An Introduction to ADO.Net

1. An Introduction to ADO.Net

2. Contents • What is ADO.Net? • What happened to ADO? • The ADO.Net object structure • Connecting • Commanding • Readers and DataSets

3. What is ADO.Net? • The data access classes for the .Net framework • Designed for highly efficient data access • Support for XML and disconnected record sets

4. And the .Net framework? • A standard cross language interface • Encapsulation of services, classes and data types • Uses XML for data representation

5. Where does ADO sit? VB C# C++ Jscript … Common Language Specification ASP.Net Windows Forms Visual Studio .NET ADO.Net XML.Net Base Class Library Common Language Runtime (CLR) Windows COM+ Services

6. What happened to ADO? • ADO still exists. • ADO is tightly coupled to client server architectures • Needs COM marshalling to pass data between tiers • Connections and locks are typically persisted

7. ADO / ADO.Net Comparisons

8. ADO / ADO.Net Comparisons

9. .NET Data Providers SQL .NET DataProvider SQL SERVER OLE DB .NET DataProvider OLE DB Provider Client Other DB ODBC .NET DataProvider ODBC Driver Other DB

10. Data Provider Functionality Client .Net Data Provider Connection Command Rows DataReader DataSet DataAdapter database

11. ADO.Net object model Fill DataAdapter DataSet Update Errors Collection UpdateCommand SelectCommand InsertCommand DeleteCommand Command Connection Parameters Data Source

12. Namespaces • System.Data & System.Data.Common • System.Data.SqlClient &System.Data.OleDB • System.Data.SqlTypes • System.XML & System.XML.Schema

13. Using Namespaces • VB.NetImports System.DataImports System.Data.SqlClientDim sqlAdp as SqlDataAdapter • C#using System.Data;using System.Data.SqlClient;SqlDataAdapter sqlAdp= new SqlDataAdapter();

14. SQL Namespace Objects • using System.Data.SqlClient; • SqlConnection • SqlCommand • SqlDataReader • SqlDataAdapter • SqlParameter • SqlParameterCollection • SqlError • SqlErrorCollection • SqlException • SqlTransaction • SqlDbType

15. Connecting to SQL • using System.Data.SqlClient;string sConnectionString = "Initial Catalog=Northwind; Data Source=localhost; Integrated Security=SSPI;";SqlDataAdapter sqlAdp= new SqlDataAdapter(sConnectionString);sqlAdp.Close();sqlAdp.Dispose();

16. Connection Pooling • ADO.Net pools connections.When you close a connection it is released back into a pool. • SqlConnection conn = new SqlConnection();conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";conn.Open(); // Pool A is created. • SqlConnection conn = new SqlConnection();conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=pubs";conn.Open(); // Pool B is created because the connection strings differ. • SqlConnection conn = new SqlConnection();conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";conn.Open(); // The connection string matches pool A.

17. Getting data • SqlCommand ExecuteReader ExecuteNonQuery ExecuteScalar ExecuteXMLReader • SqlDataAdapter DataSet

18. Using the command object • SqlCommand Multiple constructors • New() • New(cmdText) • New(cmdText, connection) • New(cmdText, connection, transaction)

19. Using the command object • string sSelectQuery = "SELECT * FROM Categories ORDER BY CategoryID";string sConnectionString = "Initial Catalog=Northwind; Data Source=localhost; Integrated Security=SSPI;";SqlConnection objConnect = new SqlConnection(sConnectString);SqlCommand objCommand = new SqlCommand(sSelectQuery, objConnect);/* • objCommand.CommandTimeout = 15;objCommand.CommandType = CommandType.Text; • */objConnect.Open();SqlDataReader drResults;drResults = objCommand.ExecuteReader()drResults.Close();objConnect.Dispose();

20. Command Methods • .ExecuteReader() - Returns DataReader • .ExecuteNonQuery() - Returns # of Rows Affected • .ExecuteXMLReader() - Returns XMLReader Object to Read XML documentation • .ExecuteScaler() - Returns a Single Value e.g. SQL SUM function.

21. The DataReader object • DataReader objects are highly optimised for fast, forward only enumeration of data from a data command • A DataReader is not disconnected

22. The DataReader object • Access to data is on a per record basis. • Forward only • Read only • Does support multiple recordsets

23. Creating a data reader SqlDataReader sqlReader; sqlReader = sqlCommand.ExecuteReader(); while (sqlReader.Read()) { // process, sqlReader("field") } sqlReader.Dispose();

24. Other Methods • GetString(), GetInt() etc. • GetSqlString(), GetSqlInt32() etc. • GetValues() • IsDBNull() • GetSchemaTable()

25. DataSets • In-memory representation of data contained in a database/XML • Operations are performed on the DataSet, not the data source • Can be created programmatically, using a DataAdapter or XML schema and document (or any mixture)

26. Creating DataSets • Setup SqlConnection • Setup a SqlDataAdapter • Create a DataSet • Call the .Fill() method on the DA

27. DataAdapters • Pipeline between DataSets and data sources • Geared towards functionality rather than speed • Disconnected by design • Supports select, insert, delete, update commands and methods

28. DataAdapters • Must always specify a select command • All other commands can be generated or specified

29. Using the DataAdapter SQLDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand =new SqlCommand ("select * from authors“, sqlConnection); DataSet sqlDS = new DataSet("authorsTable"); sqlDA.Fill(sqlDS, "authorsTable");

30. DataAdapters • For speed and efficiency you should set your own InsertCommand, UpdateCommand and DeleteCommand • Call GetChanges to seperates the updates, adds and deletes since the last sync. Then sync each type.

31. DataTables • A DataSet contains one or more DataTables. • Fields are held within the DataTable. • And in DataRows, DataColumns.

32. Sets, Tables and Rows DataSet DataTable DataTable DataRow DataRow

33. Using DataTables With a DataTable we can • Insert, modify and update • Search • Apply views • Compare • Clear • Clone and Copy

34. DataRelations • New to ADO.Net • Tables within a DataSet can now have relationships, with integrity. • Supports cascading updates and deletes.

35. DataViews • Like a SQL view • Single, or multiple tables • Normally used with GUI applications via Data Binding.

36. References • ADO.Net Programmer’s ReferenceBilbija, Dickenson et al.Wrox Press • http://oberon.idunno.org/sql/ • My email :desai8@uwindsor.ca

37. Thank You!!