private object executeQueryScalar(string cText, SqlConnection _sqlConnection) { try { if (_sqlConnection.State != ConnectionState.Open) { _sqlConnection.Open(); } } catch (Exception) { return(null); } SqlTransaction trans; trans = _sqlConnection.BeginTransaction(IsolationLevel.ReadUncommitted); using (SqlCommand sqlCommand = _sqlConnection.CreateCommand()) { sqlCommand.CommandText = cText; sqlCommand.CommandType = CommandType.Text; sqlCommand.Notification = null; sqlCommand.CommandTimeout = this.config.defaultSQLQueryTimeout; sqlCommand.Transaction = trans; try { return(sqlCommand.ExecuteScalar()); } catch (System.InvalidOperationException) { return(null); } catch (SqlException error) { if (error.Message.Contains("error: 19") || error.Message.Contains("error: 20") || error.Message.Contains("SHUTDOWN")) { /* A transport-level error has occurred when sending the request to the server. * (provider: Session Provider, error: 19 - Physical connection is not usable) */ } else { this.logger.write(ECPRERROR.SqlExceptionDetail(error), Logger.LOGLEVEL.ERROR); } } } return(null); }
private void receiveMessage(dynamic jsonData, ulong deliveryTag) { /* Receive all messages from ebridge and check if it have * data conflict before do update. * * for delete: * { * "type": "delete", * "source": "HR", * "key": "MRN", * "value": 100, * "uuid": "7b832c4b-e92a-4521-9c11-575a42119348" * } * * for update: * { * "type": "update", * "source": "HR", * "key": "MRN", * "value": 100, * "uuid": "7b832c4b-e92a-4521-9c11-575a42119348", * "last_seen": "07/12/2015 12:30", * "data": { * "first_name": "Foo" * } * } * * for insert * { * "type": "insert", * "source": "HR", * "uuid": "7b832c4b-e92a-4521-9c11-575a42119348", * "data": { * "first_name": "Foo", * "last_name": "Bar" * } * } * * for upsert: * { * "type": "upsert", * "source": "PRONOTES", * "key": "body", * "value": "%envoy_id: 123%", * "uuid": "7b832c4b-e92a-4521-9c11-575a42119348", * "last_seen": "07/12/2015 12:30", * "data": { * "body": "envoy_id: 123" * } * } * * Determine conflict with type = update || type = upsert: * - if the table have touchdate field then get the value * and compare it with last_seen value (datetime). * - if last_seen < touchdate then send an error message to * ecpr-cud queue with the same uuid: * * { * "type": "error", * "error": "conflict", * "uuid": "7b832c4b-e92a-4521-9c11-575a42119348" * } * * - if the table do not have touchdate or last_seen >= touchdate * then update the record. * * - if type == 'upsert' and cannot update then insert. * */ try { string msg_type = jsonData.type; string table_name = jsonData.source; string table_pkey = jsonData.key; string table_pval = jsonData.value; string table_last_seen = jsonData.last_seen; string table_uuid = jsonData.uuid; dynamic table_data = jsonData.data; if (!string.IsNullOrEmpty(msg_type) && !string.IsNullOrEmpty(table_name)) { table_name = table_name.ToUpper(); if (msg_type == "insert") { string qQuery = @" SET Context_Info 0x55555 INSERT INTO {0} ({1}) VALUES ({2}) "; string pKeys; string pVals; this.buildINSERTINTO(table_data, out pKeys, out pVals); qQuery = String.Format(qQuery, table_name, pKeys, pVals); this.executeNonQuery(qQuery); } else { if (string.IsNullOrEmpty(table_pkey) || string.IsNullOrEmpty(table_pval)) { syncError("Cannot UPDATE or DELETE because 'key' and 'value' not found or have empty value in the message", jsonData); } else { if (msg_type == "update" || msg_type == "upsert") { if (string.IsNullOrEmpty(table_last_seen)) { syncError("Cannot UPDATE because 'last_seen' not found or have empty value in the message", jsonData); } else { if (!this.isValidDateTimeFormat(table_last_seen)) { syncError(String.Format("Cannot UPDATE because value of 'last_seen' is not correct datetime format, the correct format is: {0}", this.dateTimeFormat), jsonData); } else { string qTQuery = "SELECT TOUCHDATE FROM {0} WHERE {1}"; if (table_pval.StartsWith("%") || table_pval.EndsWith("%")) { qTQuery += " LIKE "; } else { qTQuery += "="; } if (this.isNumeric(table_pval)) { qTQuery += "{2}"; } else { qTQuery += "'{2}'"; } using (SqlConnection _sqlConnection = new SqlConnection(this.config.cprDatabaseConnectionString)) { SqlDataReader sqlDataReader = this.executeQuery(String.Format(qTQuery, table_name, table_pkey, table_pval), _sqlConnection); bool canUpdate = true; bool canInsert = false; if (sqlDataReader != null) // meant touchdate is exists in the table { if (sqlDataReader.HasRows) // meant where key=value is existing { sqlDataReader.Read(); DateTime touchDate = sqlDataReader.GetDateTime(0); DateTime lastSeen = DateTime.ParseExact(table_last_seen, this.dateTimeFormat, CultureInfo.InvariantCulture); touchDate = DateTime.ParseExact(touchDate.ToString(this.dateTimeFormat), this.dateTimeFormat, CultureInfo.InvariantCulture); if (lastSeen < touchDate) // we have conflict { syncError(String.Format("Cannot UPDATE because have conflict TouchDate({0}) - LastSeen({1}).", touchDate.ToString(), lastSeen.ToString()), jsonData); Dictionary <string, dynamic> pMsg = new Dictionary <string, dynamic> { { "type", "error" }, { "error", "conflict" }, { "uuid", table_uuid }, { "source", table_name }, { "key", table_pkey }, { "value", jsonData.value } }; DataChangesCache.AddItem("DATA_CONFLICT:" + table_uuid, JsonConvert.SerializeObject(pMsg, Formatting.Indented)); canUpdate = false; } } else { canUpdate = false; if (msg_type == "upsert") { canInsert = true; // insert for upsert message } } sqlDataReader.Close(); } if (canUpdate) { string qQuery = @" SET Context_Info 0x55555 UPDATE {0} SET {1} WHERE {2}"; if (table_pval.StartsWith("%") || table_pval.EndsWith("%")) { qQuery += " LIKE "; } else { qQuery += "="; } if (this.isNumeric(table_pval)) { qQuery += "{3};"; } else { qQuery += "'{3}';"; } qQuery = String.Format(qQuery, table_name, buildSET(table_data), table_pkey, table_pval); if (msg_type == "upsert") { if (!this.executeNonQuery(qQuery)) { canInsert = true; } } else { this.executeNonQuery(qQuery); } } else { if (msg_type == "update") { syncError(String.Format("Cannot UPDATE because the value {0} of the key {1} does not exists in db", table_pval, table_pkey), jsonData); } } if (msg_type == "upsert") { if (canInsert) { qTQuery = @" SET Context_Info 0x55555 INSERT INTO {0} ({1}) VALUES ({2})"; string pKeys; string pVals; this.buildINSERTINTO(table_data, out pKeys, out pVals); qTQuery = String.Format(qTQuery, table_name, pKeys, pVals); this.executeNonQuery(qTQuery); } } } } } } else if (msg_type == "delete") { string qQuery = @" SET Context_Info 0x55555 DELETE FROM {0} WHERE {1}="; if (this.isNumeric(table_pval)) { qQuery += "{2};"; } else { qQuery += "'{2}';"; } this.executeNonQuery(String.Format(qQuery, table_name, table_pkey, table_pval)); } else { syncError(String.Format("Unknown type == {0} on table {1}", msg_type, table_name), jsonData); } } } } else { syncError("Cannot process the json message from ebridge-cud without 'source' and 'type'", jsonData); } } catch (SqlException error) { if (error.Message.Contains("error: 19") || error.Message.Contains("error: 20") || error.Message.Contains("ExecuteReader") || error.Message.Contains("SHUTDOWN")) { throw error; } else { syncError(ECPRERROR.SqlExceptionDetail(error), jsonData); } } catch (Exception error) { if (error.Message.Contains("error: 19") || error.Message.Contains("error: 20") || error.Message.Contains("ExecuteReader") || error.Message.Contains("SHUTDOWN")) { throw error; } else { syncError(error.Message, jsonData); } } }