0

I have a really simple question here, but I just can't find an answer that solves it.

I have a connection string like this:

SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DB\DB.mdf;Initial Catalog=DB;Integrated Security=True");

It works, but when I try to use it in a PC that doesn't have SQL Server Express installed, it just won't connect. It'll obviously work if I remove that "\SQLEXPRESS" piece, I know, but I'd prefer not to do it manually on each PC I install the software. I tried to do it by using try-catch, but it's too slow and I think it isn't very recommended.

Well, I just want to know how to do it via code. Is there a simple way to do so? Will it solve the problem completely? (I mean, will it certainly work in other editions?)

Thanks in advance! (And sorry for my English)

EdJr
  • 13
  • 1
  • 3
  • "It'll obviously work if I remove that "\SQLEXPRESS" piece". No, not if there is **no** SQL Server on the machine. – Oded Jul 06 '12 at 20:16
  • That's got nothing to do with sql server express being installed. It just happens that the default instance name is sqlexpress for sqlexpress. I could install an instance called TonyHasSweatyCheeks and it would be sqlserver express. You need to get the instance name and servername from somewhere else. – Tony Hopkinson Jul 06 '12 at 20:22
  • Oded, sorry, that's what I meant (that it would work without "\SQLEXPRESS" as long as SQL Server is installed). @TonyHopkinson, thanks, I really didn't know that. – EdJr Jul 06 '12 at 22:01

4 Answers4

3

Connection strings should not be hard-coded into your software.

You should place them in configuration (web.config or app.config) for the exact reason you are posting this question.

You can't assume there will be any version of SQL installed on the machine that your application will run on - the user may want to use a centralized server.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • Also the . in your connection string means 'local machine'. It's the equivalent of 'localhost' so your connectionstring is looking for a SQL instance called SQLEXPRESS on the machine you install this on – Charleh Jul 06 '12 at 20:18
  • @Charleh - Did you mean to post your comment on the question? – Oded Jul 06 '12 at 20:19
  • Nope was just adding to your answer :P – Charleh Jul 06 '12 at 20:23
  • Dear me. When you are next down in London I will buy you a Pint - perhaps you will be OK with editing my posts then ;) – Oded Jul 06 '12 at 20:26
  • My parents live in London so next time I'm down I'm taking you up on that! – Charleh Jul 06 '12 at 20:29
1

In T-SQL itself, you can inspect the edition server property:

SELECT  
   SERVERPROPERTY('edition') as 'Product Edition'

This will return something like Express Edition (64-bit) if the version you're checking is indeed an Express edition.

You can read more about all other available server properties in the relevant MSDN documentation here.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Well, I've already read an article about this, but I still have to do it manually.... When I tried to do it via code, I still needed to set the connection string properly. Also, after what others said, I guess this is not the problem, but thanks anyway! – EdJr Jul 06 '12 at 22:16
0

You should probably write something that lets your user choose the server. You can use the registry to detect SQL Server instances running locally, but that won't work if you are connecting to a network instance. See this answer about using SMO (Server Management Objects). Programmatically detect SQL Server Edition

Community
  • 1
  • 1
jac
  • 9,666
  • 2
  • 34
  • 63
0

Just to clarify: A SQL server can be installed as a 'default' or a 'named' instance. I think by default, express installs as a named instance

This allows a single server machine to run multiple SQL servers

To connect to a default instance you specify the server name or IP and that's all

Server=SomeServer

To connect to a named instance you specify the server/ip + instance name separated by a backslash.

Server=SomeServer\SomeInstance

In your case you are looking to connect to a SQL instance called 'SQLEXPRESS' on the local machine. (a dot . means local machine)

You ideally want to install SQL on a server machine and use an application configuration file (App.config) to specify the connection string..

App.config supports a section called ConnectionStrings which you can add your connection string to. Then if you add a reference to System.Configuration to your project you can get hold of the connectionstring using the config manager class:

App.config:

<connectionStrings>
    <add name="YourConnectionString"
        connectionString="Server=someSqlServer\SomeInstance;Initial Catalog=SomeDB;Integrated Security=True" 
providerName="System.Data.SqlClient" />
</connectionStrings>

Use in code:

ConfigurationManager.ConnectionStrings["YourConnectionStringName"]
Charleh
  • 13,749
  • 3
  • 37
  • 57
  • Thank you, it's very well explained! But I have two questions: 1: Just for curiosity, what's the advantage of using this method, and not that one I posted? 2: What can I do in order to get the server/instances names? As I said, I tried what Wayne Phipps posted, but no value was returned. 3: How/where should I use ConfigurationManager.ConnectionStrings["name"], exactly? Sorry if I'm being a moron.... – EdJr Jul 06 '12 at 22:36
  • The advantage is that you don't need to rebuild the application if the server location changes or the authentication method changes. You don't need to get the server names you should already know them, and the config manager should be used when you need a connection string, it returns a string, you can use it when you create your sql connection – Charleh Jul 07 '12 at 00:09
  • Ah, now I get it. Thank you very much! – EdJr Jul 07 '12 at 13:53