SQL Server CE Max Database Size

I am crawling an Internet database so I can apply some spectral graph methods to it. I am using my computer at the university. I have just logged in and found out that my crawler was reporting it had (oddly and partially) finished. Then I found out the error was not that the database owners had detected me (I have implemented the protocols to keep a low profile), but that the local database is capped at 256 MB file size.

Long story short, if you want to use more than 256 MB on a desktop SQL Server CE database, add the parameter Max Database Size=1024 (to increase it to 1 MB) in your connection string. Separate variables with ';'.

Nested queries with SQL Server Compact Edition

Following the previous post, I am finding some small troubles with SQL Server CE and their solutions, and I think they will be useful for anybody working with it.

SQL Server CE does not support nested queries!

The way to circumvent this is by using inner joins, as mentioned in this blog.

My query now reads:

String sql = "SELECT Authors.* FROM Authors INNER JOIN " +
                    "(SELECT AuthorID FROM SubDomain_Authors " +
                    "WHERE (DomainID = " + subdomain.DomainID +
                    ") AND (SubDomainID = " + subdomain.SubDomainID +
                    ")) AS t ON Authors.ID = t.AuthorID";

SqlCeCommand cmd = new SqlCeCommand(sql, con);

                SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                if (rs.HasRows)
                {

Using Visual C# 2008 Express to query a SQL database

I am currently working on building a database that I intend to feed to a graph so that I can explore its spectral properties and derive interesting issues regarding that data.

To do that, I am downloading downloading data from a source, processing it and storing it into a database. Since the data are highly structured, I decided that I would use an SQL-like database. To my comfort, I found out how to use the SQL Server Compact Ediction (v 3.5) that Microsoft ships with a number of its products (I know that v 4.0 ships with Visual Studio 2010, but I don't know where the 3.5 I had installed came from). In any case, while working on the project, I found out that Visual C# itself can be used as a SQL query viewer, to query a database.

These are the steps to query a database from your Visual C# 2008 Express

• Click on the menu "Data" and then "Add new data source" if you have an open project
• Click on the menu "Tools" and then "Connect to database" otherwise
• Click on database
• "New connection"
• Select "Microsoft SQL Server Compact 3.5 (Data provider .NET Framework para Microsoft SQL Server Compact 3.5)"
• Examine to get to your file. You can create a new file with a new filename if you will.
• Assuming that you have data to query: Look for the tables, right-click on it and click on "Show table data", or "New query". In the first case, "SELECT * FROM Table" will be executed, whereas the second case will let you modify the SELECT statement.

MS SQL CE stores the database locally in .sdf files and it is embedded into your program as a SQL engine DLL so you can have access to the objects that interpret statements, access and control files. And by the way, it has a limit of 4GB per data-base file.

It is probably not that big of a deal but it was gladly surprised when I found out I could get this project done with just the Express edition and no supporting tools.