Unable to connect to a Local Database

Question

I am playing with C# and a local database (An empty SQL Server Compact Edition database for local data)

But I am unable to connect to the database and get data.

This is what I try:

// Properties.Settings.Default.DatabaseConnectionString = Data Source=|DataDirectory|\Database.sdf
// I guess Visual Studio put it there after I created my database...

using(SqlConnection sqlConnection = new SqlConnection(Properties.Settings.Default.DatabaseConnectionString)) {
    using(SqlCommand sqlCommand = new SqlCommand("SELECT * FROM users WHERE id = @id", sqlConnection)) {
        sqlCommand.CommandType = CommandType.StoredProcedure;
        sqlCommand.Parameters.AddWithValue("@id", 1);
        try {
            sqlConnection.Open();
            SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow);
            if(reader.Read()) {
                System.Console.WriteLine(reader);
                System.Console.WriteLine(reader["id"]);
                System.Console.WriteLine(reader["name"]);
            }
        }
        catch(Exception e) {
            System.Console.WriteLine(e.GetBaseException());
        }
        finally {
            sqlConnection.Close();
        }
    }
}

My whole programm hangs for a while, after the pause I get this message:

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)

You have to be sured that the `named pipes` are turned - "ON" — Maris, Aug 02 '13 at 13:22
@SonerGönül Well, Visual Studio put it there, so I guess it belongs there? — Ron van der Heijden, Aug 02 '13 at 13:24
above error 100% related to connectionstring I add `Database.connectionString` to qustion — Anant Dabhi, Aug 02 '13 at 13:26
@AnantDabhi Yes, it came from a class Database with a static string.. I changed it in the example to the Property settings made by Visual Studio. — Ron van der Heijden, Aug 02 '13 at 13:29

score 3 · Accepted Answer · edited May 23 '17 at 12:28

I am playing with C# and a local database (An empty SQL Server Compact Edition database for local data)

You are using a Sql Server Compact file, not a Sql Server Local DB

To deal with Sql Server Compact, you need to use the System.Data.SqlServerCe namespace, not System.Data.SqlServer.

Replace SqlConnection, SqlCommand, ...

With SqlceConnection, SqlCeCommand, ...

And Stored procedures are not supported in Sql Server Compact (How to use Stored Procedure in SqlCE), so sqlCeCommand.CommandType can not be CommandType.StoredProcedure.

You need to use commandType.Text with command parameters.

using(SqlCeConnection sqlCeConnection = new SqlCeConnection(Properties.Settings.Default.DatabaseConnectionString)) {
    using(SqlCeCommand sqlCeCommand = new SqlCeCommand("SELECT * FROM users WHERE id = @id", sqlCeConnection)) {
        sqlCeCommand.CommandType = CommandType.Text;
        sqlCeCommand.Parameters.AddWithValue("@id", 1);
        try {
            sqlCeConnection.Open();
            SqlCeDataReader reader = sqlCeCommand.ExecuteReader(CommandBehavior.SingleRow);
            if(reader.Read()) {
                System.Console.WriteLine(reader);
                System.Console.WriteLine(reader["id"]);
                System.Console.WriteLine(reader["name"]);
            }
        }
        catch(Exception e) {
            System.Console.WriteLine(e.GetBaseException());
        }
        finally {
            sqlCeConnection.Close();
        }
    }
}

Thanks! So my database type is wrong. What type do I need to have a local database using prepared statements? The Express Edition? — Ron van der Heijden, Aug 02 '13 at 14:14
@Bondye I am not sure I undestand what you are looking for. What do you mean by 'using prepared statements'? — Chris, Aug 02 '13 at 14:19
Uhmm.. Just [Prepared statements](http://en.wikipedia.org/wiki/Prepared_statement#C.23_ADO.NET) to prevent some injections and overheading. — Ron van der Heijden, Aug 02 '13 at 14:22
@Bondye OK... So just create your Database from Visual Studio or copy the existing Database in your project and let Visual Studio Create typed Dataset and appropriate code. Make sense? — Chris, Aug 02 '13 at 14:32

Alex Filipovici · Answer 2 · 2013-08-02T14:14:42.793

You need to use:

using System.Data.SqlServerCe;

And then

using (SqlCeConnection sqlConnection = new SqlCeConnection(Properties.Settings.Default.DatabaseConnectionString))
{
    using (SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM users WHERE id = @id", sqlConnection))
    {
        sqlCommand.CommandType = CommandType.Text;
        sqlCommand.Parameters.AddWithValue("@id", 1);
        try
        {
            sqlConnection.Open();
            SqlCeDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow);
            if (reader.Read())
            {
                System.Console.WriteLine(reader);
                System.Console.WriteLine(reader["id"]);
                System.Console.WriteLine(reader["name"]);
            }
        }
        catch (Exception e)
        {
            System.Console.WriteLine(e.GetBaseException());
        }
        finally
        {
            sqlConnection.Close();
        }
    }
}

Unable to connect to a Local Database

2 Answers2