Sql – Linq2Sql Updates

data accesslinq-to-sqlnetsqlsql-update

Wondering if anyone else has done most of their Update SQL using stored procedures over Linq2Sql?

I like Linq2Sql for all the other operations but Updates seem to be nasty. The generated SQL doesn't look good in profiler with all the columns in the Where clause, then you have to select the current object to set the fields from the edited object before running SubmitChanges().

I'm finding just writing a old fashioned stored procedure better for Updates and using Linq2Sql for the rest. How about your experience?

Best Answer

You don't need all the columns in the Where clause if you include a Timestamp column in your table and a primary key. Then L2S will use those two columns only in the Where clause.

I've built an n-tier data access layer using L2S for a manufacturing operation, without using a single stored procedure. It can be done, and can be done quite well.

Randy

Related Solutions

Mysql – Multiple Updates in MySQL

Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.

Using your example:

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

Sql – Check if a row exists, otherwise insert

I assume a single row for each flight? If so:

IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)
BEGIN
    --UPDATE HERE
END
ELSE
BEGIN
   -- INSERT HERE
END

I assume what I said, as your way of doing things can overbook a flight, as it will insert a new row when there are 10 tickets max and you are booking 20.

Best Answer

Related Solutions

Mysql – Multiple Updates in MySQL

Sql – Check if a row exists, otherwise insert

Related Topic