Coding ADO NET Objects Connection Command Data Reader

Coding ADO. NET Objects: Connection, Command, Data. Reader

A Simplified View of ADO. Net Objects Ado. Net Data Provider Adapter Connection Dataset SQL Updates Command Reader Data Consumer Win. Form Web. Form Results of SQL Selects

Connection with a Connection Object • A connection object represents a unique session with a data source. • Property: – Connection string: string used to open a database. • Data source, OLE DB provider, password, if any, security, etc. • Methods: – Use the Open/Close method to open/close a connection.

Connection String • Containing information about database, OLE DB provider, password, if any, security, etc. • For Jet database: – ="Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales. DB. mdb“ • For Access 2007: – = "Provider=Microsoft. ACE. OLEDB. 12. 0; Data Source=C: Sales. DB 2007. accdb“ • For SQL Server Express – "Data Source=DCHAO 100 Lsqlexpress; Initial Catalog=test. SQL 08; Integrated Security=True"

Connection Object • Example 1: – dim str. Conn as string ="Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales. DB. mdb" – dim obj. Conn as new Oledb. Connection(str. Conn) – obj. Conn. open() • Example 2: – dim str. Conn as string ="Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales. DB. mdb" – dim obj. Conn as new Oledb. Connection() – obj. Connection. String=str. Conn – obj. Conn. open() • Object Browser: System. Data. Ole. DB/oledb. Connection/New • Basic Methods: – Open, Close – Begin. Transaction

Command Object • The command object allows us to execute a SQL statement. • Properties: – – Command. Type: Text, stored procedure, table. Direct Command. Text: SQL statement, procedure name, table name Connection Other properties: • Parameters • Basic Methods: – Execute. Reader: Creates a Data. Reader object that contains the results of the query. – Execute. Scalar: Executes the query, and returns the first column of the first row in the result set returned by the query. Typically used to execute a SQL aggregate function such as SUM, MAX, MIN, etc. – Execute. Non. Query: Change the data in a database without using a Data. Set by executing SQL’s UPDATE, INSERT, or DELETE statements.

Data. Reader Object • It is read-only and forward-only cursor. • Basic Methods: – Read: Reads the current record and advances the pointer to the next record. It returns False if no more record in the Data. Reader. – Close: Closes the data. Reader. • Property: – Has. Rows – Boolean, indicates whether this System. Data. Common. Db. Data. Reader contains one or more rows.

Execute. Reader Example dim str. Conn as string= "Provider=Microsoft. ACE. OLEDB. 12. 0; Data Source=C: Sales. DB 2007. accdb“ dim obj. Conn as new Oledb. Connection(str. Conn) dim str. SQL as string = "select * from customer; " dim obj. Comm as new Oledb. Command(str. SQL, obj. Conn) dim Results as string obj. Conn. open() dim obj. Data. Reader as oledb. Data. Reader obj. Data. Reader=obj. Comm. execute. Reader() Note: No NEW in the Dim statement when define a data. Reader object.

Import Name. Space • The Imports statement must appear before all other declarations in a file and cannot appear inside a class or module declaration. – Imports System. Data. Ole. Db – Public Class Form 1 • Note: To import a name. Space for all forms/modules in a project: – Use Project Property Page: Imports

Read Records in a Data. Reader • dim Results as string • do while obj. Data. Reader. Read()=true Results+=obj. Data. Reader("cid") + “ “ + obj. Data. Reader("Cname") + vb. Cr. LF • loop • Textbox 1. text=Results • Note: obj. Data. Reader. Item(0) • Note: obj. Data. Reader. Item(“cid”) • Note: The Read method will (1) read a record, (2) advance pointer, (3) return true/false to indicate if more records exisit.

