public void PartsToDataSet(InfoParts fullPartsList, string schema = "dbo")
{
var tableColumns = PartsSizes(fullPartsList);
//var sQLHandler = new SQLHandler(SqlConnectionString);
var sQLHandler = new SQLHandler();
var server = fullPartsList.Server;
var serverId = sQLHandler.ServerID(server);
//var schema = "dbo";
var dtName = TablePrefix + fullPartsList.Set;
var fileDt = AddColumnsToDataTable(dtName, tableColumns);
var tablebOK = PrepareOrUpdateTable(sQLHandler, schema, dtName, tableColumns);
Breakspot = 0;
DataTable sqlDT = PrepareSQLDT(fileDt);
var sqlDTLoaded = FillSqlDt(sQLHandler, sqlDT, schema, dtName, server);
var dtLoaded = FillDataTable(fileDt, fullPartsList, serverId);
var sqUpdateNeeded = CompareSQLDTtoFileDT(sqlDT, fileDt);
var result2 = sQLHandler.UpdateDA(sqlDT, schema, dtName, server);
}
private bool PrepareOrUpdateTable(SQLHandler sQLHandler,
string schema,
string tableName,
SortedDictionary <string, TableColumn> tableColumns)
{
bool success = false;
var tableExists = sQLHandler.TableExists(schema, tableName); // dtName);
if (tableExists)
{
int failures = 0;
// check all columns
foreach (var col in tableColumns)
{
var colName = col.Key;
var colType = col.Value.ColType;
var ct = sQLHandler.ColTypeStringToEnum(colType);
var colLength = col.Value.ColLength; // max length in table
var dbColLength = sQLHandler.ColumnLength(schema, tableName, colName);
var colExists = (dbColLength != 0);
if (!colExists)
{
if (ContinueParts.Contains(colName.ToLower()))
{
continue;
}
//if (colName.Equals("Name")) continue; // skip this Name column?
var result = sQLHandler.AddColumn(schema, tableName, colName, ct, colLength);
if (!result)
{
failures++;
var msg = $"Add Column Error table [{schema}].[{tableName}] Column {colName}.";
Console.WriteLine(msg);
}
}
if ((dbColLength < colLength) && (dbColLength < SQLHandler.MaxStringLength))
{
var result = sQLHandler.AlterColumn(schema, tableName, colName, ct, colLength);
if (result)
{
failures++;
var msg = $"Alter Column Error? table [{schema}].[{tableName}] Column {colName}.";
Console.WriteLine(msg);
}
}
}
if (failures == 0)
{
success = true;
}
}
else
{
// how long is the "Name" key field?
int nameLength = 32; // guess
{
try
{
nameLength = tableColumns["Name"].ColLength;
}
catch (Exception ex)
{
Console.WriteLine($"Error creating table [{schema}].[{tableName}] - Can't find 'Name' column. {ex.Message}");
}
}
// create new table
var newtable = sQLHandler.PrepareTable(schema, tableName, nameLength);
if (newtable)
{
bool columnsOK = true;
foreach (var col in tableColumns)
{
var colName = col.Key;
if (ContinueParts.Contains(colName.ToLower()))
{
continue;
}
//if (colName.Equals("Name")) continue; // skip this name, OK dont skip this,but must do sooner
var colType = col.Value.ColType;
var ct = sQLHandler.ColTypeStringToEnum(colType);
var colLength = col.Value.ColLength; // max length in table
if (colName.Equals("Path"))
{
colName = "PathId"; ct = SQLHandler.ColumnType.ctInt; colLength = 4;
}
if (colName.Equals("Instance"))
{
colName = "InstanceId"; ct = SQLHandler.ColumnType.ctInt; colLength = 4;
}
var newparm1 = sQLHandler.PrepareColumn(colName, ct, colLength);
if (!newparm1)
{
columnsOK = false;
var msg = $"Column Error table [{schema}].[{tableName}] Column {colName}.";
Console.WriteLine(msg);
}
}
if (columnsOK)
{
var newTableCreated = sQLHandler.CreateTable();
success = newTableCreated;
Console.WriteLine($"Could {(newTableCreated ? "" : "not ")}create table [{schema}].[{tableName}]");
}
else
{
sQLHandler.AbandonNewTable();
Console.WriteLine($"Could not create table [{schema}].[{tableName}]");
}
}
}
return(success);
}