Touchy connection strings

My ASP.NET 2.0 / SQL Server 2005 application was working perfectly in my development environment but I experienced the following error message when testing deployment in a Windows 2003 virtual machine:

"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)".

My connection string was:

Server=localhost;Database=myDB;Integrated Security=SSPI;

Simply changing it to the following solved the issue:

Data Source=.;Initial Catalog=myDB;Integrated Security=True;

Considering both shared memory and TCP/IP where enabled in both environments, I can neither explain why the issue occurred or why the change above solved it. If someone can, please leave a comment.

Why I rarely use ADO.NET Datasets

Have you ever seen these great presentations which teach you how to build a master-details web page in a few minutes without writing a single line of code using a combination of DataSets, TableAdapters, ObjectDataSources, GridViews and FormViews? There is a great one at http://download.microsoft.com/download/8/3/6/836dd5f8-fa92-499f-8219-0d326f13bf18/hilo_data_final.wmv.

There are sound technical reasons not to use DataSets in ASP.NET applications due to their stateful nature. These reasons are explained by Frans Bouma in his blog at http://weblogs.asp.net/fbouma/archive/2003/05/13/6966.aspx. I have experienced other reasons not to use DataSets as a business layer.

The entity-relationship model (the way data is organised in database) rarely corresponds so perfectly (see the video mentioned above) to the way you are going to present it. You will need to go through various transformations, for example:

• You may have an invoice which is constituted of items and you need to present the invoice total which is actually the total of all item amounts. This total may also need to be presented in several currencies.


• You may have an N-N relationship between messages and contacts in your database, but you need to display a message with a To, Cc and Bcc fields which are separated lists of email addresses.

Data is rarely presented in the way it is stored in database and a business layer gives you the objects that you need between data and presentation, for example:

• You need to store UTC dates which will have to be converted using the time zone defined in your user’s profile;


• You need to store country codes which will have to be mapped to localized country names;


• You need to store a document status as a byte like 0, 1 and 2, which will have to be mapped to an enumerated value like “draft”, “approved” and “rejected”.

You also need to validate data before storing it in database. You can use validation controls in your presentation layer, but their features are limited and it is good practice to implement business rules in a business layer.

So what should we do?

1. Implement your data access layer (DAL) as stored procedures handling create, read, update and delete (CRUD) operations;


2. Implement a business logic layer (BLL) in C# or VB.NET calling stored procedures using data readers and commands, which will achieve much better performances.

Then when do you use datasets?

1. You only use datasets in rapid application development (RAD) scenarios, or when


2. You have no other choice, for example when a component that you need (otherwise you would spend hours reinventing the wheel) requires datasets.

Using Guids or Integers as database identifiers

Globally unique identifiers (Guids) are those strings that you find in COM clsid’s or in the windows registry and that look like: {ED256ABD-5BE7-4E46-BCDA-1E26B0364EBB}. You need 128 bits to store a Guid.

Integers are simply numbers that identify database rows in a table in an ascending order. They generally start at a seed of 1 and are automatically incremented by the database itself with an incremental step of 1, so you get 1, 2, 3, … Note that the database lets you specify the seed and the increment as required. You need 32 bits to store an integer.

The detractors of Guids claim that Guids require a lot more storage space, so they dramatically affect performances. If you want to store contact details with name, address, telephone and fax number in a table, you will easily get a dozen fields which will at least require 1000 bytes to store with a double-byte character set (required for working in any country). In this example, the difference on storage between using a Guid and an Integer as an identity is below 1%.

There are several advantages to using Guids:

• Guids are well-typed in Java and in the .Net framework and I personally like to have a specific type for identifiers in the business layer;


• They can be generated in the application, so you do not have to query the database after an insert to know which identifier has been generated for you;


• They are guaranteed to be globally unique by using a combination of the network card MAC address and time at instant of generation, which is a reasonable guarantee. This is required in replication scenarios and SQL Server replication relies on Guids anyway, so using Guids is more future proof.

Stored procedures or inline SQL

Using stored procedure versus using inline SQL has been a debate for long in the software development community. There are good articles on the subject, including:

http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
http://www.simple-talk.com/sql/t-sql-programming/to-sp-or-not-to-sp-in-sql-server/
http://www.simple-talk.com/sql/t-sql-programming/to-sp-or-not-to-sp-in-sql-server-an-argument-for-stored-procedures/

Generally the debate is about best practice, security, performance and maintainability with two camps:

• Rapid Application Developers prefer to rely on tools (RAD, ORM) that generate code, which all use inline SQL statements. This camp will claim that their tools enforce best practices, prevent SQL injection attacks and ensure maintainability while SQL execution plan caching makes performance equivalent to stored procedures.
• Developers who write their own business layer and data access layer code without support from such tools should opt for Stored Procedures which will make their application generally more secure, maintainable and performing better. I am on this side. Also note that having all your SQL code in stored procedures makes it easier to have it audited and reviewed by a database expert.

Attaching databases to SQL Server 2005

Often, sample projects contain a database file (MDF) but no log file (LDF).

In this case you need to attach the database running the following query against master in SQL Server Management Studio:

EXEC sp_attach_db @dbname = N'DBName',
@filename1 = N'D:\Working Folders\Project\App_Data\DBName.mdf'

This will not only attach the database but also create a log file.

Note: replace DBName and path as required.