How to set column Properties in SQL Server

0

I have to create a Copy of a Database on SQL Server. On this way I got a connection to the new DB

 ADODB.Connection connection = new ADODB.Connection();

        OleDbConnectionStringBuilder builder = new System.Data.OleDb.OleDbConnectionStringBuilder();
        builder["Provider"] = provider;
        builder["Server"] = @"Themis\DEV";
        builder["Database"] = file_name;
        builder["Integrated Security"] = "SSPI";
        string connection_string = builder.ConnectionString;
        connection.Open(connection_string, null, null, 0);

        return connection;
    }

I create the tables with ADOX

 ADOX.Catalog cat, Dictionary<string, ADOX.DataTypeEnum> columntype)
    {
        List<string> primaryKeysList = GetPrimaryKey(tabelle);
        Key priKey = new Key();

        Catalog catIn = new Catalog();
        catIn.ActiveConnection = dbInfo.ConIn;
        Dictionary<string, List<string>> indexinfo = new Dictionary<string, List<string>>();
        GetSecondaryIndex(tabelle, indexinfo);
        if (columntype.Count != 0) columntype.Clear();

        if (size.Count != 0) size.Clear();

        foreach (DataRow myField in schemaTable.Rows)
        {
            String columnNameValue = myField[columnName].ToString(); //SpaltenName
            bool ich_darf_dbnull_sein = (bool)myField["AllowDBNull"];


            ADOX.Column columne = new ADOX.Column();
            columne.ParentCatalog = cat;
            columne.Name = columnNameValue;

            if (!columntype.ContainsKey(columnNameValue))
            {
                columntype.Add(columnNameValue, (ADOX.DataTypeEnum)myField["ProviderType"]);
            }
            columne.Type = (ADOX.DataTypeEnum)myField["ProviderType"];
            //type.Add((ADODB.DataTypeEnum)myField["ProviderType"]);
            columne.DefinedSize = (int)myField["ColumnSize"];
            dbInfo.ColumnName = columnNameValue;
            dbInfo.TableName = tabelle;

            dbInfo.Column_size = (int)myField["ColumnSize"];
            dbInfo.Column_Type = (ADOX.DataTypeEnum)myField["ProviderType"];
            size.Add((int)myField["ColumnSize"]);
            if (primaryKeysList.Contains(columnNameValue))
            {
                dbInfo.IsPrimary = true;
            }
            else dbInfo.IsPrimary = false;

            object index = catIn.Tables[tabelle].Columns[columnNameValue].Attributes;
            if (index.Equals(ColumnAttributesEnum.adColFixed) || (int)index == 3)
                dbInfo.Fixed_length = true;
            else
                dbInfo.Fixed_length = false;

            Console.WriteLine("{0}={1}", myField[columnName].ToString(), catIn.Tables[tabelle].Columns[columnNameValue].Attributes);

                TargetDBMS.SetColumnProperties(columne, dbInfo);



            switch (columne.Type)
            {
                case ADOX.DataTypeEnum.adChar:
                case ADOX.DataTypeEnum.adWChar:
                case ADOX.DataTypeEnum.adVarChar:
                case ADOX.DataTypeEnum.adVarWChar:
                    columne.DefinedSize = (int)myField["ColumnSize"];
                    break;
                default:
                    break;
            }
            if (primaryKeysList.Contains(columnNameValue))
            {
                priKey.Name = "PK_" + tabelle + "_" + columnNameValue;
                primaryKeysList.Remove(columnNameValue);
                priKey.Columns.Append(myField[columnName], (ADOX.DataTypeEnum)myField["ProviderType"], (int)myField["ColumnSize"]);

            }
            columnNameList.Add(columnNameValue);

            table.Columns.Append(columne);
        } 
        table.Keys.Append((object)priKey, KeyTypeEnum.adKeyPrimary);
    }

But when I set the Properties for the columns I got an Exception

 internal override void SetColumnProperties(ADOX.Column columne, DbInfo dbInfo)
    {
        GetColumnProperties(dbInfo);
                columne.Properties["Autoincrement"].Value = dbInfo.Field_prop["Autoincrement"];
                columne.Properties["Default"].Value = dbInfo.Field_prop["Default"];
                columne.Properties["Nullable"].Value = dbInfo.Field_prop["Nullable"];
    }

My Program works well for Access DB, but I cannot set it for the DB on SQL Server

Exception (0x80040E21) Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

If I try this way

string query = "SELECT * FROM Forms";
 DataTable dt = new DataTable();
            using (SqlConnection sqlConn = Connection())
            using (SqlCommand cmd = new SqlCommand(query, sqlConn))
        {
            sqlConn.Open();

            dt.Load(cmd.ExecuteReader());

        }


        foreach (DataColumn col in dt.Columns)
        {
            Console.WriteLine(col.ColumnName);
            col.AllowDBNull = true;
            dt.AcceptChanges();
            col.AutoIncrement = false;
            dt.AcceptChanges();


        }

it does not change the properties in the DB

c#
oledb
adox
asked on Stack Overflow Aug 10, 2016 by Bird75 • edited Aug 10, 2016 by Filburt

1 Answer

0

The Problem is partially solved

columne.Properties["Autoincrement"].Value = (bool)dbInfo.Autoincrement;

because the dbInfo.Autoincrement was an object I have to write (bool) dbInfo.Autoincrement

Not solved is this

columne.Properties["Default"].Value = (string)dbInfo.Default_Value;

because the type of a value Default_Value can be 0, empty ("") or "-"...I don’t know what i can do in this case

answered on Stack Overflow Aug 16, 2016 by Bird75

User contributions licensed under CC BY-SA 3.0