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 the 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 by 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 the maximum number of connections that is allowed, 256, 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..." then "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 the 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 in the project's root). Click on "Test Connection". If it passes the test then proceed.

 

Now, you will have the following screen, click on 'Next'.

 

And select the "Class" table, as in:

 

Now, you have a Database Model and DbContext named "StudentEntities". To see the "DbContext" name you need to expand the "Model1.edmx" file and open the "Model1.Context.cs" file.

 

Step 5: Coding

 

Now, I'm done with the database part, let's write some code to get the database records on the 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 the screen.

 

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

 

I hope you like it. Thanks.