|
|
Beginning MYSQL 5.0 with Visual Studio.NET 2003
Posted On January 18, 2007 by Priyadarshan Roy filed under
Welcome to the wonderful world of MYSQL 5 RC (Release Candidate), a phenomenal release in the history of MYSQL Development!
Though there are a host of features to talk about, I will be discussing only the newer features. 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 prerequisite 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, and Microsoft Visual Studio.NET 2003 (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 (http://dev.mysql.com/downloads/administrator/1.1.html).
MySQL Query Browser 1.1 (http://dev.mysql.com/downloads/query-browser/1.1.html).
You may also want to try out MySQL Manager Lite 3.4, a neat tool from EMS Corporation (This is a free version). EMS MySQL Manager Lite for Windows (full installation package) is available at (http://sqlmanager.net/products/mysql/manager/download).
MySQL .NET and ODBC Connectors for connecting to Visual Studio.NET 2003 or 2005 Beta 2.
ODBC Driver http://dev.mysql.com/downloads/connector/odbc/3.51.html.
.NET Data Provider Driver http://dev.mysql.com/downloads/connector/net/1.0.html.
Finally, the urge to learn new things and adapt accordingly.
What's New in MySQL 5.0?
The following features are implemented in MySQL 5.0: blissful
· BIT Data Type: BIT[(M)]: A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
This data type was added in MySQL 5.0.3 for MyISAM and extended in 5.0.5 to MEMORY, InnoDB and BDB. Before 5.0.3, BIT was a synonym for TINYINT(1);
· Cursors: Elementary support. A real need of the hour for most database professionals. What would we do without them, as we have to navigate through each record; it’s a blessing feature in MYSQL 5;
· Data Dictionary (Information Schema): provides access to database metadata;
· Instance Manager: Can be used to start and stop MySQL Server, even from a remote host;
· Precision Math: MySQL 5 introduces precision math, i.e. numeric value handling that results in more accurate results and more control over invalid values than in earlier versions of MySQL. Precision math is based on two implementation changes – introduction of new SQL modes in MySQL 5.0.2 that control how strict the server is about accepting or rejecting invalid data and introduction in MySQL 5.0.3 of a library for fixed-point arithmetic;
· Storage Engines: New storage engines include ARCHIVE and FEDERATED.
The ARCHIVE storage engine was added in MySQL 4.1.3. It is used for storing large amounts of data without indexes in a very small footprint.
The FEDERATED storage engine was added in MySQL 5.0.3. This engine stores data in a remote database. In this release, it works with MySQL only, using the MySQL C Client API. Future releases will be able to connect to other data sources using other driver or client connection methods;
· Stored Procedures: Implementation of Stored Procedures.
Stored procedures and functions are a new feature in MySQL version 5.0. A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing individual statements but can refer to the stored procedure instead.
Stored procedures can provide improved performance because less information needs to be sent between the server and client. The tradeoff is that this does increase the load on the database server system because more work is done on the server side and less on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.
· Strict Mode and Standard Error Handling: Strict Mode means a mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.
Standard Error Handling values and the symbols in parentheses correspond to definitions in the include/mysqld_error.h MySQL source file.
For users of IBM DB2 here’s some news, MySQL also shares the SQLSTATE error Handling code format.
The SQLSTATE values correspond to definitions in the include/sql_state.h MySQL source file.
SQLSTATE error codes are displayed only if you use MySQL version 4.1 and up. SQLSTATE codes were added for compatibility with X/Open, ANSI and ODBC behavior.
Message values correspond to the error messages that are listed in the sql/share/english/errmsg.txt file. %d or %s represent numbers or strings that are substituted into the messages %when they are displayed.
Triggers: A new and wanted feature for most of us J. I have included a sample CREATE TRIGGER Statement below.
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
· VARCHAR data type: True VARCHAR support. Maximum effective length of VARCHAR is 65,532 bytes now and no stripping of trailing whitespace occurs.
· Views: Views (including updatable views) are implemented in MySQL Server version 5.0. Views are available in binary releases from 5.0.1 and up.
Now that we have covered the new features of MySQL 5.0, let us build our application and write some code.
Building our database
Create a database called DEVIQ.
CREATE DATABASE DEVIQ;
Create a table called MySQLDemo (code 1).
Code 1
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
Next, grant appropriate permissions (code 2).
Code 2
# 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 are ready, we can build our .NET application and fill data into the table mysqldemo. To guarantee that our application will perform as expected, please ensure that all the software requirements have been installed and are ready.
Since MySQL has not come out with a GUI-based interface, unlike SQLDataAdpater or OLEDB DataAdapters, we shall be hand coding our application. 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.
Figure 1 depicts how our application will look.
In this application, we have two buttons, Load Guest List and Exit. While the Load Guest List button will enable us to view the guest list from MYSQLDemo table, the Exit button will help us exit from the application.
Essential steps to get started are given below:
1. Please import the following Reference to your project from the location where you have installed 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 ‘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 code 3 for the Load Guest List Button.
Code 3
//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. The result will be as shown in figure 2.
Wow! Wasn’t that easy, MySQL has a lot more features to offer to its wide user base and corporate customers. I hope you have enjoyed learning MySQL J.
Please do drop me a line and let me know your feedback on the article, or anything in general with respect to databases J.
Feel free to mail me your comments and valuable inputs on how I can write better in MYSQL and .NET.
The author can be found at: databasearchitect@gmail.com.
Though there are a host of features to talk about, I will be discussing only the newer features. 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 prerequisite 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, and Microsoft Visual Studio.NET 2003 (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 (http://dev.mysql.com/downloads/administrator/1.1.html).
MySQL Query Browser 1.1 (http://dev.mysql.com/downloads/query-browser/1.1.html).
You may also want to try out MySQL Manager Lite 3.4, a neat tool from EMS Corporation (This is a free version). EMS MySQL Manager Lite for Windows (full installation package) is available at (http://sqlmanager.net/products/mysql/manager/download).
MySQL .NET and ODBC Connectors for connecting to Visual Studio.NET 2003 or 2005 Beta 2.
ODBC Driver http://dev.mysql.com/downloads/connector/odbc/3.51.html.
.NET Data Provider Driver http://dev.mysql.com/downloads/connector/net/1.0.html.
Finally, the urge to learn new things and adapt accordingly.
What's New in MySQL 5.0?
The following features are implemented in MySQL 5.0: blissful
· BIT Data Type: BIT[(M)]: A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
This data type was added in MySQL 5.0.3 for MyISAM and extended in 5.0.5 to MEMORY, InnoDB and BDB. Before 5.0.3, BIT was a synonym for TINYINT(1);
· Cursors: Elementary support. A real need of the hour for most database professionals. What would we do without them, as we have to navigate through each record; it’s a blessing feature in MYSQL 5;
· Data Dictionary (Information Schema): provides access to database metadata;
· Instance Manager: Can be used to start and stop MySQL Server, even from a remote host;
· Precision Math: MySQL 5 introduces precision math, i.e. numeric value handling that results in more accurate results and more control over invalid values than in earlier versions of MySQL. Precision math is based on two implementation changes – introduction of new SQL modes in MySQL 5.0.2 that control how strict the server is about accepting or rejecting invalid data and introduction in MySQL 5.0.3 of a library for fixed-point arithmetic;
· Storage Engines: New storage engines include ARCHIVE and FEDERATED.
The ARCHIVE storage engine was added in MySQL 4.1.3. It is used for storing large amounts of data without indexes in a very small footprint.
The FEDERATED storage engine was added in MySQL 5.0.3. This engine stores data in a remote database. In this release, it works with MySQL only, using the MySQL C Client API. Future releases will be able to connect to other data sources using other driver or client connection methods;
· Stored Procedures: Implementation of Stored Procedures.
Stored procedures and functions are a new feature in MySQL version 5.0. A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing individual statements but can refer to the stored procedure instead.
Stored procedures can provide improved performance because less information needs to be sent between the server and client. The tradeoff is that this does increase the load on the database server system because more work is done on the server side and less on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.
· Strict Mode and Standard Error Handling: Strict Mode means a mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.
Standard Error Handling values and the symbols in parentheses correspond to definitions in the include/mysqld_error.h MySQL source file.
For users of IBM DB2 here’s some news, MySQL also shares the SQLSTATE error Handling code format.
The SQLSTATE values correspond to definitions in the include/sql_state.h MySQL source file.
SQLSTATE error codes are displayed only if you use MySQL version 4.1 and up. SQLSTATE codes were added for compatibility with X/Open, ANSI and ODBC behavior.
Message values correspond to the error messages that are listed in the sql/share/english/errmsg.txt file. %d or %s represent numbers or strings that are substituted into the messages %when they are displayed.
Triggers: A new and wanted feature for most of us J. I have included a sample CREATE TRIGGER Statement below.
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
· VARCHAR data type: True VARCHAR support. Maximum effective length of VARCHAR is 65,532 bytes now and no stripping of trailing whitespace occurs.
· Views: Views (including updatable views) are implemented in MySQL Server version 5.0. Views are available in binary releases from 5.0.1 and up.
Now that we have covered the new features of MySQL 5.0, let us build our application and write some code.
Building our database
Create a database called DEVIQ.
CREATE DATABASE DEVIQ;
Create a table called MySQLDemo (code 1).
Code 1
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
Next, grant appropriate permissions (code 2).
Code 2
# 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 are ready, we can build our .NET application and fill data into the table mysqldemo. To guarantee that our application will perform as expected, please ensure that all the software requirements have been installed and are ready.
Since MySQL has not come out with a GUI-based interface, unlike SQLDataAdpater or OLEDB DataAdapters, we shall be hand coding our application. 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.
Figure 1 depicts how our application will look.
In this application, we have two buttons, Load Guest List and Exit. While the Load Guest List button will enable us to view the guest list from MYSQLDemo table, the Exit button will help us exit from the application.
Essential steps to get started are given below:
1. Please import the following Reference to your project from the location where you have installed 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 ‘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 code 3 for the Load Guest List Button.
Code 3
//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. The result will be as shown in figure 2.
Wow! Wasn’t that easy, MySQL has a lot more features to offer to its wide user base and corporate customers. I hope you have enjoyed learning MySQL J.
Please do drop me a line and let me know your feedback on the article, or anything in general with respect to databases J.
Feel free to mail me your comments and valuable inputs on how I can write better in MYSQL and .NET.
The author can be found at: databasearchitect@gmail.com.