Imports System.Windows.Forms
Imports System.Data.SQLite
Public Class GenericOpt
'DataGridView to show the data
Private dgGeneric As DataGridView
'SqlConnection to connect to the database
Private sqlCnGeneric As SQLiteConnection
'DataAdapter to fetch the data
Private daGeneric As SQLiteDataAdapter
'Dataset to hold the data
Private dsGeneric As DataSet
'Table to perform the operation on
Private strGenericTblNm As String
'Select Query
Private strGenericSelectQuery As String
'Columns to Hide in the datagridview
'Private arrClmnsToHide As DataColumn()
'Columns to Make Read Only
'Private arrClmnsToReadOnly As DataColumn()
'Collection for the Add Data Operation
Private InputData As Dictionary(Of String, String)
'Get the Current Index of Row in the dataset
Private intRowIndex As Integer
'Form Object to which the BindingContext is associated with
Private oForm As Form
Public Shared m_Updtext As String = "Edit"
'GetEdit/Update text
Public Property GetUpdTxt() As String
Get
Return m_Updtext
End Get
Set(ByVal value As String)
m_Updtext = value
End Set
End Property
'Getter and Setter method for InputData
Public Property GetInputData() As Dictionary(Of String, String)
Get
Return InputData
End Get
Set(ByVal value As Dictionary(Of String, String))
InputData = value
End Set
End Property
'Getter and Setter method for Form Object
Public Property GetFormObject() As Form
Get
Return oForm
End Get
Set(ByVal value As Form)
oForm = value
End Set
End Property
Private m_Query As String
Public Property GetQuery() As String
Get
Return m_Query
End Get
Set(ByVal value As String)
m_Query = value
End Set
End Property
Public Sub New()
End Sub
Public Sub New(ByVal strParamTblNm As String, ByVal strParamSelectQuery As String, ByVal dgParamGridView As DataGridView, ByVal oParamForm As Form)
sqlCnGeneric = Utility.RetSqLiteCn()
strGenericTblNm = strParamTblNm
strGenericSelectQuery = strParamSelectQuery
daGeneric = New SQLiteDataAdapter(strGenericSelectQuery, sqlCnGeneric)
Dim cmdB As New SQLiteCommandBuilder(daGeneric)
dsGeneric = New DataSet
dgGeneric = dgParamGridView
oForm = oParamForm
FillDataSet()
End Sub
Public Sub New(ByVal strParamTblNm As String, ByVal strParamSelectQuery As String)
sqlCnGeneric = Utility.RetSqLiteCn()
strGenericTblNm = strParamTblNm
strGenericSelectQuery = strParamSelectQuery
daGeneric = New SQLiteDataAdapter(strGenericSelectQuery, sqlCnGeneric)
Dim cmdB As New SQLiteCommandBuilder(daGeneric)
dsGeneric = New DataSet
FillDataSet()
End Sub
Public Function FillDataSet() As DataSet
Try
sqlCnGeneric.Close()
sqlCnGeneric.Open()
dsGeneric.Clear()
daGeneric.FillSchema(dsGeneric, SchemaType.Source, strGenericTblNm)
daGeneric.Fill(dsGeneric, strGenericTblNm)
Catch ex As Exception
MsgBox(ex.ToString)
Finally
sqlCnGeneric.Close()
End Try
Return dsGeneric
End Function
Public Sub FillDg()
If dgGeneric IsNot Nothing Then
dgGeneric.DataSource = dsGeneric
dgGeneric.DataMember = strGenericTblNm
End If
End Sub
Public Sub AddData()
Try
Dim addRow As DataRow = dsGeneric.Tables(strGenericTblNm).NewRow
If Me.InputData IsNot Nothing Then
For Each tempData As KeyValuePair(Of String, String) In Me.InputData
addRow(tempData.Key) = tempData.Value
Next
dsGeneric.Tables(strGenericTblNm).Rows.Add(addRow)
daGeneric.Update(dsGeneric, strGenericTblNm)
End If
FillDataSet()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Sub AddDataByVerify(ByVal strPrimaryKeyData As String)
Try
Dim addRow As DataRow = dsGeneric.Tables(strGenericTblNm).NewRow
If Me.InputData IsNot Nothing Then
If dsGeneric.Tables(strGenericTblNm).Rows.Find(strPrimaryKeyData) Is Nothing Then
For Each tempData As KeyValuePair(Of String, String) In Me.InputData
addRow(tempData.Key) = tempData.Value
Next
dsGeneric.Tables(0).Rows.Add(addRow)
daGeneric.Update(dsGeneric, strGenericTblNm)
End If
End If
FillDataSet()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Sub DeleteData()
Try
intRowIndex = oForm.BindingContext(dsGeneric, strGenericTblNm).Position
If intRowIndex = -1 Then
MsgBox("Please Select a Valid Record!", MsgBoxStyle.Critical, "Information")
Else
If MsgBox("Are you sure you want to delete this record?", MsgBoxStyle.YesNo, "Confirmation") = MsgBoxResult.Yes Then
oForm.BindingContext(dsGeneric, strGenericTblNm).RemoveAt(intRowIndex)
daGeneric.Update(dsGeneric, strGenericTblNm)
MsgBox("Record Deleted Successfully!")
End If
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Function GetRowIndex() As Integer
Return oForm.BindingContext(dsGeneric, strGenericTblNm).Position
End Function
Public Sub UpdateData()
Try
oForm.BindingContext(dsGeneric, strGenericTblNm).EndCurrentEdit()
daGeneric.Update(dsGeneric, strGenericTblNm)
MsgBox("Record Updated Successfully!")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Sub UpdateData(ByVal objDataToUpdate As Object)
dsGeneric.Tables(0).Rows(0).ItemArray() = objDataToUpdate
daGeneric.Update(dsGeneric, strGenericTblNm)
MsgBox("Record Updated Successfully!")
End Sub
Public Sub SearchData(ByVal strSearchQuery As String, Optional ByVal clmnParamToHide As Int16() = Nothing, Optional ByVal clmnParamToReadOnly As Int16() = Nothing)
Try
sqlCnGeneric.Close()
sqlCnGeneric.Open()
'dgGeneric.DataSource = Nothing
daGeneric.SelectCommand.CommandText = strSearchQuery
dsGeneric.Clear()
Dim cmdB As New SQLiteCommandBuilder(daGeneric)
daGeneric.Fill(dsGeneric, strGenericTblNm)
'dgGeneric.DataSource = Nothing
If dsGeneric IsNot Nothing Then
dgGeneric.DataSource = dsGeneric
dgGeneric.DataMember = strGenericTblNm
If clmnParamToHide IsNot Nothing Then
For Each i As Int16 In clmnParamToHide
dgGeneric.Columns(i).Visible = False
Next
End If
If clmnParamToReadOnly IsNot Nothing Then
For Each i As Int16 In clmnParamToReadOnly
dgGeneric.Columns(i).ReadOnly = True
Next
End If
End If
Catch ex As Exception
MsgBox(ex.ToString)
Finally
sqlCnGeneric.Close()
End Try
End Sub
Public Sub MoveRowUp()
If oForm IsNot Nothing Then
oForm.BindingContext(dsGeneric, strGenericTblNm).Position -= 1
Else
MsgBox("Form object not initialized!")
End If
End Sub
Public Sub MoveRowDown()
If oForm IsNot Nothing Then
oForm.BindingContext(dsGeneric, strGenericTblNm).Position += 1
Else
MsgBox("Form object not initialized!")
End If
End Sub
Public Sub MoveRowFirst()
If oForm IsNot Nothing Then
oForm.BindingContext(dsGeneric, strGenericTblNm).Position = 0
Else
MsgBox("Form object not initialized!")
End If
End Sub
Public Sub MoveRowLast()
If oForm IsNot Nothing Then
oForm.BindingContext(dsGeneric, strGenericTblNm).Position = oForm.BindingContext(dsGeneric, strGenericTblNm).Count - 1
Else
MsgBox("Form object not initialized!")
End If
End Sub
End Class
