Using SQL Server Compact 4.0 with Entity Framework.

In previous section we have seen, how we can use a use SQL Server Compact 4.0 with visual studio. In this section we would see how to use this with entity framework. We would use sample database provided with the SQL Server Compact 4.0 , i.e. Northwind.sdf database for demonstrating the ideas. Created a sample asp.net project for any purpose program, For how think that we are going to build an application based on Northwind database. Go ahead and add the file in App_Data folder.

Ado.net Entity Data Model

In this section we will see how we can add the Data model, frankly speaking its pretty simple but still explaining it. Bellow I have put down the steps.

1. Add Ado.net Entity Data Model by right click on the project then select Add New Item, from the data page on item category select “ADO.NET Entity Data Model”. Click Add This will bring up Entity Data Model wizard.
2. Select “Generate from Database” In the first page and click Next.
3. In “Choose your data connection” page all connection should be prepopulated since you added a database and opened in “Data Connections”. If not Manually do it by clicking “New Connection”.
4. In “Choose your database object” page select the all objects. and click finish.
5. Your Data Model will be Generated.

Sample Codes

Bellow I have put down few simple code, just to demonstrate few sample code examples. We have added a data manager class, and added few simple methods to manipulate the data.

public class OrderData
    {
        public List<Order> GetAllOrder()
        {
            NorthwindEntities entities = new NorthwindEntities();
            List<Order> orders = entities.Orders.ToList();
            return orders;
        }

        public void AddOrder(Order order)
        {
            NorthwindEntities entities = new NorthwindEntities();
            entities.AddToOrders(order);
            entities.SaveChanges();
            entities.AcceptAllChanges();
        }

        public Order GetOrderById(int orderId)
        {
            NorthwindEntities entities = new NorthwindEntities();
            Order order = entities.Orders.ToList().Where(p => p.Order_ID == orderId).FirstOrDefault();
            return order;
        }

        public void UpdateOrder(Order order)
        {
            NorthwindEntities entities = new NorthwindEntities();
            Order updatingorder = entities.Orders.ToList().Where(p => p.Order_ID == order.Order_ID).FirstOrDefault();
            if (updatingorder != null)
            {
                updatingorder.Order_Date = order.Order_Date;
                //other changes
            }
            entities.SaveChanges();
        }
    }

Section endings

In this section we have seen a simple code and demonstration how we can use Entity Framework with SQL Server Compact 4.0. In next section we will see how we can use SQL Server Compact 4.0 with linq to SQL. Best of luck and happy programming.