A Study of Gage Repeatability and Reproducibility for Automated Measuring Systems using VB.NET

'Author  : Syed Shanu
'Company : Telstar-Hommel
'Date    : 2007-06-22
'Description :This Class is  created for Database Connection Establishment and insert ,update and select from database this class is called as a data Access layer
Public Class SqlFnClass
    Dim GetConnection As New ConnectionClass
    'Class Containing all the Functions Related to Database 
    Function Nextid(ByVal Tablename As String, ByVal Fieldname As String) As Integer
        'Function Genetates the Next value of the Field passing as argument in the Given Table
        Dim Connection As Odbc.OdbcConnection
        Dim Maxdataset, Countdataset As DataSet
        Dim Count As Integer
        Dim RowData As DataRow
        Dim TableData As DataTable
        Try
            Maxdataset = New DataSet
            Countdataset = New DataSet
            Countdataset = ExecuteQuery("select count(*) from " & Tablename)
            'Calculating the Number of Records in the Table
            TableData = Countdataset.Tables(0)
            RowData = TableData.Rows.Item(0)
            If RowData(0) = 0 Then
                ExecuteNonquery("insert into " & Tablename & " values(1)")
                Return (1)
            Else
                Maxdataset = ExecuteQuery("select max(" & Fieldname & ") from " & Tablename)
                'Selecting the Maximum value of Field    
                Count = Maxdataset.Tables(0).Rows.Count
                TableData = Maxdataset.Tables(0)
                RowData = TableData.Rows.Item(0)
                Count = RowData(0) + 1
                ExecuteNonquery("insert into " & Tablename & "(" & Fieldname & ")" & " values(" & Count & ")")
                Return (Count)
            End If
        Catch ex As Exception
            'When Exception arise Thrown to the Called Function
            Throw ex
        End Try
    End Function
    Function GetCount(ByVal TableName As String, ByVal FieldName As String) As DataSet
        'Returns the Number of Fields in the Table
        Dim Query As String
        Try
            Query = "select count(" & FieldName & ") from " & TableName
            Return (ExecuteQuery(Query))
            'Returning Count value of Field
        Catch
            Throw New System.Exception("Error in Counting Fields,Check the Query")
            'Exception thrown 
        End Try
    End Function
    Function ExecuteNonquery(ByVal Query As String) As Integer
        Dim Connection As Odbc.OdbcConnection
        Dim Command As Odbc.OdbcCommand
        Dim chkExecNonQuery As Integer
        Try
            Dim Getconnection As New ConnectionClass
            'Connection = GetConnection.Connectionclass
            Connection = Getconnection.Conobj
            'Getconnection.Conobj
            Command = New Odbc.OdbcCommand(Query, Connection)
            chkExecNonQuery = Command.ExecuteNonQuery()
            Call Getconnection.dispose()
            Return (chkExecNonQuery)
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Function ExecuteQuery(ByVal query As String) As DataSet
        'Executes the Query and Returns the Result in Dataset   
        Dim Connection As Odbc.OdbcConnection
        Dim Data As DataSet
        Dim Adapter As Odbc.OdbcDataAdapter
        Dim Command As Odbc.OdbcCommand
        Dim Cmdbuilder As Odbc.OdbcCommandBuilder
        Data = New DataSet
        Try
            Dim Getconnection As New ConnectionClass
            Connection = Getconnection.Conobj
            Command = New Odbc.OdbcCommand(query, Connection)
            Adapter = New Odbc.OdbcDataAdapter(Command)
            Cmdbuilder = New Odbc.OdbcCommandBuilder(Adapter)
            Adapter.Fill(Data)
            Call Getconnection.dispose()
            Return (Data)
            'Passing Dataset to the Called Function
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function CheckExists(ByVal TableName As String, ByVal Condition As String) As Integer
        'Checks whether the Given Condition is True or Not
        Dim DatasetReturn As DataSet
        Dim Query As String
        Try
            If Condition = "" Then
                Query = "select * " & " from " & TableName
            Else
                Query = "select  * " & " from " & TableName & " where " & Condition
            End If
            DatasetReturn = ExecuteQuery(Query)
            If DatasetReturn.Tables(0).Rows.Count <> 0 Then
                Return (1)
                'If True Return 1
            Else
                Return (0)
                'If False Return 0
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Function DateTime(ByVal GetDate As Date) As String
        Dim Day, Month, Year, FormatedDate As String
        Dim Hour, Minute, Second As String
        Day = GetDate.Day
        Month = GetDate.Month.ToString()
        Year = GetDate.Year.ToString()
        Hour = GetDate.Hour.ToString()
        Minute = GetDate.Minute.ToString()
        Second = GetDate.Second.ToString()
        FormatedDate = Year & "-" & Month & "-" & Day & " " & Hour & ":" & Minute & ":" & Second
        Return (FormatedDate)
    End Function
    Function Revoke(ByVal TableName As String, ByVal StatusColumnName As String, ByVal StatusValue As Integer, ByVal IdCol As String, ByVal IdValue As Integer) As Integer
        Dim Query As String
        Dim RowsAffected As Integer
        Try
            Query = "Update " & TableName & " Set " & StatusColumnName & " = " & StatusValue & " Where " & IdCol & " = " & IdValue
            RowsAffected = ExecuteNonquery(Query)
            Return (RowsAffected)
        Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try
    End Function
