Its fun to reminisce about how database access has evolved over the years. In the early days I used to write parameterized SQL statements directly in my code. Eventually that evolved into using stored procedures. Mainly because by using a stored procedure I could change the way data was gathered without recompiling my source code. Eventually, I abandoned stored procedures altogether when LINQ to SQL was released. Finally, if we fast forward to the current day, my preferred method of accessing data is with EF code first. EF Code first is great because I can spend even less time managing my database and more time solving business problems. With the level of abstraction getting higher and higher it makes you wonder if perhaps the next generation of developers will not even write T-SQL anymore. They will probably laugh at us old timers when we talk about stored procs and T-SQL.  Wait! Back up a minute! Just because we have abstracted ourselves from the database doesn’t mean t... [More]

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 hosting 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 r... [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]