Skip to content
wantalgh edited this page Jul 16, 2024 · 11 revisions

Light-ORM, A Lightweight and Easy-to-Use ORM Framework


Overview

Light-ORM is an ORM tool based on .NET Standard 2.0, characterized by its convenice and ease of use, allowing develpers to access SQL databases with minimal coding effort, thereby significantly improving work efficiency and reducing software development costs. To make Light-ORM simple and easy to use, the tool adopts a lightweight design with the following advantages:

  • The tool's API is concise and easy to call. A single line of code can perform a data access operation, such as querying rows that meet certain conditions from the database and mapping the retrieved data into an list of .NET objects.
  • The tool has a low learning curve. Due to its lightweight nature, using this tool dose not require a heavy learning cost. You only need to understand the use of the LightDataClient.DataClient class to complete operations such as adding, deleting, querying, and updating the database.
  • The tools is hightly applicable. It only requires ordinary POCO to map data from the database, supports connecting to various SQL database products using different SQL dialects, and does not introduce any thoer configuraiton files or annotations into thd project, thus not effecting the project structure.
  • The tool has simple dependencies. It only depends on .NET Standard 2.0, so it can be easily introduced into other projects without bringing a heavier reference burden or conflicting with other libraries in the project.
  • The tool is easy to understand and extend. Because of its lightweight nature, the tool itself consists of only a few simple classes, without complex process logic and using an extensible design, users can customize their own SQL generation logic and data mapping rules as needed.

Introduction

Reference

Choose one of the following methods:

Introducing this tool into the project will not refer to a specific data source tool library. You need to refer to a specific database access tool for your project according to your needs, such as Microsoft.Data.SqlClient for accessing SqlServer and System.Data.SQLite.Core for accessing SQLite.

Initialization

Light-ORM uses the LightDataClient.DataClient class to complete all data operations. Initializing an instance of this class is very simple.

Accessing SqlServer, assuming the database connection string is in the variable sqlConnStr, then only one line of code is needed to complete the initialization:

DataClient client = new DataClient(() => new SqlConnection(sqlConnStr));

Accessing SQLite requires specifying the second param of the DataClient constructor to specify the SQLite SQL dialect generator used by the tool.

DataClient client = new DataClient(() => new SQLiteConnection(sqliteConnStr), new Sqlite3SqlBuilder());

The library currently has two build-in SQL dialect generators, Tsql2005Builder and Sqlite3SqlBuilder, which are used to support SqlServer and SQLite respectively. If you need, you can implement your own ISqlDialectBuilder. By implementing ISqlDialectBuilder, you can customize SQL dialect generation rules and data column mapping rules to support more databases and more complex data modes.

The member methods of the DataClient class are thread-safe, and it is recommended to initialize a singleton instance of DataClient for one data source when using it.

Access

Assuming there is a data table User with three columns: Id, Name, and BirthDate, and we have a corresponding data class User, which has three fields corresponding to the column names of the data table. The following examples mainly use this table and data class as a reference.

CREATE TABLE [User] (
    [Id]          INT              NOT NULL,
    [Name]        NVARCHAR (50)    NULL,
    [BirthDate]   DATETIME2 (7)    NOT NULL
);
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime BirthDate { get; set; }
}

Querying data mainly uses the DataClient.ExecuteModels method, which has params such as sql, parameter, skip, and take. Using these params can complete a variety of data query fuctions.

By calling the ExecuteModels methods, pass the query SQL statement and the type of the query result data class, you can execute SQL and map the query results to a list of objects with just one line of code:

IEnumerable<User> users = client.ExecuteModels<User>("SELECT * FROM User");

ExecuteModels will execute the SELECT statement passed to the method's sql param, and then generate an instance of data object for each row of data retrieved from the database, mapping the data to object properties whth the same column names by default.

The ExecuteModels method supports SQL parameterized queries. You can easily pass an anonymous object to the parameter param of the method. It also only takes one line of code:

IEnumerable<User> users = client.ExecuteModels<User>("SELECT * FROM User WHERE Id = @QueryId",  new { QueryId = 1});

ExeuteModels will convert the properties of anonymous objects into SQL query parameters. The property names are consistent with the parameter names,The converted query parameters are used with the SELECT statement。 You can also pass multiple query parameters at the same time:

