Voy.DALBase 1.3.0-beta8

Voy.DALBase Readme

[TOC]

English

Simple ORM framework for MySql/Sql Server（Will add more） using lambda expressions.

Install

How to use

Instantiate

using Microsoft.Data.SqlClient; //Connect to SqlServer database.
using MySqlConnector;           //Connect to Mysql database.
using System.Data.OleDb;        //Connect to Access database.
using System.Data.SQLite;       //Connect to SQLite database.
using System.Data.Common;
using Voy.DALBase.Tools;

//When you need to connect to Mysql database.
DbConnection conn = new MySqlConnection("Server=XXX.XXX.XXX.XXX; Port=XXXX; Database=XXXX; Uid=XXXX; Pwd=XXXXXXXX; SslMode=Preferred;"); 

//When you need to connect to SqlServer database.
DbConnection conn = new SqlConnection("Server=XXX.XXX.XXX.XXX; Port=XXXX; Database=XXXX; Uid=XXXX; Pwd=XXXXXXXX; TrustServerCertificate=true;"); 

//When you need to connect to SQLite database.
string dbPath = @"D:\db1.sqlite";
SQLiteConnection.CreateFile(dbPath);
DbConnection connDB = new SQLiteConnection($"Data Source={dbPath}");

VDB vdb = new VDB(conn);   //normal mode.
VDBSingleton.Instance.VDB = new VDB(conn);  //singleton mode.

Public Methods

Insert

//Insert data with Lambda expressions.
var ins1 = vdb.Insert<User>(u => new User { Name = "Tom", Age = 30 });
int i1 = ins1.Execute();

//Insert data with entity objects. (<TClass> can be omitted)
var ins2 = vdb.Insert(new User { Name = "Jerry", Age = 40 });
int i2 = ins2.Execute();

//Insert multiple data.
var ins3 = vdb.Insert<User>(new List<User>
{
    new User { Name = "Doctor", Age = 10, CreateTime = DateTime.Now },
    new User { Name = "Bashful ", Age = 20, CreateTime = DateTime.Now },
    new User { Name = "Sleepy", Age = 50, CreateTime = DateTime.Now },
    new User { Name = "Sneezy", Age = 60, CreateTime = DateTime.Now },
    new User { Name = "Happy", Age = 70, CreateTime = DateTime.Now },
    new User { Name = "Dopey", Age = 80, CreateTime = DateTime.Now },
    new User { Name = "Grumpy", Age = 90, CreateTime = DateTime.Now },
});
int i3 = ins3.Execute();

• Usage: Insert<TClass>(), the parameter cannot be empty. The name of the method to obtain the result: Execute().
• You can use Lambda expressions and entity objects as parameters to insert one record at a time, or use List<TClass> type parameters to insert multiple records in batches.
• Returns the auto-increment primary key value when inserting a record. If the primary key is not self-increasing, return 0; when inserting multiple records, return the number of affected rows.
• Nullable properties will have a null value inserted, non-nullable properties will have a default value inserted.

Update

//Update data with Lambda expressions.
var upd1 = vdb.Update<User>(u => new User { Id = 1, Name = "Sleepy", Age = 50 });
int u1 = upd1.Execute();

//Update data with expression + Where method.
var upd2 = vdb.Update<User>(u => new User { Name = "Sneezy", Age = 60 }).Where(u => u.Name == "Jerry");
int u2 = upd2.Execute();

//Update data with an anonymous object.
var upd3 = vdb.Update<User>(new { Id = 1, Name = "Happy", Age = 70 });
int u3 = upd3.Execute();

//Update data with anonymous object update + Where method.
var upd4 = vdb.Update<User>(new { Name = "Dopey", Age = 80 }).Where(u => u.Name == "Sneezy");
int u4 = upd4.Execute();

• Usage: Update<TClass>(), the parameter cannot be empty. The name of the method to obtain the result: Execute().
• You can use: Lambda expressions, anonymous objects as parameters to modify a record. Entity objects are currently not supported, and using entity objects will not execute successfully. For forward compatibility of subsequent versions, the parameter type continues to use dynamic.
• Any column can be updated when using the anonymous object parameter and the "Where()" method to update the record; when only using the object parameter to update, the value of the primary key field will not be updated as a filter condition.
• The attribute with the same name as "TClass" in the anonymous object parameter will not appear in the generated SQL statement if it has the "DatabaseGeneratedOption.Computed" label.
• When the primary key field is not included in the column of the anonymous object, and the Where statement is not used for update filtering, the update operation will not be executed for data security.

