Working with Databases using Entity Framework

A look at various ways of accessing data within C# 7 applications, from the traditional ADO.Net models to Entity Framework and LINQ.

Let's first start by looking at ADO.Net.

ADO.Net Object Model

The database on the server (MSSql, Oracle, MySql, etc...) is accessed by ADO.Net using the SqlConnection object. A SqlCommand object is used to query the database, and the results are stored in a SqlDataReader (connected mode) or a DataSet (disconnected mode) object. You can manipulate the data or present it with a data-aware control from these two objects.

Connected mode means that the connection between the client and server is always open; disconnecting means that once the query has run, the data set is stored in memory, and the connection is closed.

Connecting to Databases in Connected Mode

string connectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=""C:SQL Server 2000Sample DatabasesNORTHWND.MDF"";Integrated Security=True;Connect Timeout=30;User Instance=True";

SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();

string commandString = "SELECT * FROM Customers";
SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
SqlDataReader dataReader = sqlCmd.ExecuteReader();

while (dataReader.Read())
{
  Console.WriteLine(String.Format("{0} {1}", dataReader["CompanyName"], dataReader["ContactName"]));
}
dataReader.Close();
sqlCon.Close();

As we have said, the SqlDataReader works in connected mode, so each call to SqlDataReader.Read will stream data from the server. It is also sequential, so once a record has been read, you can't access it again without executing the sqlCommand.ExecuteReader method again. The time between sqlCon.Open() and sqlCon.Close(), there is a connection to the server. If your application opens the connection on start-up and doesn't close it until it exits, you can cause unnecessary records or table locks. The way around this is using a DataSet, which will download a local copy of a subset of the main database that can be manipulated, and changes will be sent back to the server later.

Connecting to Databases in Disconnected Mode

The disconnected mode will execute a query on the server and return the results in a DataSet object. This Data can then be used to display data and modify, insert or delete records without a database connection. The changes are sent back to the server when all changes have been made.

string connectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=""C:SQL Server 2000Sample DatabasesNORTHWND.MDF"";Integrated Security=True;Connect Timeout=30;User Instance=True";

string commandString = "SELECT * FROM Customers";

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(commandString, connectionString);

da.Fill(ds,"Customers"); // Customers is the name of the DataTable
            
foreach (DataRow dr in ds.Tables[0].Rows)
{
  Console.WriteLine(String.Format("{0} {1}", dr[1].ToString(), dr[2].ToString())); 
}

Updating in Connected Mode

The important thing to remember about the connected mode is that updates are processed as a command is executed. Disconnected methods allow the data to be previewed and further amended before being committed to the server.

Additional SQL commands must be created after the first reader has been closed, or you will have to create new objects.

string connectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=""C:SQL Server 2000Sample DatabasesNORTHWND.MDF"";Integrated Security=True;Connect Timeout=30;User Instance=True";

SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();

string commandString = "SELECT * FROM Customers";
SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
SqlDataReader dataReader = sqlCmd.ExecuteReader();

while (dataReader.Read())
{
  Console.WriteLine(String.Format("{0} {1}", dataReader["CompanyName"], dataReader["ContactName"]));
}
dataReader.Close();

commandString = "UPDATE Customers SET ContactName = 'Jane Doe' WHERE CustomerID = 'ALFKI'";
sqlCmd = new SqlCommand(commandString,sqlCon);
sqlCmd.ExecuteNonQuery();
            
sqlCon.Close();

Updating in Disconnected Mode

Disconnected updates are processed by manipulating the DataSet object. The Rows indexers access the record and the field.

ds.Tables[0].Rows[0][2] = "Jane Doe";

You will also need to specify an Update or Insert command to the data adaptor to instruct the server on how it should update the table. Luckily, there is a built-in tool called SqlCommandBuilder that helps with this.

SqlCommandBuilder myBuilder = new SqlCommandBuilder(da);
da.UpdateCommand = myBuilder.GetUpdateCommand();

Changes to the DataSet are not committed to the server until the Update method is called from the DataAdapter.

da.Update(ds, "Customers");

You can see all the updated records (or those that have not been updated) using a DataView and a DataViewRowState object. You can filter the current data grid view, or you can create a new data grid view with only the updated records.

