Deploying SQL Express database

[Elroacho]

I have two applications and one database.

One application sits on a server with the database.

This application runs each night and updates the database.

The other application is installed on a number of PCs connected to the server.

This application only reads from the database.

I have all this working fine on my Development PC where everthing is local.

I now have to create and installation for each of the applications and of course be able to deploy the database

The approach I have taken is to write a small app to install an ODBC drive that is used by the server app to get data from an external interbase database. The small app is also tries to create/copy/attach my database

This small app is included with the install of the server application and is intended to only be run once after main app has been installed. This is where I am currently having problems.

I have the following code...

tmpConn.ConnectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=True;Connect Timeout=30;User Instance=True";
SqlCommand myCommand = new SqlCommand(@"USE master
                                        CREATE DATABASE CDEvolution ON 
                                      ( FILENAME = N'C:\Program Files\PCIT\CDEovlution\CDEvolution.mdf' ),
                                      ( FILENAME = N'C:\Program Files\PCIT\CDEovlution\CDEvolution_log.ldf' )
                                        FOR ATTACH
                                        GO", tmpConn);
try
{
     tmpConn.Open();
     myCommand.ExecuteNonQuery();
     Console.WriteLine("Database has been created successfully!");
}
catch (System.Exception ex)
{
     Console.WriteLine(ex.ToString());
}

Now if I use the above sql from the command prompt using sqlcmd it works and creates a link to teh database in my application folder. I know this becase if I then do drop command on the database the mdf and ldf files disappear. So I know the SQL is sound.

When I run the app the first time I get the "Database has been created successfully!" message, but if I connect using sqlcmd and do the "select name from sys.databases" I only get the four default databases.

If I run it a second time I get the following message...

System.Data.SqlClient.SqlException: Database 'CDEvolution' already exists.
Changed database context to 'master'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, 
      SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at PostInstall_Setup.Program.CreateDatabase()

Should I expect to see my database when I do a "select name from sys.databases" querry. Or because it's only an attach would it not show up?

Is this the right approach? If so what am I doing wrong?

I am expecting that I may have another challenge when it comes to connecting the client applications to the database but I am new to this and trying to take each issue one at a time.

Hope this is making sense, so if anyone has any advice on if I am doing it right or if not how I should do it please shout out.

I have one week to get this sorted so the pressure is on.

Thanks in advance for your time and help.

Cheers,

Kevin.