C# – set column default value of data table when filed with thesqldataadapter

cdatabaseMySQL

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

Best Answer

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

Related Topic