Adding data columns to a disconnected database

[canoewhiteh2o]

I am having trouble adding data columns to a disconnected database. I
first load a datatable using:
private DataTable dtMacros = new DataTable();
private ArrayList macro = new ArrayList();
private SQLiteConnection cn;
private SQLiteDataAdapter adapterMacros;
cn = new SQLiteConnection("Data Source = irDatabase.db3");
SQLiteCommand commandMacros = new SQLiteCommand();
commandMacros.CommandText = "SELECT * FROM irMacros";
adapterMacros = new SQLiteDataAdapter(commandMacros.CommandText,
cn);
SQLiteCommandBuilder builderMacros = new
SQLiteCommandBuilder(adapterMacros);
adapterMacros.Fill(dtMacros);
dtMacros.PrimaryKey = new DataColumn[]
{ dtMacros.Columns["MacroName"] };

I am using an arraylist (macro) to store values for a new row - all
values are strings.

Later I update the data with the following code:
private void UpdateMacroDatabase(ArrayList macro, string macroName)
{
//Add datatable columns if needed for the datatable in
memory
if (dtMacros.Columns.Count <= macro.Count)
{
int i = dtMacros.Columns.Count;
while (i <= macro.Count)
{
dtMacros.Columns.Add("Command" + (i-1).ToString(),
typeof(String));
i++;
}
}
//update the datatable in memory
DataRow row = dtMacros.Rows.Find(macroName);
if (row == null)
{
row = dtMacros.NewRow();
row[0] = macroName;
for (int i = 0; i < macro.Count; i++)
{
row[i+1] = macro;
}
dtMacros.Rows.Add(row);
}
else
{
row["MacroName"] = macroName;
for (int i = 1; i <= macro.Count; i++)
{
row = macro[i - 1];
}
}
adapterMacros.Update(dtMacros);
dtMacros.AcceptChanges();
macro.Clear();
}

The datatable in memory adds the new columns and rows as it should.
The database on disk will add the new rows and will update the
columns, but will not add the new columns.

Thanks for any help.

Regards,
Gary B

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,


You need to add the columns in the "real" DB, which makes me wonder, why
they are not there in the first place?

 

[canoewhiteh2o]

Well they are not there in the first place because I am allowing the
user to increase the number of columns based on user need. I am using
the db to store mouse clicks within a macro.

Anyway, I did figured it out. I threw away the CommandBuilder and
constructed my on sql strings. Probably should have taken that
approach in the first place.