Example #1
0
        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);
        }
Example #2
0
        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);
        }
Example #3
0
        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);
        }
Example #4
0
        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);
        }
Example #5
0
        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);
        }
Example #6
0
        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);
        }
Example #7
0
        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);
        }
Example #8
0
 public InfoUpdateEventArgs(InfoParts infoPart)
 {
     InfoPart = infoPart;
 }
Example #9
0
        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);
        }
Example #10
0
        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);
        }