Introduction to ADO NET ADO NET Objects NET

Introduction to ADO. NET

ADO. NET Objects. NET Applications Data Set Data Reader Command Object Connection Object Managed Data Provider (OLEDB) Database

Connection with a Connection Object • A connection object represents a unique session with a data source. • Connection string: database, OLE DB provider, password, if any, security, etc. • Use the Open/Close method to open/close a connection. • Manage transaction: Begin. Transaction(), Commit, roll. Back.

Connection String • Containing information about database, OLE DB provider, password, if any, security, etc. • For Jet database: – Provider=Microsoft. Jet. OLEDB. 4. 0; Persist Security info = False; Data Source=c: …Nwind. mdb

Data Link Properties • Access permissions – Read—Read only. – Read. Write—Read and write. – Share Deny None—Neither read nor write access can be denied to others. – Share Deny Read—Prevents others from opening in read mode. – Share Deny Write—Prevents others from opening in write mode. – Share Exclusive—Prevents others from opening in read/write mode. – Write—Write only.

Providers • MSDASQL – Microsoft OLE DB Provider for ODBC • • Microsoft. Jet. OLEDB. 3. 51 Microsoft. Jet. OLEDB. 4. 0 MSDAORA – For Oracle SQLOLEDB – Microsoft SQL Server

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

Command Object • The command object allows us to execute a SQL statement. • Properties: – Command. Type: SQL or stored procedure – Command. Text: SQL statement – Connection • Basic Methods: – Execute. Reader: Creates a Data. Reader object that contains the results of the query. – Execute. Non. Query: Execute SQL’s INSERT, DELETE, UPDATE statements.

Execute. Reader Example dim str. Conn as string ="Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales 2 k. mdb" 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()

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 – Module 1 – Public Sub Main() – Dim my. File As Stream. Reader – Or: – Imports System. Data. Ole. Db – Public Class Form 1

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. – Close: Closes the data. Reader.

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

Add Items from a Data. Reader to a Listbox Dim str. Conn As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales 2 k. mdb" Dim obj. Conn As New Ole. Db. Connection(str. Conn) Dim str. SQL As String = "select cname 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("cname")) Loop

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

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. Jet. OLEDB. 4. 0; Data Source = c: sales 2 k. mdb" 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

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

MS Flex. Grid • A spreadshseet like form contains rows and columns. A box where a row and column intersect is known as a cell which can set to contain a string value. • To add a Flex. Grid – Right-click on the Windows. Forms tab of the toolbox. – Click on the Customize Tool. Box – Under the COM Component, click the check box associated with the MS Flex. Grid.

Flex. Grid Properties • Rows: The number of rows in a grid. Can be changed as new records are added. • Cols: The number of columns. • Row, Col: – Zero based index to the cell. • Text: – Ax. MSFlex. Grid 1. Row = 1 – Ax. MSFlex. Grid 1. Col = 0 – Ax. MSFlex. Grid 1. Text = “Hi”

Display Records Retrieved by a Data. Reader in a MS Flex. Grid 1. Create the Reader: Dim str. Conn As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales 2 k. mdb" 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()

2. Create Flex. Grid’s Label: Dim row. ID As Integer row. ID = 0 Ax. MSFlex. Grid 1. Rows = 1 Ax. MSFlex. Grid 1. Cols = 4 Ax. MSFlex. Grid 1. Row = row. ID Ax. MSFlex. Grid 1. Col = 0 Ax. MSFlex. Grid 1. Text = "Customer. ID" Ax. MSFlex. Grid 1. Col = 1 Ax. MSFlex. Grid 1. Text = "Name" Ax. MSFlex. Grid 1. Col = 2 Ax. MSFlex. Grid 1. Text = "City" Ax. MSFlex. Grid 1. Col = 3 Ax. MSFlex. Grid 1. Text = "Rating"

3. Display records in cells: Ax. MSFlex. Grid 1. Rows += 1 row. ID += 1 Do While obj. Data. Reader. Read() = True Ax. MSFlex. Grid 1. Row = row. ID Ax. MSFlex. Grid 1. Col = 0 Ax. MSFlex. Grid 1. Text = obj. Data. Reader("cid") Ax. MSFlex. Grid 1. Col = 1 Ax. MSFlex. Grid 1. Text = obj. Data. Reader("cname") Ax. MSFlex. Grid 1. Col = 2 Ax. MSFlex. Grid 1. Text = obj. Data. Reader("city") Ax. MSFlex. Grid 1. Col = 3 Ax. MSFlex. Grid 1. Text = obj. Data. Reader("rating") row. ID += 1 Ax. MSFlex. Grid 1. Rows += 1 Loop obj. Conn. Close()

Data. Set Object • A Data. Set object can hold several tables and relationships between tables. • A Data. Set is a set of disconnedted data. Data is extracted from the database and stored in the Data. Set object. Updates to the Data. Set must copy back to the database to make the changes permanent.

Data. Set and Related Objects • Data. Set: Can contain multiple tables and relationships. • Data. Table object: Represents a table in the dataset. • Data. Adapter: This the object used to pass data between the database and the dataset. The Fill method copies the data into the dataset, and the Update method copies the updates back into the database. • Data. View: This represents a specific view of the Data. Tables held in the dataset.

Data. Set and Related Objects Data. View Data. Set Data. Table Command Data. Adapter Connection

Structure of a Dataset Tables Data table Rows Data Row Columns Data Column Constraints Relations Data Relation Constraint

Reading Data into a Table • dim str. Conn as string ="Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales 2 k. mdb" • dim obj. Conn as new Oledb. Connection(str. Conn) • dim str. SQL as string = "select * from customer; " • dim obj. Data. Set as new Dataset() • dim obj. Adapter as new Oledb. Data. Adapter(str. SQL, obj. Conn) • obj. Adapter. Fill(obj. Data. Set, "Cust")

Binding a Table in a Dataset to a Data. Grid Imports System. Data. Ole. Db Public Class Form 4 Inherits System. Windows. Form Dim str. Conn As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales 2 k. mdb" Dim obj. Conn As New Ole. Db. Connection(str. Conn) Dim obj. Data. Set As New Data. Set() Private Sub Form 4_Load(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles My. Base. Load Dim str. SQL As String = "select * from customer; " Dim obj. Adapter As New Ole. Db. Data. Adapter(str. SQL, obj. Conn) obj. Adapter. Fill(obj. Data. Set, "Cust") Data. Grid 1. Data. Source = obj. Data. Set Data. Grid 1. Data. Member = "Cust" End Sub

Data. View Object • The Data. View object exposes a complete table or a subset of the records from a table. – Dim Obj. Data. View As New Data. View() – Obj. Data. View. Table = obj. Data. Set. Tables("Cust") • Properties: – Row. Filter – obj. Data. View. rowfilter="rating='" & Ratinglist. Selected. Item. Text & "'“ • Data. View can be used as a Data. Source in data binding.

Data. View Example Dim Obj. Data. View As New Data. View() 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 Obj. Data. View. Table = obj. Data. Set. Tables("Cust") Obj. Data. View. Row. Filter = "rating='" & new. Rating & "'" Data. Grid 1. Visible = True Data. Grid 1. Data. Source = Obj. Data. View

Defining an Adapter without A Command Object • dim str. Conn as string ="Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales 2 k. mdb" • dim obj. Conn as new Oledb. Connection(str. Conn) • dim str. SQL as string = "select * from customer; " • dim obj. Data. Set as new Dataset() • dim obj. Adapter as new Oledb. Data. Adapter(str. SQL, obj. Conn) • obj. Adapter. Fill(obj. Data. Set, "Cust")

Defining an Adapter with A Command Object • • • dim str. SQLCust as string = "select * from customer; " dim obj. Comm as new Oledb. Command() dim obj. Adapter as new Oledb. Data. Adapter() dim obj. Data. Set as new Dataset() obj. Comm. Connection=obj. Conn obj. Command. Type=COmmand. Type. Text obj. Command. Text=str. SQLCust obj. Adapter. Select. Command=obj. Comm obj. Conn. open() obj. Adapter. Fill(obj. Data. Set, "Customer") • Note: Adapter has Select. Command, Insert. Command, Delete. Command, Update. Command properties (Check Object Browser).

Creating Multiple Tables in A Data. Set • dim str. SQLCust as string = "select * from customer; " • dim str. SQLOrder as string ="select * from orders; " • dim obj. Comm as new Oledb. Command() • dim obj. Adapter as new Oledb. Data. Adapter() • dim obj. Data. Set as new Dataset() • obj. Comm. Connection=obj. Conn • obj. Command. Type=COmmand. Type. Text • obj. Command. Text=str. SQLCust • obj. Adapter. Select. Command=obj. Comm • obj. Conn. open() • obj. Adapter. Fill(obj. Data. Set, "Customer") • obj. Comm. COmmand. Text=str. SQLOrder • obj. Adapter. Fill(obj. Data. Set, "Orders")

Adding Relationship to a Dataset • The Dataset object has a Relations property. It is a collection of Data. Relations. We can use a relationship to enforce the referential integrity. • To define a Data. Relation: – Data. Rel. Obj=Data. Relation(Relation. Name, Parent. Table Field, Child. Table. Field) • obj. Rel = New Data. Relation("cust. Order", obj. Dataset. tables("customer"). columns("cid"), obj. Dataset. tables("orders"). columns("cid"))

Data. Table’s Rows Property • This is a collection of all the records in a table, a collection of Data. Row objects. • The Data. Row object has a Get. Child. Rows method that returns a collection of rows from another table that are related as child rows to this row.

Access Rows in a Data. Row Collection • dim obj. Table as Data. Table = obj. Dataset. Tables("Customer") • dim obj. Row as Data. Row • For each obj. Row in obj. Table. Rows • str. Result=str. Result+“ " & obj. Row("cid") & " " & obj. Row("cname") & vb. Cr. Lf • Next

