none
Retrieve data from SQL Server CE 4 in C# code

    問題

  • Hi everyone:

    I'm developing a WPF application with SQL Server CE 4 embedded.  I added the database (.sdf) in my project directory and set the connection string in app.config as follows

    <connectionStrings>
            <add name="ontodbConnectionString"
                connectionString="Data Source=ontodb.sdf;Persist Security Info=False"
                providerName="System.Data.SqlServerCe.4.0" />
        </connectionStrings>

    I then in my code established an SqlConnection in my code as follows

     using (SqlConnection conn = new SqlConnection
                    (ConfigurationManager.ConnectionStrings["ontodbConnectionString"].ConnectionString))
                {                
                    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Concept", conn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
    ....

    When executing to the line da.Fii(dt), it gave me an error message:

    Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

    Can anyone give me some advice of how to solve this problem?  Thanks in advance


    - Andy


    • 已編輯 Andy Chiou 2012年5月29日 上午 07:35
    2012年5月29日 上午 07:34

解答

  • To use ADO.NET against SQL Server Compact, you must add a reference to System.Data.SqlServerCe.dll version 4.0 and use objects like SqlCeConnection, SqlCeDataAdapter etc.


    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    • 已標示為解答 Andy Chiou 2012年5月30日 上午 03:33
    2012年5月29日 上午 08:22
    版主

所有回覆

  • To use ADO.NET against SQL Server Compact, you must add a reference to System.Data.SqlServerCe.dll version 4.0 and use objects like SqlCeConnection, SqlCeDataAdapter etc.


    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    • 已標示為解答 Andy Chiou 2012年5月30日 上午 03:33
    2012年5月29日 上午 08:22
    版主
  • Thanks ErikEJ, this solves my problem and I can connect to the database suceessfully.  But it leads to another problem that after I execute all INSERT statement without any error, the data are not in the database in the Server Explorer.  Any idea how this happens?  Thanks in advance.

    - Andy

    2012年5月30日 上午 03:38
  • Look in your bin/debug folder for a copy of the database file. (suggest you use a full path for your connection string)

    Please mark as answer, if this was it. Visit my SQL Server Compact blog


    2012年5月30日 上午 11:26
    版主