MagnaDB Framework for .NET

1. Overview

A simple ORM to make things simpler

MagnaDB is a fast and reliable ORM that makes you worry less about databases and lets you code peacefully.
This framework packs Code-First capabilities, as well as Database-First easy integration. It'll go with you, the path you decide to take. No need for complicated migrations, nor strict database access/modification; you do you; just make sure your models and tables match and everything will be just fine.

You're on the No-SQL train? This is for you.
You still love SQL like the first time? This is for you.

MagnaDB is designed to be a Hybrid ORM, letting you manipulate databases objects directly from the code, but also letting the window open databases commands to get in. All this while keeping you safe.

2. Getting Started

In order to get started we first need to install MagnaDB to one of our projects.

For the present example let's create a new Console Application Project for .NET Framework. Let's name it TestMagnaDB (or whatever you wish to name it. It's up to you).

If you're using Visual Studio, or Visual Studio Code (with the NuGet Package Manager extension) you just need to execute the following command in the NuGet Package Manager Console.

3. Table Models

After you have successfully installed MagnaDB to your project, the next step is to create some models.
We'll start with a Table Model.

A TableModel is a class that resembles a Table definition in a database, and objects from this class are mapped to a row from this table in the database through their properties.

Let's create a Cat model (you can do this by creating a new Class file in your project; name it Cat); it will have the following properties: Id, Name, Race, DateOfBirth.

It would look something like this:

using System;
using MagnaDB.SQLite;

namespace TestMagnaDB
{
    public class Cat
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public string Breed { get; set; }
        public DateTime DateOfBirth { get; set; }
    }
}

using System;
using MagnaDB.SQLite;

namespace TestMagnaDB
{
    public class Cat : TableModel<Cat>
    {
        protected override string ConnectionString => "Data Source=testmagna.db;New=True;Version=3;";
        protected override string TableName => "Cats";
        protected override MagnaKey Key => this.MakeKey(cat => cat.Id);

        [Identity]
        public long Id { get; set; }
        public string Name { get; set; }
        public string Breed { get; set; }
        public DateTime DateOfBirth { get; set; }
    }
}

• TableModel<T>: this class must be inherited when creating a table model and contains all methods and functions concerning them. The implementing class must be specified as the type parameter when defining the inheritance (as seen in the example above).
• ConnectionString: this propery must return a string containing the properties connection string (formats may vary from one DB Engine to another, please see the DB Engines) for more information.
• TableName: this propery must return a string containing the name of the database table which this class will be mapped to.
• Key: this propery must return a MagnaKey object containg a Key/Value combination of the properties that compose the primary key for the class/table (the use of the extension method MakeKey() is preferred, and should provide an easier way to specify all columns composing the primary key).
• IdentityAttribute [Identity]: this attribute indicates that the property it is decorating will be used as an Identity or Auto Increment column. Learn more

using System;

namespace TestMagnaDB
{
    class Program
    {
        static void Main(string[] args)
        {
            // First things, first, let's create our Cat Table
            // If a table already exists in the database, we'll
            // have an exception thrown, so we'll use try/catch
            try
            {
                Cat.CreateTable();
            }
            catch
            {
                // The table already exists.
            }
            
            // Next, we'll create a new Cat object
            Cat milo = new Cat()
            {
                Name = "Milo",
                Breed = "Persian",
                DateOfBirth = new DateTime(2019, 11, 7)
            };

            // Let's insert Milo to the DB
            milo.Insert();

            // After a TableModel object is successfully inserted to the database
            // it will have its Identity decorated property (if any) automatically updated
            Console.WriteLine("{0}, the {1} cat's Id is {2}", milo.Name, milo.Breed, milo.Id);

            // Turns out Milo's not a Persian, but an American Shorthair
            // We'll fix this and update Milo's breed in the database
            milo.Breed = "American Shorthair";
            milo.Update();

            // If we want to retrieve Milo's information from the database
            // we can do so by using the Get() static method, and providing Milo's Id
            // as an argument. This works for classes with Identity decorated properties.
            Cat miloFromTheDB = Cat.Get(milo.Id);

            Console.WriteLine("This kitten's name is {0}", miloFromTheDB.Name);
            Console.WriteLine("It's a(n) {0}", miloFromTheDB.Breed);
            Console.WriteLine("It was born on {0:MMM dd yyyy}", miloFromTheDB.DateOfBirth);
            Console.WriteLine("{0}Press any key to continue...", Environment.NewLine);
            Console.ReadKey();
        }
    }
}

