Connecting MySQL with .NET - Revisited July 6, 2008
Posted by prasu in : Databases, Tips and Tricks , trackbackToday, 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"
Comments»
Really cool! Is this somehow better than ODBC?