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(); } }