Check if column name already exists in Access/oledb

Question

I made a simple Windows forms application which creates a table in the access file(mdb) insert, delete, update columns. (I used OLEDB connection for this.) I wrote this code to insert column.

   con.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = con;
    cmd.CommandText = "alter table [" +tableName + "] add [" + columnName + "] long;
    cmd.ExecuteNonQuery();
    con.Close();

Now i want program to check if column name which user want to insert is already exists in the database. What should i do for that?

Answer 1

For MS-Access:

Since you are doing this at application code, you can wrap it in a try .. catch block. If column exists then it will throw an exception, which you can catch and do whatever needed like

try
{
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = "alter table [" +tableName + "] add [" + columnName + "] long";
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
  //do your processing
}
finally
{
      con?.Close();
}

If using SQL Server then

You can check against sys.columns system view like

SELECT 1 FROM sys.columns 
        WHERE [name] = N'your_new_columnName' 
        AND [object_id] = OBJECT_ID(N'Your_tableName');

Again, yo be better you can consider wrapping this in a stored procedure and call that procedure in your application code like

create procedure usp_altertable(table_name varchar(20), column_name varchar(20))
as
begin
DECLARE @sql varchar(100);
   IF NOT EXISTS(SELECT 1 FROM sys.columns 
            WHERE [name] = N'your_new_columnName' 
            AND [object_id] = OBJECT_ID(N'Your_tableName'))
SET @sql = 'alter table ' +table_name +' add ' + column_name + ' long';
EXEC(@sql);
end

Finally, if table_name and column_name are coming as user input from UI then be wary of SQL Injection and use parameterized query instead.