DB name hardcoded in retrieval procedures

Posts   
 
    
Bashar
User
Posts: 108
Joined: 11-Nov-2004
# Posted on: 27-Aug-2012 14:58:09   

Hi guys,

Our application allows the user to set the connection string using Microsoft's Data Connection Configuration Dialog. When creating an Adapter instance we are using this code:

        public DataAdapter(string connectionString, bool keepConnectionOpen)
            : base(connectionString, keepConnectionOpen)
        {
            CatalogNameOverwriteHashtable catalog = new CatalogNameOverwriteHashtable();
            catalog.Add("HR", DataBase.DataBaseName);
            SchemaNameOverwriteHashtable schema = new SchemaNameOverwriteHashtable();
            schema.Add("dbo", DataBase.SchemaName);
            
            this.CatalogNameOverwrites = catalog;
            this.SchemaNameOverwrites = schema;
        }

This works perfectly for all code, except for retrieval SPs.

I don't know what to do next.

  • LLBLGen v3.1 (recently upgraded to v3.5)
  • VS 2010
  • SQL 2008 R2
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 27-Aug-2012 20:37:01   

LLBLGen Runtime library version, please.

Bashar
User
Posts: 108
Joined: 11-Nov-2004
# Posted on: 27-Aug-2012 20:48:05   

Where can I find that? flushed

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 27-Aug-2012 20:50:53   
Bashar
User
Posts: 108
Joined: 11-Nov-2004
# Posted on: 27-Aug-2012 20:53:02   

Oops... should've known that... v2.0.50727

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 27-Aug-2012 21:07:40   

that's the .net runtime version. wink windows explorer -> right click -> properties -> version tab

Frans Bouma | Lead developer LLBLGen Pro
Bashar
User
Posts: 108
Joined: 11-Nov-2004
# Posted on: 27-Aug-2012 21:17:13   

Attached

Attachments
Filename File size Added on Approval
LLBL version.PNG 49,495 27-Aug-2012 21:18.08 Approved
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 28-Aug-2012 06:56:43   

I can't reproduce it with your LLBLGen build version and the latest one (3.5.12.824). I tried with SQLServer and MSOracle.

**My custom Adapter **

public class DataAccessAdatperCatalogOverrided : DataAccessAdapter
{
    public DataAccessAdatperCatalogOverrided(string connectionString, bool keepConnectionOpen)
        : base(connectionString, keepConnectionOpen)
    {
        CatalogNameOverwriteHashtable catalog = new CatalogNameOverwriteHashtable();
        catalog.Add("Northwind", "Northwind2");
        SchemaNameOverwriteHashtable schema = new SchemaNameOverwriteHashtable();
        schema.Add("dbo", "mySchema");
            
        this.CatalogNameOverwrites = catalog;
        this.SchemaNameOverwrites = schema;
    }
}

Usage

var cnnStr = "data source=.;initial catalog=Northwind;integrated security=SSPI;persist security info=False;packet size=4096";
using (var adapter = new DataAccessAdatperCatalogOverrided(cnnStr, false))
{
    var results = RetrievalProcedures.CustOrderHist("ALFKI", adapter);
}

Generated SQL

'[Northwind2].[mySchema].[CustOrderHist]'

How are you making the SP call? Since you are using your own Adapter class you should be careful about doing this:

using (var adapter = new DataAccessAdatperCatalogOverrided(cnnStr, false))
{
    var results = RetrievalProcedures.CustOrderHist("ALFKI");
}

Above code won't use my custom DataAccessAdatperCatalogOverrided class, it instead will instantiate a normal DataAccessAdapter class inside the RetrievalProcedures.CustOrderHist call.

