mports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Text

Public Class DBoxSqlConnection
    Implements IDisposable

    Private conn As SqlConnection
    Private trans As SqlTransaction
    Private cmdTimeOut As Int32

    'Ignore the first '@RETURN_VALUE" parameter, so start from 1
    Private parameterShiftIndex As Int32 = 1
    Private ReadOnly returnValue As String = "@RETURN_VALUE"

    Private Shared parameterCache As Hashtable = Hashtable.Synchronized(New Hashtable)

    Public Shared SyncLocker As New Object()

    Public Enum ExecuteType
        ExecuteScalar
        ExecuteNonQuery
        ExecuteReader
        ExecuteDataTable
    End Enum

    Public Sub New()
        conn = New SqlConnection()
    End Sub

    Public Sub New(ByVal connectionString As String)
        conn = New SqlConnection(connectionString)
    End Sub

    Public ReadOnly Property State() As ConnectionState
        Get
            Return conn.State
        End Get
    End Property

    Public Property CommandTimeout() As Int32
        Get
            Return cmdTimeOut
        End Get
        Set(ByVal value As Int32)
            cmdTimeOut = value
        End Set
    End Property

    Public ReadOnly Property InnerSQLConnection() As SqlConnection
        Get
            Return conn
        End Get
    End Property

    Public ReadOnly Property InnerTrans() As SqlTransaction
        Get
            Return trans
        End Get
    End Property

    Public Sub Open()
        If conn.State = ConnectionState.Closed Then
            conn.Open()
        End If
    End Sub

    Public Sub Open(ByVal connectionString As String)
        conn.ConnectionString = connectionString
        conn.Open()
    End Sub

    Public Sub Close()
        If Not conn Is Nothing AndAlso conn.State = ConnectionState.Open Then
            conn.Close()
        End If
    End Sub

#Region "execute"

    Public Function Execute(ByVal sqlText As String) As Int32
        Return Execute(sqlText, CommandType.Text)
    End Function

    Public Function Execute(ByVal sqlText As String, ByVal cmdType As CommandType) As Int32
        Return CType(Execute(sqlText, cmdType, ExecuteType.ExecuteNonQuery, Nothing), Int32)
    End Function

    Public Function Execute(ByVal cmd As SqlCommand) As DataTable
        Dim closeConnAfterUse As Boolean = False
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                closeConnAfterUse = True
            End If
            Return GetDataTable(cmd)
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Connection string:" + conn.ConnectionString)
            Log.Instance.Error(ex.StackTrace)
            Throw New CustomException("Cannot connect to Dropbox database server.", ex)
        Finally
            If closeConnAfterUse AndAlso conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
        End Try

    End Function

    Public Function Execute(ByVal cmd As SqlCommand, ByVal dataSetName As String) As DataSet
        Dim closeConnAfterUse As Boolean = False
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                closeConnAfterUse = True
            End If
            Return GetDataSet(cmd, dataSetName)
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Connection string:" + conn.ConnectionString)
            Log.Instance.Error(ex.StackTrace)
            Throw New CustomException("Cannot connect to Dropbox database server.", ex)
        Finally
            If closeConnAfterUse AndAlso conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
        End Try

    End Function

    Public Function Execute(ByVal sqlText As String, ByVal cmdType As CommandType, ByVal executeType As ExecuteType, ByVal ParamArray parameterValues() As Object) As Object
        Dim cmd As SqlCommand = Nothing
        Dim closeConnAfterUse As Boolean = False
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                closeConnAfterUse = True
            End If

            cmd = GetCommand(sqlText, cmdType, parameterValues)

            Select Case executeType
                Case executeType.ExecuteNonQuery
                    Return cmd.ExecuteNonQuery()
                Case executeType.ExecuteScalar
                    Return cmd.ExecuteScalar()
                Case executeType.ExecuteReader
                    Return cmd.ExecuteReader()
                Case executeType.ExecuteDataTable
                    Return GetDataTable(cmd)
                Case Else
                    Return Nothing
            End Select
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Connection string:" + conn.ConnectionString)
            Log.Instance.Error(ex.StackTrace)
            Throw New CustomException("Cannot connect to Dropbox database server.", ex)
        Finally
            If Not cmd Is Nothing Then
                cmd.Dispose()
            End If

            If closeConnAfterUse AndAlso conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
    End Function

#End Region

