// set
public void AddString(String key, String value)
{
String cmd = String.Format(@"INSERT INTO {0} VALUES ({2}, {1})", TableName, QCDBClient.EscapeString(value),
QCDBClient.EscapeString(key));
DB.ExecuteNonQuery(cmd);
}
// update
public void UpdateString(String key, String value)
{
String cmd = String.Format(@"UPDATE {0} SET value = {1} WHERE name = {2}", TableName, QCDBClient.EscapeString(value),
QCDBClient.EscapeString(key));
DB.ExecuteNonQuery(cmd);
}
public override bool HasNextEvent()
{
DateTime now = DateTime.Now;
String cmdText = String.Format(@"Select TOP 1 row_id FROM {0} WHERE status = {1} AND fire_time < {2} ",
TableName, QCDBClient.EscapeString(Status_Live),
QCDBClient.EscapeString(now.ToString()));
return(DB.ExecuteScalar(cmdText) != null);
}
public override void AppendLog(String level, String message)
{
String cmd = String.Format(@"INSERT INTO {0} VALUES ({1}, {2}, {3})",
TableName,
QCDBClient.EscapeString(level),
QCDBClient.EscapeString(DateTime.Now.ToString()),
QCDBClient.EscapeString(message.Substring(0, Math.Min(2000, message.Length))));
DB.ExecuteNonQuery(cmd);
System.Diagnostics.Debug.Print(DateTime.Now.ToShortTimeString() + @" [" + level + "]" + ": " + message);
}
protected static void InsertEventInTable(DateTime fireTime, QCEvent e, String tableName)
{
lock (InsertLock) {
String eventXML = e.ToSoapString();
String cmdText = String.Format(@"INSERT INTO {0} (fire_time, status, event_xml) VALUES ({1}, {2}, {3})",
tableName,
QCDBClient.EscapeString(fireTime.ToString()),
QCDBClient.EscapeString(Status_Live),
QCDBClient.EscapeString(eventXML));
SDB.ExecuteNonQuery(cmdText);
}
}
public String StoreBlob(String memo, byte[] blob)
{
var key = Guid.NewGuid().ToString();
var cmd = String.Format(@"INSERT INTO {0} ([key], memo, blob_value) values( {1}, {2}, @blob)",
TableName,
QCDBClient.EscapeString(key),
QCDBClient.EscapeString(memo)
);
var par = new SqlParameter("blob", blob);
var pars = new List <SqlParameter>(); pars.Add(par);
DB.ExecuteNonQuery(cmd, pars);
return(key);
}
public StreamingEventSource()
{
DB = InitDB();
Options = InitStreamingOptions();
DB.CreateTable(TableName, ValueDefs);
SqlConnection = DB.NewSqlConnection();
SqlConnection.Open();
// init the cursor and options
String dropCursorCmd = String.Format(@"DEALLOCATE {0};", CursorName);
String createCursorCmd = String.Format(@"DECLARE {0} CURSOR Dynamic Read_Only
FOR SELECT * FROM {1} where row_id>={2}
OPEN {0};", CursorName, TableName, Options.CurrentRow);
DB.ExecuteNonQuery(createCursorCmd, SqlConnection);
FetchNextCursorCmd = String.Format(@"FETCH NEXT FROM {0};", CursorName);
FetchPriorCursorCmd = String.Format(@"FETCH PRIOR FROM {0};", CursorName);
FetchScrollCursorCmd = String.Format(@"FETCH ABSOLUTE {0} FROM {1};", Options.CurrentRow, CursorName);
ValueNames = ValueDefs.ToList()
.Select(def => def.ColumnName).ToArray();
// skip to the unhappend event
//while (true)
//{
Object[] rs = NextRow();
if (rs != null)
{
if (IsValidRow(rs))
{
//back one
PriroRow();
//break;
}
//else continue; // visited rows
}
// else break;
//}
}
public override QCEvent NextEventImpl()
{
String fetchEventText = String.Format(@"SELECT TOP 1 * FROM {0} WHERE status = {1} AND fire_time < {2} ",
TableName,
QCDBClient.EscapeString(Status_Live),
QCDBClient.EscapeString(DateTime.Now.ToString()));
Object[] results = DB.ExecuteFirstRow(fetchEventText);
String rowIdText = results[0].ToString();
String fireTime = results[1].ToString();
String status = results[2].ToString();
String eventText = results[3].ToString();
String updateEventText = String.Format(@"UPDATE {0} SET status = {1} WHERE row_id = {2}",
TableName,
QCDBClient.EscapeString(Status_Dead),
// rowId is integer, don't need to be escaped
rowIdText);
DB.ExecuteNonQuery(updateEventText);
// de serelize
return(QCEvent.FromSoapString(eventText));
}
public byte[] FetchBlob(String key)
{
var row = DB.ExecuteFirstRow("SELECT blob_value from " + TableName + " WHERE [key] = " + QCDBClient.EscapeString(key));
return((byte[])row[0]);
}
protected BlobStore(QCDBClient db)
{
DB = db;
DB.CreateTable(TableName, ValueDefs);
}
public void InitTable()
{
DB = InitDB();
DB.CreateTable(TableName, ValueDefs);
}
// basic methods
// get
public String GetString(String key)
{
String value = null;
using (SqlConnection sqlConnection = DB.NewSqlConnection()) {
String cmdText = @"SELECT value FROM " + TableName + " WHERE name = " + QCDBClient.EscapeString(key);
SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
sqlConnection.Open();
Object r = sqlCommand.ExecuteScalar();
if (r != null)
{
value = r.ToString();
}
sqlConnection.Close();
}
return(value);
}
