Introduction
Beginning MYSQL 5.0
With
Visual Studio.NET 2003
- By Anil Mahadev..
Database Technologist and Enthusiast
Welcome to the Wonderful of MYSQL 5, a phenomenal release in the History of MYSQL Development.
There are a host of features to discuss, but due to the audience knowledge in mind, I will be discussing only the newer
features of the MYSQL Database 5.0.
Walkthrough with MySQL 5 with .NET
In this tutorial, I will dive into the nitty gritties of MYSQL 5 Application Development using the MYSQL Connector for .NET
for Visual Studio.NET 2003 and build a simple Database Application using VC# and MYSQL 5.
This tutorial is a Level 200 session, which means that pre-requisite knowledge of the products is necessary.
System Requirements:
A basic P4 Machine with 512 MB RAM and about 40 GB HDD, a pointing device,
A keyboard and a monitor.
Software Requirements.
Development Software
Windows 2000 and above.
Microsoft Visual Studio.NET 2003 (Professional or Team System).
(Any edition having C# language support).
Database Software
MySQL Server 5.0 RC Download Link
http://dev.mysql.com/downloads/mysql/5.0.html
Graphical User Interface tools to help MySQL Users
MySQL Administrator 1.1
MySQL Query Browser 1.1
SQL Manager
MySQL .NET and ODBC Connectors for connecting to .NET
ODBC Driver
.NET Data Provider Driver
5) Finally the urge to learn new things and adapt accordingly.
We shall first discuss some of the new features of MYSQL 5.0
Now that we have all the information and tools needed,
lets build our Application and write some Code.
Building our Database.
Create a database called DEVIQ.
CREATE DATABASE DEVIQ;
Create a table called MySQLDemo
CREATE TABLE `mysqldemo` (
`ID` int(11) NOT NULL,
`Name` varchar(255) default NULL,
`Email` varchar(255) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Grant appropriate permissions
# Grant privileges for user 'devIQ'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'devIQ'@'localhost' WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON deviq.mysqldemo TO 'devIQ'@'localhost' WITH
GRANT OPTION;
GRANT SELECT (Email), INSERT (Email), UPDATE (Email), REFERENCES (Email) ON deviq.mysqldemo TO 'devIQ'@'localhost';
GRANT SELECT (ID), INSERT (ID), UPDATE (ID), REFERENCES (ID) ON deviq.mysqldemo TO 'devIQ'@'localhost';
GRANT SELECT (Name), INSERT (Name), UPDATE (Name), REFERENCES (Name) ON deviq.mysqldemo TO 'devIQ'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON DEVIQ.mysqldemo TO 'devIQ'@'localhost' WITH
GRANT OPTION;
GRANT SELECT (Email), INSERT (Email), UPDATE (Email), REFERENCES (Email) ON DEVIQ.mysqldemo TO 'devIQ'@'localhost';
GRANT SELECT (Name), INSERT (Name), UPDATE (Name), REFERENCES (Name) ON DEVIQ.mysqldemo TO 'devIQ'@'localhost';
GRANT SELECT (ID), INSERT (ID), UPDATE (ID), REFERENCES (ID) ON DEVIQ.mysqldemo TO 'devIQ'@'localhost';
Now that our database and table is created we can build our .NET application and fill data into this table called MYSQLDemo.
To make sure our Application will perform as expected, please make sure all the software requirements are installed and ready
to go.
Our current objective is to just get some data from our MySQL Table that’s it!
In later editions, I will delve into advanced topics of working with Triggers and Stored Procedures
In this application , we have two buttons, Load Guest List and Exit. The Load Guest List button will enable us to view the
guest list from our table mysqldemo.
The exit button, will help us to exit from the application.
The code needed to get started are given below.
1. Please import the following Reference to your project.
from the following location where you have installed your MySQL Connector for .NET. Since we are using Visual Studio.NET
2003, we should import the .NET 1.1 version
x:\Program Files\MySQL\MySQL Connector Net 1.0.6\bin\.NET 1.1\MySql.Data.dll.
Replace the x with your drive letter.
2. Once the reference is added in the project, you will also have to import the namespace in our form. By typing
Using MYSQL.Data.MySQLClient
Now type the following code for the Load Guest List Button
//Load the Guest List
public void LoadGuests()
{
try
{
//create a new mysqlconnection
MySqlConnection mycon = new MySqlConnection("datasource=localhost;username=replace with your
userid;password=replace with your password;database=DEVIQ");
//create a mysql DataAdapter
MySqlDataAdapter myadp = new MySqlDataAdapter("Select * from mysqldemo",mycon);
//create a dataset
DataSet myds = new DataSet();
//now fill and bind the DataGrid
myadp.Fill(myds,"mysqldemo");
dataGrid1.DataSource = myds.Tables["mysqldemo"].DefaultView;
dataGrid1.SetDataBinding(myds,"mysqldemo");
}
catch(MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnLoad_Click(object sender, System.EventArgs e)
{
LoadGuests();
}
Now type the following code for the Exit Button
private void btnExit_Click(object sender, System.EventArgs e)
{
this.Close();
}
After typing all of the above code in the respective buttons.
Press F5 now.
And View the Result as shown in Figure 1.2
Summary
Wow !!! wasn’t that easy, MySQL has a lot more features to offer its wide user base and corporate customers.
I hope you have enjoyed learning MySQL with me as much as I have enjoyed writing about it :).
Please do drop me a line and let me know your feedback on the article, or anything in general with respect to databases :).
Please feel free to mail me your comments and valuable inputs on how I can write better in MYSQL and .NET.
You can reach me @ Anil Mahadev
anilm001@gmail.com
MySQL Logo is Copyright of MYSQL AB Corporation and other International Countries where present.
Microsoft Windows and Microsoft Visual Studio.NET logo  are copyright of Microsoft Corporation, USA and other International Countries where present.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|