Tag Archives: ExecuteNonQuery

Update Oledb Access columns at run time in C#/.Net


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

How to delete rows using simple sql statement in C#


As we have seen how to delete rows in a dataset  in C#.Net, there are simple ways to do the same using SQL command object. All we need is an sql statement and an Object to do the job.

con.open();Common.cmd = new SqlCommand("delete from purchasepart where billno=" + b1, Common.con);

Common.cmd.ExecuteNonQuery();con.close();

cmd object configure the delete statement and call the ExecuteNonQuery() , and will delete those rows you are specified. Keep in mind that the connection should kept open while the ExecuteNonQuery(); method invoked.