VBNETDataAccesssF07.ppt

Database Processing

• Querying database

• Updating database:

Steps to Retrieve Data

VB6 Data Access

• Two Data Object Library approaches

– DAO (Data Access Objects)

• Original access strategy (up to VB6) • Closely linked to MS Access

• Available in most applications that support database connectivity, but cannot support action queries that change content in some databases

– ADO (ActiveX Data Objects)

• Newer approach • More generic

• Same approach for ASP (VB Script active server pages)

Microsoft Components

• RDO

• (Remote Data Objects): Original component to access databases across a network

• Full-featured, but only available in Microsoft’s high-end enterprise-level programming environments

• Jet Database Engine: VB’s database engine for MS Access & ISAM (Indexed Sequential Access Method) DBMSs

• ODBC (Open Database Connectivity): Interface for SQL relational databases

ADO in VB6

VISUAL BASIC 6.0 APPLICATION

ADO

OLE DB

ODBC

A Simplified View of ADO.Net

Objects

Ado.Net

Data Provider

Connection

Adapter

Command

Reader

Dataset

Data Consumer

WinForm

VB provides tools to display

database tables

• VB provides tools to display database tables

•

Data binding

links tables to controls on forms

– Controls called components establish the link

– A wizard guides you through the process

• We’ll use these data-related components:

–

Data source

– usually a database

–

Binding source

– connects data bound controls to a

dataset

–

Table adapter

– uses SQL to select data

Cont..

• The flow of data from database to application

– Data travels from data source to application

– Application can view/change dataset contents

– Changes to dataset can be written back to the data

source

Creating an ActiveX

Data Control

• To create an ADO data control, you must add a

type library

to your project

• A type library is a library of code that has

programs, such as additional controls, that you

can add to VB IDE and then use in your

ActiveX Options Supported by

Visual Basic 6

• Active documents

– Have both a visual interface and code to process user inputs and actions

• Active programs

– Provide processing for other Web programs, but do not have a visual interface component

• Active controls

ADO.NET Objects

• Connection Object: Represent a connection to the

database.

• Command Object: The command object allows us

to execute a SQL statement or a stored procedure.

• DataReader: It is a read-only and forward-only

pointer into a table to retrieve records.

• DataSet Object: A DataSet object can hold several

tables and relationships between tables.

Data Providers

• ODBC Provider

– Open Database Connectivity

• A driver manager

• Used for relational databases

• OLE DB Provider

– OLE DB interfaces provide applications with uniform access to data stored in diverse information sources, or data stores.

– Access

Data Connection Approaches

• Data Control based

– non-programming

– associated with form based controls (DataGrid,

DataList etc)

– Limited control over data

• Programming based

VB6 ADO Linkage Issues

• Under Project|References

– Microsoft ActiveX Data Objects 2.5 Library

• Under Project|Components (added to Toolbox)

– Microsoft ADO Data Control 6.0 (OLEDB)

– Microsoft Data Grid Control 6.0 (OLEDB)

– Microsoft Data List Controls 6.0 (OLEDB)

Using ODBC

• Windows XP:

• Control Panel /Administrative Tools/DataSource(ODBC)

• Three types of data source names

– User DSN: usable only by you and only on the machine currently using.

– System DSN: Any one using the machine can use.

– File DSN: Can be copied and used by other computers with the same driver installed.

• Demo:

– Excel: Data/Import

VB.NET Database Tools

• Database connection:

– Tools/Connect to database

• Data Source

• Server Explorer

– Data connections: Right click data connection

• Add Connection

– Tables, Views

• Create new SQL Server Database

• Toolbox:Data tab

Creating SQL Server Database

• From Server Explorer, right click data connection and choose:

• Create new SQL Server Database

• Server name:

– LocalServerName\SQLExpress

• Add new table: Right click Tables and choose Add New Table

• Add rows: Right click the table name and choose Show table data.

How to create ADO.Net objects?

• Automatically generated when creating

data bound form.

– Form wizard

• Using Data Adapter Wizard

• Using code:

– Example:

– dim strConn as string ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\sales2k.mdb"

Data Binding

• Connect a control or property to one or more data

elements.

• Simple binding: Use simple binding to display a

field value in controls that show Data Bindings in

the property window, such as text box or label.

Creating Data Bound Form

• Creating a form with ADO.Net objects and

data-bound controls to display and update information in

a dataset.

• Demo:

– Add data source:

• Data/Add New Data Source • Data/Show Data Source

– Click the dropdown list next to the table’s name:

• Datagrid view • Details

Items Added to the Form

• Connection

• Table Adapter: click smart tag

– Add query – Preview data

• Dataset:

– Edit in dataset designer

• Binding Source

– Add query: Add a new tool strip. – Preview data

• Binding navigator

Generated Code

Private Sub CUSTOMERBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CUSTOMERBindingNavigatorSaveItem.Click Me.Validate()

Me.CUSTOMERBindingSource.EndEdit()

Me.CUSTOMERTableAdapter.Update(Me.SalesDBDataSet.CUSTOMER)

End Sub

Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'TODO: This line of code loads data into the

'SalesDBDataSet.CUSTOMER' table. You can move, or remove it, as needed.

Me.CUSTOMERTableAdapter.Fill(Me.SalesDBDataSet.CUSTOMER) Me.EmpTableAdapter.Fill(Me.DataDataSet.emp)

Other Data Form Demos

• DataGrid View

• Add /Modify/Delete records.

• Read only form:

Hierarchical Forms

• Parent table/Child table

– Add parent table and child table to Data Source

– Drag the parent table and the child table to the form. Parent table uses detail view and child table uses

dataGrid view

