private void Form1_Load(object sender, EventArgs e)
{
//Подключение к базе данных
DbSync.SetDbContext(@"Data Source=DESKTOP-IL0K9BD\SQLEXPRESS;Initial Catalog=phonesdb;User ID=sa;Password=sa");
dbsync = new DbSync();
//Получить таблицу из бд
users = DbSync.db.GetTable <User>();
}
private void UploadData()
{
Mouse.OverrideCursor = Cursors.Wait;
DbSync.Upload();
Mouse.OverrideCursor = null;
MessageBox.Show("Uw data is geüpload!");
}
private void DownloadInspection()
{
if (DbSync.HasInspectionLocal)
{
var response = MessageBox.Show("Er is al een inspectie gedownload, als u doorgaat zal uw lokale inspectie worden verwijderd!", "Waarschuwing", MessageBoxButton.OKCancel, MessageBoxImage.Asterisk);
if (response != MessageBoxResult.OK)
{
return;
}
}
Mouse.OverrideCursor = Cursors.Wait;
if (DbSync.Download(SelectedInspection.ID))
{
MessageBox.Show("De inspectie is gedownload.");
}
Mouse.OverrideCursor = null;
}
public async Task SyncDb(SyncDbCfg cfg, ILogger log, IReadOnlyCollection <string> tables = null, bool fullLoad = false, int optionLimit = 0)
{
var toRun = cfg.Tables.Where(t => tables == null || !tables.Any() || tables.Contains(t.Name)).ToArray();
var dur = await toRun.BlockAction(async t => {
var tableLog = log.ForContext("Table", t.Name);
tableLog.Information("Table Sync {Table} - started", t.Name);
using var sourceConn = await Snowflake.OpenConnection(log);
using var destConn = await SqlServerCfg.OpenConnection(tableLog);
var sync = new DbSync(
new SnowflakeSourceDb((SnowflakeDbConnection)sourceConn.Conn, Snowflake.Cfg.Schema, tableLog),
new MsSqlDestDb(destConn, SqlServerCfg.DefaultSchema, t.FullTextCatalog, tableLog));
var res = await sync.UpdateTable(t, tableLog, fullLoad, optionLimit)
.WithWrappedException($"sync table '{t.Name}'", tableLog) // log the error and rethrow. Won't interrupt untill other sync have completed
.WithDuration();
tableLog.Information("Talbe sync {Table} - completed in {Duration}", t.Name, res.HumanizeShort());
}, cfg.Parallel).WithDuration();
log.Information("Completed loading {Tables} in {Duration}", toRun.Select(t => t.Name), dur.Duration.HumanizeShort());
}
public async Task SyncDb(ILogger log, CancellationToken cancel, IReadOnlyCollection <string> restrictTables = null, bool fullLoad = false,
int optionLimit = 0)
{
var tables = new[] {
new SyncTableCfg("video_stats",
new SyncColCfg("video_id")
{
Id = true, SqlType = "varchar(20)"
}, // this is a big table, so optimising for perf with types more than normal
new SyncColCfg("date")
{
Id = true, SqlType = "date"
},
new SyncColCfg("channel_id")
{
SqlType = "varchar(30)"
},
new SyncColCfg("views")
{
SqlType = "float"
},
new SyncColCfg("watch_hours")
{
SqlType = "float"
},
new SyncColCfg("tags")
{
SqlType = "varchar(1000)"
},
new SyncColCfg("lr")
{
SqlType = "varchar(10)"
}
)
{
Sql = @"select d.*, array_to_string(cl.tags, '|') as tags, cl.lr
from video_stats_daily d
inner join channel_latest cl on d.channel_id = cl.channel_id"
}
};
var toRun = tables.Where(t => restrictTables == null || !restrictTables.Any() || restrictTables.Contains(t.Name)).ToArray();
var dur = await toRun.BlockAction(async t => {
var tableLog = log.ForContext("Table", t.Name);
tableLog.Information("Table Sync {Table} - started", t.Name);
using var sourceConn = await Snowflake.OpenConnection(log);
using var destConn = await SqlServerCfg.OpenConnection(tableLog);
var sync = new DbSync(
new SnowflakeSourceDb(sourceConn.Conn, Snowflake.Cfg.Schema, WhCfg.Stage, WhCfg.FileMb.Megabytes(), tableLog),
new MsSqlDestDb(destConn.Conn, Version.Prerelease.HasValue() ? Version.Prerelease : SqlServerCfg.DefaultSchema, tableLog), Store);
var res = await sync.UpdateTable(t, tableLog, cancel, fullLoad, optionLimit)
.WithWrappedException($"sync table '{t.Name}'", tableLog) // log the error and rethrow. Won't interrupt untill other sync have completed
.WithDuration();
tableLog.Information("Table sync {Table} - completed in {Duration}", t.Name, res.HumanizeShort());
}, Cfg.Parallel).WithDuration();
log.Information("Completed loading {Tables} in {Duration}", toRun.Select(t => t.Name), dur.Duration.HumanizeShort());
}
private bool CanDownloadInspection()
{
return(SelectedInspection != null && Settings.IsOnlineMode && !DbSync.HasDownloadedInspection(SelectedInspection) && CanAnswerInspection());
}
public ActionResult Sync(DbSync _objDb)
{
obj = objFunc.GetDbSessionSrc();
if (obj == null)
{
return(Json("Please check your connection to source database!"));
}
objDa = new CommonDA(obj);
string where = string.Empty;
if (!string.IsNullOrEmpty(_objDb.WHERE))
{
where += " WHERE ";
where += _objDb.WHERE;
}
string sql = "SELECT " + _objDb.SOURCE_COLUMN + " FROM " + _objDb.DB_SOURCE + where;
DataTable dt = objDa.ExecuteSQL(sql, GlobalStruct.ActionType.ExecuteDataTable, ref errMsg).ToDataTable();
//string[] _columnNames = dt.Columns.Cast<DataColumn>()
// .Select(x => x.ColumnName)
// .ToArray();
if (!string.IsNullOrEmpty(errMsg))
{
return(Json(errMsg));
}
int rowCount = dt.Rows.Count;
if (rowCount == 0)
{
return(Json("Table does not contain any item!"));
}
sql = string.Empty;
string sqlSync = string.Empty;
string sqlInsert = string.Empty;
var colName = _objDb.SOURCE_COLUMN.Split(',');
sql += "DECLARE BLOBVAL RAW(32767); BEGIN ";
string blobVal = string.Empty;
foreach (DataRow item in dt.Rows)
{
sqlSync += " UPDATE " + _objDb.DB_DESTINATION + " SET ";
sqlInsert += "INSERT INTO " + _objDb.DB_DESTINATION + "(" + _objDb.SOURCE_COLUMN + ") SELECT ";
foreach (string cName in colName)
{
sqlSync += cName + " = " + GetColValueByName(item, cName, ref blobVal) + ",";
sqlInsert += GetColValueByName(item, cName, ref blobVal) + ",";
}
sqlSync = sqlSync.Remove(sqlSync.Length - 1);
sqlInsert = sqlInsert.Remove(sqlInsert.Length - 1);
sqlSync += " WHERE " + GetWhereByKey(item, _objDb.KEY.Split(',')) + " 1 = 1; ";
sqlInsert += " FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM " + _objDb.DB_DESTINATION + " WHERE " + GetWhereByKey(item, _objDb.KEY.Split(',')) + " 1 = 1); ";
sqlSync += sqlInsert;
sql += blobVal + sqlSync;
}
sql += " END;";
obj = objFunc.GetDbSessionDes();
if (obj == null)
{
return(Json("Please check your connection to destination database!"));
}
objDa = new CommonDA(obj);
objDa.ExecuteSQL(sql, GlobalStruct.ActionType.ExecuteNonQuery, ref errMsg);
if (!string.IsNullOrEmpty(errMsg))
{
return(Json(errMsg));
}
return(Json(rowCount.ToString() + " items has been synchronized!"));
}