Populate a column created by ADOX with a list C#

Question

I created a table in my database with ADOX. I wish to populate my columns with a list. How can I do this? One list is a string which should populate "ScheduleName" column and one list is an integer list which should populate "SchedulePace" column. Here is how I am creating my table:

public partial class ScheduleStart : Form
{
    const string _ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\A2 Computing\C# Programming Project\TriHard.accdb";

    private Catalog OpenDatabase()
    {
        Catalog catalog = new Catalog();
        Connection connection = new Connection();

        try
        {
            connection.Open( _ConnectionString);
            catalog.ActiveConnection = connection;
        }
        catch (Exception)
        {
            catalog.Create(_ConnectionString);
        }
        return catalog;
    }


    private void button1_Click(object sender, EventArgs e)
    {
        // Only for demonstration purposes, no error checks:
        // This code will only work as long as the table "Publisher" does not exist

        // First create an new database if necessary
        Catalog cat = OpenDatabase();

        // Create a new table "Publisher" using ADOX
        Table table = new Table();
        table.Name = "ScheduleEvent";
        cat.Tables.Append(table);

        // Add Column "ScheduleID" with Autoincrement
        ADOX.Column col = new Column();
        col.Name = "ScheduleID";
        col.ParentCatalog = cat;
        col.Type = ADOX.DataTypeEnum.adInteger;
        col.Properties["Nullable"].Value = false;
        col.Properties["AutoIncrement"].Value = true;
        table.Columns.Append(col);

        // Add column "ScheduleName"
        col = new Column();
        col.Name = "ScheduleName";
        col.ParentCatalog = cat;
        col.Type = ADOX.DataTypeEnum.adWChar;
        col.DefinedSize = 50;
        col.Attributes = ColumnAttributesEnum.adColNullable;
        table.Columns.Append(col);

        // Add column "SchedulePace"
        col = new Column();
        col.Name = "SchedulePace";
        col.ParentCatalog = cat;
        col.Type = ADOX.DataTypeEnum.adInteger;
        col.DefinedSize = 50;
        col.Attributes = ColumnAttributesEnum.adColNullable;
        table.Columns.Append(col);

        // Make "PublisherID" the primary key
        ADOX.Index index = new ADOX.Index();
        index.PrimaryKey = true;
        index.Name = "PK_ScheduleEvent";
        index.Columns.Append("ScheduleID", table.Columns["ScheduleID"].Type, table.Columns["ScheduleID"].DefinedSize);
        table.Indexes.Append(index);

        MessageBox.Show("The Schedule table has been created");

    }

    public ScheduleStart()
    {
        InitializeComponent();
    }

So this creates the table and the primary key increments itself when I add new values. I wish to populate the other two fields with lists I have created. How can I do this?

score 0 · Answer 1 · answered Feb 17 '15 at 14:15

Once your table has been created you can use either System.Data.OleDb or System.Data.Odbc to open a .Connection, then use a prepared statement (parameterized query) to

INSERT INTO ScheduleEvent (ScheduleName, SchedulePace) VALUES (?, ?)

There are lots of examples here on Stack Overflow so you shouldn't have any trouble finding one.

Populate a column created by ADOX with a list C#

1 Answers1