JCoder.Db4Net 3.5.3

dotnet add package JCoder.Db4Net --version 3.5.3
NuGet\Install-Package JCoder.Db4Net -Version 3.5.3
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="JCoder.Db4Net" Version="3.5.3" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add JCoder.Db4Net --version 3.5.3
#r "nuget: JCoder.Db4Net, 3.5.3"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install JCoder.Db4Net as a Cake Addin
#addin nuget:?package=JCoder.Db4Net&version=3.5.3

// Install JCoder.Db4Net as a Cake Tool
#tool nuget:?package=JCoder.Db4Net&version=3.5.3

JCoder.Db4Net

属性
作者(Authors) Jackie Law
版权(Copyright) Copyright © Jackie Law. All rights reserved.
协议 MIT
联系(QQ) 1378071853
联系(Wechat) JackieLaw1024
联系(Email) JackieLaw-dev@outlook.com

[TOC]


一.架构说明

JCoder.Db4Net
语言 C#
协议 MIT
.Net框架 .Net Standard2.1; .Net Framework 4.7.2; .Net 7; .Net 6;
简述 Database类库,一款为方便使用数据库的操作类库。

1.1 引用类库

引用类库 版本
- -

1.2 相关类库

相关类库 说明
JCoder.Db4Net 一款为方便使用数据库的操作类库。
JCoder.Db4Net.Sqlite 一个基于JCoder.Db4Net且支持Sqlite的库。
JCoder.Db4Net.MySql 一个基于JCoder.Db4Net且支持Mysql的库。
JCoder.Db4Net.SqlServer 一个基于JCoder.Db4Net且支持SqlServer的库 。
JCoder.Mock4Net 一款简易版的用于模拟数据的功能库。
JCoder.Log4Net 一款用于日志记录的类库。
JCoder.Log4Net.Sqlite 一款使用Sqlite进行日志记录的类库。
JCoder.Ioc4Net 一款简易款的Ioc操作类库。
JCoder.Extension A simple library for C# extension.(一款简易的用于c#的扩展库。)

获取方式

  1. 版本要求:>=3.0.0
  2. 以版本3.0.0为例
获取方式 描述
Package Manager Install-Package JCoder.Db4Net -Version 3.0.0
.Net CLI dotnet add package JCoder.Db4Net --version 3.0.0
PackageReference <PackageReference Include="JCoder.Db4Net" Version="3.0.0" />
Paket CLI paket add JCoder.Db4Net --version 3.0.0
Script&Interactive #r "nuget: JCoder.Db4Net, 3.0.0"
Cake // Install as a Cake Addin<br/>#addin nuget:?package=JCoder.Db4Net&version=3.0.0<br/><br/>// Install as a Cake Tool<br/>#tool nuget:?package=JCoder.Db4Net&version=3.0.0

三.使用说明

3.1 主要类与接口

名称 类型 描述
IDbSqlTexter 接口 主要用于SQL语句的生成。
IDbBuilder 接口 主要用于数据库结构的处理。
IDbOperator 接口 主要用于SQL的执行与处理。
IFactoryDbOperator 接口 通过ProviderFactory方式,进行SQL的执行与处理。<br/>主要有以下实现:MsSqlFactoryOperator、SQLiteFactoryOperator 、MySqlFactoryOperator 。
Db 工具类,主要用于数据库通用的处理。
Orm 工具类,主要用于数据库在数据对象上的通用处理。

3.2特性说明

