Getting Integer values from a Datareader

[smcgarry]

How can I get C# to pull back a value from a datareader that is a datatype of int in a sql database. The following code returns an InvalidCastException error:

SqlCommand cmd = new SqlCommand("SELECT name, xtype, length, colstat FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = '" + Table + "')", cn);
SqlDataReader dr = cmd.ExecuteReader();
SqlParameter prm;
long xtype = 0;
while (dr.Read())
{
xtype = (long)dr.GetSqlInt64(1);
}

xtype in syscolumns in SQL is a datatype of tinyint. I have tried using dr.GetSqlInt16, GetSqlInt32, and GetSqlInt64 as well as throwing it into a variable declared as integer, short, and long. I've also tried using dr.GetInt16 (instead of GetSqlInt16), GetInt32, and GetInt64...same error. It seems that C# doesn't like anything except strings and objects when it comes to the datareader and I need to be able to get an integer value back. Thoughts?

[MasterBlaster]

I would advise using a stored procedure instead of inline SQL. Then you can cast your retun fields to any compatable datatype before it even hits your prog. Why waste time making your client sort out datatypes. You database is much better at it. Not to mention the security mess it creates.

[MasterBlaster]

Dammit forgot to try to answer the question. Mabe try this?

SELECT name, CAST(xtype AS INT), length, colstat.......

[smcgarry]

I'm not sure that would fix my problem since xType is defined as integer in the table. Casting it as INT is still going to return the SqlInteger32 datatype to the datareader. C# doesn't seem to like a lot of implicit conversions and for some reason REALLY doesn't like taking a SQL integer definition and casting it to a standard integer definition. Does that make sense? Basically what I'm doing in C# is what is done through the datadapter wizard. I'm just making it more global so the dataadapters don't have to be tied to the form (thus the reason I am querying sysobjects and syscolumns).

[MasterBlaster]

the error message seems to think it is a tinyint. That's wierd. I've seen problems come up with that datatype but never a sql int. Sorry, don't know what else to tellya. Good Luck.:(

[hellswraith]

Are you talking about a tinyint?

Definition of tinyint: Integer data from 0 through 255. Storage size is 1 byte.

Seems like the closest type that matches would be a byte instead of a integer.

Now, if it is a int in the database, then you should have no problems using the int datatype in C#.


Try this if it is a int in the database:

int xtype = 0;
while (dr.Read())
{
if(dr[1] != DBNull.Value)
{
xtype = dr[1];
}
}