I have a project that creates a SqlDataAdapter and uses its Fill method to
fill a DataTable with data from a user-provided query. From there I can
obviously access details about the rows and columns returned by the query.
However, I need to be able to determine the size of the varchar fields that
are returned by the query.

The obvious answer appeared to be to check the DataColumn.MaxLength
property. However, after using the Fill method, this contains the value -1
(unlimited size) for every varchar column, regardless of the underlying
field type.

The only way I can find to retrieve the actual field types is to use the
SqlDataAdapter.FillSchema method. This works fine, but appears to re-execute
the query, causing the whole process to take twice as long. As my queries
are sometimes quite complex and slow, this isn't an acceptable solution.

So is there any way for me to retrieve the field sizes for varchar fields
without having to re-run my query?

Thanks,

You might want to use "SqlDataReader" to return the result. You can fill
the datatable using the datareader object return by calling
"ExecuteReader" method of the "SqlCommand" instance.

I copied the code I used to test our "SpeedyDB ADO.NET Provider" here
for you.




     private void ConvertReaderToDataSet(IDataReader dr, ref DataSet ds)
     {

       do {
         DataTable schemaTable = dr.GetSchemaTable();
         if (schemaTable != null) {
           ArrayList pkCols = new ArrayList();

           DataTable dataTable = new DataTable();
           foreach (DataRow schemaRow in schemaTable.Rows) {
             DataColumn col = new DataColumn();
             col.ColumnName = schemaRow["ColumnName"].ToString();
             col.DataType = (Type)schemaRow["DataType"];
             // set the length of the field for string types only
             if (schemaRow["DataType"].ToString() == "System.String")
               col.MaxLength = (Int32)schemaRow["ColumnSize"];
             if (schemaRow["IsUnique"] != null && schemaRow["IsUnique"]
!= DBNull.Value)
               col.Unique = (bool)schemaRow["IsUnique"];
             col.AllowDBNull = (bool)schemaRow["AllowDBNull"];
             try {
               col.AutoIncrement = (bool)schemaRow["IsAutoIncrement"];
             }
             catch (Exception) {
             }
             col.AllowDBNull = (bool)schemaRow["AllowDBNull"];

             dataTable.Columns.Add(col);
           }

           ds.Tables.Add(dataTable);

           object[] aData = new object[dataTable.Columns.Count];
           // read all rows from the DataReader
           byte[] mb = new byte[30000];
           char[] mc = new char[8000];
           long readB = 0;
           long readC = 0;
           long total = 0;
           while (dr.Read()) {
             // read the row from the DataReader into an array
             dr.GetValues(aData);
             // add the row from the array to the DataTable
             dataTable.Rows.Add(aData);
           }
         }
       }
       while (dr.NextResult());

     }


Charles Zhang
http://www.speedydb.com
(SpeedyDB ADO.NET Provider is the fastest, most secure ADO.NET Provider
over Wide Area Network)



Oenone wrote:
Show quoteHide quote

Charles Zhang wrote:
That's done the trick -- working perfectly. Many thanks for your help.

The data stream (returned by the DataReader) returns objects. The length of
the strings in the VarChar columns can be determined by using the Len
operator in VB.

    intLen = Len(MyRow(intColumnOrdinal).ToString)



Show quoteHide quote

OrderID or DataTable
Currency Manager
copying an object by value
Getchanges problem on expression column
Typed DataSet from existing DataSet
queries regarding connecting to oracle
concurrency in ado.net
Cascading Delete
client-server app where data is disconnected, but stays fresh
using an xml file to store a pre-connection value---