By use of these you can do simple operation with mysql and
usin vb.net application.
I will also post my further work in mysql.
Thank you for see it.
And connection string in mysql is following
Dim connStr As String
connStr = String.Format("server={0};user id={1}; password={2}; database={3};
pooling=false", _
TxtServerName.Text, TxtUserName.Text, TxtPassword.Text, TxtDbName.Text)
The all txt____ are textbox from where you take
input for your connection string.
The name is says its meaning. I think you can
easily understand.
first add new class and class name like above which i tell you.
now copy entire below code and enjoy....
Imports MySql.Data.MySqlClient
Imports MySql.Web
Imports System
Imports System.ComponentModel
'developer by nilesh makavana
Public Class MysqlBasicFunction
Private CnObject As MySqlConnection
'Private DTable As DataTable
'Private da As MySqlDataAdapter
'Private cb As MySqlCommandBuilder
Private Cnstring As String
Public Sub New(ByVal
ConnectionString As String)
Cnstring = ConnectionString
End Sub
Public Function ConnectToDatabase()
As Boolean
'If Not conn Is Nothing Then conn.Close()
CnObject = New MySqlConnection(Cnstring)
If CnObject.State = Data.ConnectionState.Open
Then
Try
CnObject.Close()
Return True
Catch exError As MySqlException
MsgBox("An Error Occurred. "
& exError.Number & " – "
& exError.Message)
Return False
Finally
CnObject.Dispose()
End Try
Else
Try
CnObject.Open()
Return True
Catch exError As MySqlException
MsgBox("An Error Occurred. "
& exError.Number & " – "
& exError.Message, "Error In
CssMysqlClass")
Return False
Finally
CnObject.Dispose()
End Try
End If
End Function
Public Function
ConnectToDatabase(ByVal ConnectionState As Boolean) As Boolean
Try
If ConnectionState = True
Then
CnObject = New MySqlConnection(Cnstring)
If CnObject.State = Data.ConnectionState.Closed Then
CnObject.Open()
Return True
End If
Else
CnObject = New
MySqlConnection(Cnstring)
If CnObject.State = Data.ConnectionState.Open Then
CnObject.Close()
CnObject.Dispose()
Return True
End If
End If
Return True
Catch exError As MySqlException
MsgBox("An Error Occurred. "
& exError.Number & " – "
& exError.Message, "Error In
CssMysqlClass")
Return False
Finally
CnObject.Dispose()
End Try
End Function
Public Function
DBFetchValueString(ByVal TableName As String, ByVal ColumnName As String, Optional ByVal condition As String = "NULL")
As String
Dim FetchValue As String = String.Empty
Dim cmd As MySqlCommand
cmd =
Nothing
Try
Using MySqlConnectionObject As
New MySqlConnection(Cnstring)
MySqlConnectionObject.Open()
Dim query As
String = String.Empty
If condition = "NULL"
Then
query = String.Format("select {0} FROM {1}", ColumnName,
TableName)
Else
query = String.Format("select {0} FROM {1} WHERE {2}",
ColumnName, TableName, condition)
End If
cmd = New
MySqlCommand(query, MySqlConnectionObject)
Using Dtreader As
MySqlDataReader = cmd.ExecuteReader
While Dtreader.Read
FetchValue =
Dtreader.Item(0).ToString
End While
End Using
End Using
Catch exError As MySqlException
MsgBox("An Error Occurred. "
& exError.Number & " – "
& exError.Message, "Error In
CssMysqlClass")
Finally
cmd.Dispose()
End Try
Return FetchValue
End Function
Public Function
TableCount(ByVal TableName As String, Optional ByVal
condition As String
= "NULL") As
Integer
Dim FetchValue As String = "NULL"
Try
Using MySqlConnectionObject As
New MySqlConnection(Cnstring)
MySqlConnectionObject.Open()
Dim MySqlQuery As
String = String.Empty
If condition = "NULL"
Then
MySqlQuery = "SELECT COUNT(*) FROM
" & TableName & ""
Else
MySqlQuery = "SELECT COUNT(*) FROM
" & TableName & " WHERE
" & condition & ""
End If
Using mMySqlCommand As New MySqlCommand
With mMySqlCommand
.Connection =
MySqlConnectionObject
.CommandType = CommandType.Text
.CommandText =
MySqlQuery
End With
Using Dtreader As
MySqlDataReader = mMySqlCommand.ExecuteReader
While Dtreader.Read
FetchValue =
Dtreader.Item(0).ToString
End While
End Using
End Using
End Using
Catch exError As MySqlException
MsgBox("An Error Occurred. "
& exError.Number & " – "
& exError.Message, "Error In
CssMysqlClass")
Finally
End Try
If FetchValue = "NULL"
Then
Return 0
Else
Return Integer.Parse(FetchValue)
End If
End Function
Public Function
TableMax(ByVal TableName As String, ByVal ColunmName As String, Optional ByVal condition As String = "NULL")
As Integer
Dim FetchValue As String = "NULL"
Try
Using MySqlConnectionObject As
New MySqlConnection(Cnstring)
MySqlConnectionObject.Open()
Dim MySqlQuery As
String = String.Empty
If condition = "NULL"
Then
MySqlQuery = "SELECT MAX("
& ColunmName & ") FROM "
& TableName & ""
Else
MySqlQuery = "SELECT MAX("
& ColunmName & ") FROM "
& TableName & " WHERE "
& condition & ""
End If
Using mMySqlCommand As New MySqlCommand
With mMySqlCommand
.Connection =
MySqlConnectionObject
.CommandType = CommandType.Text
.CommandText =
MySqlQuery
End With
Using Dtreader As
MySqlDataReader =
mMySqlCommand.ExecuteReader
While Dtreader.Read
FetchValue =
Dtreader.Item(0).ToString
End While
End Using
End Using
End Using
Catch exError As MySqlException
MsgBox("An Error Occurred. "
& exError.Number & " – "
& exError.Message, "Error In
CssMysqlClass")
Finally
End Try
If FetchValue = "NULL"
Then
Return 0
Else
Return Integer.Parse(FetchValue)
End If
End Function
Public Function
TableMin(ByVal TableName As String, ByVal ColunmName As String, Optional ByVal condition As String = "NULL")
As Integer
Dim FetchValue As String = "NULL"
Try
Using MySqlConnectionObject As
New MySqlConnection(Cnstring)
MySqlConnectionObject.Open()
Dim MySqlQuery As
String = String.Empty
If condition = "NULL"
Then
MySqlQuery = "SELECT MIN("
& ColunmName & ") FROM "
& TableName & ""
Else
MySqlQuery = "SELECT MIN("
& ColunmName & ") FROM "
& TableName & " WHERE "
& condition & ""
End If
Using mMySqlCommand As New MySqlCommand
With mMySqlCommand
.Connection =
MySqlConnectionObject
.CommandType = CommandType.Text
.CommandText = MySqlQuery
End With
Using Dtreader As
MySqlDataReader =
mMySqlCommand.ExecuteReader
While Dtreader.Read
FetchValue =
Dtreader.Item(0).ToString
End
While
End Using
End Using
End Using
Catch exError As MySqlException
MsgBox("An Error Occurred. "
& exError.Number & " – "
& exError.Message, "Error In CssMysqlClass")
Finally
End Try
If FetchValue = "NULL"
Then
Return 0
Else
Return Integer.Parse(FetchValue)
End If
End Function
Public Function
TableSum(ByVal TableName As String, ByVal ColunmName As String, Optional ByVal condition As String = "NULL")
As Integer
Dim FetchValue As String = "NULL"
Try
Using MySqlConnectionObject As
New MySqlConnection(Cnstring)
MySqlConnectionObject.Open()
Dim MySqlQuery As
String = String.Empty
If condition = "NULL"
Then
MySqlQuery = "SELECT SUM("
& ColunmName & ") FROM "
& TableName & ""
Else
MySqlQuery = "SELECT SUM("
& ColunmName & ") FROM "
& TableName & " WHERE "
& condition & ""
End If
Using mMySqlCommand As New MySqlCommand
Dim TempDS As
New DataSet
With mMySqlCommand
.Connection = MySqlConnectionObject
.CommandType = CommandType.Text
.CommandText =
MySqlQuery
End With
Using Dtreader As
MySqlDataReader =
mMySqlCommand.ExecuteReader
While
Dtreader.Read
FetchValue =
Dtreader.Item(0).ToString
End While
End Using
End Using
End Using
Catch exError As MySqlException
MsgBox("An Error Occurred. "
& exError.Number & " – "
& exError.Message, "Error In
CssMysqlClass")
Finally
End Try
If FetchValue = "NULL"
Then
Return 0
Else
Return Integer.Parse(FetchValue)
End If
End Function
Public Function
SetDataTable(ByVal _Datatable As DataTable, ByVal MySqlQuery As String) As Boolean
Dim DataReader As IDataReader
Dim cmd As MySqlCommand
cmd =
Nothing
Try
Using MySqlConnectionObject As
New MySqlConnection(Cnstring)
MySqlConnectionObject.Open()
cmd = New MySqlCommand(MySqlQuery,
MySqlConnectionObject)
DataReader = cmd.ExecuteReader()
_Datatable.Load(DataReader)
DataReader.Close()
End Using
Catch exError As MySqlException
MsgBox("An Error Occurred. "
& exError.Number & " – "
& exError.Message, "Error In
CssMysqlClass")
Return False
Finally
cmd.Dispose()
End Try
Return True
End Function
Public Function
ExecuteNonQuery(ByVal MySqlQuery As String) As Boolean
Try
Using MySqlConnectionObject As
New MySqlConnection(Cnstring)
MySqlConnectionObject.Open()
Using mMySqlCommand As New MySqlCommand
With mMySqlCommand
.Connection =
MySqlConnectionObject
.CommandType = CommandType.Text
.CommandText =
MySqlQuery
.ExecuteNonQuery()
End With
End Using
End Using
Catch exError As MySqlException
MsgBox("An Error Occurred. "
& exError.Number & " – "
& exError.Message, "Error In
CssMysqlClass")
Return False
Finally
End Try
Return True
End Function
End Class
if you like my post then please share with friend so they also take benefit of it.
How do I update data and insert data with this class
ReplyDeleteFirst you create connection string for it.
ReplyDeleteex.
Dim connStr As String
connStr = String.Format("server={0};user id={1}; password={2}; database={3}; pooling=false",TxtServerName.Text, TxtUserName.Text, TxtPassword.Text, TxtDbName.Text)
and
for use it class create a object of it.
Dim cn As New MysqlBasicFunction(connStr)
If cn.ConnectToDatabase(True) = True Then
cn.ExecuteNonQuery("YOUR INSERT AND UPDATE QUERY COMES HERE.")
end if
you also use call store procedure from mysql without using it class
Using mMySqlConnection As New MySqlConnection("Connection string")
mMySqlConnection.Open()
Using mMySqlCommand As New MySqlCommand
With mMySqlCommand
.Connection = mMySqlConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "`databasename`.`store_procedure_Name`"
End With
mMySqlCommand.Parameters.AddWithValue("_ItemCatId", _ItemCatId)
mMySqlCommand.Parameters.AddWithValue("_ItemCatName", _ItemCatName)
mMySqlCommand.Parameters.AddWithValue("_Description", _Description)
mMySqlCommand.ExecuteNonQuery()
End Using
End Using