Base.DBRepository 1.7.0

What is the Base.DBRepository

The Base.DBRepository is an ORM,a database repository,and compared to other ORM, it is simple, easy to use, efficient, and takes up less resources。

The update of this package will make the connection pool less occupied。And directly use entity addition, deletion, modification, and query, and use stored procedures to query data sets

The required configuration files for this package are as follows：

The value of “DataBaseType” can be “MySql”、“SqlServer”、“Oracle”、"SQLite"、"ODBC"、"OleDb"、"DM" or “PostgreSQL” 。

How to use it

1.Reference Package Reference Package

using Base.DBRepository;

2. Configure default database access link string

AppSettings.json：

"DataBase":{ "ConnectionString":"server=.......;", "DataBaseType":"MySql" }

If a database is used in the project, the package of which database needs to be referenced。

[Note: The field values of entity markers [Ignore] will not be inserted into the database]

ODBC nuget package: System. Data. Odbc nuget package uses version 6.0.2 。

SQLite needs to add 2 packages: System.Data.SQLite package and SourceGear.sqlite3 package。

DM 达梦 needs to reference the official nuget package (DM.DmProvider.8.3.1.42930.nupkg)。

The Base.DBRepository already supports ODBC access to KingbaseES 人大金仓。

3.Directly call the api of the Base.DBRepository

for example：

entity：

[TableName("users")]                   //Configuration Table Name
public class users
{
    [Key]                                       //Configuration Primary key
    public string Id { get; set; }
    public string UserName { get; set; }
    public DateTime? createtime { get; set; }
    [Ignore]                                                   //Configuration Ignore Field
    public decimal Scores { get; set; }
}

query data:

           List<users> list = RepositoryCore.QueryList<users>("select * from users ");

           users u = RepositoryCore.GetModelEntity<users>("b923149b-6aae-48fd-b7bc-83cfe489725e");

           users u = RepositoryCore.GetModelEntity<users>("Users", "Id", "b923149b-6aae-48fd-b7bc-83cfe489725e");

Insert Table Data Using Entities：

        users u = new users()
        {
            Id = Guid.NewGuid().ToString(),
            UserName = "yanzi",
            Age = 26,
            createtime = DateTime.Now
        };
        int result = RepositoryCore.AddEntity<users>(u);
        or
        int result = RepositoryCore.AddEntity<users>(u, "Users");

Use of transactions:

        using (IDbTransaction tran = RepositoryCore.CreateDataTran())  //Create  Transaction
        {
            try
            {
                string sql = @"insert into [TestDB].[dbo].[Users](Id,Age,Sex)values
                ('C5555BAF-64DE-472B-AF11-489782E86FE1',15,1)";
                RepositoryCore.ExecuteSql(sql, null, tran);             //use Transaction  tran
                //Other operations for changing data
                ............
                tran.Commit();                   //commit Transaction
            }
            catch (Exception ex)
            {
                tran.Rollback();                 //rollback Transaction
            }
        }

Using Stored Procedures：

        SqlParameter[] parameters = new SqlParameter[]
        {
           new SqlParameter("@ID", ID),
           new SqlParameter("@Name", Name),
           new SqlParameter("@CreateDate_Start", CreateDate_Start),
           new SqlParameter("@CreateDate_End", CreateDate_End)
        };
        List<Test_Entity> list = RepositoryCore.QueryListStoredProc<Test_Entity>("SP_Test_Entity", parameters);

Base. DBRepository supports accessing multiple different databases within the same project, such as:

        RepositoryCore.ResetRepository("Server = .;Database = TestDB;User ID = sa;Password = 123456;Trusted_Connection = False;", "SqlServer");  //Accessing SQL server
        RepositoryCore.QueryList<users>("select * from users1 where Age>@Age ", parameters.ToArray()); //The obtained data comes from the SQL server database

        RepositoryCore.ResetRepository("Server = .;Database = TestDB;User ID = sa;Password = 123456;Trusted_Connection = False;", "Mysql"); //Accessing Mysql
        RepositoryCore.QueryList<users>("select * from users2 where Age>@Age ", parameters.ToArray());//The obtained data comes from the Mysql database

        RepositoryCore.ResetRepository(); //Reset default repository (access database link to configuration file)
        RepositoryCore.QueryList<users>("select * from users3 where Age>@Age ", parameters.ToArray());//The obtained data comes from the default configuration  database

More methods are as follows

