Hi , i might be out in the dark here coz im a bit of a DB retard .

anyway, when i develop i work with a local sqlserver db, in that db all tables are named dbo.tablename

but when i make modifications on the live db the tables can be named "blah.tablename"

and when i then use the llblgen code on it it crashes saying it cant find the table named "dbo.tablename" since thats the name of the table in the generated code.

do the owner prefix really have to be included in the table name?

//Roger

The owner is included in the table name for performance reasons. You should only use different schemas in SqlServer if you really have to, otherwise use the default schema in a catalog, dbo (and thus create your tables under the db owner user), and for example, use different catalogs for each developer.

If you have to work with the setup you're confronted with, do a global rename/replace on "dbo" in the EntityFieldFactory.vb/cs file in the HelperClasses.

A rename of the schema in the GUI is implemented in the gui updates.

Any idea when this will be ready?

Bluecell

BlueCell wrote:

Any idea when this will be ready?

Bluecell

The gui updates are available since june 28th . Please download the latest installer from the upgrades section to get this functionality.

What a well hidden feature

BlueCell wrote:

What a well hidden feature

It's not that common (and added for a few special cases )

Is there a global way to change the catalog owner... I have several installations on shared SQL Servers. The ISPs force me to use the login name as the catalog owner which is different in each case.

I think this is a real case for something we can put into the app.config / web.config file.

What do you think?

Marcus

Marcus wrote:

Is there a global way to change the catalog owner...

Dooooh, yes just rename it at the top of the tree!!!

However part 2 of my previous question still stands regarding the config file and the need to deploy assemblies into situations where the catalog owner varies...

Marcus

Yeah renaming it is the trick

Having something in the config file is not that recommended I think, because it has to overwrite every time the name of the schema in the field.

I'll add it to the todo to add it to the properties to set when you start a generate action, so you can control last minute stuff.

Otis wrote:

I'll add it to the todo to add it to the properties to set when you start a generate action, so you can control last minute stuff.

Okay but this is not going to solve the problem since LLBLGen is often used to build "products" which are shipped as compiled assemblies. These assemblies will be deployed into a various environments each of which will have different configurations.

This is especially true when a product is ASP.NET based and is destined for small businesses whos web sites are hosted on shared ISP Servers. Each ISP puts different restrictions on their customers and often the ISP dictates catalog owner names for all the customers objects in the shared SQL Server.

This is the case I have experienced this week.

Otis wrote:

Having something in the config file is not that recommended I think, because it has to overwrite every time the name of the schema in the field.

Surely when you are building the dynamic SQL the catalog owner can also be inserted on the fly. The catalog owner name could be cached in memory since it's unlikely to change between application restarts, so reading the web.config need only happen once.

Additionally, there would also be the need to have the ability to specify catalog owner on a per "per object" basis as is the case using the Designer today.

While I appreciate that this is not a trivial change, for me at least it is one of high importance....

So as a workaround, can you confirm the following:

Assuming I have set "dbo" in the Designer, I will replace all instances of "dbo" in PersistenceInfoFactory.cs and RetrievalProcedures.cs with a method for obtaining the catalog owner from the config file (assume I will implement a cache ).

Is there anything else I need to do?

[edit]

Actually it looks like if I replace "<[SourceSchemaName]>" in the adapter templates with a cache lookup base on some object name key. For instance

toReturn.AddFieldPersistenceInfo("<[EntityFieldName]>", new FieldPersistenceInfo("<[SourceCatalogName]>", "<[SourceSchemaName]>", "<[SourceObjectName]>", "<[SourceColumnName]>", <[SourceColumnIsNullable]>, (int)SqlDbType.<[SourceColumnDbType]>, <[SourceColumnMaxLength]>, <[SourceColumnScale]>, <[SourceColumnPrecision]>, <[IsIdentity]>, "<[IdentityValueSequenceName]>"));<[NextForeach]>

would become:

toReturn.AddFieldPersistenceInfo("<[EntityFieldName]>", new FieldPersistenceInfo("<[SourceCatalogName]>", SchemaNameCache["<[SourceObjectName]>"], "<[SourceObjectName]>", "<[SourceColumnName]>", <[SourceColumnIsNullable]>, (int)SqlDbType.<[SourceColumnDbType]>, <[SourceColumnMaxLength]>, <[SourceColumnScale]>, <[SourceColumnPrecision]>, <[IsIdentity]>, "<[IdentityValueSequenceName]>"));<[NextForeach]>

where SchemaNameCache["<[SourceObjectName]>"] would lookup the correct Schema name...

I do get your concerns about this having a performance overhead however...

[/edit]

Marcus

Good points

I too ran into this issue with our own site in a hosting environment, and I had to rename the schemas, which was easy for me, but indeed, if it's a product you bought it's not.

The easiest way is to inject the name into the Database specific creator object, because only there the names are actually constructed: all SQL producing code uses these objects to produce names for fields, tables etc.

So I think what would be best is that users can specify from-to name pairs in the config file like (example) <schemaRename from="dbo" to="foo"/> (which should be placed inside a catalog rename as well if you want to rename catalog AND schema)

and also <catalogRename from="testDB" to="production" />

This because soon multiple catalogs can be added to a single project, and you perhaps want to rename a schema in one catalog but not in another.

The startup code then creates a hashtable, and makes that one a static hashtable by passing it on to the database specific creator object to be used by the DQE. That's it I think. Will also make schema / catalog overwriting faster in adapter (it's not slow now, but it can be done more efficiently).

Just ran into this exact problem today....is the ability to do a schema rename/mapping available now or is that something you're considering adding (sounds like the latter)?

Not yet available. You have to do it manually in the designer for now.

Should work (with adapter scenario) : Make an inherit of DataAccessAdapter an use it instead of generated one :

 public class MyDataAccessAdapter : MyProject.DataLayer.DatabaseSpecific.DataAccessAdapter
    {
        #region Private Member Variables
        private         string              _dbOwner            = string.Empty;
        #endregion

        #region Constructors
        /// <summary>
        /// CTor
        /// </summary>
        public MyDataAccessAdapter() : base()
        {
            this.loadCatalogOwner();
        }


        /// <summary>
        /// CTor
        /// </summary>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        public MyDataAccessAdapter(bool keepConnectionOpen) : base(keepConnectionOpen)
        {
            this.loadCatalogOwner();
        }


        /// <summary>
        /// CTor
        /// </summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        public MyDataAccessAdapter(string connectionString) : base(connectionString)
        {
            this.loadCatalogOwner();
        }


        /// <summary>
        /// CTor
        /// </summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        public MyDataAccessAdapter(string connectionString, bool keepConnectionOpen) : base(connectionString,keepConnectionOpen)
        {
            this.loadCatalogOwner();
        }
        
        
        /// <summary>
        /// CTor. SqlServer specific
        /// </summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        /// <param name="catalogNameUsageSetting">SqlServer specific. Configurates this data access adapter object how to threat catalog names in 
        /// persistence information.</param>
        /// <param name="catalogNameToUse">SqlServer specific. The name to use if catalogNameUsageSetting is set to ForceName. Ignored otherwise.</param>
        public MyDataAccessAdapter(string connectionString, bool keepConnectionOpen, CatalogNameUsage catalogNameUsageSetting, string catalogNameToUse)  : base(connectionString, keepConnectionOpen, catalogNameUsageSetting, catalogNameToUse)
        {
            this.loadCatalogOwner();
        }

        #endregion

        #region Public Properties
        /// <summary>
        /// Gets / Set the database owner
        /// </summary>
        public string               CatalogSchemaOwner 
        {
            get { return this._dbOwner; }
            set { this._dbOwner = value; }
        }
        #endregion

        #region Overrided Methods : GetFieldPersistenceInfo(s)
        protected override IFieldPersistenceInfo GetFieldPersistenceInfo(IEntityField2 field)
        {
            IFieldPersistenceInfo persistanceInfo = base.GetFieldPersistenceInfo(field);
            this.handleOwnerName(persistanceInfo);
            return persistanceInfo;
        }

        protected override IFieldPersistenceInfo[] GetFieldPersistenceInfos(IEntity2 entity)
        {
            IFieldPersistenceInfo[] persistanceInfoList = base.GetFieldPersistenceInfos(entity);
            this.handleOwnerName(persistanceInfoList);
            return persistanceInfoList;
        }

        protected override IFieldPersistenceInfo[] GetFieldPersistenceInfos(string entityName)
        {
            IFieldPersistenceInfo[] persistanceInfoList = base.GetFieldPersistenceInfos(entityName);
            this.handleOwnerName(persistanceInfoList);
            return persistanceInfoList;
        }
        #endregion

        #region Catalog owner modification
        private             void        handleOwnerName(IFieldPersistenceInfo persistenceInfo) 
        {
            switch(this.CatalogNameUsageSetting)
            {
                case CatalogNameUsage.Default:
                    // do nothing
                    return;
                case CatalogNameUsage.ForceName:
                    // write the name specified as the catalog name
                    persistenceInfo.SourceSchemaName = this.CatalogSchemaOwner;
                    break;
                case CatalogNameUsage.Clear:
                    persistenceInfo.SourceSchemaName = String.Empty;
                    break;
            }
        }
        private             void        handleOwnerName(IFieldPersistenceInfo[] persistenceInfos) 
        {
            if(this.CatalogNameUsageSetting == CatalogNameUsage.Default)
            {
                // nothing to do, return
                return;
            }
            
            for (int i = 0; i < persistenceInfos.Length; i++)
            {
                this.handleOwnerName(persistenceInfos[i]);
            }
        }
        private             void        loadCatalogOwner() 
        {
            try 
            {
                this.CatalogSchemaOwner = System.Configuration.ConfigurationSettings.AppSettings["DatabaseOwner"].ToString();
            } 
            catch 
            {
                this.CatalogSchemaOwner = "dbo";
            }
        }
        
        #endregion
    }

In the config file add a key :

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add key="DatabaseOwner" value="dbo" />
    </appSettings>
</configuration>

Maybe there are some errors but this is the idea cu

Otis wrote:

Not yet available. You have to do it manually in the designer for now.

Frans,

Any update on when this will be available?

Marcus

Marcus wrote:

Otis wrote:

Not yet available. You have to do it manually in the designer for now.

Frans,

Any update on when this will be available?

Marcus

I hope at the end of february. I'm almost done with the gui enhancements and can then move on to the rest of the stuff I've to add, which should roughly take 2 weeks or so. Perhaps I'll cut some feature here and there (no not this ). I'll do my best to get it out as soon as I can.