Parallel Queries with the Symbiotic Micro ORM

• Download source files - 1.6 MB

Introduction

The article will explain how to run queries in parallel and return the results using the Symbiotic ORM. We will also explain how to troubleshoot cases where a query/s has an error, and how to isolate the problem query.

Symbiotic is a free .NET ORM that supports the following database vendors: SQL Server, SQL Azure, MySQL, Sqlite, Oracle, PostgreSql, Firebird, DB2/LUW.

This article will concentrate on the SQL Server database.

This article will assume you have a strong knowledge of C# and SQL Server.

Background

You will need a SQL Server database, it can be a local database file, server database or an Azure SQL database.

Please make sure you build your project as x64. See the menu: "Build \ Configuration Manager".

Step 1: Create TestItem1, TestItem2 Tables

Run the following SQL script to create the tables we will use for this article.

CREATE TABLE [dbo].[TestItem1](
	[TestId] [int] IDENTITY(1,1) NOT NULL,
	[StringData] [nvarchar](50) NOT NULL,
	[DateData] [datetime] NOT NULL
) ON [PRIMARY];


CREATE TABLE [dbo].[TestItem2](
	[TestId] [int] IDENTITY(1,1) NOT NULL,
	[StringData] [nvarchar](50) NOT NULL,
	[DateData] [datetime] NOT NULL
) ON [PRIMARY];

Step 2: Create a Project & Add Symbiotic ORM NuGet Package

Create a new C# console project for .NET 4.6.1 or higher in Visual Studio.

Then add the Nuget package "Symbiotic_Micro_ORM_Net_Standard_x64". You can use the main menu: "Project \ Manage Nuget Packages..."

You may need to refresh the project in the "Solution Explorer" to update the references.

Step 3: Create TestItem1 & TestItem2 Classes

These classes will be used to represent the tables you created in Step 1.

[DatabaseTable("TestItem1"), DebuggerDisplay("TestItem1: {TestId} {DateData} {StringData}")]

public class TestItem1
{
    [DatabaseColumnAttribute("TestId", IsPrimaryKey = true, IsIdentityColumn = true)]
    public int TestId { get; set; }

    [DatabaseColumnAttribute("StringData")]
    public string StringData { get; set; }

    [DatabaseColumnAttribute("DateData")]
    public DateTime DateData { get; set; }
}

[DatabaseTable("TestItem2"), DebuggerDisplay("TestItem2: {TestId} {DateData} {StringData}")]
public class TestItem2
{
    [DatabaseColumnAttribute("TestId", IsPrimaryKey = true, IsIdentityColumn = true)]
    public int TestId { get; set; }

    [DatabaseColumnAttribute("StringData")]
    public string StringData { get; set; }

    [DatabaseColumnAttribute("DateData")]
    public DateTime DateData { get; set; }
}

Step 4: Add Usings for Symbiotic ORM

Add the following usings lines to the top of the "Program" class.

using FrozenElephant.Symbiotic;
using FrozenElephant.Symbiotic.DataProviderSqlServer; // Using the Sql Server data provider

Step 5: Initialize the Factory Class

Add the following lines of code inside the beginning of "Main" method.

These lines initialize factory class and set the database connection string.

You will need to modify the connection string to match your database, server and user / password.

 // Initialize the factory and set the connection string
_DBTypesFactory = new DatabaseTypesFactorySqlServer(); // using sql server provider
_DBTypesFactory.ConnectionString = "Data Source=yourServer;
Initial Catalog=yourDatabase;User ID=ZZZZZZZ;Password=XXXXXX;Connect Timeout=35;
Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;
MultiSubnetFailover=False;MultipleActiveResultSets=true;Enlist=false";

Your "Program" class should now look like the code below:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;
using FrozenElephant.Symbiotic;
using FrozenElephant.Symbiotic.DataProviderSqlServer; // Using the SQL Server data provider

namespace Advanced_Parallel_Queries_With_Symbiotic_CS
{
    // Make sure the build is set to x64
    class Program
    {
        // the factory is where all Symbiotic ORM objects are created, 
        // this allows the developer to override creation as needed.
        private static IDatabaseTypesFactory _DBTypesFactory;

        static void Main(string[] args)
        {
            // Initialize the factory and set the connection string
            _DBTypesFactory = new DatabaseTypesFactorySqlServer(); // using SQL Server provider
            _DBTypesFactory.ConnectionString = "Data Source=yourServer;
            Initial Catalog=yourDatabase;User ID=ZZZZZZZ;Password=XXXXXX;
            Connect Timeout=35;Encrypt=False;TrustServerCertificate=True;
            ApplicationIntent=ReadWrite;MultiSubnetFailover=False;
            MultipleActiveResultSets=true;Enlist=false";
        }
    }
}

Step 6: Add Methods to Create Test Records

Add the following methods to the "Program" class.

The following methods will be used to create test records that we will later query.

public static void CreateRandomItems()
{
    IList<testitem1> items = new List<testitem1>();

    for (int t = 0; t <= 1000; t++)
    {
        var emp = new TestItem1();
        emp.DateData = DateTime.Now;
        emp.StringData = Guid.NewGuid().ToString();

        items.Add(emp);
    }

    IObjectWriter writer = _DBTypesFactory.CreateObjectWriter();
    writer.InsertUpdate(items);
}

