| Bug #16643 | DataAdapter.FillSchema does not set default values | ||
|---|---|---|---|
| Submitted: | 19 Jan 2006 17:57 | Modified: | 29 Mar 2007 15:46 |
| Reporter: | vincent ugenti | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 1.0.4 & 1.0.7 | OS: | Windows (Windows 2000 Professional) |
| Assigned to: | Reggie Burnett | CPU Architecture: | Any |
[22 Feb 2006 8:40]
Valeriy Kravchuk
Thank you for a problem report. Yes, the default values should be set. At least, they are set properly in the mysql command line client. Please, try to repeat with a newer version of Connector/Net, 1.0.7, and inform about the results.
[22 Feb 2006 14:20]
vincent ugenti
This was tested already on 1.0.7, with the same result. It was reported as Bug #16679 which is currently marked as "Duplicate". Shortly after I submitted the original bug report I noticed there was a newer version available and I tried it right away (unfortunately, with the same problem).
[5 Apr 2006 11:54]
Tonci Grgin
Thank you for your bug report.
Verified as described by reporter:
ICSharpCode.SharpZipLib C:\mysql-connector-net-1.0.7
WinXP SP2
VS 2005
CREATE TABLE `test` (
`qqq` int(10) unsigned NOT NULL default '0',
`www` char(10) default NULL,
PRIMARY KEY (`qqq`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Code snippet:
MySqlConnection connection = new MySqlConnection();
MySqlCommand command = new MySqlCommand();
connection.ConnectionString = "DataSource=munja;Database=test;UserID=root;Password=";
connection.Open();
DataSet dataSet = new DataSet();
MySqlDataAdapter dataAdapter = new MySqlDataAdapter("select * from test limit 1", connection);
MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
dataAdapter.FillSchema(dataSet, System.Data.SchemaType.Source);
commandBuilder.RefreshSchema();
+ dataSet {System.Data.DataSet} System.Data.DataSet
- dataSet.Tables["Table"].Columns {System.Data.DataColumnCollection} System.Data.DataColumnCollection
- base {System.Data.DataColumnCollection} System.Data.InternalDataCollectionBase {System.Data.DataColumnCollection}
Count 2 int
IsReadOnly false bool
IsSynchronized false bool
+ SyncRoot {System.Data.DataColumnCollection} object {System.Data.DataColumnCollection}
+ Static members
+ Non-Public members
- Non-Public members
+ base {System.Data.DataColumnCollection} System.Data.InternalDataCollectionBase {System.Data.DataColumnCollection}
+ _list Count = 2 System.Collections.ArrayList
- columnFromName Count = 2 System.Collections.Hashtable
+ ["www"] {www}
- ["qqq"] {qqq}
Key "qqq" object {string}
- Value {qqq} object {System.Data.DataColumn}
+ base {qqq} System.ComponentModel.MarshalByValueComponent {System.Data.DataColumn}
AllowDBNull false bool
AutoIncrement false bool
AutoIncrementSeed 0 long
AutoIncrementStep 1 long
Caption "qqq" string
ColumnMapping Element System.Data.MappingType
ColumnName "qqq" string
+ DataType {Name = "UInt32" FullName = "System.UInt32"} System.Type {System.RuntimeType}
DateTimeMode UnspecifiedLocal System.Data.DataSetDateTime
>>- DefaultValue {} object {System.DBNull}
- Static members
Value {} System.DBNull
Expression "" string
+ ExtendedProperties Count = 0 System.Data.PropertyCollection
MaxLength -1 int
Namespace "" string
Ordinal 0 int
Prefix "" string
ReadOnly false bool
+ Table {Table} System.Data.DataTable
Unique true bool
+ Static members
+ Non-Public members
+ Raw View
ColumnsImplementingIChangeTracking {Dimensions:[0]} System.Data.DataColumn[]
columnsImplementingIChangeTracking {Dimensions:[0]} System.Data.DataColumn[]
ColumnsImplementingIChangeTrackingCount 0 int
ColumnsImplementingIRevertibleChangeTrackingCount 0 int
defaultNameIndex 1 int
delayedAddRangeColumns null System.Data.DataColumn[]
fInClear false bool
+ List Count = 2 System.Collections.ArrayList
nColumnsImplementingIChangeTracking 0 int
nColumnsImplementingIRevertibleChangeTracking 0 int
onCollectionChangedDelegate null System.ComponentModel.CollectionChangeEventHandler
onCollectionChangingDelegate null System.ComponentModel.CollectionChangeEventHandler
onColumnPropertyChangedDelegate null System.ComponentModel.CollectionChangeEventHandler
+ table {Table} System.Data.DataTable
[26 Sep 2006 20:22]
Reggie Burnett
This is a complicated task so we created a worklog to implement this in a followup release.
[29 Mar 2007 15:38]
Reggie Burnett
Don't know currently how to implement this. Plus, I don't know of any provider out there that currently does this.
[29 Mar 2007 15:46]
vincent ugenti
JET Engine for Access Database implements the Default Values correctly using the default OleDb implementation in the Microsoft.NET Framework v1.0. Have not tested on any other engines.
OleDbConnection c = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=Data\\DATABASE.MDB;");
And following the steps outlined in the original submission, the default values are populated correctly.
Description: Using DataAdapter.FillSchema to add an empty DataTable to a DataSet that contains the MySQL database schema. All the columns and their types and whether or not they allow nulls are set correctly, but the default values are not set correctly. As a result, when creating a new row, values are initialized to DBNull instead of the correct default values. How to repeat: Create a table with default values for one or more columns. Then in the program, create an empty table with database schema programmatically: *------------------* DataSet dataSet = new DataSet(); MySqlDataAdapter dataAdapter = new MySqlDataAdapter("select * from <tablename> limit 1", (MySqlConnection)DB.connection); MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter); dataAdapter.FillSchema(dataSet, System.Data.SchemaType.Source); commandBuilder.RefreshSchema(); *------------------* dataSet.Tables["Table"].Columns will contain the collection of columns and will know the correct column names and types from the call to FillSchema. But the DataColumn.DefaultValue property will not be set for any of the columns. This can be evidenced by calling dataSet.Tables["Table"].NewRow(). The resulting DataRow object will have all fields initialized to DBNull instead of the default values from the database schema. Right now the only way to work around this is to hard-code the default values into my program. Obviously it would be better to retrieve the default values from the schema in case they change later. Suggested fix: Update the MySqlDataAdapter.FillSchema method to populate the default values in the resulting DataTable.Columns' DefaultValue properties. Then the .NET framework should be able to handle the rest.