/// <summary>
/// Remplace la totalité des données dans le tableau
/// </summary>
/// <param name="iSimpleDataTable"></param>
/// <param name="iNewDataList"></param>
public static void ReplaceCompleteDataToSimpleDateTable(SimpleDataTable iSimpleDataTable, List <List <string> > iNewDataList)
{
if (iNewDataList.IsNullOrEmpty())
{
throw new Exception("La nouvelle liste est vide ou null");
}
var mergeData = new object[iNewDataList.Count, iNewDataList.First().Count];
//Création du tableau
int rowIndex = 0;
foreach (var iRowItem in iNewDataList.Enum())
{
var columnIndex = 0;
foreach (var iColumnItem in iRowItem.Enum())
{
mergeData[rowIndex, columnIndex] = iColumnItem;
columnIndex++;
}
rowIndex++;
}
//Ecriture des données dans le tableau
iSimpleDataTable.SetTableData(mergeData);
}
public void CreateTables(SimpleSQLManager dbManager)
{
SimpleDataTable dtConfigRow = dbManager.QueryGeneric("SELECT COUNT(*) CT FROM sqlite_master where type='table' and name='ConfigRow'");
List <SimpleDataRow> simpleDataConfigRowRows = dtConfigRow.rows;
if (int.Parse(simpleDataConfigRowRows[0]["CT"].ToString()) == 0)
{
dbManager.CreateTable <ConfigRow>();
Debug.Log("create table ConfigRow.");
}
SimpleDataTable dtResourceRow = dbManager.QueryGeneric("SELECT COUNT(*) CT FROM sqlite_master where type='table' and name='ResourceRow'");
List <SimpleDataRow> simpleDataResourceRowRows = dtResourceRow.rows;
if (int.Parse(simpleDataResourceRowRows[0]["CT"].ToString()) == 0)
{
dbManager.CreateTable <ResourceRow>();
Debug.Log("create table ResourceRow.");
}
SimpleDataTable dtSessionRow = dbManager.QueryGeneric("SELECT COUNT(*) CT FROM sqlite_master where type='table' and name='SessionRow'");
List <SimpleDataRow> simpleDataSessionRowRows = dtSessionRow.rows;
if (int.Parse(simpleDataSessionRowRows[0]["CT"].ToString()) == 0)
{
dbManager.CreateTable <SessionRow>();
Debug.Log("create table SessionRow.");
}
}
public ImportScriptPreviewForm(string sql, SimpleDataTable table)
{
InitializeComponent();
SqlTextControl textbox = new(true) {
Dock = DockStyle.Fill
};
textbox.SqlText = sql;
_scriptPanel.Controls.Add(textbox);
ImportPreviewControl grid = new(table.ToDataTable()) { Dock = DockStyle.Fill };
_previewPanel.Controls.Add(grid);
Ui ui = new(this, 150, 40);
ui.Init(_table);
ui.Init(_split, 0.5);
ui.InitHeader(_scriptLabel);
ui.InitHeader(_previewLabel);
ui.Init(_buttonFlow);
ui.MarginTop(_buttonFlow);
ui.Init(_okButton);
}
}
public static DataTable ToDataTable(this SimpleDataTable self)
{
var dt = new DataTable();
foreach (var col in self.Columns)
{
dt.Columns.Add(col);
}
foreach (var row in self.Rows)
{
var dtRow = dt.NewRow();
var objs = new object[row.Length];
for (int i = 0; i < row.Length; i++)
{
objs[i] = row[i];
var bytes = row[i] as byte[];
if (bytes != null)
{
if (ArrayUtil.IsSqlArray(bytes))
{
objs[i] = "[" + string.Join(", ", ArrayUtil.GetArrayElements(bytes)) + "]";
}
}
}
dtRow.ItemArray = objs;
dt.Rows.Add(dtRow);
}
return(dt);
}
public static DataTable ToDataTable(this SimpleDataTable self, int maxRows = int.MaxValue)
{
var dt = new DataTable();
foreach (var col in self.Columns)
{
dt.Columns.Add(col);
}
dt.BeginLoadData();
foreach (var row in self.Rows)
{
var objs = new object[row.Length];
for (int i = 0; i < row.Length; i++)
{
objs[i] =
row[i] switch {
double n => $"{n:0.####}",
byte[] bytes => BlobUtil.ToString(bytes),
_ => row[i]
};
}
dt.LoadDataRow(objs, true);
if (dt.Rows.Count >= maxRows)
{
break;
}
}
dt.EndLoadData();
return(dt);
}
public void SetsColumnNamesToEmplyListAndSetsRowsToEmpyList()
{
SimpleDataTable target = new SimpleDataTable();
Assert.NotNull(target.ColumnNames);
Assert.NotNull(target.Rows);
Assert.Equal(0, target.ColumnNames.Count);
Assert.Equal(0, target.Rows.Count);
}
public void SetsColumnNamesToEmplyListAndSetsRowsToEmpyList()
{
SimpleDataTable target = new SimpleDataTable();
Assert.NotNull(target.ColumnNames);
Assert.NotNull(target.Rows);
Assert.Equal(0, target.ColumnNames.Count);
Assert.Equal(0, target.Rows.Count);
}
public void WhenNoColumnsAddedToTableYet_ThenReturnsRowWithNoColumns()
{
SimpleDataTable target = new SimpleDataTable();
IDataRow result = target.NewRow();
Assert.NotNull(result);
Assert.Equal(0, target.ColumnNames.Count);
Assert.Equal(0, result.ColumnNames.Count);
}
public void WhenNoColumnsAddedToTableYet_ThenReturnsRowWithNoColumns()
{
SimpleDataTable target = new SimpleDataTable();
IDataRow result = target.NewRow();
Assert.NotNull(result);
Assert.Equal(0, target.ColumnNames.Count);
Assert.Equal(0, result.ColumnNames.Count);
}
public static void AddDataToSimpleDataTable(SimpleDataTable iSimpleDataTable, List <List <string> > iNewDataList, bool iIsNewDataCanBeWider = false)
{
if (iNewDataList.IsNullOrEmpty())
{
throw new Exception("La liste ne peut pas être null");
}
var originalData = iSimpleDataTable.GetCachedTableData();
var newDataIsWider = iNewDataList.First().Count() > originalData.GetLength(0) + 1;
if (iIsNewDataCanBeWider && newDataIsWider)
{
throw new Exception("Les nouvelles données contiennent plus de colonnes que le tableau initial");
}
//Détermine les dimensions du tableau final
var columnCount = 0;
if (originalData.GetLength(1) > 0)
{
columnCount = (iNewDataList.First().Count() > originalData.GetLength(1)) ? iNewDataList.First().Count() : originalData.GetLength(1);
}
else
{
columnCount = iNewDataList.First().Count();
}
var mergeData = new object[iNewDataList.Count + originalData.GetLength(0), columnCount];
//Ajout données existant dans le tableau
for (int j = 0; j <= originalData.GetLength(0) - 1; j++)
{
for (int i = 0; i <= originalData.GetLength(1) - 1; i++)
{
mergeData[j, i] = originalData[j, i];
}
}
//Ajout des nouvelles données
var rowIndex = originalData.GetLength(0);
foreach (var iRowItem in iNewDataList.Enum())
{
var columnIndex = 0;
foreach (var iColumnItem in iRowItem.Enum())
{
mergeData[rowIndex, columnIndex] = iColumnItem;
columnIndex++;
}
rowIndex++;
}
iSimpleDataTable.SetTableData(mergeData);
}
public string LoadToken(SimpleSQLManager dbManager)
{
SimpleDataTable dt = dbManager.QueryGeneric("SELECT Token FROM SessionRow WHERE Id = 1");
List <SimpleDataRow> simpleDataRows = dt.rows;
if (dt == null || simpleDataRows == null || simpleDataRows.Count == 0)
{
return("-");
}
return(simpleDataRows[0]["Token"].ToString());
}
public string GetDescByCode(SimpleSQLManager dbManager, string code, string lan)
{
SimpleDataTable dt = dbManager.QueryGeneric(string.Format("SELECT Desc FROM ResourceRow WHERE Code = '{0}' AND Lan = '{1}'", code, lan));
List <SimpleDataRow> simpleDataRows = dt.rows;
if (dt == null || simpleDataRows == null || simpleDataRows.Count == 0)
{
return("-");
}
return(simpleDataRows[0]["Desc"].ToString());
}
public void WhenColumnsAddedToTable_ThenReturnsRowWithSameColumnsAsTable()
{
SimpleDataTable target = new SimpleDataTable();
target.ColumnNames.Add("Col1");
target.ColumnNames.Add("Col2");
IDataRow result = target.NewRow();
Assert.NotNull(result);
Assert.Equal(2, target.ColumnNames.Count);
Assert.Same(target.ColumnNames, result.ColumnNames);
}
public string LoadLan(SimpleSQLManager dbManager)
{
SimpleDataTable dt = dbManager.QueryGeneric("SELECT Lan FROM ConfigRow WHERE Id = 1");
List <SimpleDataRow> simpleDataRows = dt.rows;
if (dt == null || simpleDataRows == null || simpleDataRows.Count == 0)
{
SaveDefaultConfig(dbManager);
return("Chinese");
}
return(simpleDataRows[0]["Lan"].ToString());
}
/// <summary>
/// Simple Row Count
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static int GetRowCount <T>(string group = "") where T : TableBaseData, new()
{
string SQL = "SELECT * FROM " + (typeof(T).Name);
if (group.Length > 0)
{
SQL += " GROUP BY " + group;
}
SimpleDataTable table = GameManager.Instance.databaseManager.QueryGeneric(SQL);
return(table.rows.Count);
}
private void WriteTable(SimpleDataTable sdt)
{
using var status = WaitStatus.StartRows(Path.GetFileName(_filePath));
var fileMode = _truncateExistingFile ? FileMode.Create : FileMode.Append;
using var stream = File.Open(_filePath, fileMode, FileAccess.Write, FileShare.None);
using var writer = new StreamWriter(stream, _fileEncoding);
if (_headerRow)
{
writer.WriteLine(string.Join(_separator, sdt.Columns.Select(c => CsvUtil.QuoteCsv(c, _separator))));
}
CsvUtil.WriteCsv(sdt.Rows, writer, status.IncrementRows, _separator, _cancel);
}
public void WhenColumnsAddedToTable_ThenReturnsRowWithSameColumnsAsTable()
{
SimpleDataTable target = new SimpleDataTable();
target.ColumnNames.Add("Col1");
target.ColumnNames.Add("Col2");
IDataRow result = target.NewRow();
Assert.NotNull(result);
Assert.Equal(2, target.ColumnNames.Count);
Assert.Same(target.ColumnNames, result.ColumnNames);
}
public TableDocumentControl(NotebookManager manager, string tableName, IWin32Window mainForm)
{
InitializeComponent();
_manager = manager;
_tableName = tableName;
_mainForm = mainForm;
_toolStrip.SetMenuAppearance();
_grid.EnableDoubleBuffering();
Load += async(sender, e) => {
Exception exception = null;
SimpleDataTable sdt = null;
manager.PushStatus("Reading table data...");
await Task.Run(() => {
try {
var n = _manager.Notebook;
sdt = n.SpecialReadOnlyQuery(
$"SELECT * FROM {_tableName.DoubleQuote()} LIMIT 1000",
new Dictionary <string, object>());
} catch (Exception ex) {
exception = ex;
}
});
manager.PopStatus();
if (exception == null)
{
var dt = new DataTable();
foreach (var col in sdt.Columns)
{
dt.Columns.Add(col);
}
foreach (var row in sdt.Rows)
{
var dtRow = dt.NewRow();
dtRow.ItemArray = row;
dt.Rows.Add(dtRow);
}
_grid.DataSource = dt;
_grid.AutoSizeColumns();
}
else
{
MessageForm.ShowError(_mainForm, "Preview Table", "An error occurred.", exception.Message);
}
};
}
public SimpleDataTable QueryGeneric(string QueryText, params object[] args)
{
SimpleDataTable result = null;
try
{
result = dbManager.QueryGeneric(QueryText, args);
}
catch (Exception e)
{
Debug.Log("Sqlite3: Exception: " + e.ToString());
return(null);
}
return(result);
}
protected override void CreateTableAndAddSomeRows(int rowCount, int columnCount)
{
SimpleDataTable table = new SimpleDataTable();
for (int col = 0; col < columnCount; col++)
{
table.ColumnNames.Add("Column " + col);
}
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
IDataRow row = table.NewRow();
for (int colIndex = 0; colIndex < columnCount; colIndex++)
{
row[colIndex] = GetColValue(rowIndex, colIndex);
}
}
}
protected override void CreateTableAndAddSomeRows(int rowCount, int columnCount)
{
SimpleDataTable table = new SimpleDataTable();
for (int col = 0; col < columnCount; col++)
{
table.ColumnNames.Add("Column " + col);
}
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
IDataRow row = table.NewRow();
for (int colIndex = 0; colIndex < columnCount; colIndex++)
{
row[colIndex] = GetColValue(rowIndex, colIndex);
}
}
}
// Return all Texture Findings
public List <SyncListFinding> getTextures()
{
SimpleDataTable table = dbManager.QueryGeneric("SELECT * FROM SyncListFinding WHERE TexturePath <> 0");
Debug.Log("Query Results: Row Count = " + table.rows.Count);
List <SyncListFinding> textures = new List <SyncListFinding>();
foreach (SimpleDataRow row in table.rows)
{
SyncListFinding item = new SyncListFinding();
item.id = int.Parse(row.fields[0].ToString());
item.name = row.fields[1].ToString();
item.colorJson = row.fields[2].ToString();
item.texturePath = row.fields[3].ToString();
textures.Add(item);
Debug.Log("Item added: id = " + item.id + ", name: " + item.name + ", color: " + item.colorJson + ", texture: " + item.texturePath);
}
return(textures);
}
public static IReadOnlyList <string> DetectTypes(SimpleDataTable table)
{
var types = new TypeFlag[table.Columns.Count];
for (var i = 0; i < table.Columns.Count; i++)
{
types[i] = TypeFlag.Text | TypeFlag.Integer | TypeFlag.Real | TypeFlag.Date | TypeFlag.DateTime;
}
foreach (var row in table.Rows)
{
for (var i = 0; i < table.Columns.Count; i++)
{
var value = Convert.ToString(row[i]);
if (string.IsNullOrWhiteSpace(value))
{
// ok for all types as a null
continue;
}
var type = TypeFlag.Text;
if (int.TryParse(value, NumberStyles.Integer, CultureInfo.InvariantCulture, out _))
{
type |= TypeFlag.Integer;
}
if (double.TryParse(value, NumberStyles.Float, CultureInfo.InvariantCulture, out _))
{
type |= TypeFlag.Real;
}
if (DateTime.TryParse(value, CultureInfo.InvariantCulture, DateTimeStyles.None, out var dateTime))
{
type |= TypeFlag.DateTime;
if (dateTime == dateTime.Date)
{
type |= TypeFlag.Date;
}
}
types[i] &= type;
}
}
var chosenTypes = new string[table.Columns.Count];
if (table.Rows.Count == 0)
{
// special case: if there are no rows, use TEXT for all.
for (var i = 0; i < table.Columns.Count; i++)
{
chosenTypes[i] = "TEXT";
}
}
else
{
for (var i = 0; i < table.Columns.Count; i++)
{
var mask = types[i];
if (mask.HasFlag(TypeFlag.Integer))
{
chosenTypes[i] = "INTEGER";
}
else if (mask.HasFlag(TypeFlag.Real))
{
chosenTypes[i] = "REAL";
}
else if (mask.HasFlag(TypeFlag.Date))
{
chosenTypes[i] = "DATE";
}
else if (mask.HasFlag(TypeFlag.DateTime))
{
chosenTypes[i] = "DATETIME";
}
else
{
chosenTypes[i] = "TEXT";
}
}
}
return(chosenTypes);
}