public static void CreateRandomItems2()
{
    IList<testitem2> items = new List<testitem2>();

    for (int t = 0; t <= 1000; t++)
    {
        var emp = new TestItem2();
        emp.DateData = DateTime.Now;
        emp.StringData = Guid.NewGuid().ToString();

        items.Add(emp);
    }

    IObjectWriter writer = _DBTypesFactory.CreateObjectWriter();
    writer.InsertUpdate(items);
}

Step 7: Add the Following Lines to the "Main" Method

Add the following method calls to the end of the "Main" method.

These calls will create the records used to query later.

// Create test records
CreateRandomItems();

CreateRandomItems2();   

Step 8: Create the Parallel Load Method

This method demonstrates running multiple queries in parallel and returning a collection containing collections of TestItem1, and TestItem2 items.

We build a collection of ISqlQuery items and call "loader.ParallelLoadItems" to run the queries in parallel.

        public static void ParallelLoadItems()
        {
            //build a collection of queries to run
            IList<isqlquery> queries = new List<isqlquery>();

            ISqlQuery s1 = _DBTypesFactory.CreateSqlQuery
            ("Select * from TestItem1", "ParallelLoads.ParallelLoadItems1", typeof(TestItem1));
            s1.AddRowLimit(_DBTypesFactory.DatabaseType, 2000);
            queries.Add(s1);

            ISqlQuery e1 = _DBTypesFactory.CreateSqlQuery("Select * 
            from TestItem1", "ParallelLoads.ParallelLoadItems2", typeof(TestItem1));
            e1.AddRowLimit(_DBTypesFactory.DatabaseType, 2000);
            queries.Add(e1);

            ISqlQuery a1 = _DBTypesFactory.CreateSqlQuery("Select * 
            from TestItem2", "ParallelLoads.ParallelLoadItems3", typeof(TestItem2));
            a1.AddRowLimit(_DBTypesFactory.DatabaseType, 2000);
            queries.Add(a1);

            ISqlQuery p1 = _DBTypesFactory.CreateSqlQuery("Select * 
            from TestItem1", "ParallelLoads.ParallelLoadItems4", typeof(TestItem1));
            p1.AddRowLimit(_DBTypesFactory.DatabaseType, 2000);
            queries.Add(p1);

            IObjectLoader loader = _DBTypesFactory.CreateObjectLoader();
            IList<ilist> data = loader.ParallelLoadItems(queries);

            // demonstrates that the list is in the same order 
            // and how to cast the collection for use
            IList<testitem2> items = (IList<testitem2>)data[2];
            items[2].StringData = "Found it!";
        }

Step 9: Planning for Problems

This method will demonstrate an error in one of the SQL queries and how to isolate the problem query and help you troubleshoot the problem. Query two has an obvious error intended to help demonstrate isolating problems when running multiple queries.

Please note that the SQL label passed in for the bad query is "my query 2", the ORM will include this in any errors when running the query. If you add a break point to the line "int a = 1;" it will stop there.

public static void ParallelLoadItemsError()
{
    try
    {
        IList<isqlquery> queries = new List<isqlquery>();

        ISqlQuery s1 = _DBTypesFactory.CreateSqlQuery("Select *
        from TestItem1", "my query 1", typeof(TestItem1));
        s1.AddRowLimit(_DBTypesFactory.DatabaseType, 2000);
        queries.Add(s1);

        ISqlQuery e1 = _DBTypesFactory.CreateSqlQuery("Select  FakeCol1,
        FakeCol2 from TestItem1", "my query 2", typeof(TestItem1));
        queries.Add(e1);

        ISqlQuery a1 = _DBTypesFactory.CreateSqlQuery("Select *
        from TestItem2", "my query 3", typeof(TestItem2));
        a1.AddRowLimit(_DBTypesFactory.DatabaseType, 2000);
        queries.Add(a1);

        ISqlQuery p1 = _DBTypesFactory.CreateSqlQuery("Select *
        from TestItem1", "my query 4", typeof(TestItem1));
        p1.AddRowLimit(_DBTypesFactory.DatabaseType, 2000);
        queries.Add(p1);

        IObjectLoader loader = _DBTypesFactory.CreateObjectLoader();
        IList<ilist> data = loader.ParallelLoadItems(queries);
    }
    catch (Exception ex)
    {
        if (ex.InnerException.InnerException.Message.Contains("my query 2") == true)
        {
              int a = 1;
        }
    }
}

Step 10: Add the Calls to Parallel Methods

Add the following method calls to the end of the "Main" method.

// perform parallel load
ParallelLoadItems();

// perform parallel load with a error
ParallelLoadItemsError();

Points of Interest

This article barely touches the surface of the capabilities of the "Symbiotic" ORM. For details of more advanced features and examples, download the nuget package and look inside the package folder for example projects.

• https://www.nuget.org/packages/Symbiotic_Micro_ORM_Net_Standard_x64/3.0.0

There is also a companion app that will create POCO classes for an existing database:

• https://www.microsoft.com/store/productId/9NN20Q6WFKGS

History

• 27^th June, 2019: Initial version