GitHub Open Source: SQLite Enhanced Components Sheng.SQLite.Plus

Github: Https://github.com/iccb1013/Sheng.SQLite.Plus

Sheng.SQLite.Plus is an enhanced component for working directly with the SQLite database using ADO, which operates in a way between the Entity Framework and ADO, and is a highly free and highly developed data for SQLite. Library access Layer components.

+ supports all ADO native operations

+ The developer defines the model and unlocks the relationship to database table one by one, which allows the developer to specify the mapping relationship flexibly. The same table can correspond to several different models.

+ supports direct use of SQL statements and dynamically maps data to the model based on query results in memory.

+ Automated transactions are supported for automatic rollback when data is processed in bulk.

+ supports a one-to-many mapping relationship where an entity class can be mapped to multiple tables, or vice versa.

+ Support Auto Fill/complement data in entity class, declare model and given primary key value or other condition, can populate model automatically.

+ Supports memory dynamic mapping of multiple grains of dataset, DataTable, DataRow, and generates strongly typed object collections directly from these data collections.

+ supports simple SQL constructor, supports automatic generation of simple SQL statements without model mapping.

+ supports fine-grained processing of entity fields, such as jsonattribute any property tag of an entity object, and automatically writes or reads fields in Json format.

+ High performance, high flexibility, high maintainability.

On Github, in addition to the full source code, a simple demo program is included:




Now assume that there is a User table, including four fields: Id,name,age,extrainfo.

We define a simple User class. (can also be generated automatically using other tools).

public class User
{
    public Guid Id
    {
        get;
        set;
    }
    public string Name
    {
        get;
        set;
    }
    public int Age
    {
        get;
        set;
    }
    public string ExtraInfo
    {
        get;
        set;
    }
}
 


Initialize Sheng.SQLite.Plus core class DatabaseWrapper,

string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings ["sqlite"]. ConnectionString;
DatabaseWrapper database = new DatabaseWrapper (connectionString);

First, simple data manipulation


1. Insert a piece of data:


public void AddUser (User user)
{
    _dataBase.Insert (user);
}

Sheng.SQLite.Plus's Insert method prototype is:

public bool Insert (object obj)

The Insert method will automatically parse the incoming object instance, analyze the type name (User) of the object and the properties it includes, and automatically implement a dynamic mapping of the User table and fields, inserting data into the table.


2. Query data


public List <User> GetUserList ()
{
    return _dataBase.Select <User> ();
}

The principle here is the same as above, the Select method automatically parses the object type, obtains the table and field information, and implements data query and filling.


3. Modify the data


public void UpdateUser (User user)
{
    _dataBase.Update (user);
}

In the Entity Framework, the way of tracking changes in object instances is used. Sheng.SQLite.Plus does not use this method, but directly based on the submitted object instance, without first querying the object from the database.


4. Delete data


public void RemoveUser (User user)
{
    _dataBase.Remove (user);
}

It should be noted that the primary key field must be specified in the entity class to modify and delete using the simple method above:


public class User
{
    [Key]
    public Guid Id
    {
        get;
        set;
    }
    ...
}

At this point we have automated basic database operations.


Is it familiar? Is it similar to Entity Framework?


Second, the mapping relationship between custom entity classes and database tables


The simple addition, deletion, modification, and checking operations mentioned above are based on the object instance to obtain the object type to obtain the type name and property (Property) set and their names, then what if the entity type name is different from the database table name? What if the property of the data entity does not correspond to the database table field one-to-one?

In large projects, this situation often exists. For complex database table design, there may be different interpretation methods at the business layer. For example, I have a user table that contains information on different dimensions of the product: basic information, Extended information, etc. At the business implementation level, I want to expand operations to two different physical objects, the basic information object and the extended information object. The fields they use may not be the same, but they include some common fields, such as Id, name.

As mentioned above, Sheng.SQLite.Plus does not mandate the mapping relationship between the entity class and the database table. The number of fields in the database table and the number of attributes in the entity class. It doesn't matter, if there is something in the entity class, it can be mapped by Attribute mark.


