Step 1: Download MySQL .NET Connector from this url http://dev.mysql.com/downloads/connector/net/5.2.html and install into your machine. After installation is completed, add this reference to your project.
Step 2: Configure the following connection string in your web.config file under <appSettings>
<add key="ConnectionString" value="Persist Security Info=False;database=yourdbname;server=localhost;user id=root;Password=;"/>
Step 3: You can use the following functions where you want.
Imports MySql.Data
Imports MySql.Data.MySqlClient
Dim str ConnectionString as String 'Global Variable
'On Load Event
ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
Public Function DbGetDataSet(ByVal strSQLStoredProcedure As String, ByVal strSQLParameters As String) As DataSet
'Querying DB and returning a dataset
Dim objSQLConnection As New MySqlConnection(ConnectionString)
Dim objSQLAdapter As New MySqlDataAdapter(strSQLStoredProcedure & " " & strSQLParameters, objSQLConnection)
Dim objDataSet As New DataSet
objSQLAdapter.Fill(objDataSet)
objSQLConnection.Close()
DbGetDataSet = objDataSet
objSQLConnection = Nothing
objSQLAdapter = Nothing
objDataSet = Nothing
End Function
Public Function DbGetDataReader(ByVal strSQLStoredProcedure As String, ByVal strSQLParameters As String) As MySqlDataReader
'Querying DB and returning a datareader
Dim objSQLConnection As New MySqlConnection(ConnectionString)
Dim objSQLCommand As MySqlCommand
Dim objSQLDataReader As MySqlDataReader
objSQLCommand = New MySqlCommand
objSQLConnection.Open()
objSQLCommand.Connection = objSQLConnection
objSQLCommand.CommandText = strSQLStoredProcedure & strSQLParameters
objSQLCommand.CommandType = CommandType.Text
objSQLDataReader = objSQLCommand.ExecuteReader
DbGetDataReader = objSQLDataReader
objSQLDataReader = Nothing
objSQLCommand = Nothing
objSQLConnection = Nothing
End Function
Public Function DbUpdateGetScalar(ByVal strSQLStoredProcedure As String, ByVal strSQLParameters As String) As String
'Querying DB and returning first column of the first row only
Dim objSQLConnection As New MySqlConnection(ConnectionString)
Dim objSQLCommand As MySqlCommand
objSQLCommand = New MySqlCommand
objSQLConnection.Open()
objSQLCommand.Connection = objSQLConnection
objSQLCommand.CommandText = strSQLStoredProcedure & strSQLParameters
objSQLCommand.CommandType = CommandType.Text
DbUpdateGetScalar = CStr(objSQLCommand.ExecuteScalar())
objSQLConnection.Close()
objSQLCommand = Nothing
objSQLConnection = Nothing
End Function
Public Function DbUpdate(ByVal strSQLStoredProcedure As String, ByVal strSQLParameters As String) As String
'Update DB and return nothing
Dim objSQLConnection As New MySqlConnection(ConnectionString)
Dim objSQLCommand As MySqlCommand
objSQLCommand = New MySqlCommand
objSQLConnection.Open()
objSQLCommand.Connection = objSQLConnection
objSQLCommand.CommandText = strSQLStoredProcedure & strSQLParameters
objSQLCommand.CommandType = CommandType.Text
objSQLCommand.ExecuteNonQuery()
objSQLConnection.Close()
objSQLCommand = Nothing
objSQLConnection = Nothing
DbUpdate = ""
End Function
Step 4: You can call a function with your sp name and arguments like below
Example 1:
dim rs as recordset
rs=DbGetDataSet("Select * from tablename", "")
Example 2:
rs= DbGetDataSet("call Sp_jbl_GetEmailID(", "6," & siteCode & ")")