LINQ to SQL Serialization

Problem:

• LINQ to SQL classes do not support binary serialization. Although I can manually modify them to meet my needs, it is a very time-consuming job, and difficult to maintain if the table is changed in the future.
• LINQ to SQL classes cannot be serialized by XML serializer if there is a relationship between tables.

These articles give us work around:
http://www.west-wind.com/WebLog/posts/147218.aspx
http://www.codeproject.com/KB/linq/linqsqlserialization.aspx

LINQ and Dynamic Query Expressions and SQL Injection

When you want to create LINQ with dynamic expression you will use concatenate string like:
var query = db.Customers.Where("City = '"+country+"' and Orders.Count >="+ordersCount)
.OrderBy("CompanyName")
.Select("new(CompanyName as Name, Phone)");

To prevent SQL injection you must use parameters:
var query = db.Customers.Where("City = @0 and Orders.Count >= @1", country, ordersCount)
.OrderBy("CompanyName")
.Select("new(CompanyName as Name, Phone)");

Enabling HTTP Compression (IIS 6.0)

1. In IIS Manager, double-click the local computer, right-click the Web Sites folder, and then click Properties.
2. Click theService tab, and in the HTTP compression section, select the Compress application files check box to enable compression for dynamic files.
3. Select the Compress static files check box to enable compression for static files.
4. In the Temporary directory box, type the path to a local directory or click Browse to locate a directory. Once a static file is compressed, it is cached in this temporary directory until it expires, or the content changes. The directory must be on the local drive of an NTFS–formatted partition. The directory cannot be compressed or shared, and the access control lists (ACLs) for the directory must include Full Control access to the identity of the application pool or to the IIS_WPG group.
5. Under Maximum temporary directory size, click a folder size option. If you specify a maximum size under Limited to (in megabytes) (the default setting is 95 MB), then when the limit is reached, IIS automatically cleans up the temporary directory by applying the "least recently used" rule.
6. Click Apply, and then click OK.
7. Created a web service extension for c:\windows\system32\inetsrv\gzip.dll
8. Used metabse explorer or Adsutil.vbs to:
   a) edit the compression levels (set to level 9 for both HcDynamicCompressionLevel and HcOnDemandCompLevel) for both deflate and gzip.
   b) ensure that gzip was used for static compression only (HcDoStaticCompression set to 1) c) set HcFileExtensions (static files) for both gzip and deflate to htm,html,txt,js
   d) set HcScriptFileExtensions for both gzip and deflate to asp,dll,exe,aspx,asbx,ashx,asmx,axd,js and css (I have css files that are dynamically generated)
   e) set HcCreateFlags to 0 for deflate and 1 for gzip
   f) Checked that, underneath parameters, HcDoDynamic, HcDoStatic and HcDoOnDemand are set to 1.
   g) Other settings: Buffer sizes are set to 8192, HcMinFileSizeForComp is 1.

Samples for Adsutil.vbs:

To enable HTTP Compression for Individual Sites and Site Elements:

1. Disable global static compression by executing the following command at a command prompt:
   adsutil set w3svc/filters/compression/parameters/HcDoStaticCompression false
2. Enable static compression at this directory by executing the following command at a command prompt:
   adsutil set w3svc/1/root/Home/StyleSheets/DoStaticCompression true
   

To disable static compression for only a single directory, first enable global static compression (if it is disabled) and then disable static compression at that directory. For example, to enable static compression for a directory at http://www.contoso.com/Home/StyleSheets, perform the following steps:

1. Disable global static compression by executing the following command at a command prompt:
   adsutil set w3svc/filters/compression/parameters/HcDoStaticCompression true
2. Enable static compression at this directory by executing the following command at a command prompt:
   adsutil set w3svc/1/root/Home/StyleSheets/DoStaticCompression false

Lightweight device-detection

It is a simple C# function which will detect most mobile browsers.


