CODEWITHSUNDEEP
c#databasesql-server-ce
jlodenius
jlodenius

Reputation: 829

c# database connection string

I'm fairly new to c# and just now started working with databases. I made a local database in my project folder, when I click properties on the database I get "Connection string" as:

Data Source="C:\Users\Documents\Visual Studio 2012\Projects\DataBaseTest\MyDatabase#1.sdf"

My question is simple, how do I create a connection string using that line? cause writing this generates an error for some reason.

SqlConnection con = new SqlConnection(Data Source="C:\Users\Documents\Visual Studio
    2012\Projects\DataBaseTest\MyDatabase#1.sdf");

Upvotes: 2

Views: 10851

Answers (6)

user3794064
user3794064

Reputation: 1

For Local Database in Visual Studio 2012, System.Data.SqlServerCe needs to be imported in the code. I created a console application which will connect to local database that i added from solution explorer tab and the code will look like 

SqlCeConnection con = new SqlCeConnection(@"Data ource=C:\Users\MyComputer\Documents\Visual Studio 2012\Projects\ConsoleApplication4\ConsoleApplication4\Database1.sdf");
            con.Open();

        string sql = "SELECT * FROM Employee";//select query
        string sql1 = "INSERT INTO Employee(Name, Age)VALUES('aaa', 12)";//insert query

                SqlCeCommand cmd = new SqlCeCommand(sql, con);
                SqlCeCommand cmd1 = new SqlCeCommand(sql1, con);
                cmd1.ExecuteScalar(); // executing insert command
                SqlCeDataReader reader = cmd.ExecuteReader();//to read data from table
                while (reader.Read())
                {
                    Console.WriteLine(String.Format("{0}, {1}", reader[0],reader[1]));
                }
               con.Close();

Upvotes: 0

Nasreddine
Nasreddine

Reputation: 37838

You are using a SQL Server Compact edition database and for that you must use SqlCeConnection instead of SqlConnection (Which is used for SQL Server).

You must also escape the back backslashes in your connection string \ becomes this \\ :

SqlCeConnection sqlConnection = new SqlCeConnection("Data Source=C:\\Users\\Documents\\Visual Studio
2012\\Projects\\DataBaseTest\\MyDatabase#1.sdf");

or use a verbatim string :

SqlCeConnection sqlConnection = new SqlCeConnection(@"Data Source=C:\Users\Documents\Visual Studio
2012\Projects\DataBaseTest\MyDatabase#1.sdf");

And if you don't have the necessary libs for using SqlCe in your projects refer to my other answer here :

Download the libs from here Microsoft SQL Server Compact 4.0

  1. Add a reference to System.Data.SqlServerCe.dll to your project
  2. Add this using directive using System.Data.SqlServerCe;
  3. Use SqlCeConnection instead of SqlConnection

Upvotes: 7

Jomy John
Jomy John

Reputation: 6518

For path you can't add backslashes just like that . You need to escape backslashes . There are two ways

to escape backslash

Method #1 - Use @ in the beginning of the path string

SqlConnection con = new SqlConnection(@"Data Source=C:\Users\Documents\Visual Studio
    2012\Projects\DataBaseTest\MyDatabase#1.sdf");

Method #2 - add double backslashes instead of one backslashes 

SqlConnection con = new SqlConnection("Data Source=C:\\Users\\Documents\\Visual Studio
    2012\\Projects\\DataBaseTest\\MyDatabase#1.sdf");

Upvotes: 3

Stephen Walker
Stephen Walker

Reputation: 574

You need to use the @ symbol to show that the string is literal, and ignore special characters.

This page shows samples http://www.dotnetperls.com/string-literal

Upvotes: 2

Roy Dictus
Roy Dictus

Reputation: 33139

You need to escape backslashes (replace backslashes by double backslashes) or put an @ in front of your string to tell C# to take it literally, like so:

new SqlConnection("C:\\Users\\...")

or

new SqlConnection(@"C:\Users\...")

Upvotes: 4

Rab
Rab

Reputation: 35572

SqlConnection con = new SqlConnection(@"C:\Users\Documents\Visual Studio
    2012\Projects\DataBaseTest\MyDatabase#1.sdf");

Upvotes: 2

Related Questions