public void CreateWriteCommand()
{
LinterDbCommand command = connection.CreateCommand();
command.Parameters.Add(":ID", ELinterDbType.Bigint);
command.Parameters.Add(":symbol", ELinterDbType.NChar, 510);
command.Parameters.Add(":time", ELinterDbType.Date);
command.Parameters.Add(":bid", ELinterDbType.Double);
command.Parameters.Add(":ask", ELinterDbType.Double);
command.Parameters.Add(":bidSize", ELinterDbType.Int);
command.Parameters.Add(":askSize", ELinterDbType.Int);
command.Parameters.Add(":provider", ELinterDbType.NChar, 510);
command.CommandType = CommandType.Text;
command.CommandText = String.Format(@"
Merge into {0}
Using (select :ID (bigint) as id1, :symbol (varchar(255)), :time (date), :bid (double), :ask (double), :bidSize (int), :askSize (int), :provider (varchar(255))) as src
On {0}.id=src.id1
WHEN MATCHED THEN
UPDATE SET {0}.id=:ID, {0}.Symbol=:symbol, {0}.Time=:time, {0}.Bid=:bid, {0}.Ask=:ask, {0}.BidSize=:bidSize, {0}.AskSize=:askSize, {0}.Provider=:provider
WHEN NOT MATCHED THEN
INSERT ({0}.id, {0}.Symbol, {0}.Time, {0}.Bid, {0}.Ask, {0}.BidSize, {0}.AskSize, {0}.Provider) VALUES (:ID, :symbol, :time, :bid, :ask, :bidSize, :askSize, :provider)",
name);
command.Prepare();
writeCommand = command;
}
public IEnumerable <KeyValuePair <long, Tick> > Read()
{
LinterDbCommand command = connection.CreateCommand();
command.CommandText = String.Format(
"SELECT * FROM {0} " +
"ORDER BY ID", name);
using (LinterDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
long key = reader.GetInt64(0);
Tick tick = new Tick();
tick.Symbol = reader.GetString(1);
tick.Timestamp = reader.GetDateTime(2);
tick.Bid = reader.GetDouble(3);
tick.Ask = reader.GetDouble(4);
tick.BidSize = reader.GetInt32(5);
tick.AskSize = reader.GetInt32(6);
tick.Provider = reader.GetString(7);
yield return(new KeyValuePair <long, Tick>(key, tick));
}
}
}
public int DropTable()
{
LinterDbCommand command = connection.CreateCommand();
command.CommandText = String.Format(
"DROP TABLE {0} ", name);
return(command.ExecuteNonQuery());
}
public void Delete(long from, long to)
{
LinterDbCommand command = connection.CreateCommand();
command.CommandText = String.Format(
"DELETE FROM {0} " +
"WHERE ID >= {1} AND ID <= {2}", name, from, to);
command.ExecuteNonQuery();
}
public void Delete(long key)
{
LinterDbCommand command = connection.CreateCommand();
command.CommandText = String.Format(
"DELETE FROM {0} " +
"WHERE ID = {1}", name, key);
command.ExecuteNonQuery();
}
public int CreateTable()
{
LinterDbCommand command = connection.CreateCommand();
command.CommandText = String.Format(
"CREATE OR REPLACE TABLE {0} (", name) +
"ID bigint primary key," +
"Symbol varchar(255)," +
"Time date," +
"Bid double," +
"Ask double," +
"BidSize int," +
"AskSize int," +
"Provider varchar(255))";
return(command.ExecuteNonQuery());
}
public Tick this[long key]
{
get
{
LinterDbCommand command = connection.CreateCommand();
command.CommandText = String.Format(
"SELECT * FROM {0} " +
"WHERE ID = {1}", name, key);
using (LinterDbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
Tick tick = new Tick();
tick.Symbol = reader.GetString(1);
tick.Timestamp = reader.GetDateTime(2);
tick.Bid = reader.GetDouble(3);
tick.Ask = reader.GetDouble(4);
tick.BidSize = reader.GetInt32(5);
tick.AskSize = reader.GetInt32(6);
tick.Provider = reader.GetString(7);
return(tick);
}
else
{
return(null);
}
}
}
set
{
lock (SyncRoot)
{
writeCommand.Parameters[0].Value = key;
writeCommand.Parameters[1].Value = value.Symbol;
writeCommand.Parameters[2].Value = value.Timestamp;
writeCommand.Parameters[3].Value = value.Bid;
writeCommand.Parameters[4].Value = value.Ask;
writeCommand.Parameters[5].Value = value.BidSize;
writeCommand.Parameters[6].Value = value.AskSize;
writeCommand.Parameters[7].Value = value.Provider;
writeCommand.ExecuteNonQuery();
}
}
}