[原創]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://www.zgcwkj.com/archives/159.html

轉載聲明：請注明本文章的標題及內容的出處和聲明，謝謝