ZayniFramework.DataAccess 2.2.1

DataAccess & ORM example.

Just like using Dapper.

• Support Microsoft SQL Server, MySQL and Oracle database with same Dao class.
• Support SQL log (input SQL statement & output results).
• Support ORM.
• Support dynamic ORM.
• Support DataContext CURD template.
• Support database schema migration CLI tool.
• NO MORE Entity Framework!!!

Add config in your app.config or web.config file.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <section name="ZayniFramework" type="ZayniFramework.Common.ZayniConfigSection, ZayniFramework.Common"/>
    </configSections>
    <ZayniFramework>
        <LoggingSettings eventLogEnable="false" logEnable="true">
            <DefaultTextLogger>
                <add name="_DefaultLog" enable="true" maxSize="8">
                    <!-- ZayniFramework's default internal logger. -->
                    <LogFile path="D:\Logs\Zayni\Tracing.log"/>
                    <Filter filterType="deny" category="Information,Warning"/>
                </add>
            </DefaultTextLogger>
        </LoggingSettings>
        <DataAccessSettings>
            <DatabaseProviders>
                <add name="Zayni" dataBaseProvider="MSSQL" sqlLogEnable="true"/>
                <add name="ZayniUnitTest" dataBaseProvider="MSSQL" sqlLogEnable="true"/>
            </DatabaseProviders>
        </DataAccessSettings>
    </ZayniFramework>
    <connectionStrings>
        <!--MSSQL-->
        <add name="Zayni" connectionString="Server=XXX;Database=XXX;User Id=XXX;Password=XXX;Max Pool Size=20;Min Pool Size=5;Connection Lifetime=30;"
             providerName="System.Data.SqlClient" />
        <add name="ZayniUnitTest" connectionString="Server=XXX;Database=XXX;User Id=XXX;Password=XXX;Max Pool Size=20;Min Pool Size=5;Connection Lifetime=30;"
             providerName="System.Data.SqlClient" />

        <!--MySQL-->
        <!--<add name="Zayni" connectionString="SERVER=XXX;DATABASE=XXX;UID=XXX;PASSWORD=XXX;Allow User Variables=True;Charset=utf8;Convert Zero Datetime=True;"
             providerName="MySql.Data.MySqlClient"/>
        <add name="ZayniUnitTest" connectionString="SERVER=XXX;DATABASE=XXX;UID=XXX;PASSWORD=XXX;Allow User Variables=True;Charset=utf8;Convert Zero Datetime=True;"
             providerName="MySql.Data.MySqlClient"/>-->
    </connectionStrings>
</configuration>

Add using.

using ZayniFramework.Common;
using ZayniFramework.DataAccess;

Write your Dao (Data Access Object) class and extends the BaseDataAccess class.

/// <summary>Your Data Access Object (Dao) class
/// </summary>
internal class UserDao : BaseDataAccess

Select data from database using BaseDataAccess.LoadDataToModel() method.

public Result<List<UserModel>> Select_LoadDataToModel_Example( UserModel query )
{
    var result = new Result<List<UserModel>>()
    {
        IsSuccess = false,
        Data      = null
    };

    string sql = @"SELECT AccountId
                        , Name
                        , Age
                        , Sex
                        , Birthday
                        , IsVIP
                        , IsGood
                        , DataFlag
                    FROM fs_unit_test_user
                        WHERE Sex = @Sex ";

    DbCommand cmd = base.GetSqlStringCommand( sql );
    base.AddInParameter( cmd, "@Sex", DbTypeCode.Int32, query.Sex );

    if ( !base.LoadDataToModel( cmd, out List<UserModel> models ) )
    {
        result.Message = base.Message;
        return result;
    }

    result.Data      = models;
    result.IsSuccess = true;
    return result;
}

Multi-select SQL statements from database by using BaseDataAccess.LoadDataToModels() method.

public Result Select_LoadDataToModels( UserModel query )
{
    dynamic result = new Result()
    {
        IsSuccess = false,
        Message   = null
    };

    string sql = @" -- QueryNo1
                    SELECT AccountId AS Account
                        , Name
                        , Age
                        , Sex
                        , Birthday AS DoB
                        , IsVIP
                    FROM fs_unit_test_user
                        WHERE Sex = @Sex;

                    -- QueryNo2
                    SELECT AccountId AS Account
                        , Name
                        , Age
                        , Sex
                        , Birthday AS DoB
                    FROM fs_unit_test_user
                        WHERE Sex = @Sex; ";

    DbCommand cmd = base.GetSqlStringCommand( sql );
    base.AddInParameter( cmd, "@Sex", DbTypeCode.Int32, query.Sex );
    
    Type[] types = { typeof ( UserModel ), typeof ( UserModel ) };

    if ( !base.LoadDataToModels( cmd, types, out List<object[]> datas ) )
    {
        return result;
    }

    List<UserModel> queryResult1 = datas.FirstOrDefault().ToList<UserModel>();
    List<UserModel> queryResult2 = datas.LastOrDefault().ToList<UserModel>();

    result.Rst1      = queryResult1;
    result.Rst2      = queryResult2;
    result.IsSuccess = true;
    return result;
}

Dynamic ORM query using BaseDataAccess.LoadDataToDynamicModel() method. You don't need to define a entity class anymore.

public Result<List<dynamic>> Select_LoadDataToDynamicModel_Example( UserModel query )
{
    var result = new Result<List<dynamic>>()
    {
        IsSuccess = false,
        Data      = null
    };

    string sql = @"SELECT AccountId AS Account
                        , Name
                        , Age
                        , Sex
                        , Birthday AS DoB
                        , IsVIP
                    FROM fs_unit_test_user
                        WHERE Sex = @Sex ";

    DbCommand cmd = base.GetSqlStringCommand( sql );
    base.AddInParameter( cmd, "@Sex", DbTypeCode.Int32, query.Sex );

    List<dynamic> models;

    if ( !base.LoadDataToDynamicModel( cmd, out models ) )
    {
        result.Message = base.Message;
        return result;
    }

    result.Data      = models;
    result.IsSuccess = true;
    return result;
}

For more BaseDataAccess examples see here.