[原创]DotNet Core 实体转SQL（C#）

实体转SQL

using MySql.Data.MySqlClient;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Serialization;

namespace Zgcwkj.DbContext
{
    public class DataModel
    {
        /// <summary>
        /// SQL操作对象
        /// </summary>
        public static MySqlDAL db = new MySqlDAL();

        /// <summary>
        /// 加载数据
        /// </summary>
        /// <returns></returns>
        public bool LoadData()
        {
            Type type = this.GetType();
            string tableName = GetTableName(type);
            var data = GetTableData(type);
            string[] column = data.notkeyColumns.ToArray();
            object[] value = data.notkeyValues.ToArray();
            StringBuilder sql = new StringBuilder();
            sql.Append($"select * from {tableName} where ");
            for (int i = 0; i < column.Length; i++)
            {
                sql.Append($" {column[i]} = '{value[i]}' ");
                if (i != column.Length - 1) sql.Append($" and ");
            }
            var dataRow = db.ExecuteSqlOne(sql.ToString());
            if (dataRow.IsNullOrEmpty()) return false;
            //赋值
            PropertyInfo[] properties = this.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
            foreach (var property in properties)
            {
                if (!dataRow[property.Name].IsNullOrEmpty())
                {
                    property.SetValue(this, dataRow[property.Name]);
                }
            }
            return true;
        }

        /// <summary>
        /// 新增数据
        /// </summary>
        /// <returns></returns>
        public bool Insert()
        {
            Type type = this.GetType();
            string tableName = GetTableName(type);
            var data = GetTableData(type);
            string columns = string.Join(",", data.notkeyColumns);
            object[] values = data.notkeyValues.ToArray();
            StringBuilder sql = new StringBuilder();
            sql.Append($"insert into {tableName} ({columns}) values(@{columns.Replace(",", ",@")})");
            int count = db.ExecuteUpdateSql(sql.ToString(), values);
            return count > 0;
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <returns></returns>
        public bool Update()
        {
            Type type = this.GetType();
            string tableName = GetTableName(type);
            var data = GetTableData(type);
            StringBuilder sql = new StringBuilder();
            string[] columns = data.notkeyColumns.ToArray();
            object[] values = data.notkeyValues.ToArray();
            sql.Append($"update {tableName} set ");
            for (int i = 0; i < columns.Length; i++)
            {
                sql.Append($" {columns[i]} = @{columns[i]} ");
                if (i != columns.Length - 1) sql.Append($",");
            }
            string[] keyColumns = data.keyColumns.ToArray();
            object[] keyValues = data.keyValues.ToArray();
            sql.Append($" where ");
            for (int i = 0; i < keyColumns.Length; i++)
            {
                sql.Append($" {keyColumns[i]} = @{keyColumns[i]} ");
                if (i != keyColumns.Length - 1) sql.Append($" and ");
            }
            int count = db.ExecuteUpdateSql(sql.ToString(), values.Concat(keyValues).ToArray());
            return count > 0;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <returns></returns>
        public bool Delete()
        {
            Type type = this.GetType();
            string tableName = GetTableName(type);
            var data = GetTableData(type);
            string[] columns = data.keyColumns.ToArray();
            object[] values = data.keyValues.ToArray();
            StringBuilder sql = new StringBuilder();
            sql.Append($"delete from {tableName} where ");
            for (int i = 0; i < columns.Length; i++)
            {
                sql.Append($" {columns[i]} = @{columns[i]} ");
                if (i != columns.Length - 1) sql.Append($" and ");
            }
            int count = db.ExecuteUpdateSql(sql.ToString(), values);
            return count > 0;
        }

        /// <summary>
        /// 获取表名称
        /// </summary>
        /// <param name="type">类型</param>
        /// <returns></returns>
        private string GetTableName(Type type)
        {
            string tableName = string.Empty, tableSchema = string.Empty;
            foreach (var attribute in type.GetCustomAttributes())
            {
                var tableAttribute = attribute as TableAttribute;//表
                tableName = tableAttribute.Name;
                tableSchema = tableAttribute.Schema;
            }
            return tableName;
        }

        /// <summary>
        /// 获取表数据
        /// </summary>
        /// <param name="type">类型</param>
        /// <returns></returns>
        private dynamic GetTableData(Type type)
        {
            List<TableMode> tableModes = GetTableObject(type);
            var keyData = tableModes.Where(T => T.IsKey == true && T.Value != default);
            var keyColumns = keyData.Select(T => T.Column).ToList();
            var keyValues = keyData.Select(T => T.Value).ToList();
            var notkeyData = tableModes.Where(T => T.IsKey == false && T.Value != default);
            var notkeyColumns = notkeyData.Select(T => T.Column).ToList();
            var notkeyValues = notkeyData.Select(T => T.Value).ToList();
            return new
            {
                tableModes,
                keyColumns,
                keyValues,
                notkeyColumns,
                notkeyValues,
            };
        }

        /// <summary>
        /// 获取表对象
        /// </summary>
        /// <param name="type">类型</param>
        /// <returns></returns>
        private List<TableMode> GetTableObject(Type type)
        {
            List<TableMode> tableModes = new List<TableMode>();
            foreach (var property in type.GetProperties())
            {
                TableMode tableMode = new TableMode();
                foreach (var attribute in property.GetCustomAttributes())
                {
                    var keyAttribute = attribute as KeyAttribute;//是否主键
                    if (!keyAttribute.IsNullOrEmpty())
                    {
                        tableMode.IsKey = true;
                    }
                    var columnAttribute = attribute as ColumnAttribute;//字段名称
                    if (!columnAttribute.IsNullOrEmpty())
                    {
                        tableMode.Column = columnAttribute.Name;
                    }
                }
                tableMode.Value = property.GetValue(this);
                tableModes.Add(tableMode);
            }
            return tableModes;
        }
    }

    /// <summary>
    /// 表对象
    /// </summary>
    partial class TableMode
    {
        /// <summary>
        /// 是否是主键
        /// </summary>
        public bool IsKey { get; set; }

        /// <summary>
        /// 字段名称
        /// </summary>
        public string Column { get; set; }

        /// <summary>
        /// 字段值
        /// </summary>
        public object Value { get; set; }

        /// <summary>
        /// 字段类型
        /// </summary>
        public string Type
        {
            get
            {
                return Value.GetType().Name;
            }
        }

        /// <summary>
        /// 字段数据
        /// </summary>
        public string Data
        {
            get
            {
                if (Value.GetType() == typeof(string))
                {
                    return $"'{Value}'";
                }
                else
                {
                    return $"{Value}";
                }
            }
        }
    }
}

数据操作工具类

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using Microsoft.Extensions.Configuration;
using System.IO;

namespace Zgcwkj.DbContext
{
    public class MySqlDAL
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string strConnect = @"server=localhost;port=3306;user id=root;password=root;database=yaohuoApp;Charset=utf8;";