IEnumerable<User> users = client.ExecuteModels<User>("SELECT * FROM User WHERE Id = @QueryId OR Name LIKE @SearchName",  new { QueryId = 1, SearchName = "%name%" });

If not provide the method's sql param content, the ExecuteModels method will automatically use the class name of the result data as the tables name and the property name as the column name to generate a SELECT statement and execute it, for example:

IEnumerable<User> users = client.ExecuteModels<User>();

The code above is equivalent to executing:

IEnumerable<User> users = client.ExecuteModels<User>("SELECT Id, Name, BirthDate FROM User");

If the sql param is not provided but an object for the parameter param is provided, the ExecuteModels method will add a WHERE condition when generating the SELECT statement, conditions have the same name as parameter object's properties, and multiple conditions are connected with AND by defalt, for example::

IEnumerable<User> users = client.ExecuteModels<User>(parameter: new { Id = 1, Name = "name" });

The code above is equivalent to executing:

IEnumerable<User> users = client.ExecuteModels<User>("SELECT Id, Name, BirthDate FROM User WHERE Id = @Id AND Name = @Name", new { Id = 1, Name = "name" });

When automatically generating a SELECT statement, if the object's class name is inconsistent with the table name, you can use the tableName param to specify the table name used when generating the SELECT statement, for example:

IEnumerable<User> users = client.ExecuteModels<User>(tableName: "User2");

The ExecuteModels method generates as SELECT statement using the specify table name instead of the type name. The code above is equivalent to executing:

IEnumerable<User> users = client.ExecuteModels<User>("SELECT Id, Name, BirthDate FROM User2");

The ExecuteMethods method supports generating a SELECT statement with pagination mechanism. When no sql content is provided and the method params skip and take are not null, for example:

IEnumerable<User> users = client.ExecuteModels<User>(skip: 0, take: 10);

The tool's SQL dialect generator will generate a SQL statement with range query logic, such as Tsql2005Builder generating a SELECT statement using the ROW_NUMBER() OVER mechanism, and Sqlite2SqlBuilder generating a SELECT statement with the LimitOffset clause.

Multiple automatic generation mechanisms for SELECT statements can be used in combination, for example:

IEnumerable<User> users = client.ExecuteModels<User>(tableName: "User2", parameter: new { Name = "name" }, skip: 0, take: 10);

The tool's mapping rules for query results are that the column names of the returned data are consistent with the property names of the data object. Therefore, if the data table's columns are different from the data object, you can use SELECT AS to adjust the column names of the returnd data, for example, there is a users table with three fields: id, user_name, and birth_date. If you want to map the query using the User object, you can do this::

var sql = """
          SELECT 
          id            AS Id,
          user_name     AS Name,
          birth_date    AS BirthDate
          FROM users
          """;
IEnumerable<User> users = client.ExecuteModels<User>(sql);

If all table's field naming conventions have a consistent conversion relationship with the data object, you can customize an ISqlDialectBuilder to generate SELECT statements with AS uniformly to handle this relationsip.

The ExecuteModels method supports calling stored procedures by setting the commandType param to StoredProcedure. For example, if there is a stored procedure sp_queryuserbyname in the database that acceps a Name parameter and returns the query result, you can:

IEnumerable<User> users = client.ExecuteModels<User>("sp_queryuserbyname", parameter: new { Name = "name" }, commandType: CommandType.StoredProcedure);

If you want to return a DataSet instead of a list of data mapped to a result object, you can use the ExecuteDataSet method, which receives the SQL statement to be executed and returns a System.Data.DataSet object。There can be multiple DataTables in a DataSet object, so the SQL executed by this method can query multiple result sets at a time.

var sql = """
          SELECT * FROM Staff WHERE Name LIKE @SearchName 
          SELECT * FROM Staff2 WHERE Name LIKE @SearchName 
          SELECT * FROM Staff3 WHERE Name LIKE @SearchName 
          """;
DataSet users = client.ExecuteDataSet(sql, new { SearchName = "%name%" });

If you only need to query a single record, you can call the ExecuteModel method. ExecuteModel has the same query functionality as ExecuteModels, but only return the first result:

User user = client.ExecuteModel<User>();

This is similar to executing:

User user = client.ExecuteModels<User>().FirstOrDefault();

If the query returns a single value, you can use the ExecuteScalar method, which only maps a single value from the query result:

