Lecture 11 programmatic database access with ado net
This presentation is the property of its rightful owner.
Sponsored Links
1 / 31

Lecture 11: Programmatic Database Access with ADO.NET PowerPoint PPT Presentation


  • 70 Views
  • Uploaded on
  • Presentation posted in: General

Lecture 11: Programmatic Database Access with ADO.NET. Objectives.

Download Presentation

Lecture 11: Programmatic Database Access with ADO.NET

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Lecture 11 programmatic database access with ado net

Lecture 11:Programmatic Database Access with ADO.NET


Objectives

Objectives

“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

  • DataSets


Part 1

Part 1

  • Architecture of ADO.NET…


Relational technology

Relational technology

  • ADO.NET is designed to access relational databases

  • Example:

    • Sales database with customers, orders, and products


Architecture

Architecture

  • ADO.NET architecture based on data providers

    • data providers encapsulate DB-specific details

.NET Programmer

ADO.NET

Data Provider

DB


Existing data providers

Existing 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.


Ado net object model

ADO.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-specific

provider-neutral

System.Data

System.Data.Common

SQL Server

System.Data.SqlClient

other DBs, e.g. MS Access

System.Data.OleDb

ODBC (Open Database Connectivity)

System.Data.Odbc


Part 2

Part 2

  • Basic database access…


Overview of database access

Overview of database access

  • Three steps:

    • open connection to database

    • execute SQL to retrieve records / update DB

    • close connection


1 open connection

connection

(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)

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());


Building connection strings

Building 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.htm


2 retrieve records

data reader

command

connection

field name of data value in current record

record

record

record

(2) Retrieve records

  • Retrieve records via SQL Select query

    • read-only access via DataReader & field names

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);

}


3 close connection

(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();


Part 3

Part 3

  • Data-driven application design…


Data tier design

Data Tier design

  • Recall N-Tier design…

DataAccess

GUI.exe

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

{

.

.

.

}


Guaranteed close

Guaranteed close?

  • Ensure reader / DB closed via try-catch-finally pattern

    • and *note* that we want to catch .NET System.Exception objects, not Java java.lang.Exception objects…

OleDbConnection dbConn = null;

OleDbDataReader dbReader = null;

try {

dbConn = new OleDbConnection(…);

dbConn.Open();

.

.

.

}

catch(System.Exception ex) {

System.Diagnostics.Debug.WriteLine("DB error: " + ex.get_Message());

throw new System.Exception("Data Error!", ex);

}

finally { // always executes whether we succeed or throw exception…

if (dbReader != null) dbReader.Close();

if (dbConn != null) dbConn.Close();

}


Part 4

Part 4

  • Updating a database…


Executing action queries

Executing action queries

  • Use action queries when you need to modify a DB

    • updates

    • inserts

    • deletes

  • Execute action queries via ExecuteNonQuery method…


Basic idea

Basic idea

  • Open, execute, and close:

String sConnection, sql;

sConnection = "...";

sql = "...";

OleDbConnection dbConn;

OleDbCommand dbCmd;

dbConn = new OleDbConnection(sConnection);

dbCmd = new OleDbCommand(sql, dbConn);

int rows;

dbConn.Open();

rows = dbCmd.ExecuteNonQuery(); // returns # of rows affected…

dbConn.Close();

if (rows != 1) // sanity check to make sure it worked...

throw new System.Exception("Query ran but failed to update DB?!");


Dynamic sql

Dynamic SQL

  • Most of the time you'll need to build SQL dynamically

    • i.e. based on input values from the user

  • Example:

    • delete the selected customer…

String fn, ln;

Customer c;

c = this.listBox1.get_SelectedItem();

fn = c.firstName;

ln = c.lastName;

sql = "...";


Dynamic string building

Dynamic string building

  • Dynamic SQL requires dynamic string building

  • Example:

    • build dynamic SQL to delete selected customer…

    • don't forget the delimiters for strings & dates!

String sql;

sql = String.Format("Delete From Customers Where " +

"FirstName='{0}' And LastName='{1}';",

fn, ln);


Example

Example

  • Delete selected customer…

private void listBox1_SelectedIndexChanged(…)

{

String fn, ln, sql; int rows; Customer c;

c = (Customer) this.listBox1.get_SelectedItem();

if (c == null) return; // nothing selected…

fn = c.firstName;

ln = c.lastName;

sql = String.Format("Delete From Customers Where " +

"FirstName='{0}' And LastName='{1}';", fn, ln);

.

.

.

dbConn.Open();

rows = dbCmd.ExecuteNonQuery(); // delete!

dbConn.Close();

if (rows != 1)

throw new System.Exception("Query ran but failed to delete?!");

this.listBox1.get_Items().Remove(c); // update GUI!

MessageBox.Show("Deleted!");

}


Lecture 11 programmatic database access with ado net

Oops!

  • Try to delete "O'Dahl, Kathie"…

  • What happens?


Escaping delimiters

Escaping delimiters

  • Most common DB programming mistake:

    • forgetting to escape delimiter characters…

  • Solution?

    • just replace ' with 2 in a row, i.e. ' '

private void listBox1_SelectedIndexChanged(…)

{

String fn, ln, sql; int rows; Customer c;

c = (Customer) this.listBox1.get_SelectedItem();

if (c == null) return; // nothing selected…

fn = c.firstName;

ln = c.lastName;

fn = fn.Replace("'", "''");

ln = ln.Replace("'", "''");

sql = String.Format("Delete From Customers Where " +

"FirstName='{0}' And LastName='{1}';", fn, ln);


Part 5

Part 5

  • DataSets…


Datasets

DataSets

  • DataSets are an in-memory data structure

    • easily filled with data from a database

    • easily passed around

    • easily displayed in a GUI app

DataAdapter

Command

Connection

DB

DataSet

  • DataSet mirrors the database

    • data forms a temporary table called "Table" within DataSet

"Table"


Filling a dataset

Filling a DataSet

  • DataAdapter object is used to fill a DataSet…

  • Example:

    • fill DataSet with all product data

"Table"

sql = "Select * From Products Order By Name Asc;";

.

.

.

DataSet ds;

OleDbDataAdapter adapter;

ds = new DataSet();

adapter = new OleDbDataAdapter(dbCmd);

dbConn.Open();

adapter.Fill(ds);

dbConn.Close();


Datagrid display

DataGrid display

  • DataSet can be bound to DataGrid control for easy display

    • one line of code!

.

.

.

this.dataGrid1.SetDataBinding(ds, "Table");


Datagrid is a powerful control

DataGrid is a powerful control

  • By default, DataGrid is read/write

    • user can modify data

    • user can add rows

    • user can delete rows

  • However, all changes are local to DataSet

    • to flush changes back to DB, reconnect and update…


Flushing changes back to database

Flushing changes back to database

  • Reconnect, and apply adapter's Update() method

    • use CommandBuilder object to generate necessary SQL for you

// retrieve existing data set from grid…

ds = (DataSet) this.dataGrid1.get_DataSource();

.

.

.

OleDbCommandBuilder cmdBuilder;

cmdBuilder = new OleDbCommandBuilder(adapter);

dbConn.Open();

adapter.Update(ds); // this will throw exception if update(s) conflict…

dbConn.Close();


Summary

Summary

  • Databases are a critical component of most business apps

  • SQL is the standard programming language for databases

  • Database programming is based on framework classes

    • in .NET, those classes are called ADO.NET

    • the more you know about SQL the better


  • Login