Mehfuz's WebLog

Live crazy, think different!

Sponsors

News

Passionate about cutting edge technologies and facinated by the modern web and phone revolution.Currently working at Telerik Corporation, the leading .net component vendor.
Follow me


Articles


Projects

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

Comments

No Comments