예제 #1
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);
        }
예제 #2
0
        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);
        }