Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home CSharp C# Get List of databases in SQL Server

C# Get List of databases in SQL Server

Sometime, you want to written a program which allows to run SQL statement by CSharp,in design you need to list of database names of a certain instance, then choose specify database name you want to connect to execute script.

In this article, I show you three ways of retrieving list of database name in MSSQL Server. One way from function supported C#,two remain ways based on store procedure which helps usMSSQL Get list of databases names.
Below is sample of database names I want get in this demo.

SQLServer List Of Databases

SQLServer List Of Databases

1. Use C# function GetSchema(“Databases”) and read data in column “database_name”

public static List<string> GetListOfDBNames1(string connection)
{
    List<string> lstDBName = new List<string>();
 
    using (SqlConnection sqlConn = new SqlConnection(connection))
    {
        sqlConn.Open();
        DataTable tblDatabases = sqlConn.GetSchema("Databases");
        sqlConn.Close();
 
        foreach (DataRow row in tblDatabases.Rows)
        {
            lstDBName.Add(row["database_name"].ToString());
        }
    }
    return lstDBName;
}

public static List<string> GetListOfDBNames1(string connection) { List<string> lstDBName = new List<string>(); using (SqlConnection sqlConn = new SqlConnection(connection)) { sqlConn.Open(); DataTable tblDatabases = sqlConn.GetSchema("Databases"); sqlConn.Close(); foreach (DataRow row in tblDatabases.Rows) { lstDBName.Add(row["database_name"].ToString()); } } return lstDBName; }

2. Use SQL supported Stored Procedure sp_databases and read data in column “database_name”

public static List<string> GetListOfDBNames2(string connection)
{
    List<string> lstDBName = new List<string>();
 
    using (SqlConnection sqlConn = new SqlConnection(connection))
    {
        sqlConn.Open();
        SqlCommand conn = new SqlCommand();
        conn.Connection = sqlConn;
        conn.CommandText = "sp_databases";[download id="28" format="1"]
        SqlDataReader sdr = conn.ExecuteReader();
        while (sdr.Read())
        {
            lstDBName.Add(sdr.GetString(sdr.GetOrdinal("database_name")));
        }
 
        sqlConn.Close();           
    }
    return lstDBName;
}

public static List<string> GetListOfDBNames2(string connection) { List<string> lstDBName = new List<string>(); using (SqlConnection sqlConn = new SqlConnection(connection)) { sqlConn.Open(); SqlCommand conn = new SqlCommand(); conn.Connection = sqlConn; conn.CommandText = "sp_databases";[download id="28" format="1"] SqlDataReader sdr = conn.ExecuteReader(); while (sdr.Read()) { lstDBName.Add(sdr.GetString(sdr.GetOrdinal("database_name"))); } sqlConn.Close(); } return lstDBName; }

3. The same way with method 2 – Use SQL supported Stored Procedure sp_helpdb and read data in column “name”

public static List<string> GetListOfDBNames3(string connection)
{
    List<string> lstDBName = new List<string>();
 
    using (SqlConnection sqlConn = new SqlConnection(connection))
    {
        sqlConn.Open();
        SqlCommand com = new SqlCommand();
        com.Connection = sqlConn;
        com.CommandText = "sp_helpdb";
        SqlDataReader sdr = com.ExecuteReader();
        while (sdr.Read())
        {
            lstDBName.Add(sdr.GetString(sdr.GetOrdinal("name")));
        }
 
        sqlConn.Close();
    }
    return lstDBName;
}

public static List<string> GetListOfDBNames3(string connection) { List<string> lstDBName = new List<string>(); using (SqlConnection sqlConn = new SqlConnection(connection)) { sqlConn.Open(); SqlCommand com = new SqlCommand(); com.Connection = sqlConn; com.CommandText = "sp_helpdb"; SqlDataReader sdr = com.ExecuteReader(); while (sdr.Read()) { lstDBName.Add(sdr.GetString(sdr.GetOrdinal("name"))); } sqlConn.Close(); } return lstDBName; }

4. Load database names in combobox by calling one of three methods above

In below attached demo, I just call function GetListOfDBNames1 for example.

 private void Form1_Load(object sender, EventArgs e)
 {
     try
     {
         string connectionString = ConfigurationSettings.AppSettings["MyAllDBConnString"];
         this.txtUserName.Text = "sa";
         this.cboDBName.DataSource = SQLHelper.GetListOfDBNames1(connectionString);
     }
     catch (Exception)
     {
         MessageBox.Show("Can not load database names!");
     }
 }

private void Form1_Load(object sender, EventArgs e) { try { string connectionString = ConfigurationSettings.AppSettings["MyAllDBConnString"]; this.txtUserName.Text = "sa"; this.cboDBName.DataSource = SQLHelper.GetListOfDBNames1(connectionString); } catch (Exception) { MessageBox.Show("Can not load database names!"); } }

Get defined connection string from App.Config

<configuration>
  <appSettings>
     <add key="MyAllDBConnString" value="Server=(local)\SQLEXPRESS;uid=sa;pwd=sa"/>
  </appSettings>
</configuration>

<configuration> <appSettings> <add key="MyAllDBConnString" value="Server=(local)\SQLEXPRESS;uid=sa;pwd=sa"/> </appSettings> </configuration>

Below is demo image :

Demo Get List Of database names in SQL

Demo Get List Of database names in SQL

++ VS2008 – [download id=”28″ format=”1″]

Mar 23, 2012quynhnguyen
HTTP/ HTTPS Document, CGI-BIN And Access/ Error Logs On cPanel, Plesk And VirtualMinRemove/Delete your saved passwords in IE, Firefox, Google Chrome
You Might Also Like:
  • MSSQL Connection String For ODBC, OLEDB, SqlConnection
  • Connect Remotely To MySQL or SQL Server Databases for Godaddy Hosting Account
  • ASP.NET C# Export Data To CSV And Prompt Download
  • Asp.net Load Connection String Dynamically For Different Environments
  • PHP Load And Save Facebook Friend List
  • PHP Connect To MS SQL Server
  • SQL Server Get list of dates between start date and end date
  • String To Upper Case In PHP, JavaScript And .Net (CSharp)
  • String To Lower Case In PHP, JavaScript And .Net (CSharp)
  • C# Read File Content
quynhnguyen
9 years ago CSharpConnectionString, CSharp, Store Procedure2,312
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
22,237 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
20,087 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
15,872 views
JQuery Allow only numeric characters or only alphabet characters in textbox
13,343 views
C# Read Json From URL And Parse/Deserialize Json
9,850 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Term Papers – Easy to Take Care of
  • How to Write Term Papers in Online Tutorials
  • How to Plan Writing an Essay
  • How to Apply For a Payday Loan With Bad Credit
  • Statistics For Sale – How To Compose One
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (28)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (123)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development