Class for mainage mssql database in vb.net

Imports System.Data.SqlClient

Public Class _MsSqlLib

'Developed By Nilesh Makavana.

Sub New()

_ConectionString = ""

End Sub

Private _ConectionString As String

#Region "Connection Function"

Public Function TestConnection() As Boolean

Try

Using Cn As New SqlConnection(_ConectionString)

Cn.Open()

Cn.Close()

End Using

Return True

Catch ex As Exception

Return False

End Try

End Function

Public Function ManageConnection() As Boolean

Try

Dim Cn As New SqlConnection(_ConectionString)

If Cn.State = ConnectionState.Open Then

Cn.Close()

ElseIf Cn.State = ConnectionState.Closed Then

Cn.Open()

End If

Return True

Catch ex As Exception

Return False

End Try

End Function

#End Region

#Region "Execute Non Query"

Public Function ExecuteNonQuery(ByVal SqlQuery As String) As Boolean

Try

Using Cn As New SqlConnection(_ConectionString)

Cn.Open()

Using cmd As New SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 30

.CommandText = SqlQuery

.Connection = Cn

End With

cmd.ExecuteNonQuery()

End Using

End Using

Return True

Catch ex As Exception

Return False

End Try

End Function

#End Region

#Region "Set Data Table"

Public Function SetDataTable(ByVal _Datatable As DataTable, ByVal SqlQuery As String) As Boolean

Dim DataReader As IDataReader

Try

Using Cn As New SqlConnection(_ConectionString)

Cn.Open()

Using cmd As New SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 30

.CommandText = SqlQuery

.Connection = Cn

End With

DataReader = cmd.ExecuteReader()

_Datatable.Load(DataReader)

DataReader.Close()

End Using

End Using

Return True

Catch ex As Exception

MsgBox(ex.ToString)

Return False

End Try

End Function

#End Region

#Region "Fetch One Field In array of list"

Public Function FetchSingleColunm(ByVal TableName As String, ByVal ColumnName As String, Optional ByVal Conditon As String = "") As List(Of String)

Dim ArrayOfFiled As New List(Of String)

Try

Using Cn As New SqlConnection(_ConectionString)

Cn.Open()

Dim DataReader As IDataReader

Using cmd As New SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 30

If Conditon = String.Empty Then

.CommandText = "select " & ColumnName & " FROM [" & TableName & "]"

Else

.CommandText = "select " & ColumnName & " FROM [" & TableName & "] where " & Conditon & ""

End If

.Connection = Cn

End With

DataReader = cmd.ExecuteReader()

While DataReader.Read

ArrayOfFiled.Add(DataReader.Item(0).ToString)

End While

DataReader.Close()

DataReader.Dispose()

End Using

End Using

Catch ex As Exception

MsgBox(ex.ToString)

'MsgBox("Error in table name or colunma name or condition or arraylist")

End Try

Return ArrayOfFiled

End Function

Public Function FetchSingleColunmStr(ByVal TableName As String, ByVal ColumnName As String, Optional ByVal Conditon As String = "") As String

Dim ArrayOfFiled As String = String.Empty

Try

Using Cn As New SqlConnection(_ConectionString)

Cn.Open()

Dim DataReader As IDataReader

Using cmd As New SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 30

If Conditon = String.Empty Then

.CommandText = "select " & ColumnName & " FROM [" & TableName & "]"

Else

.CommandText = "select " & ColumnName & " FROM [" & TableName & "] where " & Conditon & ""

End If

.Connection = Cn

End With

DataReader = cmd.ExecuteReader()

While DataReader.Read

ArrayOfFiled = (DataReader.Item(0).ToString)

End While

DataReader.Close()

DataReader.Dispose()

End Using

End Using

Catch ex As Exception

MsgBox(ex.ToString)

'MsgBox("Error in table name or colunma name or condition or arraylist")

End Try

Return ArrayOfFiled

End Function

Public Function FetchSingleColunmStr(ByVal Query As String) As String

Dim ArrayOfFiled As String = String.Empty

Try

Using Cn As New SqlConnection(_ConectionString)

Cn.Open()

Dim DataReader As IDataReader

Using cmd As New SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 30

.CommandText = Query

.Connection = Cn

End With

DataReader = cmd.ExecuteReader()

While DataReader.Read

ArrayOfFiled = (DataReader.Item(0).ToString)

End While

DataReader.Close()

DataReader.Dispose()

End Using

End Using

Catch ex As Exception

MsgBox(ex.ToString)

