Using DataSet & DataTable

A DataSet is used for the disconnected approach. We connect to the database, fill in the DataSet with the data and go offline. The DataSet is designed as an offline container of data. Actually filling of data in a DataSet need not be necessarily through a database, it could be through any file also or straight away from a device. To fill in a DataSet we need a DataAdapter. A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. Take a look at the following program. We have used the OLE DB .NET Data Provider. We have used Microsoft Access to create the account table.

class myclass
{

static void Main( string[] args)
{

string str="Provider=Microsoft.Jet.OLEDB.4.0 ;Data
Source=c:\\bank.mdb";
string cmd = "SELECT accno, name, balance from account" ;
OleDbDataAdapter dest= new OleDbDataAdapter( cmd, str ) ;
OleDbCommandBuilder mybuilder= new
OleDbCommandBuilder (dest) ;
DataSet ds = new DataSet ( ) ;
dest.Fill ( ds, "account" ) ;
DataTable dt = ds.Tables["account"] ;
DataRow nrow = ds.Tables["account"].NewRow ( ) ;
nrow[0] = 6;
nrow[1] = "Rashi";
nrow[2] =76000 ;
ds.Tables["account"].Rows.Add ( nrow ) ;
dest.Update ( ds, "account" ) ;

}

}

After initializing the command and connection strings, we created the DataAdapter object. This object is passed to the constructor of the OleDbCommandBuilder class. This is because the DataAdapter class does not automatically generate the SQL statements to make changes in the database. The OleDbCommandBuilder class does so. Now we need to populate a DataSet with the DataAdapter. The Fill( ) method does the filling of DataSet. The statement

ds.Tables["account"]

returns the table "account" from the DataSet. Tables is a property belonging to the DataSet class. It gets the collection of Tables contained in the DataSet. "account" is an indexer on the collection of tables. We collected this "account" table in a new DataTable object: - dt. We then created a new row in the account table. Next we initialized it with values. Now we need to add it to the table. This is achieved through the following

ds.Tables["account"].Rows.Add ( nrow ) ;

Rows is a property belonging to the DataTable class. It gets the collection of rows contained in the table i.e. it returns a DataRowCollection object. This class has a method Add( ) in it. The method Add( ) adds a DataRow to the DataRowCollection. Now to reconcile the changes in the database we use the Update( ) method. This method calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable.