Connection to SQL Server Express from C#

Question

I tried to open a connection with SQL Server Express and assign a new record on a specific table in C#.

This code is giving me this error

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

And the following:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

My code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection myConnection = new SqlConnection("Server=HABCHY-PC/SQLEXPRESS;" +
                                    "Trusted_Connection=yes;" +
                                    "Database=mydatabase;" +
                                    "User Instance=true;"+
                                    "Connection timeout=30");
            try
            {
                myConnection.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            } 

            SqlCommand myCommand = new SqlCommand("INSERT INTO students (firstname, age) "+
                    "Values ('string', 1)", myConnection);
            myCommand.ExecuteNonQuery();

            try
            {
                SqlDataReader myReader = null;
                myCommand.CommandText = "select * from students";
                myReader = myCommand.ExecuteReader();

                while (myReader.Read())
                {
                    Console.WriteLine(myReader["firstname"].ToString());
                    Console.WriteLine(myReader["age"].ToString());
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            try
            {
                myConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }
}

Please tell me what's the problem.

you have try catch and even if connection does not open, you proceed to query against this connection. Check if there is no exception while connecting to db — Kamil Budziewski, Apr 02 '14 at 07:11
Well, did it hit your first `catch` block to tell you that it couldn't open? — Marc Gravell, Apr 02 '14 at 07:14
before this line myCommand.ExecuteNonQuery(); wirte myCommand.open(); — user3217843, Apr 02 '14 at 07:15
I would suggest using a using block, that'll save you having to worry about closing your connection if there's an exception — Liath, Apr 02 '14 at 07:16
@user3217843 there's no such thing as an `Open()` method for the `SqlCommand` class. — Abbas, Apr 02 '14 at 07:26
Are you even serious? [SqlCommand class methods](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand_methods(v=vs.110).aspx) — Abbas, Apr 02 '14 at 07:29
@Abbas I think he is confused between `SqlCommand` and `SqlConnection`. — Shell, Apr 02 '14 at 07:31

brothers28 · Answer 1 · 2014-04-02T11:18:29.060

2

I think this should work for you:

using System;
using System.Linq;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        public static SqlConnection GetConnectionsString()
        {
            return new SqlConnection("Server=HABCHY-PC\\SQLEXPRESS;" +
                                       "Trusted_Connection=true;" +
                                       "Database=mydatabase;" +
                                       "User Instance=true;" +
                                       "Connection timeout=30");
        }

        static void Main(string[] args)
        {
            using (SqlConnection myConnection = GetConnectionsString())
            {
                try
                {
                    SqlCommand myCommand = new SqlCommand("INSERT INTO students (firstname, age) " + "Values ('string', 1)", myConnection);
                    Console.WriteLine("ee");

                    myConnection.Open();

                    myCommand.ExecuteNonQuery();

                    SqlDataReader myReader = null;
                    myCommand.CommandText = "select * from students";
                    myReader = myCommand.ExecuteReader();
                    while (myReader.Read())
                    {
                        Console.WriteLine(myReader["firstname"].ToString());
                        Console.WriteLine(myReader["age"].ToString());
                    }

                 }
                 catch (Exception e)
                 {
                     Console.WriteLine(e.ToString());
                 }
             }
         }
     }
 }

As you can see I extracted the SQLConnection in another method. This will help you when you want to make other database requests later. With the using block you can make shure that your connection will be closed even if you got an exeption in your request.

edited Apr 02 '14 at 11:18

answered Apr 02 '14 at 07:25

brothers28

1,196
17
23

2

Why call `myConnection.Close()` when you're using a `using` statement? – Abbas Apr 02 '14 at 07:27
I tried your code, now it doesn't giving me an error and the connection is closed but the exception is still remaining :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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server). Thank you for your help:) – Youmna Habchy Apr 02 '14 at 07:45
Have you checked this? " All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running” " @y.h For more information check out this question http://stackoverflow.com/questions/18060667/connect-to-server-a-network-related-or-instance-specific-error – brothers28 Apr 02 '14 at 08:07
I already checked that the sql service is running and the TCP/IP enabled and the TCP port is 1433 and checked the windows Firewall and added a new rule – Youmna Habchy Apr 02 '14 at 08:26
3

