Connecting to SQL server from IronRuby

sql_server_data_reader.rb

# IronRuby script

require "rubygems"
require "system.data"
require "json"

module SqlServerDataReader
  # method to read data from a given source_database on a given source_server, based on the given query
  # it will yield records row by row so you can do something with the yield row in a block
  #
  #  record_read = read_data("hsi-billing","FlightLinkRPM","SELECT * FROM BillingRecord") do |row
  #  ...
  #  end
  #
  def read_data(source_server,source_database,query)
    connection_string = source_connection_string(source_server, source_database)
    connection = System::Data::SqlClient::SqlConnection.new(connection_string)
    command = System::Data::SqlClient::SqlCommand.new(query, connection)
    #puts "Opening SQL Server database connection..."
    connection.Open
    reader = command.ExecuteReader
    #puts "SQL command executed."
    records_read = 0
    begin
      fields = []
      for i in 1..(reader.FieldCount)
        fields.push reader.GetName(i-1).to_s
      end

      while reader.Read do
        record = {}

        fields.each_with_index do |field, i|
          record[field] = reader[i] unless reader.IsDBNull(i)
        end

        yield record
        records_read += 1
      end
    ensure
      if (reader != nil)
        reader.Close
      end
      connection.Close
    end
    records_read
  end

  def source_connection_string(server, database)
    "Data Source=#{server};Initial Catalog=#{database};Integrated Security=SSPI;"
  end
end