Friday, December 18, 2009

Configuring ASP session state on SQL server

This is a brief to the point note on how to setup ASP session state to use SQL server to store session information.

There are 2 steps setting up the ASPState database and the configuration setting in your web.config.

For some reason you cannot run the sql file directly cant remember what the issue is, but you need to use ASP_RegSQl.exe from a commmand prompt. The script file and the aspregsql exe are located here C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727


1) Open a command propmt and locate the following path C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 based on your Os version and .Net version

2) Use the following statement

a. Using default ASPState database and Sql security

aspnet_regsql -S serverName -U UserName -P Password -ssadd -sstype p

b. Using default ASPState database and windows security

aspnet_regsql -S serverName -E -ssadd -sstype p

c. Using custom database and Sql security

aspnet_regsql -d TableName -S serverName -U UserName -P Password -ssadd -sstype c

t - Stores session data in the SQL Server tempdb database. This is the default. If you store session data in the tempdb database, the session data is lost if SQL Server is restarted.

p - Stores session data in the ASPState database instead of in the tempdb database.

c - Stores session data in a custom database. If you specify the c option, you must also include the name of the custom database using the -d option.

3) In your configuration file

a) Using default Sql security

<sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" sqlconnectionstring="Data Source=Server;User ID=UserID;Password=Password;" cookieless="false">

b) Using default windows security

<sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" sqlconnectionstring="Data Source=Server;Integrated-Security=SSPI;" cookieless="false">

c) Custom database name

<sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" sqlconnectionstring="Data Source=Server;Initial Catalog=tablename;User ID=UserID;Password=Password;" cookieless="false">

Using SQL server to store ASP Session info in a Web Farm

Things to watch for when using SQL to store the session info in a web farm scenario

1) The machine key between the servers needs to be the same as AspState Session info is encrypted using the machine key.

2) The application path to your websites on all machines needs to be consistent as well.

Other issues

Since the session information needs to be converted from memory to a more persist-able form such as text. Out of memory session stores serialize and deserialize the session, you need to ensure that all complex/custom objects that you wish to store in a session are serializable. If you come across any such object that you cannot serialize by using the serialization attribute you can always serialize it programatically

Wednesday, December 16, 2009

Code Management on a budget

Code management on a budget in a team development environment

If you've done any development in a team environment, I'm sure your aware of the pains associated with such development.

You spend a whole day debugging and fixing conflicts only to come in the next day and find your code broken again. You decide enough is enough and walk over to your teammate and ask him for an explanation. He shows you that his version compiles and runs just fine. You walk back to your terminal scratching your head thoroughly frustrated and spend the next six hours locating an environment configuration variable that has been breaking your code.

A code base can grow exponentially in a team environment, getting bloated with buggy code, conflicts, configuration issues, environment issues etc. A huge amount of time is wasted in debugging and resolving environment and configuration issues, increasing your projects time and reducing productive coding.

So what's the solution? While it is a complex problem with a number of parameters that could go wrong and no perfect solution. Implementing an efficient code management process that suits your team's development needs and environment may not be perfect but could still make things a little less frustrating and improve your overall efficiency.

Based on how your team operates you'll need to find the right balance between a technical or managerial solution. So below is a rough guide on what is available out there and by no means exhaustive.


