VberAdmin_V4/SourceCode/Zero/VberYue.Zero.EntityFramework/Data/SqlQueryHelper.cs

using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Reflection;

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;

namespace VberZero.Data;

public static class SqlQueryHelper
{
    public static IEnumerable<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
    {
        DataTable dt = SqlQuery(facade, sql, parameters);
        return dt.ToEnumerable<T>();
    }

    public static IEnumerable<T> ToEnumerable<T>(this DataTable dt) where T : class, new()
    {
        PropertyInfo[] propertyInfos = typeof(T).GetProperties();
        T[] ts = new T[dt.Rows.Count];
        int i = 0;
        foreach (DataRow row in dt.Rows)
        {
            T t = new T();
            foreach (PropertyInfo p in propertyInfos)
            {
                if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                    p.SetValue(t, row[p.Name], null);
            }
            ts[i] = t;
            i++;
        }
        return ts;
    }

    public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
    {
        DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
        DbDataReader reader = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(reader);
        reader.Close();
        conn.Close();
        return dt;
    }

    private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters)
    {
        DbConnection conn = facade.GetDbConnection();
        dbConn = conn;
        conn.Open();
        DbCommand cmd = conn.CreateCommand();
        if (facade.IsSqlServer())
        {
            cmd.CommandText = sql;
            CombineParams(ref cmd, parameters);
        }
        return cmd;
    }

    private static void CombineParams(ref DbCommand command, params object[] parameters)
    {
        if (parameters != null)
        {
            foreach (SqlParameter parameter in parameters)
            {
                if (!parameter.ParameterName.Contains("@"))
                    parameter.ParameterName = $"@{parameter.ParameterName}";
                command.Parameters.Add(parameter);
            }
        }
    }
}