Add Items from a Data. Reader to a Listbox Dim str. Conn As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; Data Source=C: Sales. DB 2007. accdb“ Dim obj. Conn As New Ole. Db. Connection(str. Conn) Dim str. SQL As String = "select cid from customer; " Dim obj. Comm As New Ole. Db. Command(str. SQL, obj. Conn) obj. Conn. Open() Dim obj. Data. Reader As Ole. Db. Data. Reader obj. Data. Reader = obj. Comm. Execute. Reader() Do While obj. Data. Reader. Read() = True LISTBOX 1. Items. Add(obj. Data. Reader("cid")) Loop Note 1: Listbox is not bound. 2. Selected. Item

Display Selected Customer Info in Textbox Private Sub List. Box 1_Selected. Index. Changed(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles List. Box 1. Selected. Index. Changed Dim str. Conn As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; Data Source=C: Sales. DB 2007. accdb" Dim obj. Conn As New Ole. Db. Connection(str. Conn) Dim str. SQL As String = "select * from customer where cid='" & List. Box 1. Selected. Item & "'; " Dim obj. Comm As New Ole. Db. Command(str. SQL, obj. Conn) obj. Conn. Open() Dim obj. Data. Reader As Ole. Db. Data. Reader obj. Data. Reader = obj. Comm. Execute. Reader() obj. Data. Reader. Read() Text. Box 1. Text = obj. Data. Reader("cname") Text. Box 2. Text = obj. Data. Reader("rating") obj. Data. Reader. Close() End Sub

Display Selected Customer Info in Textbox Private Sub List. Box 1_Selected. Index. Changed(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles List. Box 1. Selected. Index. Changed Dim str. Conn As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; Data Source=C: Sales. DB 2007. accdb" Dim obj. Conn As New Ole. Db. Connection(str. Conn) Dim str. SQL As String = "select * from customer where cid = '" & List. Box 1. Selected. Item & "'" Dim obj. Comm As New Ole. Db. Command(str. SQL, obj. Conn) obj. Conn. Open() Dim obj. Data. Reader As Ole. Db. Data. Reader obj. Data. Reader = obj. Comm. Execute. Reader() obj. Data. Reader. Read() Text. Box 1. Text = obj. Data. Reader("Cname") Text. Box 2. Text = obj. Data. Reader("rating") End Sub

Using a Parallel Array. List to Store Cname and Rating • Array. List – Add – Clear – Remove – Item – Index. Of

Using a Parallel Array. List Dim Cname. List, Rating. List As New Array. List Private Sub Form 1_Load(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles My. Base. Load Dim str. Conn As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; Data Source=C: Sales. DB 2007. accdb" Dim obj. Conn As New Ole. Db. Connection(str. Conn) Dim str. SQL As String = "select * from customer; " Dim obj. Comm As New Ole. Db. Command(str. SQL, obj. Conn) obj. Conn. Open() Dim obj. Data. Reader As Ole. Db. Data. Reader obj. Data. Reader = obj. Comm. Execute. Reader() Do While obj. Data. Reader. Read() = True List. Box 1. Items. Add(obj. Data. Reader("cid")) Cname. List. Add(obj. Data. Reader("cname")) Rating. List. Add(obj. Data. Reader("Rating")) Loop End Sub

Use Listbox Selected. Index Private Sub List. Box 1_Selected. Index. Changed(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles List. Box 1. Selected. Index. Changed Text. Box 1. Text = Cname. List(List. Box 1. Selected. Index) Text. Box 2. Text = Rating. List(List. Box 1. Selected. Index) End Sub

Use SQL to Retrieve Summaries Dim str. Conn As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; Data Source=C: Sales. DB 2007. accdb“ Dim obj. Conn As New Ole. Db. Connection(str. Conn) Dim str. SQL As String = "select count(cid) as cust. Count from customer; " Dim obj. Comm As New Ole. Db. Command(str. SQL, obj. Conn) Dim Results As String obj. Conn. Open() Dim obj. Data. Reader As Ole. Db. Data. Reader obj. Data. Reader = obj. Comm. Execute. Reader() If obj. Data. Reader. Read = True Then Message. Box. Show(obj. Data. Reader("cust. Count"). To. String) End If Note: Alias ----- select count(cid) as cust. Count

Using Command’s Execute. Scalar to Run SQL Aggregates • Dim str. Conn As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; Data Source=C: Sales. DB 2007. accdb" • Dim obj. Conn As New Oledb. Connection(str. Conn) • Dim str. SQL As String = "select count(cid) from customer'; " • Dim obj. Comm As New Oledb. Command(str. SQL, obj. Conn) • obj. Conn. Open() • Text. Box 1. Text = obj. Comm. Execute. Scalar

Execute. Scalar Example Private Sub List. Box 1_Selected. Index. Changed(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles List. Box 1. Selected. Index. Changed Dim str. Conn As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; Data Source=C: Company. accdb" Dim obj. Conn As New Ole. Db. Connection(str. Conn) Dim str. SQL As String Select Case List. Box 1. Selected. Index Case 0 str. SQL = "select count(eid) from emp'; " Case 1 str. SQL = "select Max(Salary) from emp'; " Case 2 str. SQL = "select Min(Salary) from emp'; " End Select Dim obj. Comm As New Oledb. Command(str. SQL, obj. Conn) obj. Conn. Open() Text. Box 1. Text = obj. Comm. Execute. Scalar End Sub

Testing for Null Is. DBNull Function If obj. Data. Reader. Read = True Then If Is. DBNull(obj. Data. Reader("cust. Count")) Then Text. Box 1. Text = 0 Else Text. Box 1. Text = obj. Data. Reader("cust. Count"). To. String End If Note: The keyword “Nothing” tests if an object is null.

Null Value Example Private Sub List. Box 1_Selected. Index. Changed(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles List. Box 1. Selected. Index. Changed Dim str. Conn As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales. DB. mdb" Dim obj. Conn As New Ole. Db. Connection(str. Conn) Dim str. SQL As String = "select * from customer where cid = '" & List. Box 1. Selected. Item & "'" Dim obj. Comm As New Ole. Db. Command(str. SQL, obj. Conn) obj. Conn. Open() Dim obj. Data. Reader As Ole. Db. Data. Reader obj. Data. Reader = obj. Comm. Execute. Reader() obj. Data. Reader. Read() Text. Box 1. Text = obj. Data. Reader("Cname") Text. Box 2. Text = obj. Data. Reader("rating") End Sub ‘ Statement won’t work if Rating is null in database

Setting a Field to Null with a Update Statement Dim str. SQLUpd As String = "Update Customer Set Rating = null Where cid = '" & List. Box 1. Selected. Item & "'"

Error Handling Try obj. Conn. Open() Dim obj. Data. Reader As Ole. Db. Data. Reader obj. Data. Reader = obj. Comm. Execute. Reader() Do While obj. Data. Reader. Read() = True Results += obj. Data. Reader("cid") + " " + obj. Data. Reader("Cname") + vb. Cr. Lf Loop Text. Box 1. Text = Results obj. Conn. Close() Catch except As System. Exception Message. Box. Show(except. Message) End Try

Possible Database Errors • • SQL syntax errors Database connection not open Null Violate database constraints • • Referential integrity Field data type and length Primary key constraint Invalid data – database may have validation rules.

Use Command Object’s Execute. Non. Query to Insert A New Record 1. Create unbound text boxes to enter new record. 2. Add an Insert button with the following handler Dim str. SQLInsert As String str. SQLInsert = "Insert into Customer values ('" str. SQLInsert = str. SQLInsert & Text. Box 1. Text & "', '" & Text. Box 2. Text & "', '" str. SQLInsert = str. SQLInsert & Text. Box 3. Text & "', '" & Text. Box 4. Text & "')" Dim obj. Comm. Insert As New Ole. Db. Command(str. SQLInsert, obj. Conn) obj. Comm. Insert. Execute. Non. Query()

Execute. Non. Query Example dim str. SQLUpd as string="Update customer set rating = ‘A’” str. SQLUpd=str. SQLUpd & " where cname='" & Cname. List. selected. Item. text & "'“ Dim obj. Comm. Upd As New Ole. Db. Command(str. SQLUpd, obj. Conn) obj. Comm. Upd. Execute. Non. Query() Note: How many records affected by this command? Execute. Non. Query method returns an integer indicating the number of record affected by the update command. Messagebox. Show(obj. Comm. Upd. Execute. Non. Query())

Demo • Create a project that do the following tasks: – Use a Data. Reader to retrieve customer IDs and populate a listbox. – Select a new rating from radio buttons for the selected customer. – Update customer’s rating using the Execute. Non. Query method of a Command object.

Declare Ole. DB objects and create listbox Imports System. Data. Ole. Db Public Class Form 3 Inherits System. Windows. Form Dim str. Conn As String = "Provider=Microsoft. ACE. OLEDB. 12. 0; Data Source=C: Sales. DB 2007. accdb" Dim obj. Conn As New Ole. Db. Connection(str. Conn) Dim str. SQL As String = "select cid from customer; " Dim obj. Comm As New Ole. Db. Command(str. SQL, obj. Conn) Private Sub Form 3_Load(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles My. Base. Load obj. Conn. Open() Dim obj. Data. Reader As Ole. Db. Data. Reader obj. Data. Reader = obj. Comm. Execute. Reader() Do While obj. Data. Reader. Read() = True List. Box 1. Items. Add(obj. Data. Reader("cid")) Loop obj. Conn. Close() End Sub

Update customer rating Private Sub Button 1_Click(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles Button 1. Click obj. Conn. Open() Dim new. Rating As String If Radio. Button 1. Checked = True Then new. Rating = "A" Else. If Radio. Button 2. Checked Then new. Rating = "B" Else new. Rating = "C" End If Dim str. SQLUpd As String = "Update customer set rating = '" & new. Rating & "'" str. SQLUpd = str. SQLUpd & " where cid='" & List. Box 1. Selected. Item & "'" Dim obj. Comm. Upd As New Ole. Db. Command(str. SQLUpd, obj. Conn) obj. Comm. Upd. Execute. Non. Query() obj. Conn. Close() End Sub

Working with ODBC Data Source Imports System. Data. Odbc Public Class Form 1 Private Sub Form 1_Load(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles My. Base. Load Dim str. Conn As String = "DSN=My. Sales. DB" Dim obj. Conn As New Odbc. Connection(str. Conn) Dim str. SQL As String = "select cname from customer; " Dim obj. Comm As New Odbc. Command(str. SQL, obj. Conn) obj. Conn. Open() Dim obj. Data. Reader As Odbc. Data. Reader obj. Data. Reader = obj. Comm. Execute. Reader() Do While obj. Data. Reader. Read() = True List. Box 1. Items. Add(obj. Data. Reader("cname")) Loop End Sub End Class

Working with SQL Server Data Source Imports System. Data. Sql. Client Public Class Form 9 Private Sub Form 9_Load(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles My. Base. Load Dim str. Conn As String = "Data Source=DCHAOD 630SQLExpress 08; Initial Catalog=Emp. DB; Integrated Security=True; Pooling=False" Dim obj. Conn As New Sql. Connection(str. Conn) Dim str. SQL As String = "select * from emptable; " Dim obj. Comm As New Sql. Command(str. SQL, obj. Conn) obj. Conn. Open() Dim obj. Data. Reader As Sql. Data. Reader obj. Data. Reader = obj. Comm. Execute. Reader() Do While obj. Data. Reader. Read() = True List. Box 1. Items. Add(obj. Data. Reader("eid")) Loop End Sub End Class