Performance related question in (sqldatabase class) Microsoft Enterprise library RRS feed

  • Question

  • I am using Microsoft Enterprise library for connecting to SQLServer in console applcation.


    Following is the code snippet:

    Imports System.Data

    Imports System.Data.SqlClient

    Imports Microsoft.Practices.EnterpriseLibrary.Data.Sql

    Module Module1

    Public SDBInstance As SqlDatabase

    Public DBCommand As Common.DbCommand

    Sub Main()

    Dim CmdText As String

    Dim objCustId As Object = Nothing

    SDBInstance = New SqlDatabase("Server=xx;Database=yy;user id=aa;password=bb")


    DBCommand = SDBInstance.GetSqlStringCommand(CmdText)

    DBCommand.CommandTimeout = 1200

    objCustId = SDBInstance.ExecuteScalar(DBCommand)

    End Sub

    End Module


    Like these there are many sql statements (insert/update/delete/select) invoked in the above fashion in the console application..

    On doing this, everytime when we execute the sql statement, Enterprise library will create a new connection object and that connection object will be closed after the execution of the same. I am facing a drastic performance issue on doing this.

    Is there any way to have the single connection object opened and execute all the sql statements through the enterprise library component. (Note: I may not want these statments to be executed in the transaction mode). Please help.

    Wednesday, July 25, 2007 4:14 PM