I need to save an entity (with related entities) in two databases, one on the local machine, and another on a server. Some primary keys will be generated on the server, so i want to save there first and then locally.

I'm remoting the entity to the server and saving it there with no problems. In my server code, I refetch after the save and send back to the client so the client will have all the generated PKs.

adapter.SaveEntity() doesn't do anything on the client with the returned entity. Of course, I immediately realized that the entiity was coming back with IsNew set to false. So I reset this flag in the entity and its related entities. But still nothing is happening. What am I missing?

I realize I could create a new entity (entities, actually) and assign all the properties from the one passed back to this new one and save it, but that seems very wasteful.

Ok, it occurred to me right after I typed this, of course I still have the new entity on the client side, I don't have to create new ones and assign all the properties, I am just missing those generated primary keys. Of course when I remote my local entity a copy is being sent over the wire, I still have the original, it just doesn't have the primary keys I want.

So, I could get back the entity from the server (with IsNew set to false) and just grab the generated primary keys, and set my local copy's PK to that (and same for related entities) and should be able to save.

However, I'm curious why when I change the IsNew to false no save is ocurring. Also, if anyone is already doing this kind of thing more elegantly, I'd like to hear about it.

Ok, I think this has nothing to do with remoting or IsNew.

I was trying to override DataAccessAdapter so that I could add SET IDENDITY_INSERT ON / OFF to my queries, as per Frans' suggestion in another thread. Probably my implementation is mucking up things.

I've taken a quick look around and I don't see an example of overriding DataAccessAdapter. This is what I have:

     public class CompletionDataAccessAdapter : DataAccessAdapter
    {
        public override void OnSaveEntity(IActionQuery saveQuery, IEntity2 entityToSave)
        {
            base.OnSaveEntity(saveQuery, entityToSave);
        }
    }

There's no code yet to alter the sql because I was trying to get this to fire before doing that. But it never does fire. Here's my code to save

                CompletionDataAccessAdapter adapter = new CompletionDataAccessAdapter();
                adapter.SaveEntity(newCompletion);

Obviously I'm missing something.

Hmmmm

Tried it with just plain old DataAccessAdapter and still nothing is happening. Now I'm really freaking out.

I checked the connection string and its fine.

Sorry......damn.....this is getting embarrassing. I better get a new user name after this

I was still saving the entity coming back from the server....

Ok, my overridden adapter is working, OnSaveEntity is firing.

So my only question is...

if I I get the entity back from the server and set IsNew to true, how come it doesn't save? This is just curiosity, really. I'm assigning the generated primary keys to my local copy now and that appears to be an acceptable workaround (not elegant, but acceptable).

OK, now I've got a real problem.

I'm successfully injecting the SET IDENTITY_INSERT into the query. (Only funny thing is I cannot see the query text in Visual Studio debugger, but i just prepended and appended the text and had faith it would work).

         public override void OnSaveEntity(IActionQuery saveQuery, IEntity2 entityToSave)
        {
            if (entityToSave.LLBLGenProEntityName == "FieldEntity")
                saveQuery.Command.CommandText = "SET IDENTITY_INSERT Field ON;" + saveQuery.Command.CommandText + ";SET IDENTITY_INSERT Field OFF;";

...
            base.OnSaveEntity(saveQuery, entityToSave);
        }

The entity has a primary key value set. But it's not getting put into the sql statement.

 exec sp_executesql N'SET IDENTITY_INSERT Field ON;INSERT INTO [Completions].[dbo].[Field] ([Field],[OperationsOfficeID]) VALUES (@Field,@OperationsOfficeID);SELECT @FieldID=SCOPE_IDENTITY();SET IDENTITY_INSERT Field OFF;', N'@FieldID int output,@Field nvarchar(30),@OperationsOfficeID int', @FieldID = @P1 output, @Field = N'fldnm', @OperationsOfficeID = 6

Of course, now that I think about it, this makes sense. I assume I will have to take an additional step here and tell LLBLGenPro that this field is not an identity column so it will include that value?

Please help! I'm doing a demo tomorrow morning!!

Oh yeah...heh....for anyone who is following this thread or looks at later for a solution to the IDENTITY problem.......

SQL Server requires the user to be dbo in order to do SET IDENTITY_INSERT ON.

I hate IDENTITY_INSERT!!!!!!!!!!! What a pain, in those few cases when you are forced to use it. (Override IDENTITY, I mean, not IDENTITY property itself, which of course is very cool). I don't think this should even exist.

The first sign of insanity is when you start talking to yourself...

swallace wrote:

The first sign of insanity is when you start talking to yourself...

Believe me, I'm worried.

By the way, Frans, I don't get an ORMFieldIsReadonlyException like the manual says when I set the PKs for my entities, even though they are IDENTITY fields.

If I could just change IsIdentity to false at runtime, seems like that would do the trick. But in the SDK docs it appears to be readonly, and I can't find it through intellisense.

My head hurts.

JimFoye wrote:

By the way, Frans, I don't get an ORMFieldIsReadonlyException like the manual says when I set the PKs for my entities, even though they are IDENTITY fields.

This is true on adapter, as you have to set the field to a value prior to fetching.

Thanks. Do you have a solution to my problem? The alternative, I believe (other than using GUIDs instead of ints, because there is no SET_GUID_INSERT on, see my previous rant), is to drop IDENTITY property for these 4 tables and when the entities arrive at the server, I generate the PKs myself. Of course, I can do this, and years ago I used to do this kind of thing - when I started working with SQL Server, DRI didn't even exist and RI was supposed to be enforced through triggers (as I'm sure you know).

Anyway....I can do it, but it would be more elegant to leave IDENTITY in place and just have DAL on the client handle gracefully.

What do you think?

Sorry for all the posts today, but I was just wanted to add...this will be a big problem for me going forward. In my work today I was just saving these 4 tables (one primary entity and 3 related). But I have lots of other tables in the server database with IDENTITY property set on int PKs. These records will be added on the server, and they will be readonly on the client. They will be replicated through my program, and when the entities arrive at the client - same thing, I've got to be able to override the IDENTITY behavior.

I know many people in this situation would just use GUIDs everywhere, but I don't want to do that, I am actually using GUIDs on two tables where it's unavoidable, but I don't want to use them everywhere else in the database. (Not to insult anybody that relies on them a lot, but that is really a cop-out, I think, to have these huge ugly GUIDs all over the place IF they're not really needed).

Seems like LLBLGenPro should give me a graceful way to handle this situation (given SQL Server's cranky behavior). If not, I will have to contemplate generating almost ALL of the PKs on the server for every little lookup table (lots), OR....maybe consider using ADO.NET in my client alongside LLBLGenPro. I don't relish either one of these options.

Another thing I considered but I assumed was unworkable was to have different schema at server and client. Server db only would use IDENTITY. But it seems to me that I would have problems because I would have to generate slightly different LLBLGenPro DAL layers for client and server and then entities passed between wouldn't work right.

I can't help but wonder if other folks haven't had this problem. Though sometimes with this distributed stuff I feel like I've gone pas the bleeding edge and into another dimension entirely.

This is a replication issue. What I don't understand is what is the value of the PK on the client? If you have 100 clients, arriving at the server, won't that cause problems?

Otis wrote:

This is a replication issue. What I don't understand is what is the value of the PK on the client? If you have 100 clients, arriving at the server, won't that cause problems?

I will post a summary and example of the problem so you can better understand. I'll have to do it this weekend, though.

Thanks.

I'll start a new thread and close this one out.