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,
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");
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
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.
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");
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
To summarize:
- Datasets store a copy of data from the database tables.
- Datasets can not directly retrieve data from Databases.
- DataAdapters are used to link Databases with DataSets.
- To populate dataset, db connection is created which is followed by creating a DataAdapter and calling its Fill method.
- OleDbCommand class can be used for applying desired SQL command on DataAdapter object. Dataset would be populated according to the selection criteria given in this command.
- DataTables contain results of SQL query.
- Modifications in DataTable can be done which would be later written on database by GetChanges method of Dataset.
