将MSSQL数据库导出为MS Access .accdb文件

本文关键字：MS Access accdb 文件 MSSQL 数据库 | 更新日期: 2023-09-27 18:09:39

我有一个定期更新数据的Microsoft SQL Server数据库。我想使用c#将所有表(最好是关系)存储到新的Microsoft Access (.accdb)文件。

SQL管理工作室安装在系统上，所以我认为一个解决方案可能是从代码中调用BCP (http://msdn.microsoft.com/en-us/library/ms162802.aspx)，但我还没有弄清楚如何在这种情况下正确使用它。我想有更好的方法不用BCP。

谁能推荐一种实现这一目标的方法?

谢谢

将MSSQL数据库导出为MS Access .accdb文件

可以在Access中导入MSSQL数据;更多信息请访问:http://office.microsoft.com/en-us/access-help/import-or-link-to-sql-server-data-HA010200494.aspx

更新:

或者，您可以选择使用sqldataadapter的所有表来存储数据集中的所有内容，参见:从SQL Server数据库获取数据集

从那里你可以保存数据集作为一个访问数据库文件，参见:c# dataset to access DB

多亏Ferdy的建议，我解决了这个问题。因为它可能对其他人有用，我把我的工作代码示例放在这里:

//The connection strings needed: One for SQL and one for Access
String accessConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:''...''test.accdb;";
String sqlConnectionString = "Data Source=localhost''SQLEXPRESS;Initial Catalog=Your_Catalog;Integrated Security=True";   
//Make adapters for each table we want to export
SqlDataAdapter adapter1 = new SqlDataAdapter("select * from Table1", sqlConnectionString);
SqlDataAdapter adapter2 = new SqlDataAdapter("select * from Table2", sqlConnectionString);
//Fills the data set with data from the SQL database
DataSet dataSet = new DataSet();
adapter1.Fill(dataSet, "Table1");
adapter2.Fill(dataSet, "Table2");
//Create an empty Access file that we will fill with data from the data set
ADOX.Catalog catalog = new ADOX.Catalog();
catalog.Create(accessConnectionString);
//Create an Access connection and a command that we'll use
OleDbConnection accessConnection = new OleDbConnection(accessConnectionString);
OleDbCommand command = new OleDbCommand();
command.Connection = accessConnection;
command.CommandType = CommandType.Text;
accessConnection.Open();
//This loop creates the structure of the database
foreach (DataTable table in dataSet.Tables)
{
    String columnsCommandText = "(";
    foreach (DataColumn column in table.Columns)
    {
        String columnName = column.ColumnName;
        String dataTypeName = column.DataType.Name;
        String sqlDataTypeName = getSqlDataTypeName(dataTypeName);
        columnsCommandText += "[" + columnName + "] " + sqlDataTypeName + ",";
    }
    columnsCommandText = columnsCommandText.Remove(columnsCommandText.Length - 1);
    columnsCommandText += ")";
    command.CommandText = "CREATE TABLE " + table.TableName + columnsCommandText;
    command.ExecuteNonQuery();
}
//This loop fills the database with all information
foreach (DataTable table in dataSet.Tables)
{
    foreach (DataRow row in table.Rows)
    {
        String commandText = "INSERT INTO " + table.TableName + " VALUES (";
        foreach (var item in row.ItemArray)
        {
            commandText += "'"+item.ToString() + "',";
        }
        commandText = commandText.Remove(commandText.Length - 1);
        commandText += ")";
        command.CommandText = commandText;
        command.ExecuteNonQuery();
    }
}
accessConnection.Close();
相关文章：