Dropping SQL Tables if they exist / Creating if they don't (Within C#)

2

I am trying to create a C# Method to DROP tables if they exist and to create if they do not.

So far I have created a SQL query which is TRYING to delete the tables if they exist.

However I am getting this error

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'NULL'.

This is my code

        static void CheckTablesExist()
        {
            try
            {
                // Build connection string
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder
                {
                    DataSource = "WIN10-LAP-HJP",
                    UserID = "sa",
                    Password = "Mypassword123",
                    InitialCatalog = "SAPExtract"
                };

                // Connect to SQL
                Console.Write("Connecting to SQL Server ... ");
                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {

                    Console.WriteLine("Done.");
                    Console.WriteLine("Checking If table dbo.FolderInfo");

                    string queryCheckTable = "if object_id(@FolderTable, @FileTable) NOT NULL DROP TABLE dbo.FolderInfo ";

                   // string queryCheckTable = "DROP TABLE IF EXISTS @FolderTable";

                    using (SqlCommand command = new SqlCommand(queryCheckTable, connection))
                    {
                        command.Parameters.Add("@FolderTable", SqlDbType.NVarChar).Value = "dbo.FolderInfo";
                        command.Parameters.Add("@FileTable", SqlDbType.NVarChar).Value = "dbo.FileInfo";

                        connection.Open();

                        var result = command.ExecuteNonQuery();

                        // Check Error


                    }
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
        }
c#
sql
sql-server
asked on Stack Overflow Jan 16, 2020 by coderStew • edited Jan 16, 2020 by Dale K

1 Answer

2

You are receiving the syntax error because you are missing the word IS before NOT NULL. The following SQL should work:

if object_id(@FolderTable, @FileTable) IS NOT NULL DROP TABLE dbo.FolderInfo
answered on Stack Overflow Jan 16, 2020 by dangeruss

User contributions licensed under CC BY-SA 3.0