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!