        /// <summary>
        /// 实例对象
        /// </summary>
        public MySqlDAL()
        {
            //读取配置文件
            var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json");
            var config = builder.Build();
            //配置数据
            var server = config["mysql:server"];
            var port = config["mysql:port"];
            var initialCatalog = config["mysql:database"];
            var userId = config["mysql:userId"];
            var password = config["mysql:password"];
            var charset = config["mysql:charset"];
            string mysql = string.Format("server={0};port={1};database={2};user={3};password={4};Charset={5};", server, port, initialCatalog, userId, password, charset);
            strConnect = mysql;
        }

        /// <summary>
        /// 实例对象
        /// </summary>
        public MySqlDAL(string strconnect)
        {
            strConnect = strconnect;
        }

        /// <summary>
        /// 查询数据表（存储过程）
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="sqlParameter">SQL参数</param>
        /// <returns>DataTable</returns>
        public DataTable QueryDataTable(string sqlStatement, params object[] sqlParameter)
        {
            DataTable dataTable = new DataTable();
            using (MySqlConnection sqlConnection = new MySqlConnection(strConnect))
            {
                sqlConnection.Open();
                MySqlCommand sqlCommand = new MySqlCommand(sqlStatement, sqlConnection);
                MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sqlCommand);
                MatchCollection matchCollection = Regex.Matches(sqlStatement, @"(?<=[^0-9a-zA-Z])@(?!@)[0-9a-zA-Z_$#@]+");//得到脚本上的变量
                for (int i = 0; i < sqlParameter.Count(); i++)
                {
                    string key = matchCollection[i].Value;
                    object value = sqlParameter[i];
                    MySqlParameter mySqlParameter = new MySqlParameter(key, value);
                    sqlCommand.Parameters.Add(mySqlParameter);
                }
                MySqlDataAdapter mda = new MySqlDataAdapter(sqlCommand);
                mda.Fill(dataTable);
                sqlConnection.Close();
            }
            return dataTable;
        }

