SQL server compact framework and batch query processing

Recently , I was playing around with my experimental OpenLinqToSql provider to create one tiny tool for Flickr using Linq.Flickr (coming soon) and I came to find out that for some reason the following query does not work in SQL server compact framework , though it is working fine with the SQL server main edition.

BEGIN

INSERT INTO PhotoUploadStatus
(PhotoPath,Synced,Action)VALUES('c:\images\FlickrLogo.GIF','False','add')

SELECT * FROM [PhotoUploadStatus] WHERE ID='1' 
AND PhotoPath='c:\images\FlickrLogo.GIF' 
AND Synced='False' AND Action='add'

END

If you have a close look, you will find that I have used BEGIN .. END block, which tells SQL server to run the two queries in a batch. SQL Server main does this pretty well, but the compact one will surely nod its head off. The reason, behind this, SQL CE  process query one after another , which means if you have three queries and if two of them fails, the third one will still run and vice-versa. In other words, it does not support Batch processing.

So, to make the query work in both editions , I have to change the query a bit like the following

INSERT INTO PhotoUploadStatus
(PhotoPath,Synced,Action)VALUES('c:\images\FlickrLogo.GIF','False','add');

SELECT * FROM [PhotoUploadStatus] WHERE ID='1'
 AND PhotoPath='c:\images\FlickrLogo.GIF' 
AND Synced='False' AND Action='add';

Note, there is no BEGIN .. END block and every statement ends with an ';'.

Thanks

kick it on DotNetKicks.com

No Comments