Data Column Length

[JLizOB]

I have a data adapter that is filling a data table with info from a SQL Server database. I am trying to retrieve the length of each datacolumn by using the maxlength property from the datacolumncollection. It keeps returning -1 though? How can I retrieve the column length for each column?

 

[JLizOB]

When googling I found that after you fill your datatable or dataset with your adapter you also need to use the dataadapter.FillSchema("Datatable or DataSet here", "Schema Here") such as below (note this is in VB not C# but is very easy to convert):

Dim dataTables() As DataTable
Dim size As String

SqlDataAdapter1.Fill(DataSet11) 'This fills your dataset

dataTables = SqlDataAdapter1.FillSchema(DataSet11, SchemaType.Source) 'This returns an array of Data Tables

size = dataTables(0).Columns(2).MaxLength 'Where DataTables(0) is the table in the adapter and Columns(2) is the column you want the size of

 

[obislavu]

You CAN detect the lenght of the columns if and only if you send a query like this one:
select c.length,c.name from sysobjects o, syscolumns c where o.id=c.id and o.name ='your table name' ;
Iterate through this set using the c.name and find out the lenght but be aware about non-text fields like integers , double, float which have a lenght depending on the database. Use this lenght for the char, varchar, etc...

obislavu

 

[curtm]

I just found an even simpler solution on another forum:

Before using the dataAdapter to fill the table, set the MissingSchemaAction property to "AddWithKey" as follows:


DataAdapter adp = new OleDbDataAdapter("SELECT * FROM FOO", Conn);
adp.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adp.Fill(table)


When this is done, column lengths are brought across from the database.