Back to -Tutorials -Sapphire (Ruby In Steel) Tutorials -Archives -Rails

Using SQL Server With A Rails Application

A step-by-step to SQL Server and SQL Server Express for Rails development

SQL Server has some great features for Ruby On Rails development, including tight integration with Visual Studio. But getting a Rails application to work with SQL Server can be tricky for anyone who’s never used the software before. In this tutorial, I’ll try to guide you through the creation of a very simple Rails application using Microsoft’s free SQL Server Express.

Download SQL Server Express

Note: This tutorial assumes that you have installed SQL Server or SQL Server Express, set up a user name and password and verified that the installation works correctly (for example, test it using the Microsoft sample applications). If you need help to use SQL Server, you should consult the compendious Microsoft documentation including the downloadable Online Books reference, the numerous tutorials movies and the Microsoft support forum. SapphireSteel Software cannot offer any support or guidance to using SQL Server or SQL Server Express.

In order to use SQL Server with Rails, you must first install the file, ADO.rb. We supply this file in the Ruby In Steel installation archive. You should copy ADO.rb into the \lib\ruby\site_ruby\1.8\DBD\ADO directory beneath your Ruby installation. For example, if Ruby is installed in C:\ruby you would need to copy ADO.rb into: ruby\lib\ruby\site_ruby\1.8\DBD\ADO\. You may need to create the \ADO directory. If you do not use SQL Server, this file is not required.

First start up SQL Server Management Studio Express (available from the SQL Server Express download site) and log in using Windows Authentication. This type of authentication uses your normal Window login details so you don’t need to enter a password at this stage. Click the Connect button to continue.

Load Ruby In Steel and start a new Ruby On Rails application:

Select the File menu then, New, Project, Rails Project.

Enter a name and (if necessary) a location for the project. e.g.

Name: Hello
Location: G:\RiSTests

Leave ‘Create directory for solution’ checked. Click OK.

The Create Rails Project Dialog appears.

Check off:
- SQLServer
- Development

Enter the database settings including a name for the database (let’s call it ‘helloworld’) and your SQL Server user name (in this case this will be your Windows logon name), your password (here your Windows logon password) and the SQL Server host name preceded by a dot (the host name is the server name with which you logged into the SQL Server Management Studio, normally in this form \). For example, if the Management Studio shows the database engine as MYCOMPUTER\SQLEXPRESS then the host name is SQLEXPRESS. In that case, specify the host name as .\SQLEXPRESS. The end result should look similar to the following, but with your own user name and password and (possibly) a different host name.

An information dialog will now appear giving details of the new project.

Click Proceed.

This will start a Rails script which will take a few moments to create the new application. At the end of this, a number of new files and folders should appear in the Solution Explorer.

Now add a controller. On the Ruby menu, click Generate. In the Generate dialog, click Controller and enter the value: SayHello index (use the same capitalization as shown). Click OK.

Again, a script runs and more files are added to the Solution Explorer.

Now create a model. On the Ruby menu, click Generate. In the Generate dialog, click Model and enter the value: SayHello (use the same capitalization as shown). Click OK.

Another script runs and the Solution Explorer is again updated.

Now we’ll connect to the database which was created when we entered details earlier in the Create Rails Project dialog. At that time, we entered the name, helloworld and the type, development. So the database is called helloworld_development. Switch to the Server Explorer (if this is not visible, load it from the View menu). Right-click the Data Connections branch and select Add Connection.

Setting Up A Default Connection
If you have not previously created a default connection, you will now see the following dialog… Here, you should select Microsoft SQL Server as the Data Source. If you wish to make this the default, ensure that ‘Always use this selection’ is checked. Then click Continue. In future, this dialog will not appear.

The Add Connection dialog now appears. Enter the server name which you are using (for example, .\SQLEXPRESS). Make sure Use Windows Authentication is selected. If the server name is correct, you should be able to select the database helloworld_development from a dropdown list under the label:Select or enter a database name. You may also want to click the Test Connection button. You should see a message stating Test connection succeeded. Click OK to close the Add Connection dialog.

You should now see the new connection (ending with the name helloworld_development) listed in the Server Explorer window.

Now we are ready to add a table called say_hellos to this database. In the table we’ll create a numeric id column which will be the primary key and a text greetings column. This is how it’s done: double-click the new database connection in the Server Explorer in order to expand its branches. Right-click the Tables branch and select Add New Table.

A table definition grid will appear. In this enter a id as the first column name, make its type smallint and do not allow Nulls. Then enter a second column name, greeting, with the type varchar(50) and again do not allow Nulls. Right-click the first column (id) and from the menu, select Set Primary Key. The data grid should appear like this…

Now select the second column (greeting and use the Column Properties page beneath the grid to set its default Value or Binding to Hello.

Press CTRL+S to save the table and give it the name say_hellos.

Using the Solution Explorer, find the say_hello_controller.rb file in the app/controllers folder and load it into the editor. Delete this:

def index

And add this in its place:

scaffold :say_hello

Save the file. The code should look like this…

Now we’re all ready to test it out. First start the server. For the sake of simplicity, we’ll assume that you are using WEBrick. Just click the Server icon on the Ruby menu.

WEBrick Settings
WEBrick is the default server for Rails projects in Ruby In Steel and it is set to run on port 3000. If you have been using another server, such as LightTPD, you can switch to WEBrick by selecting Tools, Options, Projects and Solutions, Ruby in Steel and selecting webbrick_service.rb as the Rails debug script and webbrick_server.bat as the Web sever script. If you need to run WEBrick on a non-standard port such as 3003 (for example, due to a conflict with some other installed server, this value can be set in the same dialog).

When the server starts, a window will pop up and a message should state that WEBrick has started on your selected port. If you see error messages, check that WEBrick is installed, that only one instance of WEBrick is running and that its correct port is specified.

Load up a web browser. This can either be a standalone browser or the one integrated into Visual Studio (View, Other Windows, Web Browser).

Into the browser bar, enter the following address (if you are using a non-standard port, say 3003, enter this number instead of 3000):


You should now see a simple ‘ready-baked’ application which will let you create and show new records by adding values for the greeting column in your say_hello table:

Try it out. Click the New say hello link and add a new record:

That covers all the basics of using SQL Server with Rails. You can, of course, create much more complex applications using the same techniques.

Bookmark and Share   Keywords:  rails  tutorial
© SapphireSteel Software 2014