How to get the DataType and Size of a column using the SqlDataReader?

Question

i am trying to get the data type of each column given to do some verification i already tried the getSchemaTable but it only gives me the schema of a table without values.

For example , i have a table in my database and a columnname : id_declarant. I want to retrieve the datatype and Size of a value from id_declarant.

Here is The code :

comm.Connection=new SqlConnection(connectionString);
String sql = @"
            SELECT * 
            FROM id_declarant,declarant
            WHERE (declarant.Nom_pren_RS='" + textBox1.Text + "') 
            and   (id_declarant.mat_fisc=declarant.mat_fisc)  "; 
comm.CommandText = sql;
comm.Connection.Open();
string mat_fisc;
string clé_mat_fisc;
string categorie ;
string num_etab_sec ;
string activite;
StringBuilder sb = new StringBuilder();
String Nom = textBox1.Text;
using (SqlDataReader reader = comm.ExecuteReader())
{
    while (reader.Read())
    {
        //here i want to know how to retrieve the reader[0].Type and Size to do the verification 
         mat_fisc = reader[0].ToString();
         clé_mat_fisc = reader["clé_mat_fisc"].ToString();
         categorie = reader["categorie"].ToString();
         num_etab_sec = reader["num_etab_sec"].ToString();
         activite = reader["activite"].ToString();
         sb.Append("EF" + mat_fisc + clé_mat_fisc + categorie + num_etab_sec + textBox2.Text + textBox3.Text + Nom + activite);

ojlovecd · Accepted Answer · 2013-06-24 08:38:19Z

5

Type type = reader.GetFieldType(0);

answered Jun 24, 2013 at 8:38

ojlovecd

4,9101 gold badge22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Stephan Over a year ago

Can you elaborate more your answer please ?

marcAntoine Over a year ago

i tried this one : string Type= reader["id_declarant"].GetFieldType(0).FullName; but it displays System.string

ojlovecd Over a year ago

It's Type type = reader.GetFieldType(0); instead of string Type= reader["id_declarant"].GetFieldType(0).FullName; BTW, what is the data type of id_declarant coumn

marcAntoine Over a year ago

yes the reader.GetFieldType(0) displays the DataType of each value in the row 0 of the reader ?? BTW id_declarant is nvarchar(7)

ojlovecd Over a year ago

reader.GetFieldType(0) displays the first column's .net data type. So the data type of id_declarant in the database coresponds System.String type in C#

|

msmith · Accepted Answer · 2020-09-16 10:06:32Z

Please use function GetTableSchema .

SqlDataReader reader= command.ExecuteReader();

using (var schemaTable = reader.GetSchemaTable())
    {
        foreach (DataRow row in schemaTable.Rows)
        {
            string ColumnName= row.Field<string>("ColumnName");
            string DataTypeName= row.Field<string>("DataTypeName");
            short NumericPrecision= row.Field<short>("NumericPrecision");
            short NumericScale= row.Field<short>("NumericScale");
            int ColumnSize= row.Field<int>("ColumnSize");
            Console.WriteLine("Column: {0} Type: {1} Precision: {2} Scale: {3} ColumnSize {4}",      
            ColumnName, DataTypeName, NumericPrecision, NumericScale, ColumnSize);
        }
    }

Using Table schema , you can get all column related property using c# .

Thanks .

Sujit Rai · Accepted Answer · 2014-02-25 09:57:04Z

0

You can use GetDataTypeName() function to get the data type of the field

   String dataType = reader.GetDataTypeName(FIELD_INDEX);

answered Feb 25, 2014 at 9:57

Sujit Rai

4556 silver badges10 bronze badges

Comments

David Castro · Accepted Answer · 2016-07-06 15:33:26Z

0

public string ReadString(IDataReader reader, string columnName) {

string myString = "";

var index = reader.GetOrdinal(columnName);

var fieldType = reader.GetFieldType(index);

if (fieldType.FullName.Contains("Guid"))
{
myString = reader.IsDBNull(index) ? "" : reader.GetGuid(index).ToString();
}
else
{
myString = reader.IsDBNull(index) ? "" : reader.GetString(index);
}
return myString;
}

answered Jul 6, 2016 at 15:33

David Castro

2,05523 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to get the DataType and Size of a column using the SqlDataReader?

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related