Solved

Microsoft DAAB enteprise library 3.1  Opening Connection

Posted on 2007-11-18
10
487 Views
Last Modified: 2013-12-17
Hi,
I have been using DAAB Enterpise Library 3.1 without any issues.. I have the following functions being used in my Data Access Classes  
1) GetDatabase returns instance of specific database
2) LoadDataset
3)ExecuteNonQuery
4)UpdateDataset
In any of these functions I never open a connection. Though everything works.... I am not able to understand where the connection is being opened ... Does any one has come accross documenation of Connection being opened on these functions.
0
Comment
Question by:dotnet0824
  • 6
  • 4
10 Comments
 
LVL 37

Expert Comment

by:samtran0331
ID: 20308961
I can't find any white paper...but in older versions of the Application blocks, you could actually see (and modify) Microsoft's class files for the Blocks...and for the DAAB, there was a file called "sqlhelper.vb" that had the code for all the functions you listed, and the connections were opened/closed in there.

I'm not sure how "open" 3.1 is now (if MS give you full access to the codefiles)...but if you did a "Go to definition" of any of those functions above, the connection opening/closing is going to be in that same class/dll.
0
 

Author Comment

by:dotnet0824
ID: 20309164
Well I never used Open connection still all my functions work with all the 4 database functions listed above .... Other functions of 3.1 also i do the same as below.. I am not understanding where exactly its opening connection etc
here is a sample how i use
DB = Base.GetDatabase();  --- Returns instance of Database
 DbCommand _dbCommand = _db.GetStoredProcCommand(_sqlCommand);
                DB.AddInParameter(_dbCommand, "@Name", DbType.String, row.Name);
                DB.AddInParameter(_dbCommand, "@City", DbType.String,row.City);
                DB.AddInParameter(_dbCommand, "@Active",DbType.Boolean, row.Active);
                DB.AddOutParameter(_dbCommand, "@identity",DbType.Int64, 4);
                 DB.AddParameter(_dbCommand, "@RETURN_VALUE",DbType.Int32, ParameterDirection.ReturnValue, null,DataRowVersion.Default, null);
                DB.UpdateDataSet(_Dataset, "DataSetName",_dbCommand, null, null, UpdateBehavior.Standard, 0);
0
 

Author Comment

by:dotnet0824
ID: 20309199
Hi,
I found that link http://blog.csdn.net/chenmintong/archive/2007/06/24/1664261.aspx   U can see its for 3.1 all functions have been used... But where is Open connection
0
 
LVL 37

Expert Comment

by:samtran0331
ID: 20311989
Hi,
When I mentionedd "open", I didn't mean look for an "Open" function, in MS-DAAB 2.0, the actual code was visible, like below.
In 3.0, I think they locked down the code so that you couldn't modify it, and so all the connection stuff is handled in the Enterprise Lib dll...

Below is a sample from the 2.0 DAAB file. ExecuteNonQuery is 9 overloaded functions, and the one below opens the connection...
    Public Overloads Shared Function ExecuteNonQuery(ByVal connectionString As String, _

                                                     ByVal commandType As CommandType, _

                                                     ByVal commandText As String, _

                                                     ByVal ParamArray commandParameters() As SqlParameter) As Integer

        If (connectionString Is Nothing OrElse connectionString.Length = 0) Then Throw New ArgumentNullException("connectionString")

        ' Create & open a SqlConnection, and dispose of it after we are done
 

        Using connection = New SqlConnection(connectionString)

            connection.Open()

            ' Call the overload that takes a connection in place of the connection string

            Return ExecuteNonQuery(connection, commandType, commandText, commandParameters)

        End Using
 

    End Function ' ExecuteNonQuery

Open in new window

0
 

Author Comment

by:dotnet0824
ID: 20312181
Thats fine... I appreciate that. But the problem now is I am using Enteprise libaray 3.1 DAAB.  I have searched on net but couldnt find any information about it............As posted in the code above it just calls updateDataset /ExecuteNonquery /LoadDataset by setting the parameters thats it........Really whats happening (does it mean that the connection is opened when we call these DAAB functions) god knows
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:dotnet0824
ID: 20312184
I have increased the points for this as its become a mystery for me...
0
 
LVL 37

Expert Comment

by:samtran0331
ID: 20312294
When you installed 3.1, there is an option to install the source code for the entire enterprise library.
Once it's installed, there are a bunch of Visual Studio projects that is the source code for all of it.
Browse to:
EntLib3Src\App Blocks\Src\Data

"EntLib3Src" is the default folder where the source gets installed.
there is a file in there called:
Database.cs

open it up in VS, all the database connection code is in there.
0
 

Author Comment

by:dotnet0824
ID: 20314194
Great samtran0331: I did install it and I found a file which has some functions.. I am pasting below.
Did you understand looking at those functions where Exactly the Database is being Opened (Connection open) I mean and Closed