Like they say the right tools get the job done. Here are a few tools that can aid in managing your code.

  1. Firstly you need some kind of a code repository(I prefer SVN). There are a host of other commercial and open source products to choose from. Visual studio, Telelogic Synergy, CVS etc
  2. You need a good diff and merge tool that can be used with the code repository (DiffMerge seems like a nice tool to do this, again a whole lot of them available.
  3. If your projects use a database then you should also get some database diff tools. I use SQLDiff.
  4. Re-factoring tools will help keep your code in good shape a nice add on to Visual studio is ReSharper 
  5. Logging is a really helpful feature to have in any project. Trying to debug a projects for every error that occurs in a team environment can make it extremely frustrating and wasteful activity. Logging can gives you the ease of locating the errors at a glance. Log4Net is an open source library that you can use. It allows you to set and change the severity level in your project and allows you to choose a logging medium like a flat file, event logs or a database.
  6. A continuous build machine can greatly help in team development, users have access to different builds. Each build can be configured to show the diff between a baseline(stable project) making it easy to compare broken versions of the build.

All the tools in the world cannot help without some degree of good process management. Like any other  management practice it requires planning, organizing, training and controlling to improve productivity.

  1. Come up with a plan on how to structure/organize, share and store your code base. Review this plan periodically and update it to suite your changing needs.
  2. Things to consider in your plan naming conventions, integration schedules/policies, consideration for environment differences, deployment practices,  release management.
  3. Documentation is key, convert your plan into policies,rules, guides for the team.  This will also ensure that information on your process is readily available to the team. 
  4. Train the team, everyone needs to be on the same wave length in order to be effective, so train everyone on the team.

Ive only outlined a few possibilities to better code management in a .Net team environment.  Get creative, use your imagination and get everyone involved to come up with the right tools and techniques for your team.

Friday, December 11, 2009

BCP for bulk inserts into SQL server (SQL 2005)

BCP is a powerful utility to bulk insert data into SQL server. You can use this from the command line thus can be incorporated into a batch file.

I used the format file approach to using BCP. You can generate the format file using the following command line
bcp [CatalogName].[Owner].[TableName] nul -m 10 -f [FormatFileName].fmt -S [ServerName] -T

Finally figured out how to remove the double quotes in a CSV data import file. You will need to change the BCP format file to achieve this. Below is the BCP format file required to do this.

1 SQLCHAR 0 0 "\"" 0 x Latin1_General_CI_AS
2 SQLCHAR 0 50 "\",\"" 1 column1 Latin1_General_CS_AS
3 SQLCHAR 0 4 "\",\"" 2 column2 Latin1_General_CS_AS
4 SQLCHAR 0 300 "\"\r\n" 3 column3 Latin1_General_CS_AS

So the trick is to add an extra fake first row in the BCP format file to strip the very first double quote in the data row of your CSV file. The fake column x has a max data size of 0.

Also you must not have spaces between the rows of the format file.

To execute the BCP use the following command line

bcp [CatalogName].[Owner].[TableName] in [ImportDataFileName].CSV -f [FormatFileName].fmt -S [ServerName] -U [UserName -P [Password]

Make sure paths to all your files are relative to where the BCP is located. BCP is usually found in the following location if you have SQL 2005 C:\Program Files\Microsoft SQL Server\90\Tools\Binn

Thursday, December 10, 2009

Batch files for automating data feeds

Today I had to come up with a system/process that could retrieve data from a web site after logging in, retrieve the data and then upload the data into a SQL database on a daily basis.

Instead of building a complex application that could do it all. I decided on writing a batch file that combined a custom command line download utility, an unzip utility and SQL BCP utility to download and update the data file.

This approach is quick and simple. Plus most routine data processing tasks require some amount of maintenance and updating from time to time. Using a batch file makes it relatively easy to access the process and manage any changes in the future.

However the interesting portion of the whole exercise was developing the custom download tool that could login to a website and download the required file/s. Its pretty simple actually what you need to do is simulate a form post using the Http Request class and stream the response to a file on your local server/machine.

While this is pretty simple it got me thinking about how you can start interfacing different web applications using this method. I'm sure most applications expose some kind of web service to do this but for those that don't and require some kind of authentication this approach opens up a host of possibilities.

Wednesday, December 09, 2009

Using a Mac Book Pro for .Net development

Most of my work involves .Net development so I needed a windows based laptop. I had narrowed down my choice to a Lenovo Thinkpad X series and Mac Book Pro simply because of their build quality, having a one year old running wild teaches you to appreciate some of the finer elements of a laptop I guess. However the Mac Book Pro offered so much more for about 500$ less.

I really like the magnetic power cord (forgetting that the power cord is still plugged to the wall problem has disappeared now) and illuminated keyboard, even the aluminum casing and illuminated apple logo makes it a real sexy beast. There still are some draw backs, you will need a copy of windows to start with and a Mouse! Even though the touch pad is click-able its rather awkward for windows use.

So come Thanks Giving sales and I just had to have one for 200$ less than the normal price.

Soon I got down to the fun part of installing windows vista on it. I must say that the install went pretty smoothly. But once I booted into Vista I found that most drivers were missing, I couldn't even get the networking to work :(

So whats the solution, just use the Leopard DVD that ships with the Mac book pro it has all the drivers in it. I had to do a bit of searching around before I figured it out. Here's a link on how to install Vista on your Mac Book Pro.

So far Ive installed VS2008 and SQL Express on it and I haven't come across anything weird yet but I haven't actually gotten any work done on it yet, I'm sure Ill get down to it sooner or later.

Between I have been having fun with Mac Os too. So its been fun trying to do somethings a little different. More on my dev experiences on the Mac Book Pro soon...

Update - 17 Dec 2009

So I finally got down to setting up an existing project on the Mac Book Pro. Ran the build and voila it works like a charm! I can see my web app in Firefox. I'm so excited now its just as fast as my windows based desktop with 4 gigs of DDR and Quad core Phenom processor. Id be interested to hear from anyone who has found any issues with this kind of dev setup.

Tuesday, December 08, 2009

I came across another OR mapping tool having used LLBGen and Nhibernate this was somehow quite refreshing Subsonic by Rob Conery. Its really simple to use at least version 3.0 and you can do all the development within the VS IDE which is awesome. Really like the .tt code generation templates that you can build from within Visual Studio