How can I retrieve all connection string as regards the databases present in my server using ASP.NET?

Question

I managed to get the name of all databases present in my server using the code below, but I don't know how to create the connection string for each.

List list = new List();
            string connectionString = "Data Source=(localdb)\MSSQLLocalDB; Integrated Security=True;";
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con))
                {
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            list.Add(dr[0].ToString());
                        }
                    }
                }

            }

Abdul Haseeb · Answer

First you create a class DatabaseInfo that stores the Name of database and Connection String

public class DatabaseInfo
{
    public string Database { get; set; }
    public string ConnectionString { get; set; }
}

Create a list of that class DatabaseInfo

List databaseInfos = new List();
string connectionString = "Data Source=(localdb)\MSSQLLocalDB; Integrated Security=True;";
using (SqlConnection con = new SqlConnection(connectionString))
{
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con))
    {
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                databaseInfos.Add(new DatabaseInfo()
                {
                    Database = dr[0].ToString(),
                    //insert the database attribute in your connection string after data source
                    ConnectionString = connectionString.Insert(connectionString.IndexOf(';') + 1, "Database = " + dr[0].ToString() + ";")
                });
            }
        }
    }
}

Now you get this result.

How can I retrieve all connection string as regards the databases present in my server using ASP.NET?

1 Answers