Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / XML

Simple CRUD with the .NET Micro ORM "Symbiotic"

Rate me:
Please Sign up or sign in to vote.
5.00/5 (9 votes)
5 Oct 2021CPOL4 min read 27K   253   13   21
The article demonstrates database create, read, update, delete operations using the .NET Symbiotic ORM.
changed tags

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.

SQL
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.

C#
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:

C#
[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:

C#
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.

C#
// 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:

C#
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.

C#
// ---------------------------------------------------------------------------
// 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.

C#
// ------------------------------------------------------------------------------
// 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:

C#
// ------------------------------------------------------------------------------
// 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.

C#
// ------------------------------------------------------------------------------
// 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:

C#
// ------------------------------------------------------------------------------
// 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.

C#
// -----------------------------------------------------------------------------
// 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.

C#
// -----------------------------------------------------------------------------
// 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.

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

History

  • 21st January, 2019: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
Software Engineer, .Net

Comments and Discussions

 
QuestionCompound Key? Pin
sestoenner1-Nov-21 4:23
sestoenner1-Nov-21 4:23 
AnswerRe: Compound Key? Pin
Eric P Schneider1-Nov-21 18:23
Eric P Schneider1-Nov-21 18:23 
QuestionWhat's wrong with SQLMetal? Pin
Member 149344648-Oct-21 13:34
Member 149344648-Oct-21 13:34 
AnswerRe: What's wrong with SQLMetal? Pin
Eric P Schneider9-Oct-21 17:02
Eric P Schneider9-Oct-21 17:02 
QuestionDB from code Pin
Alex (RSA)7-Oct-21 4:06
professionalAlex (RSA)7-Oct-21 4:06 
AnswerRe: DB from code Pin
Eric P Schneider7-Oct-21 15:19
Eric P Schneider7-Oct-21 15:19 
QuestionGood work! Pin
Martin Garmendia7-Oct-21 0:44
Martin Garmendia7-Oct-21 0:44 
AnswerRe: Good work! Pin
Eric P Schneider7-Oct-21 15:33
Eric P Schneider7-Oct-21 15:33 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA6-Oct-21 1:15
professionalȘtefan-Mihai MOGA6-Oct-21 1:15 
This is a great inspiring article. I am pretty much pleased with your good work. You put really very helpful information. Keep it up once again.
GeneralRe: My vote of 5 Pin
Eric P Schneider7-Oct-21 15:34
Eric P Schneider7-Oct-21 15:34 
QuestionMessage Closed Pin
5-Oct-21 23:09
Member 153838555-Oct-21 23:09 
PraiseThis looks terrific and what I need right now Pin
Slow Eddie2-Jun-20 11:49
professionalSlow Eddie2-Jun-20 11:49 
GeneralRe: This looks terrific and what I need right now Pin
Eric P Schneider2-Jun-20 13:30
Eric P Schneider2-Jun-20 13:30 
GeneralMessage Closed Pin
5-Oct-21 23:09
Member 153838555-Oct-21 23:09 
Questionvb.net Pin
Eric P Schneider27-Feb-19 16:57
Eric P Schneider27-Feb-19 16:57 
QuestionProblem while doing Step 3. Pin
_jaws_20-Jan-19 16:33
_jaws_20-Jan-19 16:33 
AnswerRe: Problem while doing Step 3. Pin
Eric P Schneider20-Jan-19 16:58
Eric P Schneider20-Jan-19 16:58 
GeneralRe: Problem while doing Step 3. Pin
_jaws_20-Jan-19 18:02
_jaws_20-Jan-19 18:02 
GeneralRe: Problem while doing Step 3. Pin
Eric P Schneider20-Jan-19 18:26
Eric P Schneider20-Jan-19 18:26 
GeneralRe: Problem while doing Step 3. Pin
Eric P Schneider20-Jan-19 18:29
Eric P Schneider20-Jan-19 18:29 
QuestionWhere's the Source Code Pin
Dewey19-Jan-19 3:19
Dewey19-Jan-19 3:19 
AnswerRe: Where's the Source Code Pin
Eric P Schneider19-Jan-19 5:16
Eric P Schneider19-Jan-19 5:16 
AnswerRe: Where's the Source Code Pin
Eric P Schneider19-Jan-19 5:56
Eric P Schneider19-Jan-19 5:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.