Connections failing after trying to connect to database that doesn't exist

Topics: Data Access Application Block
Aug 22, 2007 at 2:12 PM
Hi,

I have this Manager app that goes through remoting to talk to our server. On the server side when a user attempts to login it checks to see if the database exists or not. If it does not exist then it attempts to create it. In the process though there are some calls that try to pull information out of the database before creating the database and these throw errors claiming that the login was invalid which is expected since the database doesn't exist. Anyways we use many calls to create the database and tables. The first call to create the database works. And then all the other calls to add tables to that database fail with the invalid login even though in the previous call we created the database.

Now this database creation works if I comment out the calls that get called before the database is starting to be created.

Anyone know what might be going on. Am I making sense here at all.

Thanks,
Matt
Aug 22, 2007 at 2:24 PM
Here is some sample code to try and be more specific:

public static DbConnection GetConnection()
{
try
{


DbConnection connection = null;


SqlDatabase db = new SqlDatabase(connectionString);
connection = db.CreateConnection();
connection.Open();


return connection;
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return null;
}
}

That is used to create our connections.

Then we have calls for DatabaseExists(), CreateDatabase(), ClearPermissionsCache(), CreateTable(), CreateColumn()

They all call that method above to get the connection and then Execute their SQL using SqlCommand

Here is the order thier called:

DatabaseExists() returns false cause it doesn't exist and there are no errors here.
ClearPermissionsCache() This statement is here for another purpose when changing the settings to another database. Can easily be removed to get things working.
CreateTable() fails if the ClearPermissionsCache call is not commented out, cause the ClearPermissionsCache trys to delete all records from a table in the database that is not created yet.
CreateColumn()




I can easily remove the calls before the database is created, but I want to know why it is failing to connect to the database after I create it after those failed calls.

Thanks.
Aug 22, 2007 at 2:32 PM
Sorry to post again, but it also might have to do with timing. Cause while debugging if slowly go through it it all succeeds. So its like it takes a little bit for that bad connection to go away for the following connections to the newly created database to work...
Aug 22, 2007 at 7:40 PM
It this questions in any way related to Enterprice Library?
Aug 22, 2007 at 9:28 PM
Yes it does, I am using the Enterprise library.

I believe that SqlDatabase class from above comes from the enterprise library dlls that I have included in my project.
Aug 22, 2007 at 9:49 PM
Hi,

If you're using the SqlDatabase class just to create a DbConnection, which is bound to be a SqlConnection, you might as well just create the SqlConnection with your connection string and return it. By doing that you can find if the DAAB is causing the behaviour you are seeing or if it's just the way ADO.NET and Sql Server work.

I don't think creating databases from your application is a best practice for most domains. As you mention, it's likely there are timing issues involved.

Fernando