数据库查询优化：减少往返次数

在现代应用程序开发中，数据库查询的性能至关重要。频繁的数据库往返不仅会降低用户体验，还可能导致服务器资源的浪费。本文将探讨一种优化策略，通过减少数据库往返次数来提升应用程序的性能。

在许多情况下，应用程序需要从数据库加载不同的数据列表以渲染特定的视图。尽管这些数据在结构上可能有所不同，但在数据库端执行的逻辑操作往往是相似的。这种重复的逻辑操作会增加总体的执行时间。因此，目标是避免多次数据库往返，而是通过一次调用获取所有需要的数据。

虽然标题听起来像是将使用Entity Framework来完成所有操作，但实际上整个过程将使用ADO.NET和一些自定义映射器来完成。这种解决方案适用于使用单一ORM（如Entity Framework）的应用程序。

使用ADO.NET和自定义映射器

将通过以下步骤来实现这一目标：

在数据库中运行SQL查询，使用Entity Framework连接。
从查询结果中填充DataSet。
从DataSet中获取个别DataTable。
将每个DataTable映射到预期的实体列表List。

将为DbContext创建扩展方法，考虑Entity Framework和Entity Framework Core。这些扩展方法期望两个参数：SQL查询字符串和可选的DbParameter对象，用于参数化查询。

根据SQL查询的不同，数据库将返回单个或多个结果表。在使用这些扩展方法时，需要确保查询返回预期的表格集合/集合，或者没有行。否则，需要相应地使用映射器。


// Entity Framework C#
using System.Data;
using System.Data.Common;
using System.Data.Entity;
public static class DbContextExtensions
{
    public static DataSet DataSet(this DbContext context, string sqlQuery, params DbParameter[] parameters)
    {
        return context.Database.DataSet(sqlQuery, parameters);
    }
    public static DataSet DataSet(this Database database, string sqlQuery, params DbParameter[] parameters)
    {
        DataSet data = new DataSet();
        DbConnection connection = database.Connection;
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(connection);
        using (var cmd = dbFactory.CreateCommand())
        {
            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlQuery;
            if (parameters != null)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }
            using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(data);
            }
        }
        return data;
    }
}
// Entity Framework Core C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
public static class DbContextExtensions
{
    public static DataSet DataSet(this DbContext context, string sqlQuery, params DbParameter[] parameters)
    {
        return context.Database.DataSet(sqlQuery, parameters);
    }
    public static DataSet DataSet(this DatabaseFacade database, string sqlQuery, params DbParameter[] parameters)
    {
        DataSet data = new DataSet();
        DbConnection connection = database.GetDbConnection();
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(connection);
        using (var cmd = dbFactory.CreateCommand())
        {
            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlQuery;
            if (parameters != null)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }
            using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(data);
            }
        }
        return data;
    }
}

DataSet将包含单个或多个结果集或表。这个辅助方法将帮助将DataTable映射到List的实体。这个映射过程取决于实体属性的名称和表的列，以及属性和列的数据类型。如果它们相同，列的值将映射到属性。通常，将测试公共属性。


// DataTable to Entity List Mapper
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
public static class DataTableExtensions
{
    /*
    Converts DataTable To List*/
    public static List ToList(this DataTable dataTable) where TSource : new()
    {
        var dataList = new List();
        const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic;
        var objFieldNames = (from PropertyInfo aProp in typeof(TSource).GetProperties(flags)
                              select new { aProp.Name, Type = Nullable.GetUnderlyingType(aProp.PropertyType) ?? aProp.PropertyType }).ToList();
        var dataTblFieldNames = (from DataColumn aHeader in dataTable.Columns
                                  select new { Name = aHeader.ColumnName, Type = aHeader.DataType }).ToList();
        var commonFields = objFieldNames.Intersect(dataTblFieldNames).ToList();
        foreach (DataRow dataRow in dataTable.AsEnumerable().ToList())
        {
            var aTSource = new TSource();
            foreach (var aField in commonFields)
            {
                PropertyInfo propertyInfos = aTSource.GetType().GetProperty(aField.Name);
                var value = dataRow[aField.Name] == DBNull.Value ? null : dataRow[aField.Name];
                // if database field is nullable
                propertyInfos.SetValue(aTSource, value, null);
            }
            dataList.Add(aTSource);
        }
        return dataList;
    }
}

这个映射过程不依赖于属性/列的数量。这意味着不强制C#模型类实现表的所有列/结果集。可以有任何数量的属性，但只有相似的属性会根据名称和数据类型进行映射。实际上，这个映射器没有依赖性，可以使用任何其他库以提高效率。


// C# Entity
public class RoleModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
}

如果从数据库表中选择所有内容，其结构将类似于C#模型RoleModel。


