如何使用c#向ms访问表添加数据表值

Question

我在SQL服务器中得到了10个表，我想将这些表值导出到一个ms访问数据库，其中也包含10个表。那么有没有办法使用c#代码导出数据呢?我不想为每个表编写大量代码，从特定表中获取数据，然后逐行插入到各自的访问表，是否有任何快捷方式可用，我可以使用，请让我知道。

Answer 1

我想这段代码对你有帮助。

OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="Database.accdb";Persist Security Info=False;");
                    //command to insert each ASIN
                    OleDbCommand cmd = new OleDbCommand();
                    //command to update each column (ASIN, Retail... from CSV)
                    OleDbCommand cmd1 = new OleDbCommand();
                    //load csv data to dtCSV datatabe
                    DataTable dtCSV = new DataTable();
                    dtCSV = ds.Tables[0];
                    // Now we will collect data from data table and insert it into database one by one
                    // Initially there will be no data in database so we will insert data in first two columns
                    // and after that we will update data in same row for remaining columns
                    // The logic is simple. 'i' represents rows while 'j' represents columns
                    cmd.Connection = myConnection;
                    cmd.CommandType = CommandType.Text;
                    cmd1.Connection = myConnection;
                    cmd1.CommandType = CommandType.Text;
                    myConnection.Open();
                    for (int i = 0; i <= dtCSV.Rows.Count - 1; i++)
                    {
                        cmd.CommandText = "INSERT INTO " + lblTable.Text + "(ID, " + dtCSV.Columns[0].ColumnName.Trim() + ") VALUES (" + (i + 1) + ",'" + dtCSV.Rows[i].ItemArray.GetValue(0) + "')";
                        cmd.ExecuteNonQuery();
                        for (int j = 1; j <= dtCSV.Columns.Count - 1; j++)
                        {
                            cmd1.CommandText = "UPDATE " + lblTable.Text + " SET [" + dtCSV.Columns[j].ColumnName.Trim() + "] = '" + dtCSV.Rows[i].ItemArray.GetValue(j) + "' WHERE ID = " + (i + 1);
                            cmd1.ExecuteNonQuery();
                        }
                    }
                    myConnection.Close();

Answer 2

如果是一次性操作，请使用SQL Server的数据导出特性或Access的导入特性来执行此操作。对于不想编写代码的最终用户来说，它们足够简单。在这里，您只需选择数据库，要导出的表和目标数据库，然后单击按钮即可导出所有内容。

Answer 3

首先确保访问表列具有相同的列名和相似的类型。然后你可以使用这个函数，我相信它是非常快速和优雅的。

    public void AccessBulkCopy(DataTable table)
    {
        foreach (DataRow r in table.Rows)
            r.SetAdded();
var myAdapter = new OleDbDataAdapter("SELECT * FROM " + table.TableName, _myAccessConn);
        var cbr = new OleDbCommandBuilder(myAdapter);
        cbr.QuotePrefix = "[";
        cbr.QuoteSuffix = "]";
        cbr.GetInsertCommand(true);
        myAdapter.Update(table);
    }

使用此代码

Answer 4

我知道答案了。我使用for循环生成如下所示的查询字符串:

    public void MainAccess(int _i)
    {
        DataTable dt = ds.Tables[_i];
        string sql = "";
        for (int i = 0; i < dt.Rows.Count; i++)
        {
        sql = sql + "INSERT INTO "+ _tableString[_i] + " values('";
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            sql += dt.Rows[i][j].ToString().Trim();
            if (j != dt.Columns.Count - 1)
            {
                sql += "','";
            }
            else
            {
                sql += "')";
            }
        }
        ExecuteQuery(sql);
        sql = null;
    }
}