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 respects to Python, Perl, Lisp, Dylan, and CLU. For more information take a look at the Wikipedia page.

One of the most popular platforms for developing applications is the Ruby on Rails (RoR) framework. RoR is a open-source web framework that really is geared toward programmer productivity. The Rails philosophy includes the following principles:

• DRY – “Don’t Repeat Yourself” – suggests that writing the same code over and over again is a bad thing.
• Convention Over Configuration – means that Rails makes assumptions about what you want to do and how you’re going to do it, rather than letting you tweak every little thing through endless configuration files.
• REST is the best pattern for web applications – organizing your application around resources and standard HTTP verbs is the fastest way to go.

Rails is organized around the MVC architecture. Also, included in the RoR stack is Active Record. Active Record provides us basic CRUD functionality for our data model. RoR has been around for quite a few years now. Although, I have no proof, I am willing to bet that Ruby was a major influence on Microsoft's release of ASP.NET MVC and LINQ. Obviously they noticed the Ruby language to some extent. Otherwise they would not have felt a need to add Ruby to the .NET stack (AKA IronRuby).

OK, lets get started with the tutorial. Before we can do anything we need to setup our environment.

Preparation Steps

In order to start you will need to make sure that you have Ruby installed. Since this is a tutorial for .NET developers I am going to assume that you are using a Windows OS. Installing Ruby on Windows is a breeze because the Ruby Gods have provided us with a  one-click installer.

After Ruby is installed you will still need to install Rails and the SQL Server Adapter. The best way to do this is to use the RubyGems. RubyGems is a packaging system that will make this process a breeze. When you request a package RubyGems will search the online repositories, find the files, determine if you need to any dependencies and finally install the package(s). So at this point, lets open a command prompt and type:

c:\> gem install rails
c:\> gem install activerecord-sqlserver-adapter

If you get an error message while running gems than you may want to temporarily disabled your windows firewall and make sure you have internet connectivity.

Voila, Rails and the SQL Server Adapter are installed. Now we can start coding.

Lets Code!

What's the first thing you do when you create a .Net App? Yes, you fire up Visual Studio and create a new project. Ruby is really no different. To start a new Rails application we first need to create a new project. In Ruby this is done by running the rails command. When you run the command a bunch of files and folders are created that will create the basic skeleton for your application. Yes, Just like when you start a new ASP.NET MVC project! From the syntax below you can see that I named my project "sql2008".

D:\code\ruby> rails sql2008

As you can see in the screenshot below, the rails command created an extensive file structure. For the purposes of this tutorial we will only dive into the app and the config folders. The app folder contains the models, views and controllers and the config folder contains configuration files:

Now, before we can go any further we will need a database. To make your life easy I have provided you with this SQL Script to create a new database named "RubyDemo" with a table named "Contact". This script was generated from a SQL 2008 database but should also work on SQL 2005 as well:

CREATE DATABASE [RubyDemo]
GO
USE [RubyDemo]
GO
CREATE TABLE [dbo].[Contacts](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Comment] [nvarchar](500) NULL,
 CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Now that the database is ready, we need to tell Ruby how to find it. This can be done my modifying the database.yml file in the /config directory. The file will have three different sections (development, test and production). For now, we will make all three sections the same:

development:
   adapter: sqlserver
   mode: odbc
   dsn: Driver={SQL Native Client};Server=.;Database=RubyDemo;Trusted_Connection=yes;

In the connection string above, I am using integrated security. If you want to use SQL authentication then just modify your dsn string to look like the following:

Driver={SQL Server Native Client};Server=.;Database=RubyDemo;Uid=myUsername;Pwd=myPassword;

Now that Ruby knows where your database is, you can generate the scaffolding. Scaffolding is the process where Rails builds classes based on the data model. Here is the command:

D:\Code\ruby\sql2008> ruby script/generate scaffold Contact FirstName:string LastName:string Comment:string

Notice that we have to pass the field names to the command as arguments and tell ruby what data type each field is. This can be a major pain if you have a large table with a lot of columns. This is one area where LINQ to SQL has an advantage. When building data models in LINQ you simply drag and drop tables from the Server Explorer onto the design surface and you are done. However, I think that since ActiveRecord is made to work all the major database vendors they did to get into the business of trying to read system catalogs in order to auto-generate the fields. Another thought is that there are many tables that have fields that are only used by background processing and really do not belong on the user interface, In those situations, it is better to "hand pick" the fields that you want to see in the model. Personally I would rather have a tool that auto-discovered the table and gave me that ability to remove the unwanted fields later on.  Perhaps we will see this functionality in a future release.

At this point you have almost everything you need to run your application. However, we do need to make one minor modification. Since, we are using an identity field for the ID field, we have to let ActiveRecord know about it. Otherwise when it generates the SQL for the CRUD operations it will try to set the ID field which will cause SQL to throw an error. To get around this problem we will tell ActiveRecord that the "ID" field is the primary key by modifying the code in the \app\models\contact.rb file:

class Contact < ActiveRecord::Base
  set_primary_key "ID"
end

Now you are ready to run your app. Open a command line and start your web server.

D:\code\ruby\sql2008> ruby script\server

The script\server command will fire up a web server just like the Cassini web server that you use in Visual Studio. The output from running the script\server command will tell you what port the server is running on. In my case, it was port 3000. So open a browser and navigate to http://localhost:<port>/Contacts.

Now, take a good look at the URL. When you generated the scaffolding for the Contact table, Ruby generated a controller, ( app\controllers\contacts_controller.rb) views (app\views\contacts\*.html.erb) and a model (\app\models\contact.rb) for you. The URL is the same name as the table you created the scaffolding for with an "s" added to it. Rails is very heavily bound to naming conventions, its part of that whole Convention over Configuration mentality. If you stick with the predefined naming conventions things will seem to just work. If you shy away from the assumed naming conventions, like trying to retrofit a Rails front-end onto a legacy database then you will have to do some extra work to let Rails know about these anomalies.

Anyways, if everything worked properly you should see the following page in your browser:

Click on the New Contact link and fill out the form. When you click the Create button, Active Record will generate the insert statement and execute it on your SQL Server database.

Yes, the generated HTML is a bit primitive looking but the basic concepts are all there. You created a completely functional application in about 10 minutes or less. Now obviously, this is a very simple application but I believe it does a good job at showing the appeal of Ruby or Rails and why Microsoft considers it a serious competitor in the programming arena.