C# .NET - Get data from database, add a column and save...

Asked By Rahim Huseyn on 14-Dec-11 03:24 AM

Hello Friends
OleDbConnection oleconn = new OleDbConnection();

oleconn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ constr;

oleconn.Open();

OleDbCommand olecommand = new OleDbCommand();

olecommand.Connection = oleconn;

olecommand.CommandText = "Select Id, Name, Name2 From Main";

OleDbDataAdapter oleadap = new OleDbDataAdapter();

oleadap.SelectCommand = olecommand;

OleDbCommand command2 = new OleDbCommand("UPDATE Main Set Name2 = ? " + "WHERE ID = ?", oleconn);

command2.Parameters.Add("Name2", OleDbType.VarChar, 120, "Name2");

OleDbParameter parameter = command2.Parameters.Add("oldID", OleDbType.Char, 5, "ID");

parameter.SourceVersion = DataRowVersion.Original;

oleadap.UpdateCommand = command2;

This way I can get data, modify and save them to database.
But I dont know how organize my SelectCommand and UpdateCommand if I added a column to Datatable programatically(dtaccess.Tables[0].Columns.Add("Name3", typeof(string));) to save this(Name3) column to DataBase.

Please Help!!

kalpana aparnathi replied to Rahim Huseyn on 14-Dec-11 03:44 AM
Try this code:


public static OleDbDataAdapter CreateCustomerAdapter(
  OleDbConnection connection)
{
  OleDbDataAdapter adapter = new OleDbDataAdapter();
  OleDbCommand command;
 
  // Create the SelectCommand.
  command = new OleDbCommand("SELECT * FROM main" +
    "WHERE id= ? AND name= ?", connection);
 
  command.Parameters.Add("id", OleDbType.VarChar, 15);
  command.Parameters.Add("name", OleDbType.VarChar, 15);
 command.Parameters.Add("name2", OleDbType.VarChar, 15);
  adapter.SelectCommand = command;
 
  // Create the InsertCommand.
  command = new OleDbCommand(
    "INSERT INTO main(ID, Name,name2) " +
    "VALUES (?, ?,?)", connection);
 
  command.Parameters.Add(
    "ID", OleDbType.Char, 5, "ID");
  command.Parameters.Add(
    "Name", OleDbType.VarChar, 40, "Name");
command.Parameters.Add(
    "Name", OleDbType.VarChar, 40, "Name2");
 
 
  adapter.InsertCommand = command;
  return adapter;
}


private void UpdateContact(Contact contact)
{
  using (OleDbConnection db = new OleDbConnection(_connString))
  {
       string query = "UPDATE [Contact] SET [Name] = ? [Name2] = ?, WHERE [Id] = ?"
 
    OleDbCommand cmd = new OleDbCommand(query, db) { CommandType = CommandType.Text };
    cmd.Parameters.AddWithValue("@Id", contact.Id);
    cmd.Parameters.AddWithValue("@Name", contact.Name);
    cmd.Parameters.AddWithValue("@Name", contact.Name);
     
 
    db.Open();
 
    int rowsAffected = cmd.ExecuteNonQuery();
 
    db.Close();
  }
}
Rahim Huseyn replied to kalpana aparnathi on 14-Dec-11 03:54 AM
I want to add a column to Access.mdb table with C#.
Must I create a new adapter to "Alter..." or can I do it with oleadap(that I created in 1 post)?