How to find the SQL server instance name in C#?

Question

I have a mdf file that I need to attach it to an existing SQL Server instance. SQL Server instance may be SQLExpress or full version. I need to build a connection string dynamically. Is there any C# code to find the current instances of SQLServer running in the machine?

string sqlServerInstance = @".\SQLEXPRESS";   //How to dynamically get the SQLServerInstance??
string connString = "AttachDBFileName='" + dbPath + "';Server='" + sqlServerInstance + "';user instance=true;Integrated Security=SSPI; Pooling=false; Connection Timeout=60";

Thanks for your help.

Answer 1

Well you can use the SMOApplication retrieval ad use the appropiate column of the returned data table. I made an example for you here:

 

-Jens K. Suessmeyer

Answer 2

Thanks!

Answer 3

Hi

I have already used your code to get the information of SQL Server.

   DataTable dt = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers();
   this.dataGridView1.DataSource = dt;

However, instance name and version are not shown. I don`t know how to solve it. I have already closed the firewall and antivirus, but still not help. Can anyone help me? Thx a lot!

---

麥wing

Answer 4

Hi Wing,
Thanks for your last post.
I have one query that i want username and password of sql server for login.
I got the computer name and Ip address of machine who has sql server installed now i want to connect with that remote sql server so for that i need username and password of that sql server so how can i do it?

Answer 5

To Ankit28,

 This link may help you : http://www.connectionstrings.com/

There are two types of remote connection.

One is using TCP/IP,another one is using SQL Server Browser.

Using TCP/IP, the SQL Server Broswer need not be opened. Only ip address and port number is needed for communication using the provided link.

Using SQL Server Broswer, servername and instance name is needed. make sure firewall allows SQL Server and SQL Server Broswer to pass.

firewall
C:\Program Files\Microsoft SQL Server\90\Shared\sqlbroswer.exe
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlsevr.exe

Connection String

connection = "Data Source=USER-XU2CETEGI4\\SQLEXPRESS;Initial Catalog=testingdatabase;User ID=kkbruce;Password=12345";

---

麥wing