Connecting MySQL with .NET - Revisited July 6, 2008
Posted by prasu in : Databases, Tips and Tricks , 1 comment so farToday, I came across another way to connect to a MySQL database in .NET
This is done through MySQL Connector/Net (We are using v 5.1.6)
MySQL Connector provides a namespace ‘MySql.Data.MySqlClient’ which gives you objects such as-
MySqlConnection - To establish a connection
MySqlCommand- Specify the MySQL Command
MySqlDataAdapter- Form the link between the dataset and the database
In order to be able to use these objects, you must first download the Connector from the MySQL website here
Therafter, in order to use this in your VB.NET Project, goto Project Menu -> Add Reference -> Under the .NET tab, Select MySQL.Data
You will need an import statement too as shown in the snippet that follows.
The connection string in this case would change to-
Server=127.0.0.1;Uid=<username>;Pwd=<password>;Database=<database name>;
Read the Documentation here
One difference that is worthy of mention is the manner in which a parameterized query is constructed. It differs from the type we used earlier for ODBC. Here is a small snippet to illustrate the same-
Imports MySql.Data.MySqlClient
Dim cn As New MySqlConnection(" Server=127.0.0.1;Uid=root;Pwd=;Database=logindb;")
Dim cmd As New MySqlCommand("select * from login where username=?user and password=?pass", cn)
cmd.Parameters.Add("?username", MySqlDbType.VarChar, 80).Value = Me.TextBox1.Text
cmd.Parameters.Add("?pass", MySqlDbType.VarChar, 80).Value = Me.TextBox2.Text
Dim ds As New DataSet
Dim adp As New MySqlDataAdapter(cmd)
adp.Fill(ds, "login")
Me.DataGridView1.DataSource = ds
Me.DataGridView1.DataMember = "login"
Connecting to MySQL in .NET June 23, 2008
Posted by tuse in : Technical , 2 commentsAll corporate data is stored in one database or the other. Therefore, one of the most common requirements in application development is to access that data which is already present in a table.
To do so, applications require some form of data access. We chose Open Database Connectivity (ODBC) for the purpose.
Read more about ODBC in the wiki
Ok, so we are going to use the ODBC driver to connect our MySQL Database. But as shown in the figure, we still need the Connector. This may be obtained from the MySQL site here
Version 5.1 is the latest, we use version 3.51
So this Connector helps our application to interact with the database.
An important property that is required while connecting programatically to a database using code in your application is the Connection String.
The connection String for a connect to a MySQL database using MySQL Connector (ODBC 3.51) is -
Driver={MySQL ODBC 3.51 Driver};Server=localhost;
Database=db name; User=username;Password=password;
This can be used with the MySQL ODBC 5.1 too. Just change the Driver name in the connection string.
Here, the server is given as localhost as we have our database on the same computer as the application (remember we used WAMP). If it be on a server, just give the host there.
Note down the connection string, it will be used in all database applications.
