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