Entity Framework Console Applications with SQL Server Compact

Microsoft SQL Server Compact Edition is an embedded database system that allows us to integrate it in our Web, Desktop and Mobile applications.

SQL Server Compact Edition has following features:

• Free to use and distribute.
• Support for web, desktop, and mobile devices.
• Small footprint for easy deployment.
• Fully embeddable architecture.
• No administration.
• Single file, code-free database format.
• Support for non-admin, embedded installations.
• Support for a subset of syntax and data types.
• Integration with Microsoft Visual Studio.
• Support for various programming languages.

Top reasons for using SQL Server Compact Edition:

• Compact 4.0 has been optimized and tuned for use for ASP.NET web applications and has been field tested to ensure that Compact 4.0 can handle the load of starter websites and scale up to a database size of 4 GB.
• Compact 4.0 is the default database for Microsoft WebMatrix, which is the web stack that encapsulates all the technologies like ASP.NET, IIS Express, Editor and SQL Server Compact that are needed to develop, test and deploy ASP.NET websites to third party website hosting providers.
• Popular open source web applications like mojoPortal, Orchard, Umbraco etc. support Compact 4.0 and can be used to rapidly develop, test and deploy websites.
• Compact 4.0 works in partial trust that is the mainline scenario in which the ASP.NET web applications are deployed. The virtual memory used by Compact 4.0 has been optimized to ensure that that maximum allowed 256 connections can be opened for the database.
• As the requirements grow to the level of enterprise databases, the schema and data can be migrated from Compact to SQL Server using the migrate option in the WebMatrix IDE. This also adds a web.config xml file to the project that contains the connection string for the SQL Server. Once the migration completes, the website project seamlessly switches from using Compact to SQL Server.

Now, let’s create a Console Application and use the SQL Server's Compact Edition.

Step 1: Create Console Application

Create a new console application File > New > Project > Visual C# > Console Application.

Step 2: Add Compact Edition Database

Right click on ‘Solution Explorer’ to add a new item and select ‘Local Database’, rename it to ‘Student.sdf’.

Now, you will get a ‘Data Source Configuration Wizard’, cancel it for now.

Step 3: Create a Table

In the ‘Server Explorer’, you will have the database named ‘Student.sdf’, expand it to add a new table.

In the 'New Table' window, create following fields and don’t forget to setup ‘ClassId’ as auto-increment.

Now, let’s insert some dummy data in the Compact Edition Database.

Step 4: Add Entity Data Model

Right click on Solution Explorer to add a new item and select ‘ADO.NET Entity Data Model’, leave the default name.

Now, in the ‘Entity Data Model Wizard’ select ‘Generate from database’ and click on Next and then click the 'New Connection' button.

In the ‘Connection Property’ window, change the ‘Data source’ to ‘Microsoft SQL Server Compact 4.0’. Browse and select the database from the project directory (you will find the database on project's root). Click on 'Test Connection', if pass the test go ahead.

Now, you will have following screen, click on ‘Next’.

And select the table which is by name ‘Class’.

Now, you have Database Model and DbContext by name ‘StudentEntities’. To see the 'DbContext' name you need to expand the 'Model1.edmx' file and open 'Model1.Context.cs' file.

Step 5: Coding

Now, I’m done with database part, let’s write some code to get the database records on screen.

using System;

using System.Linq;

namespace ConsoleApplication5_Compact

{

    class Program

    {

        static void Main(string[] args)

        {

            using (var context = new StudentEntities())

            {

                var stdQuery = (from d in context.Classes

                                select new { Class = d.ClassName, Teacher = d.ClassTeacher });

                foreach (var q in stdQuery)

                {

                    Console.WriteLine("Class Name : " + q.Class + ", Class Teacher Name : " + q.Teacher);

                }

                Console.ReadKey();

            }

        }

    }

}

Now, run the application and you will get your data on screen.

So, in this post you learnt how to use SQL Server Compact Edition in Console Application. In the same way we can use it with MVC, Web Forms or Mobile Application.

I hope you like it. Thanks.