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
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; } |
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; } |
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; } |
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!"); } } |
Get defined connection string from App.Config
<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
++ VS2008 – [download id=”28″ format=”1″]
