is this best practice to for connection ?? RSS

• • Reply
  asifbhura

  Member

  55 Points

  166 Posts

  is this best practice to for connection ??

  Apr 13, 2010 05:12 AM|asifbhura|LINK

  Hi

   

  given below code, i am using to this code toe execute my query or SP, some time i need to open both connection means oracle and sql server

  some time one of them,

  is this best practice to open and close connection,

  if not then please suggest me for closing and opening connection

  
  Public Class CommonDB
      Private Shared SqlCon As New SqlConnection
      Private Shared OraCon As New OleDb.OleDbConnection
      Private Shared ds As DataSet
      Private Shared Con As DbConnection
      Private Shared DataAdapter As DbDataAdapter
      Private Shared DataReader As DbDataReader
      Public Shared Cntype As ConnectionType

  
      Public Shared Function OpenConnection(ByVal ConType As ConnectionType) As DbConnection

          If ConType = ConnectionType.SQLSERVER Then
              SqlCon.ConnectionString = ConfigurationManager.AppSettings("DBConnection").ToString
              Con = SqlCon

          Else
              OraCon.ConnectionString = "Provider=MSDAORA.1;User ID=******;Password=******;Data Source=gov;Connection Lifetime=0;Min Pool Size =5;Max Pool Size=500;Connection Timeout=60;"
              Con = OraCon

          End If
          If Con.State = ConnectionState.Closed Then
              Con.Open()
          End If
          Return Con
      End Function

  
      Public Shared Sub CloseConnection()
          If Con.State = ConnectionState.Open Then
              Con.Close()
              'Con.Dispose()
          End If
      End Sub

      Public Enum ConnectionType
          SQLSERVER = 1
          ORACLE = 2
      End Enum

      Public Shared Function ExecuteQuery(ByVal Sql As String, ByVal CommType As CommandType, ByVal ConnType As ConnectionType, ByVal ParamArray Parameters As DbParameter()) As Object
          Dim GetOutPut As Object = Nothing
          Dim cmd As DbCommand = Command(ConnType)
          Try

              cmd.CommandText = Sql
              cmd.CommandType = CommType
              cmd.Connection = OpenConnection(Cntype)
              cmd.Transaction = Con.BeginTransaction
              cmd.Parameters.AddRange(Parameters)
              GetOutPut = cmd.ExecuteNonQuery()
              cmd.Transaction.Commit()
          Catch ex As Exception
              cmd.Transaction.Rollback()
          Finally
              CloseConnection()
              'cmd.Dispose()
          End Try
          Return GetOutPut
      End Function

  
      Public Shared Function ExecuteScalarQuery(ByVal Sql As String, ByVal CommType As CommandType, ByVal ConnType As ConnectionType) As Object
          Dim GetReult As Object = Nothing
          Dim cmd As DbCommand = Command(ConnType)
          Try
              cmd.CommandText = Sql
              cmd.CommandType = CommType
              cmd.Connection = OpenConnection(Cntype)
              cmd.Transaction = Con.BeginTransaction
              'cmd.Parameters.AddRange(Parameters)
              GetReult = cmd.ExecuteScalar()
              cmd.Transaction.Commit()
          Catch ex As Exception
              cmd.Transaction.Rollback()
          Finally
              CloseConnection()
              'cmd.Dispose()
          End Try
          Return GetReult
      End Function

  
     

  
      Public Shared Function ExecuteReaderQuery(ByVal Sql As String, ByVal CommType As CommandType, ByVal ConnType As ConnectionType, ByVal ParamArray Parameters As DbParameter()) As DbDataReader

          Dim sdr As DbDataReader = Nothing
          Dim cmd As DbCommand = Command(ConnType)
          Try

  
              cmd.CommandText = Sql
              cmd.CommandType = CommType
              cmd.Connection = OpenConnection(Cntype)
              cmd.Transaction = Con.BeginTransaction
              cmd.Parameters.AddRange(Parameters)
              sdr = cmd.ExecuteReader()
              cmd.Transaction.Commit()
          Catch ex As Exception
              cmd.Transaction.Rollback()
          Finally
              sdr.Close()
              'sdr.Dispose()
              CloseConnection()
              ' cmd.Dispose()
          End Try
          Return sdr
      End Function

      Public Shared Function ExecuteQuery(ByVal Sql As String, ByVal CommType As CommandType, ByVal ConnType As ConnectionType) As Object
          Dim GetExecOutPut As Object = Nothing
          Dim cmd As DbCommand = Command(ConnType)
          Try

              cmd.CommandText = Sql
              cmd.CommandType = CommType
              cmd.Connection = OpenConnection(Cntype)
              cmd.Transaction = Con.BeginTransaction
              GetExecOutPut = cmd.ExecuteNonQuery()
              cmd.Transaction.Commit()
          Catch ex As Exception
              cmd.Transaction.Rollback()
          Finally
              CloseConnection()
              ' cmd.Dispose()
          End Try
          Return GetExecOutPut
      End Function

      Public Shared Function FillDataSet(ByVal Sql As String, ByVal CommType As CommandType, ByVal ConnType As ConnectionType) As DataSet
          ds = New DataSet
          Dim cmd As DbCommand = Command(ConnType)
          Try

              cmd.CommandText = Sql
              cmd.CommandType = CommType
              cmd.Connection = OpenConnection(Cntype)
              DataAdapter = Adapter(ConnType, cmd)
              DataAdapter.Fill(ds)
          Catch ex As Exception

          Finally
              'cmd.Dispose()
              CloseConnection()
          End Try

          Return ds

      End Function
    

         Public Shared Function FillDataSet(ByVal Sql As String, ByVal CommType As CommandType, ByVal ConnType As ConnectionType, ByVal tablename As String, ByVal ParamArray Parameters As DbParameter()) As DataSet
          ds = New DataSet
          Dim cmd As DbCommand = Command(ConnType)
          Try

              cmd.CommandText = Sql
              cmd.CommandType = CommType
              cmd.Connection = OpenConnection(Cntype)
              cmd.Parameters.AddRange(Parameters)
              DataAdapter = Adapter(ConnType, cmd)
              DataAdapter.Fill(ds, tablename)
          Catch ex As Exception

          Finally
              CloseConnection()
              'cmd.Dispose()
          End Try

          Return ds

      End Function

      Public Shared Function CreateParameter(ByVal ParName As String, ByVal ParVal As Object, ByVal cntype As ConnectionType) As DbParameter
          Dim Par As DbParameter = Param(Cntype)
          Par.ParameterName = ParName
          Par.Value = ParVal
          Return Par
      End Function

      Public Shared Function Command(ByVal ConnType As ConnectionType) As DbCommand
          If ConnType = ConnectionType.SQLSERVER Then
              Command = New SqlCommand
              Cntype = ConnectionType.SQLSERVER
          Else
              Command = New OleDb.OleDbCommand
              Cntype = ConnectionType.ORACLE
          End If
          Return Command
      End Function

      Public Shared Function Adapter(ByVal ConnType As ConnectionType, ByVal cmd As DbCommand) As DbDataAdapter
          If ConnType = ConnectionType.SQLSERVER Then
              Adapter = New SqlDataAdapter(cmd)
          Else
              Adapter = New OleDb.OleDbDataAdapter(cmd)
          End If
          Return Adapter
      End Function

      'Public Shared Function Reader(ByVal Contype As ConnectionType) As
      '    If Contype = ConnectionType.SQLSERVER Then
      '        Reader = SqlDataReader
      '        Cntype = ConnectionType.SQLSERVER
      '    Else
      '        Reader = OleDb.OleDbDataReader
      '        Cntype = ConnectionType.ORACLE
      '    End If
      '    Return Reader
      'End Function

      Public Shared Function Param(ByVal cntype As ConnectionType) As DbParameter
          If Cntype = ConnectionType.SQLSERVER Then
              Param = New SqlParameter
          Else
              Param = New OleDb.OleDbParameter
          End If
          Return Param
      End Function
  End Class

   

• • Reply
  Xequence

  Contributor

  3105 Points

  2122 Posts

  Re: is this best practice to for connection ??

  Apr 13, 2010 11:13 AM|Xequence|LINK

  Best practice would be taking advantage of the using clause. The using clause closes your connection with the end using statement.

  Using connection As New SqlConnection(strConnection)
                  connection.Open()
                  Dim cmd As SqlCommand = New SqlCommand()
                  cmd.Connection = connection
                  cmd.CommandType = CommandType.Text
                  cmd.CommandText = "SELECT MAX(lup_pk) FROM tbllookup"
                  Dim adapter As SqlDataAdapter = New SqlDataAdapter(cmd)
  
  
                  adapter.Fill(ds, "lup_pk")
                  strID = ds.Tables("lup_pk").Rows(0).Item(0).ToString()
              End Using

  http://msdn.microsoft.com/en-us/library/htd05whh(VS.80).aspx

  Mysite
  Credentials
  CurbSmash