Blog

Adding New Field In Database

By Kunal Vaishya Oct 26 2012
Interesting thing in code to add one field in table once the table is already in the database.
    • Like
    • Love It
    • Awesome
    • Interesting
    • It's Okay
    • Thumbs Down
  • 1.6k
  • 0
In most of the product we have use script for updating Tables, views, procedure and functions, I found one very interesting thing in my code when I add one Field in Table once the table is already in the database
 
Here is logic for adding one column
 
tempcmd.CommandText = "SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + table_name + "'";
dr = tempcmd.ExecuteReader();
while (dr.Read())
{
    if (dr["COLUMN_NAME"].ToString().ToUpper() == field_name.ToUpper())
   {
      dr.Dispose();
      return true;
   }
}
dr.Dispose();
tempcmd.CommandText = "Alter Table " + table_name + " Add " + field_name + " " + field_datatype;
tempcmd.ExecuteNonQuery();
return true;
 
Instead of using this Query we have to add one more filteration on Column_Name so that we don't have to Loop.
 
Query = SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tblname' and Column_Name='abc'
 
Secong thing we can increase the Speed of execution.
 
Please reply if any one have any problem in this Condition.