Adding New Field In Database

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.