Connect to SQL Server 2012 Database with C# (Visual Studio 2012)

Question

Evening all,

I'm trying to connect to a SQL Server 2012 database from C#. My connection settings when using SQL Server Management Studio are as below:-

Server Type:    Database Engine
Server Name:    Paul-PC\SQLEXPRESS
Authentication: Windows Authentication
Username:   Greyed out
Password:   Greyed out

The name of the database I'm trying to connect to is "testDB".

Here's my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DatabaseConnection
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection("server=localhost;" +
                                       "Trusted_Connection=yes;" +
                                       "database=testDB; " +
                                       "connection timeout=30");
            try
            {
                myConnection.Open();
                MessageBox.Show("Well done!");
            }
            catch(SqlException ex)
            {
               MessageBox.Show("You failed!" + ex.Message);
            }

        }
    }
}

Unfortunately, my code fails to connect with the following error:-

"You failed!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."

Any suggestions? SQL Server is running locally.

1. Does it do the same error if you do `(local)` instead of `localhost` for the server? 2. Can you open the server in Sql Server Management Studio? — Scott Chamberlain, Jun 23 '13 at 20:39
@Shaharyar what do you mean by this? I have checked services.msc and SQL Server is running. — thefragileomen, Jun 23 '13 at 20:41
@ScottChamberlain Yes I can access it through Management Studio and I'm actually logged in when I run the code. I tried changing 'localhost' to 'local' but to no avail — thefragileomen, Jun 23 '13 at 20:41
Does this help? http://stackoverflow.com/questions/449607/point-to-localhost-sqlexpress-using-only-localhost and http://stackoverflow.com/questions/15023131/sql-server-2012-login-to-pc-name-sqlexpress-works-but-not-to-local-local — Chris, Jun 23 '13 at 21:00
What happens if you use `server=(local)\\SQLEXPRESS`? also are you 100% sure you are working on `Paul-PC`? — Scott Chamberlain, Jun 23 '13 at 21:04
@Chris Thanks for the links. I've enabled TCP/IP but still no joy. — thefragileomen, Jun 23 '13 at 21:09
@ScottChamberlain Excellent. "server=(local)\\SQLEXPRESS" solved the problem. Thanks — thefragileomen, Jun 23 '13 at 21:09
@thefragileomen If your questions has been answered then consider marking one of the answers as accepted. — RyanfaeScotland, Mar 24 '15 at 16:40

score 6 · Answer 1 · answered Jun 23 '13 at 21:03

6

In your connection string replace server=localhost with "server = Paul-PC\\SQLEXPRESS;"

answered Jun 23 '13 at 21:03

Milen

8,697
7
43
57

score 3 · Answer 2 · edited Sep 16 '14 at 05:42

I tested all the answers here, but for me, none worked. So I studied a bit the problem, and finally I found the connection string needed. To get this string, you do:
1. in you project name:
a. right click the project name,
b. click Add,
c. select SQL Server Database (obviously you can rename it as you wish).
Now the new desired database will be added to your project.
2. The database is visible in the Server Explorer window.
3. Left click the database name in the Server Explorer window; now check the Solution Explorer window, and you will find the "Connection String", along side with Provider, State, Type, Version.
4. Copy the connection string provided, and put it in the Page_Load method:

string source = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=c:\x\x\documents\visual studio 2013\Projects\WebApplication3\WebApplication3\App_Data\Product.mdf;Integrated Security=True";
SqlConnection conn = new SqlConnection(source);
conn.Open();
//your code here;
conn.Close();

I renamed my database as Product. Also, in the "AttachDbFilename", you must replace "c:\x\x\documents\" with your path to the phisical address of the .mdf file.

It worked for me, but I must mention this method works for VS2012 and VS2013. Don't know about other versions.

It only worked in Visual Studio 3013 Community.While copying connection string from properties dont forget to REMOVE the double quotes after AttachDbFilename that points to the mdf file. If you put that in conn string you will get exception — Nidhin David, Mar 28 '15 at 16:21

score 0 · Answer 3 · edited Jun 24 '13 at 04:38

0

Replacing server=localhost with server=.\SQLEXPRESS might do the job.

edited Jun 24 '13 at 04:38

marc_s

732,580
175
1,330
1,459

answered Jun 23 '13 at 20:42

Dennis Traub

50,557
7
93
108

Thanks Dennis. I tried this but it Visual Studio returned an error when trying to compile - "unrecognised escape sequent". I therefore tried a double backslash but code still doesn't work. – thefragileomen Jun 23 '13 at 20:45
2

the escape sequence thing is because of the single "/", if you're a beginner this website will be a very good reference to different connection strings for any DBMS : http://www.connectionstrings.com/ – joe Jun 23 '13 at 22:42

score 0 · Answer 4 · answered Jun 23 '13 at 21:06

0

Try:

SqlConnection myConnection = new SqlConnection("Database=testDB;Server=Paul-PC\\SQLEXPRESS;Integrated Security=True;connect timeout = 30");

answered Jun 23 '13 at 21:06

Ben Narube

448
3
10

ali · Answer 5 · 2015-01-03T13:50:01.570

0

Note to under

connetionString =@"server=XXX;Trusted_Connection=yes;database=yourDB;";

Note: XXX = . OR .\SQLEXPRESS OR .\MSSQLSERVER OR (local)\SQLEXPRESS OR (localdb)\v11.0 &...

you can replace 'server' with 'Data Source'

too you can replace 'database' with 'Initial Catalog'

Sample:

 connetionString =@"server=.\SQLEXPRESS;Trusted_Connection=yes;Initial Catalog=books;";

edited Jan 03 '15 at 13:50

answered Dec 10 '14 at 20:19

ali

185
1
3

Welcome to Stack Overflow! Please consider including some information about your answer, rather than simply posting code. We try to provide not just 'fixes', but help people learn. You should explain what was wrong in the original code, what you did differently, and why your change(s) worked. – Andrew Barber Dec 10 '14 at 21:15

score -1 · Answer 6 · edited Nov 21 '13 at 13:10

-1

use this style

@"server=.\sqlexpress;"

edited Nov 21 '13 at 13:10

Alexander Vogt

17,879
13
52
68

answered Nov 21 '13 at 12:51

russian

1

this is not the correct way to answer,do answer in some detailed way.and do read FAQ to use stackoverflow in a proper way. – Hamad Nov 21 '13 at 13:14
1

You also may have noticed that pretty much this exact answer was posted [5 months ago](http://stackoverflow.com/a/17265115/237838) – Andrew Barber Nov 21 '13 at 17:04

Connect to SQL Server 2012 Database with C# (Visual Studio 2012)

6 Answers6

Linked