Executing a query and reading the column sizes

[Oenone]

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,

 

[Charles Zhang]

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)

 

[William \(Bill\) Vaughn]

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)



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Oenone]

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.

That's done the trick -- working perfectly. Many thanks for your help.