Attribute类 应用位置 描述
DbDatabaseAttribute Class、Struct 数据库的相关信息,可用于通过Entity进行CRUD。
DbTableAttribute Class、Struct 数据库的数据表信息,可用于通过Entity进行CRUD。
DbViewAttribute Class、Struct 数据库的视图信息,可用于通过Entity进行查询等操作。
DbRefTableAttribute Property (未完成)关联的数据表信息,可用于通过Entity获取关联的副表数据。
DbFieldAttribute Property 数据列信息,可用于通过Entity进行CRUD。
DbFieldExtendAttribute Property 数据列的扩展信息。
DbFieldPropertyAttribute Property 数据列的属性信息。
DbNumberExtAttribute Property 数字型数据列的扩展信息。
DbStringExtAttribute Property 文本型数据列的扩展信息。
DbTimeExtAttribute Property 时间型数据列的扩展信息。
DbAllowNullAttribute Property 数据列是否允许为Null值。
DbAutoIncrementAttribute Property 数据列是否自动增长。
DbPrimaryKeyAttribute Property 数据列是否为主键数据列。
DbForeignKeyAttribute Property 数据列是否为外键数据列。
DbGuidAttribute Property (未完成)数据列是否为GUID数据列。
DbIndexAttribute Property 数据表的索引列信息。
DbIndexFieldPropertyAttribute Property 数据表的索引列的扩展属性信息。
DbIgnoreAttribute Property 进行与数据库相关的处理时,将忽略标记的字段与属性。
DbIgnoreInsertAttribute Property 进行Insert处理时,将忽略标记的字段与属性。
DbIgnoreUpdateAttribute Property 进行Update处理时,将忽略标记的字段与属性。
DbEnumNameAttribute Property Entity数据转换为DataTable时,将枚举字段、属性转换为枚举名称的数据列。
DbFieldRouteAttribute Property Entity数据转换为DataTable时,根据路径获取标记属性对象中的实例值。
DbIgnoreConvertionAttribute Property Entity数据转换为DataTable时,将忽略标记的字段与属性。
DbJsonAttribute Property (未完成)Entity数据转换为DataTable时,将字段、属性转换为Json格式的数据列。
DbNameAttribute Class、Struct、Property 名称信息。可应用于以下情况,1.定义DataTable的数据列名称;2.定义DataTable的Table名称;
DbSortAttribute Property 排序信息。可用于Entity转换为DataTable时的DataColumn顺序。

3.3 函数名称说明

注意事项 描述
函数名称包含Entity或Entities 属性数据处理以DbAttribute优先。
函数名称包含Object或Objects 属性数据有多种处理方式。<br/>OnlyProperty方式直接忽略DbAttribute。<br/>OnlyDbAttribute方式只处理有DbAttribute的。<br/>DbAttributeFirst方式以DbAttribute优先。

4.1 创建实例-IDbOperator\IFactoryDbOperator

  1. 创建实例
    
    // MySql, 需要先在项目里引用或加载MySql官方库MySql.Data.dll。
    IDbOperator _dber = new MySqlFactoryOperator()
    {
        ConnectionString = "server=127.0.0.1;database=test;uid=user;pwd=testpassword;Connect Timeout=18000"
    };
    
    // SQLite, 需要先在项目里引用或加载Sqlite官方库System.Data.SQLite.dll。
    IDbOperator _dber = new SQLiteFactoryOperator()
    {
        ConnectionString = "Data Source=.\data\test.db3;"
    };
    
    // Sql Server, 需要先在项目里引用或加载Sql Server官方库System.Data.SqlClient.dll。
    IDbOperator _dber = new MsSqlFactoryOperator()
    {
        ConnectionString = "Server=127.0.0.1;Database=test;Uid = user; Pwd=testpassword;"
    };
    
    
  2. 使用创建全局实例
    
    // MySql
    IDbOperator _dber = Db.Hub.GetDberOrNew<MySqlFactoryOperator>();
    _dber.ConnectionString = "server=127.0.0.1;database=test;uid=user;pwd=testpassword;Connect Timeout=18000";
    
    