#Region "Removed Code"
    'Function GetKotDetails() As DataSet
    '    Dim Query As String
    '    Dim dataset As DataSet

    '    Try
    '        'Query = "Update " & TableName & " Set " & StatusColumnName & " = " & StatusValue & " Where " & IdCol & " = " & IdValue
    '        'If ordid = 0 Then
    '        Query = "select  *  from kottable"
    '        dataset = ExecuteQuery(Query)
    '        'Else
    '        '    Query = "select  *  from kottable  where  orderid = " & ordid
    '        '    dataset = ExecuteQuery(Query)
    '        'End If

    '        Return dataset
    '    Catch Ex As Exception
    '        MsgBox(Ex.Message)
    '    End Try
    'End Function

    'Function fillkotdetails(ByVal ordid As Integer) As DataSet
    '    'Dim query As String
    '    'Dim dataset As DataSet
    '    'Try

    '    '    'dataset = ExecuteQuery("exec getorderdetailsfromkot " & ordid)
    '    '    dataset = ExecuteQuery("Exec getorderdetailsfromkot  " & ordid)
    '    '    Return dataset

    '    'Catch ex As Exception
    '    '    MsgBox(ex.Message)

    '    'End Try
    'End Function

    'Coding by RT to Fill Combobox Values in ESR Search Screen

    'Function Getcbxdetails(ByVal Tablename As String, ByVal Fieldname As String, ByVal x As Integer) As DataSet
    '    'Dim Query As String
    '    'Try
    '    '    If x = 1 Then
    '    '        Query = "select " & Fieldname & " from " & Tablename
    '    '        Return (ExecuteQuery(Query))
    '    '    ElseIf x = 2 Then
    '    '        Query = "select employeeFirstName," & Fieldname & " from " & Tablename & " where employeedesignation = 'waiter' order by employeeFirstName"
    '    '        Return (ExecuteQuery(Query))
    '    '        'Returning Count value of Field
    '    '    End If
    '    'Catch
    '    '    Throw New System.Exception("Error in loading details to Combobox")
    '    '    '       'Exception thrown 
    '    'End Try
    'End Function
    'Function getfilter(ByVal divisionfield As String, ByVal table As String, ByVal waiter As String, ByVal x As Integer) As DataSet

    '    Dim dataset As New DataSet
    '    Dim query As String
    '    Try
    '        If (x = 1) Then
    '            dataset = ExecuteQuery("exec getdivisionfilter '" & divisionfield & "'")
    '            Return dataset
    '        ElseIf x = 2 Then
    '            dataset = ExecuteQuery("exec gettablefilter '" & divisionfield & "'" & "," & "'" & table & "'")
    '            Return dataset
    '        ElseIf x = 3 Then
    '            dataset = ExecuteQuery("exec getwaiterfilter '" & divisionfield & "'" & "," & "'" & table & "'" & "," & "'" & waiter & "'")
    '            Return dataset

    '        End If
    '    Catch ex As Exception
    '        MsgBox(ex.Message)

    '    End Try

    'End Function
    'Function getkitchensearchdetailsfieldfilter(byval fieldname as String,optional byval kitchenid as integer)as dataset
    '    dim 
    '    Try

    '    Catch ex As Exception

    '    End Try

    'End Function

#End Region
End Class