Updating XML fields using LINQ to SQL

There is an hard to avoid issue with LINQ to SQL. That is you cannot update data on XML data fields using LINQ to SQL. This is a work around so you can update data in XML data fields using LINQ to SQL.

• Open your DML file on Microsoft Visual Studio.


• Drag and drop the table(s) that have the XML data field(s). (This is a new instance let the original table to be there. For example if original table name is "Employee" this might be "Employee1".)


• Click on the data field name that has XML type data.


• On properties box change the Type under Code Generation to String.


• Now when ever you need to update the XML data, use the new table instance.

Please note when compiling you may get a warning saying two references for same table might to lead to a conflict. You can safely ignore it and do wise coding. :-)

Recommended Books

.NET/IIS URL Rewriting using URL Rewriter

You can easily rewrite URLs on .NET/IIS using free open source program called URL Rewriter.
If you don't know what is URL rewriting is, it is simply the mechanism which give out search engine friendly URL like example.com/products/psp instead of example.com/products.aspx?product=psp
Whats actually happening is, there is only products.aspx file exists and it takes query sting product.
But by using URL rewriting, it passes the psp as the query string to the products.aspx file.
So what user see is example.com/products/psp but (s)he is actually looking at the example.com/products.aspx?product=psp.

So Interested? ;-)
If so this is how it will be done,

• Goto URL Reriter home page and download the binary.
• On Microsoft Visual Studio add the reference to the Intelligencia.UrlRewriter dll.
• Open the web.config file and add following portions.

<configsections>
<section name="rewriter" requirepermission="false" type="Intelligencia.UrlRewriter.Configuration.RewriterConfigurationSectionHandler, Intelligencia.UrlRewriter" />
<configsections>

<system.web>
<httpmodules>
<add type="Intelligencia.UrlRewriter.RewriterHttpModule, Intelligencia.UrlRewriter" name="UrlRewriter" />
<httpmodules>
</system.web>

<rewriter>
<rewrite url="/products/(.+)" to="/products.aspx?tag=$1" />
</rewriter>

• Now if you test this on Visual Studio, you will see that URL rewriting is working.
• But, If you deploy this on ISS it will not work.
• The trick is,
• Right Click on the web site on ISS manager.
• Goto the "Home Directory" tab.
• Select configurations.
• Select .asax extension.
• Click "Edit" button.
• Copy the executable path and click the "Cancel" button.
• Now click on the "Insert" button.
• Paste the previously copied executable path on the text box.
• Make sure that "Verify that file exists" is unchecked.
• Click Ok
• Close the web site properties dialog.
• And thats it. ;-)

You can use reguler expressions, IF clause, UNLESS clause etc. URL handling. You can find out lot about it from URL Rewriter Refence.

Do the bulk updates using Microsoft SQL Server Correctly.

This is the scenario:
You have a table called tblItem with fields,
itemID, itemTitle, itemDescription, itemImage and itemPrice.
itemID is set as the primary key or it is the field that can be used to uniquely identify a given row.
And this table is populated with 1000 records.
Imaging that you have to change the price of about 700 items.
And your data entry team supplied you a Microsoft Excel spreadsheet with itemID and new itemPrice.
So you have to update the tblItem table, without effecting other 300 items.

Before you start updating:
Make sure that itemID is unique in tblItems and the spreadsheet, otherwise you will get an error.

How to do the update:

• Import data from the spreadsheet and create a new table called tblTemp
• The fields will be tempID and tempPrice
• Run this query:

UPDATE tblItem
SET itemPrice = (SELECT tempPrice FROM tblTemp WHERE tempID = itemID)
WHERE itemID IN (SELECT tempID FROM tblTemp);

With this query you can make sure that other records which aren't present in the spreadsheet will not get updated and only the records present in the spread sheet get update.

Words of caution:

• Don't forget to delete the temporary table.
• It is always a good idea to make backup.