4.2 IDbOperator使用

  1. CanConnect(是否能够连接数据库)
    // CanConnect(是否能够连接数据库)
    if (_dber.CanConnect()) { return; }
    
  2. CreateParameter(创建相应的参数)
    // CreateParameter(创建相应的参数)
    return _dber.CreateParameter( "ID", 123);
    
  3. Execute(执行SQL)
    // Execute(执行SQL)
    var _parameters = new List<DbParameter>()
    {
        _dber.CreateParameter("ID","ID123"),
        _dber.CreateParameter("Name","Name123"),
        _dber.CreateParameter("CreateTime",DateTime.Now),
    };
    var _sql = @"insert table1 (ID,Name,CreateTime) values (@ID,@Name,@CreateTime);";
    var _count = _dber.Execute(_sql, _parameters);//直接执行SQL
    //var _count = _dber.Execute(true,_sql, _parameters);// 使用事务执行SQL
    return _count >= 1;
    
  4. Execute(执行多条SQL)
    // Execute(执行多条SQL)
    var _parameters = new List<DbParameter>()
    {
        _dber.CreateParameter("Value1","ID123"),
        _dber.CreateParameter("Value2","Name123"),
        _dber.CreateParameter("Value3","Name1456"),
    };
    var _sqls = new string[]
    {
        @"insert table3 (Values) values (@Value1);",
        @"insert table3 (Values) values (@Value2);",
        @"insert table3 (Values) values (@Value3);",
    } ;
    var _count = _dber.Execute(_sqls, _parameters);//直接执行SQL
    //var _count = _dber.Execute(true, _sqls, _parameters);// 使用事务执行SQL
    return _count >= 1;
    
  5. ExecuteTransaction(使用事务执行指定的SQL)
    // ExecuteTransaction(使用事务执行指定的SQL)
    public void ExecuteTransaction()
    {
        _dber.StartTransaction();
    
         try
         {
            for (int i = 0; i < 3; i++)
            {
                 var _parameters = new List<DbParameter>()
                 {
                     _dber.CreateParameter("ID",i.ToString()),
                     _dber.CreateParameter("Name","Name123"),
                     _dber.CreateParameter("CreateTime",DateTime.Now),
                 };
                 var _sql = @"insert table1 (ID,Name,CreateTime) values (@ID,@Name,@CreateTime);";
                 var _isSucceed = _dber.ExecuteTransaction(_sql, _parameters);
            }
         }
         catch (Exception ex)
         {
             _dber.RollbackTransaction();
             return;
         }
    
         _dber.FinishTransaction();
    }
    
    
  6. ExecuteTransaction(使用指定的事务执行指定的SQL)
    // ExecuteTransaction(使用指定的事务执行指定的SQL)
    public void ExecuteTransaction()
    {
        for (int i = 0; i < 3; i++)
        {
            var _key = "Transaction" + i.ToString();
            _dber.StartTransaction(_key);
    
            var _id = i.ToString() + DateTime.Now.ToLongTimeString();
            try
            {
                var _parameters = new List<DbParameter>()
                {
                    _dber.CreateParameter("ID",_id),
                    _dber.CreateParameter("Name","Name123"),
                    _dber.CreateParameter("CreateTime",DateTime.Now),
                };
                var _sql = @"insert table1 (ID,Name,CreateTime) values (@ID,@Name,@CreateTime);";
                var _isSucceed = _dber.ExecuteTransaction(_key, _sql, _parameters);
            }
            catch (Exception ex)
            {
                _dber.RollbackTransaction(_key);
                return;
            }
    
            if (!_dber.FinishTransaction(_key))
            {
                return;
            }
        }
    }
    
    
  7. RollbackTransaction(回滚指定的事务)
    // RollbackTransaction(回滚指定的事务)
    public void RollbackTransaction()
    {
        var _key = "Transaction";
        _dber.StartTransaction(_key);
    
        var _id = nameof(RollbackTransaction);
    
        try
        {
            var _parameters = new List<DbParameter>()
            {
                _dber.CreateParameter("ID",_id),
                _dber.CreateParameter("Name","Name123"),
                _dber.CreateParameter("CreateTime",DateTime.Now),
            };
            var _sql = @"insert table1 (ID,Name,CreateTime) values (@ID,@Name,@CreateTime);";
            var _isSucceed = _dber.ExecuteTransaction(_key, _sql, _parameters);
        }
        catch (Exception ex)
        {
            _dber.RollbackTransaction(_key);
            return;
        }
    
        _dber.RollbackTransaction(_key);
    }
    
  8. QueryTable(查询数据,并返回DataTable)
    public DataTable QueryTable()
    {
        var _parameters = new List<DbParameter>()
        {
            _dber.CreateParameter("ID",123),
        };
        var _sql = "select * from table1 where ID=@ID;";
        return _dber.QueryTable(_sql, _parameters);
    }
    
  9. QueryTables(查询数据,并返回DataSet)
    public DataSet QueryTables()
    {
        var _parameters = new List<DbParameter>()
        {
            _dber.CreateParameter("ID",123),
        };
        var _sql = "select * from table1 where ID=@ID;";
        _sql += "select * from table2 where ID=@ID;";
        return _dber.QueryTables(_sql, _parameters);
    }
    
  10. QueryReader(查询数据,并返回DbDataReader)
public QueryReader QueryReader()
{
    var _parameters = new List<DbParameter>()
    {
        _dber.CreateParameter("ID",123),
    };
    var _sql = "select * from table1 where ID=@ID;";
    return _dber.QueryReader(_sql, _parameters);
}
  1. QueryDictionary (将查询结果的首两列数据转换为Dictionary)
