Frans,

I now have a situation I believe can best be solved by explicitly inserting a value for the PK on a table with IDENTITY property set (MS SQL). I found this thread

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2129

By the way, if I search for IDENTITY_INSERT this thread doesn't turn up in the search results

Is this still the workaround? Seems a bit cumbersome to set up the stored procs and be calling them before and after insert. Also, if the second proc never gets called SQL Server will remember that IDENTITY_INSERT is set on the table and that can cause problems later. (Not sure if doing this in a transaction prevents that, but I don't think a SET statement would get rolled back). Finally, can I even set the PK field myself in the entity? (I know I can find the answer to that in 10 seconds but just so my question is complete ).

identity inserts are only recommended in replication scenario's. Otherwise don't define the column as identity or use a different ID scenario.

The point with the search engine is that '_' is illegal so it splits the words in two. If you search on identity insert it will show up

This weekend I'll re-index the search engine with a much less strict noise list, so a lot more search terms are possible. I'll also add pre-fabricated search links to the website (and the forum) so looking up things will be much easier.

Otis wrote:

identity inserts are only recommended in replication scenario's. Otherwise don't define the column as identity or use a different ID scenario.

Yeah, that's exactly what I have! Home-grown replication scenario! I'm rethinking it now, though, and I think that I will be able to do away with the IDENTITY property in the target database.

I realize it may be a very infrequent request, but this may be something that should be easier to do in LLBLGenPro.

JimFoye wrote:

Otis wrote:

identity inserts are only recommended in replication scenario's. Otherwise don't define the column as identity or use a different ID scenario.

Yeah, that's exactly what I have! Home-grown replication scenario! I'm rethinking it now, though, and I think that I will be able to do away with the IDENTITY property in the target database.

Depends. If you're adding data to the target database as well, you need the identity column there too. But then again, you should opt for a different key I think, as then can have dupes.

I realize it may be a very infrequent request, but this may be something that should be easier to do in LLBLGenPro.

Well, I think it's related to the choice for identity fields. They come at a price: 2 databases which have to be sync-ed give problems.

I'm going back to having IDENTITY columns in my local database. Distributed systems are hard! And it seems like each one has it's own requirements that make general rules hard to come by. In my case I'm starting to see there are problems when the server and local schema are different (this goes beyond the issue of IDENTITY).

Anyway........it would be nice if I could get LLBLGenPro to emit SET INDENTITY_INSERT on a table prior to doing an insert, for those rare occasions where it's needed. I realize this is db specific, not sure how you would do it, but you're so wonderful at solving problems, Frans!

JimFoye wrote:

I'm going back to having IDENTITY columns in my local database. Distributed systems are hard! And it seems like each one has it's own requirements that make general rules hard to come by. In my case I'm starting to see there are problems when the server and local schema are different (this goes beyond the issue of IDENTITY).

Yes, keeping everything in sync is a nice headache . Our CMS (asp based with COM ) uses a 'call tracker': I wrote a track layer which tracks all calls to the procs when the user uses the CMS designer to modify the site meta-data. Then I export these calls as XML. that XML is then imported on the webservers where the site runs and 'played back'. This way you don't have problems with identity values being out of sync. of course I could have saved myself some serious time by using GUIDs, but I was very performance paranoia at that time.

Anyway........it would be nice if I could get LLBLGenPro to emit SET INDENTITY_INSERT on a table prior to doing an insert, for those rare occasions where it's needed. I realize this is db specific, not sure how you would do it, but you're so wonderful at solving problems, Frans!

I've to dissapoint you, I'll not implement that. Though you can easily add that yourself: add 2 procs, and call these before and after the actual statement. Ok, a little overhead, but for the rare situations in which you might need it, you can.

OK, then I'm switching to Pragmatier.

Wait.....they're out of business.

JimFoye wrote:

OK, then I'm switching to Pragmatier.

Wait.....they're out of business.

Ah, that joke went over so well

Say, if I have two schema that are exactly the same, except for that a table in first has IDENTITY property set on PK and the second doesn't, and I generate the DAL for first, any problems when I use that DAL to insert an entity in the second?

JimFoye wrote:

JimFoye wrote:

OK, then I'm switching to Pragmatier.

Wait.....they're out of business.

Ah, that joke went over so well

I happen to know Mats, going out of business is never something you joke about.

Say, if I have two schema that are exactly the same, except for that a table in first has IDENTITY property set on PK and the second doesn't, and I generate the DAL for first, any problems when I use that DAL to insert an entity in the second?

You have to create a derived class from DataAccessadapter, as you have to reset the identity flag on the persistence info for the field (override GetFieldPersistenceInfo*() in your derived class). Otherwise the field never gets inserted.

You have to create a derived class from DataAccessadapter, as you have to reset the identity flag on the persistence info for the field (override GetFieldPersistenceInfo*() in your derived class). Otherwise the field never gets inserted.

Ah, perfect. I think this is the solution to my whole quandry about these IDENTITY fields. I really don't need them in the remote database, as they exist on tables that would only be populated on the server data base. So I think I'll just take these off and implement your solution.

can someone clue me in as to how to write a stored proc that takes a table name and turn the IDENTITY_INSERT on or off. I'm constructing a SQL statement in a variable then executing it, but it does not seem to work.

I also found this. WIll putting inside a transaction keep this from hapening

If a SET statement is set in a stored procedure, the value of the SET option is restored after control is returned from the stored procedure. Therefore, a SET statement specified in dynamic SQL does not affect the statements that follow the dynamic SQL statement.

thanks Jason

I think you can fake it by using adapter and a derived class of DataAccessAdapter. In there, you override OnSaveEntity(). You add a flag to your derived class which signals this override to add the identityinsert to the query or not. If the flag is set to true, you grab the CommandText's contents of the command object of the query object passed into OnSaveEntity(). You prefix it with SET IDENTITY_INSERT tablename ON; and suffix it with ;SET IDENTITY_INSERT tablename OFF;

It will then end up in the query, which should be ok.

FWIW, re:

You have to create a derived class from DataAccessadapter, as you have to reset the identity flag on the persistence info for the field (override GetFieldPersistenceInfo*() in your derived class). Otherwise the field never gets inserted.

A co-worker came up with this class, perhaps of use to someone else:

using System;
using System.Collections;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace IXYS.Framework.Data
{
    /// <summary>
    /// Summary description for Class1.
    /// </summary>
    public class IdentityInsertAdapter : IXYS.Data.DatabaseSpecific.DataAccessAdapter
    {
        public IdentityInsertAdapter() { }

        protected override IFieldPersistenceInfo GetFieldPersistenceInfo(IEntityField2 field)
        {
            IFieldPersistenceInfo info = base.GetFieldPersistenceInfo(field);
            IFieldPersistenceInfo tmpInfo = info;
            if (info.IsIdentity) tmpInfo = ResetIdentity(info);

            return tmpInfo;
        }

        protected override IFieldPersistenceInfo[] GetFieldPersistenceInfos(string entityName)
        {
            ArrayList modInfos = new ArrayList();
            IFieldPersistenceInfo[] infos = base.GetFieldPersistenceInfos(entityName);
            foreach(IFieldPersistenceInfo info in infos)
            {
                IFieldPersistenceInfo tmpInfo = info;
                if (info.IsIdentity) tmpInfo = ResetIdentity(info);
                modInfos.Add(tmpInfo);
            }

            return modInfos.ToArray(typeof(IFieldPersistenceInfo)) as IFieldPersistenceInfo[];
        }

        protected override IFieldPersistenceInfo[] GetFieldPersistenceInfos(IEntity2 entity)
        {
            ArrayList modInfos = new ArrayList();
            IFieldPersistenceInfo[] infos = base.GetFieldPersistenceInfos(entity);
            foreach(IFieldPersistenceInfo info in infos)
            {
                IFieldPersistenceInfo tmpInfo = info;
                if (info.IsIdentity) tmpInfo = ResetIdentity(info);
                modInfos.Add(tmpInfo);
            }

            return modInfos.ToArray(typeof(IFieldPersistenceInfo)) as IFieldPersistenceInfo[];
        }

        private IFieldPersistenceInfo ResetIdentity(IFieldPersistenceInfo info)
        {
            IFieldPersistenceInfo i = new FieldPersistenceInfo(
                info.SourceCatalogName,
                info.SourceSchemaName,
                info.SourceObjectName,
                info.SourceColumnName,
                info.SourceColumnIsNullable,
                info.SourceColumnDbType,
                info.SourceColumnMaxLength,
                info.SourceColumnScale,
                info.SourceColumnPrecision,
                false,
                info.IdentityValueSequenceName);

            return i;
        }
    }
}

Also, rather than calling SP's to set the IDENTITY_INSERT, we're doing this:

        private void SetIdentityInserts(string tableName, bool status)
        {
            SetIdentityInserts(tableName, status, false);
        }
        private void SetIdentityInserts(string tableName, bool status, bool reseed)
        {
            SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Main.ConnectionString"] );
            SqlCommand cmd = conn.CreateCommand();

            try
            {
                conn.Open();

                if (reseed)
                {
                    cmd.CommandText = String.Format( "dbcc checkident ({0}, reseed, 1)", tableName );
                    cmd.ExecuteNonQuery();
                }
                cmd.CommandText = String.Format( "set identity_insert {0} {1}", tableName, (status) ? "ON" : "OFF" );
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }

So, usage would be:

SetIdentityInserts("SomeTableName", true, true); // last value reseeds and is optional
IdentityInsertAdapter iiAdapter = new IdentityInsertAdapter();
iiAdapter.SaveEntity(someEntity);
SetIdentityInserts("SomeTableName", false);

Of course, this isn't the kind of thing you'd want to do when there's a load on a table. We did it for some data import stuff where the PK values had to be retained in the destination.

Thanks for sharing your code!

There is another recent thread on here where someone posted another solution, but I can't find it for some reason.

Then there was a thread where I went on and on about this before I decided to give up using IDENTITY, this was the thread that made most people on the forum realize I'm a little nutty.

We figured that out WAAAY before that thread, Jim

Frans, Jim, Jackk100 (or anyone):

What do I need to change to ensure that LLBL generates the SQL for the ID column when I turn on identity inserts? We are writing a one-time conversion application that needs to turn on identity inserts. I tried the code from Jackk100, but when I look at the query that's generated and executed, the ID field is not there (so it throws an exception since I turn on IDENTITY_INSERTs).

Here's the class I derived from DataAccessAdapter (containing only small modifications from Jackk100's):

    public class IdentityInsertAdapter : BILLCOMM.DataLayer.DatabaseSpecific.DataAccessAdapter
    {
        public IdentityInsertAdapter(String connect, Boolean keepOpen, String database) 
            : base(connect, false, CatalogNameUsage.ForceName, database) 
        { 
        }

        public override void OnSaveEntity(IActionQuery saveQuery, IEntity2 entityToSave)
        {
            if (entityToSave.LLBLGenProEntityName == "InvoiceEntity")
            {
                BatchActionQuery batchQuery = (BatchActionQuery)saveQuery;
                if (batchQuery.Count > 1)
                {
                    throw new ApplicationException("Doesn't happen in my conversion");
                }
                ActionQuery query = (ActionQuery)(batchQuery.ActionQueries[0]);

                query.Command.CommandText = "set identity_insert INVOICE ON\n" + query.Command.CommandText;
                query.Command.CommandText += "\nset identity_insert INVOICE OFF";
            }
            base.OnSaveEntity(saveQuery, entityToSave);
        }

        protected override IFieldPersistenceInfo GetFieldPersistenceInfo(IEntityField2 field)
        {
            IFieldPersistenceInfo info = base.GetFieldPersistenceInfo(field);
            IFieldPersistenceInfo tmpInfo = info;
            if (info.IsIdentity) 
                tmpInfo = ResetIdentity(info);

            return tmpInfo;
        }

        protected override IFieldPersistenceInfo[] GetFieldPersistenceInfos(string entityName)
        {
            ArrayList modInfos = new ArrayList();
            IFieldPersistenceInfo[] infos = base.GetFieldPersistenceInfos(entityName);
            foreach (IFieldPersistenceInfo info in infos)
            {
                IFieldPersistenceInfo tmpInfo = info;
                if (info.IsIdentity) 
                    tmpInfo = ResetIdentity(info);
                modInfos.Add(tmpInfo);
            }

            return modInfos.ToArray(typeof(IFieldPersistenceInfo)) as IFieldPersistenceInfo[];
        }

        protected override IFieldPersistenceInfo[] GetFieldPersistenceInfos(IEntity2 entity)
        {
            ArrayList modInfos = new ArrayList();
            IFieldPersistenceInfo[] infos = base.GetFieldPersistenceInfos(entity);
            foreach (IFieldPersistenceInfo info in infos)
            {
                IFieldPersistenceInfo tmpInfo = info;
                if (info.IsIdentity) 
                    tmpInfo = ResetIdentity(info);
                modInfos.Add(tmpInfo);
            }

            return modInfos.ToArray(typeof(IFieldPersistenceInfo)) as IFieldPersistenceInfo[];
        }

        private IFieldPersistenceInfo ResetIdentity(IFieldPersistenceInfo info)
        {
            IFieldPersistenceInfo i = info;
            if ((info.SourceColumnName.ToLower() == "id") && (info.SourceObjectName.ToLower() == "invoice"))
            {
                i = new FieldPersistenceInfo(
                    info.SourceCatalogName,
                    info.SourceSchemaName,
                    info.SourceObjectName,
                    info.SourceColumnName,
                    info.SourceColumnIsNullable,
                    info.SourceColumnDbType,
                    info.SourceColumnMaxLength,
                    info.SourceColumnScale,
                    info.SourceColumnPrecision,
                    false,
                    info.IdentityValueSequenceName,
                    info.TypeConverterToUse,
                    info.ActualDotNetType);
                i.IdentityValueSequenceName = "";
            }

            return i;
        }
    }

Should I be doing something different?

Thanks in advance, Josh

Alright - I figured out a kludgey way to get this done. For anyone who's interested in doing the same thing with identity inserts, here's the code:

    /// <summary>
    /// Make sure that invoice.id is always inserted
    /// </summary>
    public class IdentityInsertQueryEngine : SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine
    {
        protected override bool CheckIfFieldNeedsInsertAction(IEntityFieldCore field)
        {
            if ((field.ContainingObjectName == "InvoiceEntity") && (field.Name == "Id"))
            {
                return true;
            }
            else
            {
                return base.CheckIfFieldNeedsInsertAction(field);
            }
        }
    }

    /// <summary>
    /// New Adapter that overrides identity inserts
    /// </summary>
    public class IdentityInsertAdapter : BILLCOMM.DataLayer.DatabaseSpecific.DataAccessAdapter
    {
        public IdentityInsertAdapter(String connect, Boolean keepOpen, String database) 
            : base(connect, false, CatalogNameUsage.ForceName, database) 
        {
        }

        protected override DynamicQueryEngineBase CreateDynamicQueryEngine()
        {
            DynamicQueryEngineBase dqe = base.CreateDynamicQueryEngine();

            IdentityInsertQueryEngine toReturn = new IdentityInsertQueryEngine();
            toReturn.PerCallCatalogNameOverwrites = dqe.PerCallCatalogNameOverwrites;
            toReturn.PerCallSchemaNameOverwrites = dqe.PerCallSchemaNameOverwrites;
            return (DynamicQueryEngineBase)toReturn;
        }

        public override void OnSaveEntity(IActionQuery saveQuery, IEntity2 entityToSave)
        {
            if (entityToSave.LLBLGenProEntityName == "InvoiceEntity")
            {
                BatchActionQuery batchQuery = (BatchActionQuery)saveQuery;
                if (batchQuery.Count > 1)
                {
                    throw new ApplicationException("Doesn't happen in my conversion");
                }
                ActionQuery query = (ActionQuery)(batchQuery.ActionQueries[0]);

                query.Command.CommandText = "set identity_insert INVOICE ON\n" + query.Command.CommandText;
                query.Command.CommandText += "\nset identity_insert INVOICE OFF";
                query.Command.CommandText += "\nDBCC CHECKIDENT (INVOICE, RESEED)";
            }
            base.OnSaveEntity(saveQuery, entityToSave);
        }

        protected override IActionQuery CreateInsertDQ(IEntity2 entityToSave, IFieldPersistenceInfo[] persistenceInfoObjects)
        {
            ArrayList modInfos = new ArrayList();
            IFieldPersistenceInfo[] infos = persistenceInfoObjects;
            foreach (IFieldPersistenceInfo info in infos)
            {
                IFieldPersistenceInfo tmpInfo = info;
                if (info.IsIdentity)
                    tmpInfo = ResetIdentity(info);
                modInfos.Add(tmpInfo);
            }

            IActionQuery query = base.CreateInsertDQ(entityToSave
                , modInfos.ToArray(typeof(IFieldPersistenceInfo)) as IFieldPersistenceInfo[]);
            return query;
        }
                
        private IFieldPersistenceInfo ResetIdentity(IFieldPersistenceInfo info)
        {
            IFieldPersistenceInfo i = info;
            if ((info.SourceColumnName.ToLower() == "id") && (info.SourceObjectName.ToLower() == "invoice"))
            {
                i = new FieldPersistenceInfo(
                    info.SourceCatalogName,
                    info.SourceSchemaName,
                    info.SourceObjectName,
                    info.SourceColumnName,
                    info.SourceColumnIsNullable,
                    info.SourceColumnDbType,
                    info.SourceColumnMaxLength,
                    info.SourceColumnScale,
                    info.SourceColumnPrecision,
                    false,
                    info.IdentityValueSequenceName,
                    info.TypeConverterToUse,
                    info.ActualDotNetType);
                i.IdentityValueSequenceName = "";
            }

            return i;
        }
    }

Couldn't you just override GetFieldPersistenceInfo* in your derived adapter, and if identity inserts are enabled via a boolean parameter you define, simply set the IsIdentity flag to false? After all, that's what's been checked in the DQE. If IsIdentity is false, it's considered a normal field.

Otis wrote:

Couldn't you just override GetFieldPersistenceInfo* in your derived adapter, and if identity inserts are enabled via a boolean parameter you define, simply set the IsIdentity flag to false? After all, that's what's been checked in the DQE. If IsIdentity is false, it's considered a normal field.

I tried - but the field was still marked as readonly (so it wasn't added to the INSERT) and I couldn't figure out any other way to change it. My workaround method seemed a bit convoluted, but worked.