DataView dv = new DataView(ds.Tables["Products"]);
dv.RowFilter = DataViewRowState.ModifiedCurrent;
dataGridView1.DataSource = dv;

Entity Framework

Now that we have seen how the underlying mechanisms work, we can look at how Entity Framework works and how it interacts with data much more conveniently.

Entity Framework enables developers to work with data using objects of domain-specific classes without focusing on the underlying database tables and columns where this data is stored.

With the Entity Framework, developers can work at a higher level of abstraction when they deal with data and can create and maintain data-oriented applications with less code than traditional ones.

Entity Data Model

The very first task of the Entity Framework API is to build an Entity Data Model (EDM). EDM is an in-memory representation of all metadata - its conceptual model, storage model, and mappings between them.

Entity Framework translates LINQ-to-Entities queries to SQL queries for relational databases using the EDM and converts results back to entity objects.

EF API infers INSERT, UPDATE, and DELETE commands based on the state of entities when the SaveChanges() method is called. The ChangeTrack keeps track of the states of each entity as and when an action is performed.

Context Class in Entity Framework

The Context class in Entity Framework is derived from DbContext. It is an important class in Entity Framework, representing a session with the underlying database.

public class OrderContext : DbContext
{
    public OrderContext()
    {
    }
        
    public DbSet<Items> Items { get; set; }
    public DbSet<Customer> StudentAddresses { get; set; }
    public DbSet<Grade> Grades { get; set; }
}

The context class is used to query or save data to the database. It is also used to configure domain classes, database-related mappings, change tracking settings, caching, transactions, etc.

You can generate the context and entities for existing databases using EDM wizard integrated into Visual Studio.

This is done by adding a new item to the solution. Right-click on the project in the solution explorer, then Add, then New Item. This will open the Add New Item window. Select "ADO.NET Entity Data Model" and provide an appropriate name to the EDM; click the Add button. This will show a connection wizard where you can connect to the database and include the tables and views you wish to include.

When you finish the wizard, it will generate a .edmx file that will be added to your project, along with the EDM classes. You can view these by expanding the created .tt T4 template file.

We can then start querying our database using LINQ and our Entity Framework context.

Querying Data with Entity Framework

using (var context = new OrderContext())
{
  var query = context.Orders
    .where(o => o.OrderNumber == "ABC123")
    .FirstOrDefault<Order>();
}

Insert Records with Entity Framework

We can do a simple DB insert by creating a new Order class and adding it to the context.

using (var context = new OrderContext())
{
  var ord = new Order()  
  {
    OrderNumber = "DEF456",
    Customer = "Charlene Conner"
  };
  
  context.Orders.Add(ord);
  context.SaveChanges();
}

Updating Records with Entity Framework

We can update data in Entity Framework by easily querying the existing data, updating the model, and saving it in the database.

using (var context = new OrderContext())
{
  var ord = context.Orders.First<Order>(); 
  ord.Customer = "Gerard Valdez";
  context.SaveChanges();
}

Deleting Records with Entity Framework

Using a similar code pattern, we can delete records as well.

using (var context = new OrderContext())
{
  var ord = context.Orders.First<Order>();
  context.Orders.Remove(ord);
  context.SaveChanges();
}

ADO.Net Connection String Collection

This table contains a few more common connection strings for connecting ADO.Net to data sources other than SQL Server. It contains connection strings for MySql, Access, Excel, CSV, and other files.

Microsoft SQL Server File (local)

Data Source=(local); Initial Catalog=DatabaseName; Integrated Security=SSPI

Microsoft SQL Server (server)

Server=(hostname); Database=DatabaseName; Trusted_Connection=Yes

Active Directory Service (OLEDB)

Provider=ADSDSOObject; User Id=username; Password=password

Microsoft JET (Access) (OLEDB)

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\some\path\filename.mdb

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\some\path\filename.mdb; Jet OLEDB:Database Password="MyDbPassword"

Microsoft Excel XLS

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\some\path\filename.xls; Extended Properties="Excel 8.0;HDR=Yes"

MySql

Please see the tutorial Connecting ASP.Net to MySql.

IBM DB2 / AS400 / iSeries

Please see the tutorial series Querying iSeries DB2 with ADO.Net.