Post
Ask Question

Entity Framework Console Applications With SQL Server Compact

Introduction

Microsoft SQL Server Compact Edition is an embedded database system that allows us to integrate it into our Web, Desktop, and Mobile applications. In this article, we will learn about Entity Framework Console Applications With SQL Server Compact.

Features of SQL Server Compact Edition

  • 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 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 rapidly develop, test and deploy websites.
  • Compact 4.0 works in partial trust, 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 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 containing the SQL Server's connection string. Once the migration completes, the website project seamlessly switches from Compact to SQL Server.

Let's create a Console Application and use the SQL Server's Compact Edition.

Step 1. Create a 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," and rename it to "Student.sdf".

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 set up "ClassId" as an 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," and leave the default name.

Now, in the "Entity Data Model Wizard," select "Generate from database," 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, 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. Code Example

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();
            }
        }
    }
}

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

Conclusion

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.


Similar Articles