Access and Manipulate Data - Using DataSets

In this tutorial you will learn about Using DataSets, Populating a DataSet From a Database, Moving Around in DataSets and Retrieving Data, Using Strongly Typed DataSets, DataSets With Multiple Tables.

Populating a DataSet from a Database

As already discussed DataSets do not contain any data when they are created. The user must fill the data in to the DataSet separately. We have already seen that there are several methods of filling a DataSet with data. DataSets can be created using the Visual Studio Design in which case TableAdapters are also created

Filling a DataSet using a TableAdapter

1. Create a new project in Visual Basic IDE.
2. On the Database Explorer click the icon for creating new data connection and
3. Choose the SQL Server file.
4. Establish the connection and you should be seeing the database objects on the window.
5. In the solution Explorer click on the project name and
6. Choose add an item option.
7. In the dialog box that opens choose DataSet item and
8. Name it as ds and click ok.
9. You will see the DataSet item added to the solution and also
10. A blank screen will be seen.
11. From the Database explorer drag and drop the table ProductCategory.
12. Choose ‘not’ in the message box that asks your permission to add the datafile as a project data.
13. Now right click on the Form1 and
14. Choose the option to see the code window.
15. Type the following codes to fill the DataSet

Dim ProductCategoryTableAdapter As New
dsTableAdapters.ProductCategoryTableAdapter()
Dim ProductCategoryDataSet As New ds
ProductCategoryTableAdapter.Fill(ProductCategoryDataSet.ProductCategory)

You can also populate a DataSet using a SqlDataAdapter or an OleDbDataAdapter . The method of doing this is same in both the cases.

We shall now see how a DataSet is filled by using a SqlDataAdapter. You have to

1. Create a SqlConnection object. SqlDatAdapter object.

2. The SqlConnection object needs connection string as an argument and the SqlDataAdapter requires the SQL Statement and Connection Object as an argument.

3. The ConnectString gives details about the Database Server, Initial Catalogue, connection type, userid and password.

4. A typical connection string could look like this:

"data source=sql.domain.no; initial catalog=xxxxx; User ID=xxxxx;pwd=xxxxx; Integrated Security=SSPI”

Code for filling the DataSet is given below:

Dim SQLStr As String
Dim ConStr As String
SQLStr = "SELECT Name FROM production.ProductCategory"
ConStr = "data source=sql.domain.no; initial catalog=xxxxx; User ID=xxxxx;pwd=xxxxx; Integrated Security=SSPI"
Dim sqlConn As New System.Data.SqlClient.SqlConnection(ConStr)
Dim ds As New DataSet
Dim SQLAdapter As New System.Data.SqlClient.SqlDataAdapter(SQLStr, ConStr)
SQLAdapter.Fill(ds)

New rows can be added manually to the data set as in the case of a data-entry. In this case the user has to first create a DataSet, a DataTabale, and a DataRow. Then he must populate the DataRow manually by supplying the value for each row and then add these rows to the DataSet. It should be remembered that there is no underlying data store that supplies data in this case. The code for the activity is given below:

Dim dsNew As New DataSet
Dim t As New DataTable
Dim tr As DataRow = dsNew.Tables("T").NewRow
tr("Name") = "Aviation Gears"
dsNew.Tables("T").Rows.Add(tr)

You can also populate a DataSet by reading from an WML file. The code listing is given below:

Dim dsXML As New DataSet()
dsXML.ReadXml("XmlFilePath and Name")

The user can also create a new DataSet and merge it with any existing DataSet. This opetaion is illustrated by the code given below:

Dim dsXML As New DataSet()
Dim dsCopy As New DataSet
dsXML.ReadXml("XmlFilePath and Name")
dsCopy.Merge(dsXML, True, MissingSchemaAction.AddWithKey)

Moving Around in DataSet and Retrieving Data

In the above sections we have seen how to create a DataSet, how to populate it etc. In the following sections we shall see how to retrieve data from a DataSet. Remember, we stated that there is no current row in a DataSet? So any row can be accessed directly by just mentioning its position? Let us add to this the fact that a DataSet can contain as many tables as required and the user can also create objects that show the relationships that exist and the constraints that are imposed on them. Thus a DataSet can be a very complex data store. Let us understand the process of navigating the DataSet by the following example.

You can write code to go to the first record of the table, go one record forward or backward and also go to the last record. We shall use the BindingContext to do this. Look at the following code:

Click here to view sample code

Retrieving data from DataSets is easy. The data in the DataSet can be displayed in a grid and a script can be written to enable the user browse through the data. The sample code illustrates this.

Imports system.Data
Public Class Form1
Dim ProductDataSet As ds

Private Sub DataLoad()
Dim ProductTableAdapter As New dsTableAdapters.ProductTableAdapter()
ProductDataSet = New ds
ProductTableAdapter.Fill(ProductDataSet.Product)
End Sub

The code that is given below will make navigation within the data set.

Click here to view sample code

Next Page: Working with DataSets - Page 2