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

LLBLGen Runtime library version, please.

Where can I find that?

Details can be found here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722

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

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

Attached

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.

        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

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));
}

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 Problem NOT solved.

Database name above should NOT be [HR].

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.

Absolutely wonderful... thanks David!!