How to get data type and column size using SqlDataReader?

Question

I am trying to get the data type of each column in order to do some validation. I already tried getSchemaTable , but it gives me a table schema with no values.

For example, I have a table in my database and a column name: id_declarant . I want to get the data type and value size 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); 

Answer 1

 Type type = reader.GetFieldType(0); 

Answer 2

Please use the GetTableSchema function.

 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, scale,ColumnSize); } } 

Using a table schema, you can get all the properties associated with a column using C #.

Thanks.

Answer 3

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

  String dataType = reader.GetDataTypeName(FIELD_INDEX); 

Answer 4

 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; }