Delete

//Delete data with "Where" method.
var del1 = vdb.Delete<User>().Where(u => u.Name == "周八");
int d1 = del1.Execute();

//Delete multiple data with "Where" method.
List<int> ids = new List<int>{ 1, 2 };
var del2 = vdb.Delete<User>().Where(u => ids.ToArray().Contains(u.Id));
int d2 = del2.Execute();

//Delete multiple data with IList<TClass>. (<TClass> can be omitted, All properties need to be assigned a value to take effect.)
var del3 = vdb.Delete(new List<User>() {
    new User(){ Id = 1, Name = "Tom", Age = 2},
    new User(){ Id = 2, Name= "Jerry", Age = 1 }
});
int d3 = del3.Execute();

• Usage method: Delete<TClass>(), the parameter can be empty. The name of the method to obtain the result: Execute().
• You can use Lambda expressions and entity objects as parameters to delete records, or use the Where method as filter conditions to delete records.
• When using an entity object as a parameter to filter records, if the object's property is a numeric default value (such as 0 for int), it will have no effect. For example, do not use "new User { Age = 0 }".
• When there are parameters and the Where statement is used for update filtering, the filter conditions will be combined with AND.
• When there is no parameter and no Where statement is used for update filtering, the update operation will not be executed for data security.

Transaction

try
{
    vdb.BeginTransaction();
    int? result = vdb.Insert<User>(u => new User { Name = "NewUser1", Age = 30 }).Execute();
    int newId = result is null ? 0 : (int)result;
    var query = vdb.Insert<Order>(new List<Order>
    {
            new Order { UserId=newId, Product = "NewProduct1", Amount = (decimal)10.10 },
            new Order { UserId=newId, Product = "NewProduct2", Amount = (decimal)20.20 },
            new Order { UserId=newId, Product = "NewProduct3", Amount = (decimal)30.30 },
            new Order { UserId=newId, Product = "NewProduct4", Amount = (decimal)40.40 },
            new Order { UserId=newId, Product = "NewProduct5", Amount = (decimal)50.50 },
    }).Execute();
    vdb.Commit();
}
catch (Exception ex)
{
    vdb.Rollback();
    //Write to log;
}

Select

//Get all fields
var sel1 = vdb.Select<User>()
    .Where<User>(u => u.Name == "Happy")
    .OrderBy(u => u.Id)
    .OrderBy(u => u.Age)
    .Page(1, 10);
var items = sel1.GetData();

//Get the specified fields
var sel2 = vdb.Select<User>(u => new { u.Id, u.Name })
    .Where<User>(u => u.Name == "Happy")
    .OrderByDesc(u => u.Id)
    .OrderBy(u => u.Age)
    .Page(1, 10);
var items = sel1.GetData();

//Multi-table query
var sel1 = vdb.Select<User, Order>((u, o) => new { u.Id, u.Name, o.Amount, o.CreateTime })
    .LeftJoin((u, o) => u.Id == o.UserId)
    .Where((u, o) => u.Name == "Happy")
    .OrderBy((u, o) => u.Id)
    .OrderBy((u, o) => u.Age)
    .Page(1, 10);
var items = sel1.GetData();

• Support multi-level master-slave table query, support one-to-many, many-to-one, one-to-one query, and support a maximum of 8 tables at a time.
• The multi-table query always returns the set of the first formal parameter, so in order to return the result correctly, the Property of the model class whose type is the sub-table mapping should be added to the model class mapped by the main table, Property can be a single model or a generic collection of List<sub-table models>.

Operators For Filter（Where Method）

• Methods can also be used multiple times, and the order of use is not required. The relationship between the filter conditions executed by multiple methods is And.
• If there is an "OR" relationship, you can use the static method "Tools.ExpressionTools.CreateExpression<T>()" to create more flexible filter conditions as parameters. For example:

var e1 = ExpressionTools.CreateExpression<User>(u => u.Id == 1 || u.Name.Contains("z"));
var e2 = ExpressionTools.CreateExpression<User>(u => u.Age > 20);

var exp = e1.And(e2);
var v1 = vdb.Select<User>().Where(exp);

