The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Creating a database using .NET

I have some .NET code like this:

void createDatabase(string databaseName)
{
  //create the database
  string connectionString = "Data Source=localhost\\SQLEXPRESS;Integrated Security=True";
  SqlConnection sqlConnection = new SqlConnection(connectionString);
  sqlConnection.Open();
  string sqlCommandString = string.Format("CREATE DATABASE \"{0}\"", databaseName);
  SqlCommand sqlCommand = new SqlCommand(sqlCommandString, sqlConnection);
  sqlCommand.ExecuteNonQuery();
  sqlConnection.Close();
  //open a connection to the database to verify when creation is successful
  connectionString = string.Format("Data Source=localhost\\SQLEXPRESS;Initial Catalog={0};Integrated Security=True", databaseName);
  bool succeeded = false;
  while (!succeeded)
  {
    try
    {
      sqlConnection = new SqlConnection(connectionString);
      sqlConnection.Open();
      sqlConnection.Close();
      succeeded = true;
    }
    catch (Exception)
    {
      //exception when opening the connection: loop to try again
    }
  }
}

The thing is that the attempt to open a connection to the newly-created database fails (with a SqlException "cannot login") for the first few seconds. I don't like the loop-until-success hack.

Is there something I can do to change the above?
Christopher Wells Send private email
Friday, November 02, 2007
 
 
Creating connections is an expensive process ... even if you weren't having this problem, you should use a connection pooling mechanism for efficiency reasons.
Steve Moyer Send private email
Friday, November 02, 2007
 
 
I suppose you could base it on a timer, but that's more complicated and no less hackish to me.  I don't see many ways around it though.  I guess there is some latency in SQL Server between the time a database is created and the time SQL has it 'prepared' to accept logins?
fair to middlin
Friday, November 02, 2007
 
 
>>Creating connections is an expensive process ...
>>even if you weren't having this problem, you should
>>use a connection pooling mechanism for efficiency
>>reasons.

.NET uses connection pooling by default and even though you see the new command, it doesn't really create a new instance, but uses a pooled connection if one is available.

Friday, November 02, 2007
 
 
Connection pooling won't happen when the connection strings are different, which I think they must be in this case.

Pooling doesn't seem relevent to my question though: I want to create the database *and* subsequently be able to connect to it, and I would guess there ought to be some better way of doing that than this.
Christopher Wells Send private email
Friday, November 02, 2007
 
 
Here's an idea, but I don't know whether it's going to do exactly what you want, or if it's practical for your setup.  SQL 2005 has a feature called Query Notifications:

http://msdn2.microsoft.com/en-us/library/ms175110.aspx

Could you use this on sysdatabases to be notified when the new DB is added?  I'm not sure if this would exactly correspond to the new DB coming online, though.
D. Lambert Send private email
Friday, November 02, 2007
 
 
Why do you have to close the connection and then re-establish the connection?

Presumably, after creating the database, you are going to go on and create tables, stored procedures etc. Why not just process a “USE [Databasename]” command followed by your create table, create stored procedures etc. commands on the sqlCommand? That should eliminate any timing issues as everything is sequenced through the same sqlCommand.
Mike Green Send private email
Friday, November 02, 2007
 
 
It's certified as Working on my Machine, so you might investigate why it fails for you.  On the other hand, I don't see why you need a new connection - can't you issue a "USE FOO" command to use the new database?  Or does that fail, too.
Brian McKeever Send private email
Friday, November 02, 2007
 
 
Funny, I was just working on the exact same thing. Rather than creating a new connection I execute a "use new_database_name" on the existing one. That works on my machine.
clcr
Friday, November 02, 2007
 
 
I'm not a .Net guy, nor have I ever used SQL Express, but the documentation says there's a member of SqlConnection called ConnectionTimeout that overrides the wait time to establish a connection. It says you can set it from within your connection string. Now, if I read your post right, you said it does eventually connect, you just need ot to wait longer, so you don't have to use the loop, right?

<a href=http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection_members.aspx target=_blank>SqlConnection Doc</a>

<a href=http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx target=_blank>SqlConnection.ConnectionTimeout Doc</a>
AverageMidget Send private email
Friday, November 02, 2007
 
 
> It's certified as Working on my Machine

