How to get Database names from given Sql Server in LINQ

I have DropDownList displays all server present in my n开发者_如何转开发etwork I want to populate the databse names when i changed it into another dropdown I want a LINQ query to get this.

See the question here on how to get the list of databases available to the logged-in user:

SELECT name
FROM sys.sysdatabases
WHERE HAS_DBACCESS(name) = 1

Implementing this into LINQ requires you to publish the sysdatabases view into your own view. I would recommend creating a view for the job in one of the databases:

CREATE VIEW [dbo].[vewDatabases]
AS
SELECT  name
FROM    sys.sysdatabases
WHERE   HAS_DBACCESS(name) = 1

GO

Add this to your LINQ data model, and you will be able to select from it like so [c#]:

var databaseNames = serverDataContext.vewDatabases.Select(a => a.name);

You can then populate the DropDownList with the contents of databaseNames.

I don't think this is a job for Linq2SQL (though I'm not overly familiar with it).

Basically, I think you can get the results you want by looking at one of the SYS_ tables, or a SP_ stored procedure. Can't remember which - been a while since I did anything with SQL Server.

Try code below:

    protected void ddList_SelectedIndexChanged(object sender, EventArgs e)
    {
        PopulateDatabaseDropDown(ddList.SelectedItem.Text);
    }
    private void PopulateDatabaseDropDown(string server)
    {
        ddDatabase.DataSource = DatabaseName(server);
        ddDatabase.DataValueField = "DatabaseId";
        ddDatabase.DataTextField = "Name";            
        ddDatabase.DataBind();
    }
    private DataBase[] DatabaseName(string serverName)
    {
        List<DataBase> data = new List<DataBase>();
        System.Data.Linq.DataContext db = new System.Data.Linq.DataContext("Data Source="+serverName+";Initial Catalog=master;Integrated Security=SSPI");
       var dbName = db.ExecuteQuery<DataBase>("select database_id, [name] from sys.databases").AsEnumerable();
       foreach (var item in dbName)
           data.Add(new DataBase() { DatabaseId = item.DatabaseId, Name = item.Name });
       return data.ToArray();
    }
}
public class DataBase
{
    public string Name { get; set; }
    public int DatabaseId { get; set; }
}