@y.h have you replaced forward slash to backslash in sql instance name. ie. `HABCHY-PC/SQLEXPRESS;` to `HABCHY-PC\SQLEXPRESS;` – Shell Apr 02 '14 at 09:07
I replaced `HABCHY-PC/SQLEXPRESS` by `HABCHY-PC\\SQLEXPRESS` and it works now. Thank you for your help I am using your code istead of mine:) – Youmna Habchy Apr 02 '14 at 09:16
@y.h you can avoid double slash by using `@` at the beginning of the string. for example. `new SqlConnection(@"Server=HABCHY-PC\SQLEXPRESS;...."` – Shell Apr 02 '14 at 09:32

score 1 · Answer 2 · answered Apr 02 '14 at 07:16

May be your problem is here Trusted_Connection=yes; replace it with Trusted_Connection=True;.

if you are trying to use User Instance in connection string then you could use connection string like this

Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=C:\MyFolder\MyDataFile.mdf;User Instance=true;

you can get more connection string sample here

score 1 · Accepted Answer · answered Apr 02 '14 at 08:16

1

Your mixing two approaches:

(1) either you have the database on the server and you access it via its logical database name (my preferred solution) - but in that case, you must not have a User Instance in your connection string - use this:

Server=HABCHY-PC\SQLEXPRESS;Database=mydatabase;Connection timeout=30;Integrated Security=SSPI;

( also note: the SQLEXPRESS instance name should be after a backslash - not a forward slash)

(2) or then you have the database as a on-disk .mdf file and it's not attached to the server (which is a messy and kludgy affair in my opinion). In that case, you have the User Instance, then then you need to specify the .mdf file as a file:

Server=HABCHY-PC\SQLEXPRESS;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;User Instance=true; 
Connection timeout=30;Integrated Security=SSPI;

answered Apr 02 '14 at 08:16

marc_s

732,580
175
1,330
1,459

I removed User Instance and still have the same problem. And concerning the backslash when i put it, it gives me an error:"Error Unrecognized escape sequence". – Youmna Habchy Apr 02 '14 at 08:33
2

@y.h: you need to use either **two** backslashes in your .NET string `Server=HABCHY-PC\\SQLEXPRESS; ....`, or then you need to use the `@` qualifier for the .NET string. That's just a basic .NET "quirk" – marc_s Apr 02 '14 at 09:06
1

OMG that's it. It works for me I put `Server=HABCHY-PC\\SQLEXPRESS;` instead of `Server=HABCHY-PC/SQLEXPRESS;`. Thank you so much:D – Youmna Habchy Apr 02 '14 at 09:10

score 0 · Answer 4 · answered Apr 02 '14 at 07:25

This will probably not be the answer to the question but I wrote this answer to help you write better code (not perfect probably :) ). I think you don't completely understand the concept of the use of a try-catch statement. There's not only try and catch but also the keyword finally. Everything in this statement will be executed, even when an exception is thrown. This way, you can handle the closing/disposing of variables if still needed, or execute other code that must be done, even when a exception is thrown.

static void Main(string[] args)
{
    SqlConnection myConnection = new SqlConnection("Server=HABCHY-PC/SQLEXPRESS;" +
                                "Trusted_Connection=yes;" +
                                "Database=mydatabase;" +
                                "User Instance=true;"+
                                "Connection timeout=30");

    SqlCommand myCommand = new SqlCommand("INSERT INTO students (firstname, age) "+
                                          "Values ('string', 1)", myConnection);

    try
    {
        myConnection.Open();
        myCommand.ExecuteNonQuery();

        myCommand.CommandText = "select * from students";
        SqlDataReader myReader = myReader = myCommand.ExecuteReader();

        while (myReader.Read())
        {
            Console.WriteLine(myReader["firstname"].ToString());
            Console.WriteLine(myReader["age"].ToString());
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
    finally
    {
        myReader.Close();
        myConnection.Close();
    }
}

You're right but it seems that the main problem is in the connection. Thank you:) — Youmna Habchy, Apr 02 '14 at 07:53

Connection to SQL Server Express from C#

4 Answers4