Most ASP.NET developers know their way around Microsoft SQL Server. From a fresh installation of Visual Studio or Visual Web Developer they can start building data driven applications targeting this Database software. Sometimes SQL server is not a choice we have; or perhaps the SQL Server space that the host provider is offering is just not enough for our web application needs but the MySql space is.
MySQL
MySQL is an Open Source Database Server Software written in C++ and its source code is available according to the General Public License . MySQL is very popular for PHP developers as most of the hosting providers offer the LAMP software bundle (Linux, Apache, MySQL, PHP).
You can get MySQL for Windows here . The installation procedure is not very difficult as most of the default choices are ok. Just make sure to configure the server instance as a Developer Machine (if it's on a server you're installing then select Server Machine or Dedicated Server Machine) and the database usage to Multifunctional (this is a general purpose database type). Also, enable TCP/IP and set MySQL to run as a Windows Service.
I won't go into details on the installation procedure or screens as it is a very straight forward process.
When MySQL is ready and installed you can then download the MySQL GUI Tools which is a 3 program bundle that includes:
- MySQL Administrator
- MySQL Query Browser
- MySQL Migration Toolkit
These tools allow you to manage the database, query its data and help you move data. When you finish installing the GUI Tools run the MySQL Administrator and create your first database.
Here you can create your first database (or perhaps your 11th). Click on Catalogs in the Icon Menu in the left and below you will see the Schemas (databases) already created.
Right click and you can create a new schema for your tables. Then select the new schema and right click to create a new table. The screen below appears. For this excercise I'll create a new table named Employees with 2 fields: ID and NAME as shown below.
From the Tools menu in the MySQL Administrator select the option MySQL Browser. The screen below appears and there you can select our newly created database by double clicking on it. Let's insert some records by executing an SQL Insert statement as shown below.
Highlight the code you want to run and press the execute button below the Query Menu Item. Now our table is ready to work with from ASP.NET
ASP.NET
We can connect to our new MySQL database server using ODBC but instead let's use a more "NET" approach. MySQL team developed drivers called Connectors that allow us to connect to MySQL; and one of them is a NET fully managed ADO.NET provider. That latest version as of this writing is 6.0.4 and you can download it here.
Follow the standard installation wizard and make sure to register the new assembly in the GAC. Now we have a new Namespace, MySql.Data.MySqlClient. If you have ever touched System.Data.SqlClient then you will feel right at home with this namespace.
Let's write some code. Create a new webform and just insert a GridView control in it. That's all we're going to need. Then write the following code in the Page_Load event:
1 Imports MySql.Data
2 Imports MySql.Data.MySqlClient
3 Imports System.Data
4 Partial Class mySQL_Page
5 Inherits System.Web.UI.Page
6
7 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
8 Dim DB As New MySqlConnection("Server=localhost;user id=root;password=pass;database=mysqltest;")
9 DB.Open()
10 Dim mycommand As New MySqlCommand("SELECT * FROM EMPLOYEES", DB)
11 Dim DA As New MySqlDataAdapter(mycommand)
12 Dim DS As New DataSet
13 DA.Fill(DS)
14 DB.Close()
15 GridView1.DataSource = DS
16 GridView1.DataBind()
17 End Sub
18 End Class
View the newly created page in your browser and voila:
Everything you need to work with MySql will be available in the MySql.Data namespace. Adapters, readers, etc.
Hope it helps. Happy programming. 
Suscribirse