data-transfer/SnapshotWinFormsApp/Tools/MyExtensions.cs

using Castle.Core.Internal;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DataTransmission.Tools;

public static class MyExtensions
{
    public static string GetTableName<T>(this T value) where T : class
    {
        var tableAttribute = typeof(T).GetCustomAttribute<TableAttribute>();
        return tableAttribute?.Name ?? string.Empty;
    }

    /// <summary>
    /// 将DataTable转换为指定类型的实体集合
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    /// <param name="dt">DataTable</param>
    /// <returns>实体集合</returns>
    public static List<T> ToList<T>(this DataTable dt) where T : new()
    {
        List<T> list = new List<T>();
        if (dt == null || dt.Rows.Count == 0)
            return list;

        PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty);

        foreach (DataRow row in dt.Rows)
        {
            T item = new T();
            foreach (PropertyInfo property in properties)
            {
                if (dt.Columns.Contains(property.Name) && !row.IsNull(property.Name))
                {
                    object value = Convert.ChangeType(row[property.Name], property.PropertyType);
                    property.SetValue(item, value, null);
                }
            }
            list.Add(item);
        }

        return list;
    }

    public static string ToSqlInsert<T>(this T entity) where T : class
    {
        if (entity == null)
            throw new ArgumentNullException(nameof(entity));

        var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        var tableName = entity.GetTableName();
        var columns = new StringBuilder();
        var values = new StringBuilder();

        foreach (var property in properties)
        {
            if (!property.CanWrite)
                continue;

            var columnName = property.Name;
            var value = property.GetValue(entity);

            if (value == null)
            {
                //values.Append("NULL, ");
                continue;
            }

            if (property.PropertyType == typeof(string) || property.PropertyType == typeof(double))
            {
                value = $"'{value.ToString().Replace("'", "''")}'";
            }
            else if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
            {
                value = $"'{(DateTime)value:yyyy-MM-dd hh:mm:ss.ffffff}'";
            }
            else if (property.PropertyType.BaseType == typeof(System.Enum))
            {
                value = $"{(int)value}";
            }
            else
            {
                value = value.ToString();
            }

            columns.Append($"\"{columnName}\", ");
            values.Append($"{value}, ");
        }

        if (columns.Length == 0)
            throw new InvalidOperationException("没有可插入的属性。");

        // 移除最后的逗号和空格
        columns.Length -= 2;
        values.Length -= 2;

        var sql = $"INSERT INTO {tableName} ({columns}) VALUES ({values});";

        return sql;
    }
}