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 using
s lines to the top of the "Program
" class.
using FrozenElephant.Symbiotic;
using FrozenElephant.Symbiotic.DataProviderSqlServer;
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 using
s lines to the top of the "Program
" class:
using FrozenElephant.Symbiotic;
using FrozenElephant.Symbiotic.DataProviderSqlServer;
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.
_DBTypesFactory = new DatabaseTypesFactorySqlServer();
_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;
namespace Getting_Started_With_Symbiotic_P1_CRUD_CS
{
class Program
{
private static IDatabaseTypesFactory _DBTypesFactory;
static void Main(string[] args)
{
_DBTypesFactory = new DatabaseTypesFactorySqlServer();
_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.
SimpleEntity newItem = new SimpleEntity();
newItem.Description = "Description " + DateTime.Now.ToString();
IObjectWriter writer = _DBTypesFactory.CreateObjectWriter();
writer.Create(newItem);
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.
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:
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.
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:
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.
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.
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