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.

Wednesday, December 10, 2008

Changing ownership of a table (object) in SQL Server

use dbname
EXEC sp_changeobjectowner 'oldowner.tblToChange', 'newowner'
Note: The default owner is dbo.

Tuesday, December 2, 2008

Making sure MasterPage CSS and JavaScript reference path are always correct

Depending on the folder, the relative path to CSS and JavaScript references may be different. We need toensure that regardless the path of the page that called the master page, the relative paths to the reference files are always correct. We also want the pages and master pages to display properly in Visual Studio and that the links are always relative.

Here is how to do it:

1. Make sure the head tag is runat=server
2. CSS should use a link tag with a relative path
3. JavaScript should use ResolveUrl built in Dot Net method to dynamically generate actual relative path

Here is the html source, assuming that the master page exists in ~/templates/ folder, the .js script exists in ~/scripts/ folder, the CSS stylesheets exists in ~/styles/ folder:

<head runat="server">
<title>Title of Page</title>
<!-- meta information goes here -->
<link href="../styles/layout.css" rel="stylesheet" type="text/css" />
<script type='text/javascript' src='<%= ResolveUrl("~/scripts/common.js") %>'>
</script>
</head>



Google AJAX APIs and Libraries

Google have started hosting always-up-to-date script libraries via their Google AJAX APIs - http://code.google.com/apis/ajax/

This means they are hosting versions of jQuery, MooTools, Scripaculous etc. which are really helpful for developing JavaScript at a fraction of the time and effort. It allows cool effects (transitions, moving parts and the like) and many scripts online utilize these.

The API also allows taking advantage of Google Maps & Visualizations, which I'll go into detail about on a later post. To utilize Google AJAX APIs:

<script src="https://www.google.com/jsapi"></script>

* I recommend always use https - to ensure no problems when the page calling it is SSL protected.

Then to load, for example, jQuery, do the following:


<script>

google.load("jquery", "1.2.6"); // Load jQuery

</script>

jQuery is a great, simple to use library that is used to produce amazing effects such as those seen in this tutorial: http://malsup.com/jquery/cycle/ (the .js used here is ridiculously easy to plug-in)