Displaying Parent/Child Records in a Relation • Define the relation. • Specify the relation in the Get. Child. Rows method of the Data. Row object.

Imports System. Data. Ole. Db Public Class Form 1 Inherits System. Windows. Form Dim str. Conn As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales 2 k. mdb" Dim obj. Conn As New Oledb. Connection(str. Conn) Dim str. SQLCust As String = "select * from customer; " Dim str. SQLOrder As String = "select * from orders; " Dim obj. Comm As New Oledb. Command() Dim obj. Adapter As New Oledb. Data. Adapter() Dim obj. Data. Set As New Data. Set() Dim obj. Rel As Data. Relation Dim str. Result As String Dim row. Index As Integer = 0 Private Sub Form 1_Load(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles My. Base. Load obj. Comm. Connection = obj. Conn obj. Command. Type = Command. Type. Text obj. Command. Text = str. SQLCust obj. Adapter. Select. Command = obj. Comm obj. Conn. Open() obj. Adapter. Fill(obj. Data. Set, "Customer") obj. Command. Text = str. SQLOrder obj. Adapter. Fill(obj. Data. Set, "Orders") obj. Rel = New Data. Relation("cust. Order", obj. Data. Set. Tables("Customer"). Columns("cid"), obj. Data. Set. Tables("Orders"). Columns("cid")) obj. Data. Set. Relations. Add(obj. Rel) Call show. Child(row. Index)

Private Sub show. Child(By. Val Row. Index) Text. Box 1. Text = obj. Data. Set. Tables("customer"). Rows(Row. Index). Item("cid") Text. Box 1. Text = obj. Data. Set. Tables("customer"). Rows(Row. Index). Item("cname") Dim obj. Order. Rel As Data. Relation = obj. Data. Set. Tables("customer"). Child. Relations("cust. Order") Dim str. Result As String Dim obj. Child. Row As Data. Row For Each obj. Child. Row In obj. Data. Set. Tables("customer"). Rows(Row. Index). Get. Child. Rows(obj. Order. Rel) str. Result = str. Result + obj. Child. Row("oid") + " " + obj. Child. Row("odate") + vb. Cr. Lf Next Text. Box 3. Text = str. Result End Sub ****Note****: How to prevent EOF error? Private Sub Button 1_Click(By. Val sender As System. Object, By. Val e As System. Event. Args) Handles Button 1. Click row. Index += 1 Call show. Child(row. Index) End Sub

Display Child Records in Grid • This technique uses the fact that the Data. View of a table can be used as a Data. Source in binding a data grid. • Assuming a Customer/Orders relationship, for each customer record: – Use Table object’s Clone method to create a temporary child table structure. – Use Table object’s Import. Row method to import child records to the clone table. – Use the clone table’s Default. View to bind the Data. Grid.

Dim obj. Row As Data. Row Dim dr. Found As Data. Row dr. Found = obj. Data. Set. Tables("Customer"). Rows("0") Text. Box 1. Text = dr. Found. Item("cid") Text. Box 2. Text = dr. Found. Item("cname") Dim child. Table As Data. Table child. Table = obj. Data. Set. Tables("orders"). Clone For Each obj. Row In dr. Found. Get. Child. Rows(obj. Order. Rel) child. Table. Import. Row(obj. Row) Next Data. Grid 1. Data. Source = child. Table. Default. View

• Can we use child table’s view to display child records in a grid?

Multiple Forms: Where to import the oledb, and where to create the oledb objects Imports System. Data. Ole. Db Module 1 Public str. Conn As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source = c: sales 2 k. mdb" Public obj. Conn As New Ole. Db. Connection(str. Conn) Public str. SQLCust As String = "select * from customer; " Public str. SQLOrder As String = "select * from orders; " Public obj. Comm As New Ole. Db. Command() Public obj. Adapter As New Ole. Db. Data. Adapter() Public obj. Data. Set As New Data. Set() Public obj. Rel As Data. Relation Public str. Result As String Public row. Index As Integer = 0 Public Obj. Data. View As New Data. View() Public Sub main() obj. Comm. Connection = obj. Conn obj. Command. Type = Command. Type. Text obj. Command. Text = str. SQLCust obj. Adapter. Select. Command = obj. Comm obj. Conn. Open() obj. Adapter. Fill(obj. Data. Set, "Customer") obj. Command. Text = str. SQLOrder obj. Adapter. Fill(obj. Data. Set, "Orders") End Sub End Module

Create Web Data Form Using ADO. Net Programming Model • Demo: – Create ADO objects and bind them to a data. Grid. – Data. View

Using Tool. Box/Data to Define Ado. Net Objects • Connection: Tool. Box/Data/Oledb. Coonection – Connection. String • Command: Tool. Box/Data/Oledb. Command – Connection – Command. Type – Command. Text

Using DBMS Views • Create views (queries) with DBMS • Create commands to select records from views.