public static Int32 ToJsonFile(List <MsSqlTableColumns> tableColumns, DateTime inventoryTime, string jsonFolder)
{
var className = MethodBase.GetCurrentMethod().DeclaringType.Name;
int ix = 0;
if (tableColumns.Count < 1)
{
return(ix);
}
var server = (from sv in tableColumns select sv).FirstOrDefault();
InfoParts p = new InfoParts(server.Server, className, inventoryTime);
foreach (var s in tableColumns)
{
ix++;
//p.Add(s.Instance, s.Path, s.Name, ix, "ORDINAL_POSITION", $"${s.Version.Length}", s.ORDINAL_POSITION);
p.Add(s.Instance, s.Path, s.Name, ix, "ORDINAL_POSITION", "Int32", s.ORDINAL_POSITION.ToString());
p.Add(s.Instance, s.Path, s.Name, ix, "COLUMN_DEFAULT", "String", s.COLUMN_DEFAULT);
p.Add(s.Instance, s.Path, s.Name, ix, "IS_NULLABLE", "Boolean", s.IS_NULLABLE.ToString());
p.Add(s.Instance, s.Path, s.Name, ix, "DATA_TYPE", "String", s.DATA_TYPE);
p.Add(s.Instance, s.Path, s.Name, ix, "CHARACTER_MAXIMUM_LENGTH", "Int32", s.CHARACTER_MAXIMUM_LENGTH?.ToString());
p.Add(s.Instance, s.Path, s.Name, ix, "NUMERIC_PRECISION", "Int32", s.NUMERIC_PRECISION?.ToString());
p.Add(s.Instance, s.Path, s.Name, ix, "NUMERIC_PRECISION_RADIX", "Int32", s.NUMERIC_PRECISION_RADIX?.ToString());
p.Add(s.Instance, s.Path, s.Name, ix, "NUMERIC_SCALE", "Int32", s.NUMERIC_SCALE?.ToString());
p.Add(s.Instance, s.Path, s.Name, ix, "DATETIME_PRECISION", "Int32", s.DATETIME_PRECISION?.ToString());
p.Add(s.Instance, s.Path, s.Name, ix, "CHARACTER_SET_CATALOG", "String", s.CHARACTER_SET_CATALOG);
p.Add(s.Instance, s.Path, s.Name, ix, "CHARACTER_SET_SCHEMA", "String", s.CHARACTER_SET_SCHEMA);
p.Add(s.Instance, s.Path, s.Name, ix, "CHARACTER_SET_NAME", "String", s.CHARACTER_SET_NAME);
p.Add(s.Instance, s.Path, s.Name, ix, "COLLATION_CATALOG", "String", s.COLLATION_CATALOG);
p.Add(s.Instance, s.Path, s.Name, ix, "COLLATION_SCHEMA", "String", s.COLLATION_SCHEMA);
p.Add(s.Instance, s.Path, s.Name, ix, "COLLATION_NAME", "String", s.COLLATION_NAME);
}
p.ToJsonFile(jsonFolder);
return(ix);
}
public static Int32 ToJsonFile(List <MsSqlStoredProcedure> sqlStoredProcedures, DateTime inventoryTime, string jsonFolder)
{
var className = MethodBase.GetCurrentMethod().DeclaringType.Name;
int ix = 0;
if (sqlStoredProcedures.Count < 1)
{
return(ix);
}
var server = (from sv in sqlStoredProcedures select sv).FirstOrDefault();
InfoParts p = new InfoParts(server.Server, className, inventoryTime);
foreach (var s in sqlStoredProcedures)
{
ix++;
p.Add(s.Instance, s.Path, s.Name, ix, "Created", "DateTime", s.Created.ToString("o"));
p.Add(s.Instance, s.Path, s.Name, ix, "Modified", "DateTime", s.Modified.ToString("o"));
p.Add(s.Instance, s.Path, s.Name, ix, "SpLength", "Int32", s.SpLength.ToString());
p.Add(s.Instance, s.Path, s.Name, ix, "HasASCII", "Boolean", s.HasASCII.ToString());
p.Add(s.Instance, s.Path, s.Name, ix, "Hash", "String", s.Hash);
p.Add(s.Instance, s.Path, s.Name, ix, "Synopsis", "String", s.Hash);
p.Add(s.Instance, s.Path, s.Name, ix, "Abstract", "String", s.Abstract);
p.Add(s.Instance, s.Path, s.Name, ix, "Status", "String", s.Status);
}
p.ToJsonFile(jsonFolder);
return(ix);
}
private void HandleInfoParts(string filename, string schema)
{
InfoParts p = GZfileIO.ReadGZtoPOCO <InfoParts>(filename);
//p.SqlConnectionString = this.SqlConnectionString;
//var p2db = new InfoPartsToDB(SqlConnectionString, TablePrefix);
var p2db = new InfoPartsToDB(TablePrefix);
p2db.PartsToDataSet(p, schema);
}
public static Int32 ToJsonFile(List <MsSqlServers> servers, DateTime inventoryTime, string jsonFolder)
{
var className = MethodBase.GetCurrentMethod().DeclaringType.Name;
int ix = 0;
if (servers.Count < 1)
{
return(ix);
}
var server = (from sv in servers select sv).FirstOrDefault();
InfoParts p = new InfoParts(server.Server, className, inventoryTime);
foreach (var s in servers)
{
ix++;
p.Add(s.Instance, s.Path, s.Name, ix, "Version", $"${s.Version.Length}", s.Version);
}
p.ToJsonFile(jsonFolder);
return(ix);
}
public static Int32 ToJsonFile(List <MsSqlDatabases> databases, DateTime inventoryTime, string jsonFolder)
{
var className = MethodBase.GetCurrentMethod().DeclaringType.Name;
int ix = 0;
if (databases.Count < 1)
{
return(ix);
}
var server = (from sv in databases select sv).FirstOrDefault();
InfoParts p = new InfoParts(server.Server, className, inventoryTime);
foreach (var s in databases)
{
ix++;
p.Add(s.Instance, s.Path, s.Name, ix, "CreationDate", "DateTime", s.CreationDate.ToString("o"));
p.Add(s.Instance, s.Path, s.Name, ix, "FilePath", "String", s.FilePath);
}
p.ToJsonFile(jsonFolder);
return(ix);
}
public static Int32 ToJsonFile(List <MsSqlTables> sqlTables, DateTime inventoryTime, string jsonFolder)
{
var className = MethodBase.GetCurrentMethod().DeclaringType.Name;
int ix = 0;
if (sqlTables.Count < 1)
{
return(ix);
}
var server = (from sv in sqlTables select sv).FirstOrDefault();
InfoParts p = new InfoParts(server.Server, className, inventoryTime);
foreach (var s in sqlTables)
{
ix++;
p.Add(s.Instance, s.Path, s.Name, ix, "RecordCount", "Int32", s.RecordCount.ToString());
p.Add(s.Instance, s.Path, s.Name, ix, "TableType", "String", s.TableType);
p.Add(s.Instance, s.Path, s.Name, ix, "Status", "String", s.Status);
}
p.ToJsonFile(jsonFolder);
return(ix);
}
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);
}
public InfoUpdateEventArgs(InfoParts infoPart)
{
InfoPart = infoPart;
}
private SortedDictionary <string, TableColumn> PartsSizes(InfoParts fullPartsList)
{
var tableColumns = new SortedDictionary <string, TableColumn>();
int identityLength = 1;
// -----------
foreach (var section in fullPartsList.Parts)
{
var instancePath = section.Key;
var instance = instancePath.Instance;
var path = instancePath.Path;
// ----------------
foreach (var part in section.Value)
{
int iLen = part.Identity.Length;
if (identityLength < iLen)
{
identityLength = iLen;
}
var pName = part.Name;
var pType = part.Type;
var len = 0;
if (pType.EndsWith("[]"))
{
pType = "String";
len = part.Value.Length;
}
else if (pType.StartsWith("$"))
{
bool ok = int.TryParse(pType.Remove(0, 1), out len);
if (ok)
{
pType = "String";
}
else
{
len = 0;
}
}
else if (pType.Equals("Byte"))
{
len = 1;
}
else if (pType.Equals("Boolean"))
{
len = 1;
}
else if (pType.Equals("UInt16"))
{
len = 2;
}
else if (pType.Equals("UInt32"))
{
len = 4;
}
else if (pType.Equals("UInt64"))
{
len = 8;
}
else if (pType.Equals("Int16"))
{
len = 2;
}
else if (pType.Equals("Int32"))
{
len = 4;
}
else if (pType.Equals("Int64"))
{
len = 8;
}
else if (pType.Equals("DateTime"))
{
len = 8;
}
else if (pType.Equals("Microsoft.PowerShell.Cmdletization.GeneratedTypes.ScheduledTask.StateEnum"))
{
len = part.Value.Length;
pType = "String";
}
else
{
len = part.Value.Length;
Console.WriteLine($"Unexpected Type on: {part.Identity}, Part: {pName}, Type: {pType} set to String. Length {len}");
pType = "String";
//throw new EvaluateException($"Unhandled Type: {pType}");
}
var found = tableColumns.TryGetValue(pName, out TableColumn tc);
if (found)
{
if (tc.ColLength < len)
{
tc.ColLength = len;
}
}
else
{
var newtc = new TableColumn()
{
ColName = pName,
ColType = pType,
ColLength = len
};
tableColumns.Add(pName, newtc);
}
}
}
// fix up ID fields from Identity part of infoPart
ValidateIDColumn(tableColumns, "InstanceId", "Int32", 4);
ValidateIDColumn(tableColumns, "PathId", "Int32", 4);
// fix up Name field length from Identity part of infoPart
ValidateIDColumn(tableColumns, "Name", "String", identityLength);
return(tableColumns);
}
private bool FillDataTable(DataTable fileDt, InfoParts fullPartsList, int serverId)
{
int lastPart = -1;
int partBase = -1; // each section will reset part number, this will help correct that between sections
DataRow myRow = null;
// foreach (InfoPart part in fullPartsList.PartsList)
foreach (var section in fullPartsList.Parts)
{
partBase++; // bump part number
if (lastPart > -1)
{
partBase += lastPart; // move up by atleast as many parts as in last section
}
var instancePath = section.Key;
var instance = instancePath.Instance;
var instanceId = Instances.ID(instance);
var path = instancePath.Path;
var pathId = Paths.ID(path);
foreach (InfoPart part in section.Value) //fullPartsList.)
{
var partID = part.Identity; // Name column
var partIndex = part.Index; // part - different numbers in case of collision of name
var partName = part.Name; // column name
var partType = part.Type; // column type
var partValue = part.Value; // column value
if (partIndex == lastPart)
{
// not new row
}
else
{
fileDt.MaybeAddRow(myRow);
// new row
myRow = fileDt.NewRow();
myRow.BeginEdit();
myRow["id"] = partIndex + partBase; // hope this is it... keep this!!!
myRow["ServerId"] = serverId;
myRow["InstanceId"] = instanceId;
myRow["PathId"] = pathId;
myRow["Name"] = partID;
lastPart = partIndex;
};
//throw new NotImplementedException("??? can we fix it path and instance");
var rowCol = myRow[partName];
var type = rowCol?.GetType();
object foo;
if (partType.StartsWith("UInt"))
{ // may have to fix this to Int
foo = GetStringIntforUInt(partType, partValue);
}
else
{
foo = partValue.Left(SQLHandler.MaxStringLength);
}
myRow[partName] = foo;
}
// save previous row
fileDt.MaybeAddRow(myRow);
//if (myRow != null)
//{
// myRow.EndEdit();
// fileDt.Rows.Add(myRow);
//}
}
return(true);
}
