C# SQL Server Extra Slash Added to String When Connecting To Named Instance

Question

Hi,

I am trying to connect to a named instance of sql server from c#. I saw a similar article on this site, but it didn't seem to have the issue I am having.

I am reading values from a table into a string. When I put the named instance into a string, I get an extra slash, and it doesn't connect. So if the database field has "myserver\myinstance", when I read it into a string, it ends up as "myserver\myinstance". Interestingly, if I use the string builder class, it only has one slash. When I convert that into a string, it gets an extra slash. It sounds like my issue is not sql related, but string related.

Here is the code, in case you need it:

public class MyClass
{

private Hashtable ipAddresses;

private void LoadPlants()
    {
        // get a list of plants from the plant table
        String conString = "Data Source=myserver;Initial Catalog=mydb;Integrated Security=SSPI;";
        string strSql = "select distinct ServerName, IPAddress from location where ipaddress is not null and ltrim(rtrim(ipaddress)) <> ''";


        SqlConnection con = new SqlConnection(conString);
        con.Open();
        SqlDataAdapter dAdapter = new SqlDataAdapter();

        dAdapter.SelectCommand = new SqlCommand(strSql, con);

        DataSet dset = new DataSet();
        dAdapter.Fill(dset, "location");
        con.Close();

        // populate the checked list box with the list of plants
        DataTable dt = dset.Tables["location"];
        ipAddresses = new Hashtable();



        for (int i = 0; i <= dt.Rows.Count -1; i++)
        {
           chklbPlants.Items.Add(dt.Rows[i]["ServerName"], false);
           string strPlantName;
           string strIpAddress;

           StringBuilder sb = new StringBuilder(); 
           sb.Append(dt.Rows[i]["IPAddress"].ToString()); // returns 192.168.0.3\mydbinstance

           strPlantName = dt.Rows[i]["ServerName"].ToString();
           strIpAddress = dt.Rows[i]["IPAddress"].ToString(); // returns 192.168.0.3\\mydbinstance

           ipAddresses.Add(dt.Rows[i]["ServerName"].ToString(), dt.Rows[i]["IPAddress"].ToString());
        }



    }

}

-- drop table location

use someDB

go

create table location
(
    ServerName varchar(255),
    IPAddress varchar(255)
)


insert into location
(ServerName, IPAddress)
values
('testserver1', '192.168.1.1')

insert into location
(ServerName, IPAddress)
values
('testserver2', '192.168.1.2')
insert into location
(ServerName, IPAddress)
values
('testserver3', '192.168.1.3\mydbinstance')
insert into location
(ServerName, IPAddress)
values
('testserver4', '192.168.1.4')

go

select * from location

go

I know it probably has something to do with escape characters, but I'm not sure how to correctly handle it.

Thanks,

Seth

Answer 1

I am just going to check you are not being led astray by the VS debugger. Have a look at this:

using System.Diagnostics;

namespace ConsoleApplication69
{
    class Program
    {
        static void Main(string[] args)
        {
            string foo = @"back\slash";

            Debugger.Break();
        }
    }
}

Run this from VS, and when it breaks, hover over foo. Note that VS then shows you a data tooltip that looks like

"back\\slash"

However this does NOT mean that foo contains two backslashes! VS is showing you the C# literal you would have to provide in order to get that value of foo. foo actually only contains one backslash!

So when you say

strIpAddress = dt.Rows[i]["IPAddress"].ToString(); // returns 192.168.0.3\\mydbinstance

what are you basing this comment on?

Maybe you already know about this. But I thought it worth checking...