private (int, string, string) GetMultipleResult(NsReceivedQuery receivedQuery)
{
var commandText = receivedQuery.Query;
int count = 0;
List <Dictionary <string, object> > result = new List <Dictionary <string, object> >();
SqlConnection conn = new SqlConnection(sqlConnectionString);
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
var cols = new List <string>();
for (var j = 0; j < reader.FieldCount; j++)
{
cols.Add(reader.GetName(j));
}
while (reader.Read())
{
result.Add(SerializeRow(cols, reader));
count++;
}
}
}
return(count, String.Empty, JsonConvert.SerializeObject(result));
}
private Task <int> RunExecuteQueryAsync(NsReceivedQuery receivedQuery)
{
var tcs = new TaskCompletionSource <int>();
Task.Factory.StartNew(() =>
{
var watch = System.Diagnostics.Stopwatch.StartNew();
ExecuteQuery(receivedQuery,
(int affected, string errText) =>
{
watch.Stop();
if (affected >= 0)
{
SendAnswerSuccess(receivedQuery, affected, receivedQuery.ResultTable, (int)watch.ElapsedMilliseconds / 1000, String.Format("Success affected rows: {0}", affected.ToString()));
Logger.Log(String.Format("Sent answer to {0} query. Success affected rows: {1}", receivedQuery.ID, affected.ToString()));
tcs.SetResult(1);
}
else if (affected < 0)
{
SendAnswerError(receivedQuery, errText);
tcs.SetResult(-1);
}
});
});
return(tcs.Task);
}
public void Execute(byte[] data)
{
var json = Encoding.UTF8.GetString(data);
NsReceivedQuery receivedQuery = JsonConvert.DeserializeObject <NsReceivedQuery>(json);
Execute(receivedQuery);
}
private void SendAnswerError(NsReceivedQuery receivedQuery, string answerResult)
{
var answer = new NsQueryToSendAnswer()
{
ProcessId = receivedQuery.ProcessId,
ProcessElementId = receivedQuery.ProcessElementId,
ID = receivedQuery.ID,
Result = answerResult,
Status = "ERROR"
};
var message = JsonConvert.SerializeObject(answer);
SendAnswer(message);
}
public void Execute(NsReceivedQuery receivedQuery)
{
Logger.Log(String.Format("Incoming query params. prId:{0}, prElId:{1}, id:{2}, isNeedResult:{3} , query: {4}",
receivedQuery.ProcessId, receivedQuery.ProcessElementId, receivedQuery.ID, receivedQuery.IsNeedResult, receivedQuery.Query));
try
{
Task <int> task = RunExecuteQueryAsync(receivedQuery);
int result = task.Result;
}
catch (Exception e)
{
Logger.Log(String.Format("Error query: {0}", e.Message));
SendAnswerError(receivedQuery, e.Message);
}
}
private void SendAnswerSuccess(NsReceivedQuery receivedQuery, int affectedRows, string resultTable, int executionTime, string answerResult)
{
var answer = new NsQueryToSendAnswer()
{
ProcessId = receivedQuery.ProcessId,
ProcessElementId = receivedQuery.ProcessElementId,
ID = receivedQuery.ID,
Result = answerResult,
Status = "OK",
AffectedRows = affectedRows,
ResultTable = resultTable,
ExecutionTime = executionTime
};
var message = JsonConvert.SerializeObject(answer);
SendAnswer(message);
}
private void ExecuteQuery(NsReceivedQuery receivedQuery, Action <int, string> Callback)
{
int count;
string errText;
try
{
(count, errText) = ExecuteTspWithoutResult(receivedQuery.Query, receivedQuery.ID);
if (receivedQuery.IsNeedResult)
{
InsertResultToRedis(receivedQuery.ID, receivedQuery.ResultColumn, receivedQuery.ResultTable);
}
Callback(count, errText);
}
catch (Exception e)
{
Callback(-1, e.Message);
}
}
private void ExecuteQuery(NsReceivedQuery receivedQuery, Action <int, string, string> Callback)
{
int count;
string errText;
try
{
string queryResult = String.Empty;
if (receivedQuery.IsNeedResult && receivedQuery.QueryResultType == "redis")
{
(count, errText) = ExecuteTspWithoutResult(receivedQuery.Query, receivedQuery.ID);
InsertResultToRedis(receivedQuery);
}
else if (receivedQuery.IsNeedResult && receivedQuery.QueryResultType == "multiple")
{
(count, errText, queryResult) = GetMultipleResult(receivedQuery);
}
else if (receivedQuery.IsNeedResult && receivedQuery.QueryResultType == "one")
{
(count, errText, queryResult) = GetMultipleResult(receivedQuery);
}
else if (receivedQuery.IsNeedResult && receivedQuery.QueryResultType == "redis-contacts")
{
(count, errText) = ExecuteTspWithoutResult(receivedQuery.Query, receivedQuery.ID);
InsertResultStringToRedis(receivedQuery);
}
else
{
(count, errText) = ExecuteTspWithoutResult(receivedQuery.Query, receivedQuery.ID);
}
Callback(count, errText, queryResult);
}
catch (Exception e)
{
Callback(-1, e.Message, String.Empty);
}
}
private void InsertResultToRedis(NsReceivedQuery receivedQuery)
{
var commandText = (!receivedQuery.UseQueryInResult)
? $"SELECT {receivedQuery.ResultColumn} FROM {receivedQuery.ResultTable} WHERE QueryId = '{receivedQuery.ID}'"
: receivedQuery.Query;
SqlConnection conn = new SqlConnection(sqlConnectionString);
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
var cols = new List <string>();
for (var j = 0; j < reader.FieldCount; j++)
{
cols.Add(reader.GetName(j));
}
var client = NsRedisHelper.getRedisClient();
var key = $"nsResult_{receivedQuery.ID}";
client.Multi();
while (reader.Read())
{
string jsonStr = JsonConvert.SerializeObject(SerializeRow(cols, reader), Formatting.Indented);
client.LPush(key, jsonStr);
}
var span = new TimeSpan(24, 1, 1);
client.Expire(key, span);
client.Exec();
client.Dispose();
}
}
}
private void InsertResultStringToRedis(NsReceivedQuery receivedQuery)
{
var commandText = receivedQuery.Query;
SqlConnection conn = new SqlConnection(sqlConnectionString);
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
var cols = new List <string>();
for (var j = 0; j < reader.FieldCount; j++)
{
cols.Add(reader.GetName(j));
}
var client = NsRedisHelper.getRedisClient();
var key = receivedQuery.KeyMask.ToString();
if (reader.HasRows)
{
while (reader.Read())
{
Regex regex = new Regex(@"(\{.+?\})");
MatchCollection matches = regex.Matches(key);
string redisKey = key;
if (matches.Count != 0)
{
foreach (Match match in matches)
{
redisKey = redisKey.Replace(match.Value,
reader.GetValue(
reader.GetOrdinal(match.Value.Trim(new char[] { '{', '}' }))
).ToString()
);
}
}
string redisValue = "";
redisValue += "{";
for (int i = 0; i < cols.Count; i++)
{
string elementOfValue = '"' + cols[i] + '"' + ":" + '"' + reader.GetValue(i).ToString() + '"';
redisValue += elementOfValue;
if (i != cols.Count - 1)
{
redisValue += ",";
}
}
redisValue += "},";
string existingValue = client.Get(redisKey);
if (existingValue != null)
{
existingValue = existingValue.Trim(new char[] { '[', ']' });
redisValue += existingValue;
redisValue = "[" + redisValue + "]";
if (!client.Set(redisKey, redisValue))
{
throw new Exception($"Execution canceled. Redis cannot set {redisKey}{redisValue}.");
}
else
{
var span = new TimeSpan(24, 1, 1);
client.Expire(redisKey, span);
}
}
else
{
redisValue = "[" + redisValue + "]";
if (!client.Set(redisKey, redisValue))
{
throw new Exception($"Execution canceled. Redis cannot set {redisKey}{redisValue}.");
}
else
{
var span = new TimeSpan(24, 1, 1);
client.Expire(redisKey, span);
}
}
}
}
client.Dispose();
}
}
}
private (int, string, string) GetOneResult(NsReceivedQuery receivedQuery)
{
return(0, String.Empty, String.Empty);
}