Thank you for trying it: when it Works on your machine does it work first time, i.e would it work even without the try/catch, or does it work only after running through the while(!succeeded) loop a few times?

> there's a member of SqlConnection called ConnectionTimeout

This has no effect, I think because the connection is being actively refused. The error message is:

    Cannot open database "foo" requested by the login. The login failed.

> Why not just process a “USE [Databasename]” command
> Funny, I was just working on the exact same thing.

Ok, I can do that successfully ...

* Connect without specifying the Initial Catalog
* CREATE DATABASE "foo"
* USE "foo"
* sqlConnection.GetSchema()
* CREATE TABLE etc

... all using the same connection.

However after doing that, the subsequent attempt to open a new connection with "Initial Catalog=foo" in the connection string still fails for the first few seconds.

So, I still need the hackish loop for the subsequent connection; oh well. I'll add a Sleep to the while (!succeeded) loop, and a Cancel dialog.

> Could you use this on sysdatabases to be notified when the new DB is added?  I'm not sure if this would exactly correspond to the new DB coming online, though.

I don't think so, because doing ...

    CREATE DATABASE "foo"; USE "foo";

... as a single command results in an exception ...

    Could not locate entry in sysdatabases for database 'foo'.

.... whereas doing them as separate commands succeeds, after which I can create tables but still (for a while) cannot establish a new connection with "Initial Catalog=foo" in the connection string.
Christopher Wells Send private email
Saturday, November 03, 2007
 
 
If the technique of keeping the connection open (and issuing the “USE FOO” command) works then why do you need to establish a new connection? Can’t you simply continue to use the existing connection, so that the problem doesn’t arise?

Or do you mean that the problem occurs when a different application (possibly running several minutes later) tries to connect to the new database for the first time?
Mike Green Send private email
Saturday, November 03, 2007
 
 
> Or do you mean that the problem occurs when a different application (possibly running several minutes later) tries to connect to the new database for the first time?

No it's not that; it's just the first few seconds.

> If the technique of keeping the connection open (and issuing the “USE FOO” command) works then why do you need to establish a new connection?

The help for USE says "When logging in to Microsoft® SQL Server™, users are usually connected to the master database automatically."

I felt (perhaps wrongly) that for most transactions to a specific database it's better to connect to the user-specific database directly (and for that reason I have only been omitting the "Initial Catalog" in my createDatabase method, and I've been including it in the connection string for all other methods which do SELECT and so on).

I've found that even a subsequent connection without "Initial Catalog" and with USE can work immediately; it's only a subsequent connection which uses "Initial Catalog" that will fail for the first few seconds after the database is created.

Is there any downside to omitting the "Initial Catalog" and doing USE as the first comand of every new connection?
Christopher Wells Send private email
Saturday, November 03, 2007
 
 
>> Is there any downside to omitting the "Initial Catalog" and doing USE as the first command of every new connection?

I don’t know the answer to that, although it is obviously a bit cumbersome to specify USE on every new connection.

>> I've found that even a subsequent connection without "Initial Catalog" and with USE can work immediately;

That’s probably re-using the same connection that was just closed, via connection pooling. That’s probably why that works.

We have an application that creates a database and switches the connection to the database with USE. It then goes on to create the tables etc. That component of our application is self-contained. It typically only runs once when the application is installed. For that reason, we don’t need to re-establish the database connection within that component. All of the other components of our application open connections by specifying “Initial Catalog”. Could your application follow the same model?

Having said that, I’ve just tried your sample code and it works every time, without passing through the try/catch loop at all. That suggests to me that it should be possible for you to configure your SQL Server and/or your connection string to achieve the same result, although precisely what needs re-configuring on your system isn’t clear to me.
Mike Green Send private email
Saturday, November 03, 2007
 
 
> I don’t know the answer to that, although it is obviously a bit cumbersome to specify USE on every new connection.

I can hide the USE statement in the method which creates and returns a new connection instance. I'm mostly worried about whether a user may not have permission to login to the default "master" database (whatever that is), though they would be allowed to login to a specific database.

Anyway, I found the cause:

1) Before I create the specified database, I verify that the database doesn't already exist: I do this by trying to connect to it with a connection string that uses "Initial Catalog". I expect this to fail.
2) I create the database using USE.
3) I try to connect to it, with a connection string that uses "Initial Catalog": this was failing.

