How to get @@SERVERNAME and use in app.config file C#

Salman Mushtaq

7y4.3kVisual Basic .NET

Hi All,

In my winform application I use Linq to Sql.

My app.config file is following code

"1.0" encoding="utf-8"?>
"SmartexFxDashboard.Properties.Settings.smartex_fx_reportsConnectionString" connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog=smartex_fx_reports;Integrated Security=True" providerName="System.Data.SqlClient"/>
"v4.0" sku=".NETFramework,Version=v4.6.1"/>

In Data Source I need to add 2 things

1. Server name

2. Instance name

Now if I have default instance I need to write

(local)

And in case I have names instance e.g. SQLEXPRESS I need to write

(local)/SQLEXPRESS

But I didn't know in client computer he have which instance. So, I need to know the instance name and save in key and instead of SQLEXPRESS I use that key.

In MS SQL SERVER 2008 if I execute query

select @@SERVERNAME
select @@SERVICENAME

First one gives me my laptop name / instance name and second one give me only instance name

So, I need your expertise how I get this instance name in app.config file and save in key and use it. So it's not hard coded and work for all.

Advance thanks and looking forward to your answers.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
Try this code:
  "ActiveConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=NameOfTheDatabase;Integrated Security=True" providerName="System.Data.SqlClient"/>
Amit Gupta7y ago
Well, the end user may be non technical person and don't try to make any modification on the default instance. I generally use .\Sqlexpress as in connection string.
Salman Mushtaq7y ago
Dear Amit,

I understand what you said. I try like following steps

1. I save my connection string
string connectionString = Data Source = .\SQLEXPRESS; Initial Catalog = DB_NAME; Integrated Security = True
2. I override the DataContext
using(DBDatacontext db = new DBDataContext(connectionString)){ ... my code ...}
It work fine.
Now my issue is when this application is installed in user computer and he installed default instance or any other instance.

So, my pre-define connection string is not working and connection is not established.

As you know for default instance we only use .(dot) or (local) in connection string.

So, I want to know the instance name and then modify my connection string.

1 way I know is I read registry
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
Key Name = InstalledInstance
But you know this space is always protected even if I run my app as Admin it give me empty string.

So, please help me to find out the instance name installed in local machine so that I used that instance to make my connection string.
Amit Gupta7y ago
However, you can use .\SqlExpress as your default instance name where (.) dot specifies the currrent username, Incase user changed the instance name then you have config file (changed that accordingly)
Salman Mushtaq7y ago
Hi Amit,

Regarding your question

How can you run the above query without loggedin to the server?

I use Sql Management Studio and logged in and run the command. Now I know the result. Same thing I need in Winform C# application -> app.config file.

I write connection string but I know in my computer I installed SQL with name instance and in client computer if I use same connection string then it doesn't work with default instance.

Can you guide me through some general code how I make it generic. Means whatever the instance it works.
Amit Gupta7y ago
Hey

Let me answer your question in reverse order

In MS SQL SERVER 2008 if I execute query
select @@SERVERNAME
select @@SERVICENAME
How can you run the above query without loggedin to the server?

how I get this instance name in app.config file and save in key and use it. So it's not hard coded and work for all.
Why doing hard coded, store connection string in config file instead (that you are already using, right?)

I didn't know in client computer he have which instance. So, I need to know the instance name and save in key and instead of SQLEXPRESS
When SQL server installed in the target machine without any modification in installer, it takes SQLEXPRESS as a default instance, so no need to worry about that.

However, you can use .\SqlExpress as your default instance name where (.) dot specifies the currrent username, Incase user changed the instance name then you have config file (changed that accordingly)

Good Luck