Adding New Field In Database

By Kunal Vaishya Blogs | SQL Oct 26, 2012
Interesting thing in code to add one field in table once the table is already in the database.
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.
COMMENT USING

PREMIUM SPONSORS

Hire Mobile & Web Developer on demand. 100% satisfaction. Try for 1 week or Money Back. Local and remote developers available all over USA.

Latest Blogs