Using LLBLGen Pro Version 2.6 Final (April 15th, 2009)
Using Template: Adapter
Target Platform: .NET 2.0
SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll File version 2.6.9.511
I've got a problem similar to the one mentioned in this thread:
http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=17014&HighLight=1
I call a stored procedure:
ActionProcedures.Usp_DeleteJournalEntries(batchSize, dateString, LLBLGenDataAccessAdapter.TheDataAccessAdapter);
LLBLGenDataAccessAdapter.TheDataAccessAdapter is the data access adapter.
Here's the usp_DeleteJournalEntries()
public static int Usp_DeleteJournalEntries(System.Int32 batchSize, System.String lessThanOrEqualToDate, DataAccessAdapter adapter)
{
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter("@batchSize", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Current, batchSize);
parameters[1] = new SqlParameter("@lessThanOrEqualToDate", SqlDbType.VarChar, 14, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Current, lessThanOrEqualToDate);
int toReturn = adapter.CallActionStoredProcedure("[GCSLRMS_Empty].[dbo].[usp_DeleteJournalEntries]", parameters);
return toReturn;
}
You can see the LLBLGen code was generated using the database name "GCSLRMS_Empty". The customer that uses this program selects a SQL Server database to use at runtime. In this case, they chose a database with the catalog name "GCSLRMS Database". That name contains a space.
Earlier in the program, I use a CatalogNameOverwrite to use the selected database instead of "GCSLRMS_Empty"
CatalogNameOverwriteHashtable aCatalogNameOverwriteHashtable = new CatalogNameOverwriteHashtable();
aCatalogNameOverwriteHashtable.Add("GCSLRMS_Empty", databaseName);
_dataAccessAdapter = new DataAccessAdapter(databaseConnectionString, false, aCatalogNameOverwriteHashtable, null);
OK, back to the stored procedure. It calls CallActionStoredProcedure().
Here's a snippet of that code:
public virtual int CallActionStoredProcedure(string storedProcedureToCall, SqlParameter[] parameters)
{
SqlCommand command = new SqlCommand(CreateCorrectStoredProcedureName(storedProcedureToCall));
command.Connection = (SqlConnection)base.GetActiveConnection();
...
}
After creating the SqlCommand, the CommandText property looks like this:
command.CommandText = "database.[dbo].[usp_DeleteJournalEntries]"
This command won't work!
I want it to say:
command.CommandText = "[GCSLRMS database].[dbo].[usp_DeleteJournalEntries]"
The problem happens in the LLBLGen code:
private string CreateCorrectStoredProcedureName(string storedProcedureName)
{
DynamicQueryEngine dqe = (DynamicQueryEngine)CreateDynamicQueryEngine();
string procName = dqe.GetNewPerCallStoredProcedureName(storedProcedureName);
return DynamicQueryEngine.GetNewStoredProcedureName(procName);
}
After calling dqe.GetNewPerCallStoredProcedureName():
procName = "gcslrms database.[dbo].[usp_DeleteJournalEntries]"
I don't understand why it didn't put square brackets around the catalog name like this:
procName = "[gcslrms database].[dbo].[usp_DeleteJournalEntries]"
Without the square brackets around the catalog name, the next statement:
DynamicQueryEngine.GetNewStoredProcedureName(procName) returns:
"database.[dbo].[usp_DeleteJournalEntries]"
So how do I work around this problem?
I tried overriding "GCSLRMS_Empty" with "[GCSLRMS Database]", but that didn't work because other parts of the program would generate SQL like this that doesn't work:
SELECT [[gcslrms_database]].[dbo].[name]
FROM [[gcslrms_database]].[dbo].[Person]
The normal catalog overwrites do properly wrap the name, the proc names are the ones which aren't wrapped. 