Union primary key query

var query = vdb.Select<User, Order>()
    .LeftJoin((u, o) => u.Id == o.UserId && u.Name == o.UserName)；
var data = query.GetData();

Multi-column query

Setting the second parameter "filterColumns" of the Where method to a "new expression" can achieve a more convenient multi-column query, so that the filter condition can be applied to all columns specified in the expression. For example:

var query = vdb.Select<User, Order>()
    .Where(u => u.Name.Contains("Tianjin"), u => new { u.Name, u.Introduction });
var data = query.GetData();

Recursive query

Suitable for querying infinitely hierarchical table structures. For example, the table contains a ParentId field, which is used to record the superior of the current record. Implementation:

• Use the first expression parameter of "InnerJoin" to set the relationship between the identification field and its parent identification field, and set the condition for the end of recursion in the second expression parameter.
• The third expression is an optional parameter. If this parameter is set as an property mapped to a field, VDB will use this property as a condition to convert the query result into a tree structure.
• The result of the spanning tree structure requires that the Model mapped by the main table contains a generic List collection property of the same type as the main table.
• SQLServer has no limit to the recursion depth, and the maximum recursion depth of MySQL is 4,294,967,295.

    [Table("user")]
    public class User
    {
        ···
        public int Id { get; set; }
        public int ParentId { get; set; }
        ...
        public List<User> Users { get; set; }        
        public List<Order> Orders { get; set; }
    }

var query = vdb.Select<User, Order>((u, o) => new { u.Id, u.ParentId, u.Name, u.ShowOrder, o.Product })
     .LeftJoin((u, o) => o.UserId == u.Id)
     .InnerJoin((u) => u.Id == u.ParentId, u => u.Id == 1, u => u.ParentId)
     ;

Cond Func

var v1 = vdb.Select<User>()
.IF(u => u.IsDeleted == 0 ? "Not deleted" : "Deleted", "Deleted status")
.IFNULL(u => u.Name ?? "Empty Name")
var result = v1.GetData();

Agg Func

var v1 = vdb.Select<User>()
.SUM(u => u.Age,)
.COUNT(u => u.IsDeleted == 0)
var result = v1.GetData();

About Pagination query on multiple tables

Should be implemented using FromQuery subqueries. For example:

var query = vdb.Select<User, Order>()
    .FromQuery(vdb.Select<User>(u => new { u.Id, u.Name })
            .Where(u => u.IsDeleted == 0)
            .OrderBy(u => u.ShowOrder)
            .Page(1, 10))
    .LeftJoin((u, o) => u.Id == o.UserId)
    ;

About Attribute For Model

• Use the [Table] and [Column] tags on the class and attribute to map the specified data table and data column.
• If no data table/data column name is specified, VDB will apply different naming rules to map class name → data table name, attribute name → data column name according to the mapped database brand.

Example of use

    /// <summary>
    /// User
    /// </summary>
    [Table("user")]
    public class User
    {
        [Column(Order = 0)]
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        [Description("Name")]
        [Column("name", TypeName = "varchar(50)")]
        public string? Name { get; set; }

        [Description("Age")]
        public int? Age { get; set; }

        [Required]
        [DefaultValue(0)]
        [Description("ShowOrder")]
        public int? ShowOrder { get; set; }

        /// <summary>
        /// UserType.
        /// </summary>
        public UserType? UserType { get; set; }

        /// <summary>
        /// OrderList
        /// </summary>
        public List<Order>? Orders { get; set; } = new List<Order>();

        [Required]
        [DefaultValue(0)]
        [Description("IsDelete")]
        public sbyte? IsDeleted { get; set; }

        [Column(TypeName = "timestamp")]
        [Required]
        [DefaultValue("CURRENT_TIMESTAMP")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Description("CreateTime")]
        public DateTime? CreateTime { get; set; }
    }

Table

Column

Description

