Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
Dataset – Read and Modify Data, a Glance
Posted Date: 14 Mar 2005 Resource Type: Articles Category: Databases
|
Posted By: chetana sadashiv kambi Member Level: Bronze
Rating:  Points: 10
|
The DataSet is a cache of data retrieved from a data source. It is a collection of DataTable objects. The tables are related to each other with DataRelation objects.
Populating Datasets
A dataset is a container; therefore, you need to fill it with data
Data adapters are an integral part of ADO.NET managed providers. That is these are set of objects used to communicate between a data source and a dataset. (In addition to adapters, managed providers include connection objects, data reader objects, and command objects.) Adapters are used to exchange data between a data source and a dataset.
Read from Dataset
NTo read the data from the dataset you need to use the Following steps
1. establish connect to the database using connection object.
SqlConnection sqlconn = new SqlConnection(strcont);
2. create a adapter and a Dataset.
SqlDataAdapter sqladpt = new SqlDataAdapter("select * from employees",sqlconn);
DataSet sqlds = new DataSet();
3. Fill the dataset using Adapter.
sqladpt.Fill(sqlds);
4. Use either .SelectCommand.ExecuteReader to read the specific data value from the dataset or use reference to the table, row and column using the indexs.
for (int i=0;i<3;i++)
{
val1 = sqlds.Tables[0].Rows[i].ItemArray[0].ToString();
val2 = sqlds.Tables[0].Rows[i].ItemArray[1].ToString();
val3 = sqlds.Tables[0].Rows[i].ItemArray[2].ToString();
}
Modify a Value in Dataset
If it is required to update a specific value in the dataset and to update the datasource with the modified changes then follow the below step.
5. Create a datarow object. Begin the edit transaction, modify the values and then end the transaction on the datarow. For more generic purpose use of Column names or indexs can be be used. For sample, I have hardcoded a value of 2 indicating the 3rd column in the Table.
DataRow dr = sqlds.Tables[0].Rows[i];
dr.BeginEdit();
dr[2] = "Nancy";
dr.EndEdit();
6. create a commandbuilder object with adapterobject as the refernce object.
This Command Builder will automatically generate the SQL statements to make required changes at the data source end.
SqlCommandBuilder cb = new SqlCommandBuilder(sqladpt);
7. Use <adapterobject>.Update method to Update the source data
Once the datavalue is changed, the status of the modified row is indicate as MODIFIED, (ADDED in case a new record is added and DELETED in case a row is deleted).
Based on these Row status values the commandbuilder will automatically generate the specific SQL statements UPDATE, INSERT and DELETE for each Single row transactions.
sqladpt.Update(sqlds);
Below is the Piece of code that reads the values from the dataset and moves it to specific variables. And then when the condition met, it will modify the value in the dataset and update the Datasource also.
try
{
string strcont = "server=localhost;database=Northwind;UID=sa;PWD=sa";
SqlConnection sqlconn = new SqlConnection(strcont);
SqlDataAdapter sqladpt = new SqlDataAdapter("select * from employees",sqlconn);
DataSet sqlds = new DataSet();
SqlCommandBuilder cb = new SqlCommandBuilder(sqladpt);
sqladpt.Fill(sqlds);
string val1, val2, val3;
for (int i=0;i<3;i++)
{
val1 = sqlds.Tables[0].Rows[i].ItemArray[0].ToString();
val2 = sqlds.Tables[0].Rows[i].ItemArray[1].ToString();
val3 = sqlds.Tables[0].Rows[i].ItemArray[2].ToString();
if (val3 == "Chetana")
{
DataRow dr = sqlds.Tables[0].Rows[i];
dr.BeginEdit();
dr[2] = "Nancy";
dr.EndEdit();
}
MessageBox.Show(sqlds.Tables[0].Rows[i].ItemArray[2].ToString());
string drs = sqlds.Tables[0].Rows[i].RowState.ToString();
MessageBox.Show(drs);
}
sqladpt.Update(sqlds);
string drs1 = sqlds.Tables[0].Rows[0].RowState.ToString();
MessageBox.Show(drs1);
sqlds.AcceptChanges();
sqlconn.Close();
}
catch(Exception exc)
{
MessageBox.Show(exc.Message);
}
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|