What are DataSets and DataAdapters
Datasets store a copy of data from the database tables. However, Datasets can not
directly retrieve data from Databases. DataAdapters are used to link Databases with
DataSets. If we see diagrammatically,
DataSets < ----- DataAdapters < ----- DataProviders < ----- Databases
DataSets and DataAdapters are used to display and manipulate data from databases.
Reading Data into a Dataset
To read data into Dataset, you need to:
Create a database connection and then a dataset object.
Create a DataAdapter object and refer it to the DB connection already created.
Note that every DataAdapter has to refer to a connection object. For example,
SqlDataAdapter refers to SqlDataConnection.
The Fill method of DataAdapter has to be called to populate the Dataset object.
We elaborate the above mentioned steps by giving examples of how each step can be
performed:
1) As we said, our first task is to create a connection to database. We would explore
later that there is no need of opening and closing database connection explicitly while
you deal with DataAdapter objects. All you have to do is, create a connection to
database using the code like this:
SqlConnection con = new SqlConnection ("data source=localhost; uid= sa; pwd= abc;
database=Northwind");
We would use Northwind database by using OleDbConnection. The Code would
Look like:
OleDbConnection con= new OleDbConnection ("Provider =Microsoft.JET.OLEDB.4.0;"
+ "Data Source=C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb");
Prepared By Rohit Mane
2) Now, create a Dataset object which would be used for storing and manipulating
data. You would be writing something like
DataSet myDataSet = new DataSet ("Northwind");
Since the name of source database is Northwind, we have passed the same name in
the constructor.
3) The DataSet has been created but as we said before, this DataSet object can not
directly interact with Database. We need to create a DataAdapter object which would
refer to the connection already created. The following line would declare a DataAdapter
object:
OleDbAdapter myDataAdapter = new OleDbAdapter (CommandObject, con);
The above line demonstrates one of many constructors of OleDbAdapter class. This
constructor takes a command object and a database connection object. The purpose of
command object is to retrieve suitable data needed for populating DataSet. As we
know SQL commands directly interacting with database tables, a similar command can
be assigned to CommandObject.
OleDbCommand CommandObject = new OleDbCommand ("Select * from employee");
Whatever data you need for your Dataset should be retrieved by using suitable
command here. The second argument of OleDbAdapter constructor is connection
object con.
Alternative approach for initializing DataAdapter object:
Place a null instead of CommandObject while you initialize the OleDbAdapter object:
OleDbAdapter myDataAdapter = new OleDbAdapter (null, con);
Then you assign your query to the CommandObject and write:
myDataAdapter.SelectCommand = CommandObject;
4) Now, the bridge between the DataSet and Database has been created. You can
populate dataset by using the Fill command:
myDataAdapter.Fill (myDataSet, "EmployeeData");
The first argument to Fill function is the DataSet name which we want to populate. The
second argument is the name of DataTable. The results of SQL queries go into
DataTable. In this example, we have created a DataTable named EmployeeData and
the values in this table would be the results of SQL query: "Select * from employee". In
this way, we can use a dataset for storing data from many database tables.
5) DataTables within a Dataset can be accessed using Tables. To access
EmployeeData, we need to write:
myDataSet.Tables["EmployeeData"].
To access rows in each Data Table, you need to write:
myDataSet.Tables["EmployeeData].Rows
Prepared By Rohit Mane
Listing 1.1 would combine all the steps we have elaborated so far.
1. <%@ Page Language= "C#" %>
2. <%@ Import Namespace= "System.Data" %>
3. <%@ Import Namespace= "System.Data.OleDb" %>
4. <html>
5. <body>
6.
7. <table border=2>
8. <tr>
9. <td><b> Employee ID </b></td>
10. <td><b> Employee Name </b></td>
11. </tr>
12.
13. <% OleDbConnection con= new OleDbConnection ("Provider
14. =Microsoft.JET.OLEDB.4.0;" + "Data Source=C:\\Program Files\\Microsoft
15. Office\\Office\\Samples\\Northwind.mdb");
16.
17. <%
18. DataSet myDataSet = new DataSet();
19. OleDbCommand CommandObject = new OleDbCommand ("Select * from
20. employee");
Prepared By Rohit Mane
21.
22. OleDbAdapter myDataAdapter = new OleDbAdapter (CommandObject, con);
23.
24. myDataAdapter.Fill (myDataSet, "EmployeeData");
25.
26. foreach (DataRow dr in myDataSet.Tables["EmployeeData"].Rows)
27. {
28. Response.write ("<tr>");
29. for (int j = 0 ; j <2 ; j++)
30. {
31. Response.write ( "<td>" + dr[j].ToString() + "</td"> );
32. }
33. Response.write ("</tr>");
34.
35. %>
36. </table>
37. </body>
38. </html>
The Code above would iterate in all rows of Employee table and display ID and name
of every employee. To Display all columns of Employee Table, Line # 29 would be
replaced by:
for (int j = 0 ; j < dr.Table.Columns.Count ; j++)
Prepared By Rohit Mane
As we said earlier, there is no need of opening and closing database connection
explicitly. DataAdapter class handles both these functions.
Changing Data with a DataSet
Until now, we only focused on reading data by using DataSet and DataAdapter class.
As you know, there are frequent modifications in database tables in real life. These
may include insertions, updates and deletions. How these functions can be handled
using DataSets and DataAdapters?
Deletions in Employee Table
To delete the Employee having id 1001:
1. int i =0;
2. foreach (DataRow dr in ds1.Tables["EmployeeData"].Rows)
3. {
4. i++;
5. if (dr["id"] = = 1001 )
6. break;
7. }
8. EmployeeData.Rows[i].Delete;
Or to Delete 74th row, use:
EmployeeData.Rows[74].Delete;
Updating Employee Table
Prepared By Rohit Mane
To change the name of Employee having id 1001:
8. foreach (DataRow dr in ds1.Tables["EmployeeData"].Rows)
9. if (dr["id"] = = 1001 )
10. dr["name"] = "new name";
Or to change name of employee in 74th row of Table,
EmployeeTable.Rows[74]["name"] = "new name";
Insertions in Employee Table
1. DataRow dr = EmployeeData.NewRow();
2. dr["id"] = "1003";
3. dr["name"] = "Ahmed Albaradi";
(or you can move it like dr[0], dr[1],a., dr[n-1])
4. EmployeeData.Rows.Add(dr);
In this example, we created a new row, set its attributes and then added it to the Data
table.
Writing Changes back to database table
We have discussed that DataSets can not directly interact with Database tables.
Moreover, all the modifications we performed above apply only to EmployeeData,
which is Data Table. Before we discuss how to write back changes, letAs explore
DataAdapter class in a bit more detail:
Prepared By Rohit Mane
We have discussed SelectCommand property which lets the Adapter selects its query.
There are three other properties including UpdateCommand, AddCommand and
DeleteCommand. All these commands would make changes in the database.
However, by using CommandBuilderObject, you donAt have to create all the available
commands. The update, Add and Delete commands are created automatically based
on SelectCommand.
OleDbCommandBuilder mybuilder = new OleDbCommandBuilder (myDataAdapter);
And after writing this statement, you are now in position to make changes back to
database. All you have to do is create a separate dataset for all the modified rows and
then apply the Update command of DataAdapter.
1. DataSet newSet = myDataSet.GetChanges (DataRowState.Modified);
2. myDataAdapter.Update(newSet, "EmployeeData");
GetChanges method would return all the modified rows .The parameter in GetChanges
method can be different. For example:
DataRowState.Added: would return newly added rows
DataRowState.Deleted: would return deleted rows
Datasets store a copy of data from the database tables. However, Datasets can not
directly retrieve data from Databases. DataAdapters are used to link Databases with
DataSets. If we see diagrammatically,
DataSets < ----- DataAdapters < ----- DataProviders < ----- Databases
DataSets and DataAdapters are used to display and manipulate data from databases.
Reading Data into a Dataset
To read data into Dataset, you need to:
Create a database connection and then a dataset object.
Create a DataAdapter object and refer it to the DB connection already created.
Note that every DataAdapter has to refer to a connection object. For example,
SqlDataAdapter refers to SqlDataConnection.
The Fill method of DataAdapter has to be called to populate the Dataset object.
We elaborate the above mentioned steps by giving examples of how each step can be
performed:
1) As we said, our first task is to create a connection to database. We would explore
later that there is no need of opening and closing database connection explicitly while
you deal with DataAdapter objects. All you have to do is, create a connection to
database using the code like this:
SqlConnection con = new SqlConnection ("data source=localhost; uid= sa; pwd= abc;
database=Northwind");
We would use Northwind database by using OleDbConnection. The Code would
Look like:
OleDbConnection con= new OleDbConnection ("Provider =Microsoft.JET.OLEDB.4.0;"
+ "Data Source=C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb");
Prepared By Rohit Mane
2) Now, create a Dataset object which would be used for storing and manipulating
data. You would be writing something like
DataSet myDataSet = new DataSet ("Northwind");
Since the name of source database is Northwind, we have passed the same name in
the constructor.
3) The DataSet has been created but as we said before, this DataSet object can not
directly interact with Database. We need to create a DataAdapter object which would
refer to the connection already created. The following line would declare a DataAdapter
object:
OleDbAdapter myDataAdapter = new OleDbAdapter (CommandObject, con);
The above line demonstrates one of many constructors of OleDbAdapter class. This
constructor takes a command object and a database connection object. The purpose of
command object is to retrieve suitable data needed for populating DataSet. As we
know SQL commands directly interacting with database tables, a similar command can
be assigned to CommandObject.
OleDbCommand CommandObject = new OleDbCommand ("Select * from employee");
Whatever data you need for your Dataset should be retrieved by using suitable
command here. The second argument of OleDbAdapter constructor is connection
object con.
Alternative approach for initializing DataAdapter object:
Place a null instead of CommandObject while you initialize the OleDbAdapter object:
OleDbAdapter myDataAdapter = new OleDbAdapter (null, con);
Then you assign your query to the CommandObject and write:
myDataAdapter.SelectCommand = CommandObject;
4) Now, the bridge between the DataSet and Database has been created. You can
populate dataset by using the Fill command:
myDataAdapter.Fill (myDataSet, "EmployeeData");
The first argument to Fill function is the DataSet name which we want to populate. The
second argument is the name of DataTable. The results of SQL queries go into
DataTable. In this example, we have created a DataTable named EmployeeData and
the values in this table would be the results of SQL query: "Select * from employee". In
this way, we can use a dataset for storing data from many database tables.
5) DataTables within a Dataset can be accessed using Tables. To access
EmployeeData, we need to write:
myDataSet.Tables["EmployeeData"].
To access rows in each Data Table, you need to write:
myDataSet.Tables["EmployeeData].Rows
Prepared By Rohit Mane
Listing 1.1 would combine all the steps we have elaborated so far.
1. <%@ Page Language= "C#" %>
2. <%@ Import Namespace= "System.Data" %>
3. <%@ Import Namespace= "System.Data.OleDb" %>
4. <html>
5. <body>
6.
7. <table border=2>
8. <tr>
9. <td><b> Employee ID </b></td>
10. <td><b> Employee Name </b></td>
11. </tr>
12.
13. <% OleDbConnection con= new OleDbConnection ("Provider
14. =Microsoft.JET.OLEDB.4.0;" + "Data Source=C:\\Program Files\\Microsoft
15. Office\\Office\\Samples\\Northwind.mdb");
16.
17. <%
18. DataSet myDataSet = new DataSet();
19. OleDbCommand CommandObject = new OleDbCommand ("Select * from
20. employee");
Prepared By Rohit Mane
21.
22. OleDbAdapter myDataAdapter = new OleDbAdapter (CommandObject, con);
23.
24. myDataAdapter.Fill (myDataSet, "EmployeeData");
25.
26. foreach (DataRow dr in myDataSet.Tables["EmployeeData"].Rows)
27. {
28. Response.write ("<tr>");
29. for (int j = 0 ; j <2 ; j++)
30. {
31. Response.write ( "<td>" + dr[j].ToString() + "</td"> );
32. }
33. Response.write ("</tr>");
34.
35. %>
36. </table>
37. </body>
38. </html>
The Code above would iterate in all rows of Employee table and display ID and name
of every employee. To Display all columns of Employee Table, Line # 29 would be
replaced by:
for (int j = 0 ; j < dr.Table.Columns.Count ; j++)
Prepared By Rohit Mane
As we said earlier, there is no need of opening and closing database connection
explicitly. DataAdapter class handles both these functions.
Changing Data with a DataSet
Until now, we only focused on reading data by using DataSet and DataAdapter class.
As you know, there are frequent modifications in database tables in real life. These
may include insertions, updates and deletions. How these functions can be handled
using DataSets and DataAdapters?
Deletions in Employee Table
To delete the Employee having id 1001:
1. int i =0;
2. foreach (DataRow dr in ds1.Tables["EmployeeData"].Rows)
3. {
4. i++;
5. if (dr["id"] = = 1001 )
6. break;
7. }
8. EmployeeData.Rows[i].Delete;
Or to Delete 74th row, use:
EmployeeData.Rows[74].Delete;
Updating Employee Table
Prepared By Rohit Mane
To change the name of Employee having id 1001:
8. foreach (DataRow dr in ds1.Tables["EmployeeData"].Rows)
9. if (dr["id"] = = 1001 )
10. dr["name"] = "new name";
Or to change name of employee in 74th row of Table,
EmployeeTable.Rows[74]["name"] = "new name";
Insertions in Employee Table
1. DataRow dr = EmployeeData.NewRow();
2. dr["id"] = "1003";
3. dr["name"] = "Ahmed Albaradi";
(or you can move it like dr[0], dr[1],a., dr[n-1])
4. EmployeeData.Rows.Add(dr);
In this example, we created a new row, set its attributes and then added it to the Data
table.
Writing Changes back to database table
We have discussed that DataSets can not directly interact with Database tables.
Moreover, all the modifications we performed above apply only to EmployeeData,
which is Data Table. Before we discuss how to write back changes, letAs explore
DataAdapter class in a bit more detail:
Prepared By Rohit Mane
We have discussed SelectCommand property which lets the Adapter selects its query.
There are three other properties including UpdateCommand, AddCommand and
DeleteCommand. All these commands would make changes in the database.
However, by using CommandBuilderObject, you donAt have to create all the available
commands. The update, Add and Delete commands are created automatically based
on SelectCommand.
OleDbCommandBuilder mybuilder = new OleDbCommandBuilder (myDataAdapter);
And after writing this statement, you are now in position to make changes back to
database. All you have to do is create a separate dataset for all the modified rows and
then apply the Update command of DataAdapter.
1. DataSet newSet = myDataSet.GetChanges (DataRowState.Modified);
2. myDataAdapter.Update(newSet, "EmployeeData");
GetChanges method would return all the modified rows .The parameter in GetChanges
method can be different. For example:
DataRowState.Added: would return newly added rows
DataRowState.Deleted: would return deleted rows
0 comments:
Post a Comment