Access the ARIA Database with the Entity Framework

The Entity Framework

Whenever the Eclipse Scripting API (ESAPI) doesn't provide an object or property we'd like, we can directly access the ARIA database to obtain that information. Typically, this involves writing custom SQL commands within our C# code. And it's painful.

The Entity Framework by Microsoft allows us to interact with a database without writing any SQL commands. It maps tables into collections of C# objects, where each row in a table is mapped to an object, and each field in a row is mapped to a property in that object. Reading fields from a database becomes as easy as getting a value from a property.

In this blog post, I'll walk you through an example that uses the Entity Framework. I recently needed to obtain the full name of a patient's primary physician, but ESAPI's Patient class only provides a PrimaryOncologistId. The Doctor table in the ARIA database contains the information we need, so we'll use the Entity Framework to get it.

Creating the Entity Context

First, create a new class library project in your existing solution. Your solution may be a binary plug-in script or a stand-alone application. (It may even be a simple console application if you're just playing around.) Name the library "AriaEntities" because it will hold the "entity" classes that represent the tables in the ARIA database. Delete the Class.cs file. We won't need it.

Right-click on the new project and choose Add, then New Item. On the left side of the dialog box, choose Data (under Visual C# Items), and then on the right side select "ADO.NET Entity Data Model". Name the new item "AriaEntityContext" and click on Add. (If you don't see the ADO.NET Entity Data Model entry, search for "entity framework tools" on the Internet, and install the appropriate version for your Visual Studio.)

In the dialog box that shows up, choose "Code First from Database." In the next screen, enter the information you need to connect to your ARIA database. For this example, select "Yes" to include the user name and password in the connection string. But in future projects, you may choose not to include them and instead specify them in your code. In the final screen, you get to choose which tables from the database you're interested in accessing. Check only the "Doctor" table as it's the only one we need.

The Entity Framework will generate some new classes and files in your project. The AriaEntityContext class will allow you to access the collection of doctors in the database, and the Doctor class will allow you to access an individual doctor's properties (in our case, the first and last name). These classes also contain commands and attributes that tell the Entity Framework how the database is configured, but I won't go into the details here. The App.config file contains the connection string used to access the database.

When we use it, The Entity Framework will try to access or create an internal table called __MigrationHistory in the database. We don't want this behavior, so let's disable it by adding the following line to the constructor of the AriaEntityContext class:

Database.SetInitializer<AriaEntityContext>(null);

Using the Entity Context

In your main project, add a reference to the AriaEntities project. You'll also need to install and add a reference to the Entity Framework. The easiest way to do that is to use NuGet, which already comes bundled with Visual Studio. In fact, when you created the AriaEntities project, Visual Studio used NuGet to install and reference the Entity Framework there.

You also need to copy the connection string section in AriaEntities's App.config file to your main project's App.config. If your main project doesn't have one, just copy the entire file.

You're finally ready to write some code. Since the Patient object contains the property PrimaryOncologistId, you just need to create a method that, given the physician ID, you return the physician's full name. Here's how you would use your entity class library to do so:

private string GetPhysicianFullName(string physicianId)
{
    using (var ariaContext = new AriaEntityContext())
    {
        try
        {
            Doctor physician = ariaContext.Doctors.First(d => d.DoctorId == physicianId);
            return $"{physician.FirstName} {physician.LastName}";
        }
        catch    // Includes database failures
        {
            return "N/A";
        }
    }
}

Inside the try statement, you access the Doctors collection in the AriaEntityContext object and get the first Doctor whose ID matches with the one you have. You then construct the full name and return it. Here I use a try statement to handle any database problems that may occur, such as no doctors having a matching ID.

Conclusion

This example has been an extremely simple use of the Entity Framework. In many cases, though, that's all we need. In other cases, we may need to use more advanced features of the Entity Framework, and there are a lot.

For example, you can model table relationships and have access to parent or child tables through your entity objects. If you're working with your own database, not only can you write to the database but you can actually create the entire database (and change it easily). Best of all, you don't have to write a single line of SQL!

Please see the Entity Framework Documentation for more information and examples.