OMS.NET/DbClass/SqlTable.cs

using System.Reflection;
using MySql.Data.MySqlClient;
using OMS.NET.Common;
namespace OMS.NET.DbClass
{
    public class SqlTable
    {
        public void Test()
        {
            Console.WriteLine("===========this is a test area=================");
            Console.WriteLine(SqlHelper.GetInsertSql(GetType()));
            Console.WriteLine(SqlHelper.GetDeleteSql(GetType()));
            Console.WriteLine(SqlHelper.GetUpdateSql(GetType()));
            Console.WriteLine(SqlHelper.GetSelectSql(GetType()));
            Console.WriteLine(SqlHelper.GetSelectSqlWithPrimaryKey(GetType()));
            Console.WriteLine("===========this is a test area=================");
        }

        /// <summary>
        /// 将对象本身插入到数据库中
        /// </summary>
        public virtual void Insert()
        {
            var type = GetType();
            var insertSql = SqlHelper.GetInsertSql(GetType());
            using MySqlConnection connection = new(GlobalArea.ConnectionStringWithDbName);
            connection.Open(); //确保在执行命令之前打开连接
            using var command = new MySqlCommand(insertSql, connection);
            AddParameters(command);
            Console.WriteLine($"SQL: {command.CommandText}");
            foreach (MySqlParameter param in command.Parameters)
            {
                Console.WriteLine($"Parameter: {param.ParameterName}, Value: {param.Value}");
            }
            command.ExecuteNonQuery();
            Log.Info($"{SqlHelper.GetTableName(type)}数据表插入({string.Join(',', ShowDetail())})");
        }

        public virtual void Update()
        {
            var type = GetType();
            var updateSql = SqlHelper.GetUpdateSql(GetType());
            using MySqlConnection connection = new(GlobalArea.ConnectionStringWithDbName);
            connection.Open();
            using var command = new MySqlCommand(updateSql, connection);
            AddParameters(command);
            command.ExecuteNonQuery();
            Log.Info($"{SqlHelper.GetTableName(type)}数据表修改({string.Join(',', ShowDetail())})");
        }

        public virtual void Delete()
        {
            var deleteSql = SqlHelper.GetDeleteSql(GetType());
            using MySqlConnection connection = new(GlobalArea.ConnectionStringWithDbName);
            connection.Open();
            using var command = new MySqlCommand(deleteSql, connection);
            AddParameters(command);
            command.ExecuteNonQuery();
            Log.Info($"{SqlHelper.GetTableName(GetType())}数据表删除({string.Join(',', ShowDetail())})");
        }

        private void AddParameters(MySqlCommand command)
        {
            //bool IsIdentity = SqlHelper.IsIdentity(GetType());
            foreach (var property in SqlHelper.GetProperties(GetType()))
            {
                if (property.GetCustomAttribute<ColumnAttribute>()!.IsIdentity)
                {
                    continue;
                }
                command.Parameters.AddWithValue("@" + property.Name, property.GetValue(this));
            }
        }

        public IEnumerable<string> ShowDetail()
        {
            return SqlHelper.GetProperties(GetType()).Select(p => $"{p.Name}={p.GetValue(this)}");
        }

    }
}