4. View Models

MagnaDB View Models are models that are only used to retrieve information from the database, meaning they are SELECT Only.

Even though Views are created from existing tables (in engines like SQL Server, or MySql), which means they derive from Tables; in MagnaDB, TableModel is a ViewModel's subclass (which is useful to know), and therefore all operations done with ViewModel can be done with TableModel.

ViewModels DO NOT have to point to a VIEW in order to work.
They can perfectly point at a Table, and query one or more of its columns with no issues.

Let's create a new Class file in our project which we previously created (sections 2., 3.) which we'll name CatView.cs

using System;
using MagnaDB.SQLite;

namespace TestMagnaDB
{
    public class CatView : ViewModel<CatView>
    {
        protected override string ConnectionString => "Data Source=testmagna.db;New=True;Version=3;";
        protected override string TableName => "Cats";
        protected override MagnaKey Key => this.MakeKey(cat => cat.Id);

        [Identity]
        public long Id { get; set; }
        public string Name { get; set; }
        public string Breed { get; set; }
    }
}

            ...
            // We'll retrieve all cats from the database using the ToList() static
            // method in our CatView class; this can also be done by using table
            // models (like the Cat class we previously created), but we're
            // using the CatView class just for demonstration.
            var aLotOfMilos = CatView.ToList();

            foreach(var item in aLotOfMilos)
            {
                Console.WriteLine("Id: {0}, Name: {1}, Breed: {2}", item.Id, item.Name, item.Breed);
            }

            Console.WriteLine("{0}Press any key to continue...", Environment.NewLine);
            Console.ReadKey();
        }
    }
}

5. Methods

**DISCLAIMER: The documentation provided for this section contains information for the different MagnaDB classes, properties, methods, events, that are considered to be relevant for its use, and not the whole project.**

1. ViewModel<T> class

Methods

Properties

Events

2. TableModel<T> class

Methods

Events

3. QueryMethods static class

Methods

4. SqlGenerator static class

Methods

6. Attributes

The following attributes let you manipulate MagnaDB's model's behavior and implement lots of cool functionalities.

All attributes target class properties

We'll be looking at each one of them below.

1. ColumnNameAttribute

            ...
            [ColumnName("pedigree")]
            public string Breed { get; set; }

2. DataDisplayableAttribute

            ...
            [DataDisplayable]
            public string Name { get; set; }

3. DateTimeTypeAttribute

            ...
            [DateTimeType(DateTimeSpecification.Date)]
            public DateTime DateOfBirth { get; set; }

4. DDLIgnoreAttribute

            ...
            [DDLIgnore]
            public decimal StockGrowthRatio { get; set; }

5. DMLIgnoreAttribute

            ...
            [DMLIgnore]
            public HttpPostedFileBase UploadedImage { get; set; }

6. DuplicationColumnAttribute

            ...
            public const int PersonalInfoIndex = 1;
            public const int SocialSecurityIndex = 2;
            public const int PassportIndex = 3;

            [DuplicationColumn(PersonalInfoIndex)]
            public string FirstName { get; set; }

            [DuplicationColumn(PersonalInfoIndex)]
            public string LastName { get; set; }

            [DuplicationColumn(PersonalInfoIndex)]
            public DateTime DateOfBirth { get; set; }
            
            [DuplicationColumn(SocialSecurityIndex)]
            public string SocialSecurityNumber { get; set; }
            
            [DuplicationColumn(PassportIndex)]
            public string PassportNumber { get; set; }

            // When invoking the IsDuplicated() method, a parameter for which duplication index
            // will be evaluated can be provided; if -1 (default if none) is provided as
            // an argument, all indexed-groups will be evaluated.

            // FirstName, LastName, and DateOfBirth will be evaluated as a group, meaning that
            // duplicated entities have the same full names and were born on the same day.
            
            // Social Security Number will be evaluated individually.
            // Passport Number will also be evaluated individually.

7. ForeignRelationAttribute