#Region "transaction"

    Public Sub BeginTrans()
        trans = conn.BeginTransaction()
    End Sub

    ''Boris_20090916_ESS Spec Registration module
    Public Sub BeginTrans(ByVal level As IsolationLevel)
        trans = conn.BeginTransaction(level)
    End Sub
    ''Boris_20090916_End

    Public Sub CommitTrans()
        trans.Commit()
    End Sub

    Public Sub RollbackTrans()
        trans.Rollback()
    End Sub

#End Region

    Public Function GetCommand(ByVal sqlText As String, ByVal cmdType As CommandType, ByVal ParamArray parameterValues() As Object) As SqlCommand
        Dim cmd As New SqlCommand()
        Dim closeConnAfterUse As Boolean = False
        Dim key As String = String.Empty
        Try
            'Set command properties
            cmd.Connection = conn
            cmd.CommandTimeout = cmdTimeOut
            cmd.CommandText = sqlText
            cmd.CommandType = cmdType
            cmd.Transaction = trans

            'Set parameters
            If parameterValues IsNot Nothing AndAlso parameterValues.Length > 0 Then
                Select Case cmdType
                    Case CommandType.StoredProcedure
                        'For stored procedure
                        If conn.State = ConnectionState.Closed Then
                            conn.Open()
                            closeConnAfterUse = True
                        End If

                        'Check if parameter cached
                        key = GetCacheKey(cmd)

                        SyncLock SyncLocker
                            If parameterCache.ContainsKey(key) Then
                                cmd.Parameters.AddRange(CloneParameters(CType(parameterCache(key), IDataParameter())))
                            Else
                                SqlCommandBuilder.DeriveParameters(cmd)

                                Dim params As IDataParameterCollection = cmd.Parameters
                                Dim paramsArray(params.Count - 1) As IDataParameter
                                params.CopyTo(paramsArray, 0)

                                parameterCache.Add(key, CloneParameters(paramsArray))
                            End If
                        End SyncLock

                        If (cmd.Parameters.Count - parameterShiftIndex <> parameterValues.Length) Then
                            Throw New InvalidOperationException("Parameters' and Values' number is not match")
                        End If

                        For i As Int32 = 0 To parameterValues.Length - 1
                            If parameterValues(i) Is Nothing Then
                                cmd.Parameters(i + parameterShiftIndex).Value = DBNull.Value
                            Else
                                cmd.Parameters(i + parameterShiftIndex).Value = parameterValues(i)
                            End If

                        Next
                    Case CommandType.Text
                        'For SQL Text, the SQL must be written as "SELECT * FROM XXX WHERE A=@0 AND B=@1 OR C=@2"
                        For i As Int32 = 0 To parameterValues.Length - 1
                            If parameterValues(i) Is Nothing Then
                                cmd.Parameters.Add(New SqlParameter("@" & i.ToString(), DBNull.Value))
                            Else
                                cmd.Parameters.Add(New SqlParameter("@" & i.ToString(), parameterValues(i)))
                            End If

                        Next

                    Case Else
                        Throw New ArgumentException("Parameters are only supported for SQL text and stored procedure")
                End Select

            End If

            Return cmd
        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Connection string:" + conn.ConnectionString)
            Log.Instance.Error(ex.StackTrace)
            Throw New CustomException("Cannot connect to Dropbox database server.", ex)
        Finally
            If closeConnAfterUse AndAlso conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
    End Function

    Public Function GetParameterValue(ByVal cmd As SqlCommand, ByVal parameterName As String) As Object
        If cmd.Parameters(parameterName).Value Is DBNull.Value Then
            Return Nothing
        Else
            Return cmd.Parameters(parameterName).Value
        End If
    End Function

    Public Function GetParameterValue(ByVal cmd As SqlCommand, ByVal parameterIndex As Int32) As Object
        If cmd.Parameters(parameterIndex).Value Is DBNull.Value Then
            Return Nothing
        Else
            Return cmd.Parameters(parameterIndex).Value
        End If
    End Function

    Public Function GetReturnValue(ByVal cmd As SqlCommand, ByVal returnParameterName As String) As Object
        Return GetParameterValue(cmd, returnParameterName)
    End Function

    Public Function GetReturnValue(ByVal cmd As SqlCommand) As Object
        Return GetReturnValue(cmd, returnValue)
    End Function

    Public Function GetDataTable(ByVal cmdText As String) As DataTable
        Return GetDataTable(GetCommand(cmdText, CommandType.Text, Nothing))
    End Function

    Public Function GetDataTable(ByVal cmd As SqlCommand) As DataTable


        Dim da As New SqlDataAdapter(cmd)
        Dim tbl As New DataTable("UniSign.Database.DataTable") 'Just for a name in order to be serialzed when calling in web services
        Try

            da.Fill(tbl)
            Return tbl.Copy()
        Finally
            tbl.Dispose()
            da.Dispose()
        End Try
    End Function

    Public Function GetDataSet(ByVal cmd As SqlCommand, ByVal dataSetName As String) As DataSet


        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet(dataSetName)
        Try

            da.Fill(ds)
            Return ds.Copy()
        Finally
            ds.Dispose()
            da.Dispose()
        End Try
    End Function

    Private Function GetCacheKey(ByVal cmd As SqlCommand) As String
        Return conn.ConnectionString & ":" & cmd.CommandText
    End Function

    Private Function CloneParameters(ByVal parametersArray() As IDataParameter) As IDataParameter()
        Dim cloneArray(parametersArray.Length - 1) As IDataParameter
        For i As Int32 = 0 To parametersArray.Length - 1
            cloneArray(i) = CType(CType(parametersArray(i), ICloneable).Clone(), IDataParameter)
        Next

        Return cloneArray
    End Function

    Public Overridable Function GetData(ByVal tableName As String, ByVal filter As Dictionary(Of String, Object)) As DataTable
        Try
            Dim sql As String = String.Format("SELECT * FROM {0} WITH(NOLOCK) ", tableName)

            'Set condition
            Dim whereSql As String = String.Empty
            Dim values As Object() = GetSelectedItemFilterValuesAndSQL(filter, whereSql)

            sql &= whereSql

            Return DirectCast(Execute(sql, CommandType.Text, ExecuteType.ExecuteDataTable, values), DataTable)

        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Connection string:" + conn.ConnectionString)
            Log.Instance.Error(ex.StackTrace)
            Throw New CustomException("Cannot connect to Dropbox database server.", ex)
        Finally
        End Try
    End Function

    Public Overridable Sub DeleteData(ByVal tableName As String, ByVal filter As Dictionary(Of String, Object))
        Dim closeAfterUse As Boolean = False
        Try
            Dim sql As String = String.Format("Delete FROM {0}", tableName)

            'Set condition
            Dim whereSql As String = String.Empty
            Dim values As Object() = GetSelectedItemFilterValuesAndSQL(filter, whereSql)

            sql &= whereSql

            Execute(sql, CommandType.Text, ExecuteType.ExecuteNonQuery, values)

        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Connection string:" + conn.ConnectionString)
            Log.Instance.Error(ex.StackTrace)
            Throw New CustomException("Cannot connect to Dropbox database server.", ex)
        Finally
        End Try
    End Sub

    Public Overridable Sub InsertData(ByVal tableName As String, ByVal filter As Dictionary(Of String, Object))
        Try
            Dim sql As String = String.Format("insert into {0} ", tableName)

            'Update
            Dim insertSql As String = " ({0}) values ({1})"
            Dim s1 As String = String.Empty
            Dim s2 As String = String.Empty
            Dim values As New List(Of Object)()
            If filter Is Nothing OrElse filter.Count = 0 Then
                insertSql = String.Empty
                Return
            Else
                Dim keyIndex As Integer = 0
                Dim i As Integer = 0
                Dim key As String = String.Empty
                For Each item As KeyValuePair(Of String, Object) In filter
                    If s1 = String.Empty Then
                        s1 &= item.Key
                    Else
                        s1 &= String.Format(" ,{0} ", item.Key)
                    End If
                    If s2 = String.Empty Then
                        s2 &= String.Format("@{0} ", i)
                    Else
                        s2 &= String.Format(" ,@{0} ", i)
                    End If
                    i += 1
                    values.Add(item.Value)
                Next
            End If
            insertSql = String.Format(insertSql, s1, s2)
            sql &= insertSql
            Execute(sql, CommandType.Text, ExecuteType.ExecuteNonQuery, values.ToArray())

        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Connection string:" + conn.ConnectionString)
            Log.Instance.Error(ex.StackTrace)
            Throw New CustomException("Cannot connect to Dropbox database server.", ex)
        Finally
        End Try
    End Sub

    Public Overridable Sub UpdateData(ByVal tableName As String, ByVal update As Dictionary(Of String, Object), ByVal filter As Dictionary(Of String, Object))
        Try
            Dim sql As String = String.Format("Update {0} set ", tableName)

            'Update
            Dim updateSql As String = String.Empty
            If update Is Nothing OrElse update.Count = 0 Then
                updateSql = String.Empty
                Return
            Else
                Dim keyIndex As Integer = 0
                Dim i As Integer = 0
                Dim key As String = String.Empty
                For Each item As KeyValuePair(Of String, Object) In update
                    key = item.Key
                    If updateSql = String.Empty Then
                        updateSql &= String.Format("{0}=@{1} ", key, i + filter.Count)
                    Else
                        updateSql &= String.Format(" ,{0}=@{1} ", key, i + filter.Count)
                    End If
                    i += 1
                Next
            End If
            sql &= updateSql
            'Set condition
            Dim whereSql As String = String.Empty
            Dim values As Object() = GetSelectedItemFilterValuesAndSQL(filter, whereSql)
            Dim collection As New List(Of Object)()
            For Each item As Object In values
                collection.Add(item)
            Next
            For Each item As KeyValuePair(Of String, Object) In update
                collection.Add(item.Value)
            Next

            sql &= whereSql

            Execute(sql, CommandType.Text, ExecuteType.ExecuteNonQuery, collection.ToArray())

        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Connection string:" + conn.ConnectionString)
            Log.Instance.Error(ex.StackTrace)
            Throw New CustomException("Cannot connect to Dropbox database server.", ex)
        Finally
        End Try
    End Sub

    Public Overridable Sub UpdateTime(ByVal tableName As String, ByVal CompareTime As Object, ByVal NewTime As Object, ByVal filter As Dictionary(Of String, Object))
        Try
            Dim sql As String = String.Format("Update {0} set ", tableName)

            'Update
            Dim updateSql As String = String.Format(" LastUpdateDateTime=@{0} ", filter.Count)

            sql &= updateSql
            'Set condition
            Dim whereSql As String = String.Empty
            Dim values As Object() = GetSelectedItemFilterValuesAndSQL(filter, whereSql)
            whereSql &= String.Format(" and (LastUpdateDateTime<@{0} or LastUpdateDateTime is NULL)", filter.Count + 1)
            Dim collection As New List(Of Object)()
            For Each item As Object In values
                collection.Add(item)
            Next
            collection.Add(NewTime)
            collection.Add(CompareTime)

            sql &= whereSql

            Execute(sql, CommandType.Text, ExecuteType.ExecuteNonQuery, collection.ToArray())

        Catch ex As Exception
            Log.Instance.Error(ex.Message)
            Log.Instance.Error("Connection string:" + conn.ConnectionString)
            Log.Instance.Error(ex.StackTrace)
            Throw New CustomException("Cannot connect to Dropbox database server.", ex)
        Finally
        End Try
    End Sub

    ''' <summary>
    ''' Get the filter values array for the selected record and return the WHERE SQL statement through output parameter
    ''' </summary>
    ''' <param name="filter"></param>
    ''' <param name="filterSQL"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetSelectedItemFilterValuesAndSQL(ByVal filter As Dictionary(Of String, Object), ByRef filterSQL As String) As Object()
        'Set filter
        If filter Is Nothing OrElse filter.Count = 0 Then
            filterSQL = String.Empty
            Return Nothing
        Else
            Dim filterString As New StringBuilder()
            Dim filterValues As New List(Of Object)
            Dim keyIndex As Integer = 0
            Dim i As Integer = 0
            Dim key As String = String.Empty
            For Each item As KeyValuePair(Of String, Object) In filter
                key = item.Key
                filterString.Append(String.Format("AND {0}=@{1} ", key, i))
                filterValues.Add(item.Value)
                i += 1
            Next

            filterSQL = String.Format(" WHERE 1=1 {0}", filterString.ToString())
            Return filterValues.ToArray()
        End If
    End Function

#Region "IDisposable members"
    Private disposedValue As Boolean = False        ' To detect redundant calls
    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                conn.Dispose()
            End If

        End If
        Me.disposedValue = True
    End Sub

    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub

#End Region


End Class