'MsgBox("Error in table name or colunma name or condition or arraylist")

End Try

Return ArrayOfFiled

End Function

Public Function FetchSingleColunm(ByVal Query As String) As List(Of String)

Dim ArrayOfFiled As New List(Of String)

Try

Using Cn As New SqlConnection(_ConectionString)

Cn.Open()

Dim DataReader As IDataReader

Using cmd As New SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 30

.CommandText = Query

.Connection = Cn

End With

DataReader = cmd.ExecuteReader()

Dim RowId As Integer = 0

While DataReader.Read

ArrayOfFiled.Add(DataReader.Item(0).ToString)

End While

DataReader.Close()

DataReader.Dispose()

End Using

Cn.Close()

End Using

Catch ex As Exception

MsgBox(ex.ToString)

'MsgBox("Error in table name or colunma name or condition or arraylist")

End Try

Return ArrayOfFiled

End Function

#End Region

#Region "Basic Tabal Function, MAX MIN Totoal Record"

Public Function TableCount(ByVal TableName As String, Optional ByVal condition As String = "NULL") As Integer

Dim FetchValue As String = "NULL"

Try

Using SqlConnectionObject As New SqlConnection(_ConectionString)

SqlConnectionObject.Open()

Dim SqlQuery As String = String.Empty

If condition = "NULL" Then

SqlQuery = "SELECT COUNT(*) FROM " & TableName & ""

Else

SqlQuery = "SELECT COUNT(*) FROM " & TableName & " WHERE " & condition & ""

End If

Using SqlCommand As New SqlCommand

With SqlCommand

.Connection = SqlConnectionObject

.CommandType = CommandType.Text

.CommandText = SqlQuery

End With

Using Dtreader As SqlDataReader = SqlCommand.ExecuteReader

While Dtreader.Read

FetchValue = Dtreader.Item(0).ToString

End While

End Using

End Using

End Using

Catch exError As Exception

MsgBox(exError.ToString)

End Try

Return Integer.Parse(FetchValue)

End Function

Public Function TableMax(ByVal TableName As String, ByVal fieldname As String, Optional ByVal condition As String = "NULL") As Integer

If TableCount(TableName) = 0 Then

Return 0

Else

Dim FetchValue As String = "0"

Try

Using SqlConnectionObject As New SqlConnection(_ConectionString)

SqlConnectionObject.Open()

Dim SqlQuery As String = String.Empty

If condition = "NULL" Then

SqlQuery = "SELECT MAX(" & fieldname & " ) FROM " & TableName & ""

Else

SqlQuery = "SELECT MAX(" & fieldname & " ) FROM " & TableName & " WHERE " & condition & ""

End If

Using SqlCommand As New SqlCommand

With SqlCommand

.Connection = SqlConnectionObject

.CommandType = CommandType.Text

.CommandText = SqlQuery

End With

Using Dtreader As SqlDataReader = SqlCommand.ExecuteReader

While Dtreader.Read

FetchValue = Dtreader.Item(0).ToString

End While

End Using

End Using

End Using

Catch exError As Exception

MsgBox(exError.ToString)

End Try

If FetchValue = "NULL" Or FetchValue = String.Empty Then

Return 0

Else

Return Integer.Parse(FetchValue)

End If

End If

End Function

Public Function TableMin(ByVal TableName As String, ByVal fieldname As String, Optional ByVal condition As String = "NULL") As Integer

Dim FetchValue As String = "NULL"

Try

Using SqlConnectionObject As New SqlConnection(_ConectionString)

SqlConnectionObject.Open()

Dim SqlQuery As String = String.Empty

If condition = "NULL" Then

SqlQuery = "SELECT MIN(" & fieldname & " ) FROM " & TableName & ""

Else

SqlQuery = "SELECT MIN(" & fieldname & " ) FROM " & TableName & " WHERE " & condition & ""

End If

Using SqlCommand As New SqlCommand

With SqlCommand

.Connection = SqlConnectionObject

.CommandType = CommandType.Text

.CommandText = SqlQuery

End With

Using Dtreader As SqlDataReader = SqlCommand.ExecuteReader

While Dtreader.Read

FetchValue = Dtreader.Item(0).ToString

End While

End Using

End Using

End Using

Catch exError As Exception

MsgBox(exError.ToString)

Finally

End Try

If FetchValue = "NULL" Or FetchValue = String.Empty Then

Return 0

Else

Return Integer.Parse(FetchValue)

End If

End Function

#End Region

End Class