'===============================================================================
' Microsoft patterns & practices Enterprise Library
' Data Access Application Block QuickStart
'===============================================================================
' Copyright © Microsoft Corporation.  All rights reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
' OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
' LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
' FITNESS FOR A PARTICULAR PURPOSE.
'===============================================================================


' Encapulsates access to the database.
Public Class SalesData

    Public Sub SalesData()
    End Sub


    ' Retrieves a list of customers from the database.
    ' Returns: List of customers in a string.
    ' Remarks: Demonstrates retrieving multiple rows of data using
    ' a DataReader
    Public Function GetCustomerList() As String

        ' DataReader that will hold the returned results            
        ' Create the Database object, using the default database service. The
        ' default database service is determined through configuration.
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim sqlCommand As String = "Select CustomerID, Name, Address, City, Country, PostalCode " & _
            "From Customers"
        Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)

        Dim readerData As StringBuilder = New StringBuilder

        ' The ExecuteReader call will request the connection to be closed upon
        ' the closing of the DataReader. The DataReader will be closed
        ' automatically when it is disposed.
        Using dataReader As IDataReader = db.ExecuteReader(dbCommand)

            ' Iterate through DataReader and put results to the text box.
            ' DataReaders cannot be bound to Windows Form controls (e.g. the
            ' resultsDataGrid), but may be bound to Web Form controls.
            While (dataReader.Read())
                ' Get the value of the 'Name' column in the DataReader
                readerData.Append(dataReader("Name"))
                readerData.Append(Environment.NewLine)
            End While
        End Using
        Return readerData.ToString()
    End Function

    ' Retreives all products in the specified category.
    ' Category: The category containing the products.
    ' Returns: DataSet containing the products.
    ' Remarks: Demonstrates retrieving multiple rows using a DataSet.
    Public Function GetProductsInCategory(ByRef Category As Integer) As DataSet

        ' Create the Database object, using the default database service. The
        ' default database service is determined through configuration.
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim sqlCommand As String = "GetProductsByCategory"
        Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)

        ' Retrieve products from the specified category.
        db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category)

        ' DataSet that will hold the returned results            
        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        ' Note: connection was closed by ExecuteDataSet method call

        Return productsDataSet
    End Function

    ' Updates the product database.
    ' Returns: The number of rows affected by the update.
    ' Remarks: Demonstrates updating a database using a DataSet.
    Public Function UpdateProducts() As Integer
        ' Create the Database object, using the default database service. The
        ' default database service is determined through configuration.
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim productsDataSet As DataSet = New DataSet

        Dim sqlCommand As String = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate " & _
            "From Products"
        Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)

        Dim productsTable As String = "Products"

        ' Retrieve the initial data
        db.LoadDataSet(dbCommand, productsDataSet, productsTable)

        ' Get the table that will be modified
        Dim table As DataTable = productsDataSet.Tables(productsTable)

        ' Add a new product to existing DataSet
        Dim addedRow As DataRow = table.Rows.Add(New Object() {DBNull.Value, "New product", 11, 25})

        ' Modify an existing product
        table.Rows(0)("ProductName") = "Modified product"

        ' Establish our Insert, Delete, and Update commands
        Dim insertCommand As DbCommand = db.GetStoredProcCommand("AddProduct")
        db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current)
        db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current)
        db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current)

        Dim deleteCommand As DbCommand = db.GetStoredProcCommand("DeleteProduct")
        db.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current)

        Dim updateCommand As DbCommand = db.GetStoredProcCommand("UpdateProduct")
        db.AddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current)
        db.AddInParameter(updateCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current)
        db.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current)

        ' Submit the DataSet, capturing the number of rows that were affected
        Dim rowsAffected As Integer = db.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard)

        Return rowsAffected

    End Function

    ' Retrieves details about the specified product.
    ' productID: The ID of the product used to retrieve details.
    ' Returns: The product details as a string.
    ' Remarks: Demonstrates retrieving a single row of data using output parameters.
    Public Function GetProductDetails(ByRef productID As Integer) As String

        ' Create the Database object, using the default database service. The
        ' default database service is determined through configuration.
        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim sqlCommand As String = "GetProductDetails"
        Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)

        ' Add paramters
        ' Input parameters can specify the input value
        db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productID)
        ' Output parameters specify the size of the return data
        db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50)
        db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8)

        db.ExecuteNonQuery(dbCommand)

        ' Row of data is captured via output parameters
        Dim results As String = String.Format(CultureInfo.CurrentCulture, "{0}, {1}, {2:C} ", _
                                       db.GetParameterValue(dbCommand, "ProductID"), _
                                       db.GetParameterValue(dbCommand, "ProductName"), _
                                       db.GetParameterValue(dbCommand, "UnitPrice"))

        Return results
    End Function

    ' Retrieves the specified product's name.
    ' productID: The ID of the product.
    ' Returns: The name of the product.
    ' Remarks: Demonstrates retrieving a single item. Parameter discovery
    ' is used for determining the properties of the productID parameter.
    Public Function GetProductName(ByRef productID As Integer) As String

        ' Create the Database object, using the default database service. The
        ' default database service is determined through configuration.
        Dim db As Database = DatabaseFactory.CreateDatabase()

        ' Passing the productID value to the GetStoredProcCommand
        ' results in parameter discovery being used to correctly establish the parameter
        ' information for the productID. Subsequent calls to this method will
        ' cause the block to retrieve the parameter information from the
        ' cache, and not require rediscovery.
        Dim sqlCommand As String = "GetProductName"
        Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand, productID)

        ' Retrieve ProductName. ExecuteScalar returns an object, so
        ' we cast to the correct type (string).
        Dim productName As String = Convert.ToString(db.ExecuteScalar(dbCommand))

        Return productName
    End Function

    ' Retrieves a list of products.
    ' Returns: A list of products as an XML string.
    ' Remarks: Demonstrates retrieving multiple rows of data as XML. This
    ' method is not portable across database providers, but is
    ' specific to the SqlDatabase.
    Public Function GetProductList() As String

        ' Use a named database instance that refers to a SQL Server database.
        Dim dbSQL As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase(), SqlDatabase)

        ' Use "FOR XML AUTO" to have SQL return XML data
        Dim sqlCommand As String = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate " & _
            "From Products FOR XML AUTO"

        Dim dbCommand As DbCommand = dbSQL.GetSqlStringCommand(sqlCommand)

        Dim productsReader As XmlReader = Nothing
        Dim productList As StringBuilder = New StringBuilder

        Try
            productsReader = dbSQL.ExecuteXmlReader(dbCommand)

            ' Iterate through the XmlReader and put the data into our results.
            While (Not productsReader.EOF)

                If (productsReader.IsStartElement()) Then
                    productList.Append(productsReader.ReadOuterXml())
                    productList.Append(Environment.NewLine)
                End If
            End While
        Finally
            ' Close the Reader.
            If (Not productsReader Is Nothing) Then
                productsReader.Close()
            End If

            ' Explicitly close the connection. The connection is not closed
            ' when the XmlReader is closed.
            If (Not dbCommand.Connection Is Nothing) Then
                dbCommand.Connection.Close()
            End If
        End Try
        Return productList.ToString()
    End Function

    ' Transfers an amount between two accounts.
    ' transactionAmount: Amount to transfer.
    ' sourceAccount: Account to be credited.
    ' destinationAccount: Account to be debited.
    ' Returns: true if sucessful otherwise false.
    ' Remarks: Demonstrates executing multiple updates within the
    ' context of a transaction.
    Public Function Transfer(ByRef transactionAmount As Integer, ByRef sourceAccount As Integer, ByRef destinationAccount As Integer) As Boolean

        Dim result As Boolean = False

        ' Create the Database object, using the default database service. The
        ' default database service is determined through configuration.
        Dim db As Database = DatabaseFactory.CreateDatabase()

        ' Two operations, one to credit an account, and one to debit another
        ' account.
        Dim sqlCommand As String = "CreditAccount"
        Dim creditCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)

        db.AddInParameter(creditCommand, "AccountID", DbType.Int32, sourceAccount)
        db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount)

        sqlCommand = "DebitAccount"
        Dim debitCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)
        db.AddInParameter(debitCommand, "AccountID", DbType.Int32, destinationAccount)
        db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount)

        Using connection As DbConnection = db.CreateConnection()
            connection.Open()
            Dim transaction As DbTransaction = connection.BeginTransaction()

            Try

                ' Credit the first account
                db.ExecuteNonQuery(creditCommand, transaction)
                ' Debit the second account
                db.ExecuteNonQuery(debitCommand, transaction)
                ' Commit the transaction
                transaction.Commit()

                result = True
            Catch
                ' Rollback transaction
                transaction.Rollback()
            End Try

            connection.Close()
            Return result
        End Using
    End Function
End Class
0
 
LVL 37

Accepted Solution

by:
samtran0331 earned 500 total points
ID: 20314748
It looks like you're in the QuickStart sample code, which is not the "real" DAAB code....

But it doesn't matter, the whole point of the DAAB is that this stuff is hidden from you and you don't have to deal with it.

But for example sake, when you use the DAAB and call "ExecuteReader", "ExecuteNonQuery", "ExecuteScalar", "LoadDataset" etc...  for instance, there is an internal call to a function called GetOpenConnection

That is a protected function in the database.cs file, you as the developer using the DAAB don't have direct access to it...that's the whole point of the DAAB...

All I can tell you is that it's internal to the DAAB source code, if you want to pick your way through it, it's fairly complex, but it's all there.

don't look at the source code in the folder:
..\EntLib3Src\Quick Starts\

That's just sample projects *using* the DAAB

If you want to see what's going on *inside* the DAAB...open up the source project at:
..\EntLib3Src\App Blocks\Src\Data

0
 

Author Comment

by:dotnet0824
ID: 20316196
Yep That was Great Information......Thanks a lot samtran0331  
I have increased the points to 500 and awarding you all those.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now