如何从SQL Server导出数据压缩到访问MDB [英] How to Export Data from SQL server Compact To Access MDB
问题描述
我需要一个解决方案来将所有数据从SQL Server CE传输到Access mdb数据库。
我尝试了这种方法 http://www.codeproject.com/Answers/483989/HowplustoplusExportplusSQLplusTablesplusToplusAcce#answer2 （解决方案＃2），但收到错误否数据库在连接字符串或IN子句中指定。
如果我连接到非紧凑SQL服务器，代码可以工作。
这是我的代码：
$ b我想知道IN子句中的连接字符串的问题，但我不知道如何更改它。$ b
private void ExportTable（string tableName，string source，string destination）
{
var connStr = string.Format（Provider = Microsoft.Jet.OLEDB.4.0; Data Source = {0}，目的地）;
var cmdText = string.Format（SELECT * INTO {0} FROM [{0}] IN''[Data Source = {1}; Max Database Size ='4000'; Persist Security Info = False; ;，tableName，source）;
using（var conn = new OleDbConnection（connStr））
{
conn.Open（）;
using（var cmd = new OleDbCommand（cmdText，conn））
{
cmd.ExecuteNonQuery（）; //这行上的错误
}
conn.Close（）;
}
}
连接字符串：Data Source = {1}; Max Database Size ='4000'; Persist Security Info = False;当我直接连接到数据库时，可以正常工作。
更新： IN条款中源数据库的格式应该如下所示： br>
[类型; DATABASE = 路径]
当我使用
var cmdText = string.Format（SELECT * INTO {0} FROM [{0 }] IN''[SqlServer CE; DATABASE = {1}];，tableName，source）;
我收到不同的错误：找不到可安装的ISAM。
您知道SQLServer CE的正确类型吗？是否支持？我找不到任何有关它的信息。
我也尝试过：SQL CE，SQLSERVER.CE，Microsoft.SQLSERVER.CE.OLEDB.3.5，Microsoft.SQLSERVER。 MOBILE.OLEDB.3.0等 - 相同的错误...
我认为这里的绊脚石是你的技巧尝试使用需要ODBC连接到SQL Server，据我所知，SQL Server Compact没有ODBC驱动程序。我非常确定Access中的语法 [ODBC; Driver = ...] 没有OLEDB等效，所以这个技巧将不能用于SQL Server Compact。 （正如你所发现的那样，它使用真正的SQL Server，因为该平台支持ODBC连接。）
我很好奇看看我可以在C＃中使用OLEDB连接到SQL Server Compact数据库来完成什么（这是支持，正如@MrZak在他的评论中指出的那样）。我想出了以下几点。它将SQL表拉入DataTable，将每行的状态设置为已添加，然后在Access中更新（插入）相应的表。
string myConnectionStringMDB =
Provider = Microsoft.ACE.OLEDB.12.0; +
@Data Source = C：\Users\Gord\Desktop\fromCE.mdb;;
string myConnectionStringSQL =
Provider = Microsoft.SQLSERVER.CE.OLEDB.3.5; +
@Data Source = C：\Users\Public\test\myData.sdf;;
使用（OleDbConnection conSQL = new OleDbConnection（），
conMDB = new OleDbConnection（））
{
conSQL.ConnectionString = myConnectionStringSQL;
conSQL.Open（）;
conMDB.ConnectionString = myConnectionStringMDB;
conMDB.Open（）;
使用（OleDbCommand cmdSQL = new OleDbCommand（），
cmdMDB = new OleDbCommand（））
{
cmdSQL.CommandType = System.Data.CommandType.Text;
cmdSQL.Connection = conSQL;
cmdSQL.CommandText =SELECT * FROM [Table1];
var daSQL = new System.Data.OleDb.OleDbDataAdapter（cmdSQL）;
var dt = new System.Data.DataTable（）;
daSQL.Fill（dt）;
foreach（dt.Rows中的System.Data.DataRow dr）
{
//将行状态从不变更改为已添加，所以下面的更新将插入它们
dr.SetAdded（）;
}
cmdMDB.CommandType = System.Data.CommandType.Text;
cmdMDB.Connection = conMDB;
cmdMDB.CommandText =SELECT * FROM [Table1];
var daMDB = new System.Data.OleDb.OleDbDataAdapter（cmdMDB）;
var cbuilderMDB = new OleDbCommandBuilder（daMDB）;
cbuilderMDB.QuotePrefix =[;
cbuilderMDB.QuoteSuffix =];
daMDB.Update（dt）;
}
conSQL.Close（）;
conMDB.Close（）;
}
I need a solution to transfer all data from SQL Server CE to Access mdb database.
I tried this approach http://www.codeproject.com/Answers/483989/HowplustoplusExportplusSQLplusTablesplusToplusAcce#answer2 (solution # 2) but getting an error "No database specified in connection string or IN clause."
The code works if I connect to non-compact SQL server.
I guess the problem is with connection string in IN clause but I cannot figure out how to change it.
Here is my code:
private void ExportTable(string tableName, string source, string destination)
{
var connStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", destination);
var cmdText = string.Format("SELECT * INTO {0} FROM [{0}] IN ''[Data Source={1};Max Database Size='4000';Persist Security Info=False;];", tableName, source);
using (var conn = new OleDbConnection(connStr))
{
conn.Open();
using (var cmd = new OleDbCommand(cmdText, conn))
{
cmd.ExecuteNonQuery(); // error on this line
}
conn.Close();
}
}
The connection string: Data Source={1};Max Database Size='4000';Persist Security Info=False; working ok when I connect to the database directly.
UPDATE: Apparently the format of the source DB in IN Clause should be as following:
[type; DATABASE = path]
When I use
var cmdText = string.Format("SELECT * INTO {0} FROM [{0}] IN ''[SqlServer CE; DATABASE={1}];", tableName, source);
I am getting different error: Could not find installable ISAM.
Do you know correct type for SQLServer CE? Is it supported at all? I could not find any info about it.
I have also tried: SQL CE, SQLSERVER.CE, Microsoft.SQLSERVER.CE.OLEDB.3.5, Microsoft.SQLSERVER.MOBILE.OLEDB.3.0 etc. - Same error...
I think the stumbling block here is that the trick you are trying to use requires an ODBC connection to the SQL Server and as far as I know there is no ODBC driver for SQL Server Compact. I'm pretty sure that the syntax [ODBC;Driver=...] in Access has no OLEDB equivalent, so the trick won't work with SQL Server Compact. (As you discovered, it does work with "real" SQL Server because ODBC connections are supported for that platform.)
I was curious to see what I could accomplish in C# using an OLEDB connection to the SQL Server Compact database (which is supported, as @MrZak pointed out in his comment). I came up with the following. It pulls the SQL table into a DataTable, sets the status of each row to "Added", and then updates (inserts into) the corresponding table in Access.
string myConnectionStringMDB =
"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source=C:\Users\Gord\Desktop\fromCE.mdb;";
string myConnectionStringSQL =
"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" +
@"Data Source=C:\Users\Public\test\myData.sdf;";
using (OleDbConnection conSQL = new OleDbConnection(),
conMDB = new OleDbConnection())
{
conSQL.ConnectionString = myConnectionStringSQL;
conSQL.Open();
conMDB.ConnectionString = myConnectionStringMDB;
conMDB.Open();
using (OleDbCommand cmdSQL = new OleDbCommand(),
cmdMDB = new OleDbCommand())
{
cmdSQL.CommandType = System.Data.CommandType.Text;
cmdSQL.Connection = conSQL;
cmdSQL.CommandText = "SELECT * FROM [Table1]";
var daSQL = new System.Data.OleDb.OleDbDataAdapter(cmdSQL);
var dt = new System.Data.DataTable();
daSQL.Fill(dt);
foreach (System.Data.DataRow dr in dt.Rows)
{
// change row status from "Unchanged" to "Added" so .Update below will insert them
dr.SetAdded();
}
cmdMDB.CommandType = System.Data.CommandType.Text;
cmdMDB.Connection = conMDB;
cmdMDB.CommandText = "SELECT * FROM [Table1]";
var daMDB = new System.Data.OleDb.OleDbDataAdapter(cmdMDB);
var cbuilderMDB = new OleDbCommandBuilder(daMDB);
cbuilderMDB.QuotePrefix = "[";
cbuilderMDB.QuoteSuffix = "]";
daMDB.Update(dt);
}
conSQL.Close();
conMDB.Close();
}
这篇关于如何从SQL Server导出数据压缩到访问MDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
