Manipulating data with Dataset Part I

Introduction:
Working with DataSet will be quiet interesting. In this article we can look at generating the dataset using DataAdapter, setting relationship and constrains to the DataSet and populate DataSet in windows form controls, modifying the DataSet, Updating DataSet to the datasource.

Generating DataSet:
Let us take an example to generate a DataSet using DataAdapter. As we said DataSet is a disconnected Architecture for data access so it don’t required a connection to the database is required, just you can pass the connecting string and no need to open a connection. Before we start working with DataSet you should know how to create a new instant for DataSet.

Dim dsetMydata As New DataSet

Or

Dim dsetMydata As New DataSet("MyDataset")

Here we can declare the DataSet name as the optional parameter to the DataSet
Now let see a simple example to generate a DataSet from a data source like MS SQL server.

Try
Dim connString As String = "server=local; database=xx; UID=xx; PWD=xx;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "select Firstname, LastName from EmployeeInfo"
Dim myAdapter As New SqlDataAdapter(strQuery, myConn)
myAdapter.Fill(dsetMydata)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

The above code will help to create the DataSet and store the data from the selected data source.
Here you have used the DataAdapter object. Command text and connection is passed as the parameter for the DataAdapter. Using Fill method in the DataAdapter you can store the retrieved information from the selected data source. As we discussed before DataSet is the collection of DataTable so we can append one or more than one DataTable from the data source.
Let see an example to retrieve two DataTable from the data source and bind it in a DataGrid

Try
Dim dsetMydata As New DataSet
Dim connString As String = "server=.; database=pubs; UID=sa; PWD=;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "select * from authors;select * from employee"
Dim myAdapter As New SqlDataAdapter(strQuery, myConn)
myAdapter.Fill(dsetMydata)
DataGrid1.DataSource = dsetMydata.Tables(0)
DataGrid2.DataSource = dsetMydata.Tables(1)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

The above code will return two record set and it is binds in the DataSet as two tables, that is table1 and table2. You can get the two table by dsetMydata.Tables(0) and dsetMydata.Tables(1) this two table is binded in the DataGrid as shown in Figure

Figure 1

Creating DataSet Manually:
Now let us create a DataSet without using DataAdapter. For creating these type of DataSet you need to do following steps
· Create Number of DataTables that the DataSet going to have.
· Create the column for each DataTable’s
· If you want to set the relationship between DataTable’s, assign it using DataRelation class
· Then add the appropriate data to each DataTable’s

Before start you have to know how to declare a DataTable and DataColumn. This is as similar to DataSet declaration. The below code show is use to declare a DataTable

Dim dtMyTable1 As New DataTable("Employee")
Dim dtMyTable2 As New DataTable("Salary")

While declaring the DataColumn, you can specify the column Name, Datatype of the column and other specifications like primary key, auto increment column and more. The below code will help you to understand more about DataColumn.

Dim dcColumnEmpId As New DataColumn("EmpId",_ System.Type.GetType("System.Int32"))
dcColumnEmpId.AutoIncrement = True
dcColumnEmpId.AutoIncrementSeed = 1
dcColumnEmpId.AutoIncrementStep = 1
Dim dcColumnName As New DataColumn("Name",System.Type.GetType("System.String"))

The above code will create two DataColumn and add some property to the column. We can set the properties if we required
After creating the column you need to add the column to the DataTable. . And also can specify a column as the primary key for the DataTable
The below code will do it.

dtMyTable1.Columns.Add(dcColumnEmpId)
dtMyTable1.PrimaryKey = New DataColumn() {dcColumnEmpId}

Now this table can be added to the DataSet as shown.

dsetMyDataSet.Tables.Add(dtMyTable1)

Let us take a complete look of the code.

