Checking the type of a SQL column in C#

Question

I have searched around here and found some threads relating to this problem but they didn't helped me. I need to check the type of a SQL column in a certain datatable and compare it. So let's say I have a column called "CustomerName" and this is a varchar in SQL. Then I want to compare it with a string or something else which is "varchar" and check if this matches. For example it should fail when my column has the type numeric and the string the value "varchar". I think the case with the string is a bad idea (for example when I've got varchar(50) and compare it to "varchar"). Is there a way to do that?

See [Get a DataTable Columns DataType](http://stackoverflow.com/questions/9660981/get-a-datatable-columns-datatype) — Rhys Jones, Mar 16 '15 at 10:14
I tried around but nothing helped. I need the SQL data type not the C# type. I need to compare it with a certain column name not with the index — Takeda15, Mar 16 '15 at 10:20

Abdul Saleem · Accepted Answer · 2018-11-06T07:11:53.427

7

    SqlDataReader read = command.ExecuteReader();
    while (read.Read())
    {
        for (int i = 0; i < read.FieldCount; i++)
        {
            Type dataType = read.GetFieldType(i);
            if (dataType == typeof(int))
            {
                // Do for integers (INT, SMALLINT, BIGINT)
            }
            else if (dataType == typeof(double))
            {
                // Do for doubles (DOUBLE, DECIMAL)
            }
            else if (dataType == typeof(string))
            {
                // Do for Strings (VARCHAR, NVARCHAR)
            }
            else if (dataType == typeof(DateTime))
            {
                // Do for DateTime (DATETIME)
            }
            else if (dataType == typeof(byte[]))
            {
                // Do for Binary (BINARY, VARBINARY, NVARBINARY, IMAGE)
            }
        }
    }

edited Nov 06 '18 at 07:11

answered Mar 16 '15 at 10:22

Abdul Saleem

10,098
5
45
45

This checks only the C# datatypes right? So if my column has "varchar" it won't work or am I wrong? – Takeda15 Mar 16 '15 at 10:25
NO. If it is varchar, then in the above code, it goes into the block where commented as 'Do for Strings' – Abdul Saleem Mar 16 '15 at 10:28
Something is wrong. Why does that not work? String connection = conStringBox.Text; String query = "SELECT [" + sqlTable + "] FROM [" + sqlColumn + "]"; SqlCommand command = new SqlCommand(query, connection); He tells me that sqlCommand has some invalid arguments – Takeda15 Mar 16 '15 at 11:01
Ok I solved it. That works! Thank you! That was what I have looked for! – Takeda15 Mar 16 '15 at 11:30
Just one last question: I just need to let him read once. How can I close the reader and jump out of the while loop after he looped one time through it? – Takeda15 Mar 16 '15 at 11:40
If you want to read it just once, why would you use **while(read.Read());** use **if (read.Read());** instead. You can close the reader by read.Close(); did'nt you tried? I might be able to explain you briefly, if you have put your codes in your question.. – Abdul Saleem Mar 16 '15 at 11:46

score 0 · Answer 2 · answered Mar 16 '15 at 10:14

0

You may try like this:

System.Type myType = reader.GetFieldType(value);
switch (myType.GetTypeCode(myType))
{
    case TypeCode.String:
        break;
    // and so on ........
}

answered Mar 16 '15 at 10:14

Rahul Tripathi

168,305
31
280
331

@Takeda15:- Its an int ordinal or in simple terms you can say that it is index of the column name. Check the MSDN for details: https://msdn.microsoft.com/en-us/library/system.data.datatablereader.getfieldtype(v=vs.110).aspx – Rahul Tripathi Mar 16 '15 at 10:26

Checking the type of a SQL column in C#

2 Answers2