4. More API

    /// <summary>
    /// insert entity data
    /// </summary>
    /// <typeparam name="T">T</typeparam>
    /// <param name="entity">entity</param>
    /// <param name="tran">Transaction</param>
    /// <returns></returns>
    int AddEntity<T>(T entity, IDbTransaction tran = null) ;
   
   
    /// <summary>
    /// insert entity data
    /// </summary>
    /// <typeparam name="T">T</typeparam>
    /// <param name="entity">entity</param>
    /// <param name="tableName">table Name</param>
    /// <param name="tran">Transaction</param>
    /// <returns></returns>
    int AddEntity<T>(T entity, string tableName, IDbTransaction tran = null) ;
   
   
    /// <summary>
    /// update entity data
    /// </summary>
    /// <typeparam name="T">T</typeparam>
    /// <param name="entity">entity</param>
    /// <param name="tran">Transaction</param>
    /// <returns></returns>
    int UpdateEntity<T>(T entity, IDbTransaction tran = null) ;
   
   
    /// <summary>
    /// update entity data
    /// </summary>
    /// <typeparam name="T">T</typeparam>
    /// <param name="entity">entity</param>
    /// <param name="tableName">table Name</param>
    /// <param name="tran">Transaction</param>
    /// <returns></returns>
    int UpdateEntity<T>(T entity, string tableName, string primaryKey, IDbTransaction tran = null) ;
   
   
    /// <summary>
    /// delete data
    /// </summary>
    /// <param name="T">T</param>
    /// <param name="value">value</param>
    /// <param name="tran">Transaction</param>
    /// <returns></returns>
    int DeleteEntity<T>(object value, IDbTransaction tran = null);
   
   
    /// <summary>
    /// delete data
    /// </summary>
    /// <param name="tableName">table Name</param>
    /// <param name="primaryKey">primary Key</param>
    /// <param name="value">value</param>
    /// <param name="tran">Transaction</param>
    /// <returns></returns>
    int DeleteEntity(string tableName, string primaryKey, object value, IDbTransaction tran = null);
   
   
    /// <summary>
    /// get entity
    /// </summary>
    /// <param name="T">T</param>
    /// <param name="value">value</param>
    /// <returns></returns>
    T GetModelEntity<T>(object value) ;
   
   
    /// <summary>
    /// get entity
    /// </summary>
    /// <param name="tableName">table Name</param>
    /// <param name="primaryKey">primary Key</param>
    /// <param name="value">value</param>
    /// <returns></returns>
    T GetModelEntity<T>(string tableName, string primaryKey, object value) ;
   
   
    /// <summary>
    /// Execution of additions, deletions, and modifications, returning the number of affected records
    /// </summary>
    /// <param name="sql">sql statement</param>
    /// <param name="parameters">parameters</param>
    /// <param name="tran">Transaction</param>
    int ExecuteSql(string sql, IDataParameter[] parameters = null, IDbTransaction tran = null);
   
   
    /// <summary>
    /// Query entity data
    /// </summary>
    /// <typeparam name="T">entity</typeparam>
    /// <param name="sql">sql statement</param>
    /// <param name="parameters">parameters</param>
    /// <returns></returns> 
    T Query<T>(string sql, IDataParameter[] parameters = null) ;
   
   
    /// <summary>
    ///  Query Entity Collection
    /// </summary>
    /// <typeparam name="T">entity</typeparam>
    /// <param name="sql">sql statement</param>
    /// <param name="parameters">parameters</param>      
    /// <returns> Entity Collection</returns>
    List<T> QueryList<T>(string sql, IDataParameter[] parameters = null) ;
   
   
    /// <summary>
    /// Query object Collection
    /// </summary>
    /// <typeparam name="T">entity</typeparam>
    /// <param name="sql">sql statement</param>
    /// <param name="parameters">parameters</param>
    /// <returns></returns>
    List<object> QueryList(string sql, Type type, IDataParameter[] parameters = null);
   
   
    /// <summary>
    /// Query individual data
    /// </summary>
    /// <param name="sql">sql statement</param>
    /// <param name="parameters">parameters</param>
    /// <returns></returns>
    object QueryScalar(string sql, IDataParameter[] parameters = null);
   
   
    /// <summary>
    /// Execute stored procedures
    /// </summary>
    /// <param name="procName">Stored Procedure Name</param>
    /// <param name="parameters">parameters</param>
    /// <param name="tran">Transaction</param>
    int ExecuteStoredProc(string procName, IDataParameter[] parameters = null, IDbTransaction tran = null);
   
   
    /// <summary>
    /// Execute stored procedures query
    /// </summary>
    /// <param name="procName">Stored Procedure Name</param>
    /// <param name="parameters">parameters</param>
    /// <param name="tran">Transaction</param>      
    IDataReader QueryStoredProc(string procName, IDataParameter[] parameters = null, IDbTransaction tran = null);
   
   
    /// <summary>
    /// Execute stored procedures query
    /// </summary>
    /// <param name="procName">Stored Procedure Name</param>
    /// <param name="parameters">parameters</param>
    /// <param name="tran">Transaction</param>      
    List<T> QueryListStoredProc<T>(string procName, IDataParameter[] parameters = null, IDbTransaction tran = null);
   
   
    /// <summary>
    /// Create Database transaction
    /// </summary>        
    IDbTransaction CreateDataTran();
   

5.Error handling

1>. Query data error: Object must implement IConvertible

reason: Caused by inconsistency between entity and database types

solution:

Data type correction or conversion between entity and library types is sufficient, such as changing some public string id {get; set;} to="public Guid id {get; set;}

2>. Using AddEntity reported an error: System.Data.SqlClient.SqlException: 'The conversion from varchar data type to datetime data type resulted in a value that is out of range

reason: There may be an unassigned datatype for datetime in the entity

solution:

Assigning a value to an unassigned datetime data type field, or commenting on an unassigned datetime data type field

More Others ...