I have inherited this code from my company. It is acting as a helper class for a database and written in VB.NET until date. It is working properly and almost 5 to 10 commercial packages I am maintaining is written based on this work. I just want to know if it can be improved. It invariably uses Async, and I am not sure about it.
Public Overloads Function bindData(ByVal qry As String, ByVal param() As MySqlParameter, Optional ByVal retRows As CommandBehavior = CommandBehavior.Default) As DataTable
Try
Using d = New MySqlConnection(connectionString)
Using cmd = d.CreateCommand
cmd.CommandText = qry
cmd.CommandType = CommandType.Text
cmd.Parameters.AddRange(param)
If d.State = ConnectionState.Closed Then d.Open()
Dim iResult As IAsyncResult = cmd.BeginExecuteReader(retRows)
While Not iResult.IsCompleted
Threading.Thread.Sleep(100)
End While
If iResult.IsCompleted Then
mydr = cmd.EndExecuteReader(iResult)
End If
Dim dt As New DataTable
dt.Load(mydr)
Return dt
End Using
End Using
Catch ex As Exception
Throw ex
End Try
End Function
Public Overloads Function bindData(ByVal qry As String, Optional ByVal retRows As CommandBehavior = CommandBehavior.Default) As DataTable
Try
Using d = New MySqlConnection(connectionString)
Using cmd As New MySqlCommand(qry, d)
If d.State = ConnectionState.Closed Then d.Open()
cmd.CommandType = CommandType.Text
Dim iResult As IAsyncResult = cmd.BeginExecuteReader(retRows)
While Not iResult.IsCompleted
Threading.Thread.Sleep(100)
End While
If iResult.IsCompleted Then
mydr = cmd.EndExecuteReader(iResult)
End If
Dim dt As New DataTable
dt.Load(mydr)
Return dt
End Using
End Using
Catch ex As Exception
Throw ex
End Try
End Function
Public Overloads Function bindProcedure(ByVal spname As String, Optional ByVal retRows As CommandBehavior = CommandBehavior.Default) As DataTable
Try
Using d = New MySqlConnection(connectionString)
Using cmd = d.CreateCommand
cmd.CommandText = spname
cmd.CommandType = CommandType.StoredProcedure
If d.State = ConnectionState.Closed Then d.Open()
Dim iResult As IAsyncResult = cmd.BeginExecuteReader(retRows)
While Not iResult.IsCompleted
Threading.Thread.Sleep(100)
End While
If iResult.IsCompleted Then
mydr = cmd.EndExecuteReader(iResult)
End If
Dim dt As New DataTable
dt.Load(mydr)
Return dt
End Using
End Using
Catch ex As Exception
Throw ex
End Try
End Function
Public Overloads Function bindProcedure(ByVal spname As String, ByVal inparam() As MySqlParameter, Optional ByVal retRows As CommandBehavior = CommandBehavior.Default) As DataTable
Try
Using d = New MySqlConnection(connectionString)
Using cmd = d.CreateCommand
cmd.CommandText = spname
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddRange(inparam)
If d.State = ConnectionState.Closed Then d.Open()
Dim iResult As IAsyncResult = cmd.BeginExecuteReader(retRows)
While Not iResult.IsCompleted
Threading.Thread.Sleep(100)
End While
If iResult.IsCompleted Then
mydr = cmd.EndExecuteReader(iResult)
End If
Dim dt As New DataTable
dt.Load(mydr)
Return dt
End Using
End Using
Catch ex As Exception
Throw ex
End Try
End Function
Public Overloads Function ExecuteQuery(ByVal qry As String, ByVal param() As MySqlParameter) As Boolean
Dim retval As Boolean = False
Try
Using d = New MySqlConnection(connectionString)
Using cmd = d.CreateCommand
cmd.CommandText = qry
cmd.CommandType = CommandType.Text
cmd.Parameters.AddRange(param)
If d.State = ConnectionState.Closed Then d.Open()
Dim iResult As IAsyncResult = cmd.BeginExecuteNonQuery()
While Not iResult.IsCompleted
Threading.Thread.Sleep(100)
End While
If iResult.IsCompleted Then
If cmd.EndExecuteNonQuery(iResult) > 0 Then
retval = True
Else
retval = False
End If
End If
End Using
End Using
Catch ex As Exception
Throw ex
retval = False
End Try
Return retval
End Function
Public Overloads Function ExecuteQuery(ByVal qry As String) As Boolean
Dim retval As Boolean = False
Try
Using d = New MySqlConnection(connectionString)
Using cmd = d.CreateCommand
cmd.CommandText = qry
cmd.CommandType = CommandType.Text
If d.State = ConnectionState.Closed Then d.Open()
Dim iResult As IAsyncResult = cmd.BeginExecuteNonQuery()
While Not iResult.IsCompleted
Threading.Thread.Sleep(100)
End While
If iResult.IsCompleted Then
If cmd.EndExecuteNonQuery(iResult) > 0 Then
retval = True
Else
retval = False
End If
End If
End Using
End Using
Catch ex As Exception
Throw ex
retval = False
End Try
Return retval
End Function