public Dictionary<string, string> QueryDictionary()
{
    var _sql = "select Key,Value from table1 ;";
    return _dber.QueryDictionary<string, string>(_sql);
}
  1. QueryRowAsDictionary (将查询结果的首行数据转换为Dictionary)

    Dictionary<string, string> _dict =  _dber.QueryRowAsDictionary("select * from table1 ;");
    foreach (var kv in _dict)
    {
        Console.WriteLine($"[{kv.Key}]{kv.Value}");
    }

    // 输出 - 有数据时
    // [Key001]00327d8c-ddcc-46cc-92d1-9f82dca9374e
    // [Column001]阎臭潭
    // [Column002]Thithea Tisasheet
    // [Column003]2023-02-13 15:44:57.304646
    
    // 输出 - 没有数据时
    // [Key001]
    // [Column001]
    // [Column002]
    // [Column003]


  1. QueryValues (将查询结果的首列数据转换为List)
public List<string> QueryNames()
{
    var _sql = "select Names from table1 ;";
    return _dber.QueryValues<string>(_sql);
}
  1. QueryValue (将查询值转换为long)
public List<long> QueryValue()
{
    var _sql = "select count(1) from table1 ;";
    return _dber.QueryScalar<long>(_sql);
}
  1. IsExistData (查询是否存在数据)
public bool IsExistData()
{
    var _sql = "select 1 from table1 where id=1;";
    return _dber.IsExistData(_sql);
}

4.3 IDbSqlTexter使用

  1. 生成实例
    
    // 用于生成MySql语句,引用JCoder.Db4Net.MySql
    IDbSqlTexter _sqltexter = new MySqlTexter();
    
    // 用于生成Sqlite语句,引用JCoder.Db4Net.Sqlite
    IDbSqlTexter _sqltexter = new SqliteTexter();
    
    // 用于生成SqlServer语句,引用JCoder.Db4Net.SqlServer
    IDbSqlTexter _sqltexter = new MsSqlTexter();
    
    
  2. 生成数据库相关的SQL语句
    
    // 生成获取当前数据库信息的SQL语句
    _sqltexter.GetDatabase();
    
     // 生成获取指定数据库信息的SQL语句
    _sqltexter.GetDatabase("database");
    
     // 生成获取数据库信息列表的SQL语句
    _sqltexter.GetDatabaseList();
    
     // 生成创建数据库的SQL语句
    _sqltexter.CreateDatabase("database");
    
     // 生成删除数据库的SQL语句
    _sqltexter.DeleteDatabase("database");
    
    
  3. 生成数据表(Table)相关的SQL语句
    
    // 生成获取当前数据库的指定数据表的数据列信息的SQL语句
    _sqltexter.GetTableColumns("table01");
    // 生成获取指定数据库的指定数据表的数据列信息的SQL语句
    _sqltexter.GetTableColumns("database","table01");
    
     // 生成获取当前数据库的指定数据表信息的SQL语句SQL语句
    _sqltexter.GetTable("table01");
     // 生成获取当前数据库的指定数据表信息的SQL语句SQL语句
    _sqltexter.GetTable("database","table01");
    
     // 生成获取当前数据库的数据表列表的SQL语句
    _sqltexter.GetTableList();
     // 生成获取指定数据库的数据表列表的SQL语句
    _sqltexter.GetTableList("database");
    
     // 生成删除当前数据库的数据表的SQL语句
    _sqltexter.DeleteTable("table01");
     // 生成删除指定数据库的数据表的SQL语句
    _sqltexter.DeleteTable("database","table01");
    
     // 生成创建数据表的SQL语句
    _sqltexter.CreateTable("table01", new List<DbColumn>());
     // 生成创建数据表的SQL语句
    _sqltexter.CreateTable("database", "table01", new List<DbColumn>());
    
    
  4. 生成视图(View)相关的SQL语句
    
    // 生成获取当前数据库的指定View的数据列信息的SQL语句
    _sqltexter.GetViewColumns("view01");
    // 生成获取指定数据库的指定View的数据列信息的SQL语句
    _sqltexter.GetViewColumns("database","view01");
    
     // 生成获取当前数据库的指定View信息的SQL语句SQL语句
    _sqltexter.GetView("view01");
     // 生成获取当前数据库的指定View信息的SQL语句SQL语句
    _sqltexter.GetView("database","view01");
    
     // 生成获取当前数据库的View列表的SQL语句
    _sqltexter.GetViewList();
     // 生成获取指定数据库的View列表的SQL语句
    _sqltexter.GetViewList("database");
    
     // 生成删除当前数据库的View的SQL语句
    _sqltexter.DeleteView("view01");
     // 生成删除指定数据库的View的SQL语句
    _sqltexter.DeleteView("database","view01");
    
    
  5. 生成函数(Function)相关的SQL语句
    
    // 生成获取当前数据库的指定函数信息的SQL语句
    _sqltexter.GetFunction("func01");
    // 生成获取指定数据库的指定函数信息的SQL语句
    _sqltexter.GetFunction("database","func01");
    
     // 生成获取当前数据库的函数列表的SQL语句
    _sqltexter.GetFunctionList();
     // 生成获取指定数据库的函数列表的SQL语句
    _sqltexter.GetFunctionList("database");
    
     // 生成删除当前数据库的Function的SQL语句
    _sqltexter.DeleteFunction("func01");
     // 生成删除指定数据库指定函数的SQL语句
    _sqltexter.DeleteFunction("database","func01");
    
    
  6. 生成存储过程(Procedure)相关的SQL语句
    
    // 生成获取当前数据库的指定存储过程信息的SQL语句
    _sqltexter.GetProcedure("proc01");
    // 生成获取指定数据库的指定存储过程信息的SQL语句
    _sqltexter.GetProcedure("database","proc01");
    
     // 生成获取当前数据库的存储过程列表的SQL语句
    _sqltexter.GetProcedureList();
     // 生成获取指定数据库的存储过程列表的SQL语句
    _sqltexter.GetProcedureList("database");
    
     // 生成删除当前数据库的Procedure的SQL语句
    _sqltexter.DeleteProcedure("proc01");
     // 生成删除指定数据库指定存储过程的SQL语句
    _sqltexter.DeleteProcedure("database","proc01");
    
    

