CIS159 - VB

1
CIS159 - VB

• Chapter 10
• Databases
• ch10.ppt

2
Overview of Topics

• Relational Database Terminology
• ADO.Net
• Connecting to a Database
• Connection
• Data Adapter
• DataSet
• Demonstrate Database Application (VB14ex)
• Binding data
• Navigating Through a Dataset

3
Relational Databases

• Most data is now stored in relational database
  management systems (DBMS or RDBMS).
• There are various vendors to choose formOracle,
  MS SqlServer, Sybase, IBM DB2, etc.
• Visual Studio is usually used to develop
  applications that store data in Microsoft
  SqlServer.
• In this class we will use MS Access, due to the
  server and security requirements of SqlServer.
• Those of you familiar with Access know that it
  has a built-in form and report designer.
  However, keep in mind that we would normally be
  accessing a database stored in SqlServer, which
  does not have built-in tools..

4
Database Terminology

• A database is made up of one or more related
  tables.
• Conceptually a table is made up of rows and
  columns (2D Array).
• Each row represents the data for one
  record(person, inventory item, course
  information).
• Each column (field) is a data element (name,
  address, city, state, zip).
• Tables have a Key Field to uniquely identify each
  record(Id number, part number, account number).
• Relationships between various tables can be
  defined.
• A DBMS stores everything (tables, columns,
  relationships, etc.) about the database in system
  tables.
• System and data tables are usually stored in one
  file (VBMail.mbd).

5
SQL Structured Query Language

• SQL is pronounced sequel.
• SQL Structured Query Language Basic set of
  commands that are common in all DBMS.
• DBMS vendors may add additional commands.
• SQL commands are usually processed against a
  record set.
• Select name, address From customer where zip
  85202
• Delete From customer where zip 85202All rows
  matching the criteria would be selected or
  deleted.
• Commands are very powerful.
• Well usually want to qualified a command by
  specifying a customer id or other unique
  identifier using the Where clause.

6
An Access Table
Columns
Row
7
VB Application Reading Database
8
Accessing a Database ADO.Net

• Use ActiveX Data Objects (ADO)
• For an Access database use
• OleDbConnection connection tool establishes a
  link to a data source.
• OleDbDataAdapter data adapter handles
  retrieving and updating the data and creates a
  Dataset.
• Dataset bind columns in the Dataset to controls
  (textbox, listbox) by setting the DataBinding
  property.
• Use the Fill method of the data adapter to load
  the data into the dataset (daVBMail.Fill(dsCustome
  r)). The Fill method is usually placed in the
  form load event.

9
Accessing and Presenting Data
10
Setup Process

• Use the next few slides to begin VB14.
• Refer to VB14 before continuing.
• The interface for VB14 is provided on the
  website.
• Remember to copy the database file (VBMail.mbd)
  into the bin folder of the project (VBMail.mbd
  is available on website and textbook CD).
• Add an OleDbDataAdapter control.
• Use Configuration Wizard to
• Create and configure the Data Adapter
• Create and configure the Data Connection

11
Toolbox Data Panel

• In the Toolbox, click on Data to bring it
  forward.
• Double-click on OleDbDataAdapter to create it and
  start the and Configuration Wizard.
• Use the Configuration Wizard to also create the
  OleDbConnection.

12
Setup Data Adapter

• The data adapter does all of the work of passing
  data back and forth between a data source and a
  program.
• After double-clicking the OleDbDataAdapter
  control, the adapter is created and the
  Configuration Wizard opens automatically.
• In the wizard, read the general information and
  click Next.
• The next step is to choose Data Connection.Use
  the New Connection to create connection(see Next
  Slide).

13
Connection - Provider

• Click on Provider tab.
• Use MS Jet 4.0 for an Access database.

14
Connection - Data Link Properties

• Connection
• Click on the Connection tab.
• Browse to find database file.VBMail.mbd in Bin
  folder.
• Leave username and password blank.
• Click Test Connection.
• If it works, click OK.

15
Data Adapter Wizard - Continued

• After creating the connection, you are returned
  to the wizard.
• Click on Next to select the newly created
  connection.
• For the Query Type choose Use SQL statements,
  and then click on Next.

16
Data Adapter Wizard Sql Statement

• For VB14 enter Select From Customer
• means to select all columns from table
  Customer.
