public static TableFixedData FromTableData(ITableData tableData)
{
var tableFixedData = new TableFixedData();
var attributesNo = tableData.Attributes.Count();
var rowsNumber = tableData.Count;
tableFixedData._data = new object[rowsNumber, attributesNo];
var index = 0;
var columns = new Dictionary<string, int>();
foreach (var attribute in tableData.Attributes)
{
columns[attribute] = index;
if (attribute == TableData.ClassAttributeName)
{
tableFixedData._classIndex = index;
}
index++;
}
tableFixedData.Attributes = new string[columns.Count];
foreach (var item in columns)
{
tableFixedData.Attributes[item.Value] = item.Key;
}
var classes = new Dictionary<string, int>();
var currentClassesIndex = 0;
for (index = 0; index < rowsNumber; index++)
{
for (int columnIndex = 0; columnIndex < tableFixedData.Attributes.Length; columnIndex++)
{
var currentValue = tableData[index][tableFixedData.Attributes[columnIndex]];
var attribute = tableFixedData.Attributes[columnIndex];
if (attribute == TableData.ClassAttributeName)
{
if (!classes.ContainsKey((string)currentValue))
{
classes.Add((string)currentValue, currentClassesIndex);
currentClassesIndex++;
}
currentValue = classes[(string)currentValue];
}
tableFixedData._data[index, columnIndex] = currentValue;
}
}
tableFixedData.ClassesValue = new string[classes.Count];
foreach (var item in classes)
{
tableFixedData.ClassesValue[item.Value] = item.Key;
}
return tableFixedData;
}
public ConditionalTree BuildConditionalTree(ITableData data, bool optimized = false)
{
if (optimized)
{
var ret = new C45AlgorithmDataOptimized(TableFixedData.FromTableData(data));
return ret.BuildConditionalTree();
}
return new C45AlgorithmData().BuildConditionalTree(data);
}
public override void BulkInsert(ITableData data, string tableName)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(DbConnection, SqlBulkCopyOptions.TableLock, null))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = tableName;
foreach (IColumnMapping colMap in data.ColumnMapping)
{
bulkCopy.ColumnMappings.Add(colMap.SourceColumn, colMap.DataSetColumn);
}
bulkCopy.WriteToServer(data);
}
}
private static DataTable CreateResultTable(IPageData pageData, DataSet ds,
DataTable table, ITableData tableData)
{
DataTable resultTable = new DataTable(table.TableName);
var displayMap = new Dictionary <string, Tuple <Tk5FieldInfoEx, IDisplay> >();
DataColumnCollection columns = resultTable.Columns;
foreach (DataColumn col in table.Columns)
{
columns.Add(col.ColumnName);
}
foreach (var field in tableData.HiddenList)
{
displayMap.Add(field.NickName, Tuple.Create(field, GetDisplay(field, pageData)));
}
foreach (var field in tableData.DataList)
{
displayMap.Add(field.NickName, Tuple.Create(field, GetDisplay(field, pageData)));
}
foreach (DataRow row in table.Rows)
{
DataRow newRow = resultTable.NewRow();
DataRowFieldValueProvider provider = new DataRowFieldValueProvider(row, ds);
newRow.BeginEdit();
try
{
Tuple <Tk5FieldInfoEx, IDisplay> display;
foreach (DataColumn col in table.Columns)
{
string fieldName = col.ColumnName;
if (displayMap.TryGetValue(fieldName, out display))
{
newRow[fieldName] = display.Item2.DisplayValue(provider[fieldName],
display.Item1, provider);
}
else
{
newRow[fieldName] = row[col];
}
}
}
finally
{
newRow.EndEdit();
}
resultTable.Rows.Add(newRow);
}
return(resultTable);
}
public void BulkUpdate(ITableData data, ICollection <string> setColumnNames, ICollection <string> joinColumnNames)
{
if (data.ColumnMapping?.Count == 0)
{
throw new ETLBoxException("A mapping between the columns in your destination table " +
"and the properties in your source data could not be automatically retrieved. There were no matching entries found.");
}
LogErrorsAndClose(conn => {
LogDebug("{action}: Sql bulk update operation.", "START");
conn.BulkUpdate(data, setColumnNames, joinColumnNames);
RowsAffected = data.RecordsAffected;
LogDebug("{action}: Sql bulk update operation completed - affected records {rowsAffected}", "END", RowsAffected);
});
}
private void OnSelected(ListUpgrade type)
{
selectedType = type;
refType = SyncData.CurrentBaseUpgrade[type];
ITable dbTable = dBReference[selectedType];
SQLiteTable_TrainningCost costTable = dBReference[DBType.TrainningCost] as SQLiteTable_TrainningCost;
refTypeTraining = dbTable[SyncData.CurrentBaseUpgrade[selectedType].Level - 1];
refCostInfo = costTable[selectedType];
CurrentSelect.Placeholder.text = selectedType.ToString().InsertSpace();
}
private void AddNonNullValue(ITableData data, List <string> values, string destColumnName, int colIndex)
{
if (UseParameterQuery)
{
values.Add(CreateParameterWithValue(data.GetValue(colIndex)));
}
else
{
string value = data.GetString(colIndex).Replace("'", "''");
string valueSql = IsAccessDatabase ? $"'{value}' AS {destColumnName}"
: $"'{value}'";
values.Add(valueSql);
}
}
public void Equals_NullOther_ReturnsFalse()
{
// Arrange
var source = new Entity {
Id = "test", Version = new byte[] { 0x02 }, UpdatedAt = DateTimeOffset.Now
};
ITableData other = null;
// Act
var actual = source.Equals(other);
// Assert
Assert.False(actual);
}
public override void BulkInsert(ITableData data, string tableName)
{
BulkInsertSql <MySqlParameter> bulkInsert = new BulkInsertSql <MySqlParameter>()
{
UseParameterQuery = true,
ConnectionType = ConnectionManagerType.MySql
};
string sql = bulkInsert.CreateBulkInsertStatement(data, tableName);
var cmd = DbConnection.CreateCommand();
cmd.Parameters.AddRange(bulkInsert.Parameters.ToArray());
cmd.CommandText = sql;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
public override void BulkInsert(ITableData data, string tableName)
{
BulkInsertSql bulkInsert = new BulkInsertSql()
{
IsAccessDatabase = true,
AccessDummyTableName = DummyTableName,
UseParameterQuery = true
};
string sql = bulkInsert.CreateBulkInsertStatement(data, tableName);
var cmd = DbConnection.CreateCommand();
cmd.Parameters.AddRange(bulkInsert.Parameters.ToArray());
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
protected override void DoBulkInsert(ITableData data)
{
if (ModifyDBSettings)
{
try
{
string dbName = this.DbConnection.Database;
PageVerify = this.ExecuteScalar($"SELECT page_verify_option_desc FROM sys.databases WHERE NAME = '{dbName}'").ToString();
RecoveryModel = this.ExecuteScalar($"SELECT recovery_model_desc FROM sys.databases WHERE NAME = '{dbName}'").ToString();
this.ExecuteNonQuery($@"USE master");
this.ExecuteNonQuery($@"ALTER DATABASE [{dbName}] SET PAGE_VERIFY NONE;");
this.ExecuteNonQuery($@"ALTER DATABASE [{dbName}] SET RECOVERY BULK_LOGGED");
this.ExecuteNonQuery($@"USE [{dbName}]");
}
catch
{
ModifyDBSettings = false;
}
}
try
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(DbConnection, SqlBulkCopyOptions.TableLock, null))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = data.Definition.Name;
foreach (IColumnMapping colMap in data.ColumnMapping)
{
bulkCopy.ColumnMappings.Add(colMap.SourceColumn, colMap.DataSetColumn);
}
bulkCopy.WriteToServer(data);
}
}
finally
{
if (ModifyDBSettings)
{
try
{
string dbName = this.DbConnection.Database;
this.ExecuteNonQuery($@"USE master");
this.ExecuteNonQuery($@"ALTER DATABASE [{dbName}] SET PAGE_VERIFY {PageVerify};");
this.ExecuteNonQuery($@"ALTER DATABASE [{dbName}] SET RECOVERY {RecoveryModel}");
this.ExecuteNonQuery($@"USE [{dbName}]");
}
catch { }
}
}
}
/// <summary>
/// Adds the appropriate headers to the provided header dictionary, based on the
/// system properties within the entity.
/// </summary>
/// <param name="headers">The <see cref="IHeaderDictionary"/> to adjust.</param>
/// <param name="entity">The entity to use as source for the headers.</param>
internal static void AddFromEntity(this IHeaderDictionary headers, ITableData entity)
{
if (entity != null)
{
if (entity.HasValidVersion())
{
headers.Remove(HeaderNames.ETag);
headers.Add(HeaderNames.ETag, entity.GetETag());
}
if (entity.UpdatedAt != default)
{
headers.Remove(HeaderNames.LastModified);
headers.Add(HeaderNames.LastModified, entity.UpdatedAt.ToString(DateTimeFormatInfo.InvariantInfo.RFC1123Pattern, CultureInfo.InvariantCulture));
}
}
}
private void ReadDataAndCreateQuery(ITableData data)
{
while (data.Read())
{
List<string> values = new List<string>();
foreach (string destColumnName in DestColumnNames)
{
int colIndex = data.GetOrdinal(destColumnName);
if (data.IsDBNull(colIndex))
AddNullValue(values, destColumnName);
else
AddNonNullValue(data, values, destColumnName, colIndex);
}
AppendValueListSql(values, data.NextResult());
}
}
public override void BulkUpdate(ITableData data, string tableName, List <string> keys, List <string> updateFields)
{
using (var conn = DbConnection)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
StringBuilder columns = new StringBuilder();
List <string> cols = new List <string>();
data.needIdentityColumn = true;
keys.ForEach(k => { if (updateFields.Contains(k))
{
updateFields.Remove(k);
}
});
var columnTypes = data.ColumnTypes;
foreach (IColumnMapping colMap in data.ColumnMapping)
{
if (columns.Length > 0)
{
columns.Append(", ");
}
columns.Append($"{colMap.SourceColumn} {(columnTypes[colMap.SourceColumn] == "uniqueidentifier" ? "varchar(50)" : ((columnTypes[colMap.SourceColumn] == "varchar" || columnTypes[colMap.SourceColumn] == "nvarchar") ? (columnTypes[colMap.SourceColumn] + "(2000)") : (columnTypes[colMap.SourceColumn] == "char" ? (columnTypes[colMap.SourceColumn] + "(10)") : columnTypes[colMap.SourceColumn])))}");
cols.Add(colMap.SourceColumn);
}
var createTempTableCommand = $"Create Table #temp_{tableName.Replace("dbo.", "", StringComparison.CurrentCultureIgnoreCase)} ({columns.ToString()})";
//Execute the command to make a temp table
SqlCommand cmd = new SqlCommand(createTempTableCommand, conn);
cmd.ExecuteNonQuery();
//BulkCopy the data in the DataTable to the temp table
BulkInsertWithIndentity(data, $"#temp_{tableName.Replace("dbo.", "", StringComparison.CurrentCultureIgnoreCase)}");
//use the merge command to upsert from the temp table to the destination table
string mergeSql = $"merge into {tableName} as Target using #temp_{tableName.Replace("dbo.", "", StringComparison.CurrentCultureIgnoreCase)} as Source on {string.Join(" AND ", keys.ConvertAll(k => k = $"Target.{k} = Source.{k}"))} when matched then update set {string.Join(" , ", updateFields.ConvertAll(c => c = $"Target.{c} = Source.{c}"))};";
cmd.CommandText = mergeSql;
rowsAffected = cmd.ExecuteNonQuery();
//Clean up the temp table
cmd.CommandText = $"drop table #temp_{tableName.Replace("dbo.", "", StringComparison.CurrentCultureIgnoreCase)};";
cmd.ExecuteNonQuery();
}
}
public ConditionalTree BuildConditionalTree(ITableData data)
{
var listRows = data.ToList();
if (listRows.Count == 0)
{
return null;
}
var firstRow = listRows[0];
var conditionaTree = new ConditionalTree
{
Root = new ConditionalTree.ConditionalNode()
};
var attributes = firstRow.Attributes.ToList();
attributes.Remove(TableData.ClassAttributeName);
BuildConditionalNodesRecursive(listRows, attributes, conditionaTree.Root);
return conditionaTree;
}
public void BulkInsert(ITableData data, string tableName)
{
var conn = DbConnectionManager.Clone();
try
{
conn.Open();
QueryStart(LogType.Bulk);
conn.BeforeBulkInsert(tableName);
conn.BulkInsert(data, tableName);
conn.AfterBulkInsert(tableName);
RowsAffected = data.RecordsAffected;
QueryFinish(LogType.Bulk);
}
finally
{
if (!conn.LeaveOpen)
{
conn.Close();
}
}
}
public ItemsOfReservation(IReservationItemData reservationItemData, ITableData tableData, IItemData itemData, Reservation reservation, bool prepared, bool delivered, int?stationId = null)
{
TableNumber = tableData.Get(reservation.TableId).Number;
List <ReservationItemDetail> newItems = new List <ReservationItemDetail>();
IEnumerable <ReservationItem> reservationItems = reservationItemData.Get(reservation.Id).Where(o => o.Prepared == prepared && o.Delivered == delivered);
foreach (ReservationItem reservationItem in reservationItems)
{
if (stationId != null)
{
if (itemData.Get(reservationItem.ItemId).StationId == stationId)
{
newItems.Add(new ReservationItemDetail(itemData, reservationItemData, reservation.Id, reservationItem.ItemId));
}
}
else
{
newItems.Add(new ReservationItemDetail(itemData, reservationItemData, reservation.Id, reservationItem.ItemId));
}
}
Items = newItems;
}
protected override void DoBulkInsert(ITableData data)
{
TryDropDummyTable();
CreateDummyTable();
try
{
BulkInsertSql bulkInsert = new BulkInsertSql()
{
ConnectionType = ConnectionManagerType.Access,
AccessDummyTableName = DummyTableName,
UseParameterQuery = true
};
string sql = bulkInsert.CreateBulkInsertStatement(data, data.Definition.Name);
var cmd = DbConnection.CreateCommand();
cmd.Parameters.AddRange(bulkInsert.Parameters.ToArray());
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
finally
{
TryDropDummyTable();
}
}
public override void BulkInsert(ITableData data, string tableName)
{
var sourceColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.SourceColumn).ToList();
var sourceColumnValues = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => "?").ToList();
var destColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.DataSetColumn).ToList();
SQLiteTransaction existingTransaction = Transaction as SQLiteTransaction;
SQLiteTransaction bulkTransaction = null;
if (existingTransaction == null)
{
bulkTransaction = this.DbConnection.BeginTransaction();
}
using (bulkTransaction)
using (var command = this.DbConnection.CreateCommand())
{
command.Transaction = existingTransaction ?? bulkTransaction;
command.CommandText =
$@"INSERT INTO {tableName}
({String.Join(",", sourceColumnNames)})
VALUES ({String.Join(",", sourceColumnValues)})
";
command.Prepare();
while (data.Read())
{
foreach (var mapping in destColumnNames)
{
SQLiteParameter par = new SQLiteParameter();
par.Value = data.GetValue(data.GetOrdinal(mapping));
command.Parameters.Add(par);
}
command.ExecuteNonQuery();
command.Parameters.Clear();
}
bulkTransaction?.Commit();
}
}
public override void Load(params object[] input)
{
MainbaseLevelBar.Value = SyncData.CurrentBaseUpgrade[ListUpgrade.MainBase].Level;
MainbaseLevelBar.SetDefaultPlaceholder();
BaseUpgradeRow resRef = SyncData.CurrentResearch;
BaseUpgradeRow upgRef = SyncData.CurrentUpgrade;
bool isUpgrade = upgRef != null?upgRef.ID.IsDefined() : false;
bool isResearch = resRef != null?resRef.ID.IsDefined() : false;
//Debug.Log(isUpgrade + " - " + isResearch);
ITable table = null;
if (isUpgrade)
{
table = WDOCtrl[upgRef.ID];
ITableData upgInfo = table[upgRef.Level - 1];
int timeInt = fieldReflection.GetPublicField <int>(upgInfo, "TimeInt");
UpgProgBar.Slider.MaxValue = timeInt;
}
if (isResearch)
{
table = WDOCtrl[resRef.ID];
ITableData resInfo = table[upgRef.Level - 1];
int timeInt = fieldReflection.GetPublicField <int>(resInfo, "TimeInt");
ResProgBar.Slider.MaxValue = timeInt;
}
UpgProgBar.gameObject.SetActive(isUpgrade);
ResProgBar.gameObject.SetActive(isResearch);
}
public override void BulkInsert(ITableData data, string tableName)
{
throw new NotImplementedException();
}
public abstract void BulkInsert(ITableData data, string tableName);
public static void BulkUpdate(IConnectionManager connectionManager, ITableData data, string tableName, ICollection <string> setColumnNames, ICollection <string> joinColumnNames) =>
new SqlTask()
{
ConnectionManager = connectionManager
}.BulkUpdate(data, tableName, setColumnNames, joinColumnNames);
public static void BulkUpdate(string name, ITableData data, string tableName, ICollection <string> setColumnNames, ICollection <string> joinColumnNames) =>
new SqlTask()
{
TaskName = name
}.BulkUpdate(data, tableName, setColumnNames, joinColumnNames);
public static void BulkDelete(IConnectionManager connectionManager, ITableData data, string tableName) =>
new SqlTask()
{
ConnectionManager = connectionManager
}.BulkDelete(data, tableName);
public static void BulkDelete(string name, ITableData data, string tableName) =>
new SqlTask()
{
TaskName = name
}.BulkDelete(data, tableName);
public static IEnumerable <IEnumerable <(TableColumn column, object value)> > EnumerateRowsAndConvertValuesToNETDataType(this ITableData data, params string[] columnNames)
{
foreach (var row in data.EnumerateRows(columnNames))
{
yield return(row.Select(column =>
{
if (column.value != null)
{
column.value = Convert.ChangeType(column.value, column.column.NETDataType);
}
return column;
}));
}
}
public static IEnumerable <IEnumerable <(TableColumn column, object value)> > EnumerateRows(this ITableData data, params string[] columnNames)
{
if (data is null)
{
throw new ArgumentNullException(nameof(data));
}
var columns = (columnNames?.Length > 0 ?
data.Definition.ColumnsSynchronized.WithNames(true, columnNames) :
data.Definition.ColumnsSynchronized).
ToArray();
data.Reset();
while (data.Read())
{
yield return(columns.Select(column =>
{
int ordinal = data.GetOrdinal(column.DataSetColumn);
object value = data.GetValue(ordinal);
return (column, value);
}));
}
}
public static void BulkDelete(IConnectionManager connectionManager, ITableData data) =>
new SqlTask()
{
ConnectionManager = connectionManager
}.BulkDelete(data);
public DecisionTree BuildConditionalTree(ITableData data, TreeOptions options)
{
var ret = new C45AlgorithmDataOptimized(TableFixedData.FromTableData(data), options);
return ret.BuildConditionalTree();
}
public BulkSqlGenerator(ITableData data) {
TableName = data.DestinationTableName;
TableData = data;
}
public ItemsOfReservation(IReservationItemData reservationItemData, ITableData tableData, Reservation reservation, int stationId) : this(reservationItemData, tableData, null, reservation, false, false, stationId)
{
}
public ItemsOfReservation(IReservationItemData reservationItemData, ITableData tableData, IItemData itemData, Reservation reservation) : this(reservationItemData, tableData, itemData, reservation, true, false)
{
}
public static void BulkInsert(string name, ITableData data) =>
new SqlTask()
{
TaskName = name
}.BulkInsert(data);
