Get Available SQLServer and it's Database using C#.NET
Post date: Mar 17, 2011 12:41:43 PM
Here, I plan to retrieve all available SQLServer list and its database using C# code. For that you have to use SQLDMO reference.
Right Click Your project -> Add Reference-> COM-> Microsoft SQLDMO Object library.
Then write following library class for your project.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLDMO;
using System.Data.SqlClient;
public class DAL
{
static Application SQLApplication = new ApplicationClass();
static SQLServer server = new SQLServerClass();
static StoredProcedure procedure = new StoredProcedureClass();
static Database database = new DatabaseClass();
//Get available SQLServers
public static string[] getListOfServers()
{
NameList listSQLServer = SQLApplication.ListAvailableSQLServers();
string[] serverName = new string[listSQLServer.Count];
for (int i = 0; i < listSQLServer.Count; i++)
{
serverName[i] = listSQLServer.Item(i + 1);
}
return serverName;
}
//Get the Available Database
public static string[] getDatabaseList()
{
//Initialize SQLDMO application interface
List<string> listDatabase = new List<string>();
foreach (Database database in server.Databases)
{
if (database.Name != null)
{
listDatabase.Add(database.Name);
}
}
return listDatabase.ToArray();
}
//Connect to the server.
public static bool ConnectionToServer(string ServerName,string username, string password,bool WindowAuthentication)
{
try
{
server.Name = ServerName;
server.LoginSecure = WindowAuthentication;
server.Connect(ServerName, username, password);
return true;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//Disconnect from server
public static bool DisconnectFromServer()
{
try
{
server.DisConnect();
return true;
}
catch(Exception ex)
{
throw ex;
}
}
//Function excute the T-SQL commands from SQL File
public static bool CreateDatabase(string sqlFile)
{
try
{
server.ExecuteImmediate(sqlFile, SQLDMO_EXEC_TYPE.SQLDMOExec_Default, sqlFile.Length);
return true;
}
catch (Exception ex)
{
throw ex;
}
}
////Function excute the T-SQL commands from SQL File
public static bool ExecuteFile(string sqlFile)
{
try
{
server.ExecuteImmediate(sqlFile, SQLDMO_EXEC_TYPE.SQLDMOExec_Default, sqlFile.Length);
return true;
}
catch (Exception ex)
{
throw ex;
}
}
}