This tutorial shows you how to get started with entity framework database first using c# code.
Entity framework is an ORM to help you easily access your sql database. It's developed by Microsoft and fully supports view, stored procedure in SQL Server and is easy to use. You can use EF with database first or code fisrt, but in this post i'm only show you how to use entity framework with database first.
To practice, you should create a new project and download the northwind database. You can view How to Download and Restore Northwind database to SQL Server
Right click on your project->Add->New Item
Enter your model name->Add
Select EF designer from database->Next
Click New Connection button to enter your sql connection
Click Test Connection to verify your sql connection, then click OK button
Select entity framework version->Next
Select the table you want to use, in this tutorial i only use the Customers table to play demo->Next
Click OK button to finish
Rebuild your project, then create a CustomerService class to access customer data from Customers table as shown below. As you can see, the Entity framework automatically generates a customer class corresponding to a Customers table in your database. You can find the Customer class in Model.edmx by clicking arrow button->Model.tt->Customer.cs
using System.Collections.Generic;
using System.Linq;
namespace WebAppDemo
{
public class CustomerService
{
private NorthwindEntities db;
public CustomerService()
{
db = new NorthwindEntities();
}
public List<Customer> GetAll()
{
return db.Customers.ToList();
}
}
}
To filter your data, you can use LINQ to query. The Entity Framework automatically parses LINQ to the SQL statement and executes it
public List<Customer> GetByCustomerId(string id)
{
var sql = from c in db.Customers
where c.CustomerID == id
select c;
return sql.ToList();
}
To insert, update, delete data you can use the following snippet
public bool Insert(Customer customer)
{
db.Customers.Add(customer);
int result = db.SaveChanges();
return result != 0;
}
public bool Update(Customer customer)
{
Customer obj = db.Customers.Find(customer.CustomerID);
if (obj != null)
{
obj.Country = customer.Country;
obj.ContactTitle = customer.ContactTitle;
obj.ContactName = customer.ContactName;
obj.CompanyName = customer.CompanyName;
obj.City = customer.City;
obj.Address = customer.Address;
obj.Fax = customer.Fax;
obj.Phone = customer.Phone;
obj.PostalCode = customer.PostalCode;
obj.Region = customer.Region;
}
int result = db.SaveChanges();
return result != 0;
}
public bool Delete(string id)
{
Customer obj = db.Customers.Find(id);
if (obj != null)
db.Customers.Remove(obj);
int result = db.SaveChanges();
return result != 0;
}
The SaveChanges method returns the total amount of data that has been changed, such as add, update, delete