Connect Asp.net with mySQL and Execute Storeprocedure

1. Download MySQL Connector Net 5.1.7 from http://dev.mysql.com/downloads/connector/net/5.1.html
2.Install .
3. Add MySql.Data.dll into bin folder from C:\Program Files\MySQL\MySQL Connector Net 5.1.7\Binaries\.NET 2.0

4.================CLASS FOR EXECUTE STORE PROCEDURE==================
Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports MySql.Data.MySqlClient
Imports MySql.Data
Imports System.Data.Odbc
Imports System.Configuration
Imports System.ComponentModel
Public Class Exec_SP_MSQL
Dim connectionstring As String = ConfigurationManager.ConnectionStrings("demo").ConnectionString.ToString()
Dim con As New MySqlConnection(connectionstring)
Public Sub OpenConnection()
con.Open()
End Sub
Public Sub CloseConnection()
con.Close()
End Sub

Public Function ExecuteSPWithReturnDataTable(ByVal spName As [String], ByVal ParamArray CommandParameter As MySqlClient.MySqlParameter()) As DataTable
Dim tmpDataTable As New DataTable()
'try
'{
Me.OpenConnection()
Dim OdbcCommand As New MySqlCommand()
Me.PrepareCommand(OdbcCommand, Nothing, spName, CommandParameter)
'this.PrepareCommand(OdbcCommand, null, spName, CommandParameter);

Dim OdbcDataAdapter As New MySqlDataAdapter(OdbcCommand)
OdbcDataAdapter.Fill(tmpDataTable).ToString()
Me.CloseConnection()
'}
'catch (Exception exception1)
'{
' //(exception1.Message);
'}

'Catch exception2 As Exception
' Throw New Exception(exception2.Message)
'End Try
Return tmpDataTable
End Function
Private Sub PrepareCommand(ByVal command As MySqlCommand, ByVal transaction As MySqlTransaction, ByVal commandText As String, ByVal commandParameters As MySqlParameter())
'this.PrepareCommand(command1, null, spName, commandParameters);
command.Connection = Me.con
command.CommandText = commandText
If transaction IsNot Nothing Then
command.Transaction = transaction
End If
command.CommandType = CommandType.StoredProcedure
If commandParameters IsNot Nothing Then
Me.AttachParameters(command, commandParameters)
End If
End Sub
Private Sub AttachParameters(ByVal command As MySqlCommand, ByVal commandParameters As MySqlParameter())
For Each parameter1 As MySqlParameter In commandParameters
If (parameter1.Direction = ParameterDirection.InputOutput) AndAlso (parameter1.Value Is Nothing) Then
parameter1.Value = DBNull.Value
End If
command.Parameters.Add(parameter1)
Next
End Sub

End Class
====================================================================

Add this class into App_Code Folder

5.Add class for each table in which you want to rur procedure
====================================================================

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.Odbc
Imports MySql.Data
Imports MySql.Data.MySqlClient


Public Class tblUse

Public Function Dotask(ByVal strFlag As String, ByVal iID As Integer, ByVal strUsername As String, ByVal strPassword As String) As DataTable
Dim _Exec_SP_MSQL As New Exec_SP_MSQL
Dim dt As New DataTable
Dim db As New DBManager.CDataAccess

Dim pFlag As New MySqlParameter("pFlag", MySqlDbType.VarChar)
Dim pID As New MySqlParameter("pID", MySqlDbType.Int32)
Dim pName As New MySqlParameter("pName", MySqlDbType.VarChar)
Dim pPassword As New MySqlParameter("pPassword", MySqlDbType.VarChar)

pFlag.Value = strFlag
pID.Value = iID
pName.Value = strUsername
pPassword.Value = strPassword

dt = _Exec_SP_MSQL.ExecuteSPWithReturnDataTable("spuser", pFlag, pID, pName, pPassword)

Return dt

End Function
End Class
====================================================================

6.How to write Store procedure with Flag in MySQL

Install MySQL 5.6.1
Install Navicat
====================================================================
begin
IF pFlag = "a" THEN

select * from tbluser where Name=pName and Password=pPassword ;


END IF;

IF pFlag = "" THEN

select * from tbluser ;


END IF;
END
===================================================================