Using LinqPad to Access Your Orchard SDF Database

Recently I was trying to setup SSL for my site www.techrepository.in and was playing around with the SSL module in Orchard. I accidently entered incorrect settings in the module which brought down the site and really wanted to bring it back as soon as possible. I definitely didn't want to download the backup, restore it in local and try to correct it by using the CLI tool available in Orchard. When I googled for solutions to resolve this issue, found one site that explained the steps to disable the module by directly updating the datastore of a blog hosted using Orchard.

I was using the SQL CE option as the backend for my blog, so I tried connecting to it using SQL Server Management Studio 2014 but only to found later that it's no longer supported. Again, I resorted to google and found this wonderful utility called LinqPad which support SQL CE databases. So in this post, I will be showing you how to connect the database and run queries against it.

About LinqPad

You may think that LinqPad can be used only for experimenting the Linq queries, but it's much more than that and according to the creators it's an ultimate scratchpad for C#, F# and VB. You can read more about the utility in their site and can be downloaded from here. The installer is around 14 MB only and installation won't take much of your time.

The home screen of the utility is a minmalistic one, has two list boxes on the left for showing the connections and other for showing the saved queries. The big one on the right is the playground where you can write and run queries against your database. 

As I mentioned earlier, apart from executing Linq or SQL queries, LinqPad can also used to evaluate expressions, statements and programs written on C#, F# and VB by selecting the one you need from the Language dropdown and execute/compile it by clicking on the play button.

Connecting SQL CE Database

To add a new connection, click on the Add Connection link in the left and you will get  a dialog as shown below. Here you will chose your datacontext and for me it was the default one.

In the next window you will get the options to select the provider and the database file.

Select SQL CE 4.0 as the provider and then select the sdf file in the Attach Database file section. The Data Context Options will be selected by default and I chose to unselect it as I wanted to see that actual table names. Since I was dealing with my database from Production environment, I chose to select Contains production data. You test the connection is successful or not by clicking the Test button and if everything is well and good, then click on OK to access the datastore.

Now, you will see your database in the connections box and can access the Tables and related info as shown below.

If you right click on a table, you will get a context menu with a lot of Linq statement templates which can be used to manipulate the table.

Clicking on the item in the menu will insert the corresponding linq expression in the right window, with Language selected as C# Expression. To execute it, click on the play button and you will get the output in the results window. The result window has options to show as plain text, in a grid and also it can show the underlying SQL statment which it used for retrieving the data.

To execute an SQL Statment, you can choose SQL from the Language dropdown, write your statement and then click on the play button to execute it. For this one I chose to show the output in a Grid and got the result as shown in the below image.

 

This is just a one use case for LinqPad and you can read much more about in their official documentation here. In the coming post, I will explain the steps I did to disable the module which brought my site down.

Till then Happy Coding


2 Comments

  • Gravatar Image

    You could also use the SQL Server Compact Toolbox, available for both SSMS, Visual Studio and as standalone application https://github.com/ErikEJ/SqlCeToolbox

Add a Comment