[RESOLVED] Connection String in C# 2008

[jsham]

What do you specify for the Data Source parameter in a C# connection string?

The following either returns a syntax errror or a runtime error:

"Data Source=.\SQLExpress;Initial Catalog=Chinook;Integrated Security=True;"

"Data Source=MYLOCAL-PC\SQLExpress;Initial Catalog=Chinook;Integrated Security=True;"

"Data Source=SQLExpress;Initial Catalog=Chinook;Integrated Security=True;"

"Data Source=MYLOCAL-PC;Initial Catalog=Chinook;Integrated Security=True;"

"Data Source=(local);Initial Catalog=Chinook;Integrated Security=True;"

The coding is as follows:

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

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

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            SqlConnection cs = new SqlConnection("Data Source=SQLExpress;Initial Catalog=ChinookPlay;Integrated Security=True;");
             cs.Open();
            MessageBox.Show(cs.State.ToString());
            cs.Close();
        }
    }
}

The run time error is:

  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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=20
  LineNumber=0
  Number=53
  Server=""
  State=0
  StackTrace:
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

[techgnome]

Well, if you are in fact connecting to a SQL Express install on your own machine, either of those first two should work... unless you changed the name of the instance when you installed SQL Express.

-tg

[jsham]

Well, if you are in fact connecting to a SQL Express install on your own machine, either of those first two should work... unless you changed the name of the instance when you installed SQL Express.

-tg

Not really.

"Data Source=.\SQLExpress;Initial Catalog=Chinook;Integrated Security=True;"

The first one (above) works on VB 2008.

It does not works on C# 2008. It returns a syntax error:

Error	1	Unrecognized escape sequence	C:\Visual Studio 2008\Projects\Example1\Example1\Form1.cs	27	66	Example1

I have SQL Server Express, VB 2008 Express, C# 2008 Express installed on the same local computer.

C# does not like the "\" in ".\SQLExpress". What should I specify for the Data Source?

[jsham]

I tested the VB 2008 codes again and it worked:

    Private Sub btnDefault_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDefault.Click

        Me.Cursor = Cursors.WaitCursor
        txtTextBox.Clear()

        Dim strConn, strSQL As String
        strConn = "Data Source=.\SQLExpress;" & _
                  "Initial Catalog=AdventureWorks;Integrated Security=True;"
        strSQL = "SELECT ProductNumber, Name FROM Production.Product WHERE ProductNumber Like 'GT%'"
        Console.WriteLine("Connection String=" & strConn)
        Console.WriteLine("Reading Production.Product table using SqlDataReader.." & vbCrLf)

        Using cn As New SqlConnection(strConn)
            :
            :
            :
        End Using

        Me.Cursor = Cursors.Default
    End Sub

When I use Data Source=.\SQLExpress; in C# 2008, it returns a syntax error for the back slash character "\".


The following C# 2008 does not works:

        private void btnAdd_Click(object sender, EventArgs e)
        {
            SqlConnection cs = new SqlConnection("Data Source=.\SQLExpress;Initial Catalog=ChinookPlay;Integrated Security=True;");
            cs.Open();
            MessageBox.Show(cs.State.ToString());
            cs.Close();
            
        }
    }

How should I specify "Data Source=.\SQLExpress;" in C# 2008?

[jmcilhinney]

It's not that the connection string doesn't work per se. The exact same connection string works in C# and VB. It's just that C# treats string literals slightly differently to VB, which is a fundamental part of C# that all beginner books and tutorials should cover.

In C#, a backslash (\) is an escape character. As such, if you want to include a literal backslash in a C# string literal then you need to escape it:

SqlConnection cs = new SqlConnection("Data Source=.\\SQLExpress;Initial Catalog=ChinookPlay;Integrated Security=True;");

The thing is, pretty much all file and folder paths contain one or more backslashes and having to escape each one is annoying and ugly. The authors of C# realised this and so they invented the verbatim string literal. It can be useful in other cases too but it's primary use is with file and folder paths:

SqlConnection cs = new SqlConnection(@"Data Source=.\SQLExpress;Initial Catalog=ChinookPlay;Integrated Security=True;");

By prefixing a string literal with @ you tell the compiler that every character between the double quotes should be taken literally. That means that each backslash is treated as a backslash character and not an escape character. As such, you can't include any escape characters, e.g. '\n'. That's not a problem for paths though. Also, line breaks in the string are taken literally too.

[jsham]

It's not that the connection string doesn't work per se. The exact same connection string works in C# and VB. It's just that C# treats string literals slightly differently to VB, which is a fundamental part of C# that all beginner books and tutorials should cover.

Thank you for your expertise, jmcilhinney. I tried both methods you described and it works! Your response is appreciated and has been rated

[jsham]

Resolved