By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,759 Members | 1,236 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,759 IT Pros & Developers. It's quick & easy.

Executing a query and reading the column sizes

P: n/a
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,

--

(O)enone
Apr 11 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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:
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,
Apr 11 '07 #2

P: n/a
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" <oe****@nowhere.comwrote in message
news:eP**************@TK2MSFTNGP04.phx.gbl...
>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,

--

(O)enone

Apr 11 '07 #3

P: n/a
Charles Zhang wrote:
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.

--

(O)enone
Apr 12 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.