views:

228

answers:

2

I'm working with a legacy application which I'm trying to change so that it can work with SQL CE, whilst it was originally written against SQL Server.

The problem I am getting now is that when I try to do dataAdapter.Update, SQL CE complains that it is not expecting the SELECT keyword in the command text. I believe this is because SQL CE does not support batch SELECT statements.

The auto-generated table adapter command looks like this...

this._adapter.InsertCommand.CommandText = @"INSERT INTO [Table] ([Field1], [Field2]) VALUES (@Value1, @Value2);
SELECT Field1, Field2 FROM Table WHERE (Field1 = @Value1)";

What is it doing? It looks like it is inserting new records from the datatable into the database, and then reading that record back from the database into the datatable? What's the point of that?

Can I just go through the code and remove all these SELECT statements? Or is there an easier way to solve my problem of wanting to use these data adapters with SQL CE?

I cannot regenerate these table adapters, as the people who knew how to have long since left.

+1  A: 

It is just updating the object with the latest values from the database, after an update. Always seemed a little unecessary to me but hey...

These are a nuisance from a maintenance point of view - if you have the option, you'll save yourself a lot of hassle by abstracting this all out to a proper data layer.

Paddy
It makes sense if you are pulling back auto-incremented/identity/rowguid fields but here it seems a little superfluous. +1 for beating me to it.
Lazarus
@Lazarus, Mmm, I agree, I always thought it was a little overkill even for that. I think it's also used to set the fields that it believes are the current in the DB, for concurrency checking (which I also never liked).
Paddy
Ah... that's interesting. I tried removing the SELECT from the command texts, and I got a concurrency violation exception. Do you think the SELECTs are necessary in order to avoid that error, or could it be something else?
RickL
@Rickl Whether the SELECTs are necessary depends entirely on your database. If you use Identity columns. triggers or default values then Yes, they are necessary. Most critical is getting the Primary key after an Insert.
Henk Holterman
In the end, I modified the .designer.cs code to recreate the UPDATE/INSERT commands using a SqlCeCommandBuilder to resolve the problem.
RickL
A: 

allows that the field values might be altered by trigger(s) on the table. Sensible enough, I'd have thought, in auto-generated boilerplate.

though the select statement is a tad whacky to assume that field1 is the primary key... but maybe the autogen code makes sure it is before generating this bit of code.

kpollock
Yes, the auto-generated code will select on the primary key fields. The example I made above was some "sanitized" code.
RickL