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.

Accessing data using DataReader

Introduction:
In this article you can see how to access the data using DataReader from the data source.

About DataReader:
DataReader is a readonly, forward only and connected recordset from the database. In DataReader, database connection is opened until the object is closed unlike DataSet. Using DataReader we can able to access one row at a time so there it is not required storing it in memory. In one DataReader we can get more than one result set and access it one by one. It will be faster for using simple purpose like populating the form. We should not use it for manipulating the records since it will always connect to database.
Let’s see few examples to use DataReader from accessing the data.


The below exaple will get the employee Firstname and Last name from the table EmployeeInfo form MS SQL server database. Using While loop the records are Fetched row by row and displayed in message box. After the last record While loop will end and then we need to close both the DataReader object and also connection object as show in the code.

CODE:

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 myCommand As New SqlCommand(strQuery, myConn)
myConn.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
While (myReader.Read())
MessageBox.Show(myReader.GetString(0))
MessageBox.Show(myReader.GetString(1))
End While
myReader.Close()
myConn.Close()

In the above example we used myReader.GetString(0) this will return the first column value of the record set. The above code will result only one record set. Now let us consider an example the resultant set has more than one record set.

Code:

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;Select * from EmployeeMaster"
Dim myCommand As New SqlCommand(strQuery, myConn)
myConn.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Dim bFlag As Boolean = True
Do Until Not bFlag
While (myReader.Read())
MessageBox.Show(vbTab & myReader.GetName(0) & vbTab & myReader.GetString(1))
End While
bFlag = myReader.NextResult()
Loop

The above code will return two record set. myReader.NextResult() will helps to move from one record set to another record set. From the above two example you will know how to use DataReader in you application.

Summray:
From this article you can able to learn how to uses the DataReader for accessing the data.