Using Entity Framework & ADO.Net Data in C# 7

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

Active Data Objects are a collection of classes and interfaces that manage database access within the .Net Framework. Entity framework is an open-source object-relational mapping (ORM) framework which sits on top of ADO.Net and provides a much easier interface for working with the objects.

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. From these two objects, you can manipulate the data or present it with a data-aware control.

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 previously, 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 record or table locks. The way around this is by using a DataSet which will download a local copy of a subset of the main database that can be manipulated and changes sent back to the server later on.

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 DataSet can then be used to display data, modify, insert or delete records without a database connection. When all changes have been made, the changes are sent back to the server.

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, whereas using Disconnected methods allows 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 to help with this called the SqlCommandBuilder.

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 the records that have not been updated) by 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 we have seen how the underlying mechanisms work, we can take a look at how Entity Framework works and how to interact with data in a much more convenient way.

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 compared with traditional applications.

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 the entire 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 also 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 a class which derives from DbContext. It is an important class in Entity Framework, which represents 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 and add it to your project, along with the EDM classes. You can view these by expanding the created .tt T4 template file.

We can then go ahead and 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 for the existing data, updating the model and saving it back to 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 of the 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 a few 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.