C# 使用mysqldataadapter归档时设置数据表的列默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15582796/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
set column default value of data table when filed with mysqldataadapter
提问by BrierMay
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.
对于每个列，它都可以正常工作，但是如何在填充表时自动设置数据库中的默认值。我希望我不必手动定义 30 列。
I tried the Accountsda.FillSchema(Accounts, SchemaType.Source); which sets up the allow nulls and auto increments but not default values
我尝试了 Accountsda.FillSchema(Accounts, SchemaType.Source); 它设置了允许空值和自动增量但不是默认值
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
我可以用 180 行代码来手动定义插入行的默认值，但是在创建/填充数据表时必须有一种方法可以从数据库中获取它
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
我在内存数据表中使用，因为有时数据只会存在 2 分钟，然后再次被删除，因为这是用于在线 rts 游戏的专用服务器。所以为了保存对数据库的点击，我正在使用数据表并操作它们并每 10 分钟刷新一次，这样我每 10 分钟只有 1,000 次点击数据库，而不是可能有 40,000 次点击
回答by BrierMay
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
根据 msdn 大师的说法，最终在他们的论坛上得到回复后，不可能获得默认值。您所能做的就是加载该值是否允许为空以及它的自动增量但随后您必须设置种子并踩自动增量它也不会从数据库中获取该值但他们提供了一个速记版本它减少到 30 行代码而不是 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
在调用 fillschema 然后填充数据表后可以简单地这样做，将其减少到一行而不是六行
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
在几次回复之后，甚至有一种更简单的方法来做到这一点，而不是我想要的方式，但也许它会帮助其他人走在同一条路上，而不是每列一行，这将它们全部分为 3 行
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
id 是主键，不能设置默认值
回答by aliceraunsbaek
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
所以我试图做一些类似于你的事情（除了我不知道如何获取关于自动增量的信息） - 我从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.
该函数采用您要为其设置值的 DataTable（我通过查询 DB 获得我的值）和表的名称。
For some reason the timestamp and date columns don't like their default value no matter what I do.
出于某种原因，无论我做什么，时间戳和日期列都不喜欢它们的默认值。
