Software Rockstar

SubSonic is an excellent open source DAL generation tool developed by Rob Conery that has been around for almost a year. In most cases SubSonic can just be added to your project, pointed to a databse, and you magically and immediately gain access to a rich and strongly typed object model that can be used to query and persist data to and from your relational data source.

SubSonic also has a cool feature called the REST handler that essentially allows one to use HTTP protocol as an interface to data stored in a back-end relational database. It is URI based and returns data back in XML format. This data can then be used by decoupled client applications as they see fit. If you have never looked at SubSonic, you owe it to yourself to checkout this very cool tool.

Recently Microsoft unveiled their plans to release similar features codenamed Astoria and Jasper with .NET framework 3.5 and Visual Studio 2008. The CTP of Astoria and Jasper are available for download from Microsoft.

Jasper is described by Microsoft as:

Project Jasper is geared towards iterative and agile development. You can
start interacting with the data in your database without having to create
mapping files or define classes. You can build user interfaces by naming
controls according to your model without worrying about binding code. Project
Jasper is also extensible, allowing you to provide your own business logic and
class model. Since Project Jasper is built on top of the ADO.NET Entity
Framework, it supports rich queries and complex mapping.

Pablo Castro, the mastermind behind Astoria describes it as:

The goal of Microsoft Codename Astoria is to enable applications to expose data as a data service that can be consumed by web clients within a corporate network and across the internet. The data service is reachable over HTTP, and URIs are used to identify the various pieces of information available through the service. Interactions with the data service happens in terms of HTTP verbs such as GET, POST, PUT and DELETE, and the data exchanged in those interactions is represented in simple formats such as XML and JSON.

The Astoria web site also includes sample online services that showcases how this new technology can be used. It also allows anyone with a Passport account to design and host their own experimental data services .

Since database are usually designed in development environments, migrating changes over to other environments (e.g. QA and production) is not a trivial task.  This is because SQL Server does not have any built-in tools to compare database schemas. 

There are quite a few third-party tools that would make your life easier, most notably, Red Gate’s SQL Compare (reasonably priced around $300).

At times, buying a commercial tool is not really an option, so one must resort to googling for free tools.  There are three such tools that I am aware of:

1. SQLDBDiff by SQLDBTools
   A very decent tool that comes in both freeware and shareware versions.  Freeware version is not badly crippled; only advanced features such as multi-database comparison, data content comparison, etc. are disabled.

2. Database Schema Comparison Utility
   This is a Code Project article that comes with C# source code of a schema comparison utility.  The utility itself is pretty bare-bone, but gets the job done.
3. StarInix Free Database Compare 2.0
   I have not used this tool, but from the advertised feature list, it looks pretty good.  Most notably, in addition to SQL Server, this tool works with Access and MySQL databases.

While SQL Server provides automatic data locking mechanism which is good enough for most situations, there are times when developers must provide locking hints in their queries for better performance. There are several locking hints that can be used with SQL Server, however, the two most common ones are as follows:

READ COMMITTED
This is the default locking strategy used by SQL Server.

PROS:
Guarantees that only committed data is read.

CONS:
If a writer has a lock in place, readers are blocked until the writer releases it’s lock, hence delaying readers.

EXAMPLE

-- Default behavior; no special hint required
SELECT EmployeeID, EmployeeName FROM Employee

NOLOCK or READ UNCOMMITTED
It is best used when approximations are acceptable, or for “dual-role” systems where database is responsible for many simultaneous reads and writes.

PROS:
Reads are blazing fast, since any exclusive locks are ignored. Also Shared Locks are not issued on rows read, so writers do not have to wait for read operations to complete.

CONS:
Data read may not be 100% accurate, since exclusive locks are ignored.

EXAMPLE

-- NOLOCK hint is used
SELECT EmployeeID, EmployeeName WITH (NOLOCK) FROM Employee

In order to retreive table schema programatically, we can use the GetSchemaTable method of the DataReader as follows:

using (OleDbConnection cn = new OleDbConnection())
{
    OleDbCommand cmd;
    DataTable schemaTable;
    OleDbDataReader reader;

    cn.ConnectionString = @"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(local);";
    cn.Open();

    cmd = new OleDbCommand("Employees", cn);
    cmd.CommandType = CommandType.TableDirect;

    using (reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
    {
        schemaTable = reader.GetSchemaTable();

        foreach (DataRow row in schemaTable.Rows)
        {
            foreach (DataColumn col in schemaTable.Columns)
                Console.WriteLine(col.ColumnName + " = " + row[col].ToString());

            Console.WriteLine();
        }

        reader.Close();
    }

    cn.Close();
}