4.4 扩展使用

1)ToEntity1st(查询数据,并将首条数据转换为数据实例)
// ToEntity1st(查询数据,并将首条数据转换为数据实例)
using JCoder.Db4Net;
using JCoder.Db4Net;

IDbOperator _operator = new MsSqlFactoryOperator()
{
    ConnectionString = "Server=127.0.0.1;Database=test;Uid = user; Pwd=testpassword;"
};
return this.Operator.ToEntity1st<T>("select top 1 * from table1;");
2)ToEntities(查询数据,并转换为数据实体列表)
// ToEntities(查询数据,并转换为数据实体列表)
using JCoder.Db4Net;

IDbOperator _operator = new MsSqlFactoryOperator()
{
    ConnectionString = "Server=127.0.0.1;Database=test;Uid = user; Pwd=testpassword;"
};
return this.Operator.ToEntities<T>("select top 10 * from table1;");
3)CRUD(增删查改)
using JCoder.Db4Net;

// Test Entity Class
[DbTable("t_table_test")]
 public class TestEntity
 {
     [DbPrimaryKey]
     [DbAutoIncrement]
     public int Key001 { get; set; }
     [DbPrimaryKey]
     [DbField(IsUnique=true)]
     public string Key002 { get; set; }

     [DbIndex("idx01")]
     public string C003 { get; set; }
 }

IDbOperator _operator = new MsSqlFactoryOperator()
{
    ConnectionString = "Server=127.0.0.1;Database=test;Uid = user; Pwd=testpassword;"
};

TestEntity x = new TestEntity()
{
    Key001 = 101,
    Key002 = "Test PK",
};

// Insert Data(新增数据)
this.Operator.Insert<TestEntity>(x);

// Update Data(更新数据)
x.C003 = "Update Test";
this.Operator.Update<TestEntity>(x);

// Delete Data(删除数据)
this.Operator.Delete<TestEntity>(x);

// Select Data(查询数据)
this.Operator.Select1st<TEntity>(" and Key001=101 and Key002-'Test PK'");
4)Create\Drop Table From Entity(由实体类创建\删除数据库的数据表)
using JCoder.Db4Net;

