C# tutorial: Entity Framework


Using Entity Framework to manipulate data of SQL Server database

Entity Framework is a new data access technology of Microsoft. It was firstly included in Visual Studio 2008 and .NET Framework 3.5 Pack1. Entity Framework is a mapping mechanism between Entity Data Model and Data Store. With the old data access technologies such as ADO and ADO.NET, you need to understand the schema of the database before the data in the database can be retrieved and manipulated. Retrieving and manipulating the data required a lot of time. The Entity Framework allows you to work with the database at the higher level. You don't work directly with the database. Rather, you work with the entity data model that describes your business objects. You will use classes generated from the model to retrieve and manipulate data in the database.

In this tutorial, i am going to show how to use Entity Framework to get data from a database stored in SQL Server Express and to manipulate the data in that database from Visual Studio C# 2010 Express.

Now let's start with a simple database called BookStoreDB. This database contains only two tables: Books and Suppliers. The Books table store data of books. Each book contains id, title, and author. The Suppliers table contains id, name, and address.

Books table

Suppliers table

 

A supplier can supply many books. A book is only be supplied by a supplier. So the relationship between the Books and Suppliers is one-to-many.

Books and Suppliers relationship

To retrieve the data from the database and manipulate that data, first you need to create a new project in Visual Studio C# 2010 Express. In this example, i create a console project called EFproject. Then add the ADO.NET Entity Data Model to the project. To add the ADO.NET Entity Data Model to the EFproject, click Project menu->Add New Item....You will see the Entity Data Model wizard that allows you to generate the model from the database.

entity data model wizard

When you see the Choose Your Data Connection window, click the New Connection button to create a connection to the SQL Server Express database. You will see a dialog that allows you to choose a database file. Click browse to select the database.

choose database file

After you selected the database file, click Test Connection button to test whether the connection is successful. Then click OK to continue. On the Choose Your Data Connection window, click next to continue. You will see a window that displays three checkboxes: Tables, Views, and StoreProcedures. Tick the Tables checkbox to select all tables in the database. Then click Finish button.

select tables

You will get the data model as shown in the picture below.

data model generated

If you click to expand the Data Source bar, you will notice that there are two object sets created for you. These object sets are Books and Suppliers. The Books object set may contain many Book objects and Suppliers object set also may contain many Supplier objects.

classes created from data model

Two entity types: Book and Supplier are also created. You will use the Book type to create Book object and Supplier type to create Supplier object.

entity types

Before writing code to retrieve and manipulate data of the database, in Visual Studio C# 2010 Express, you need to modify the connection string to connect to the SQL Server Express instance. The original connection string generated for you is stored in the App.Config file as shown below.

App.Config file


<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="BookStoreDBEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl; provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\BookStoreDB.mdf;
Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>

The content of the App.Config file should be modified to connect to the SQL Server Express instance as shown below:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="BookStoreDBEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;
provider connection string=&quot;Data Source=ASPIREV5\SQLEXPRESS;
Initial Catalog=BookStoreDB;Integrated Security=True;Connect Timeout=30;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>

Now you are ready to write C# to interact with the database through the Entity Data Model. In the class Program under the Main method, create a method called queryBooks. This method will be called from the Main method to read all books from the database.

static void queryBooks()
{

using (var context = new BookStoreDBEntities())
{

//refer to the Books object set
  var books = context.Books;

//show all books' data

  foreach (var book in books)
   {
    Console.WriteLine("ID={0},Title={1},Author={2}", book.id, book.title, book.author);

  }

 }
}

In the Main method, write the following code to call the queryBooks method:

queryBooks();
Console.Read();

C# code to get data from database

To add a new book to the Books table, you will create a new method. Here is the addBook method that can be called to add a new book to the Books table:

static void addBook()
{

using (var context = new BookStoreDBEntities())
{

//create Book object

  Book bookToAdd = new Book();

//assign its properties
   bookToAdd.id =3;
   bookToAdd.title = "JSP";
   bookToAdd.author = "SOK";
   bookToAdd.suppplier = 2;

//add the Book to the object set Books
  context.Books.AddObject(bookToAdd);

//save change to the database
  context.SaveChanges();

  }

}

To update a book, you can use Linq query to read the book object that you want to update. Then assign new values to the properties of the object. Below is the updateBook method that can be called to update supplier of a book.

static void updateBook(int bid, int newsupp)
{
using (var context = new BookStoreDBEntities())
{
 //get the book to update
 var bookToUpdate = from b in context.Books
 where b.id==bid
 select b;
 //change the supplier of the book
 foreach(var book in bookToUpdate)
  book.suppplier =newsupp;
 //save change to the database
 context.SaveChanges();

 }
}

To delete a book from the database, you can use the DeleteObject of the BookStoreDBEntities context class. See the example code below.

static void deleteBook(int bid)
{
using (var context = new BookStoreDBEntities())
{
  var bookToDelete = from b in context.Books
  where b.id == bid
  select b;
  foreach(var book in bookToDelete)
   context.DeleteObject(book);
  context.SaveChanges();

   }
}



Comments

CAPTCHA image




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.