public List <JsonEntity> LoadJson(string configAddress)
{
List <JsonEntity> jsonEntities = new List <JsonEntity>();
using (StreamReader r = new StreamReader(configAddress))
{
try {
string json = r.ReadToEnd();
var convertedJson = JArray.Parse(json);
foreach (JObject token in convertedJson)
{
JsonEntity obj = new JsonEntity()
{
mssql = token["mssql"].ToString(),
mysql = token["mysql"].ToString(),
fields = JsonConvert.DeserializeObject <Dictionary <string, string> >(token["fields"].ToString())
};
jsonEntities.Add(obj);
}
} catch (Exception ex)
{
throw ex;
}
}
return(jsonEntities);
}
public void PushMsSqlToMySql(string connectionStringMySql,
JsonEntity jsonEntity, List <string> msSqlData)
{
string tablesMySqlString = string.Join(",", jsonEntity.fields.Values);
using (MySqlConnection dbConnection = new MySqlConnection(connectionStringMySql))
{
try
{
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
string rowsQuery = "";
for (int i = 0; i < msSqlData.Count; i++)
{
rowsQuery += String.Format(@"INSERT IGNORE INTO {0} ({1}) VALUES ({2});",
jsonEntity.mysql, tablesMySqlString, msSqlData[i]);
}
using (MySqlCommand command = new MySqlCommand(rowsQuery, dbConnection))
{
try
{
command.CommandTimeout = 3000;
command.ExecuteNonQuery();
}
catch (Exception exc)
{
Console.WriteLine(String.Format("Error. Message: {0}; Source: {1}", exc.Message, exc.Source));
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbConnection.Close();
}
}
}
public List <string> GetMsSqlData(string connectionStringMsSql,
JsonEntity jsonEntity, DateTime startDate, DateTime dueDate, int offset = 0, int limit = 100)
{
string tablesMsSqlString = string.Join(",", jsonEntity.fields.Keys);
string sqlQuery = String.Format(@"SELECT {0} FROM dbo.[{1}] WHERE (ModifiedOn BETWEEN '{2}' AND '{3}') ORDER BY ModifiedOn ASC OFFSET {4} ROWS FETCH NEXT {5} ROWS ONLY",
tablesMsSqlString, jsonEntity.mssql, startDate.ToString("yyyy-MM-dd H:mm:ss"),
dueDate.ToString("yyyy-MM-dd H:mm:ss"), offset, limit);
List <string> rows = new List <string>();
using (SqlConnection dbConnection = new SqlConnection(connectionStringMsSql))
{
try
{
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (SqlCommand command = new SqlCommand(sqlQuery, dbConnection))
{
command.CommandTimeout = 3000;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string str = "";
for (int i = 0; i < jsonEntity.fields.Count; i++)
{
if (reader.GetValue(i).ToString() != "")
{
if (reader.GetValue(i).GetType() == typeof(Guid))
{
str += "UuidToBin(\"" + reader.GetValue(i).ToString() + "\"),";
}
else if (reader.GetValue(i).GetType() == typeof(DateTime))
{
var date = reader.GetDateTime(i);
str += "\"" + date.ToString("yyyy-MM-dd H:mm:ss") + "\",";
}
else
{
str += "\"" + reader.GetValue(i).ToString().Replace("\"", "\"\"") + "\",";
}
}
else
{
str += "\"\",";
}
}
str = str.Substring(0, str.Length - 1);
rows.Add(str);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbConnection.Close();
}
}
return(rows);
}
public List <string> GetMySqlData(string connectionStringMySql,
JsonEntity jsonEntity, DateTime startDate, DateTime dueDate, int offset = 0, int limit = 100)
{
string tablesMySqlString = string.Join(",", jsonEntity.fields.Values);
string sqlQuery = String.Format(@"SELECT {0} FROM {1} WHERE (ModifiedOn BETWEEN '{2}' AND '{3}') ORDER BY ModifiedOn ASC LIMIT {4} OFFSET {5}",
tablesMySqlString, jsonEntity.mysql, startDate.ToString("yyyy-MM-dd H:mm:ss"),
dueDate.ToString("yyyy-MM-dd H:mm:ss"), limit, offset);
List <string> rows = new List <string>();
using (MySqlConnection dbConnection = new MySqlConnection(connectionStringMySql))
{
try
{
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (MySqlCommand command = new MySqlCommand(sqlQuery, dbConnection))
{
command.CommandTimeout = 3000;
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string str = "";
for (int i = 0; i < jsonEntity.fields.Count; i++)
{
if (reader.GetValue(i).ToString() != "")
{
if (reader.GetValue(i).GetType() == typeof(Byte[]))
{
Byte[] bytes = (byte[])reader.GetValue(i);
Guid guid = new Guid(bytes);
str += "'" + guid + "',";
}
else if (reader.GetValue(i).GetType() == typeof(decimal))
{
int value = (int)reader.GetDecimal(i);
str += "'" + value.ToString() + "',";
}
else if (reader.GetValue(i).GetType() == typeof(DateTime))
{
DateTime date = reader.GetDateTime(i);
str += "'" + date.ToString("yyyy-MM-dd H:mm:ss") + "',";
}
else
{
str += "'" + reader.GetValue(i).ToString().Replace("'", "''") + "',";
}
}
else if (reader.IsDBNull(i))
{
if (jsonEntity.fields.ElementAt(i).Value.Contains("Id"))
{
str += "NULL,";
}
else
{
str += "'',";
}
}
else
{
str += "'',";
}
}
str = str.Substring(0, str.Length - 1);
rows.Add(str);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbConnection.Close();
}
}
return(rows);
}