CODEWITHSUNDEEP
sql-server
jnemecz
jnemecz

Reputation: 3608

SQL Server Compact - connection to SDF data file

I am creating in Visual Studio Express 2012 for web simple CRUD web application (library) as school project. I am inspired by this very nice tutorial - http://www.asp.net/mvc/tutorials/mvc-music-store/mvc-music-store-part-1

Now I have problem with connection to a SQL Server Compact edition .sdf data file.

In web.config I have this connection string:

<connectionStrings>
    <add name="LibraryEntities"
     connectionString="Data Source=C:\Users\Administrator\Documents\Visual Studio 2012\Projects\2OBOP3_KU1\R10491\App_Data\R10491_library.sdf;"
     providerName="System.Data.SqlServerCe.4.0"/>
  </connectionStrings>

I created simple ASPX file for database connection testing:

<script runat="server">
  protected void Page_Load(object sender, EventArgs e)
  {
      using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["LibraryEntities"].ToString()))
      {
          SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Writers", cn);
          cn.Open();
          SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
          rdr.Read();
          Response.Write(rdr[0].ToString()); //read a value
      }
  }
</script>

When I run an application it throws an exception:

Exception Details: System.Data.SqlClient.SqlException: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

But when I try to connect to my .sdf file in "Database explorer" tab in "Database Explorer" menu (next to "Solution explorer") I connect successfully.

So I assuming there should be a problem in connection string - isn't it? Thank you for your advices.

Upvotes: 2

Views: 4510

Answers (1)

Steve
Steve

Reputation: 216273

You are connecting to a SQL Server Compact not to SQL Server.

You should use SqlCeConnection not SqlConnection. 

  using System.Data.SqlServerCe;

  protected void Page_Load(object sender, EventArgs e)
  {
      using (SqlCeConnection cn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["LibraryEntities"].ToString()))
      {
          SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(*) FROM Writers", cn);
          cn.Open();
          SqlCeDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
          rdr.Read();
          Response.Write(rdr[0].ToString()); //read a value
      }
  }

also you need to have a reference to system.data.sqlserverce.dll

Upvotes: 4

Related Questions