        /// <summary>
        /// 查询数据表（存储过程）
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="sqlParameter">SQL参数</param>
        /// <returns>List</returns>
        public List<Dictionary<string, object>> QueryList(string sqlStatement, params object[] sqlParameter)
        {
            DataTable dataTable = new DataTable();
            using (MySqlConnection sqlConnection = new MySqlConnection(strConnect))
            {
                sqlConnection.Open();
                MySqlCommand sqlCommand = new MySqlCommand(sqlStatement, sqlConnection);
                MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sqlCommand);
                MatchCollection matchCollection = Regex.Matches(sqlStatement, @"(?<=[^0-9a-zA-Z])@(?!@)[0-9a-zA-Z_$#@]+");//得到脚本上的变量
                for (int i = 0; i < sqlParameter.Count(); i++)
                {
                    string key = matchCollection[i].Value;
                    object value = sqlParameter[i];
                    MySqlParameter mySqlParameter = new MySqlParameter(key, value);
                    sqlCommand.Parameters.Add(mySqlParameter);
                }
                MySqlDataAdapter mda = new MySqlDataAdapter(sqlCommand);
                mda.Fill(dataTable);
                sqlConnection.Close();
            }
            return dataTable.ToList();
        }

        /// <summary>
        /// 插入、更新、删除（存储过程）
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="sqlParameter">SQL参数</param>
        /// <returns>变化的条数</returns>
        public int UpdateData(string sqlStatement, params object[] sqlParameter)
        {
            int count = 0;
            using (MySqlConnection sqlConnection = new MySqlConnection(strConnect))
            {
                sqlConnection.Open();
                MySqlCommand sqlCommand = new MySqlCommand(sqlStatement, sqlConnection);
                MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sqlCommand);
                MatchCollection matchCollection = Regex.Matches(sqlStatement, @"(?<=[^0-9a-zA-Z])@(?!@)[0-9a-zA-Z_$#@]+");//得到脚本上的变量
                for (int i = 0; i < sqlParameter.Count(); i++)
                {
                    string key = matchCollection[i].Value;
                    object value = sqlParameter[i];
                    MySqlParameter mySqlParameter = new MySqlParameter(key, value);
                    sqlCommand.Parameters.Add(mySqlParameter);
                }
                MySqlDataAdapter mda = new MySqlDataAdapter(sqlCommand);
                count = sqlCommand.ExecuteNonQuery();
                sqlCommand.Dispose();
                sqlConnection.Close();
                sqlConnection.Dispose();
            }
            return count;
        }

        /// <summary>
        /// 查询数据表（存储语句）
        /// </summary>
        /// <param name="sqlStatement">SQL语句</param>
        /// <param name="sqlParameter">SQL参数</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteSql(string sqlStatement, params object[] sqlParameter)
        {
            DataTable dataTable = new DataTable();
            using (MySqlConnection sqlConnection = new MySqlConnection(strConnect))
            {
                try
                {
                    sqlConnection.Open();
                    MySqlCommand sqlCommand = new MySqlCommand(sqlStatement, sqlConnection);
                    MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sqlCommand);
                    MatchCollection matchCollection = Regex.Matches(sqlStatement, @"(?<=[^0-9a-zA-Z])@(?!@)[0-9a-zA-Z_$#@]+");//得到脚本上的变量
                    for (int i = 0; i < sqlParameter.Count(); i++)
                    {
                        string key = matchCollection[i].Value;
                        object value = sqlParameter[i];
                        MySqlParameter mySqlParameter = new MySqlParameter(key, value);
                        sqlCommand.Parameters.Add(mySqlParameter);
                    }
                    sqlDataAdapter.Fill(dataTable);
                }
                catch (Exception e)
                {
                    string message = e.Message;
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return dataTable;
        }

        /// <summary>
        /// 查询一条数据（存储语句）
        /// </summary>
        /// <param name="sqlStatement">SQL语句</param>
        /// <param name="sqlParameter">SQL参数</param>
        /// <returns>DataTable</returns>
        public DataRow ExecuteSqlOne(string sqlStatement, params object[] sqlParameter)
        {
            DataTable dataTable = new DataTable();
            using (MySqlConnection sqlConnection = new MySqlConnection(strConnect))
            {
                try
                {
                    sqlConnection.Open();
                    MySqlCommand sqlCommand = new MySqlCommand(sqlStatement + " limit 1", sqlConnection);
                    MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sqlCommand);
                    MatchCollection matchCollection = Regex.Matches(sqlStatement, @"(?<=[^0-9a-zA-Z])@(?!@)[0-9a-zA-Z_$#@]+");//得到脚本上的变量
                    for (int i = 0; i < sqlParameter.Count(); i++)
                    {
                        string key = matchCollection[i].Value;
                        object value = sqlParameter[i];
                        MySqlParameter mySqlParameter = new MySqlParameter(key, value);
                        sqlCommand.Parameters.Add(mySqlParameter);
                    }
                    sqlDataAdapter.Fill(dataTable);
                }
                catch (Exception e)
                {
                    string message = e.Message;
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            if (dataTable.Rows.Count > 0) return dataTable.Rows[0];
            return null;
        }

        /// <summary>
        /// 插入、更新、删除（存储语句）
        /// </summary>
        /// <param name="sqlStatement">SQL语句</param>
        /// <param name="sqlParameter">SQL参数</param>
        /// <returns>变化的条数</returns>
        public int ExecuteUpdateSql(string sqlStatement, params object[] sqlParameter)
        {
            int count = 0;
            using (MySqlConnection sqlConnection = new MySqlConnection(strConnect))
            {
                try
                {
                    sqlConnection.Open();
                    MySqlCommand sqlCommand = new MySqlCommand(sqlStatement, sqlConnection);
                    MatchCollection matchCollection = Regex.Matches(sqlStatement, @"(?<=[^0-9a-zA-Z])@(?!@)[0-9a-zA-Z_$#@]+");//得到脚本上的变量
                    for (int i = 0; i < sqlParameter.Count(); i++)
                    {
                        string key = matchCollection[i].Value;
                        object value = sqlParameter[i];
                        MySqlParameter mySqlParameter = new MySqlParameter(key, value);
                        sqlCommand.Parameters.Add(mySqlParameter);
                    }
                    count = sqlCommand.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    string message = e.Message;
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return count;
        }

        /// <summary>
        /// 插入、更新、删除（存储语句）
        /// </summary>
        /// <param name="sqlStatement">SQL语句</param>
        /// <param name="sqlParameter">SQL参数</param>
        /// <returns>变化的条数</returns>
        public int ExecuteSqlStatementUpdateData(string sqlStatement, params MySqlParameter[] sqlParameter)
        {
            int count = 0;
            using (MySqlConnection sqlConnection = new MySqlConnection(strConnect))
            {
                try
                {
                    sqlConnection.Open();
                    MySqlCommand sqlCommand = new MySqlCommand(sqlStatement, sqlConnection);
                    sqlCommand.Parameters.AddRange(sqlParameter);
                    count = sqlCommand.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    string message = e.Message;
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return count;
        }

        /// <summary>
        /// 二进制文件查询方法
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="sqlParameter">SQL参数</param>
        /// <returns>二进制</returns>
        public byte[] QueryDataByte(string storedProcedureName, MySqlParameter[] sqlParameter)
        {
            byte[] File = null;
            using (MySqlConnection sqlConnection = new MySqlConnection(strConnect))
            {
                try
                {
                    sqlConnection.Open();
                    MySqlDataReader sqlDataReader = null;
                    MySqlCommand sqlCommand = new MySqlCommand(storedProcedureName, sqlConnection);
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddRange(sqlParameter);
                    sqlDataReader = sqlCommand.ExecuteReader();
                    if (sqlDataReader.Read())
                    {
                        File = (byte[])sqlDataReader[0];
                    }
                    sqlDataReader.Close();
                }
                catch (Exception e)
                {
                    string message = e.Message;
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                }
                return File;
            }
        }
    }
}

本文禁止转载，谢谢

---

版权属于：zgcwkj

本文链接：https://blog.zgcwkj.cn/archives/159.html

转载声明：请注明本文章的标题及内容的出处和声明，谢谢