1. An int attribute with Key and Identity tags, and the mapped data column is an auto-growth column.
2. The fields identified by Key and Required are required and do not need to be set repeatedly.
3. The Identity label (automatic growth) is only valid for int type columns. After the DefaultValue is set for non-int type columns, the Identity label will be ignored.
4. For attributes without a Column tag, the corresponding column name will be generated from the attribute name according to the database brand, and the corresponding column type and default maximum length will also be generated according to the data type of the attribute.
5. For attribute mapping columns with the Computed tag set, the default value will be calculated when inserting and updating, and the default value for which Computed is not set is only calculated when inserting.
6. If the value of the required item is not set when inserting data, and the item has no default value, the Insert statement will automatically increase the default value of the item type according to the content of "TypeName". If TypeName is not set, the closest field type will be assigned based on the attribute type.
7. When using the entity class to insert records, it is recommended to set all mapped attributes to nullable types. The reason is that the non-empty attributes in the entity class will automatically generate default values, and the fields with values will be added to the SQL statement.
8. When inserting a record using an object instance, if the value of the attribute mapped to the timestamp type field is not set, it cannot be executed because the minimum time is automatically generated. Workaround 1. Set the property as a nullable type. 2. Change the timestamp to time type. 3. Set the attribute value of the field mapping within the range allowed by the timestamp.
9. It is recommended to set the int type attribute (mostly seen in the Id field) to the self-growth (Identity tag) as the nullable type "int?", otherwise the field with a value of 0 will be included in the SQL insertion content, but it can be executed successfully .
10. When inserting in batches, the attributes mapped to mandatory fields with default values must be set or not set at all. Because there is an object in the set of parameters with this field set, this field will appear in the SQL statement, and other records without this field cannot be executed successfully due to lack of parameters.
11. ForeignKey Demo：

