type cast textbox value dynamically to the data type value of a table column

Question

I am trying to build dynamic query which will built on user selection for example my database structure is like below:

columnname  datatype
productid     int
productname   varchar(100)
updatedate    datetime
lastsaledate  datetime

i have a combobox which will load table names dynamically. if a particular table is selected all the columns names will be generated to listbox, then user will select columns based on his requirement and export the data to excel. some times he may try to retrieve data based on selecting the column and entering the value for the column.

My problem is as my sql query is building dynamically based on user selection, sometimes he may select productid to retrieve all the products then the datatype is int then my sql query should build like

select * from products where productid= @pid

as @pid value is supplied from textbox i will get error datatype mismatch or something. how to dynamically convert to the datatype of the selected column.

var type = Type.GetType(label2.Text);
            queryCmd += " WHERE " + comboBox2.SelectedItem.ToString() + "=" + Convert.ChangeType(textBox1.Text, type);

 public static Type GetType(string typeName)
    {
        var type = Type.GetType(typeName);
        if (type != null) return type;
        foreach (var a in AppDomain.CurrentDomain.GetAssemblies())
        {
            type = a.GetType(typeName);
            if (type != null)
                return type;
        }
        return null;
    }

something like var type = Type.GetType(label1.text); i tried this for the conversion but "type" is null always — Tan, Jan 31 '16 at 21:53
Seems like a thing You're looking for http://stackoverflow.com/a/5054392/444149 — Marty, Jan 31 '16 at 21:54
ah no...i am dynamically reading data_type of a column from table schema and building query based on user selection. for the where clause of the query i have to pass the value collected in textbox to my where clause as user may choose a column with int or varchar or datetime the textbox value should be converted to fit the column type. this is what exactly i am looking for — Tan, Jan 31 '16 at 21:57
So if You know the type that is should be ? what is the problem ? You always get a string from Your textbox. So why is it that knowing the target type - You cant convert a string to it ? int.Parse(str) or float.parse(str) ? — Marty, Jan 31 '16 at 21:59
int and parse should be decided by my label value, if label.text=int , then the convert type should be "int".parse(str) if label.text=float, then the convert type should be "float".parse(str)..etc — Tan, Jan 31 '16 at 22:05

score 0 · Answer 1 · answered Jan 31 '16 at 22:26

0

If you want to cast a string to an int you could cast it like this:

string value = "10";
int result = Convert.ToInt32(value);//value is what you want to convert

If the value fails to convert you will get an exception that you will need to handle. Or you could use TryParse like this:

bool result = Int32.TryParse(value, out number);
if (result)
{
    Console.WriteLine("Converted '{0}' to {1}.", value, number);
}
else
}
//            if (value == null) value = ""; 
    Console.WriteLine("Attempted conversion of '{0}' failed.", 
                           value == null ? "<null>" : value);
}

These methods are available for all datatypes.

answered Jan 31 '16 at 22:26

Vivek Verma

333
3
13

i should get the assembly qualified name of my label1.text which stores int or bit or datetime or anything..you know how to do it – Tan Jan 31 '16 at 22:31
I am not sure I understand what you are asking. But you can cast the contents of your Label1.Text to any datatype you want (if the cast is possible) using the example above. If the cast is not possible you will have to write code to handle that. – Vivek Verma Jan 31 '16 at 22:35
http://stackoverflow.com/questions/11107536/convert-string-to-type-in-c-sharp can you explain this a little bit please – Tan Jan 31 '16 at 22:35
i am posting my screen then you will understand what i am asking for – Tan Jan 31 '16 at 22:37
can you check i have added image – Tan Jan 31 '16 at 22:47
Are you trying to get the datatypes of your own classes or intrinsic datatypes like int, string and datetime? – Vivek Verma Jan 31 '16 at 22:53
I don't see an image. – Vivek Verma Jan 31 '16 at 22:54
i am trying to get intrinsic datatype – Tan Jan 31 '16 at 22:59
Since you are using intrinsic datatypes, you don't need GetType. You can convert the value of the contents of label or textbox like I showed you in my post. Just use DateTime.TryParse instead for cdt. Or you can use Convert.ToDateTime. – Vivek Verma Jan 31 '16 at 23:11

Reza Aghaei · Accepted Answer · 2016-02-01T00:49:54.183

Find the Type

To convert a string to target type having type name, if you are using the full type name like System.String and System.Int32 you can simply use Type.GetType(typeName). For example:

var type  = Type.GetType("System.Int32");

If you are not using full type names and you use friendly type names like string and int, you can create a dictionary of types and get the type by it's name from dictionary, for example:

var types = new Dictionary<string, Type>();
types.Add("bool", typeof(bool));
types.Add("int", typeof(int));
types.Add("string", typeof(string));
//and so on for byte, short, long, float, double, decimal, ...

then:

var type = types["int"];

Convert the string value to target type

You can use Convert.ChangeType(value, conversionType) method to change the value to conversion type. You should also handle probable FormatException when the value is not convertible to target type. For example:

var value = Convert.ChangeType("1", type);

Add parameter to command

You can add parameters to your the Parameters collection of your command using AddWithValue (parameterName, value) method and pass parameter name and the value you converted from the string. For example:

command.Parameters.AddWithValue("@Id", value);

Note

If you want to continue creating query using string concatenation like last code section of your question (which suffers from SQL Injection vulnerability) you don't need to any type cast because you will use only string values, for example "Id=1" is created using "Id=" + textBox1.Text and no type cast is needed. But I strongly recommend to stop creating queries using string concatenation.

you are right, i have wasted 1 hr on this, no type casting needed when building query as i am doing. i know building query lead to sql injection. thaks for your suggestion — Tan, Jan 31 '16 at 23:13

type cast textbox value dynamically to the data type value of a table column

2 Answers2