Unformatted text preview:

Lecture 11: Programmatic Database Access with ADO.NETObjectivesPart 1Relational technologyArchitectureExisting data providersADO.NET object modelPart 2Overview of database access(1) Open connectionBuilding connection strings(2) Retrieve records(3) Close connectionPart 3Data Tier designGuaranteed close?Part 4Executing action queriesBasic ideaDynamic SQLDynamic string buildingExampleOops!Escaping delimitersPart 5DataSetsFilling a DataSetDataGrid displayDataGrid is a powerful controlFlushing changes back to databaseSummaryLecture 11:Programmatic Database Access with ADO.NET11-2MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETObjectives“Programmatic database access typically involves executing SQL queries using classes from the language's framework. In .NET, the Active Data Objects (ADO) classes in the FCL are the primary means of database programming. ADO.NET is a vendor-neutral, object-oriented, SQL-based approach…”•Architecture of ADO.NET•Basic database access•Application design•Updating a database•DataSets11-3MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETPart 1•Architecture of ADO.NET…11-4MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETRelational technology•ADO.NET is designed to access relational databases•Example:–Sales database with customers, orders, and products11-5MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETArchitecture•ADO.NET architecture based on data providers–data providers encapsulate DB-specific detailsData ProviderADO.NET.NET Programmer DB11-6MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETExisting data providers•.NET currently ships with 4 data providers:–one for Microsoft SQL Server–one for Oracle–one for older OLEDB technology (used for ADO, VB6)–one for older ODBC (Open Database Connectivity) technology•More third-party providers are available…–Oracle's own provider: http://otn.oracle.com/tech/windows/odpnet/–DB2: http://www7b.software.ibm.com/dmdd/downloads/dotnetbeta/–MySQL: http://www.mysql.com/, http://crlab.com/mysqlnet/–etc.11-7MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETADO.NET object model•ADO.NET is an object-oriented approach•Classes are spread across a number of FCL namespaces–some are provider-neutral, others are provider-specificSQL Server System.Data.CommonSystem.DataSystem.Data.SqlClientSystem.Data.OleDbother DBs, e.g. MS Accessprovider-neutralODBC (Open Database Connectivity) System.Data.Odbc11-8MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETPart 2•Basic database access…11-9MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETOverview of database access•Three steps:1. open connection to database2. execute SQL to retrieve records / update DB3. close connection11-10MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETimport System.Data.*;import System.Data.OleDb.*;String sConnection;sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=Sales.mdb";OleDbConnection dbConn;dbConn = new OleDbConnection(sConnection);dbConn.Open();MessageBox.Show(dbConn.get_State().toString());import System.Data.*;import System.Data.OleDb.*;String sConnection;sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=Sales.mdb";OleDbConnection dbConn;dbConn = new OleDbConnection(sConnection);dbConn.Open();MessageBox.Show(dbConn.get_State().toString());(1) Open connection•Connections are opened based on connection string info–here we open a connection to a MS Access 2000 database–"Sales.mdb" must exist in same dir as .EXE (e.g. bin\Debug)connection11-11MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETBuilding connection strings•Connection strings are vendor-specific•Connection strings are not well-documented•Where to turn for help?–www.connectionstrings.com–www.able-consulting.com/ADO_conn.htm11-12MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET(2) Retrieve records•Retrieve records via SQL Select query–read-only access via DataReader & field namesString sql, fn, ln; Customer c;sql = "Select * From Customers Order By LastName Asc, FirstName Asc;";OleDbCommand dbCmd;OleDbDataReader dbReader;dbCmd = new OleDbCommand(sql, dbConn);dbReader = dbCmd.ExecuteReader();while ( dbReader.Read() ) // retrieve records one-by-one… { fn = String.valueOf(dbReader.get_Item("FirstName")); ln = String.valueOf(dbReader.get_Item("LastName")); c = new Customer(fn, ln); this.listBox1.get_Items().Add(c);}String sql, fn, ln; Customer c;sql = "Select * From Customers Order By LastName Asc, FirstName Asc;";OleDbCommand dbCmd;OleDbDataReader dbReader;dbCmd = new OleDbCommand(sql, dbConn);dbReader = dbCmd.ExecuteReader();while ( dbReader.Read() ) // retrieve records one-by-one… { fn = String.valueOf(dbReader.get_Item("FirstName")); ln = String.valueOf(dbReader.get_Item("LastName")); c = new Customer(fn, ln); this.listBox1.get_Items().Add(c);}data readerrecordrecordrecordfield name of data value in current recordcommandconnection11-13MicrosoftIntroducing CS using .NETJ# in Visual Studio .NET(3) Close connection•Be sure to close reader and connection…–to flush pending updates (in general)–so others can access DB (connections are limited resources)dbReader.Close();dbConn.Close();dbReader.Close();dbConn.Close();11-14MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETPart 3•Data-driven application design…11-15MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETData Tier design•Recall N-Tier design…GUI.exeDataAccessimport System.Data.*;import System.Data.OleDb.*;public class DataAccess{ private String sConnection; public DataAccess(String filename) { this.sConnection = String.Format("Provider=...;Data Source={0}{1}", System.AppDomain.get_CurrentDomain().get_BaseDirectory(), filename); } public java.util.ArrayList getCustomers() throws System.Exception { . . . }import System.Data.*;import System.Data.OleDb.*;public class DataAccess{ private String sConnection; public DataAccess(String filename) { this.sConnection = String.Format("Provider=...;Data Source={0}{1}", System.AppDomain.get_CurrentDomain().get_BaseDirectory(), filename); } public java.util.ArrayList getCustomers() throws System.Exception { . . . }11-16MicrosoftIntroducing CS using .NETJ# in Visual Studio .NETGuaranteed close?•Ensure reader / DB closed via try-catch-finally pattern–and *note*


View Full Document

UIC CS 340 - Lecture 11- DB-Programming

Documents in this Course
Load more
Download Lecture 11- DB-Programming
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Lecture 11- DB-Programming and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Lecture 11- DB-Programming 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?