public class User
{
    public int Id { get; set; }
    ...
    public List<Order> Orders { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    [ForeignKey("Seller")]
    public int SellerId { get; set; }
    [ForeignKey("Buyer")]
    public int BuyerId { get; set; }
    ...
    public User Seller { get; set; }
    public User Buyer { get; set; }
}

DBFirst

Example

using Voy.DALBase;
using Voy.DALBase.Tools;
using Voy.DALBase.Utils;

string _connString = "Server=192.168.110.100; Port=3306; Database=vdb_test; Uid=XXXX; Pwd=XXXXXX; SslMode=Preferred;";
DbConnection conn = new MySqlConnection(_connString);
CodeTool tool = new CodeTool(conn) { Language = ProgrammingLanguage.CSharp };

var result1 = tool.CreateModel().ToFile();
var result2 = tool.CreateIRepository().ToFile();
var result3 = tool.CreateRepository().ToFile();
var result4 = tool.RegisterIoC().InsertFile();
var result5 = tool.CreateWebAPIController().ToFile();

• You can use the CodeTool's method to generate codes based on the structure of the data table.
• Use the ToString() method to get the code in text form. The generated code is output as the characters contained in the Value of the Dictionary, and the Key in the Dictionary is the table name.
• Use the ToFile() method to automatically save the code to the specified file directory. The default path is the folder of the project or insert the generated code into the file.
• Supports specifying the use of programming languages such as C# and Visual Basic.
• Supports generating data model classes, database interfaces, database classes, Ioc registration code fragments, and WebAPI controller classes.
• It supports specifying the mapped data table, or you can ignore this parameter and scan all data tables to create all data models.
• Support for specifying namespace names.
• Support for adding inherited base classes, or implemented interfaces.
• Support specifying data columns that need to be ignored.

IoC registration example

public static void Main(string[] args)
{    
    string _connString = "Server=192.168.110.100; Port=3306; Database=vdb_test; Uid=XXXX; Pwd=XXXXXX; SslMode=Preferred;";
    DbConnection conn = new MySqlConnection(_connString);
    CodeTool tool = new CodeTool(conn) { Language = ProgrammingLanguage.CSharp };

    var result1 = tool.CreateModel().ToFile();
    var result2 = tool.CreateIRepository().ToFile();
    var result3 = tool.CreateRepository().ToFile();
    var result4 = tool.RegisterIoC(nameof(conn)).InsertFile();
    var result5 = tool.CreateWebAPIController().ToFile();

    var builder = WebApplication.CreateBuilder(args);

    // Add services to the container.

    builder.Services.AddControllers();
    // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
    builder.Services.AddEndpointsApiExplorer();
    builder.Services.AddSwaggerGen();

    // The following is the code automatically generated by "tool.RegisterIoC(nameof(conn)).InsertFile()".
    Func<IServiceProvider, VDB> vdb = r => new VDB(conn);

    builder.Services.AddSingleton<IVDB, VDB>(vdb);
    builder.Services.AddScoped<IUserRepository, UserRepository>();
    ...

    var app = builder.Build();

Contact Us

email:cnxl@hotmail.com, we will reply as soon as possible.

中文

适用于MySql/Sql Server（将添加更多），可使用Lambda表达式的简单ORM框架。

安装

如何使用

实例化

using Microsoft.Data.SqlClient; //连接到SqlServer数据库。
using MySqlConnector;           //连接到Mysql数据库。
using System.Data.OleDb;        //连接到Access数据库。
using System.Data.SQLite;       //连接到SQLite数据库。
using System.Data.Common;
using Voy.DALBase.Tools;

//当你需要连接到Mysql数据库。
DbConnection conn = new MySqlConnection("Server=XXX.XXX.XXX.XXX; Port=XXXX; Database=XXXX; Uid=XXXX; Pwd=XXXXXXXX; SslMode=Preferred;"); 

//当你需要连接到SqlServer数据库。
DbConnection conn = new SqlConnection("Server=XXX.XXX.XXX.XXX; Port=XXXX; Database=XXXX; Uid=XXXX; Pwd=XXXXXXXX; TrustServerCertificate=true;"); 

//当你需要连接到SQLite数据库。
string dbPath = @"D:\db1.sqlite";
SQLiteConnection.CreateFile(dbPath);
DbConnection connDB = new SQLiteConnection($"Data Source={dbPath}");

VDB vdb = new VDB(conn);   //普通模式。
VDBSingleton.Instance.VDB = new VDB(conn);  //单例模式。

公共方法

插入

//使用Lambda表达式插入数据。
var ins1 = vdb.Insert<User>(u => new User { Name = "张三", Age = 30 });

//使用实体对象插入数据。（可省略<TClass>）
var ins2 = vdb.Insert(new User { Name = "李四", Age = 40 });

//批量插入数据。
var ins3 = vdb.Insert<User>(new List<User>
{
    new User { Name = "刘一", Age = 10, CreateTime = DateTime.Now },
    new User { Name = "陈二", Age = 20, CreateTime = DateTime.Now },
    new User { Name = "王五", Age = 50, CreateTime = DateTime.Now },
    new User { Name = "赵六", Age = 60, CreateTime = DateTime.Now },
    new User { Name = "孙七", Age = 70, CreateTime = DateTime.Now },
    new User { Name = "周八", Age = 80, CreateTime = DateTime.Now },
    new User { Name = "吴九", Age = 90, CreateTime = DateTime.Now },
    new User { Name = "郑十", Age = 100, CreateTime = DateTime.Now },
});

• 使用方法：Insert<TClass>()，参数不可为空。获取结果方法名称：Execute()。
• 可以使用Lambda表达式、实体对象做为参数单次插入一条记录，也可以用List<TClass>型参数批量插入多条记录。
• 插入一条记录时返回自增主键值。如果主键不是自增长，返回0。插入多条记录时返回影响行数。
• 可空的属性将插入空值，不可空属性将插入默认值。

更新

//用Lambda表达式更新数据。
var upd1 = vdb.Update<User>(u => new User { Id = 1, Name = "王五", Age = 50 });

//用表达式 + Where方法更新数据。
var upd2 = vdb.Update<User>(u => new User { Name = "赵六", Age = 60 }).Where(u => u.Name == "李四");

//用匿名对象更新数据。
var upd3 = vdb.Update<User>(new { Id = 1, Name = "孙七", Age = 70 });

//用匿名对象更新 + Where方法更新数据。
var upd4 = vdb.Update<User>(new { Name = "周八", Age = 80 }).Where(u => u.Name == "赵六");

• 使用方法：Update<TClass>()，参数不可为空。获取结果方法名称：Execute()。
• 可以使用Lambda表达式、匿名对象作为参数修改一条记录，目前不支持实体对象，使用实体对象将不能成功执行。为了后续版本向前兼容，参数类型持续使用dynamic。
• 使用匿名对象参数和Where()方法更新记录时可以更新包括主键在内的任何列；只使用对象参数更新时，主键字段的值将作为筛选条件不进行更新。
• 匿名对象参数中与“TClass”中同名的属性，如果带有“DatabaseGeneratedOption.Computed”标签将不会出现在生成的SQL语句中。
• 匿名对象的列中未包括主键字段，也没有使用Where语句进行更新筛选时，为了数据安全更新操作将不会被执行。

删除

//用Lambda表达式删除数据。
var del1 = vdb.Delete<User>(u => new User { Id = 1 });

//用Where方法删除数据。
var del2 = vdb.Delete<User>().Where(u => u.Name == "周八");

//用实体对象删除数据。（可省略<TClass>）
var del3 = vdb.Delete(new User() { Name = "周八" });

//用Lambda表达式 + Where方法删除数据。
var del4 = vdb.Delete<User>(u => new User { Id = 1 }).Where(u => u.Name == "周八");

//用实体对象 + Where方法删除数据。
var del5 = vdb.Delete<User>(new User { Id = 1 }).Where(u => u.Name == "周八");

• 使用方法：Delete<TClass>()，参数可为空。获取结果方法名称：Execute()。
• 可以使用Lambda表达式、实体对象做为参数删除记录，也可以用Where方法作为筛选条件删除记录。
• 使用实体对象为参数筛选记录时，如果对象的属性是数值型的默认值（例如int的0）则无效果，例如不要使用“new User { Age = 0 }”。
• 当有参数，也使用Where语句进行更新筛选时，筛选条件将进行AND合并。
• 当无参数，也没有使用Where语句进行更新筛选时，为了数据安全更新操作将不会被执行。

事务

try
{
    vdb.BeginTransaction();
    int? result = vdb.Insert<User>(u => new User { Name = "NewUser1", Age = 30 }).Execute();
    int newId = result is null ? 0 : (int)result;
    var query = vdb.Insert<Order>(new List<Order>
    {
            new Order { UserId=newId, Product = "NewProduct1", Amount = (decimal)10.10 },
            new Order { UserId=newId, Product = "NewProduct2", Amount = (decimal)20.20 },
            new Order { UserId=newId, Product = "NewProduct3", Amount = (decimal)30.30 },
            new Order { UserId=newId, Product = "NewProduct4", Amount = (decimal)40.40 },
            new Order { UserId=newId, Product = "NewProduct5", Amount = (decimal)50.50 },
    }).Execute();
    vdb.Commit();
}
catch (Exception ex)
{
    vdb.Rollback();
    //Write to log;
}

查询

//获取全部字段
var sel1 = vdb.Select<User>()
    .Where<User>(u => u.Name == "王五")
    .OrderBy(u => u.Id)
    .OrderBy(u => u.Age)
    .Page(1, 10);
var items = sel1.GetData();

//获取指定字段
var sel2 = vdb.Select<User>(u => new { u.Id, u.Name })
    .Where<User>(u => u.Name == "王五")
    .OrderByDesc(u => u.Id)
    .OrderBy(u => u.Age)
    .Page(1, 10);
var items = sel1.GetData();

//多表查询
var sel1 = vdb.Select<User, Order>((u, o) => new { u.Id, u.Name, o.Amount, o.CreateTime })
    .LeftJoin((u, o) => u.Id == o.UserId)
    .Where((u, o) => u.Name == "张三")
    .OrderBy((u, o) => u.Id)
    .OrderBy((u, o) => u.Age)
    .Page(1, 10);
var items = sel1.GetData();

• 支持多级主从表查询，支持一对多、多对一、一对一查询，最大可支持一次查询8个表。
• 多表查询总是返回第一个形参的集合，所以为了正确返回结果，应在主表映射的模型类中增加类型为子表映射的模型类的属性，属性可以是单一模型或是List<子表模型>的泛型集合。

过滤器的运算符（Where 方法）

• 以上方法可以多次使用，使用顺序无要求。多个方法执行的筛选条件之关系为And。
• 如果有“OR”的关系可以使用静态方法“Tools.ExpressionTools.CreateExpression<T>()”来创建更灵活的筛选条件作为参数。例如：

var e1 = ExpressionTools.CreateExpression<User>(u => u.Id == 1 || u.Name.Contains("z"));
var e2 = ExpressionTools.CreateExpression<User>(u => u.Age > 20);

var exp = e1.And(e2);
var v1 = vdb.Select<User>().Where(exp);

联合主键查询

var query = vdb.Select<User, Order>()
    .LeftJoin((u, o) => u.Id == o.UserId && u.Name == o.UserName)；
var data = query.GetData();

多列查询

将Where方法的第二个参数“filterColumns”设置为new表达式可以实现更方便的多列查询，这样可以将过滤条件应用到表达式中指定的全部列。例如：

var query = vdb.Select<User, Order>()
    .Where(u => u.Name.Contains("天津"), u => new { u.Name, u.Introduction });
var data = query.GetData();

递归查询

适用于查询无限分级的表结构。例如表中含有ParentId字段，用来记录当前记录的上级。 实现方法：

• 使用“InnerJoin”的第一个表达式参数中设置标识字段与其父标识字段的关系，并在第二个表达式参数中设置递归结束的条件。
• 第三个表达式为可选参数，如果设置该参数为一个字段映射的属性，VDB将使用该属性作为条件，将查询结果转化为树结构。
• 生成树结构的结果要求主表映射的Model中包含有主表同类型的泛型List集合属性。
• SQLServer没有递归深度的限制，MySQL最大递归深度为4,294,967,295。

    [Table("user")]
    public class User
    {
        ···
        public int Id { get; set; }
        public int ParentId { get; set; }
        ...
        public List<User> Users { get; set; }        
        public List<Order> Orders { get; set; }
    }

var query = vdb.Select<User, Order>((u, o) => new { u.Id, u.ParentId, u.Name, u.ShowOrder, o.Product })
    .LeftJoin((u, o) => o.UserId == u.Id)
    .InnerJoin((u) => u.Id == u.ParentId, u => u.Id == 1, u => u.ParentId)
    ;

条件函数

var v1 = vdb.Select<User>()
.IF(u => u.IsDeleted == 0 ? "未删除" : "已删除", "删除状态")
.IFNULL(u => u.Name ?? "空名字")
var result = v1.GetData();

聚合函数

var v1 = vdb.Select<User>()
.Sum(u => u.Age,)
.Count(u => u.IsDeleted == 0)
var result = v1.GetData();

关于多表的分页查询

应使用FromQuery子查询来实现。例如：

var query = vdb.Select<User, Order>()
    .FromQuery(vdb.Select<User>(u => new { u.Id, u.Name })
            .Where(u => u.IsDeleted == 0)
            .OrderBy(u => u.ShowOrder)
            .Page(1, 10))
    .LeftJoin((u, o) => u.Id == o.UserId)
    ;

关于Model的属性

• 在类和属性上分别使用[Table]和[Column]标签可以映射指定的数据表和数据列。
• 在不指定数据表/数据列名称的情况下，VDB会根据映射的数据库品牌，应用不同的命名规则来映射类名->数据表名、属性名->数据列名。

使用示例

    /// <summary>
    /// 用户
    /// </summary>
    [Table("user")]
    public class User
    {
        [Column(Order = 0)]
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        [Description("姓名。")]
        [Column("name", TypeName = "varchar(50)")]
        public string? Name { get; set; }

        [Description("年龄。")]
        public int? Age { get; set; }

        [Required]
        [DefaultValue(0)]
        [Description("显示顺序。")]
        public int? ShowOrder { get; set; }

        /// <summary>
        /// 用户类型。
        /// </summary>
        public UserType? UserType { get; set; }

        /// <summary>
        /// 订单列表。
        /// </summary>
        public List<Order>? Orders { get; set; } = new List<Order>();

        [Required]
        [DefaultValue(0)]
        [Description("是否逻辑删除。")]
        public sbyte? IsDeleted { get; set; }

        [Column(TypeName = "timestamp")]
        [Required]
        [DefaultValue("CURRENT_TIMESTAMP")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Description("创建时间。")]
        public DateTime? CreateTime { get; set; }
    }

数据表

数据列

说明

1. 设置了Key和Identity标签的int型属性，映射的数据列是自动增长列。
2. Key、Required标识的字段均为必填，无需对同一个属性重复设置这两个特性。
3. Identity标签（自动增长）只对int类型的列生效，非int类型列设置DefaultValue后，将忽略Identity标签。
4. 设置了Computed标签的属性映射列，在插入和更新时默认值均会被计算，没有设置Computed的默认值只在插入时计算。
5. 没有设置Column标签的属性，会根据数据库品牌，用属性名生成相应形式的列名，也会根据属性的数据类型生成相应的列类型及默认最大长度。
6. 插入数据时如果没有设置必填项的数值，该项目也没有默认值，Insert语句将根据“TypeName”的内容自动增加该项目类型的默认值。如果没有设置TypeName，则会根据属性类型分配最接近的字段类型。
7. 使用实体类插入记录时，推荐将映射的属性全部设置为可空类型。原因是实体类里的非空属性会自动产生默认值，有值的字段都会加入SQL语句中。
8. 在使用对象实例插入记录时，如果没有设置映射为时间戳类型字段的属性的值，会由于自动生成了最小时间而无法执行。解决方法1.将属性设置为可空类型。2.时间戳改为时间类型。3.设置该字段映射的属性值在时间戳允许的范围内。
9. 设置为自增长（Identity标签）的int类型的属性（多见于Id字段）建议设置为可空类型“int？”，否则会在sql插入内容中包括值为0的该字段，但是可以执行成功。
10. 批量插入时，有默认值的必填字段映射的属性要全部设置或全部不设置数值。因为参数的集合中有一个对象设置了该字段，就会在SQL语句中出现该字段，其他未设置该字段的记录则因缺少参数而不能执行成功。
11. ForeignKey示例：

public class User
{
    public int Id { get; set; }
    ...
    public List<Order> Orders { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    [ForeignKey("Seller")]
    public int SellerId { get; set; }
    [ForeignKey("Buyer")]
    public int BuyerId { get; set; }
    ...
    public User Seller { get; set; }
    public User Buyer { get; set; }
}

DBFirst

示例

using Voy.DALBase;
using Voy.DALBase.Tools;
using Voy.DALBase.Utils;

string _connString = "Server=192.168.110.100; Port=3306; Database=vdb_test; Uid=XXXX; Pwd=XXXXXX; SslMode=Preferred;";
DbConnection conn = new MySqlConnection(_connString);
CodeTool tool = new CodeTool(conn) { Language = ProgrammingLanguage.CSharp };

var result1 = tool.CreateModel().ToFile();
var result2 = tool.CreateIRepository().ToFile();
var result3 = tool.CreateRepository().ToFile();
var result4 = tool.RegisterIoC().InsertFile();
var result5 = tool.CreateWebAPIController().ToFile();

• 可以根据数据表的结构，使用CodeTool的方法来生成代码。
• 使用ToCodeString()方法获得文字形式的代码。生成的代码输出为包含在Dictionary的Value中的字符，Dictionary中的Key是表名。
• 使用ToFile()方法可以让代码自动保存到指定的文件目录中，默认路径是项目的文件夹中或将生成的代码插入文件中。
• 支持指定使用C#、Visual Basic等编程语言。
• 支持生成数据模型类、资料库接口、资料库类、Ioc注册代码片段、WebAPI控制器类。
• 支持指定映射的数据表，也可以忽略这个参数而扫描全部数据表，进而创建全部的数据模型。
• 支持指定命名空间名称。
• 支持添加继承的基类，或实现的接口。
• 支持指定需要忽略的数据列。

IoC注册示例

public static void Main(string[] args)
{    
    string _connString = "Server=192.168.110.100; Port=3306; Database=vdb_test; Uid=XXXX; Pwd=XXXXXX; SslMode=Preferred;";
    DbConnection conn = new MySqlConnection(_connString);
    CodeTool tool = new CodeTool(conn) { Language = ProgrammingLanguage.CSharp };

    var result1 = tool.CreateModel().ToFile();
    var result2 = tool.CreateIRepository().ToFile();
    var result3 = tool.CreateRepository().ToFile();
    var result4 = tool.RegisterIoC(nameof(conn)).InsertFile();
    var result5 = tool.CreateWebAPIController().ToFile();

    var builder = WebApplication.CreateBuilder(args);

    // Add services to the container.

    builder.Services.AddControllers();
    // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
    builder.Services.AddEndpointsApiExplorer();
    builder.Services.AddSwaggerGen();

    // 以下为“tool.RegisterIoC(nameof(conn)).InsertFile()”自动生成的代码。
    Func<IServiceProvider, VDB> vdb = r => new VDB(conn);

    builder.Services.AddSingleton<IVDB, VDB>(vdb);
    builder.Services.AddScoped<IUserRepository, UserRepository>();
    ...

    var app = builder.Build();

联系我们

email:cnxl@hotmail.com，我们将尽快回复。