Problems Connecting to a SQL Compact DB created in C# Express 2008

[MichaelR87]

Good afternoon All,

I have a query which has been plaguing me for quite a while.

I am trying to open a database that I have created on my local machine in Visual C# 2008 here. I can connect and manipulate it in C# but when I try to access it with my developed application I run into errors.

Currently on my main form, my code to access the support.sdf log looks like this:

public Client_main()
{
InitializeComponent();

SqlConnection sqlconn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["DBCONNECTION"]);

try
{

sqlconn.Open();
}
catch (Exception e)
{

MessageBox.Show("Error: " + e);
}
}

I am using System.Data.Sqlclient and System.Configuration in which has been added to the project as well.

Now the contents of my App.config file that is part of the project looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="DBCONNECTION" connectionString="Data Source=|DataDirectory|\support.sdf;Persist Security Info=False"
providerName="Microsoft.SqlServerCe.Client.3.5" />
</connectionStrings>
</configuration>


I thought with these two together, I have the right call to open up the database and begin to manipulate it with my program. Unfortunately I am given the exception error:

Error: System.InvalidOperationException: The ConnectionString property has not been initialized.
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection out Connection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open().

These errors are reported in the constructor of which I have posted above. This is really confusing me, I have looked at multiple tutorials and believe that I have the right connection string that should Initialise a proper connection.

Any advice or hints would be greatly appreciated.

Regards,

Michael

[TheCPUWizard]

Are the SQL Libraries (.dlls) being properly deployed?

[MichaelR87]

Hmm looking through at what I references I have imported with my Solution. I have the System.Data.SqlServerCe imported. I thought I would try and import the System.Data.SqlClient which I use with my statement

using System.Data.SqlClient;

but this is not in my list of references to add to the project. Although I already have referenced the System.Data .dll file into the solution as well.

This seems to be unusual I think, but then again I have never touched C# with any form of Database attached before.

[MichaelR87]

If anyone has a work around to this issue or could suggest another way that I could go about connecting a database it would be greatly appreciated!

It seems unusual that I can successfully test a connection fine but then cannot connect due to initialization errors. But I am assuming that the same connection string being used to test is that of what is located in the app.config file as part of my solution file.

Regards,

Michael.

[dlarkin77]

Try using a SqlCeConnection instead of a SqlConnection

SqlCeConnection sqlconn = new SqlCeConnection(System.Configuration.ConfigurationManager.AppSettings["DBCONNECTION"]);

[MichaelR87]

Thanks dlarkin77,

I will give this ago when the chaos in this office calms down!

[Arjay]

Say shouldn't you be using the ConnectionString entry?

ConfigurationManager.ConnectionStrings[ "DBCONNECTION" ].ConnectionString;

rather than the AppSettings entry

ConfigurationManager.AppSettings["DBCONNECTION"]

Unless you need to hold the database connection open, consider wrapping the db code inside a 'using' block. It will close the connection when the block goes out of scope (ditto for SqlCommand too).

using( SqlConnection sqlconn = new SqlConnection( ... ) )
{
  // use the database connection

  using( SqlCommand cmd = new SqlCommand( ... ) )
  {
    // use the sql command here
  } // SqlCommand closed automatically

} // Connection closed automatically

I would also either print out the connection string after retrieving it with a MessageBox or temporarily hardcode the full path to the sdf file (as installed on the CE machine).

[MichaelR87]

Hmmm I am still trying to see how to fix this situation, I have decided to rework this a bit.

I created another test Database that is empty but used to try and test the connectivity when the application is launched.

This is the code that I use:

public Client_main()
{
InitializeComponent();

SqlConnection sqlconn = new SqlConnection("Data Source=local;Initial Catalog=ITSUPPORT;");

MessageBox.Show(Convert.ToString(sqlconn));

//SqlDataReader rdr = null;

//try
//{

// sqlconn.Open();
//}
//catch (Exception e)
//{

// MessageBox.Show("Error: " + e);
//}
}


It can be seen where I have commented out the code and have used the messagebox to show the actual connection string.

Unforunately my connection string reads as: System.Data.SqlClient.SqlConnection

It does not seem to contain my connection string!

[MichaelR87]

Definately my Ignorance. It turns out that SqlCeConnection was required instead of SqlConnection .... sigh.

But works now, I can get back to coding the rest of it