Display list of databases in App_Data folder

Hi All,

The web site I'm working on will allow admin users to create a new database for each new client. I'm hoping to be able to keep all of the databases in the local App_Data folder, but I'm finding that I can't even find a way of generating a list of the databases in the App_Data folder.

The code I have is:

Dim serverConnAs New ServerConnection(".\SQLEXPRESS")serverConn.LoginSecure =TrueserverConn.ConnectTimeout = 30serverConn.Connect()Dim sqlServerAs Server =New Server(serverConn)For Each dbAs DatabaseIn sqlServer.Databases ListView1.Items.Add(db.Name)Next

This does populate a list of databases, but not the ones in the App_Data folder - just those attached directly to the server.

Any help on this will be greatly appreciated as it's starting to drive me nuts!

Thanks,

Paul

Hi Paul,

What you are getting is correct. You will get a list of databases running on the server.

If you want to create databases on a particular directory you have to override the default directory

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data or something similar depending on where is your default data directory.

You have to specify that in your create database script

USE MASTER

CREATE DATABASE DataBaseName
ON
(
NAME = LogicalName_Data,
FILENAME = 'c:\YourPath\App_Data\DataBaseName_Data.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1
)
LOG ON
(
NAME = 'LogicalName_Log',
FILENAME = 'c:\YourPath\App_Data\DataBaseName_Log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)

Use

Server server = new Server(new ServerConnection(cnnDb));
server.ConnectionContext.ExecuteNonQuery(sqlString);

to create the new database.

If want to find all databases that are present in the directory.

You have to do a naming convention trick of naming the database starting with

GeneratedDb_ and filter the names based on it or

You can use DirectoryInfo class and read the App_Data folder and get all files having extension .mdf

Or loop through the filegroups in Database and check for App_Data folder

Happy programming,
Anton

|||

Hello,

this may be helpful to u

http://www.codeproject.com/aspnet/dbadmin.asp

http://www.codeproject.com/aspnet/MyDbViewerSite.asp

|||

Hi guys,

Just wanted to say a big thank you for your replies. It looks like this is tougher than I expected as I don't think I can get away with updating the master database to force the creation of the databases (ultimately, the web app won't be loaded on my server), and I had loved the idea of using '|DataDirectory|' so that I didn't need to explicitly give the server name for the connection strings already created. However, I think I've read enough now to believe that I am better off setting things to work directly with the server rather than locally - at least I seem to be able to create new databases and copy data as I need to.

Thanks again,

Paul