Programming with MySQL and .NET Technologies - Article
(Page 2 of 3 )
Dataset Pros and Cons
Working with multiple tables: A Datasets can contain more than one table of results, you can work with the tables individually or navigate between them as parent-child tables, it also has ability to enforce rules of integrity in memory rather than in at database level.
Data from multiple sources: A Dataset can represent data from different sources (from different databases, from XML files, spreadsheets and so on) and all in the same Dataset, you can manipulate it and relate it in a homogeneous format as if it had come from a single format. This means, regardless of the data source, your code interacts with Dataset in the same way. This allows you to change the underlying data source without changing your code.
Exchange with other application: Dataset includes extensive support for serializing data as XML and reading and writing XML Schemas.
Reuse: A Dataset allows us to work with the same records repeatedly without requiring the database.
Performance: The most important benefit of using Datasets, however, is improved performance. Since the Dataset is disconnected from the underlying databases, your code will make fewer calls to the database, boosting performance.
The above performance optimization comes with a price. Since the Dataset object is disconnected from the underlying database, there is always chance that the data is out of date. Data concurrency issues arise when multiple users have access to the same data and any single user can update the data without the other users’ knowledge.Fortunately, the Dataset object comes with built-in support for catching data concurrency issues as they arise so that application can react accordingly. Read more about this issues from this link.
Walkthrough Example In this walkthrough you will create a windows application that display data in data grid, DataGrid control is one of the flexible and powerful controls available in Windows Forms. You can add new records (by clicking the last row of the grid), update records (by changing the existing value in the grid cells) and delete records (by clicking the DEL button from the keyboard) on a data grid with very little effort. A very important point is that Update and Delete statements do not work unless the database table contains a primary key.
At first download and install Odbc_net.msi reference library. Open a new project and add to Reference.
Fig: Windows form (insert, update or delete a record)
Now add following namespace to work with ODBC database. If you want to know more about namespaces follow this link. Please find the support materials with this article for full source code developed with Microsoft Visual Studio.NET.
Fig. Data adapters copy data to and from a database
using Microsoft.Data.Odbc;
private void Form1_Load(object sender, System.EventArgs e)
{
PopulateDataGrid();
}
private void PopulateDataGrid()
{
clsDbAccess DbAccess=new clsDbAccess();
MyConnStr=DbAccess.DbAccess_GL;
sConnString=MyConnStr;
Conn = new OdbcConnection(sConnString);
ds = new System.Data.DataSet();
try
{
Conn.Open();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
strSQL ="select sbiStudid, sbiFirstName, sbiLastName, sbilogin, sbipassword from astabstudentbaseinformation";
da =new OdbcDataAdapter(strSQL,Conn );
CmdBld= new OdbcCommandBuilder(da);
da.Fill(ds,"Students");
dataGrid1.DataSource = ds.Tables["Students"].DefaultView;
Conn.Close();
Conn.Dispose();
}
private void button1_Click(object sender, System.EventArgs e)
{
clsDbAccess DbAccess=new clsDbAccess();
MyConnStr=DbAccess.DbAccess_GL;
sConnString=MyConnStr;
Conn = new OdbcConnection(sConnString);
da =new OdbcDataAdapter(strSQL,Conn );
CmdBld= new OdbcCommandBuilder(da);
cmdBuilder=new OdbcCommandBuilder();
Copy_of_DS=new DataSet();
// D A T A has modified
if(ds.HasChanges(DataRowState.Modified))
{
MessageBox.Show("modified:");
Copy_of_DS=ds.GetChanges(DataRowState.Modified);
int ChangeRecords;
ChangeRecords=da.Update(Copy_of_DS,"Students");
if(ChangeRecords>0)
{
MessageBox.Show(ChangeRecords.ToString());
}
}
//Data is deleted
if(ds.HasChanges(DataRowState.Deleted))
{
Copy_of_DS=ds.GetChanges(DataRowState.Deleted);
int ChangeRecords;
ChangeRecords=0;
ChangeRecords=da.Update(Copy_of_DS,"Students");
if(ChangeRecords>0)
{
MessageBox.Show(ChangeRecords.ToString());
}
}
//Data is added
if(ds.HasChanges(DataRowState.Added))
{
Copy_of_DS=ds.GetChanges(DataRowState.Added);
int ChangeRecords;
ChangeRecords=0;
try
{
ChangeRecords=da.Update(Copy_of_DS,"Students");
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
if(ChangeRecords > 0)
{
MessageBox.Show(ChangeRecords.ToString()+ " Record Added.");
}
}
ds.AcceptChanges();
dataGrid1.Refresh();
}
Next: Conclusion >>
More C# Articles
More By Ahm Asaduzzaman
/ 54 
