Geekpedia Tutorials Home

Building a C# Chat Client and Server

Building a C# Chat Client and ServerA step by step tutorial teaching you how to create your own chat client and chat server easily in C#, for local networks or the Internet.

in C# Programming Tutorials

Getting Hard Drive Information

Getting Hard Drive InformationA C# tutorial showing you how to make use of WMI to extract information on disk drives, such as model, capacity, sectors and serial number.

in C# Programming Tutorials

UPS Shipping Calculator

UPS Shipping CalculatorThis tutorial will teach you how to calculate the shipping cost based on the weight, height, length and depth of the box, the distance and the UPS service type.

in PHP Programming Tutorials

Create Your Own Rich Text Editor

Create Your Own Rich Text EditorCreating a Rich Text Editor using JavaScript is easier to do than you might think, thanks to the support of modern browsers; this tutorial will walk you through it.

in JavaScript Programming Tutorials
Search
Tutorials
Programming Tutorials
IT Jobs
From CareerBuilder

How to get a list of SQL Server databases

On Thursday, June 16th 2005 at 02:51 AM
By Andrew Pociu (View Profile)
*****   (Rated 4.3 with 16 votes)
Advertisement
More C# Resources
There are multiple ways of getting a list of the SQL Server databases, the easiest one is to execute the sp_databases stored procedure, like in the example below:

System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection("server=192.168.0.1;uid=sa;pwd=1234");
SqlCon.Open();

System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";

System.Data.SqlClient.SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();

while(SqlDR.Read())
{
MessageBox.Show(SqlDR.GetString(0));
}


Don't forget to change the connection string to match your server details (IP, userid and password).
Digg Digg It!     Del.icio.us Del.icio.us     Reddit Reddit     StumbleUpon StumbleIt     Newsvine Newsvine     Furl Furl     BlinkList BlinkList

Rate Rate this Knowledge Base article
Comment Current Comments
by 1 on Tuesday, August 1st 2006 at 05:45 AM

by avrail on Monday, April 2nd 2007 at 04:30 PM

k, thats good but how to get the server name it self if i was in a network and i didnt know the server name?

by Steven Davidson on Monday, April 16th 2007 at 06:09 PM

Wonderful! Thanks for the clever solution!

by Kunal Mohanta on Friday, July 13th 2007 at 08:13 AM

u need to do soket programming using System.Net.Sockets

the code is something like below...
static public SqlServerInfo[] Seek()
{
Socket socket = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp);

socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.Broadcast, 1);
socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 3000);

// For .Net v 2.0 it\\\'s a bit simpler
// socket.EnableBroadcast = true; // for .Net v2.0
// socket.ReceiveTimeout = 3000; // for .Net v2.0

ArrayList servers = new ArrayList();
try
{
byte[] msg = new byte[] { 0x02 };
IPEndPoint ep = new IPEndPoint(IPAddress.Broadcast, 1434);
socket.SendTo(msg, ep);

int cnt = 0;
byte[] bytBuffer = new byte[1024];
do
{
cnt = socket.Receive(bytBuffer);
servers.Add(new SqlServerInfo(null, bytBuffer));
socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 300);
} while (cnt != 0);
}
catch (SocketException socex)
{
const int WSAETIMEDOUT = 10060; // Connection timed out.
const int WSAEHOSTUNREACH = 10065; // No route to host.

// Re-throw if it\\\'s not a timeout.
if (socex.ErrorCode == WSAETIMEDOUT || socex.ErrorCode == WSAEHOSTUNREACH)
{
// DO nothing......
}
else
{
// Console.WriteLine(\\\"{0} {1}\\\", socex.ErrorCode, socex.Message);
throw;
}
}
finally
{
socket.Close();
}

// Copy from the untyped but expandable ArrayList, to a
// type-safe but fixed array of SqlServerInfos.

SqlServerInfo[] aServers = new SqlServerInfo[servers.Count];
servers.CopyTo(aServers);
return aServers;
}

by Bass on Sunday, August 5th 2007 at 03:33 AM

