c# - 创建新的数据库并填充另一个数据库

Question

我在 C# windows 程序 中遇到一个问题，那就是我无法使用 2 个数据库表和数据中的简单数据库。在这个项目中，我想打开 Access 数据库 并使用另一个名称创建新的 Access 数据库，但使用相同的表、列和行并填充源源数据库中的数据。

我无法从源数据库中读取并插入到新的目标数据库中。源代码如下，请帮助我完成这个项目，非常感谢。

private void button3_Click(object sender, EventArgs e)
{
    OleDbConnection cn = new OleDbConnection();
    cn.ConnectionString = @"provider=Microsoft.ACE.OLEDB.12.0;" + @"data source=" + openFileDialog1.FileName;
    OleDbCommand cmd = new OleDbCommand();
    cn.Open();
    DataTable table = cn.GetSchema("Tables");

    int i = 0;
    foreach (System.Data.DataRow row in table.Rows)
    {
        if ((string)row["TABLE_TYPE"] == "TABLE")
        {
            comboBox1.Items.Add(row["TABLE_NAME"]);
            Tables[i] = row["TABLE_NAME"].ToString();
            listBox1.Items.Add(Tables[i]);
            i++;
            n++;
        }
    }
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    OleDbConnection conne = new OleDbConnection();
    conne.ConnectionString = @"provider=Microsoft.ACE.OLEDB.12.0;" + @"data source=" + openFileDialog1.FileName;
    conne.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conne;
    DataTable Dt = new DataTable();
    cmd.CommandText = "select * from " + comboBox1.Text;
    OleDbDataAdapter adapter = new OleDbDataAdapter();
    adapter.SelectCommand = cmd;
    adapter.Fill(Dt);
    dataGridView1.DataSource = Dt;
    dataGridView1.Visible = true;
    conne.Close();
}
private void button2_Click(object sender, EventArgs e)
{
    saveFileDialog1.Filter = "accdb|*.accdb";
    saveFileDialog1.Title = "Save Access DataBase File";
    saveFileDialog1.FileName = strFileName;
    saveFileDialog1.ShowDialog();
    System.IO.File.Copy(openFileDialog1.FileName, saveFileDialog1.FileName);

    ADOX.Catalog cat = new ADOX.Catalog();
    cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + saveFileDialog1.FileName);
    Console.WriteLine("Database Created Successfully");
    OleDbConnection connsave = new OleDbConnection();
    connsave.ConnectionString = @"provider=Microsoft.ACE.OLEDB.12.0;" + @"data source=" + saveFileDialog1.FileName;
    connsave.Open();
    OleDbCommand cmdsave = new OleDbCommand();
    cmdsave.Connection = connsave;

    OleDbConnection connopen = new OleDbConnection();
    connopen.ConnectionString = @"provider=Microsoft.ACE.OLEDB.12.0;" + @"data source=" + openFileDialog1.FileName;
    connopen.Open();
    OleDbCommand cmdopen = new OleDbCommand();
    cmdopen.Connection = connopen;

    int i = 0;

    foreach (string strtablename in Tables)
    {
        if (i < n)
        {
            cmdsave.CommandText = "CREATE TABLE [" + Tables[i] + "]";
            cmdsave.ExecuteNonQuery();
            cmdsave.CommandText = "DELETE FROM [" + Tables[i] + "]";
            cmdsave.ExecuteNonQuery();
            cmdopen.CommandText = "SELECT * FROM [" + Tables[i] + "]";
            cmdopen.ExecuteNonQuery();
            cmdsave.CommandText = "INSErT INTO [" + Tables[i] + "]";
            cmdsave.ExecuteNonQuery();                       
            i++;
        }
    }
    connopen.Close();
    connsave.Close();
    textBox2.Text = saveFileDialog1.FileName.ToString();
    MessageBox.Show("DataBase Save Sucessfull in \"" + textBox2.Text + "\"");
}

Answer 1

您可以使用 IN 子句执行 INSERT INTO 语句:

INSERT INTO DestinationTable (DestinationField)
IN '' [;DATABASE= C:\Users\Rob\Documents\Northwind 2007.accdb]
SELECT SourceField FROM SourceTable

他们在这里进行了更详细的介绍: http://blogs.office.com/b/microsoft-access/archive/2009/03/27/accessing-external-data-using-the-in-clause.aspx