Accessing Databases using Ado

1. Accessing Databases using Ado.net By Eric Vinokur

2. What we’re gonna do • Be introduced to technologies used for accessing databases • Become familiar with some ADO.NET classes • Look at what a database really is • Understand how to use and manipulate data from databases • Be introduced to SQL Query Statements

3. What is a database? • Location in a software that harbors large amounts of data • Information based • Physical Representation: • Collections of files with locations • LogicalRepresentation: • Records, fields, or tables • Software is needed to view databases in this way

4. How to access a database • Client • Data Providers: • Microsoft Access • Microsoft SQL Server • Oracle • Open/Embedded Databases • Each provider must provide details for the methods that are used in the interface • ODBC and DBMS • Open Database Connectivity • Database Management System

5. SQl server

6. oracle

7. Oracle

8. SQL – Structured query language • SEQUEL • Designed by IBM in 1974 • Standardized language used for requesting and manipulating data • SQL Statements or Queries– SELECT, INSERT, UPDATE, and DELETE data in database tables

9. Select Query • Select * From memberTable Order By LastNameAsc, FirstNameAsc; • Asterisk (*) selects all fields (columns) in database • Can replace * by field name(s) • Asc (ascending) returns in ascending order by LastName; duplicate last names ordered by first name • Retrieves all rows (records) • Where clause can be added to selectively identify rows Select PhoneNumber From memberTable Where FirstName = 'Gary' AND LastName = 'Jones';

10. Sql statement – SQL Server

11. Sql statement – Oracle

12. ADO.Net • ActiveX Direct Objects • Addition to .NET Framework • Contains number of classes that can be used to retrieve, manipulate, and update data in databases. • Already exists in Visual Studio • Works in Disconnect (temp file) or Connect manner

13. Main classes of ado.net • Connection • Command • DataReader • DataAdapter

15. Using the classes

17. Possible implementations • Close( ) • BeginDbTransaction( ) • ChangeDatabase( ) • CreateDbCommand( ) • OpenStateChange( )

18. Datareader implementations

19. Connection • Add using directive using System.Data.OleDb; • Instantiate an object of connection class • Send connection string that includes the actual database provider and the data source (name of the database) string sConnection; sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=member.mdb"; OleDbConnection dbConn; dbConn = new OleDbConnection(sConnection); dbConn.Open();

20. Retrieving data with SQL stringsql; sql = "Select * From memberTable Order By LastNameAsc, " + "FirstNameAsc;"; // Note the two semicolons OleDbCommanddbCmd = newOleDbCommand(); dbCmd.CommandText = sql; // set command SQL string dbCmd.Connection = dbConn;// dbConn is connection object

21. Retrieving data – continued • First call to dbReader.Read( ) retrieves first row • dbReader[0] refers to 1234 • dbReader[1] refers to “Rebecca” • dbReader["FirstName"] also refers to "Rebecca"

22. Retrieving data – continued Member aMember; OleDbDataReaderdbReader; dbReader = dbCmd.ExecuteReader( ); // dbCmd—OleDbCommand object while (dbReader.Read( )) { // retrieve records 1-by-1... aMember = new Member(dbReader["FirstName"].ToString( ), dbReader["LastName"].ToString( )); this.listBox1.Items.Add(aMember); } dbReader.Close(); // Close the Reader object dbConn.Close(); // Close the Connection object

23. Assignments are hand-written or typed in word

24. Thank you • Except Nolter