public void Dapper_Execute_Test()
{
var count = repository.Execute("select count(id) from Test where id=2");
Assert.AreEqual <int>(count, 1);
}
static void Main(string[] args)
{
var data = new List <IntegrationTriggeredController>();
using (var ctx = new DapperRepositoryBase <IntegrationTriggeredController>())
{
data = ctx.GetAll("select baseTable.Table_Name, baseTable.Table_ID_Name, baseTable.Table_ID, baseTable.Operation from Integration_Triggered_Controller as baseTable");
}
using (var ctx = new DapperRepositoryBase <IntegrationTriggeredController>())
{
foreach (var item in data)
{
var query = "";
var ColumnNames = new Dictionary <string, string>();
var ColumnValues = new Dictionary <string, string>();
var InsertColumnsNames = "";
var InsertColumnValues = "";
if (item.Operation == "d")
{
//table_name = hangi tabloda değişiklik yapıldığı
//table_id_name = değişiklik yapılan tablonun pri key'i
//table_id = değişiklik yapılan tablodaki satırın id'i
query = $@"set IDENTITY_INSERT {item.Table_Name} ON delete from {item.Table_Name} where {item.Table_ID_Name}={item.Table_ID}";
}
else
{
query = $"set IDENTITY_INSERT {item.Table_Name} ON select COLUMN_NAME Columnname, data_type datatype from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='{item.Table_Name}'";
var ClmPrp = new List <ColumnProps>();
using (var prp = new DapperRepositoryBase <ColumnProps>())
{
ClmPrp = prp.GetAll(query);
ClmPrp.ForEach(k =>
{
ColumnNames.Add(k.ColumnName, k.DataType);
});
}
// buradan sonra işlem yapmak istediğim tabloya ait tüm bilgilere sahibim.
foreach (var col in ColumnNames)
{
InsertColumnsNames += col.Key + ",";
}
InsertColumnsNames = InsertColumnsNames.Remove(InsertColumnsNames.Length - 1, 1);
query = $"set IDENTITY_INSERT {item.Table_Name} ON select {InsertColumnsNames} from {item.Table_Name} where {item.Table_ID_Name}={item.Table_ID}";
using (var ado = new AdonetRepositoryBase())
{
using (var cmd = new SqlCommand(query, ado.sqlcon))
{
cmd.CommandType = System.Data.CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
foreach (var clm in ColumnNames)
{
if (clm.Value == "nvarchar" || clm.Value == "varchar" || clm.Value == "datetime")
{
if (clm.Value == "datetime")
{
if (DateTime.TryParse(reader[clm.Key].ToString(), out var dt))
{
var mnt = dt.Month.ToString().Length > 1 ? dt.Month.ToString() : "0" + dt.Month.ToString();
var dy = dt.Day.ToString().Length > 1 ? dt.Day.ToString() : "0" + dt.Day.ToString();
var dv = $"{dt.Year}-{mnt}-{dy} 00:00:00";
InsertColumnValues += "'" + dv + "',";
ColumnValues.Add(clm.Key, "'" + dv + "'");
}
else
{
InsertColumnValues += "null,";
ColumnValues.Add(clm.Key, "null");
}
}
else
{
if (!string.IsNullOrEmpty(reader[clm.Key].ToString()))
{
InsertColumnValues += "'" + reader[clm.Key] + "',";
ColumnValues.Add(clm.Key, "'" + reader[clm.Key] + "'");
}
else
{
InsertColumnValues += "null,";
ColumnValues.Add(clm.Key, "null");
}
}
}
else
{
if (string.IsNullOrEmpty(reader[clm.Key].ToString()))
{
InsertColumnValues += "null,";
ColumnValues.Add(clm.Key, "null");
}
else
{
InsertColumnValues += reader[clm.Key].ToString().Replace(",", ".") + ",";
ColumnValues.Add(clm.Key, reader[clm.Key].ToString().Replace(",", "."));
}
}
}
}
}
}
}
if (ColumnValues.Count > 0)
{
InsertColumnValues = InsertColumnValues.Remove(InsertColumnValues.Length - 1, 1);
if (item.Operation == "i")
{
//insert into visit (id, name, surname) values (5, 'kubra','asdf')
query = $@"set IDENTITY_INSERT {item.Table_Name} ON insert into {item.Table_Name} ({InsertColumnsNames}) values ({InsertColumnValues})";
}
else if (item.Operation == "u")
{
// update visit set id=5, name='kubra', surname='asdf' where id=10
var updateStr = "";
foreach (var updateData in ColumnValues)
{
if (updateData.Key != "ID" && updateData.Key != "id")
{
updateStr += updateData.Key + "=" + updateData.Value + ",";
}
}
updateStr = updateStr.Remove(updateStr.Length - 1, 1);
query = $@"set IDENTITY_INSERT {item.Table_Name} ON update {item.Table_Name} set {updateStr} where {item.Table_ID_Name}={item.Table_ID}";
}
}
}
if (!string.IsNullOrEmpty(query) && !query.Contains("select"))
{
using (var context = new DapperRepositoryBase <IntegrationTriggeredController>())
{
System.Console.WriteLine(query);
context.Execute(query, JupiterFramework.DataAccess.Dapper.Enums.DataSources.CRM);
context.ClearTriggerController("delete from Integration_Triggered_Controller");
}
}
}
}
System.Console.ReadLine();
}