Simple CRUD with the .NET Micro ORM "Symbiotic"

• Download source code - 1.8 MB

Introduction

The article will teach you how to read and write your objects data to a database using the .NET Symbiotic Micro ORM. Symbiotic is a free .NET ORM that supports the following database vendors: SQL Server, SQL Azure, My SQL, Sqlite, Oracle, PostgreSql, Firebird, DB2/LUW.

This article will concentrate on the SQL Server database. This article will assume you have basic 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 SimpleEntities Table

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

CREATE TABLE [dbo].[SimpleEntities](
    [EntityId] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_SimpleEntities] PRIMARY KEY CLUSTERED
(
    [EntityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) 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: 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 4: Create SimpleEntity Class

Add a new class named "SimpleEntity" to the project.

This class will be used to represent the table you created in step 1.

Replace or change "SimpleEntity" class code to match below:

    [Serializable, DatabaseTable("SimpleEntities"), 
    DebuggerDisplay("SimpleEntity: EntityId= {EntityId},  Description= {Description}")]
    public class SimpleEntity
    {
        [DatabaseColumn("EntityId", IsPrimaryKey = true, IsIdentityColumn = true)]
        public int EntityId { get; set; }

        [DatabaseColumn("Description")]
        public string Description { get; set; }
    }

The DatabaseTable attribute indicates what database table this maps to. There is also a DatabaseWriteTable and DatabaseReadTable to allow more control.

The DatabaseColumn attribute indicates the database column/field name in the SQL results to map to the property of the object. If a DatabaseColumn is present, the ORM expects to find a result, if not, an error will occur.

Step 5: 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 6: 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.Data;

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

namespace Getting_Started_With_Symbiotic_P1_CRUD_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 7: Create a Record

Add the following below lines to the end of the "Main" method.

The first two lines create a new instance of the "SimpleEntity" class and populate the description.

The third line creates an instance of an IObjectWriter called "writer", which will be used to write items to the database.

The last line writes the data contained in the "SimpleEntity" to the database.

// ---------------------------------------------------------------------------
// create a record
SimpleEntity newItem = new SimpleEntity();
newItem.Description = "Description " + DateTime.Now.ToString();
    
// create the writer object, this class is used for all writes
IObjectWriter writer = _DBTypesFactory.CreateObjectWriter();
            
// call create on the writer passing in the instance to save to the database
writer.Create(newItem); // note: the primary key property "EntityId" will be populated after the write

Step 8: Read a Record

Add the following below lines to the end of the "Main" method.

The first line creates an instance of an IObjectLoader called "loader", which will be used to read items from the database.

The last line uses the loader to retrieve the record from the database as a populated "SimpleEntity" instance stored inside the loadedItem variable.

// ------------------------------------------------------------------------------
// Read a single record

// create the loader object, this class is used for all reads
IObjectLoader loader = _DBTypesFactory.CreateObjectLoader();

SimpleEntity loadedItem = loader.ObtainItem<SimpleEntity>(newItem.EntityId);

Step 9: Update a Record

Add the following below lines to the end of the "Main" method.

The first two lines we modify the Description of the SimpleEntity instance called "newItem".

The last line writes the new data from the "newItem" instance to the database:

// ------------------------------------------------------------------------------
// Update a record

string newDesc = "Updated " + DateTime.Now.ToString();
newItem.Description = newDesc;
writer.Update(newItem);

Step 10: Insert or Update a Record

Add the following below lines to the end of the "Main" method.

The first two lines we modify the Description of the SimpleEntity instance called "newItem".

The last line will either insert the "newItem" instance record if the record doesn't exist, otherwise it will update it.

// ------------------------------------------------------------------------------
// InsertUpdate a record

// InsertUpdate will create or update, the ORM checks if it exists, 
// if so then updates the record otherwise it creates it.
string newDesc2 = "Updated " + DateTime.Now.ToString();
newItem.Description = newDesc2;
writer.InsertUpdate(newItem);

Step 11: Delete a Record

Add the following below lines to the end of the "Main" method.

This line deletes "newItem" instance from the database:

// ------------------------------------------------------------------------------
// Delete a record
writer.Delete(newItem);

Step 12: Query Multiple Records

The first line is a standard SQL to return all the records in the "SimpleEntities" table.

Line two creates an ISqlQuery instance needed to run the query.

Line three runs the query and returns a collection of SimpleEntity items.

Keep in mind if you have no records, the collection will be empty.

// -----------------------------------------------------------------------------
// Query multiple records
string sql = "Select * from simpleEntities";
ISqlQuery query = _DBTypesFactory.CreateSqlQuery(sql, "My simple sql");

IList<simpleentity> items = loader.ObtainItems<simpleentity>(query);

Step 13: Query with Parameters

The first line creates a parameterized SQL statement with a parameter called "max".

Line two creates an ISqlQuery instance needed to run the query.

Line three created a parameter and loaded the parameter into the query with the value of "3".

Line four runs the query and returns a collection of SimpleEntity items.

Keep in mind if no records match the query where clause then the collection will be empty.

// -----------------------------------------------------------------------------
// Query with parameters
string sql2 = "Select * from simpleEntities where Entityid > @max";
ISqlQuery query2 = _DBTypesFactory.CreateSqlQuery(sql2, "My simple sql");
query2.CreateAndAddParameter(_DBTypesFactory, DbType.Int32, "max", 3);
            
IList<SimpleEntity> items2 = loader.ObtainItems<SimpleEntity>(query2);

Points of Interest

This article barely touches the surface of the capabilities of the "Symbiotic" ORM. For more advanced features details, 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

• 21^st January, 2019: Initial version