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