static DataRow FindRowByKeys(DataTable dt, List <KeyValuePair <DataColumn, object> > vKey, tagUpdateEvent ue, out string filter)
{
DataRow[] rows = null;
filter = "";
foreach (var kv in vKey)
{
if (filter.Length > 0)
{
filter += " AND ";
}
filter += ("[" + kv.Key.ColumnName + "]=");
if (kv.Value is short || kv.Value is int || kv.Value is long || kv.Value is decimal || kv.Value is double)
{
filter += kv.Value.ToString();
}
else if (kv.Value is string)
{
filter += ("'" + kv.Value.ToString() + "'");
}
else
{
throw new Exception("Other key column not supported");
}
}
if (ue != tagUpdateEvent.ueInsert)
{
rows = dt.Select(filter);
}
if (rows != null && rows.Length == 1)
{
return(rows[0]);
}
else if (rows != null && rows.Length > 1)
{
throw new Exception("Multiple rows found beyond our expectation");
}
return(null);
}
private void Push_OnPublish(CClientSocket sender, CMessageSender messageSender, uint[] group, object msg)
{
//this event is fired from worker thread from socket pool thread
string message = "", filter = "";
List <KeyValuePair <DataColumn, object> > vKeyVal = new List <KeyValuePair <DataColumn, object> >();
object[] vData = (object[])msg;
//vData[0] == event type; vData[1] == host; vData[2] = user; vData[3] == db name; vData[4] == table name
tagUpdateEvent ue = (tagUpdateEvent)(int)vData[0];
string table_name = vData[3].ToString() + "." + vData[4].ToString();
lock (m_cs)
{
DataTable dt = m_ds.Tables[table_name];
if (dt == null)
{
return;
}
DataColumn[] keys = dt.PrimaryKey;
int cols = dt.Columns.Count;
switch (ue)
{
case tagUpdateEvent.ueUpdate:
{
foreach (DataColumn dc in keys)
{
KeyValuePair <DataColumn, object> kv = new KeyValuePair <DataColumn, object>(dc, vData[5 + 2 * dc.Ordinal]);
vKeyVal.Add(kv);
}
DataRow dr = FindRowByKeys(dt, vKeyVal, ue, out filter);
for (int n = 0; n < cols; ++n)
{
if (dt.Columns[n].ReadOnly)
{
continue;
}
object d = vData[5 + 2 * n + 1];
dr[n] = d;
}
message = "Table " + table_name + " updated for row (" + filter + ")";
}
break;
case tagUpdateEvent.ueDelete:
{
int index = 0;
foreach (DataColumn dc in keys)
{
KeyValuePair <DataColumn, object> kv = new KeyValuePair <DataColumn, object>(dc, vData[5 + index]);
vKeyVal.Add(kv);
++index;
}
dt.Rows.Remove(FindRowByKeys(dt, vKeyVal, ue, out filter));
message = "Table " + table_name + " deleted for row (" + filter + ")";
}
break;
case tagUpdateEvent.ueInsert:
{
foreach (DataColumn dc in keys)
{
KeyValuePair <DataColumn, object> kv = new KeyValuePair <DataColumn, object>(dc, vData[5 + dc.Ordinal]);
vKeyVal.Add(kv);
}
DataRow dr = FindRowByKeys(dt, vKeyVal, ue, out filter); //generate filter only
dr = dt.NewRow();
for (int n = 0; n < cols; ++n)
{
dr[n] = vData[5 + n];
}
dt.Rows.Add(dr);
message = "Table " + table_name + " inserted for row (" + filter + ")";
}
break;
default:
message = "Unknown DB message found"; //shouldn't come here
break;
}
BeginInvoke(m_thread_message, message, dt);
}
}
static void Main(string[] args)
{
Console.WriteLine("Remote host: ");
string host = Console.ReadLine();
CConnectionContext cc = new CConnectionContext(host, 20901, "sa", "Smash123");
#if DEBUG
using (CSocketPool <CSqlServer> spSql = new CSocketPool <CSqlServer>(true, 3600 * 1000))
#else
using (CSocketPool <CSqlServer> spSql = new CSocketPool <CSqlServer>())
#endif
{
if (!spSql.StartSocketPool(cc, 1))
{
Console.WriteLine("Failed in connecting to remote async sql server. Press any key to close the application ......");
Console.Read();
return;
}
CSqlServer sql = spSql.Seek();
//track all DML (DELETE, INSERT and UPDATE) events
sql.Socket.Push.OnPublish += (sender, messageSender, group, msg) => {
if (group[0] == DB_CONSTS.STREAMING_SQL_CHAT_GROUP_ID)
{
object[] vMsg = (object[])msg;
tagUpdateEvent ue = (tagUpdateEvent)(int)(vMsg[0]);
string server = (string)vMsg[1];
string user = (string)vMsg[2];
string database = (string)vMsg[3];
Console.WriteLine("DML event={0}, server={1}, database={2}, user={3}, table={4}", ue, server, database, user, vMsg[4].ToString());
}
};
List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> > ra = new List <KeyValuePair <CDBColumnInfoArray, CDBVariantArray> >();
//enable monitoring DML events through triggers by flag DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES
bool ok = sql.Open("", dr, DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES);
sql.WaitAll();
CAsyncDBHandler.DRowsetHeader rh = (h) => {
CDBColumnInfoArray v = h.ColumnInfo;
if (v.Count > 0)
{
Console.WriteLine("dbPath={0}, tablePath={1}", v[0].DBPath, v[0].TablePath);
ra.Add(new KeyValuePair <CDBColumnInfoArray, CDBVariantArray>(v, new CDBVariantArray()));
}
};
CAsyncDBHandler.DRows rows = (h, vData) => {
int endIndex = ra.Count - 1;
ra[endIndex].Value.AddRange(vData);
};
//bring all table data which have USqlStream trigger (usqlserver.USqlStream.PublishDMLEvent) with an empty sql input string when opening with the flag DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES
ok = sql.Execute("", er, rows, rh);
TestCreateTablesAndStoredProcedures(sql);
ok = sql.Execute("select * from SpatialTable", er, rows, rh);
ok = sql.Execute("delete from employee;delete from company;delete from test_rare1;delete from SpatialTable;INSERT INTO SpatialTable(mygeometry, mygeography)VALUES(geometry::STGeomFromText('LINESTRING(100 100,20 180,180 180)',0),geography::Point(47.6475,-122.1393,4326))", er);
ok = sql.Execute("INSERT INTO test_rare1(mybool,mymoney,myxml,myvariant,mydateimeoffset)values(1,23.45,'<sometest />', N'美国总统川普下个星期四','2017-05-02 00:00:00.0000000 -04:00');INSERT INTO test_rare1(mybool,mymoney,myvariant)values(0,1223.45,'This is a test for ASCII string inside sql_variant');INSERT INTO test_rare1(myvariant)values(283.45)", er);
TestPreparedStatements(sql);
TestPreparedStatements_2(sql);
InsertBLOBByPreparedStatement(sql);
CDBVariantArray vPData = new CDBVariantArray();
//first set
vPData.Add(0); //retval
vPData.Add(1);
vPData.Add(21.2);
vPData.Add(null);
//2nd set
vPData.Add(0); //retval
vPData.Add(2);
vPData.Add(11.42);
vPData.Add(null);
TestStoredProcedure(sql, ra, vPData);
sql.WaitAll();
vPData.Clear();
//first set
vPData.Add(-1); //return int
vPData.Add(1); //@testid
vPData.Add(DateTime.Now);
vPData.Add("<test_sqlserver />"); //@myxml
Guid guid = Guid.NewGuid();
vPData.Add(guid); //@tuuid
vPData.Add(true); //@myvar
//2nd set
vPData.Add(-2); //return int
vPData.Add(4); //@testid
vPData.Add(DateTime.Now);
vPData.Add("<test_sqlserver_again />"); //@myxml
Guid guid2 = Guid.NewGuid();
vPData.Add(guid2); //@tuuid
vPData.Add(false); //@myvar
TestStoredProcedure_2(sql, ra, vPData);
sql.WaitAll();
TestBatch(sql, ra);
CDBVariantArray vParam = new CDBVariantArray();
//first set
vParam.Add(1); //ID
vParam.Add(0); //retval
//last three data will be updated with outputs
vParam.Add(1); //input @p_company_id, output retval
vParam.Add(21.2); //input @p_sum_salary, output @p_sum_salary
vParam.Add(null); //output @p_last_dt
vParam.Add(2); //EMPLOYEEID
//2nd set
vParam.Add(2); //ID
vParam.Add(0); //retval
//last three data will be updated with outputs
vParam.Add(2); //input @p_company_id, output retval
vParam.Add(11.42); //input @p_sum_salary, output @p_sum_salary
vParam.Add(null); //output @p_last_dt
vParam.Add(3); //EMPLOYEEID
TestBatch2(sql, ra, vParam);
sql.WaitAll();
int index = 0;
Console.WriteLine("+++++ Start rowsets +++");
foreach (KeyValuePair <CDBColumnInfoArray, CDBVariantArray> it in ra)
{
Console.Write("Statement index = {0}", index);
if (it.Key.Count > 0)
{
Console.WriteLine(", rowset with columns = {0}, records = {1}.", it.Key.Count, it.Value.Count / it.Key.Count);
}
else
{
Console.WriteLine(", no rowset received.");
}
++index;
}
Console.WriteLine("+++++ End rowsets +++");
Console.WriteLine();
Console.WriteLine("Press any key to close the application ......");
Console.Read();
Console.WriteLine("Press any key to close the application ......");
Console.Read();
}
}
