In this post we will demonstrate how to connect to MGRID from a Microsoft .Net application. We will extend the MARC-HI Everest framework to easily operate with the data in this application. There are bits of code present in this post that will only work when you set up a complete project in Visual Studio .Net. For a complete example, you can contact us at info@mgrid.net.

Use case

We will be working with a simple use case in this example. We have a table ‘observation’ in MGRID that is defined as:

CREATE SEQUENCE observationSeq;
CREATE TABLE observation(id int PRIMARY KEY DEFAULT nextval('observationSeq'),
                         amount pq);

Setting up the data connection

There is no standard data connector in .Net to connect to an MGRID (PostgreSQL based) database. Luckily though, there is a .Net data provider available from http://npgsql.projects.postgresql.org/. For this example we used the Npgsql2.0.11.91-bin-ms.net3.5sp1.zip version.

Create a project in Visual Studio .Net. We chose a .Net 3.5 Windows Forms project. In your project be sure to reference the Npgsql.dll that is in this archive. To connect to MGRID, use the following statement:

using Npgsql;
..
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;
User Id=mgrid;Password=aaa;Database=postgres;");
conn.Open();

You can now perform a query on the database, for example:

NpgsqlCommand command = new NpgsqlCommand("select * from observation", conn);
NpgsqlDataReader reader = command.ExecuteReader();

You now have a reader object with which you can read records, just like with a ‘normal’ .Net datareader.

Using the MARC-HI Everest framework

First download the MARC-HI Everest framework from: http://te.marc-hi.ca/view.aspx?project=af66d54ed41e4ac18b44d0d3ca6cabf0

This framework has a number of libraries. In this example we will only use the MARC.Everest.dll, so be sure to reference this one in your project. You can now read values from MGRID into MARC Everest objects. Continuing with the datareader example from above:

using MARC.Everest.DataTypes;
..
while (reader.Read())
{
    PQ pq = (string)reader[1];
    pq.Unit = pq.Unit.Trim();
}

The MGRID database returns a PQ object as a string representation, for example ‘10 ml.’ The PQ class from the MARC Everest framework can be instantiated from this string value. Unfortunately, there is a small bug in the framework that causes the Unit part of the object to get a space character (‘ ’) in front of it, hence the .Trim().

We now have a PQ object that has correctly filled Unit and Value properties. To insert a PQ object to MGRID, we can use the .ToString() method of PQ. For example, a command that inserts an observation record in MGRID:

PQ amount;
.. // Fill the amount object
NpgsqlCommand commandInsert = new NpgsqlCommand(
       "insert into observation (amount) values ('" + amount.ToString() + "')",
       conn);

Extending the PQ class

In this example, we will extend the PQ class with an ‘Add’ method that uses MGRID to add two PQ objects. First we define a method:

public static PQ PQAdd(PQ pq1, PQ pq2)
        {
            // SQL template to add 2 PQ objects
            string addCmdSqlTemplate = "select pq '{0}' + pq '{1}'";
            string addCmdSql = String.Format(addCmdSqlTemplate,
                                             pq1.ToString(),
                                             pq2.ToString());
            NpgsqlConnection conn = Conn;
            try
            {
                conn.Open();
                NpgsqlCommand addCommand = new NpgsqlCommand(addCmdSql, conn);

                PQ pqAdd = (string)addCommand.ExecuteScalar();

                // Workaround bug in Marc everest framework
                pqAdd.Unit = pqAdd.Unit.Trim();
                return pqAdd;
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

Here, Conn is a property that creates the connection object to MGRID. We can directly call this method to add two PQ objects, but we can also make an extension method on the PQ class to add this functionality:

    public static class PQExtender
    {
        public static PQ Add(this PQ pq, PQ otherPq)
        {
            return MgridDataAccess.PQAdd(pq, otherPq);
        }
    }

Here MgridDataAccess is the data access class that the PQAdd method is defined in.
We can now do this:

PQ pq1 = "10 ml";
pq1 = pq1.Unit.Trim();
PQ pq2 = "0.1 l";
pq2 = pq2.Unit.Trim();

PQ pqSum = pq1.Add(pq2);

This will result in a pqSum object that will have the .ToString() value of ‘110 ml.’
Note that if you would try to add ‘10 ml’ and ‘0.1 m’, you would get an exception from MGRID; in this case ‘ERROR: 42804: cannot add: unit mismatch between ml and m.’

Conclusion

It is relatively simple to access MGRID from a Microsoft .Net based solution. Other than slight modifications in the data layer, there is no significant difference in how you would build your application than if it were connected to a Microsoft SQL server database, for example. The big difference is that the MGRID Healthcare Datatypes provide a wealth of additional functionality over SQL server databases, which makes building healthcare applications easier.