Deleting a Table from Orchard's SQL CE Database

by Oliver 21. September 2013 11:08

In day-to-day development on discoverize, our Orchard based custom portal software, I use CompactView to look into my local instance's SQL CE database file if that's what I need to do.

Dropping a Table from my Orchard.sdf Database

Today, I was experimenting with some migrations code and needed to undo some table creation so that I could run an improved version on the same DB. I hoped for CompactView to get the job done, but somehow I couldn't get around how to do it from there. I tried running the following script from its SQL editor:

ALTER TABLE [Discoverize_Management_UsersEntriesPartRecord] DROP CONSTRAINT [PK__Discoverize_Management_UsersEntriesPartRecord__000000000000104D];
DROP TABLE [Discoverize_Management_UsersEntriesPartRecord];

Unfortunately, CompactView told me that the table was currently in use:

image

I was a bit baffled by this error. Somehow I had expected it to just work.

SqlCeCmd to the Rescue

Well, all is not lost. For some tasks I've already used the SQL Compact Command Line Tool in the past and it was waiting to be used again. This is what I first tried (without the newlines):

SqlCeCmd40.exe -d "Data Source=App_Data\Sites\Default\Orchard.sdf" -q "ALTER TABLE [Discoverize_Management_UsersEntriesPartRecord] DROP CONSTRAINT [PK__Discoverize_Management_UsersEntriesPartRecord__000000000000104D]; DROP TABLE [Discoverize_Management_UsersEntriesPartRecord];"

Unfortunately, this query returned an error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 148,Token in error = DROP ]

This error referred to the DROP in the DROP TABLE statement which surprised me but a simple workaround was to just send two queries to the DB instead of a single one:

SqlCeCmd40.exe -d "Data Source=..\src\Orchard.Web\App_Data\Sites\Default\Orchard.sdf" -q "ALTER TABLE [Discoverize_Management_UsersEntriesPartRecord] DROP CONSTRAINT [PK__Discoverize_Management_UsersEntriesPartRecord__000000000000104D]"

(-1 rows affected)

C:\Projects\discoverize\tools>SqlCeCmd40.exe -d "Data Source=..\src\Orchard.Web\App_Data\Sites\Default\Orchard.sdf" -q "DROP TABLE [Discoverize_M
anagement_UsersEntriesPartRecord];"

(-1 rows affected)

Checking back with CompactView, I verified that the table was now successfully deleted.

Do it Step by Step

I'm not a guru with SQL CE databases but verifying that my syntax was correct and breaking the task into smaller pieces already got me back on track.

Happy coding!

enjoyed the post?

Tags:

SQL CE | Orchard

Comments (2) -

ErikEJ
ErikEJ Denmark
9/25/2013 11:28:12 AM #

You can also include the statements in a single script file, and seperate the statements with GO and newline (using SqlCeCmd)

Reply

Martin
Martin United Kingdom
1/14/2015 1:07:44 PM #

Could you not just have called SchemaBuilder.DropTable("TableName")?

Reply

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

About Oliver

shades-of-orange.com code blog logo I build web applications using ASP.NET and have a passion for javascript. Enjoy MVC and Orchard CMS, and I do TDD whenever I can. I like clean code. Love to spend time with my wife and our three children. My profile on Stack Exchange, a network of free, community-driven Q&A sites

About Anton

shades-of-orange.com code blog logo I'm a software developer at teamaton. I code in C# and work with MVC, Orchard, SpecFlow, Coypu and NHibernate. I enjoy beach volleyball, board games and Coke.