SQL -Tip

The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1.

DBCC CHECKIDENT('Customer', RESEED, 0)

How to get the list of all SQL Servers

Recently ,  we were working on an implementation where I need to:
a. Populate all the SQL servers in the LAN , when n/w is connected
b. Poplulate all the SQL servers running on my system , when n/w is not connected.

When reading this problem statement we devised a big flowchart with lot of conditions:
a. i. Check n/w
a. ii If connected , read all SQL Servers
a. iii List it in drop down.

b. i Check n/w
b. ii If not connected , see which all SQL Servers are installed.
b. iii Check if servers are running
b. iv List all the SQL Servers in the drop down.
b. v If you don't find anything show proper message.

How , we achieved it??

We just knocked-off checking the LAN Connectivity.
So, one loop is removed.

We just knocked-off reading SQL Servers installed in user's PC.
So, another condition removed.

We also knowcked-off , checking which SQL servers are running.

So, what we have now??

Only following lines of code:

Add namespaces:

using

System.Data.Sql;

using

System.Data.SqlClient;


 Create an instance of DataTable

DataTable

dt = new DataTable();

Call the GetDataSources() method of CreateDataSourceEnumertaor namespace:

dt =
SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();

This above line will give you a data table containing all the SQL Servers if network is available.
Else, it will return a datatable containing all the SQL Server instances that are installed and running in your local system.

Yippiee!!!

You can read more of CreateDataSourceEnumerator here.