The fix is to disable connection pooling, because otherwise 3) will reuse the connection from 1) which has cached the login failure status (I guess it doesn't even try to login the 2nd time).

Thanks for your help.
Christopher Wells Send private email
Saturday, November 03, 2007
 
 
I didn’t think of that. Our equivalent of your step 1 is to open the connection without using “Initial Catalog”. Then issue a USE to generate an expected exception to check the non-existence of the database. Step 2 can use the same connection as step 1. Step 3 is then guaranteed to use a new connection, regardless of the previous connection pooling setting, because its connection string is different to the connection string used for steps 1 and 2.
Mike Green Send private email
Saturday, November 03, 2007
 
 
> issue a USE to check the non-existence of the database

That seems like the best, thanks.

Incidentally the help for connection pooling recommends always using USE when there's more than one database.
Christopher Wells Send private email
Sunday, November 04, 2007
 
 
"3) will reuse the connection from 1) which has cached the login failure status (I guess it doesn't even try to login the 2nd time)."

Hmmm, I would personally call that a bug. I wonder what Microsoft would say about it. The connection pooling code should not pool connections that are invalid. At the very least it should retry the login instead of just failing. We've written our own connection pooling code for Java and I remember that this was an issue with our code as well. It is tricky because you need to distinguish between errors on a connection due to it being offline/inaccessible and normal SQL errors from invalid statements and such. There is no facility for this in JDBC so you end up writing manual tests. I would think that the same thing applies to .NET as well.
anon
Monday, November 05, 2007
 
 
[sorry for the delay in following up - I kind of lost the thread]
>Thank you for trying it: when it Works on your machine does it work first time [..]

Yes, first time through the loop.

> I'm mostly worried about whether a user may not have permission to login to the default "master" database (whatever that is), though they would be allowed to login to a specific database.

My understanding is that logins are server-wide, and are granted permissions to individual databases.  That is, you don't log in to a database per se, so it shouldn't be a problem.
Brian McKeever Send private email
Thursday, November 08, 2007
 
 
All this crap, stress and frustration just to create a database connection using .Net is the reason why I don't USE .Net for database development.....if you are using a small-scale database, us MS Access....none of these issues exist within this app.

.Net is great when you need it for NON-database oriented apps but once you start looking to connect to a database, .Net becomes a problem.

However, having said that, you might want to think about using a different backend instead of anything SQL-based.

VistaDB is one option that appears to be very easy to use to connect to .Net front ends.

IN my opinion, it shouldn't take more than a few lines of code (if that) to connect to a database. It's just ridiculous that it takes 20-30 lines of code just to connect to a database.....what a joke.
Brice Richard Send private email
Saturday, November 10, 2007
 
 
> IN my opinion, it shouldn't take more than a few lines of code (if that) to connect to a database. It's just ridiculous that it takes 20-30 lines of code just to connect to a database.....what a joke.

Which part of

      sqlConnection = new SqlConnection(connectionString);
      sqlConnection.Open();

looks like 20 lines?  Why do you think the fault lies with .net?  It seems to me like a problem with the server, that for some reason it hasn't fully loaded the newly created db.  But whatever, get on your hobby horse and rock away, troll.
Brian McKeever Send private email
Monday, November 12, 2007
 
 
> It seems to me like a problem with the server

It was a problem with the client machine's connection pool's having cached a previous login failure; but yes, the primitive ADO.NET APIs are no less elegant than any other SQL API I've seen.

> My understanding is that logins are server-wide, and are granted permissions to individual databases.  That is, you don't log in to a database per se, so it shouldn't be a problem.

The help suggests that if an Initial Catalog isn't specified in the connection string then you're logging-in to the "master" catalog by default; oh well, I'll test that eventually: if it's a problem then I'll disable collection-pooling just for the "CREATE DATABASE" sequence (to avoid polluting the connection pool with the anticipated login failure).
Christopher Wells Send private email
Tuesday, November 13, 2007
 
 
Yes, I somehow missed those posts where you clarified that.  Oops.
Brian McKeever Send private email
Wednesday, November 14, 2007
 
 
Why on earth do you want to create a SQL database from code
to start with ???
Hamish McCreight Send private email
Saturday, November 24, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz