how to get the data type of a field of a table in a database(Oracle and SqlServer)

Question

Hi all while doing programming in asp.net 3.5 using c#i am stuck with a problem that how we can explicitly and dynamically know the datatype of a field of a table in a database and i want to store the datatype in a variable.In the method that i want to define i will pass the connection stringtable name and field nameand in return the method will give me the datatype ? plz explain for Oracle and SQL Server database. Thanks

Kirtan Patel · Answer

In SQL Server you can use sp_columns 'Table_Name_Here' which will produce result like below In Oracle you can Use Desc TableName or If you want only one Column then you have to do complex query to System Object

Kumar AG · Answer

Thats what I said Mr. Kirtan you very well know what datatype you are sending to database before sending you are validating the data and there is no question of roundtrip if you are not validating then you may consider For Info read your post: http://www.dotnetheaven.com/Forums/ShowMessages.aspx?ThreadID=62527 MSDN: AddWithValue replaces the SqlParameterCollection.Add method that takes a String and an Object . The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value. Use AddWithValue whenever you want to add a parameter by specifying its name and value.

Kirtan Patel · Answer

@Kumar AG DataTable will Give you data type but it will not the actual one that SQL Server Uses ..because data-table is not for Specif SQL Server .and also note that there is Diffrence in Using .AddWithValue and Add() methods of parameters ..that AddWithValue takes Values as Object Type while Add ensures first before making Round Trip to Server

Kumar AG · Answer

Validating the user input datatype with the database column's datatype should not be done at runtime. This should be done at design time. For example if you have a filed for Name your application should accept string values if you have salary field database datatype would be either money data type or numeric datatype so your corresponding application datatype should be decimal. For each data type in database there is a corresponding datatype in .net. When user enters a salary in a text box validate the user input whether it is numeric or not (you can use Decimal.TryParse to validate user input). If it is not numeric display an error message. For this you can use Validating Event of the text box. As Kirtan said when you are sending the values to database send as parameters. You can use Command.Parameters.AddWithvalue method. AddWithValue method will convert the datatype of the value to Database column datatype you don't need to explicitly specify the datatype. Hope this helps Hiren Soni was saying about getting the name and datatype of a database column. That is the easiest way to know the column definition. For more info please read help on DataTable

Kirtan Patel · Answer

Friend You dont need to add this Feature in DLL as you can use directly in code ..Your Parameter of Query will ensure that data is matching with database Field or not and According to it it will generate exceptions . SqlConnection con = new SqlConnection ( You connection String here ); con.Open(); SqlCommand comm = new SqlCommand ( insert into Users(Username) values(@username)  con); //This Will Ensure that TextBox Value is Matched With Database Field or not if not Then Willl generate Exception try { comm.Parameters.Add( @username  SqlDbType .VarChar 20 textBox1.Text); comm.ExecuteNonQuery(); } catch { MessageBox.Show(Not Valid data is Entred!!); } con.Close(); The code Hiren Soni Provide is for DataTable DataType not for Ensuring that the data is valid or not as Matching with SQL database Field.

bootstrap · Answer

helloplease explain with some details what is written in this code. Datacolumn col; For each col in dataset.table[0].columns Console.writeline( {0} 	 {1} col.columnname col.datatype);

bootstrap · Answer

yes i mean that when user enters information in TextBox then it Should be Validated that Its Maching with The Database's Field or not ? I want to create this method in a class library that i will use in many projects.

Kumar AG · Answer

Thats what Hiren Soni replied to you.

Kirtan Patel · Answer

you mean that when user enters information in TextBox then it Should be Validated that Its Maching with The Database's Field or not ? is this you want to do ??

bootstrap · Answer

hi that's fine .but i want to store the datatype of a field in a variable dynamically.I want to create a method in a dll filethat will validate a text box. that i will use it for more than one project. so how can i store the datatype of a field in a variable in sqlserver and oracle both?

Hiren Soni · Answer

Datacolumn col; For each col in dataset.table[0].columns Console.writeline( {0} 	 {1} col.columnname col.datatype); Got it answer?