编写SQL OleDbCommand以将数据从SqlServer导入Access数据库

Question

我有以下代码：

ADOX.Catalog cat = new ADOX.Catalog();
string pathToNewAccessDatabase = "Data Source=D:\Data\NewMDB.mdb";
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + pathToNewAccessDatabase + ";Jet OLEDB:Engine Type=5");
System.Data.OleDb.OleDbConnection AccessConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + pathToNewAccessDatabase);
AccessConn.Open();
System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO [ReportFile] FROM [Data Source=server_path; Initial Catalog=database_name; User Id=user_name; Password=pass_word;Trusted_Connection=False].[dbo.DataSourceTable]", AccessConn);
AccessCommand.ExecuteNonQuery();
AccessConn.Close();

我想从SQL SERVER中选择ACCESS数据库。

此外，如果密码包含[字符，我该如何转义？

Answer 1

我建议首先建立一个到SQL服务器的SQLConnection，并将您想要的数据查询到DataTable中。

using (SqlConnection conn = new SqlConnection("yourConnectionString"))
{
    using (SqlCommand comm = new SqlCommand("Select columns from targetTable", conn))
    {                    
        SqlDataReader reader = comm.ExecuteReader();
        DataTable tbl = new DataTable();
        tbl.Load(reader);
    }
}

在数据表中有了数据后，通过循环数据为insert命令创建查询。

string insertCommandString = string.Empty;
for (int row = 0; row < tbl.Rows.Count; row++)
{
    insertCommandString = "Insert into yourTableName(yourColumnNames) values(";
    for (int column = 0; column < tbl.Columns.Count; column++)
    {
        if(tbl.Columns[column].DataType == typeof(String))
        {
            insertCommandString += "'" + tbl.Rows[row][column].ToString() + "'";
        }
        else
        {
            insertCommandString += tbl.Rows[row][column].ToString();
        }
        if (column < tbl.Columns.Count - 1)
        {
            insertCommandString += ",";
        }
   }
   insertCommandString += ")";
   System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand(insertCommandString, AccessConn);
   AccessCommand.ExecuteNonQuery();
 }

您不应该从密码中转义任何字符。