ASP.NET 36 :MySQL Getting started

MySQL - Getting started

The easiest way to use MySQL with ASP.NET is to use the MySQL ODBC Connector from MySQL AB. If you're work is being hosted by a company which supports MySQL, they have probably already installed this, but if you're testing your code on your own machine with your own instance of MySQL, then you need to install this. MySQL Connector/ODBC 3.51 can be downloaded from this page: http://dev.mysql.com/downloads/connector/odbc/3.51 .html 

Once installed, you will be able to connect to your MySQL database server. Doing so is quite easy, but we will also need some data to test on. During the next chapters, we will be using a table called test_users. You can find the SQL code to create and fill the table with test data below. Run it trough your favorite MySQL client's SQL function, or use the command prompt to import into a new or existing database. 

First of all, let's create a new project for this in Visual Studio. Secondly, let's store the connection information for the database in once place, so we can reuse all over our application. We will do this with the web.config file. You can add it by right clicking the project name in the Solution Explorer, and selecting "Add New Item". From the dialog that pops up, select "Web Configuration File". Press Add, and the file will be added to your project. It will automatically be opened. Now, find the part that says and replace it with this:

<connectionStrings>
  <add name="MySQLConnStr" connectionString="DRIVER={MySQL ODBC 3.51 Driver};Database=YourDatabase;Server=localhost;UID=YourUsername;PWD=YourPassword;"/>
</connectionStrings>

By doing so, we can access the connection string from all over the application. You should replace 3 values in it: YourDatabase, YourUsername and YourPassword. They should of course be replaced with the name of the database that you care to use, as well as the username and password for one of the users of the MySQL database server. In the next chapter, I will show you how we retrieve the value and use it. 

Here is the SQL code to create and fill our test table with some data:

CREATE TABLE `test_users` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `country` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

INSERT INTO `test_users` VALUES (1,'John','USA');
INSERT INTO `test_users` VALUES (2,'Mark','Canada');
INSERT INTO `test_users` VALUES (3,'Lukas','Germany');
INSERT INTO `test_users` VALUES (4,'Ingrid','Sweden');