David Elizondo | LLBLGen Support Team
Bashar
User
Posts: 108
Joined: 11-Nov-2004
# Posted on: 28-Aug-2012 11:58:39   
        public static TVAccountStatementTypedView GetAccountStatement(DateTime fromDate, DateTime toDate, int? accountID, int? employeeID)
        {
            var tv = new DAL.TypedViewClasses.TVAccountStatementTypedView();
            var adapter = DataAdapterFactory.Create();

            adapter.FetchTypedView(tv, DAL.DatabaseSpecific.RetrievalProcedures.GetQueryForTVAccountStatementTypedView(fromDate, toDate, accountID, employeeID));

            return tv;
        }

        public static DataAdapter Create(string connection, int timeout, bool keepConnectionOpen)
        {
            if (string.IsNullOrEmpty(connection))
            {
                connection = DataBase.DefaultConnection;
            }
            
            DataAdapter toReturn = new DataAdapter(connection, keepConnectionOpen);

            
            toReturn.CommandTimeOut = timeout;
            return toReturn;
        }

I think I need to fire somebody wink

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 28-Aug-2012 20:28:31   

I think I need to fire somebody.

Not sure I understand this remark, does it mean the issue is solved?

If it's not, could you please wrap the call in ausing statement as below:

using(var adapter = DataAdapterFactory.Create())
{
adapter.FetchTypedView(tv, DAL.DatabaseSpecific.RetrievalProcedures.GetQueryForTVAccountStatementTypedView(fromDate, toDate, accountID, employeeID));
}
Bashar
User
Posts: 108
Joined: 11-Nov-2004
# Posted on: 29-Aug-2012 12:57:46   

Did that... not solved...

Output:
Method Enter: DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(4)
Method Enter: DataAccessAdapterBase.OpenConnection

Connection physically opened. Method Exit: DataAccessAdapterBase.OpenConnection Executed Sql Query: Query: Stored procedure call: [HR].[dbo].[usp_AccountStatement](@FromDate, @ToDate, @AccountID, @EmployeeID) Parameter: @FromDate : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 29/08/2012 12:00:00 AM. Parameter: @ToDate : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 29/08/2012 12:00:00 AM. Parameter: @AccountID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: @EmployeeID : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: <undefined value>.

Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(4)

        public static TVAccountStatementTypedView GetAccountStatement(DateTime fromDate, DateTime toDate, int? accountID, int? employeeID)
        {
            var tv = new DAL.TypedViewClasses.TVAccountStatementTypedView();

            using (var adapter = DataAdapterFactory.Create())
            {
                adapter.FetchTypedView(tv, DAL.DatabaseSpecific.RetrievalProcedures.GetQueryForTVAccountStatementTypedView(fromDate, toDate, accountID, employeeID));
            }

            return tv;
        }

I think I need to fire somebody.

Yeah, the guy who wrote the code rage Problem NOT solved.

Database name above should NOT be [HR].

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 30-Aug-2012 05:48:14   

The problem is this:

... DAL.DatabaseSpecific.RetrievalProcedures.GetQueryForTVAccountStatementTypedView(fromDate, toDate, accountID, employeeID) ...

The GetQuery...TypedView(params) method always use inside the normal DataAccessAdapter to construct the call. As you see, you don't pass the adapter to that method, so it somehow has to construct the sql call, so it uses a normal adapter to do that. It's similar to the warning I posted above.

As you are using your own custom adapter, you should fetch it this way:

public static TVAccountStatementTypedView GetAccountStatement(DateTime fromDate, DateTime toDate, int? accountID, int? employeeID)
{
    var tv = new DAL.TypedViewClasses.TVAccountStatementTypedView();
    using (var adapter = DataAdapterFactory.Create())
    {
        RetrievalProcedures.FetchTVAccountStatementTypedView(adapter, tv, fromDate, toDate, accountID, employeeID);             
    }

    return tv;
}

That way the used adapter is always the one you pass to the method.

David Elizondo | LLBLGen Support Team
Bashar
User
Posts: 108
Joined: 11-Nov-2004
# Posted on: 01-Sep-2012 17:28:49   

Absolutely wonderful... thanks David!!