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 string,table name and field name,and in return the method will give me the datatype ?
plz explain for Oracle and SQL Server database.
Thanks
Loading
Kirtan PatelPosted Nov 30, 2009, 10:12 PM
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 AGPosted Dec 2, 2009, 5:28 PM
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 PatelPosted Dec 2, 2009, 4:45 PM
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 AGPosted Dec 2, 2009, 3:37 PM
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 PatelPosted Dec 2, 2009, 11:44 AM
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();
bootstrapPosted Dec 1, 2009, 10:18 PM
"Datacolumn col; For each col in dataset.table[0].columns Console.writeline(" {0} \t {1}", col.columnname, col.datatype);"
bootstrapPosted Dec 1, 2009, 9:37 PM
I want to create this method in a class library that i will use in many projects.
Kumar AGPosted Dec 1, 2009, 10:26 AM
Kirtan PatelPosted Dec 1, 2009, 10:05 AM
is this you want to do ??
bootstrapPosted Dec 1, 2009, 9:01 AM
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 file,that 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 SoniPosted Nov 30, 2009, 10:01 PM