Or (when using .NET 2.0) you could just use the build in function \"System.Data.Sql.SqlDataSourceEnumerator\" this wil give you a list off all available SQL Servers in the network.

Works like this:

using System.Data;
using System.Data.Sql;

SqlDataSourceEnumerator servers = SqlDataSourceEnumerator.Instance;
DataTable serversTable = servers.GetDataSource();

foreach(DataRow row in serversTable.Rows)
{
string serverName = string.format(\"{0}\\\\{1}\", row[0], row[1];
// Add this to your list
}

For details on the columns in the row see MSDN online (check for System.Data.Sql.SqlDataSourceEnumerators).

by kuldeep Shige on Thursday, May 28th 2009 at 12:33 AM

sp_databases proc is awesome
but it gives me all db name when i mention a db name with the connection string.

by Kuldeep Shige on Thursday, May 28th 2009 at 12:42 AM

Yeah it really works for me....thanx buddy

by Charu Joshi on Wednesday, July 22nd 2009 at 01:38 AM

Hi,

I appriciate, Your article has given me a great help. I implemented that but i stuck on a point. i have two sql express instances on mymachine. Using NetServerEnum i am only getting the ServerName like my ServerName is Rsys100 and my sql express instances are Rsys100 and another one is Rsys100\SqlExpress then it's giving just Rsys100. My pupose is to get both the instances name. Can you or anyone else, please help me in this regard.



by Tanvir AHmed on Tuesday, September 1st 2009 at 09:45 AM

It's a great !!! job.

Now I need a help. Can you help me to get the list of DBMS/ Database servers installed in a computer???
please please help me.

by Nick on Wednesday, April 21st 2010 at 05:08 AM

getting the server name works great with SqlDataSourceEnumerator.Instance, but is there a way of getting the data base names without establishing a connection? i want the user to be able to select the sever then the database before entering any login details.....is this possible?

by Srinivas on Monday, May 31st 2010 at 12:58 AM

HI Tech Guru,
Nice article

I tried your code. it works fine.
Please Provide all the system procedures

Thanks

by Harshil Shukla on Tuesday, October 12th 2010 at 02:06 AM

I have a better way rather than executing system stored procedure as below:

using System.Data;
using System.Data.SqlClient;

public class GetDatabases
{

String strConn = strConnectionString;
using (SqlConnection sqlConn = new SqlConnection(strConn))
{
sqlConn.Open();
DataTable tblDatabases = sqlConn.GetSchema("Databases");
sqlConn.Close();

foreach (DataRow row in tblDatabases.Rows)
{
String strDatabaseName = row["database_name"].ToString();
}
}

}

by Lakshmi Narayana on Friday, November 12th 2010 at 12:35 AM

Hi,

This is very good program.
I tried this code.It was worked.
But I have one doubt How to get all the tables from certain data base.



Thanks.

by Shankar on Friday, July 22nd 2011 at 10:50 AM



Wonderful job man!!

Something that i was searching for days!!

thanks

by article submission service on Sunday, December 18th 2011 at 12:08 PM

The update somehow monkeyed with the password for the ASPNET user used to run the worker process. I edited machine.config, set the processModel tag's password attribute to password=AutoGenerate. I then saved machine.config, restarted IIS, and ran the application in IE locally as Administrator. This cleared up the problem.

by Alex on Monday, March 19th 2012 at 10:51 AM

Wonderful, it is woking great

by xbxbx on Friday, April 25th 2014 at 10:17 AM

dbdxb


Comment Comment on this Knowledge Base article
Name: Email:
Message:
Knowledge Base Related Knowledge Base Articles
There are no related KB articles.

Comment Related Source Code
There is no related code.

Comment Related Tutorials
There are no related tutorials.

Jobs C# Job Search
My skills include:

Enter a City:

Select a State:


Advanced Search >>
Ads

From the creators of Geekpedia, a revolutionary new coupon website!

BargainEZ has coupons codes, printable coupons, bargains and it is the leading source of Passbook coupons for iPhone and iPod touch devices.

Coupons
Discover Geekpedia
Other Resources