1. Database table name mapping designation


We define two different entity classes:


[Table ("User")]
public class User_BaseInfo
{
    [Key]
    public Guid Id
    {
        get;
        set;
    }
    public string Name
    {
        get;
        set;
    }
    public int Age
    {
        get;
        set;
    }
}

[Table ("User")]
public class User_ExtraInfo
{
    [Key]
    public Guid Id
    {
        get;
        set;
    }
    public string ExtraInfo
    {
        get;
        set;
    }
}

Just add TableAttribute before the type definition, operate on the object instance of the User_BaseInfo or User_ExtraInfo class, and directly use the addition, deletion, modification and search methods mentioned above. At this point we have begun to remove the strong association between the entity class and the database table results.


2. Mapping of database table fields


Strictly speaking here, it is not the mapping of database table fields in the general ORM, but the mapping of result set fields. For example, the result set obtained through a complex SQL or stored procedure is not a table in the database at all.

In some scenarios, additional attributes need to be defined in the entity class to store specific information or implement specific functions. These data do not need to be stored persistently. Or the attribute name in the entity class is not exactly the same as the database table field name. For example, after mapping a table to multiple data entities, in order to distinguish between descriptions and results obtained based on complex queries (SQL, stored procedures) Field names in the set.


[Table ("User")]
public class User_ExtraInfo
{
    [Key]
    public Guid Id
    {
        get;
        set;
    }
    [Column ("ExtraInfo")]
    public string Infomation
    {
        get;
        set;
    }
    [NotMapped]
    public int Count
    {
        get;
        set;
    }
}

Just add ColumnAttribute or NotMapped before the attribute definition, and use the method of addition, deletion, modification, and checking above to achieve the corresponding operation.


3. Many-to-many mapping of entity classes to database tables


This feature is used to further strongly type a two-dimensional database table (or result set).

When using the general ORM framework, for complex database table structures, many field definitions can often be seen, but in our actual business, these fields may have different logical attributions. In addition, in development, we may In the process of data transfer and operation, it is hoped that only part of the data will be passed or disclosed, rather than the entire object.



public class User
{
    [Key]
    public Guid Id
    {
        get;
        set;
    }
    public string Name
    {
        get;
        set;
    }
    public int Age
    {
        get;
        set;
    }
    [Partial]
    public ExtraInfo ExtraInfo
    {
        get;
        set;
    }
}
public class ExtraInfo
{
    public string ExtraInfo
    {
        get;
        set;
    }
}

Just add PartialAttribute to the object, the object representing the attribute is a sub-object represented by a part of the fields in the current data set.

PartialAttribute also provides FieldRelationship to further specify mapping relationships.

In this way, we have implemented a many-to-one mapping of entity classes to data tables (data sets), so how do we implement a many-to-many mapping? In fact, it is very simple. Use SQL, views, and stored procedures to perform multi-table queries, and use PartialAttribute in combination.


Third, advanced operations


1. Advanced query


In addition to the basic Select <T> () method mentioned above, Sheng.SQLite.Plus provides several additional advanced ways to query data.


a) Basic query

public List <T> Select <T> () where T: class, new ()

Shown above.


b) Additional query conditions

public List <T> Select <T> (Dictionary <string, object> attachedWhere) where T: class, new ()

Additional query conditions are specified via attachedWhere. String and object in Dictionary <string, object> specify fields and field values, respectively.


c) Query directly through SQL statements

public List <T> Select <T> (string sql) where T: class

Write SQL statements directly for data query. The Select method can automatically map according to the returned result set and the specified object type, and return a strongly typed object collection.

You can pass any SQL statement that can return a result set, and the returned result set automatically matches the generic T, which is not necessarily the object mapped by the table in the database.


d) Parameterized SQL statement query

public List <T> Select <T> (string sql, List <CommandParameter> parameterList) where T: class

