Manipulating data with Dataset Part II

Introduction:
In the previous part you came to know about generating and Creating the DataSet. Now come on BUDDIES let us take dive into dataset in depth like Editing and Updating in DataSet and EATING DATASET :), and how it will affect the data source.

Editing and Updating in a DataSet
In DataSet you can get the records from the data source and edit the records with in the DataSet and update back to the data source. Let’s see an example for editing and updating the data.
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"
Dim myAdapter As New SqlDataAdapter(strQuery, myConn)
myAdapter.Fill(dsetMydata)
Dim dr As DataRow = dsetMydata.Tables(0).Rows(0)
dr("au_lName") = "Bill"
dr("au_fName") = "Gates"
DataGrid1.DataSource = dsetMydata.Tables(0)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
The above code returns all the rows from the data source and stores it in DataSet

Dim dr As DataRow = dsetMydata.Tables(0).Rows(0)
dr("au_lName") = "Bill"
dr("au_fName") = "Gates"

This code helps to edit the DataTable. Before editing you should specify the row number that has to be edited. In the above example you have specified Rows (0) which is the first record or row that has to be edited.Now this change is updated in the DataSet, if you don’t want to update this information in the DataSet, you can add the following code
dsetMydata.RejectChanges()

This rejects the changes that happened in the DataSet. As the DataSet holds both original and edited version of records in memory, a call to RejectChages() gets the original version of records.

Let us now come to the most advance part of this section that is updating the changed records using DataAdapter Update method..

Once you change the records in the dataset, it has to be updated into the data source. Instead of doing row by row updating, you have a Update method in DataAdapter object which helps in bulk update in to the data source. To achieve this you need to use parameter method to pass the values to the data source. The above code helps to do it. The step by step process briefed below helps to update DataSet in to data source.

• Creates Command object with command text and connection to data source as parameter as shown below.

Dim mycom As New SqlCommand("update authors set au_lname=@lname, au_fname=@fname where au_id=@id", myConn)

• Using parameter member in the command object you can create a new parameter for that command object. The below code shows how to implement this. In the Parameter you need to specify the parameter variable, DataType, Size and the column to be affected in the data source.

mycom.Parameters.Add(New SqlParameter("@lname", SqlDbType.VarChar, 50, "au_lname"))

• Assign the command object to the DataAdapter Update command type as below

myAdapter.UpdateCommand = mycom

• Using DataAdapter.Upadate method you can update the modified data in to the data source. And also can check if the data has been properly updated, if not then you can roll back the DataSet by dsetMydata.RejectChanges()


If (dsetMydata.Tables(0).HasErrors) Then
dsetMydata.RejectChanges()
Else
dsetMydata.AcceptChanges()

The below code shows how to update the records to the dataset.

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"
Dim myAdapter As New SqlDataAdapter(strQuery, myConn)
myAdapter.Fill(dsetMydata)
Dim dr As DataRow = dsetMydata.Tables(0).Rows(0)
dr("au_lName") = "Bill"
dr("au_fName") = "Gates"
Dim mycom As New SqlCommand("update authors set au_lname=@lname, au_fname=@fname where au_id=@id", myConn)
mycom.Parameters.Add(New SqlParameter("@lname", SqlDbType.VarChar, 50, "au_lname"))
mycom.Parameters.Add(New SqlParameter("@fname", SqlDbType.VarChar, 50, "au_fname"))
mycom.Parameters.Add(New SqlParameter("@id", SqlDbType.VarChar, 50, "au_id"))
myAdapter.UpdateCommand = mycom
myAdapter.Update(dsetMydata.Tables(0))
If (dsetMydata.Tables(0).HasErrors) Then
dsetMydata.RejectChanges()
Else
dsetMydata.AcceptChanges()
End If
Conclusion
So, hope my article have helped you better understand dataset, soon will be coming up with something good for you. Be sure to place comments on the article