// SQL
DROP TABLE IF EXISTS [dbo].[tb_Test_Role];
GO
CREATE TABLE [dbo].[tb_Test_Role] (
    [Id] INT IDENTITY(1,1) NOT NULL,
    [Name] NVARCHAR(50) NOT NULL,
    [IsActive] BIT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


// SQL
DROP PROCEDURE IF EXISTS [dbo].[sp_Test_Role];
GO
CREATE TABLE [dbo].[tb_Test_Role] (
    [Id] INT IDENTITY(1,1) NOT NULL,
    [Name] NVARCHAR(50) NOT NULL,
    [IsActive] BIT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE OR ALTER PROCEDURE [dbo].[sp_Test_Role](
    @Name VARCHAR(MAX) = ''
) AS
BEGIN
    /*
    return data in two different set*/
    SELECT * FROM [tb_Test_Role] WHERE IsActive = 0 AND [Name] LIKE '%' + @Name + '%';
    -- inactive roles
    SELECT * FROM [tb_Test_Role] WHERE IsActive = 1 AND [Name] LIKE '%' + @Name + '%';
    -- active roles
END

该存储过程执行两次选择，结果为两个结果集，每个集合都类似于C#模型RoleModel。


// SQL
GO TRUNCATE TABLE [dbo].[tb_Test_Role]
INSERT INTO [dbo].[tb_Test_Role] ([Name], IsActive) VALUES ('admin 0', 0), ('sa 0', 0), ('user 0', 0);
INSERT INTO [dbo].[tb_Test_Role] ([Name], IsActive) VALUES ('admin 1', 1), ('sa 1', 1), ('user 1', 1);
GO SELECT * FROM [dbo].[tb_Test_Role];
GO EXEC sp_Test_Role '';

单个结果集/表：


// C#
/*
Query returning single result set or table*/
var db = new MopDb();
DataTable allRole = db.DataSet("SELECT * FROM [dbo].[tb_Test_Role]").Tables[0];
List allRoleList = allRole.ToList();

多个结果集/表：


// C#
/*
Stored procedure returning multiple result sets or tables*/
var db = new MopDb();
DataSet searchRole = db.DataSet("EXEC [dbo].[sp_Test_Role] @paramName", new SqlParameter("paramName", SqlDbType.NVarChar) { Value = "" });

DataTable inactiveRole = searchRole.Tables[0];
DataTable activeRole = searchRole.Tables[1];
List inactiveRoleList = inactiveRole.ToList();
List activeRoleList = activeRole.ToList();

SqlServer: SqlParameter
Oracle: OracleParameter
MySql: MySqlParameter
PostgreSql: NpgsqlParameter

如果想将NULL值作为参数化查询传递，可以在两个地方进行管理：


// SqlParameter Level
int? isActive = 1;
DataSet filterRole = db.DataSet("EXEC [dbo].[sp_Get_Roles] @paramIsActive", new SqlParameter("paramIsActive", SqlDbType.Bit) { Value = isActive ?? (object)DBNull.Value, IsNullable = true });

扩展方法：


public static DataSet DataSet(this Database database, string sqlQuery, params DbParameter[] parameters)
{
    /*
    added to manage null values*/
    foreach (var parameter in parameters.Where(x => x.Value == null))
    {
        parameter.Value = DBNull.Value;
    }
}

这个解决方案和项目是在Visual Studio 2022中创建的：

WithEf 是 .NET Framework 4.8
WithEfCore 是 .NET 6.0

预计此代码将从.NET Framework 4.5和.NET Core 2.2开始工作。

App.config at WithEf:


<connectionStrings>
    <add name="MopDbConnection" connectionString="Data Source=10.10.15.13\DB002; Initial Catalog=TESTDB; PASSWORD=dhaka; USER ID=FSTEST;" providerName="System.Data.SqlClient"/>
</connectionStrings>

appsettings.json at WithEfCore:


{
    "ConnectionStrings": {
        "MopDbConnection": "server=10.10.15.13\\DB002;database=TESTDB; user id=FSTEST;password=dhaka"
    }
}

MySQL游标使用指南

本文介绍了MySQL中游标的使用，包括游标的特性、创建游标的方法以及游标在数据库操作中的应用。

通用枚举值转换器实现

本文介绍了一种将枚举与单选按钮绑定的通用方法，通过实现一个转换器来简化ViewModel的复杂性，并提高代码的可维护性。

数据库查询优化：减少往返次数

使用ADO.NET和自定义映射器

MySQL游标使用指南

通用枚举值转换器实现

沪ICP备2024098111号-1

上海秋旦网络科技中心：上海市奉贤区金大公路8218号1幢联系电话：17898875485

数据库查询优化：减少往返次数

使用ADO.NET和自定义映射器

MySQL游标使用指南

通用枚举值转换器实现

沪ICP备2024098111号-1

上海秋旦网络科技中心：上海市奉贤区金大公路8218号1幢 联系电话：17898875485

上海秋旦网络科技中心：上海市奉贤区金大公路8218号1幢联系电话：17898875485