Connecting to MySQL using Connector.NET with ASP.NET

Before you read, I consider that you are familiar with the following topics.

* MySQL Connector.NET
* Database Administration with MySQL
* Structured Query Languages
* and ASP.NET

Before writing a code, let’s add our third-party app library (connector.NET) in our Bin directory. Why? We need to add a reference in its base class since we’re going to use it as our data provider. One example is to access our mysql using queries. For that, I assume that you already have the binaries of MySQL Connector.NET. What I use in this tutorial is MySQL the Connector.NET 5.2.2, which is still in beta version. Bytheway, I don’t recommend using beta versions for production purposes. Now you can grab one of them in here.  After downloading the binaries install it and make sure that you have the installer pack and not the source. Make sure that you add another user for yourself to be used in our testing.

Here, I’ll write the code in VB since its common to everybody. You might want the C# sample that is included for download as well as the VB sample.

Now, let’s jump to the point. Let’s fire-up our Microsoft Visual Web Developer Express or Visual Studio 2005 and start a new web project. In this case, I will refer you to the tutorial on how to start a new web project here and here.

After starting a new project, will have to open our web.config file and add our ConnectionString. In this case, under your <configuration></configuration> schema you can see the <connectionStrings/> tag in place. Replace it with the following:

  <connectionStrings>
    <add name="MySqlConnection" connectionString="server=localhost;database=testing;uid=dba;pwd=sql;pooling=false;"/>
  </connectionStrings>


Databse property should be set with your database name as well as the user and password. Will have to leave the pooling option to false since we’re not using it.

Now, let’s add a folder App_Code which ASP.NET reserves as application folders. In your App_Code, add a class in it and rename it with DatabaseProvider.vb and DatabaseProvider.cs for C# project.

Here’s the directory structure of the tutorial looks like.

 


Open the class to begin with the coding. And write the following code as follow:


Imports System.Configuration
Imports MySql.Data.MySqlClient

 Public Class DatabaseProvider
    Public objConnection As MySqlConnection

    Function connect_db() As Boolean
        Try
            objConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("MySqlConnection").ConnectionString)
 
           objConnection.Open()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Function close_db() As Boolean
        Try
            If objConnection.State = Data.ConnectionState.Open Then
                objConnection.Close()
            End If
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
End Class


same as usual we import System.Configuration to be able to use the Connectionstrings property. We also imported MySql.Data.MySqlClient namespace from our third-party class from MySQL. So why do we need to put this code inside a class? Good question. So that we can easily access the methods in all our asp.net applications. We can then load a new instance of the class and the expose it in our pages. So how can we do it? Let’s go to our Default.aspx code-behind. I’m not going to explain every line above since I’ve noted above when you first read this tutorial.


Our code-behind should look like this:


Imports MySql.Data.MySqlClient

Partial Class _Default
    Inherits System.Web.UI.Page


    Dim db As New DatabaseProvider

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If db.connect_db() Then
            Response.Write("Now we're connected!")
        Else
            Response.Write("No we're not!")
        End If
    End Sub
End Class


Above, we declared db as a new instance of DatabaseProvider class in our DatabaseProvider.vb and expose the methods in it. Now try to run our application and see what happens.

We’re done! Hope you like it. It’s really basic and no need to explain a lot of things. But if you have questions, you may post a comment! Thanks!

Tutor05142008_VB.zip (115.13 kb)

Tutor05142008_CSharp.zip (115.19 kb)

Leave a Reply

Close Menu