set column default value of data table when filed with mysqldataadapter

Question

this is my code right now:

private static MySqlConnection conn = null;
private static MySqlDataAdapter AccountsDa = null;
private static MySqlCommandBuilder AccountsCb = null;
AccountsDa = new MySqlDataAdapter("SELECT * FROM accounts", conn);
AccountsCb = new MySqlCommandBuilder(AccountsDa);
Accounts = new DataTable();
AccountsDa.Fill(Accounts);

I'm trying to figure out how to define the column default values without having to do it by hand if I do like this:

DataColumn col = new DataColumn();
col.ColumnName = "id";
col.AllowDBNull = false;
col.DataType = System.Type.GetType("System.Int32");
col.DefaultValue = 0;
Accounts.Columns.Add(col);

for every colum it works fine but how do I have it automatically set the default values from the database when the table is filled. I'm hoping I don't have to define 30 columns by hand.

I tried the Accountsda.FillSchema(Accounts, SchemaType.Source); which sets up the allow nulls and auto increments but not default values

the problem arrises when adding a row to the data table later sometimes I only need to set the value for one column and let the rest of the columns resort to their default value.

I could put 180 lines of code to manually define the default values for inserting rows but there has to be a way to grab that from the database when creating/filling the data table

I'm using in memory data tables because there are times where data will only exist for example 2 minutes and then be deleted again as this is for a dedicated server for an online rts game. so to save hits on the database I'm using data tables and manipulating them and flushing them every 10 minutes so that I only have 1,000 hits to the database every 10 mins instead of possibly 40,000 hits

Solution 1

well according to the msdn gurus after finally getting a response on their forums its not possible to get the default values. all you can do is load wether the value is allowed to be null and wether its autoincrememnt but then you stll have to set the seed and step on auto incrememnt it doesn't get that from the database either but they gave a shorthand version that cuts it down to 30 lines of code instead of 180

after calling fillschema and then filling the data table can simply do like this wich cuts it down to one line instead of the six

Cities.Columns["wood"].DefaultValue = 0;

after a few replies there is even a much easier way to do this not the way I wanted but maybe it will help someone else down the same road instead of one line for each column this does them all in 3 lines

foreach (DataColumn col in Cities.Columns) {
    if (col.ColumnName != "id") col.DefaultValue = 0;
}

id is the primary key and can't set a default value

Solution 2

So I was trying to do something similar to you (except I have no idea how to get the information about auto increment) - I got the idea from https://stackoverflow.com/a/12731310/222897

    private void AssignMandatoryColumns([NotNull] DataTable structure, string tableName)
    {
        // find schema
        string[] restrictions = new string[4]; // Catalog, Owner, Table, Column
        restrictions[2] = tableName;
        DataTable schemaTable = _dbCon.GetSchema("Columns", restrictions);
        if (schemaTable == null) return;

        // set values for columns
        foreach (DataRow row in schemaTable.Rows)
        {
            string columnName = row["COLUMN_NAME"].ToString();
            if (!structure.Columns.Contains(columnName)) continue;
            if (row["IS_NULLABLE"].ToString() == "NO") structure.Columns[columnName].AllowDBNull = false;

            //if (structure.Columns[columnName].AutoIncrement) continue; // there can be no default value
            var valueType = row["DATA_TYPE"];
            var defaultValue = row["COLUMN_DEFAULT"];
            try
            {
                structure.Columns[columnName].DefaultValue = defaultValue;
                if (!structure.Columns[columnName].AllowDBNull && structure.Columns[columnName].DefaultValue is DBNull)
                {
                    Logger.DebugLog("Database column {0} is not allowed to be null, yet there is no default value.", columnName);
                }
            }
            catch (Exception exception)
            {
                if (structure.Columns[columnName].AllowDBNull) continue; // defaultvalue is irrelevant since value is allowed to be null
                Logger.LogWithoutTrace(exception, string.Format("Setting DefaultValue for {0} of type {1} {4} to {2} ({3}).", columnName, valueType, defaultValue, defaultValue.GetType(), structure.Columns[columnName].AllowDBNull ? "NULL" : "NOT NULL"));
            }
        }
    }

The function takes the DataTable you want to set the values for (I get mine by querying the DB) and the name of the table.

For some reason the timestamp and date columns don't like their default value no matter what I do.