public static bool isMobileBrowser()
{
string user_agent;
int mobile_browser;
Match match;
user_agent = HttpContext.Current.Request.ServerVariables["HTTP_USER_AGENT"];
mobile_browser = 0;
string pattern = "(up.browserup.linkmmpsymbiansmartphonemidpwapphonewindows cepdamobileminipalm)";
Regex agentEx = new Regex(pattern, RegexOptions.IgnoreCase);
match = agentEx.Match(user_agent);
if(match.Success)mobile_browser = mobile_browser+1;
if(HttpContext.Current.Request.ServerVariables["HTTP_ACCEPT"].IndexOf("application/vnd.wap.xhtml+xml")>=0
HttpContext.Current.Request.ServerVariables["HTTP_X_PROFILE"]!=string.Empty
HttpContext.Current.Request.ServerVariables["HTTP_PROFILE"]!=string.Empty)
{
mobile_browser = mobile_browser+1;
}
string[] mobile_agents = {
"w3c ", "acs-", "alav", "alca", "amoi", "audi",
"avan", "benq", "bird", "blac", "blaz", "brew",
"cell", "cldc", "cmd-", "dang", "doco", "eric",
"hipt", "inno", "ipaq", "java", "jigs", "kddi",
"keji", "leno", "lg-c", "lg-d", "lg-g", "lge-",
"maui", "maxo", "midp", "mits", "mmef", "mobi",
"mot-", "moto", "mwbp", "nec-", "newt", "noki",
"oper", "palm", "pana", "pant", "phil", "play",
"port", "prox", "qwap", "sage", "sams", "sany",
"sch-", "sec-", "send", "seri", "sgh-", "shar",
"sie-", "siem", "smal", "smar", "sony", "sph-",
"symb", "t-mo", "teli", "tim-", "tosh", "tsm-",
"upg1", "upsi", "vk-v", "voda", "wap-", "wapa",
"wapi", "wapp", "wapr", "webc", "winw", "winw",
"xda", "xda-"
};
int size = mobile_agents.Length;
string mobile_ua = user_agent.Substring(0, 4).ToUpper();
for(int i=0; i {
if( mobile_agents[i] == mobile_ua)
{
mobile_browser = mobile_browser+1;
break;
}
}
if(mobile_browser>0)
return true;
return false;
}

SQL 2005 to delete lots of data in batches

If you want to delete lots of data (millions), Running one query causes the TransactionLog to grow with huge size.

You have 2 solutions for this problem:
1. If you can your data is offline:

• Copy the rows you want to keep to a temporary table
• Drop the original table
• Rename the temporary table to original name
• Reinstate any indexes

2. Second approach is to delete the rows in a loop. Delete a modest number each time round the loop. Keep looping until no more rows exist to delete.

• You will need to either backup the TLog frequently during this process (to stop it extending to a vast size), or change the RECOVERY MODEL to SIMPLE whilst this is running, and back to FULL again after it finished.
• If this batch works 24 hours/7 days you should also put a WAIT for 5 seconds or so inside the loop so that during each iteration other connected users get "their chance"

SQL Script to delete rows in loop:(needs local variables declaring)

SELECT @intRowsToDelete = COUNT(*) -- Number of rows to be deleted FROM dbo.MyTable WHERE ... MyDeleteCriteria ...
WHILE @intRowCount > 0 AND @intErrNo = 0 AND @intLoops > 0

BEGIN
SELECT @dtLoop = GetDate()
SELECT @strSQL =
SET ROWCOUNT @DEL_ROWCOUNT -- number of delete rows / iteration
DELETE D FROM dbo.MyTable AS D WHERE ... MyDeleteCriteria ...
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
SET ROWCOUNT 0 -- Reset batch size to "all"
SELECT @intRowsToDelete = @intRowsToDelete - @intRowCount,
@intLoops = @intLoops - 1
-- Debugging usage only:
PRINT 'Deleted: ' + CONVERT(varchar(20), @intRowCount)
+ ', Elapsed: ' + CONVERT(varchar(20), DATEDIFF(Second, @dtLoop, GetDate()))
+ ' seconds,' + ' remaining to delete=' + CONVERT(varchar(20), @intRowsToDelete)
+ ', Loops left=' + CONVERT(varchar(20), @intLoops)
WAITFOR DELAY '000:00:05' -- 5 seconds for other users to gain access
END

TFS Cache

If you changed schemas TFS client, will face many strange errors like {Item already exists, out of memory, cannot run query}
Cause:
Visual Studio and Team Explorer provide a caching mechanism which can get out of sync.
Solution:
For Windows Vista delete contents of this folder
C:\Users\{your account}\AppData\Local\Microsoft\Team Foundation\1.0\Cache
C:\Users\{your account}\AppData\Local\Microsoft\Team Foundation\2.0\Cache
For Windows Xp, 2003 delete contents of this folder
C:\Documents and Settings\{your account}\Local Settings\Application Data\Microsoft\Team Foundation\1.0\Cache
C:\Documents and Settings\{your account}\Local Settings\Application Data\Microsoft\Team Foundation\2.0\Cache

How do you delete a work item?

There is no permanent delete feature for Work Items in this version of the product (2005, 2008). Instead, you put the WI into a terminal state (closed, obsolete, etc.)


There is now a tool in codeplex for this, called TFS Power Pack:
KillBill - Stops a Team Build currently running on a build server.
WorkItem Terminator - Permanently deletes a work item from the TFS database.
http://www.codeplex.com/Wiki/View.aspx?ProjectName=TfsPowerPack

Or delete them via this SQL statement.

Declare @DELID int set @DELID = @WorkItemId

DELETE FROM [TfsWorkItemTracking].[dbo].[WorkItemLongTexts] WHERE ID = @DELID
DELETE FROM [TfsWorkItemTracking].[dbo].[WorkItemsAre] WHERE ID = @DELID
DELETE FROM [TfsWorkItemTracking].[dbo].[WorkItemsWere] WHERE ID = @DELID
DELETE FROM [TfsWorkItemTracking].[dbo].[WorkItemsLatest] WHERE ID = @DELID