SQL database refresh

[161696e57f]

Hi Everyone.

Background :-
Winform App creates database in MSSQL LocalDB & creates tables. Data is imported & analysed then report is created. At any point in the process the user can start a new project & tables are removed to be re-created later as the user progresses through the steps in the application.

Problem :-
The app works perfectly the first time, however when the user starts a new project (second project) the SQL tables are dropped (as planned) however when the app attempts to re-create the tables an error occurs stating that the database is offline. However it is online & accessible through the MSSQL Management Studio.

Not sure which code would be useful to post as it all works the first time round - any ideas on what's happening as everything works fine the first time & through MSSQL management Studio........

[AleMonteiro]

The App creates the DB and the tables, but it drops only the tables? If so, on the second time isn't it trying to create an DB that already exists?

And when the second project is created the first project tables are dropped, so the first project isn't available anymore?

Wouldn't it be better if the project tables were distinct? I mean, use a prefix... project_1_table_1, project_2_table_1...

[161696e57f]

The app isn't designed to retain the projects, so when a new project is started the older one is discarded. Essentially the app performs statistical analysis on the data & displays the results in a UI with an option to export to Excel.

When the second project is initiated then all the app attempts to do is re-create the tables not the entire database as the DB still exists. I have tried dropping the entire database & re-creating it from scratch each time a project is started but come up with the same error (even though I can see everything is OK via MSSQLMS)

There are in excess of 60 statistical tests the app performs & also provides a graphical UI to display the results if the user doesn't want to export to excel so I need the backend in a standard format, with a standard naming convention to enable all this.

Interesting thing is that if I exit the app & then relaunch it all works ok. It only errors on the second attempt so there must be a lock somewhere that I'm missing (have checked that I close all DB connections, etc. before trying the table re-create). Have also tested the SQL code in MSSQLMS & it's the same code that executes correctly the first time so all good there.

This is the code that I'm using (merely changing the SQL code for the various actions)

// Drop the tables within the DataBase on the SQL Server
const string connectionString = @"Data Source(LocalDB)\BenInst;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

SqlCommand cmd = connection.CreateCommand();

cmd.CommandText = String.Format("DROP TABLE BenDB.dbo.OriginalData");
cmd.ExecuteNonQuery();

cmd.CommandText = String.Format("DROP TABLE BenDB.dbo.RawData");
cmd.ExecuteNonQuery();

connection.Close();

[161696e57f]

Here is the create table code from a seperate form :-

// Connect to the SqlLocalDB instance 
const string connectionString = @"Data Source=(LocalDB)\BenInst;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

// Create a new DataTable in BenDB to host the import
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "CREATE TABLE BenDB.dbo.RawData(Master_Value varchar(MAX))";
cmd.ExecuteNonQuery();

[AleMonteiro]

Are you sure you are closing the connection in every case? For the description of the error you might be letting the connection open.

[cgeier]

If you have foreign keys, the order in which you drop the tables is important. If you are re-creating the tables exactly the same, it isn't necessary to drop the tables, only to delete the data.

[arunkumars]

use "using" like below

using (SqlConnection connection = new SqlConnection(connectionString))
{
    // your queries and logic go here.
}

this will ensure that all the connections are closed without any external action like con.close();