• The '<' and '>' signs ARE NOT PLACEHOLDERS and must enclose the implementing class' property.
• {0} is a shorthand for the implementing class' table name (the tablename can be explicitly specified if so desired).
• {1} is a shorthand for the foreign class' table name (the tablename can be explicitly specified if so desired).
• The implementing class property and foreign class property must be of the same type (just as a foreign key relationship in databases would be defined). However, these properties don't need to be a/part of a primary key in order to work, nor does the existence of primary/foreign keys in the database affect the [ForeignRelation] attribute's behavior.

      ...
      public class Cat : TableModel<Cat>
      {
          protected override string ConnectionString => "Data Source=testmagna.db;New=True;Version=3;";
          protected override string TableName => "Cats";
          protected override MagnaKey Key => this.MakeKey(cat => cat.Id);
  
          [Identity]
          public long Id { get; set; }
          public string Name { get; set; }
          public string Breed { get; set; }
          public DateTime DateOfBirth { get; set; }
      
          [ForeignRelation("<{0}.Id> = {1}.PatientId")]
          public List<Checkup> Checkups { get; set; }
      }

      public class Checkup : TableModel<Checkup>
      {
          protected override string ConnectionString => "Data Source=testmagna.db;New=True;Version=3;";
          protected override string TableName => "Checkups";
          protected override MagnaKey Key => this.MakeKey(cat => cat.Id);
  
          [Identity]
          public long Id { get; set; }
          public DateTime CheckupDate { get; set; }
          public string Prescription { get; set; }

          public long PatientId { get; set; }

          [ForeignRelation("<{0}.PatientId> = {1}.Id")]
          public Cat Patient { get; set; }
      }

8. IdentityAttribute

            ...
            [Identity]
            public long Id { get; set; }

9. InsertIgnoreAttribute

            ...
            [InsertIgnore]
            public DateTime TimeStamp { get; set; }

10. SelectIgnoreAttribute

            ...
            [SelectIgnore]
            public string SocialSecurityNumber { get; set; }

11. UpdateIgnoreAttribute

            ...
            [UpdateIgnore]
            public string SongId { get; set; }

7. Events

MagnaDB events provide functionalities similar to database triggers.

Whether you're trying a only-code approach for your application, or your database services are limited, and don't provide you with database triggers; events are a perfect workaround. And, who knows? They might even provide some extra functionalities.

The events in MagnaDB are the following:

For ViewModel<T>

• GetFailed
• GetSucceeded
• SelectFailed
• SelectSucceeded

• GetFailed (inherited)
• GetSucceeded (inherited)
• SelectFailed (inherited)
• SelectSucceeded (inherited)
• BeforeInsert
• InsertFailed
• InsertSucceeded
• BeforeUpdate
• UpdateFailed
• UpdateSucceeded
• BeforeDelete
• DeleteFailed
• DeleteSucceeded

using System;
using MagnaDB.SQLite;

namespace TestMagnaDB
{
    public class User : TableModel<User>
    {
        protected override string ConnectionString => "Data Source=testmagna.db;New=True;Version=3;";
        protected override string TableName => "Users";
        protected override MagnaKey Key => this.MakeKey(user => user.Id);

        [Identity]
        public long Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Username { get; set; }
        public string PasswordHash { get; set; }
        public bool EmailIsConfirmed { get; set; }

        public User()
        {
            BeforeInsert += User_BeforeInsert;
            InsertSucceeded += User_InsertSucceeded;
        }

        // The sender parameter receives the invoking object for Insert(),
        // Update(), and Delete() methods. For methods retrieving data the
        // object(s) resulting from the queries are received as the sender
        // parameter instead.

        // The MagnaEventArgs contains properties relating to the database
        // connection including the transaction (if any), the connection
        // (if any), and/or the connection string (if any), the triggering 
        // method's command was executed against.
        public void User_BeforeInsert(object sender, MagnaEventArgs e)
        {
            PasswordHash = AnExistingClassYouMayHave.HashFunctionThatIsSomewhereElse(PasswordHash);
        }

        // Notice how this event handler is implemented asynchronously
        // In some contexts this can mean performance optimization for
        // your application, so don't be afraid to try it.
        public async void User_InsertSucceeded(object sender, MagnaEventArgs e)
        {
            await AnExistingClassYouMightAlsoHave.SendEmailConfirmationAsync(Id);
        }
    }
}

8. Exceptions

These are the existing exception types in MagnaDB.

9. Database Engines

MagnaDB is currently available for the following DB Engines:

• SQL Server
• MySQL
• SQLite

Database Connections in SqlServer

Database Connections in MySQL

Database Connections in SQLite

Integer support in SQLite