require 'tiny_tds'
@client = TinyTds::Client.new username: 'sa', password: 'Yukon900',
    host: 'localhost', port: 1433
puts 'Connecting to SQL Server'

if @client.active? == true then puts 'Done' end

def execute(sql)
    @client.execute(sql).do
    true
end

# create database
puts "Dropping and creating database 'SampleDB'"
execute("DROP DATABASE IF EXISTS [SampleDB]; CREATE DATABASE [SampleDB];")

# create sample table with data
puts "Creating sample table with data"
puts ("USE SampleDB; CREATE TABLE Employees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50))
    INSERT INTO Employees (Name, Location) VALUES (N'Jared', N'Australia'),	(N'Nikita', N'India'), (N'Tom', N'Germany')")

# insert new employee
puts "Inserting new employee Jake into Employees table"
execute("INSERT INTO Employees (Name, Location) VALUES (N'Jake', N'United States')")


# update location for employee
puts "Updating Location for Nikita"
execute("UPDATE Employees SET Location = N'United States' WHERE NAME = N'Nikita'")

# delete employee
puts "Deleting employee Jared"
execute("DELETE FROM Employees WHERE NAME = N'Jared'")

# read all employees
puts "Reading data from table"
@client.execute("SELECT * FROM Employees").each do |row|
    puts row
end

puts "All done."

@client.close