Query parameterized SQL statements, such as:


List <CommandParameter> parameterList = new List <CommandParameter> ();
parameterList.Add (new CommandParameter ("@ extraInfo", "ABC"));
List <User> userList = _dataBase.Select <User> ("SELECT * FROM [User] WHERE ExtraInfo = @extraInfo", parameterList);

2.Dynamic mapping with DataSet in memory


When we use a stored procedure or other method to obtain a DataSet, Sheng.SQLite.Plus supports dynamic mapping of it, and obtains strongly typed object instances or collections of object instances based on the DataSet data set.


The RelationalMappingUnity class provides the following methods:

public static List <T> Select <T> (DataSet ds) where T: class

Think of the DataSet as a complete data source from which to find the table name mapped by the specified object type for instantiation.

public static List <T> Select <T> (DataTable dt) where T: class

Instantiates the specified object type using DataTable as the only data set.

public static T Select <T> (DataRow dr) where T: class
public static object Select (DataRow dr, Type type)
public static object Select (DataRow dr, Type type, Dictionary <string, string> fieldPair)

The above three methods provide more fine-grained operation possibilities, and get a strongly typed object instance directly from DataRow.


3. Data population


Many times we need to query to obtain an object instance according to a known condition. For example, we get the User's Id, and we want to query the database table to get the User object. In Sheng.SQLite.Plus, we can use the Fill method.


public bool Fill <T> (object obj) where T: class, new ()
public User GetUser (Guid id)
{
    User user = new User ();
    user.Id = id;
    if (_dataBase.Fill <User> (user))
        return user;
    else
        return null;
}

The Fill method returns a bool value indicating whether the data was successfully queried and filled.


The Fill method also has a higher-order overload that allows you to specify additional query conditions:

public bool Fill <T> (object obj, Dictionary <string, object> attachedWhere) where T: class, new ()

4.SQL statement constructor


Sometimes, we want to implement simple operations on database tables directly through SQL statements. Sheng.SQLite.Plus provides a SQL statement constructor to help generate SQL statements, which can reduce the workload and error probability of developers writing SQL statements. Improve the quality of software engineering.


public void AddUser (User user)
{
    SqlStructureBuild sqlStructureBuild = new SqlStructureBuild ();
    sqlStructureBuild.Type = SqlExpressionType.Insert;
    sqlStructureBuild.Table = "User";
    sqlStructureBuild.AddParameter ("Id", user.Id);
    sqlStructureBuild.AddParameter ("Name", user.Name);
    sqlStructureBuild.AddParameter ("Age", user.Age);
    SqlExpression sqlExpression = sqlStructureBuild.GetSqlExpression ();
    _dataBase.ExcuteSqlExpression (sqlExpression);
}

The ExcuteSqlExpression method executes the SqlExpression object generated by the SQL constructor using a parameterized, strongly typed method.


5. Affairs


For continuous database operations, Sheng.SQLite.Plus is automatically encapsulated as a transaction for execution, and if the execution fails, it will automatically roll back.


a) Continuous write operation


It is very simple, you can use the Insert method to insert a collection of objects. The method prototype is as follows:

public void InsertList (List <object> objList)

The write operation of the connection does not require that the parameters passed in are the same type, that is, multiple different similar entity objects can be passed in, such as User and Order at the same time, and Sheng.SQLite.Plus will also Encapsulated for transaction execution, either all writes succeed or rollback.


b) Complex compound operations


For relatively complex database transaction operations, you can use SQL statement constructors to construct SqlExpression objects, put them into collections in order of execution, and execute them through ExcuteSqlExpression.

public void ExcuteSqlExpression (List <SqlExpression> sqlExpressionList)

Multiple SqlExpression objects executed in this way are also encapsulated as transactions for execution.


Github: https://github.com/iccb1013/Sheng.SQLite.Plus

 

Welcome friends to join our group:

 

GitHub open source: SQLite enhancements Sheng.SQLite.Plus