int total = client.ExecuteScalar<int>("SELECT COUNT(*) FROM User");

If the executed SQL does not return a result. you can call the ExecuteNone method, which only returns the number of rows affected by the executed SQL:

int affectedRows = client.ExecuteNone("DELETE FROM User");

In addition to querying. this tool supports using an automatic mapping mechanism to add or modify data. For example, you can call the InsertModel method to insert a record into the database:

var user = new User()
{
    Id = 1,
    Name = "name",
    BirthDate = DateTime.Now
};
int affectedRows = client.InsertModel(user);

The InsertModel method's model param receives an object, and the method generates an INSERT statement using the object's class name as the table name, and the property name as the column name and parameter. and then executes it. The above statement is equivalent to executing:

int affectedRows = client.ExecuteNone("INSERT User (Id, Name, BirthDate) VALUES (@Id, @Name, @BirthDate)", new {Id = 1, Name = "name", BirthDate = DateTime.Now});

If the data table name is inconsistent with the object name, you can use the tableName param to specify the table anme used when automatically generating the INSERT statement, for example:

int affectedRows =  client.InsertModel(user, "User2");

In this way, when the InsertModel method automatically generates the INSERT statement, it will use the property names of the user object as the column names and parameters, but use User2 as the table name.

Using an anonymous object and specifying the table name allows you to flexibly insert records into any table and any column. For example, there is a users table with id, user_name, and reg_date fields, where id is an auto-increment field and does not need to be assigned when inserting data, so you can do this:

var model = new 
{
    user_name = "name",
    reg_date = DateTime.Now
};
int affectedRows =  client.InsertModel(model, "users");

The above code is equivalent to executing:

int affectedRows = client.ExecuteNone("INSERT users (user_name, reg_date) VALUES (@user_name, @reg_date)", new {user_name = "name", reg_date = DateTime.Now});

If you need to return the auto-increment value of the newly inserted record, since System.Data does not have a related standard interface, the InsertModel method does not have a mechanism to automatically return the auto-increment value. but you can manually write SQL and then call the ExecuteObejct method to achieve this function: Assuming the Id of the User table in SqlServer is an INT type auto-incrment field, you can return the auto-increment value of the new record like this:

var sql = """
          INSERT User (Name, BirthDate) VALUES (@Name, @BirthDate) 
          SELECT SCOPE_IDENTITY()
          """;
int autoId = client.ExecuteScalar<int>(sql, new { Name = "name", BirthDate = DateTime.Now});

Assuming the Id of the User table in SqlServer is a UNIQUEIDENTIFIER type field, you need the database to generate and reutrn it automatically:

var sql = """
          DECLARE @Id UNIQUEIDENTIFIER
          SET @Id = NEWID()
          INSERT INTO User (Id, Name, Birth_Date) VALUES (@Id, @Name, @Date) 
          SELECT @Id
          """;
Guid autoId = client.ExecuteScalar<Guid>(sql, new { Name = "name", Date = DateTime.Now});

Assuming the Id of the User table in SQLite is an INTEGER type auto-increment field, you can:

var sql = """
          INSERT INTO User (Name, Birth_Date) VALUES (@Name , @Date);
          SELECT last_insert_rowid()
          """;
long autoId = client.ExecuteScalar<long>(sql, new { Name = "name",  Date = DateTime.Now.ToString("s") });

To update existing data in the data table, you can call the tool's UpdateModel method, for example:

var user = new User() 
{
    Id = 1,
    Name = "new",
    BirthDate = DateTime.Now
};
int affectedRows = client.UpdateModel(user, null);

The UpdateModel method will use the class name of the object passed in the model parameter as the table name, and the property name as the field name and paramenter to generate an UPDATE statement. The above code is equivalent to executing:

int affectedRows = client.ExecuteNone("UPDATE User SET Id = @Id, Name = @Name, BirthDate = @BirthDate", new { Id = 1, Name = "new", BirthDate = DateTime.Now });

It is very dangerous not to specify an update condition, so when using the UpdateModel method, you shoud provide a condition param to limit the update scope.

int affectedRows = client.UpdateModel(user, new { Id = user.Id });

In this way, the UPDATE statement generated by the UpdateModel method will have a WHERE clause, and the condition object's properties will be used as the query parameters for the WHERE clause.