// Test Entity Class
 [DbTable("t_table_test")]
 public class TestEntity
 {
     [DbPrimaryKey]
     [DbAutoIncrement]
     public int Key001 { get; set; }
     [DbPrimaryKey]
     [DbField(IsUnique =true)]
     public string Key002 { get; set; }
     [DbIgnore]
     public string Key003 { get; set; }

     [DbIndex]
     [DbField(AllowNull = false, Default = "", Description = "Test Comment")]
     public string C001 { get; set; }
     [DbIndex("idx01")]
     public string C002 { get; set; }
     [DbIndex("idx01")]
     public string C003 { get; set; }

     [DbIgnore]
     public int C004 { get; set; }
     [DbField(AllowNull = false, Default = "0", Description = "Test Comment2")]
     public int C005 { get; set; }
     [DbNumberFieldExt(Precision = 11, Scale = 2)]
     public double C006 { get; set; }

     [DbField(AllowNull = false, Default = "", Description = "Test Comment2")]
     public string C007 { get; set; }
     public string C008 { get; set; }
     [DbTimeFieldExt(UpdateTimeOnCreate = true, UpdateTimeOnUpdate = true)]
     public DateTime C009 { get; set; }
 }

IDbOperator _operator = new MsSqlFactoryOperator()
{
    ConnectionString = "Server=127.0.0.1;Database=test;Uid = user; Pwd=testpassword;"
};

this.Operator.CreateTable<TestEntity>();
this.Operator.DropTable<TestEntity>();
   

3.5 Orm工具类使用

1)转换首条数据为实体对象(支持DbDataReader、DataTable)
using JCoder.Db4Net;

// data source
var _table = new DataTable();
// Property only.
var _result = JCoder.Db4Net.Orm.ToObject1st<T>(_table);
var _result = _table.ToObject1st<T>();
// Attributes first
var _result = JCoder.Db4Net.Orm.ToEntity1st<T>(_table);
var _result = _table.ToEntity1st<T>();

2)将数据转换为实体对象(支持DataRow、IDictionary<string, string>、IDictionary<string, object>))
using JCoder.Db4Net;

// data source
var _dict = new Dictionary<string, string>();   
// Attributes first
var _result = JCoder.Db4Net.Orm.ToEntity<T>(_dict);  
var _result = _dict.ToEntity<T>();     
// Property only.
var _result = JCoder.Db4Net.Orm.ToObject<T>(_dict);
var _result = _dict.ToObject<T>();

3)将数据源转换为实体对象列表(支持DbDataReader、DataTable)
using JCoder.Db4Net;

// data source
var _table = new DataTable();   
// Attributes first
var _result = JCoder.Db4Net.Orm.ToEntities<T>(_table);
var _result = _table.ToEntities<T>();   
// Property only.
var _result = JCoder.Db4Net.Orm.ToObjects<T>(_table);
var _result = _table.ToObjects<T>();

4)将数据源转换为实体对象字典(支持DbDataReader、DataTable)
using JCoder.Db4Net;

// data source
var _table = new DataTable();
_table.Columns.Add(new DataColumn("ID", typeof(string)));

// Property only.
var _result = JCoder.Db4Net.Orm.ToObjectDict<T>(_table, "ID");
var _result = _table.ToObjectDict<T>("ID");
// Attributes first
var _result = JCoder.Db4Net.Orm.ToEntityDict<T>(_table, "ID");
var _result = _table.ToEntityDict<T>("ID");

5)将数据填充到已有的实体对象里(支持DbDataReader、DataRow、IDictionary<string, string>、IDictionary<string, object>)
using JCoder.Db4Net;

var _target = new T();

// data source
var _source = new DbDataReader();   
// Property only.
var _result = JCoder.Db4Net.Orm.FillInObject<T>(_source, _target);
var _result = _source.FillInObject<T>(_target);
// Attributes first
var _result = JCoder.Db4Net.Orm.FillInEntity<T>(_source, _target);
var _result = _source.FillInEntity<T>(_target);

6)将实体对象转换为DataTable
using JCoder.Db4Net;

var _list = new List<T>();
var t = new T();

// Property only.
// 实体对象转换为DataTable
DataTable _table = JCoder.Db4Net.Orm.ObjectToDataTable(t);
DataTable _table = t.ObjectToDataTable();
// 实体对象列表转换为DataTable
DataTable _table = JCoder.Db4Net.Orm.ObjectsToDataTable(_list);
DataTable _table = _list.ObjectsToDataTable();

// Attributes first
// 实体对象转换为DataTable
DataTable _table = JCoder.Db4Net.Orm.EntityToDataTable(t);
DataTable _table = t.EntityToDataTable();
// 实体对象列表转换为DataTable
DataTable _table = JCoder.Db4Net.Orm.EntitiesToDataTable(_list);
DataTable _table = _list.EntitiesToDataTable();

7)将List转换为DataTable
using JCoder.Db4Net;

// 将List转换为DataTable(使用特性进行排序,自定义名称等)
public class BoTest
{
     [DbSubField("Width", "S001-Width", Sort = 2120)]//获取Size对象里的Width属性值,并显示为"S001-Width"
     [DbSubField("Height", "S001-Height", Sort = 2121)]//获取Size对象里的Height属性值,并显示为"S001-Height"
     public Size S001 { get; set; }
     public string Key { get; set; }
     [DbEnumName]//枚举显示为文本
     public AttributeTargets AT { get; set; }
     public AttributeTargets AT2 { get; set; }
     [DbSort(100)]
     public string P100 { get; set; }
     [DbSort(111)]//设置排序值(从小到大依次排序)
     public string P111 { get; set; }
     public DateTime CreateTime { get; set; }
     [DbIgnoreConvertion]//转换时忽略
     public int Z001 { get; set; }
}

 var _list = new List<BoTest>();
 for (int i = 0; i < 100; i++)
 {
     _list.Add(new()
     {
         S001 = new(i * 100, i * 1000),
         Key = $"Key_{i}",
         P100 = $"P100_{i}",
         P111 = $"P111_{i}",
         CreateTime = DateTime.Now,
         Z001 = i,
         AT = AttributeTargets.Interface,
         AT2 = AttributeTargets.Delegate,
     });
 }
 var _table = JCoder.Db4Net.Orm.EntitiesToDataTable(_list);
8)将实体对象转换为Dictionary<string,string>
using JCoder.Db4Net;

var t = new T();

// Property only.
// 实体对象转换为Dictionary<string,string>
Dictionary<string, string> _dict = JCoder.Db4Net.Orm.ObjectToDict(t);
Dictionary<string, string> _dict = t.ObjectToDict();

// Attributes first
// 实体对象转换为Dictionary<string,string>
Dictionary<string, string> _dict = JCoder.Db4Net.Orm.EntityToDict(t);
Dictionary<string, string> _dict = t.EntityToDict();


3.6 类Linq方式

1) Query查询数据
using JCoder.Db4Net;

IDbOperator _dber = Db.Hub.GetDberOrNew<MySqlFactoryOperator>();
var _exper = _dber.NewQuery<BoTest001>();
_exper.UseParameter = true; // 是否使用参数化执行Sql(Using sql parameters for sql execution).

_exper.Where(x => x.Key=="ABC"); // 设置筛选条件,Key="ABC"
_exper.Where(x => x.Key=="ABC" || x.Int001 > 5); // 设置筛选条件,Key="ABC" 或者 Int001>5
_exper.Where(x => x.Key=="ABC" && x.Int001 > 5); // 设置筛选条件,Key="ABC" 并且 Int001>5
_exper.Where(x => x.Key=="ABC")
      .Where(x => x.Int001 > 5); // 设置筛选条件,Key="ABC" 并且 Int001>5

_exper.OrderBy(x => x.Key); // 设置排序,以Key排序
_exper.OrderByDesc(x => x.Key); // 设置排序,以Key倒序
_exper.OrderBy(x => x.Bool001)
      .OrderByDesc(x => x.Key); // 设置排序,Bool001为第一排序,Key为第二排序(倒序)
      
_exper.Limited(5); // 设置只获取前5条数据
_exper.Limited(20,5); // 设置获取从第20条起的5条数据
_exper.Paging(5,10); // 设置获取第5页数据(10条数据为1页,起始页码为1)

_exper.SetColumn(x => x.AT); // 设置只查询AT数据列
_exper.SetColumn("Column001", x => x.CreateTime); // 设置只查询CreateTime数据列,并为之重命名为Column001

// 执行SQL并返回结果
DbResultTable _result = _exper.RunForTableR();
DataTable _result = _exper.RunForTable();
List<BoTest001> _result = _exper.RunForEntities();
BoTest001 _result = _exper.RunForEntity1st();
DbResult<bool> _result = _exper.RunForExistR();
bool _result = _exper.RunForExist();

2) Delete删除数据
using JCoder.Db4Net;

IDbOperator _dber = Db.Hub.GetDberOrNew<MySqlFactoryOperator>();
var _exper = _dber.NewDelete<BoTest001>();
_exper.UseParameter = true; // 是否使用参数化执行Sql(Using sql parameters for sql execution).

_exper.Where(x => x.Key=="ABC"); // 设置筛选条件,Key="ABC"
_exper.Where(x => x.Key=="ABC" || x.Int001 > 5); // 设置筛选条件,Key="ABC" 或者 Int001>5
_exper.Where(x => x.Key=="ABC" && x.Int001 > 5); // 设置筛选条件,Key="ABC" 并且 Int001>5
_exper.Where(x => x.Key=="ABC")
      .Where(x => x.Int001 > 5); // 设置筛选条件,Key="ABC" 并且 Int001>5

_exper.OrderBy(x => x.Key); // 设置排序,以Key排序
_exper.OrderByDesc(x => x.Key); // 设置排序,以Key倒序
_exper.OrderBy(x => x.Bool001)
      .OrderByDesc(x => x.Key); // 设置排序,Bool001为第一排序,Key为第二排序(倒序)
      
_exper.Limited(5); // 设置只删除前5条数据

// 执行SQL并返回结果
DbResultCount _result = _exper.RunR();
int _result = _exper.Run();

3) Update更新数据
using JCoder.Db4Net;

IDbOperator _dber = Db.Hub.GetDberOrNew<MySqlFactoryOperator>();
var _exper = _dber.NewUpdate<BoTest001>();
_exper.UseParameter = true; // 是否使用参数化执行Sql(Using sql parameters for sql execution).

_exper.Where(x => x.Key=="ABC"); // 设置筛选条件,Key="ABC"
_exper.Where(x => x.Key=="ABC" || x.Int001 > 5); // 设置筛选条件,Key="ABC" 或者 Int001>5
_exper.Where(x => x.Key=="ABC" && x.Int001 > 5); // 设置筛选条件,Key="ABC" 并且 Int001>5
_exper.Where(x => x.Key=="ABC")
      .Where(x => x.Int001 > 5); // 设置筛选条件,Key="ABC" 并且 Int001>5

_exper.OrderBy(x => x.Key); // 设置排序,以Key排序
_exper.OrderByDesc(x => x.Key); // 设置排序,以Key倒序
_exper.OrderBy(x => x.Bool001)
      .OrderByDesc(x => x.Key); // 设置排序,Bool001为第一排序,Key为第二排序(倒序)
      
_exper.Limited(5); // 设置只删除前5条数据

_exper.Update(x => x.Key=="ABC"); // 更新数据列Key的值为ABC
_exper.Update(x => x.Key=="ABC" && x.Int001 == 5); // 更新数据列Key的值为ABC,数据列Int001的值为5
_exper.Update(x => x.CreateTime, DateTime.Now); // 更新数据列CreateTime的值为当前时间

// 执行SQL并返回结果
DbResultCount _result = _exper.RunR();
int _result = _exper.Run();

实例使用

  1. 参考JCoder.Db4Net
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 is compatible.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 is compatible.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.1 is compatible. 
.NET Framework net472 is compatible.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.
  • .NETFramework 4.7.2

  • .NETStandard 2.1

  • net6.0

  • net7.0

  • net8.0

NuGet packages (6)

Showing the top 5 NuGet packages that depend on JCoder.Db4Net:

Package Downloads
JCoder.Db4Net.Sqlite

A library based on JCoder.Db4Net and it support Sqlite. (一个基于JCoder.Db4Net且支持Sqlite的库)

JCoder.Db4Net.ORM

A ORM library based on the JCoder.Db4Net. (基于JCoder.Db4Net的ORM库)

JCoder.Db4Net.MySql

A library based on JCoder.Db4Net and it support Mysql.(一个基于JCoder.Db4Net且支持Mysql的库)

JCoder.Db4Net.SqlServer

A library based on JCoder.Db4Net and it support microsoft sql server. (一个基于JCoder.Db4Net且支持sql server的库)

JCoder.Extension.SqliteAssistKitsts

一款使用Sqlite进行辅助的工具库。

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
3.5.3 156 1/3/2024
3.5.2 234 12/3/2023
3.5.1 219 10/5/2023
3.4.2 210 10/2/2023
2.26.2304.308 199 4/3/2023
2.21.4 1,515 11/8/2022
2.20.12 1,159 11/4/2022
2.19.5 1,423 10/14/2022
2.18.6 1,397 10/13/2022
2.13.3 1,386 6/5/2022
2.12.3 1,442 5/25/2022
2.11.4 1,473 5/13/2022

Please contact Jackie by QQ 1378071853 or E-mail JackieLaw-dev@outlook.com;