Dim dsetMyDataSet As New DataSet
Dim dtMyTable1 As New DataTable("Employee")
Dim dtMyTable2 As New DataTable("Salary")
Dim dcColumnEmpId As New DataColumn("EmpId", System.Type.GetType("System.Int32"))
dcColumnEmpId.AutoIncrement = True
dcColumnEmpId.AutoIncrementSeed = 1
dcColumnEmpId.AutoIncrementStep = 1
Dim dcColumnName As New DataColumn("Name", System.Type.GetType("System.String"))
dtMyTable1.Columns.Add(dcColumnEmpId)
dtMyTable1.PrimaryKey = New DataColumn() {dcColumnEmpId}
dtMyTable1.Columns.Add(dcColumnName)
Dim dcColumnSalEmpId As New DataColumn("EmpId", System.Type.GetType("System.Int32"))
Dim dcColumnSalary As New DataColumn("EmpSalary", System.Type.GetType("System.String"))
dtMyTable2.Columns.Add(dcColumnSalEmpId)
dtMyTable2.Columns.Add(dcColumnSalary)
dsetMyDataSet.Tables.Add(dtMyTable1)
dsetMyDataSet.Tables.Add(dtMyTable2)dsetMyDataSet.Relations.Add("EmployeeMaping", dsetMyDataSet.Tables("Employee").Columns("EmpId"), dsetMyDataSet.Tables("Salary").Columns("EmpId"))

In this example we created two tables Employee, Salary. Employee has EmpId and Name column and Salary has EmpId and EmpSalary column. Each column is added to appropriate DataTables and the tables is attached to the DataSet. And you can set the relationship to the table as in code below

dsetMyDataSet.Relations.Add("EmployeeMaping",_ dsetMyDataSet.Tables("Employee").Columns("EmpId"),_ dsetMyDataSet.Tables("Salary").Columns("EmpId"))

You can able to create a DataSet with any number of DataTables. Now we can see how to add Datarow to a created DataTable. For adding a row in the DataTable, first you need to create a new DataRow for that tables and then you need to add a value for it. The below sample code will helps to add the rows to the DataTable in the DataSet in a loop.

For i As Integer = 1 To 5
Dim dr As DataRow = dsetMyDataSet.Tables("Employee").NewRow()
Dim dr1 As DataRow = dsetMyDataSet.Tables("Salary").NewRow()
dr("Name") = "MyName" & i.ToString()
dr1("EmpId") = i.ToString()
dr1("EmpSalary") = (5000 * i).ToString()
dsetMyDataSet.Tables("Employee").Rows.Add(dr)
dsetMyDataSet.Tables("Salary").Rows.Add(dr1)
Next

The above code will insert records in the DataTable Employee and Salary. Since you assigned the DataRelation to both the table, you can able to retrieve the information from the tables. The below code will help to retrieve the information from the table.
Dim MyChildRow() As DataRow
Dim MyParentrow As DataRow
MyChildRow = dsetMyDataSet.Tables("Employee").Rows(0).GetChildRows("EmployeeMaping")
MyParentrow = dsetMyDataSet.Tables("Salary").Rows(0).GetParentRow("EmployeeMaping")

MyChildRow will get all the columns in the Salary Table for the EmpId of Employee table for the first row.
MyParentrow will get all the columns in the Employee table for the EmpId of Salary table for the first row. Now you can understood how to set the relationship between tables and how to retrieve
the data using the relationships.
Now we can see how to set constrains to a DataColumn. You can set some constraints like Primary key, Unique key and Foreign key to the DataColumn. You had seen how to set a primary key to a column. The below code will help to set Unique Key constraints to the DataColumn. The Unique Key can be declaring in two ways. Let see an example to implement the Unique Key in two ways.

dcColumnEmpId.Unique = True

OR

Dim myUniqueKey As New UniqueConstraint(dcColumnEmpId)
dtMyTable1.Constraints.Add(myUniqueKey)

Now we can see how to set Foreign Key Constraints to your DataColumns. This is similar to setting a Primary key.

Dim myForeignKey As New ForeignKeyConstraint("EmployeeMaping", dtMyTable1.Columns("EmpId"), dtMyTable1.Columns("EmpId"))_
dtMyTable2.Constraints.Add(myForeignKey)

Summary:
In Part I you have seen generating and Creating the DataSet. In Part II you will see how to Editing and Updating in DataSet and how it will affects the datasource.