This is a migrated thread and some comments may be shown as answers.

Problem with MS SQL database with multiple schemas

1 Answer 66 Views
Databases and Data Types
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Makoto
Top achievements
Rank 1
Makoto asked on 25 Mar 2010, 09:30 PM
I have a database that is using several schemas.  I was able to generate the classes with Reverse Mapping just fine, and also see the Schema name show up in the mappings.

For example, I have a table called AccountBase in a schema called ClientSetup. Here's the XML mapping:
<class name="AccountBase"
            <extension key="db-do-not-create-table" value="true" /> 
            <extension key="db-table-name" value="ClientSetup.AccountBase" /> 
... 
</class> 

However, when I query it by using the following:

customerScope.Extent<AccountBase>(); 

I get the following error:
Telerik.OpenAccess.RT.sql.SQLException: Invalid object name 'AccountBase'

In the exception, the SQL that it's using is this (shortened for brevity):
SELECT a.[ID] AS COL1, ......, a.[WebSite] AS COL35 FROM [AccountBase] 

It should have generated SQL that said FROM [ClientSetup].[AccountBase] instead.

Also, in our project, we have several different customers, and each customer can have their own separate database, and we store the connection strings for each customer in the database.  We have a common database and several customer databases.  I've had to make a custom ObjectScopeProvider (called CustomerScopeProvider) to handle this, and hopefully it is correct. I'm using the generated ObjectScopeProver class to handle all the Common database tasks.

Here's the code that I use to get an instance of the CustomerScopeProvider:

 public static IObjectScope GetPerRequestScope(HttpContextBase context, int customerId) 
        { 
            IObjectScope commonScope = ObjectScopeProvider.GetPerRequestScope(context); 
            var customer = commonScope.Extent<Customer>().Single(c => c.Id == customerId); 
 
            string connection = string.Format("Data Source={0};Initial Catalog={1};User Id=user;Password=password", customer.DBServer, customer.DBName); 
 
            string key = HttpContext.Current.GetHashCode().ToString("x") + Thread.CurrentContext.ContextID.ToString() + customerId.ToString(); 
 
            IObjectScope scope; 
            if (context == null
            {                 
                scope = Database.Get(connection, commonScope.Database.BackendConfiguration, commonScope.Database.MetaData).GetObjectScope(); 
            } 
            else 
            { 
                scope = (IObjectScope)context.Items[key]; 
                if (scope == null
                { 
                    scope = Database.Get(connection, commonScope.Database.BackendConfiguration, commonScope.Database.MetaData).GetObjectScope(); 
                    context.Items[key] = scope; 
                } 
            } 
 
            return scope; 
        } 

Is there something that I'm doing wrong that would cause it to not include the schema name in the query?


1 Answer, 1 is accepted

Sort by
0
Serge
Telerik team
answered on 26 Mar 2010, 05:39 PM
Hello Makoto,

First I want to ask you if all your databases contain the same schemata. Essentially what you are doing is getting an scope to the main database and then passing its backend configuration and metadata to Database.Get() in order to get a new scope (if there are any differences in the metadata you are likely to get in trouble).

What I will suggest is to use the Database.Get() overload that has only one argument, the connection id. Also can you try executing your query with the default scope and get back to us.

Best wishes,
Serge
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
Tags
Databases and Data Types
Asked by
Makoto
Top achievements
Rank 1
Answers by
Serge
Telerik team
Share this question
or