Connecting to MySQL with ODBC

[sps itsec46]

Hi guys, I think it's a simple question: I connect to a MySQL database with OdbcDataAdapter and besides the data I want to get the schema of the database directly from the database, so I use something like this:

public DataSet GetData()
{
// Create DataSet
DataSet ds = new DataSet(datasetName);
OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = GetSelectAllCmd(); // Generates Query: "SELECT * FROM " + tableName
 
// Get schema from database
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 
// Rename table
adapter.TableMappings.Add("Table", tableName);
 
// Get data
adapter.Fill(ds);
 
// Do we have errors?
if(ds.HasErrors)
ds.RejectChanges();
else
ds.AcceptChanges();
 
// Free resources
adapter.Dispose();
 
return ds;
}

Everything seems working fine, but the schema isn't transfered correctly. The MaxLength property of the DataColumn member has the value of the longest entry in the database. For example if there is a column called 'name' and the database has an entry like 'John' the MaxLength property is 4. But I specified the 'name' column to allow 100 chars while creating the table (... name VARCHAR(100) NOT NULL, ...).   Furthermore I'm not able to change the MaxLength property. The following has no effect:

ds.Tables[tableName].Columns["name"].MaxLength = 100;

So I'm not able to insert a name to the 'name' column that is longer than 4. I get the error "The value violates the MaxLength limit of this column". Many thanks in advance!!! Regards, mYkel P.S.: Sorry I also posted this in SQL/ADO/ADO.NET forum but perhaps it fits better here...

[Heath Stewart]

The schema that you get is relative to the result set. If you want to have a specific schema that matches your tables, I suggest you create a typed DataSet by using either the xsd.exe utility that ships with the .NET Framework SDK to create a typed DataSet class from an XML Schema, or add a new DataSet class in Visual Studio .NET and either re-create your tables (and optionally your keys and relationships), or drag and drop your tables from the Server Explorer after adding a connection object to your database. Use that typed DataSet in your OdbcDataAdapter calls (like Fill) and in your queries. If you're using the designer in VS.NET for a DataGrid or some other data-bound control, it gives you nice design-time support for specific table names and columns. If also provides faster access to data when using the typed table and column names because they reference the DataTable and DataColumn objects directly, instead of using a string name that requires several looks ups. If you don't want to use a typed DataSet, you can also add a columns to your TableMapping property. See the DataTableMapping class and its ColumnMappings property documentation in the .NET Framework SDK for more information.

Microsoft MVP, Visual C# My Articles

[sps itsec46]

I got it working with a typed DataSet... thanks very much for your help Heath!!! You're the best! :rose: Regards, mYkel