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.

Using SQLite with Entity Framework in Visual Studio 2010

Introduction

In my previews blog post we have seen how using a simple “data provider” provided by http://sqlite.phxsoftware.com/ can make our life easier in context of SQLite in .net. In this short article we are going to see how we can use SQLite to generate Entity Framework Data model.

Choosing the Provider

First thing first create any type of project in Visual studio 2010. But I would prefer an web application or windows forms or perhaps a WPF application. Now click on project and select add new item to add a new item type. From Add new item wizard select “Ado.net Entity Data Model” and click on add this will brought up the Entity data model wizard.

• Step 1: Select Generate from Database
• Step 2: In next window select New connection
• Step 3: Connection properties window will popup and the select right kind of provider for SQLite.
• Step 4: After that the Connection properties will have browser or New db,

• Step 5: Now select existing db if you have using browser or New to create new db.
• Step 6: Continue with the wizard to select database table items to include in model.
• Step 7: Click finish to close the wizard and a data model will be generated for you.

If you had any relationship in the database those will be automatically be synchronized in data model, that’s it we are good to jump in the code to retrieve data and do all type of necessary stuff that we must do in a database. Best of luck and happy coding.