我怎样才能连接到数据库SDF？无连接字符串我尝试似乎工作 [英] How can I connect to a SDF database? No connection string I try seems to work

问题描述

我试过从字面​​上50+不同的尝试我的连接字符串我的本地数据库和似乎没有任何工作。我基本上只是尝试打开数据库文件，所以我可以在我拿出我的Excel电子表格的数据转储的连接。我使用Visual C＃使脱机WinForm应用程序。

I've tried literally 50+ different attempts at my connection string for my local database and nothing seems to work. I'm essentially just trying to open a connection the database file so I can dump in the data I've pulled out of my excel spreadsheet. I'm using Visual C# making an offline winform application.

不管我尝试在我的app.config什么的连接字符串，它总是在尝试写dReader失败到数据库

No matter what connection string I try in my app.config, it always fails when it tries to write "dReader" to the database.

该错误通常是这取决于我尝试什么字符串：

The error is usually this depending on what string I try:

网络而与SQL Server建立连接时出现 - 相关或实例特定的错误服务器找不到或无法访问验证实例名称是否正确，以及SQL Server配置为允许远程连接（服务提供商：。命名管道提供商，错误：40 - 无法打开到SQL Server的连接）

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

我已经通过许多在线的例子和资源，并没有去似乎工作。 。我希望这里有人能指出为什么它的失败

I've gone through many online examples and resources and none seem to work. I'm hoping someone here can point out why it's failing.

下面是我在它的最新形式的app.config：

Here is my app.config in its latest form:

<connectionStrings>
    <add name="DDP_Project.Properties.Settings.DDP_DatabaseConnectionString"
        connectionString="Data Source=E:\Other DDP Projects\DDP_Project_SDF\DDP_Project\DDP_Database.sdf;"
        providerName="Microsoft.SqlServerCe.Client.3.5" />
</connectionStrings>

下面是我的表单代码：

Here is my form code:

    private void Profiles_Click(object sender, EventArgs e)
    {
        profilesDialog.FileName = "[YOUR_UPLOAD_FILE_HERE]";
        var result = profilesDialog.ShowDialog();

        if (result == DialogResult.OK)
        {
            HandleFileSelection();
        }
    }

    private void HandleFileSelection()
    {
        var file = profilesDialog.FileName;


         // Create a connection to the file datafile.sdf in the program folder
    string dbfile = new System.IO.FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location).DirectoryName + "\\DDP_Database.sdf";
    SqlCeConnection connection = new SqlCeConnection("datasource=" + dbfile);


    string strConnection = ConfigurationManager.ConnectionStrings["DDP_Project.Properties.Settings.DDP_DatabaseConnectionString"].ConnectionString;


        //Create connection string to Excel work book
        string excelConnectionString = string.Format(
            @"Provider=Microsoft.Jet.OLEDB.4.0;
            Data Source=""{0}"";
            Extended Properties=""Excel 8.0;HDR=YES;""", file
        );

        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        OleDbCommand cmd = new OleDbCommand("SELECT [ID],[STATUS],[FAN_NUM],[PROFILE_NAME],[DESTINATION_HOST],[USER_ID],[USER_PASSWORD],[PROTOCOL],[PORT],[PATH],[CONTACT_NAME],[CONTACT_EMAIL],[CONTACT_PHONE],[CONTACT_ALT_PHONE],[CONTACT_CITY],[CONTACT_STATE],[CONTACT_CONTACT_TIME] FROM [Sheet1$]", excelConnection);

        excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();

        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

        sqlBulk.DestinationTableName = "Profiles";
        sqlBulk.ColumnMappings.Add("ID", "ID");
        sqlBulk.ColumnMappings.Add("STATUS", "STATUS");
        sqlBulk.ColumnMappings.Add("FAN_NUM", "FAN_NUM");
        sqlBulk.ColumnMappings.Add("PROFILE_NAME", "PROFILE_NAME");
        sqlBulk.ColumnMappings.Add("DESTINATION_HOST", "DESTINATION_HOST");
        sqlBulk.ColumnMappings.Add("USER_ID", "USER_ID");
        sqlBulk.ColumnMappings.Add("USER_PASSWORD", "USER_PASSWORD");
        sqlBulk.ColumnMappings.Add("PROTOCOL", "PROTOCOL");
        sqlBulk.ColumnMappings.Add("PORT", "PORT");
        sqlBulk.ColumnMappings.Add("PATH", "PATH");
        sqlBulk.ColumnMappings.Add("CONTACT_NAME", "CONTACT_NAME");
        sqlBulk.ColumnMappings.Add("CONTACT_EMAIL", "CONTACT_EMAIL");
        sqlBulk.ColumnMappings.Add("CONTACT_PHONE", "CONTACT_PHONE");
        sqlBulk.ColumnMappings.Add("CONTACT_ALT_PHONE", "CONTACT_ALT_PHONE");
        sqlBulk.ColumnMappings.Add("CONTACT_CITY", "CONTACT_CITY");
        sqlBulk.ColumnMappings.Add("CONTACT_STATE", "CONTACT_STATE");
        sqlBulk.ColumnMappings.Add("CONTACT_CONTACT_TIME", "CONTACT_CONTACT_TIME");

        sqlBulk.WriteToServer(dReader);
        sqlBulk.Close();
        excelConnection.Close();

    }

    private void profilesDialog_FileOk(object sender, EventArgs e)
    {


    }
}

}

推荐答案

试试这个...

首先：

首先创建一个测试方法，你可能请检查您是否可以连接到sqlcedatabase。

Create first a test method which you may check if you can connect to sqlcedatabase.

private void testconnection()
{
  string strConnection = ConfigurationManager.ConnectionStrings["DDP_Project.Properties.Settings.DDP_DatabaseConnectionString"].ConnectionString;                     
  using (var conn = new SqlCeConnection(string.Format("Data Source={0};Max Database Size=4091;Max Buffer Size = 1024;Default Lock Escalation =100;", strConnection)))
  {
     conn.Open();

     try
     {
         //your Stuff                    
     }
     catch (SqlCeException)
     {
         throw;
     }
     finally
     {
         if (conn.State == ConnectionState.Open) conn.Close();
     }
  }
}

二：

Second:

只需加载您的Excel文件数据到一个DataTable和使用的foreach然后将它保存在你的SQL CE数据库文件。

Just Load your excel file Data into a Datatable and use foreach then save it on your sql ce database file..

//Something like
//oledbcon
//oledb dataadapter
//datatable
// dapt.Fill(dt);

foreach(DataRow excel in dt.Rows)
{
    ceCmd.Parameters.AddWithValue("ID",excel["ID"]);
    ceCmd.ExecuteNonQuery();
}

问候

Regards

这篇关于我怎样才能连接到数据库SDF？无连接字符串我尝试似乎工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！