Thursday, December 11, 2008

String Formatting in ASP.Net Code behind to improve readability

Quite often I write SOL procedures in the code behind as oppose to using a stored procedure. The main reason for this is if I want to generate the SOL statement on the fly based on the users input. Here is a handy tip for improving the readability of your SQL written inside the code behind page;

str_sql = String.Format(@"
SELECT tblProducts.ProductID, tblProducts.Title, SellCost, COALESCE(MediaID, 0) AS MediaID FROM tblProducts
LEFT JOIN tblRelatedMedia ON tblProducts.ProductID = tblRelatedMedia.ProductID AND tblRelatedMedia.Ordered = 1
LEFT JOIN tblProductGroups tblBrands ON tblProducts.BrandGroupID = tblBrands.GroupID
WHERE tblProducts.IsDeleted = 0 AND tblProducts.IsAvailable = 1
AND (tblProducts.ProductID IN (SELECT ProductID FROM tblRelatedGroups WHERE GroupID = {0}) OR
tblProducts.ProductID IN (SELECT ProductID FROM tblRelatedGroups WHERE GroupID IN (SELECT GroupID FROM tblProductGroups WHERE ParentGroupID = {0})))", group_id);

Basically using the string.format with the @ symbol enables me to lay the SQL out like I would if I was creating a stored procedure in SQL SERVER. This makes it much easier to debug any issues with the SQL generated.

No comments: