.NET-ORM Database Helper

最新推荐文章于 2022-10-08 14:06:21 发布
最新推荐文章于 2022-10-08 14:06:21 发布
阅读量115 收藏 1
点赞数
分类专栏： C# 文章标签： database sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liulilittle/article/details/120434323
版权

MysqlHelper.cs

namespace Ppp.Databases.Mysql
{
    using System.Data.Common;
    using global::MySql.Data.MySqlClient;
    using global::Ppp.Databases.Extensions;

    public sealed class MysqlHelper : DatabaseHelper // SDL FOR MYSQL
    {
        protected override string LeftEscapeCharacter => "`";

        protected override string RightEscapeCharacter => "`";

        protected override DbCommand CreateCommand()
        {
            return new MySqlCommand();
        }

        protected override DbParameter CreateParameter()
        {
            return new MySqlParameter();
        }

        protected override void DeriveParameters(DbCommand command)
        {
            var cmd = command as MySqlCommand;
            if (cmd != null)
            {
                MySqlCommandBuilder.DeriveParameters(cmd);
            }
        }
    }
}

DatabaseHelper.cs

namespace Ppp.Databases.Extensions
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Linq;
    using System.Reflection;
    using global::Ppp.Utilities;

    public abstract class DatabaseHelper // SDL FOR DATABASE
    {
        protected abstract string LeftEscapeCharacter { get; }

        protected abstract string RightEscapeCharacter { get; }

        protected abstract DbCommand CreateCommand();

        protected abstract DbParameter CreateParameter();

        protected abstract void DeriveParameters(DbCommand command);

        private DbCommand CreateInsertImpl(object obj, string fromTable)
        {
            if (obj == null || string.IsNullOrEmpty(fromTable))
            {
                throw new ArgumentException();
            }
            string sql = "INSERT INTO {0}({1}) VALUES({2})";
            DbCommand cmd = this.CreateCommand();
            DbParameterCollection args = cmd.Parameters;
            PropertyInfo[] props = (obj.GetType()).GetProperties();
            int len = props.Length - 1;
            string fileds = null, values = null;
            for (int i = 0; i <= len; i++)
            {
                PropertyInfo prop = props[i];
                if (i >= len)
                {
                    values += ("@" + prop.Name);
                    fileds += string.Format("{0}{1}{2}", this.LeftEscapeCharacter, prop.Name, this.RightEscapeCharacter);
                }
                else
                {
                    values += string.Format("@{0},", prop.Name);
                    fileds += string.Format("{0}{1}{2},", this.LeftEscapeCharacter, prop.Name, this.RightEscapeCharacter);
                }
                object val = prop.GetValue(obj, null);
                if (val == null)
                {
                    val = DBNull.Value;
                }
                DbParameter parameter = this.CreateParameter();
                parameter.Value = val;
                parameter.ParameterName = string.Format("@{0}", prop.Name);
                args.Add(parameter);
            }
            if (fileds != null)
            {
                while (fileds.Length > 0 && fileds[fileds.Length - 1] == ',')
                {
                    fileds = fileds.Remove(fileds.Length - 1);
                }
            }
            sql = string.Format(sql, fromTable, fileds, values);
            cmd.CommandText = sql;
            return cmd;
        }

        private DbCommand CreateInsertImpl(IList<KeyValuePair<string, object>> obj, string fromTable)
        {
            if (obj == null || string.IsNullOrEmpty(fromTable))
            {
                throw new ArgumentException();
            }
            string sql = "INSERT INTO {0}({1}) VALUES({2})";
            DbCommand cmd = this.CreateCommand();
            DbParameterCollection args = cmd.Parameters;
            int len = obj.Count - 1;
            string fileds = null, values = null;
            for (int i = 0; i <= len; i++)
            {
                var prop = obj[i];
                if (i >= len)
                {
                    values += ("@" + prop.Key);
                    fileds += string.Format("{0}{1}{2}", this.LeftEscapeCharacter, prop.Key, this.RightEscapeCharacter);
                }
                else
                {
                    values += string.Format("@{0},", prop.Key);
                    fileds += string.Format("{0}{1}{2},", this.LeftEscapeCharacter, prop.Key, this.RightEscapeCharacter);
                }
                object val = prop.Value;
                if (val == null)
                {
                    val = DBNull.Value;
                }
                DbParameter parameter = this.CreateParameter();
                parameter.Value = val;
                parameter.ParameterName = string.Format("@{0}", prop.Key);
                args.Add(parameter);
            }
            if (fileds != null)
            {
                while (fileds.Length > 0 && fileds[fileds.Length - 1] == ',')
                {
                    fileds = fileds.Remove(fileds.Length - 1);
                }
            }
            sql = string.Format(sql, fromTable, fileds, values);
            cmd.CommandText = sql;
            return cmd;
        }

        private DbCommand CreateInsertImpl(IDictionary<string, object> obj, string fromTable)
        {
            if (obj == null || string.IsNullOrEmpty(fromTable))
            {
                throw new ArgumentException();
            }
            return this.CreateInsertImpl(obj.ToList(), fromTable);
        }

        public virtual DbCommand CreateInsert(object obj, string fromTable, string primaryKey, string whereCast)
        {
            IDictionary<string, object> d = obj as IDictionary<string, object>;
            if (d != null)
            {
                return this.CreateInsertImpl(d, fromTable);
            }
            IList<KeyValuePair<string, object>> l = obj as IList<KeyValuePair<string, object>>;
            if (l != null)
            {
                return this.CreateInsertImpl(l, fromTable);
            }
            return this.CreateInsertImpl(obj, fromTable);
        }

        private DbCommand CreateUpdateImpl(IDictionary<string, object> obj, string fromTable, string primaryKey, string whereCast)
        {
            if (obj == null || obj.Count <= 0 || string.IsNullOrEmpty(fromTable) || string.IsNullOrEmpty(primaryKey))
            {
                throw new ArgumentException();
            }
            return this.CreateUpdateImpl(obj.ToList(), fromTable, primaryKey, whereCast);
        }

        private DbCommand CreateUpdateImpl(IList<KeyValuePair<string, object>> obj, string fromTable, string primaryKey, string whereCast)
        {
            if (obj == null || obj.Count <= 0 || string.IsNullOrEmpty(fromTable) || string.IsNullOrEmpty(primaryKey))
            {
                throw new ArgumentException();
            }
            string when = string.Empty, sql = string.Format("UPDATE {0} SET ", fromTable);
            DbCommand cmd = this.CreateCommand();
            DbParameterCollection args = cmd.Parameters;

            int len = obj.Count - 1;
            for (int i = 0; i <= len; i++)
            {
                var prop = obj[i];
                if (prop.Key != primaryKey)
                {
                    sql += string.Format(" {0}{1}{2}=@{3},", this.LeftEscapeCharacter, prop.Key, this.RightEscapeCharacter, prop.Key);
                }
                else
                {
                    when += string.Format(" WHERE {0}{1}{2}=@{3}", this.LeftEscapeCharacter, primaryKey, this.RightEscapeCharacter, primaryKey);
                }
                object val = prop.Value;
                if (val == null)
                {
                    val = DBNull.Value;
                }
                DbParameter parameter = this.CreateParameter();
                parameter.Value = val;
                parameter.ParameterName = string.Format("@{0}", prop.Key);
                args.Add(parameter);
            }
            if (!string.IsNullOrEmpty(whereCast))
            {
                when += string.Format(" AND {0} ", whereCast);
            }
            while (sql.Length > 0 && sql[sql.Length - 1] == ',')
            {
                sql = sql.Remove(sql.Length - 1);
            }
            cmd.CommandText = (sql += when);
            return cmd;
        }

        private DbCommand CreateUpdateImpl(object obj, string fromTable, string primaryKey, string whereCast)
        {
            if (obj == null || string.IsNullOrEmpty(fromTable) || string.IsNullOrEmpty(primaryKey))
            {
                throw new ArgumentException();
            }
            string when = string.Empty, sql = string.Format("UPDATE {0} SET ", fromTable);
            DbCommand cmd = this.CreateCommand();
            DbParameterCollection args = cmd.Parameters;
            PropertyInfo[] props = (obj.GetType()).GetProperties();
            int len = props.Length - 1;
            for (int i = 0; i <= len; i++)
            {
                PropertyInfo prop = props[i];
                if (prop.Name != primaryKey)
                {
                    sql += string.Format(" {0}{1}{2}=@{3},", this.LeftEscapeCharacter, prop.Name, this.RightEscapeCharacter, prop.Name);
                }
                else
                {
                    when += string.Format(" WHERE {0}{1}{2}=@{3}", this.LeftEscapeCharacter, primaryKey, this.RightEscapeCharacter, primaryKey);
                }
                object val = prop.GetValue(obj, null);
                if (val == null)
                {
                    val = DBNull.Value;
                }
                DbParameter parameter = this.CreateParameter();
                parameter.Value = val;
                parameter.ParameterName = string.Format("@{0}", prop.Name);
                args.Add(parameter);
            }
            if (!string.IsNullOrEmpty(whereCast))
            {
                when += string.Format(" AND {0} ", whereCast);
            }
            while (sql.Length > 0 && sql[sql.Length - 1] == ',')
            {
                sql = sql.Remove(sql.Length - 1);
            }
            cmd.CommandText = (sql += when);
            return cmd;
        }

        public virtual DbCommand CreateUpdate(object obj, string fromTable, string primaryKey, string whereCast)
        {
            IDictionary<string, object> d = obj as IDictionary<string, object>;
            if (d != null)
            {
                return this.CreateUpdateImpl(d, fromTable, primaryKey, whereCast);
            }
            IList<KeyValuePair<string, object>> l = obj as IList<KeyValuePair<string, object>>;
            if (l != null)
            {
                return this.CreateUpdateImpl(l, fromTable, primaryKey, whereCast);
            }
            return this.CreateUpdateImpl(obj, fromTable, primaryKey, whereCast);
        }

        public virtual int ExecuteNonQuery(DbCommand command, DbConnection connection)
        {
            if (command == null)
            {
                throw new ArgumentNullException("cmd");
            }
            command.Connection = connection ?? throw new ArgumentNullException("connection");
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            return command.ExecuteNonQuery();
        }

        public virtual object ExecuteScalar(DbCommand command, DbConnection connection)
        {
            if (command == null)
            {
                throw new ArgumentNullException("cmd");
            }
            command.Connection = connection ?? throw new ArgumentNullException("connection");
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            return command.ExecuteScalar();
        }

        public virtual bool InvalidParameter(params string[] args)
        {
            if (args.IsNullOrEmpty<string>())
            {
                return true;
            }
            foreach (string item in args)
            {
                if (item.IndexOf("'") > 0)
                {
                    return true;
                }
            }
            return false;
        }

        public virtual DbParameter[] GetParameters(object value)
        {
            if (value == null)
            {
                throw new ArgumentNullException("value");
            }
            Type clazz = value.GetType();
            PropertyInfo[] properties = clazz.GetProperties();
            DbParameter[] parameters = new DbParameter[properties.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                PropertyInfo prop = properties[i];
                object obj = prop.GetValue(value, null);
                if (obj == null)
                {
                    obj = DBNull.Value;
                }
                DbParameter parameter = this.CreateParameter();
                parameter.Value = obj;
                parameter.ParameterName = string.Format("@{0}", prop.Name);
                parameters[i] = parameter;
            }
            return parameters;
        }

        public virtual DbParameter[] GetParameters(DbConnection connection, string procedure)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            using (DbCommand cmd = this.CreateCommand())
            {
                cmd.CommandText = procedure;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = connection;
                //
                this.DeriveParameters(cmd); // 探测存储过程参数
                                            //
                DbParameterCollection args = cmd.Parameters;
                DbParameter[] buffer = new DbParameter[args.Count];
                for (int i = 0; i < args.Count; i++) // 交接且设置默认值
                {
                    DbParameter arg = args[i];
                    arg.Value = DBNull.Value;
                    buffer[i] = arg;
                }
                return buffer;
            }
        }
    }
}

liulilittle
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
.NET技术学习大纲
09-25
深入学习Entity Framework的使用方法，掌握Code First、Database First、Model First三种开发模式。掌握LINQ查询语法，了解EF性能优化的方法。理解EF的工作原理，学会使用SQL监控工具。掌握在EF中执行原生SQL的方法...
ASP.NET MVC 5高级编程
01-04
你可以使用Entity Framework等ORM工具来操作数据库，通过定义实体类来映射数据库表。模型层应确保数据的一致性和完整性，通过验证规则和业务逻辑实现。 2. **视图（View）**：视图是用户界面的呈现部分，通常由...
【两种DbParameter的使用方式】防止SQL注入 C# MVC
weixin_43041350的博客
10-08 1994
1.第一种方法就是在使用conn的情况下，使用动态参数dapper来代替SQL拼接。2.第二种方法就是使用DbParameter（在有database的情况下）2.2参数较多，则用一个List。2.1 需要的参数不多的时候。
使用实体框架返回数据表
寒冰屋的专栏
04-23 379
下载源8.9 KB 背景 在应用程序的特定情况下，我们必须以DataTable对象的形式获取结果。该应用程序正在使用Oracle数据库和Entity Framework EDMX。我们必须使用现有的数据库上下文实例从未映射的共享视图中选择数据。为此，我实际上为DbContext对象编写了一些扩展方法，如下所述。 扩展方式 我们将创建DbContext同时考虑Entity Framewo...
wf中的IDictionary
thebesttome的专栏
06-19 1671
在wf中参数的传值和获取经常用到IDictionary。现在简单介绍下IDictionaryIDictionary接口是所有字典类集合的基本接口，该接口与ICollection，IEnumerable接口是所有非泛型类集合的最基本的接口IEnumerable接口用于公开枚举数，该枚举数支持在非泛型集合上进行简单迭代。实现了该接口的集合可以用foreach语句迭代其中的元素ICollec
C#字典转换成where条件
weixin_30786617的博客
12-28 1542
where 1=1 andDictionary[key1]=Dictionary[value1] andDictionary[key2]=Dictionary[value3]。。。。 /// <summary> /// 传入一个字段返回where条件 /// </summary> /// <param name="...
JObject——转化为字典IDictionary<string, object>
热门推荐
世界中心的专栏
12-23 1万+
代码如下： /// /// JObject扩展 /// public static class JObjectExtensions { /// /// 将JObject转化成字典 /// /// /// public static IDictionary
Dos.ORM代码生成器
04-21
Dos.ORM框架提供了数据访问层的抽象，使得在.NET环境中，开发人员可以更方便地进行数据库操作。通过使用这个框架，开发人员能够快速构建应用程序的数据访问层，而无需手动编写大量与数据库交互的代码。 在压缩包中...
.net面试机试题集锦
04-11
- Entity Framework：ORM（对象关系映射）工具，用于简化数据库操作，理解Code First、Database First工作流。 6. **Web Services与WCF** - SOAP与RESTful服务：了解Web服务的两种主要类型，以及它们的区别和应用...
DbParameter
ainixi7099的博客
05-01 644
124124 转载于:https://www.cnblogs.com/buchizaodian/p/8975672.html
认父亲的DbParameter!!
weixin_33982670的博客
06-19 253
昨日,写下了类似下面的代码:           //定义两个参数          OleDbParameter[] param = {                                          new OleDbParameter("@Name", OleDbType.VarChar, 30) { Value = "小王" },                ...
.net core orm框架
最新发布
04-01
.NET Core ORM框架是一种数据访问技术，它可以将对象模型映射到关系数据库中的表。ORM框架可以使开发人员更快速地访问和操作数据库，同时也可以避免手动编写SQL语句的繁琐和容易出错。 以下是.NET Core ORM框架的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值