• Click on Next.
• On the final screen, click on Finish.
• On the prompt about the password being visible,
  click on dont include password.

• More than one table could be included.

17
Rename Adapter and Connection

• After the Wizard has finished, the data adapter
  and connection will have been created.
• The default name for the adapter is
  OleDbDataAdapter1.
• Click on OleDbDataAdapter1 one time to select it.
• Rename the data adapter (da) to daVBMail
  (daDataSource) by changing the Name property.
• The default name for the connection is
  OleDbConnection1.
• Click on OleDbConnection1 one time to select it.
• Rename the data connection (dc) to dcVBMail
  (dcDataSource) by changing the Name property.

18
Connection String

• The data adapter wizard adds many parameters to
  the connection string property of the Connection.
• When the database file is selected in the wizard,
  the directory path is included with the file
  name. For example (E\VB14\bin\VBMail.mdb).
• If you use a different computer to run the
  program later, or rename a folder, or move the
  project, the connection will fail because the
  drive letter or directory may be different.
• We can change the connection string at runtime by
  adding the following command (all on one line) in
  the form load event proceduredcVBMail.Connectio
  nString ProviderMicrosoft.Jet.OLEDB.4.0
  Data SourceVBMail.mdb
• Make sure to place the database file in the bin
  folder.

19
Generate Data Set - Steps

• Next step is to create the data set in design
  mode.
• Click on the data adapter (daVBMail) one time to
  select it.
• Under the Properties, youll see three options
• Configure Data Adapter starts wizard again
• Generate Data Set creates data set to use in
  program
• Preview Data displays data stored in table
• Click on Generate Data Set.
• Name the new dataset dsCustomer.
• Add the dataset to the Designer.
• Click OK.
• Rename the dataset (ds) from DsCustomer1 to
  dsCustomer by changing the name property of the
  control.

20
Generate Data Set - Image
21
Bind the Data to Controls

• There should be three items in the component
  tray.
• daVBMail
• dcVBMail
• dsCustomer
• Now use the column names in the Data Set to
  populate labels or textboxes with data from the
  table.
• This process is referred to Binding the data.

22
Data Binding

• Simple Binding
• Connect one data element to a control.
• Connect a label to the name column (etc.)
• Use Data Bindings property and select the column
  from the database that should be displayed in
  control.
• Complex Binding
• Connect more than one data element to a control.
• Connect a list box to multiple columns.
• Use DataSource and DataMember Properties.

23
Data Binding Property
24
Navigating through a Dataset

• Dataset is a temporary set of data stored in
  memory.
• In ADO.NET datasets are disconnected the copy of
  data kept in memory does not keep an active
  connection to the data source.
• We can navigate through the dataset one row or
  record at a time.
• The property Position of the forms BindingContext
  tell us the current records position.
  Me.BindingContext(dsCustomer, "Customer").Position
  
• The property Rows.Count of the Dataset tells us
  how many records are in the Dataset.
  dsCustomer.Tables("Customer").Rows.Count
• The actual table name must be placed in the
  quotation marks. This information may be obtain
  from the data adapter.

25
BindingContext - Position

• Position tells the current records position
  within the Dataset.
• Increase or decrease the value to move through
  the records within the Dataset.
• Me.BindingContext(dsCustomer, "Customer").Position
  1
• Me.BindingContext(dsCustomer, "Customer").Position
  - 1
• The Dataset and Table name are passed as
  parameters to get the position value. Dataset
  may have more than one table, so that is why the
  table name is required.
• The value is zero based, so the first record is
  in position zero and the last record is in
  position Rows.Count 1.

26
Dataset Rows.Count

• The record count is a property of the
  Dataset.intRecordCount dsCustomer.Tables("Cust
  omer").Rows.Count
• Specify the Dataset.Tables and table name.
• Use the count as an informational item to the
  user by displaying in a label or use it to check
  if the Dataset is empty.

27
MS SqlServer

• To access a SqlServer database use
• SqlConnection
• SqlDataAdapter
• The rest is pretty much the same.DataSet and
  Fill method.

28
Summary

• Relational Database Terminology
• Connecting to a Database
• Binding data
• Navigating Through a Dataset
• Next
• Create project VB14
• Download interface
• Download database vbmail.mdb
• Bind textboxes and code navigation procedures
  first
• Next Class
• Go Over Updating DataBases