CsharpDB

Create A Database Using LIXI2 C# Classes

This post will step through the process of creating a database that conforms to the structure of a LIXI2 schema. Each element will be represented by a DB table, and each attribute will have an equivalent field in the table.

First you must create your LIXI C# classes from a LIXI Schema. If you have not done so already, follow the step by step instructions on how to Create LIXI C# classes from a LIXI2 schema.

The tool we will be using to create our LIXI database is Microsoft Entity Framework. Entity Framework is an object-relational mapper (ORM) that enables .NET developers to work with a database using .NET objects. We will be using the code-first approach to Entity Framework.

If you have completed the steps outlined in the post Create LIXI C# classes from a LIXI2 schema you will already have a visual studio project with a Program.cs file and a Schema.cs file containing your LIXI2 classes. You will need to make the following enhancements to this project in order to generate your database:

Add a Context Class

Add this context class to your Program.cs file:

public class PackageContext : DbContext
{
    public DbSet<Package> Packages { get; set; }
}

Note: you will need to add a package reference to the latest version of Entity Framework. You will be prompted to do so by Visual Studio after you have created your context class.

Add Using Statements

Add these using statements to your Program.cs file

using System.Data.Entity;
using System.Collections.Generic;
using System.Linq;

Add this using statements to your schema.cs file:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

Add Primary Keys to Each Class

We need to define a primary key for each class. To do this, we will need to add an 'id' property to each class.

For example, the Package class need an 'id' property annotated with the 'key' data annotation.

The 'XmlIgnore' property annotation is added to prevent the key being included in the serialised XML.

public partial class Package
{
    [Key]
    [XmlIgnore]
    public int id { get; set; }

To do this, edit your schema.cs file - do a find and replace using regular expression

find:

class (.*)\r\n    {

replace:

class \1\r\n    {\n\n\t\t\t\t[Key]\n\t\t\t\t[XmlIgnore]\n\t\t\t\tpublic int id { get; set; }

Change the DateTime type to DateTime2

The SQL server database that is generated will define date time columns using the "datetime" type. This will cause an issue with null values for datetime attributes. The solution is to change the type of these columns to "datetime2". We can do this by executing the following find and replace on our C# classes.

find:

public System.DateTime

replace:

[Column\(TypeName = "datetime2"\)]\n        public System.DateTime

Build the Database

When an object of the DbContext is instantiated the Database will be created:

static void Main(string[] args)
{
    var context = new PackageContext();
}

Inserting a LIXI2 message into the Database

We will create a new method to insert LIXI2 a package into the database. Add this method to open the DB context and insert the LIXI2 package into the DB:

static void InsertPackage()
{
    using (var context = new PackageContext())
    {
        Package package = NewPackage();
        context.Packages.Add(package);
        context.SaveChanges();
    }
}

In this example, we call a function that creates a small LIXI2 package:

static Package NewPackage()
{
    Package package = new Package();
    package.Publisher = new PackagePublisher();
    package.Publisher.CompanyName = "LIXI";
    package.Publisher.ContactName = "John Matthews";
    package.Publisher.Email = "john@lixi.org.au";
    package.Publisher.PublishedDateTimeSpecified = true;
    package.Publisher.PublishedDateTime = new System.DateTime(2020, 1, 1);
    return package;
}

Make sure to update your main method to call the new Insert Package function:

static void Main(string[] args)
{
    InsertPackage();
}

Retrieving a LIXI message from the Database

We can retrieve a LIXI package object by executing an LINQ statement on our DB context element. This function will retrieve a record from the Database and return it as a LIXI message:

static Package RetrievePackage()
{
    using (var context = new PackageContext())
    {
        Package package = (Package)context.Packages
        .Where(x => x.Publisher.ContactName == "John Matthews")
        .Include("Publisher")
        .FirstOrDefault<Package>();
        return package;
    }
} 

Update your main to call our new function.

static void Main(string[] args)
{
    Package package = RetrievePackage();
    Print(package);
}

In this example we are requesting all records where the publishers contact name is "John Matthews" .

.Where(x => x.Publisher.ContactName == "John Matthews")

We can specify which components of the package we want returned using the Include function and the LIXI path of the element to include. The path is relative to the package element. For example, the loan details path is expressed as: "Content.Application.LoanDetails".

In this example we are requesting the Publisher of the message:

.Include("Publisher")

And finally, the 'FirstOrDefault' command will return only the first matching record.

.FirstOrDefault<Package>();

The package is then printed to the console as the LIXI formatted XML message:

<Package>
  <Publisher CompanyName="LIXI" 
             ContactName="John Matthews" 
             Email="john@lixi.org.au" 
             PublishedDateTime="2020-01-01T00:00:00">
  </Publisher>
</Package>

Related Posts

To create LIXI2 C# classes from a LIXI Schema follow the step by step instructions: Create LIXI C# classes from a LIXI2 schema.


Written by:
John Matthews, LIXI Technical Lead
First Published: February 17, 2020