LINQ (Language Integrated Query) to SQL is a great tool because it allows developers to concentrate on business problems instead of worrying about writing SQL. Unfortunately, generated code typically comes with a catch. My general rule of thumb is that "I never trust any tools that have a wizard or generate code". After all, every time you release code into production you are putting your reputation on the line. Therefore, don't you think it is important to know what your code is really doing under the covers? Since I started getting involved with Database Administration about 3 years ago I have become extremely conscious of the SQL that my code generates. After all, most database performance problems stem from the fact that developers test on empty databases and everything seems to work fine until millions of records trickle into the system. Then ugly problems like missing indexes, functions in the where clause and poorly written queries bubble to the surface. The occurrenc... [More]

A while ago, I wrote an article about how to use Ruby with SQL Server 2005. The post generated a lot of comments and most of them dealt with the fact that the libraries I used in the original tutorial are now either being deprecated or near impossible to find. In addition to that, most of my readers are .NET developers. So, I thought it would make sense to write an article about Ruby from the perspective of a .NET developer. Therefore, this tutorial not only teaches you how to use Ruby on Rails with SQL Server but it will also help you see similarities between the features in ASP.NET stack and Ruby on Rails framework where applicable. A Little Background Information Ruby originated in Japan in the mid 1990s and was initially developed by Yukihiro "Matz" Matsumoto. Ruby supports multiple programming paradigms, including functional, object oriented, imperative and reflective. It also has a dynamic type system and automatic memory management; it is therefore similar in varying... [More]

In my humble opinion, one of the best features of SQL 2008 is the MERGE command. In a nutshell, the MERGE statement allows you to insert, update, or delete data based on certain join conditions in a single statement. Traditionally this type of work was accomplished by creating a cursor, looping over each row and running some conditional logic to determine if you needed to insert, update or delete some rows. If you view the syntax for the MERGE command with SQL books online you may be a little overwhelmed. The 100+ line syntax sample is not easy on the eyes. Therefore I was afraid that some people may be discouraged from using it, and I felt obligated to give an example that is a little easier to understand. First we will start by creating two tables and mocking up some data to test the MERGE command with: 1: CREATE Table Planets ( 2: ID int not null, 3: Name varchar(25) not null, 4: Comments varchar(50) null 5: ) 6: GO ... [More]

Storing BLOB (Binary Large Objects) in a SQL database can be a very convenient way to tie documents with metadata. For example, if you are building a document management system it is very nice to be able to stuff a word document in a data row along with other information such as who created the document, when it was modified and etcetera. The first step in accomplishing this task is to define a database table. The only real trick is to use a image column for the datatype of the column storing the BLOB data. For example consider this table creation script: CREATE TABLE [dbo].[PurchaseOrderAttachment]( [ID] [int] IDENTITY(1,1) NOT NULL, [PurchaseOrder] [nvarchar](50) NOT NULL, [Data] [image] NOT NULL, [Filename] [nvarchar](100) NOT NULL ) To insert the data using C# you will need the following code (using LINQ) MyDataContext db = new MyDataContext(); PurchaseOrderAttachment a = new PurchaseOrderAttachment(); a.Filename = System.IO.Path.GetFileName(attachmen... [More]

Whenever I build an application that contains any kind of statistical data I commonly get the request to make all of the grids and/or tables to include a export to CSV capability. In my mind, there are two options to approach this functionality. Build a method that iterates over a control. For example in a grid you can iterate over the columns and rows to build the CSV file. Build a method that iterates over the data. In my opinion approach #2 is more flexible and lends itself better to code re-use. Tying export functionality to a specific control is really not a good idea unless you have a unique situation which requires it. In my MVC application I am using LINQ to SQL and I used the Repository pattern that you may have seen before if you have ever looked at the nerddinner.com source code. The majority of my data access code returns an IQueryable<T> result. Therefore my export function is an extension method on the IQueryable<T> type. In order to get the column... [More]