– Click Dataset object’s smart tag to choose Edit in Dataset Designer

– With the designer, right click the parent table and choose Add/Relation

Detail Form with Bound ListBox

• Example: Customer table form with CID listbox

and displays selected customer information in

textboxes.

– Choose detail view for the customer table.

– Click the dropdown list next to the CID field and click ListBox

– Drag the Customer table to the form.

– Bind the CID field to the BindingSource:

Creating A Database Application

Without Programming

• Creating a database application to display

information and update database.

• A main form with buttons to open data

forms:

– DisplayInfo

– Enter New

– Modify

Data Adapter Wizard

• Configure Data Adapter and generating a dataset:

– From the Data tab of the ToolBox, Drag OledbDataAdapter to the form.

– Use the Data Adapter Wizard to configure the Adapter. – Right Click the Adapter to preview data and create

dataset.

• Bind the dataset to controls.

• In the Form Load event, use Adapter’s Fill method

to load the dataset:

Creating Bound DataGridView

• DataGridView control:

– Data Source property:

• DataSet

– Data Member property

• A table in the dataset

– In the Form Load event, use Adapter’s Fill method to load the dataset:

• OleDbDataAdapter1.Fill(DataSet11)

BindingSource Object

• It is an object that keeps track of position (the current row) of a data source.

• Useful properties:

– DataSource – DataMember

– Position property: is the index of the current row. The index is a 0-based index, the first record has a position of 0.

• Methods:

– MoveFirst, MoveLast, MoveNext, MovePrevious – AddNew

Adding AddNew and Save Button

• Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

• CUSTOMERBindingSource.AddNew() • End Sub

• Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

• CUSTOMERBindingSource.EndEdit() •

Adding AddNew and Save Button

AddNew button: Use BindingSource AddNew Method

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

CUSTOMERBindingSource.AddNew() End Sub

Save button: Use BindingSource EndEdit method and

Adapter’s Update method:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

CUSTOMERBindingSource.EndEdit()

CUSTOMERTableAdapter.Update(SalesDBDataSet1.CUSTOMER)

BindingSource’s Position Property

• If controls are bound to a BindingSource object, to

move the current record we change the Position

property of the BindingSource object:

– To move to the next record:

• Me.EmpBindingSource.Position += 1 • Or Me.EmpBindingSource.MoveNext

– To move to the previous record:

• Me.EmpBindingSource.Position -= 1

Binding Text Box

• Select Data Bindings property:

– Text: choose field

• Add navigation buttons:

– MoveNext:

• CUSTOMERBindingSource.Position += 1

– MovePrevious:

Other Objects Related to Data

Binding

• BindingContext: It is an object that manages a

collection of data sources used for binding.

• CurrencyManager: It is an object that keeps track

of position (the current row) of a data source. Two

useful properties:

– Position property: is the index of the current row. The index is a 0-based index, the first record has a position of 0.

MoveNext and MoveLast Example

• MoveNext:

–

• MoveLast:

– Me.BindingContext(DataSet21, "customer").Position = Me.BindingContext(DataSet21, "customer").Count -1

• How to MovePrevious and MoveFirst?

• Note: The Position property takes care of the end of file automatically.

BindingNavigator Object

• This object automatically adds navigation

buttons to the form.

• Property:

– BindingSource property

• Specify a BindingSource object

Binding ListBox

• Example: Bind Customer Table’s CID field

to a listbox.

– Create a Adapter for Customer table , and

generate the dataset.

– Add ListBox and set binding properties:

• Data Source: Customer table

• Display Member: Field to display in the listbox.

Display Selected Record

• Bound textbox (same data source as the listbox):

– If the Listbox and the textbox are bound to the same BindingSource object, the textbox will automatically displays the record of the selected listbox item.

• Unbound textbox

– To display the ValueMember

• Textbox1.text = ListBox1.SelectedValue

– To display other fields:

• Textbox1.text = ListBox1.SelectedItem(“Cname”) • Can we use TextBox1.text=ListBox1.SelectedItem?

ListBox SelectedItem Property

• How to display the selected record in unbound

textbox?

• After binding to a data source, this property return a

DataRowView object.

• What is DataRowView?

– Object Browser:

• System.Data

– DataRowView: Item property is the default property

• To retrieve a column from a DataRowView object

(use 0-based index to identity a column):

• ListBox1.SelectedItem.Item(1) • Or: ListBox1.SelectedItem(1)

Using Object Browser

• View/Object Browser

• DataSet object model:

• System.Data

– DataSet

• Relations • Tables

– Rows – Columns

Recordset vb6

• Two dimensional array holding a table/result of SQL

• ADO allows four different cursor types (ways of using recordsets) – Dynamic Cursor (CursorType = adOpenDynamic)

• moves freely through recordset (forwards, backwards, BOF, EOF) • all appends, updates, deletes made by other users seen by recordset

and can be changed by recordset (slow) – Keyset Cursor (CursorType = adOpenKeyset)

• Similar to dynamic - allows viewing of records changed by other users but disallows changes on those records (slow)

– Static Cursor (CursorType = adOpenStatic)

• cannot see other users’ changes (hence works faster) – Forward Only Cursor (CursorType = adOpenForwardOnly)

Recordset Setup

• Dim rst As ADODB.Recordset • Set rst = New ADODB.Recordset • Recordset Methods

– AddNew, Delete, Update – Open, Close, Cancel

• rst.Open <SQL query/Table>, <connection variable>, <LockType> • e.g. Rst.Open “emp”, adoconn,

– Move, MoveFirst, MoveNext, MovePrevious, MoveLast

– Seek - searches the index and moves to the row with required value – Find - searches for row with required value (seek is faster but requires

index)