Home arrow C# arrow Page 2 - Programming with MySQL and .NET Technologies
C#

Programming with MySQL and .NET Technologies


A topic not covered by many is that with entales developing web sites with MySQL within the .Net Framework. Ahm illustrates how you can use these two technologies under the one roof using the Dataset family.

Author Info:
By: Ahm Asaduzzaman
Rating: 4 stars4 stars4 stars4 stars4 stars / 75
July 29, 2003
TABLE OF CONTENTS:
  1. · Programming with MySQL and .NET Technologies
  2. · Article
  3. · Conclusion

print this article
SEARCH DEVARTICLES

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();
}


blog comments powered by Disqus
C# ARTICLES

- Introduction to Objects and Classes in C#, P...
- Visual C#.NET, Part 1: Introduction to Progr...
- C# - An Introduction
- Hotmail Exposed: Access Hotmail using C#
- Razor Sharp C#
- Introduction to Objects and Classes in C#
- Making Your Code CLS Compliant
- Programming with MySQL and .NET Technologies
- Socket Programming in C# - Part II
- Socket Programming in C# - Part I
- Creational Patterns in C#
- Type Conversions
- Creating Custom Delegates and Events in C#
- Inheritance and Polymorphism
- Understanding Properties in C#

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials