Create Microsoft Access Table from Windows DataGridView DataTable C#

Question

I would like to know how I can create new Microsoft Access Table from Windows DataGridView DataTable in C#.

I already have Database. (E.g. Database.mdb or .accdb)
But I haven't created tables in my Database file.
I already have DataGridView which displays the data in table.
I want to create a new table which is exactly the same as what DataGridView displays.

Please help me solve this problem. I've tried creating empty table. But most of my Database Statements are hard-coded. (E.g. I make all my columns VARCHAR datatypes.) I am really appreciated.

Thank you so much. :)

These are my codes.

public void CreateDatabaseTable(string database, string dbTableName)
    {
        OleDbConnection con;
        OleDbCommand cmd;
        string queryStr = "";

        try
        {
            con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + database);

            con.Open();

            queryStr = getDataGridViewHeaders().ToString();

            cmd = new OleDbCommand("CREATE TABLE " + dbTableName +
                "( [keyID] AUTOINCREMENT PRIMARY KEY NOT NULL," + queryStr + ")", con);

            cmd.ExecuteNonQuery();

            con.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

public string getDataGridViewHeaders()
    {
        int colCount = dataGridView.Columns.Count;
        string headerCols = "";

        if (colCount > 0)
        {
            headerCols = "[" + dataGridView.Columns[0].HeaderText + "]" + " VARCHAR";
        }

        for (int col = 1; col < colCount; col++)
        {
            headerCols = headerCols + " , " + "[" + dataGridView.Columns[col].HeaderText + "]" + "VARCHAR";
        }

        Console.WriteLine(headerCols);
        return headerCols;
    }

can you give some examples of what Database Statements are hard-coded ? — Micah Armantrout
– Micah Armantrout, Commented Feb 28, 2012 at 1:44

Maslow · Accepted Answer · 2012-08-07 13:08:57Z

This post shows the code specifically

Excerpt sample in VB:

' Part 2: Create one Table using OLEDB Provider 
    Dim con As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & databaseName)
    con.Open()
    'Get database schema
    Dim dbSchema As DataTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, tableName, "TABLE"})
  con.Close()

    ' If the table exists, the count = 1
    If dbSchema.Rows.Count > 0 Then
        ' do whatever you want to do if the table exists
    Else
        'do whatever you want to do if the table does not exist
        ' e.g. create a table
        Dim cmd As New OleDb.OleDbCommand("CREATE TABLE [" + tableName + "] ([Field1] TEXT(10), [Field2] TEXT(10))", con)
        con.Open()
        cmd.ExecuteNonQuery()
        MessageBox.Show("Table Created Successfully")
        con.Close()
    End If

Collectives™ on Stack Overflow

Create Microsoft Access Table from Windows DataGridView DataTable C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related