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 (Under the Advanced Tab of the Connection Dialog Window) • 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 – 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 Note: 1. Listbox is not bound. 2. Selected. Item
To Reuse a Data. Reader • obj. Data. Reader. Close() • obj. Data. Reader = obj. Comm. Execute. Reader()
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 • Violate database constraints • Referential integrity • Field data type and length • Primary key constraint
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()
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. • Table’s Default. View property: – Dim Obj. Data. View As New Data. View() – Obj. Data. View. Table = obj. Data. Set. Tables("Cust") – Or: Obj. Data. View = obj. Data. Set. Tables("Cust"). Default. View • Data. View can be used as a Data. Source in data binding.
Data Binding with Data. View Object 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() 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") Dim Obj. Data. View As New Data. View() Obj. Data. View = obj. Data. Set. Tables("Cust"). Default. View Data. Grid 1. Data. Source = Obj. Data. View
Accessing Records in Data. View Using Data. View and Data. Row. View object’s Item property: objdata. View = obj. Data. Set. Tables("customer"). Default. View Text. Box 1. Text = objdata. View. Item(row. Index). Item(0) Text. Box 2. Text = objdata. View. Item(row. Index). Item(1) Move to the next record: row. Index += 1 Text. Box 1. Text = objdata. View. Item(row. Index). Item(0) Text. Box 2. Text = objdata. View. Item(row. Index). Item(1)
Selecting a Subset of Records with Data. View’s Row. Filter Property • obj. Data. View. Row. Filter = criteria
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")
Binding Multiple Tables to a Data. Grid (Example 1) Data. Grid 1. Data. Source = obj. Data. Set
Binding Multiple Tables to a Data. Grid (Example 2) Dim Obj. Data. View As New Data. View() Dim Table. Name As String If Radio. Button 1. Checked = True Then Obj. Data. View = obj. Data. Set. Tables("Customer"). Default. View Else Obj. Data. View = obj. Data. Set. Tables("Orders"). Default. View End If Data. Grid 1. Data. Source = Obj. Data. View
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) • Dim obj. Rel As Data. Relation • obj. Rel = New Data. Relation("cust. Order", obj. Dataset. tables("customer"). columns("cid"), obj. Dataset. tables("orders"). columns("cid")) • Adding a relation to the dataset: – obj. Data. Set. Relations. Add(obj. Rel)
Creating a Hierarchical Data. Grid Dim obj. Rel As Data. Relation 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) Data. Grid 1. Data. Source = obj. Data. Set. Tables("customer")
Data. Table’s Rows Property • This is a collection of all the records in a table, a collection of Data. Row objects. • Data. Row object’s properties and methods.
Accessing a Record with Index obj. Adapter. Fill(obj. Data. Set, "Customer") Text. Box 1. Text = obj. Data. Set. Tables("Customer"). Rows(row. Index). Item(0) Text. Box 2. Text = obj. Data. Set. Tables("Customer"). Rows(row. Index). Item(1)
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
Data. Row object’s Get. Child. Rows Method • Returns a collection of rows from another table that are related as child rows to this row.
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
Creating Parameter Queries • 1. Specify the parameters in the SQL statement. – Dim str. SQLOrder As String = "select * from orders where cid= ? ; " • 2. Add the parameters to the Command Object’s Parameters collection. – obj. Comm. Parameters. Add("? cid", Ole. Db. Type. Var. Char) • 3. Set parameter’s value. – obj. Adapter. Select. Command. Parameters("? cid"). Value = "1"
Parameter Query Example 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) obj. Conn. Open() Dim str. SQLOrder As String = "select * from orders where cid= ? ; " Dim obj. Comm As New Ole. Db. Command() Dim obj. Adapter As New Ole. Db. Data. Adapter() Dim obj. Data. Set As New Data. Set() obj. Comm. Connection = obj. Conn obj. Command. Type = Command. Type. Text obj. Command. Text = str. SQLOrder obj. Comm. Parameters. Add("? cid", Ole. Db. Type. Var. Char) obj. Adapter. Select. Command. Parameters("? cid"). Value = "1" obj. Adapter. Fill(obj. Data. Set, "Orders") Data. Grid 1. Data. Source = obj. Data. Set Data. Grid 1. Data. Member = "orders"
• 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. Connection – 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.
- Slides: 55