ZayniFramework.DataAccess 2.0.0

Package Description

There is a newer version of this package available.
See the version list below for details.
Install-Package ZayniFramework.DataAccess -Version 2.0.0
dotnet add package ZayniFramework.DataAccess --version 2.0.0
<PackageReference Include="ZayniFramework.DataAccess" Version="2.0.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add ZayniFramework.DataAccess --version 2.0.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#r "nuget: ZayniFramework.DataAccess, 2.0.0"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// Install ZayniFramework.DataAccess as a Cake Addin
#addin nuget:?package=ZayniFramework.DataAccess&version=2.0.0

// Install ZayniFramework.DataAccess as a Cake Tool
#tool nuget:?package=ZayniFramework.DataAccess&version=2.0.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.

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.

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.

NuGet packages (4)

Showing the top 4 NuGet packages that depend on ZayniFramework.DataAccess:

Package Downloads
ZayniFramework.Middle.Service
The Middle.Service module provide a self-hosted .NET middleware framework to separate the communication protocol detail with your business logic code. You can use it to build the backend microservices easier. Support TCPSocket, WebSocket and RabbitMQ protocol. You can also implement the IRemoteHost interface and register it into the Middle.Service module as an extension module for this middleware framework. Have a nice day... :)
ZayniFramework.Middle.Service.Client
The Middle.Service.Client is the client SDK for Middle.Service module. You can use it to connect, send RPC request or publish message to the server side's service host very easy. Support TCPSocket, WebSocket and RabbitMQ protocol.
ZayniFramework.Caching
This is a cache management for .NET application. You can store you data in in-process memory, Redis server, MySQL memory table engine or Zayni Framework's build-in Remote.ShareData.Service.
ZayniFramework.DataAccess.LightORM
This is a lightweight CRUD ORM template for .NET data access application. You can use the DataContext to execute Insert, Update, Delete and Select action to database.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version History

Version Downloads Last updated
2.31.120 101 3/14/2021
2.30.115 273 3/6/2021
2.30.114 38 3/6/2021
2.20.101 119 3/1/2021
2.19.3 95 2/11/2021
2.19.2 1,625 2/6/2021
2.19.1 171 1/6/2021
2.19.0 162 1/1/2021
2.18.3 156 12/27/2020
2.18.2 913 8/29/2020
2.18.1 4,105 8/26/2020
2.18.0 4,419 8/20/2020
2.17.135 141 8/19/2020
2.17.134 216 7/28/2020
2.17.133 220 7/27/2020
2.17.132 235 7/18/2020
2.17.131 406 7/11/2020
2.16.130 390 6/13/2020
2.15.128 284 6/3/2020
2.15.127 352 5/31/2020
2.15.126 1,156 4/30/2020
2.14.122 285 4/13/2020
2.13.100 401 3/12/2020
2.12.51 409 2/18/2020
2.11.50 311 2/10/2020
2.10.44 444 1/30/2020
2.9.43 606 1/11/2020
2.8.42 415 1/10/2020
2.8.41 377 1/5/2020
2.7.40 411 1/2/2020
2.7.39 326 1/2/2020
2.7.38 482 1/1/2020
2.6.37 387 12/23/2019
2.6.35 572 12/4/2019
2.6.1 332 12/2/2019
2.6.0 337 11/28/2019
2.5.2 496 11/26/2019
2.5.1 428 11/12/2019
2.5.0 333 11/9/2019
2.4.3 483 10/16/2019
2.4.2 305 10/16/2019
2.4.1 400 9/20/2019
2.3.113 39 3/6/2021
2.3.112 45 3/6/2021
2.3.28 386 9/19/2019
2.3.27 488 8/30/2019
2.3.26 469 8/20/2019
2.3.25 395 8/12/2019
2.3.22 334 7/31/2019
2.3.21 487 7/20/2019
2.3.20 441 6/22/2019
2.3.19 403 6/14/2019
2.3.18 388 6/13/2019
2.3.17 414 6/13/2019
2.3.15 430 6/8/2019
2.3.14 408 6/8/2019
2.3.13 424 5/30/2019
2.3.12 392 5/24/2019
2.3.11 380 5/24/2019
2.3.10 424 5/21/2019
2.3.9 413 5/9/2019
2.3.8 433 5/8/2019
2.3.7 441 4/30/2019
2.3.6 517 4/23/2019
2.3.5 434 4/19/2019
2.3.4 418 4/18/2019
2.3.3 403 4/17/2019
2.3.2 436 4/6/2019
2.3.1 498 12/15/2018
2.3.0 542 12/7/2018
2.2.6 600 11/25/2018
2.2.1 842 11/17/2018
2.2.0 460 11/16/2018
2.1.0 479 11/15/2018
2.0.1 449 11/6/2018
2.0.0 467 11/4/2018
Show less