Public Function TaxSaleAdd100(ByRef rows As Integer) As Boolean

        Dim bSuccess As Boolean
        bSuccess = False

        dbconn = New DatabaseConn
        Dim sqlConn As SqlConnection = dbconn.GetConnectionStarSource

        Dim spProcName As String = "TaxSale31015"
        Dim sqlCmd As SqlCommand = New SqlCommand()

        SetCommandType(sqlCmd, CommandType.StoredProcedure, spProcName, sqlConn)
        Try
            sqlConn.Open()
            rows = sqlCmd.ExecuteNonQuery()
            bSuccess = True
            Return bSuccess

        Catch ex As Exception
            Throw (ex)
        End Try
    End Function

    Public Function ExecuteDataConversion(ByVal Jobname As String, ByVal dbServername As String) As Integer
        Dim flag As Integer
        dbconn = New DatabaseConn
        Dim sqlConnMigrate As SqlConnection = dbconn.GetConnection()
        Dim spProcName As String = "sp_start_job"
        Dim sqlCmd As SqlCommand = New SqlCommand()

        SetCommandType(sqlCmd, CommandType.StoredProcedure, spProcName, sqlConnMigrate)
        Try
            AddParamToSQLCmd(sqlCmd, "job_name", SqlDbType.VarChar, 50, ParameterDirection.Input, Jobname)
            AddParamToSQLCmd(sqlCmd, "server_name", SqlDbType.VarChar, 50, ParameterDirection.Input, dbServername)

            sqlConnMigrate.Open()
            flag = ExecuteScalar(sqlCmd, sqlConnMigrate)
            Return flag
        Catch ex As Exception
            Throw (ex)
        Finally
            sqlConnMigrate.Close()
        End Try

    End Function


    Public Sub LoadCtrnlBill(ByRef counttraccno As Object, ByRef paidamt As Object)
        dbconn = New DatabaseConn
        Dim result As Object = 0.0
        Dim sqlConn As SqlConnection = dbconn.GetConnectionStarSource

        Try

            Dim query1 As String = "select count(TR_ACCT_NO) from cntrbill c join PayRegister p on c.TR_ACCT_NO=p.TR_ACCOUNT_NO and c.tr_year=p.tr_year and c.tr_town=p.tr_town where(c.TR_ACCT_NO = p.TR_ACCOUNT_NO And c.tr_year = p.tr_year And c.tr_town = p.tr_town) and c.tr_dvpaidamt<>p.tr_dv_paidamt or c.tr_acvpaidamt<>p.tr_acv_paidamt"
            Dim query2 As String = "select sum(tr_dv_paidamt)+sum(tr_acv_paidamt) from cntrbill c join PayRegister p on c.TR_ACCT_NO=p.TR_ACCOUNT_NO and c.tr_year=p.tr_year and c.tr_town=p.tr_town where(c.TR_ACCT_NO = p.TR_ACCOUNT_NO And c.tr_year = p.tr_year And c.tr_town = p.tr_town) and c.tr_dvpaidamt<>p.tr_dv_paidamt or c.tr_acvpaidamt<>p.tr_acv_paidamt"

            Dim sqlCmd1 As New SqlCommand(query1, sqlConn)
            Dim sqlCmd2 As New SqlCommand(query2, sqlConn)

            sqlConn.Open()
            paidamt = sqlCmd2.ExecuteScalar

            If Convert.IsDBNull(paidamt) Then
                paidamt = 100
            End If
            counttraccno = CType(sqlCmd1.ExecuteScalar, Object)
        Catch ex As Exception
            Throw (ex)
        Finally
            sqlConn.Close()


        End Try
    End Sub

    Public Function LoadCtrnlBillSelect() As DataSet

        dbconn = New DatabaseConn
        Dim sqlConn As SqlConnection = dbconn.GetConnectionStarSource

        Dim query1 As String = "select TR_ACCOUNT_NO from cntrbill c right join PayRegister p on c.TR_ACCT_NO=p.TR_ACCOUNT_NO and c.tr_year=p.tr_year and c.tr_town=p.tr_town where tr_acct_no is null"
        Try
            Dim sqlCmd1 As New SqlCommand(query1, sqlConn)
            Dim ds As New DataSet
            sqlConn.Open()
            sqlAdapter.SelectCommand = sqlCmd1

            sqlAdapter.Fill(ds, "AccountList")
            Return ds
        Catch ex As Exception
            Throw (ex)
        End Try

    End Function

    Public Function LoadCtrnlBillUpdate() As Boolean
        Dim bSuccess As Boolean
        bSuccess = False

        dbconn = New DatabaseConn
        Dim sqlConn As SqlConnection = dbconn.GetConnectionStarSource

        Dim query1 As String = "update cntrbill set	TR_ACV_STATEPAID=p.TR_ACV_STATEPAID,TR_DV_STATEPAID=p.TR_DV_STATEPAID,  TR_ACV_PAIDTOAPPL=p.TR_ACV_PAIDTOAPPL,TR_DV_PAIDTOAPPL=p.TR_DV_PAIDTOAPPL,	TR_DVPaidAmt=p.TR_DV_PaidAmt, TR_ACVPaidAmt = p.TR_ACV_PaidAmt from cntrbill c join PayRegister p on c.TR_ACCT_NO=p.TR_ACCOUNT_NO and c.tr_year=p.tr_year and c.tr_town=p.tr_town where(c.TR_ACCT_NO = p.TR_ACCOUNT_NO And c.tr_year = p.tr_year And c.tr_town = p.tr_town) and c.tr_dvpaidamt<>p.tr_dv_paidamt or c.tr_acvpaidamt<>p.tr_acv_paidamt"
        Dim sqlCmd As SqlCommand = New SqlCommand(query1, sqlConn)

        Try
            sqlCmd.CommandType = CommandType.Text
            sqlConn.Open()
            sqlCmd.ExecuteNonQuery()
            bSuccess = True
            Return bSuccess

        Catch ex As Exception

        End Try

    End Function

    Private Sub AddOutputParamToSQLCmd(ByRef sqlCmd As SqlCommand, ByVal paramId As String, ByVal sqlType As SqlDbType, _
                                           ByVal paramSize As Integer, ByVal paramDirection As ParameterDirection, ByVal sqlParam As SqlParameter)

        If sqlCmd Is Nothing Then
            Throw New ArgumentNullException("sqlCmd")
        End If

        If paramId = String.Empty Then
            Throw New ArgumentOutOfRangeException("paramId")
        End If

        sqlParam.ParameterName = paramId    ' parameter name in Stored Procedure
        sqlParam.SqlDbType = sqlType
        sqlParam.Direction = paramDirection

        If paramSize > 0 Then
            sqlParam.Size = paramSize
        End If

        sqlCmd.Parameters.Add(sqlParam)

    End Sub


    Private Sub AddParamToSQLCmd(ByRef sqlCmd As SqlCommand, ByVal paramId As String, ByVal sqlType As SqlDbType, _
                            ByVal paramSize As Integer, ByVal paramDirection As ParameterDirection, _
                            ByVal paramvalue As Object)

        If sqlCmd Is Nothing Then
            Throw New ArgumentNullException("sqlCmd")
        End If

        If paramId = String.Empty Then
            Throw New ArgumentOutOfRangeException("paramId")
        End If

        Dim newSqlParam As SqlParameter = New SqlParameter()
        newSqlParam.ParameterName = paramId    ' parameter name in Stored Procedure
        newSqlParam.SqlDbType = sqlType
        newSqlParam.Direction = paramDirection

        If paramSize > 0 Then
            newSqlParam.Size = paramSize
        End If

        If Not paramvalue Is Nothing Then
            newSqlParam.Value = paramvalue
        Else
            newSqlParam.Value = 0
        End If
        sqlCmd.Parameters.Add(newSqlParam)

    End Sub


    Private Sub SetDataAdapter(ByVal sqlCmd As SqlCommand, ByVal sqlConn As SqlConnection)
        sqlAdapter = New SqlDataAdapter
        sqlAdapter.SelectCommand = sqlCmd
    End Sub

    Private Sub SetCommandType(ByRef sqlCmd As SqlCommand, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal Conn As SqlConnection)
        sqlCmd.CommandType = cmdType
        sqlCmd.CommandText = cmdText
        sqlCmd.Connection = Conn
    End Sub


    Public Sub ExecuteNonQuery(ByVal sqlCmd As SqlCommand, ByVal SqlConn As SqlConnection)
        If sqlCmd Is Nothing Then
            Throw New ArgumentNullException("sqlCmd")
        End If
        sqlCmd.Connection = SqlConn
        sqlCmd.ExecuteNonQuery()
    End Sub

    Public Function ExecuteScalar(ByVal sqlCmd As SqlCommand, ByVal sqlConn As SqlConnection) As Object
        Dim retval As Object
        If (sqlConn Is Nothing) Then Throw New ArgumentNullException("sqlConn")
        retval = sqlCmd.ExecuteScalar()
        Return retval

    End Function