Base.Net.DBRepository 1.2.0

The Base.Net.DBRepository is an ORM. The Base.Net.DBRepository already supports. net4.6 and above versions.The Base.DBRepository supports the following databases . For example, MySql、SqlServer、Oracle、OleDb、PostgreSQL. If a database is used in the project, the package of which database needs to be referenced。

How to use it

<appSettings>
	<add key="ConnectionString" value="Server = .;Database = TestDB;User ID = sa;Password = 123456;Trusted_Connection = False;" />
    <add key="DataBaseType" value="SqlServer"/>
</appSettings>

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]

for example：

using Base.Net.DBRepository;

query data:

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

           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, "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. Net.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

More API

    /// <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="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="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="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();

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