Tag: ExecuteNonQuery

How to add fields to a access table in C# at run time ? This tutorial will share what you need to learn.

.Net Coder

First I have to say that , in this example used the Oledb adapter and connection which is not included in this code. You can check my previous posts on oledb connections(connection and Adapter).

I am going to put a checking section before adding column, others wise C# will catch an error. How ? I can use the Schema of the connection

var schema = con.GetSchema("COLUMNS"); 
                  var col = schema.Select("TABLE_NAME='" + tble + "' AND COLUMN_NAME='" + colname + "'" );

Here using var schema I can check the existence of column and if it found the var column contain a name, that means the length of col will be greater than zero. That make it simple. Let’s complete our function UpdateColumn

public static void UpdateColumn(string tble, string colname, string type)
        {
            OleDbCommand updateField = new OleDbCommand();
            try
            {
                 var schema = con.GetSchema("COLUMNS"); 
                 var col = schema.Select("TABLE_NAME='" + tble + "' AND COLUMN_NAME='" + colname + "'" );

                if(col.Length > 0)
                {
                   // Column exist
                }
                else
                {
                    // Column doesn't exist
                   updateField.CommandText=" ALTER TABLE " + tble +" ADD COLUMN " + colname +  "  "  +  type ;
                   updateField.Connection=con;
                   updateField.ExecuteNonQuery();
                  
                }
            }

Put the function in static class , if you want to use in the entire project

You can Call the Function as follows

 //Update Database
            this.UpdateColumn("Accounts", "extended", "bit");

Remember if you want add a new column to a table with Type of Yes/No, you have to use bit instead of Yes/No or bool.In access table design it will be Yes/No type

That is all I have today