When the data table name is inconsistent with the data object class name, you can use the tableName param to specify the table name used when generating the UPDATE statement:

int affectedRows = client.UpdateModel(user, new { Id = user.Id }, "User2");

As this time, the UpdateModel will use User2 as the table name when generating the UPDATE statement.

Note that as long as the data object has a certain property, the corresponding field will be generated when generating the UPDATE statement, even if the property of the data object is null. If you want to flexibly select the data column to be updated, you can use an anonymous object, for example, there is a users table with id, username, and birthday columns, and you want to update the username and birthday columns in the table with the content of user object, you can:

var wrapper = new 
{
    username = user.Name,
    birthday = user.BirthDate
};
int affectedRows = client.UpdateModel(wrapper, new { id = user.Id }, "users");

The UpdateModel method will use the specified table name and the property names of the anonymous object to generate the corresponding UPDATE statement, and use the condition object to generate the WHERE clause, which is equivalent to executing:

int affectedRows = client.ExecuteNone("UPDATE users SET username = @username, birthday = @birthday WHERE id = @id", new { username = user.Name, birthday = user.BirthDate, id = user.Id });

For easy use, this tool provides the InsertOrUpdateModel method, which inserts new record if there is no record that meets the condition, otherwise updates the record that meets the conditions, for example:

var user = new User()
{
    Id = 1,
    Name = "name",
    BirthDate = DateTime.Now
};
int affectedRows = client.InsertOrUpdateModel(user, new { Id = user.Id });

Tsql2005Builder will generate a composite SQL statement with a IF EXISTS() judgment for SqlServer execution, and Sqlite3SqlBuilder will generate a composite SQL statement with a changes()=0 judgment for SQLite execution. Like the UpdateModel method, InsertOrUpdateModel can also use anonymous object and specify table names to flexibly select the fields to be inserted or updated, for example:

var wrapper = new 
{
    username = user.Name,
    birthday = user.BirthDate
};
int affectedRows = client.InsertOrUpdateModel(wrapper, new { username = user.Name }, "users");

In this way, only the username and birthday fields of the users table that meet the conditions will be updated or inserted, without affecting other rows and fields.

This tool provides the DeleteModel method to delete data, which is very convenient to use, just pass in the table name:

int affectedRows = client.DeleteModel("User", null);

The DeleteModel method will generate the corresponding DELETE statement and execute it, which is equivalent to:

int affectedRows = client.ExecuteNone("DELETE FROM User");

Executing a DELETE statement without conditions is very dangerous, so you can use a condition object to specify the deletion range:

int affectedRows = client.DeleteModel("User", new { Name = "name" });

This equivalent to executing:

int affectedRows = client.ExecuteNone("DELETE FROM User WHERE Name = @Name", new { Name = "name" });

You can also directly call the ExecuteNone method to execute handwritten SQL:

client.ExecuteNone("TRUNCATE TABLE User")

This tool supports using TransactionScope to start a transaction, for example:

// SqlServer
using (var scope = new TransactionScope())
{
    // Add one row
    client.InsertModel(new User() { Id = 1, Name = "name", BirthDate = DateTime.Now});

    // Add another row
    client.ExecuteNone("INSERT INTO User (Id, Name, Entry_Date) VALUES (2, 'name2', GETDATE())");

   // complete transcationScope
    scope.Complete();
}

Currently, TransactionScope cannot work well with SQLite, so if you use this tool to perform transaction in SQLite, you need to use the ExeuteNone method to execute SQL statements with transations:

// SQLite
var sql = """
          BEGIN TRANSACTION;
                      
          INSERT INTO User (Name, BirthDate) VALUES ('name1' , datetime('now'));
          INSERT INTO User (Name, BirthDate) VALUES ('name2' , datetime('now'));
                      
          COMMIT;
          """;
client.ExecuteNone(sql);

Summary

Because of its lightweight nature, this tool can conveniently and flexibly help you complete basic database operations, but it does not provide some advanced features of heavy ORM frameworks. If you need advanced ORM capabilities, you can introduce heavy ORM frameworks such as EntityFramework or Hibernate into the project to complete complex requirements, while using this tool to complete functions that need to be quickly and flexibly implemented at low cost, because this tool adopts a very simple lightweight design and will not conflict with other ORM frameworks.

Clone this wiki locally