Now that we have a connection its
time to look at how we actually read records from our database.
There are two ways you can do it, either execute SQL commands directly
against the database or use a data adapter to fill a dataset and then manipulate
that.
The first method is great if you want to just update or delete
items from your database and in my opinion the second is best if you want to display
records to users and manipulate them.
We'll look at the first later but for now we will cover the dataset
and data adapter method.
First off we'll need a dataset.
Creating a dataset
A dataset is really a 'copy' of one of your tables in your database.
It is held in memory and populated with a data adapter. The data adapter is also
used to write changes back to the physical database from your dataset.
|
Dim ds As New
Data.DataSet |
That's it.. one shiny new dataset ready to be filled with data,
so what are we waiting for!
Creating a data adapter
As already mentioned a data adapter is used to fill a dataset with
data. You create one as such..
|
Dim adapter As OleDbDataAdapter =
New OleDbDataAdapter("Select * from Customers",
con) |
Its fairly self explanatory when you read it through.. The slightly confusing bit though is the two overloads
that are used.
The "Select * from Customers" is a very simple
SQL select statement. The adapter will use this when fetching new records from our
database.
The second overload is the connection that the adapter is to use
when executing the select statement, in this case its 'con' which is the connection
we created in part1.
You also need to set a special property on the adapter as well.
Its the Missing Schema Action property. It tells the adapter what to do when it
fills a completely blank table in a dataset.
If you don't set this property then you will get various messages
like "The table does not have a primary key defined' etc when you try to select
rows from it. Simply enter this line to configure the property.
|
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey |
Filling the dataset
Again this is pretty straight forward, we just use the 'Fill' method
of our data adapter to fill the dataset like so..
|
adapter.Fill(ds,"Customers") |
This will create a table called 'Customers' in our dataset (ds)
and then fill it with data as defined by the adapters select command.
The table you create in your dataset does not have to be called
the same as the source table, but its easier to work with when you are working with
several tables if you do!
As a point of note, a dataset can hold lots of different tables
as long as they are all given different names.
Part 3 :
Modifying the data in a dataset
Part 1 :
Creating a connection