Tuesday, November 29, 2011

Discarding changes in LINQ memory

I was looking for a solution just like what I used to do with ADO.NET back in the days when LINQ was non-existent.

My issue was that when I received an exception while inserting a row in SQL db (due to duplicate value already present), I wanted to discard that insertion from the memory and carry out with the next insertion. The problem was that the last insertion error was not removed from memory.

Many people suggested recreation of the DataContext object, but that was not an option for me. After some "Googling", I found the link below which was helpful:

http://graemehill.ca/discard-changes-in-linq-to-sql-datacontext

Though I did something like the one below in the catch block, where "spmap" was the new object created for insertion.
datacontext.tablename.DeleteOnSubmit(spmap);
Works for me! :)

Wednesday, November 2, 2011

Handling NULL values in SQL case statements while inserting or Updating Records

I am working on this massive data import project. Reminds me of those old days, when lots of stuff was done at the SQL level and and .NET took care of the UI Side. LINQ spoiled me to do all the SQL stuff using LINQ to SQL.

Anyway, working on SQL console is a lot of fun. Especially for big data imports and exports, that's the way to go. Feels like you are more so of a DBA!

NULL values are always tricky to work with. I came across this problem where I had to write a case statement where if the value is NULL, update the 'remarks' field, else append the value to the field:

Here's what I was doing:
update [tablename]
set remarks=case remarks when null then 'source:ms.csv'
else (remarks + '; source:ms.csv') end
where email='xyz@abc.com'
Apparently, the field was'nt updated since the remarks field was blank for that record. Looks strange!

After doing a little research and trying a few conditions, here's how it worked:
update [tablename]
set remarks=case isnull(remarks,'true') when 'true' then 'source:ms.csv'
else (remarks + ' source:ms.csv') end
where email='xyz@abc.com'

Note that the second parameter should be implicitly convertible to the first parameter of isnull.

UPDATE: April 2012
For numeric/decimal/int columns, this would be it:
update [tablename]
set totalcount=case isnull(totalcount,0) when 0 then 1
else (totalcount + 1) end
where email='xyz@abc.com'

Check the microsft documentation here for details.

Thats how it goes! You can take care of bigger chunks of data, but its the little things like those NULL values that drive you crazy!
There was an error in this gadget