ruby连接MSSQL2008数据库类整理

require 'win32ole'
class SqlServer
# This class manages database connection and queries
attr_accessor :connection,:data,:fields
attr_writer :username, :password
def initialize(host, username = 'sa', password='')
@connection = nil
@data = nil
@host = host
@username = username
@password = password
end
def open(database)
# Open ADO connection to the SQL Server database
connection_string = "DRIVER=SQL Server;"
connection_string << "SERVER=#{@host};"
connection_string << "UID=#{@username};"
connection_string << "PWD=#{@password};"
connection_string << "APP=Microsoft Office 2003;WSID=user3130.fpihz.com;"
connection_string<<"DATABASE=#{database}"
@connection = WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)
end
def query(sql)
# Create an instance of an ADO Recordset
recordset = WIN32OLE.new('ADODB.Recordset')
# Open the recordset, using an SQL statement and the
# existing ADO connection
recordset.Open(sql, @connection)
# Create and populate an array of field names
@fields = []
recordset.Fields.each do |field|
@fields << field.Name
end
begin
# Move to the first record/row, if any exist
recordset.MoveFirst
# Grab all records
@data = recordset.GetRows
rescue
@data = []
end
recordset.Close
# An ADO Recordset's GetRows method returns an array
# of columns, so we'll use the transpose method to
# convert it to an array of rows
@data = @data.transpose
end
def close
@connection.Close
end
end

#let me see
db = SqlServer.new('FPI301\MSSQLSERVER2008', 'sa', '123456')
db.open("sword_shizhan_ifs")
db.query('SELECT * from sword_area;')
puts field_names = db.fields
cust = db.data
puts cust.size
cust.each {|s|
print s,"\n"}
db.close