How can I fix the "File Activation Error" error message in SQL, from a program written in C#?

Question

I write a C# program which uses a database, Matches.mdf.

I want to test the database file existence, using File.Exists routine (codes will come at the end of the question). If the file doesn't exist, the program creates a new database with the above name. To test the database existence routine, I renamed the database file, but when I wanted to create the database, I got the following error message: Database "Matches" already exists, please specify a different name.

At a second test, I used a database dropping routine before calling the creating routine. Big mistake. Every time I try to create the Matches.mdf database, I get the following error message:

I am sure that the cause of this error message is me, tinkering around, because the same database creation and deletion routines worked fine before.

I know I can solve the problem by changing the path of the database file, but I want to know what exactly I broke up here so I know for next time.

What I am asking is: what can I do to solve the above error?

Later edit: I tried to manually recreate the Matches.mdf using the query tool from SQL Server Object Explorer from VS 2019. Worked perfectly, but I don't think it's a good solution long term.

Necessary codes:

Variable declarations:

static readonly string DatabaseFolder = Path.GetDirectoryName(Application.ExecutablePath) + "\\db";
readonly string DatabaseFile = DatabaseFolder + "\\Matches.mdf";
readonly string DatabaseLog = DatabaseFolder + "\\MatchesLog.ldf";

The function that checks the database file existence:

    public bool DatabaseExists()
    {
        return File.Exists(DatabaseFile);
    }

The database creation routine:

 private bool CreateDatabaseFile()
        {
        SqlConnection MyConn = new SqlConnection(CreateDatabaseConnectionString);
        string Str = "Create Database Matches on Primary (Name=Matches, Filename='@DatabaseFile') log on (Name=MatchesLog, Filename='@DatabaseLog')"; 
        SqlCommand DatabaseCreationCommand = new SqlCommand(Str, MyConn);
        DatabaseCreationCommand.Parameters.Add("@DatabaseFile", SqlDbType.Text).Value = DatabaseFile;
        DatabaseCreationCommand.Parameters.Add("@DatabaseLog", SqlDbType.Text).Value = DatabaseLog;
        try
        {
            MyConn.Open();
            DatabaseCreationCommand.ExecuteNonQuery();
        }
        catch (SqlException S)
        {
            MessageBox.Show(S.Message);
            return false;
        }
        catch (IOException I)
        {
            MessageBox.Show(I.Message);
            return false;
        }
        catch (InvalidOperationException I)
        {
            MessageBox.Show(I.Message);
            return false;
        }
        catch (InvalidCastException I)
        {
            MessageBox.Show(I.Message);
            return false;
        }
        finally
        {
            MyConn.Close();
        }
        return true;
    }

The database deleting routine:

public void DeleteDatabase()
        {
            string Str;
            SqlConnection MyConn = new SqlConnection(CreateDatabaseConnectionString);
            Str = "Alter database Matches set single_user with rollback immediate\r\ndrop database Matches";
            SqlCommand command = new SqlCommand(Str, MyConn);
            try
            {
                MyConn.Open();
                command.ExecuteNonQuery();
            }
            catch (SqlException S)
            {
                MessageBox.Show(S.Message);
            }
            catch (IOException I)
            {
                MessageBox.Show(I.Message);
            }
            catch (InvalidOperationException I)
            {
                MessageBox.Show(I.Message);
            }
            catch (InvalidCastException I)
            {
                MessageBox.Show(I.Message);
            }
            finally
            {
                MyConn.Close();
            }
        }

The above code(s) worked perfectly before I snooped around the wrong way. — Bogdan Doicin, Jun 03 '19 at 13:01
Even if `CREATE DATABASE` allowed parameters for the file paths (and it doesn't), these wouldn't work/be recognized as parameters when added in single quotes. It's not impossible the database was successfully created before you started testing, but then not by this code -- possibly other code that did some funky textual replacement on the command instead, or else someone undertook to parameterize the statement "properly" without ever testing if it actually worked. — Jeroen Mostert, Jun 03 '19 at 13:05
Interestingly as well, I can't see where the OP is supplying the values for those parameters either. — Thom A, Jun 03 '19 at 13:07
@JeroenMostert you were right. If you want to type an answer, be my guest. — Bogdan Doicin, Jun 03 '19 at 13:48

score 0 · Accepted Answer · answered Jun 03 '19 at 13:47

As it is said here and confirmed by Jeroen Mostert, Create database does not accept queries. The database was created before, using some string concatenation. Afterwards the query string was parametrized, without realizing that this command doesn't take parameters. This is why changing the creating database query to

Create Database Matches

works perfectly.

Well, live and learn!

How can I fix the "File Activation Error" error message in SQL, from a program written in C#?

1 Answers1