MySqlHelper

最新推荐文章于 2023-09-02 18:25:25 发布
最新推荐文章于 2023-09-02 18:25:25 发布
阅读量442 收藏
点赞数
文章标签： .net mysql mysqlhelper 批插入 批修改
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/suiersuier/article/details/84957474
版权

执行sql语句方法定义：

public static Object SqlScalar(string connectString, string sql)
        {
            return SqlScalar(connectString, sql, 1);
        }
        public static Object SqlScalar(string connectString, string sql, object parms)
        {
            return SqlScalar(connectString, sql, parms, 1);
        }
        public static int SqlExecute(string connectString, string sql)
        {
            return SqlExecute(connectString, sql, 1);
        }
        public static int SqlExecute(string connectString, string sql, object parms)
        {
            return SqlExecute(connectString, sql, parms, 1);
        }
        public static T SqlSingle<T>(string connectString, string sql)
        {
            return SqlSingle<T>(connectString, sql, 1);
        }
        public static T SqlSingle<T>(string connectString, string sql, object parms)
        {
            return SqlSingle<T>(connectString, sql, parms, 1);
        }
        public static List<T> SqlList<T>(string connectString, string sql)
        {
            return SqlList<T>(connectString, sql, 1);
        }
        public static List<T> SqlList<T>(string connectString, string sql, object parms)
        {
            return SqlList<T>(connectString, sql, parms, 1);
        }
        public static DataTable SqlDataTable(string connectString, string sql)
        {
            return SqlDataTable(connectString, sql, 1);
        }
        public static DataTable SqlDataTable(string connectString, string sql, object parms)
        {
            return SqlDataTable(connectString, sql, parms, 1);
        }

执行存储过程方法定义：

 public static Object ProScalar(string connectString, string proName)
        {
            return SqlScalar(connectString, proName, 4);
        }
        public static Object ProScalar(string connectString, string proName, object parms)
        {
            return SqlScalar(connectString, proName, parms, 4);
        }
        public static int ProExecute(string connectString, string proName)
        {
            return SqlExecute(connectString, proName, 4);
        }
        public static int ProExecute(string connectString, string proName, object parms)
        {
            return SqlExecute(connectString, proName, parms, 4);
        }
        public static T ProSingle<T>(string connectString, string proName)
        {
            return SqlSingle<T>(connectString, proName, 4);
        }
        public static T ProSingle<T>(string connectString, string proName, object parms)
        {
            return SqlSingle<T>(connectString, proName, parms, 4);
        }
        public static List<T> ProList<T>(string connectString, string proName)
        {
            return SqlList<T>(connectString, proName, 4);
        }
        public static List<T> ProList<T>(string connectString, string proName, object parms)
        {
            return SqlList<T>(connectString, proName, parms, 4);
        }
        public static DataTable ProDataTable(string connectString, string sql)
        {
            return SqlDataTable(connectString, sql, 4);
        }
        public static DataTable ProDataTable(string connectString, string sql, object parms)
        {
            return SqlDataTable(connectString, sql, parms, 4);
        }

执行sql和存储过程方法实现：

#region SqlScalar

        /// <summary>
        /// 返回查询结果的第一行第一列
        /// </summary>
        /// <param name="connectString">数据库连接串</param>
        /// <param name="sql">sql语句，或者存储过程名称</param>
        /// <param name="commandType">sql语句</param>
        /// <returns></returns>
        private static Object SqlScalar(string connectString, string sql, int commandType = 1)
        {
            object obj = null;
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (commandType == 1)
                    {
                        cmd.CommandType = CommandType.Text;
                    }
                    else
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    obj = cmd.ExecuteScalar();
                }
            }
            return obj;
        }
        /// <summary>
        /// 返回查询结果的第一行第一列
        /// </summary>
        /// <param name="connectString">数据库连接串</param>
        /// <param name="sql">sql语句</param>
        /// <param name="parms">参数</param>
        /// <returns></returns>
        private static Object SqlScalar(string connectString, string sql, object parms, int commandType = 1)
        {
            List<MySqlParameter> parmList = GetSqlCommandParams(parms);
            object obj = null;
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (commandType == 1)
                    {
                        cmd.CommandType = CommandType.Text;
                    }
                    else
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    if (parmList != null)
                    {
                        cmd.Parameters.AddRange(parmList.ToArray());
                    }
                    obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
            }
            return obj;
        }

        #endregion

        #region SqlExecute
        /// <summary>
        /// 返回影响数据库的条数
        /// </summary>
        /// <param name="connectString">数据库连接串</param>
        /// <param name="sql">要执行的sql</param>
        /// <returns></returns>
        private static int SqlExecute(string connectString, string sql, int commandType = 1)
        {
            int obj = 0;
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (commandType == 1)
                    {
                        cmd.CommandType = CommandType.Text;
                    }
                    else
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    obj = cmd.ExecuteNonQuery();
                }
            }
            return obj;
        }

        private static int SqlExecute(string connectString, string sql, object parms, int commandType = 1)
        {
            List<MySqlParameter> parmList = GetSqlCommandParams(parms);
            int obj = 0;
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (commandType == 1)
                    {
                        cmd.CommandType = CommandType.Text;
                    }
                    else
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    if (parmList != null)
                    {
                        cmd.Parameters.AddRange(parmList.ToArray());
                    }
                    obj = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
            }
            return obj;
        }
        #endregion

        #region SqlSingle
        /// <summary>
        /// 返回查询结果的第一行
        /// </summary>
        /// <typeparam name="T">要返回的类型</typeparam>
        /// <param name="connectString">数据库连接串</param>
        /// <param name="sql">要执行的sql</param>
        /// <returns></returns>
        private static T SqlSingle<T>(string connectString, string sql, int commandType = 1)
        {
            T obj = default(T);
            Type t = typeof(T);
            Assembly ass = t.Assembly;
            PropertyInfo p = null;
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (commandType == 1)
                    {
                        cmd.CommandType = CommandType.Text;
                    }
                    else
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        if (dr != null)
                        {
                            Dictionary<string, PropertyInfo> propertysDic = GetFiles<T>(dr);
                            while (dr.Read())
                            {
                                obj = (T)ass.CreateInstance(t.FullName);
                                if (propertysDic != null && propertysDic.Count > 0)
                                {
                                    foreach (var key in propertysDic.Keys)
                                    {
                                        p = propertysDic[key];
                                        p.SetValue(obj, MySqlHelper.ChangeType(dr[key], p.PropertyType));
                                    }
                                }
                                break;
                            }
                        }
                    }
                }
            }
            return obj;
        }


        private static T SqlSingle<T>(string connectString, string sql, object parms, int commandType = 1)
        {
            List<MySqlParameter> parmList = GetSqlCommandParams(parms);
            T obj = default(T);
            Type t = typeof(T);
            Assembly ass = t.Assembly;
            PropertyInfo p = null;
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (commandType == 1)
                    {
                        cmd.CommandType = CommandType.Text;
                    }
                    else
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    if (parmList != null)
                    {
                        cmd.Parameters.AddRange(parmList.ToArray());
                    }
                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        if (dr != null)
                        {
                            Dictionary<string, PropertyInfo> propertysDic = GetFiles<T>(dr);
                            while (dr.Read())
                            {
                                obj = (T)ass.CreateInstance(t.FullName);
                                if (propertysDic != null && propertysDic.Count > 0)
                                {
                                    foreach (var key in propertysDic.Keys)
                                    {
                                        p = propertysDic[key];
                                        p.SetValue(obj, MySqlHelper.ChangeType(dr[key], p.PropertyType));
                                    }
                                }
                                break;
                            }
                        }
                    }
                    cmd.Parameters.Clear();
                }
            }
            return obj;
        }
        #endregion

        #region SqlList
        /// <summary>
        /// 返回列表集合
        /// </summary>
        /// <typeparam name="T">要返回的对象类型</typeparam>
        /// <param name="connectString">数据库连接串</param>
        /// <param name="sql">要执行的sql</param>
        /// <returns></returns>
        private static List<T> SqlList<T>(string connectString, string sql, int commandType = 1)
        {
            List<T> list = new List<T>();
            Type t = typeof(T);
            Assembly ass = t.Assembly;

            PropertyInfo p = null;
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (commandType == 1)
                    {
                        cmd.CommandType = CommandType.Text;
                    }
                    else
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        if (dr != null)
                        {
                            Dictionary<string, PropertyInfo> propertysDic = GetFiles<T>(dr);
                            while (dr.Read())
                            {
                                T obj = default(T);
                                obj = (T)ass.CreateInstance(t.FullName);
                                if (propertysDic != null && propertysDic.Count > 0)
                                {
                                    foreach (var key in propertysDic.Keys)
                                    {
                                        p = propertysDic[key];
                                        p.SetValue(obj, MySqlHelper.ChangeType(dr[key], p.PropertyType));
                                    }
                                }
                                list.Add(obj);
                            }

                        }

                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 返回列表集合
        /// </summary>
        /// <typeparam name="T">要返回的对象类型</typeparam>
        /// <param name="connectString">数据库连接串</param>
        /// <param name="sql">要执行的sql</param>
        /// <param name="parms">sql中用到的参数</param>
        /// <returns></returns>
        private static List<T> SqlList<T>(string connectString, string sql, object parms, int commandType = 1)
        {
            List<MySqlParameter> parmList = GetSqlCommandParams(parms);
            List<T> list = new List<T>();
            Type t = typeof(T);
            Assembly ass = t.Assembly;
            PropertyInfo p = null;
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (commandType == 1)
                    {
                        cmd.CommandType = CommandType.Text;
                    }
                    else
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    if (parmList != null)
                    {
                        cmd.Parameters.AddRange(parmList.ToArray());
                    }
                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        if (dr != null)
                        {
                            Dictionary<string, PropertyInfo> propertysDic = GetFiles<T>(dr);
                            while (dr.Read())
                            {
                                T obj = default(T);
                                obj = (T)ass.CreateInstance(t.FullName);
                                if (propertysDic != null && propertysDic.Count > 0)
                                {
                                    foreach (var key in propertysDic.Keys)
                                    {
                                        p = propertysDic[key];
                                        p.SetValue(obj, MySqlHelper.ChangeType(dr[key], p.PropertyType));
                                    }
                                }
                                list.Add(obj);
                            }

                        }
                    }
                    cmd.Parameters.Clear();
                }
            }
            return list;
        }
        #endregion

        #region SqlDataTable
        private static DataTable SqlDataTable(string connectString, string sql, int commandType = 1)
        {
            DataTable dt = null;
            DataSet ds = new DataSet();
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (commandType == 1)
                    {
                        cmd.CommandType = CommandType.Text;
                    }
                    else
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                    }
                }
            }
            if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }
        private static DataTable SqlDataTable(string connectString, string sql, object parms, int commandType = 1)
        {
            List<MySqlParameter> parmList = GetSqlCommandParams(parms);
            DataTable dt = null;
            DataSet ds = new DataSet();
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    if (commandType == 1)
                    {
                        cmd.CommandType = CommandType.Text;
                    }
                    else
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    if (parmList != null)
                    {
                        cmd.Parameters.AddRange(parmList.ToArray());
                    }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                    }
                }
            }
            if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }
        #endregion

        #region GetSqlCommandParams
        /// <summary>
        /// 获取sqlcommand参数
        /// </summary>
        /// <param name="parms"></param>
        /// <returns></returns>
        private static List<MySqlParameter> GetSqlCommandParams(object parms)
        {
            List<MySqlParameter> parmList = null;
            PropertyInfo[] propertys = parms.GetType().GetProperties();
            if (propertys != null && propertys.Length > 0)
            {
                parmList = new List<MySqlParameter>();
                foreach (var item in propertys)
                {
                    if (!item.CanRead)
                    {
                        continue;
                    }
                    parmList.Add(new MySqlParameter("@" + item.Name, item.GetValue(parms)));
                }
            }
            return parmList;
        }
        #endregion

        #region GetFiles
        /// <summary>
        /// 获取泛型中的类型属性和数据库datareder中的属性共有部分
        /// </summary>
        /// <typeparam name="T">要返回的类型</typeparam>
        /// <param name="reader">SqlDataReader</param>
        /// <returns></returns>
        private static Dictionary<string, PropertyInfo> GetFiles<T>(MySqlDataReader reader)
        {
            Dictionary<string, PropertyInfo> result = new Dictionary<string, PropertyInfo>();
            int cloumFiles = reader == null ? 0 : reader.FieldCount;
            Type t = typeof(T);
            PropertyInfo[] propertys = t.GetProperties();
            if (propertys != null && cloumFiles > 0)
            {
                List<string> readerFilesList = new List<string>(); //存储当前reader中的所有列名称
                for (int i = 0; i < cloumFiles; i++)
                {
                    readerFilesList.Add(reader.GetName(i).ToLower());
                }
                //取reder中和T类型中都有属性
                List<PropertyInfo> resultList = propertys.Where(s => s.CanRead && readerFilesList.Contains(s.Name.ToLower())).ToList();
                if (resultList != null && resultList.Count > 0)
                {
                    foreach (var item in resultList)
                    {
                        result.Add(item.Name, item);
                    }
                }
            }
            return result;
        }
        #endregion

        #region ChangeType
        /// <summary>
        /// 将数据库中查询出来的值转化为T类型中想要的类型，这样避免直接赋值object类型装箱时的性能消耗
        /// </summary>
        /// <param name="value">数据库中查出来的value值</param>
        /// <param name="type">要转化的类型</param>
        /// <returns></returns>
        private static object ChangeType(object value, Type type)
        {
            if (type.FullName == typeof(string).FullName)
            {
                return Convert.ChangeType(Convert.IsDBNull(value) ? null : value, type);
            }
            if (type.IsGenericType && type.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                NullableConverter convertor = new NullableConverter(type);
                return Convert.IsDBNull(value) ? null : convertor.ConvertFrom(value);
            }
            return Convert.IsDBNull(value) ? null : value;
        }
        #endregion

批操作实现方法：

public static int BatchInsert<T>(string connectString, List<T> list, string tableName, string[] columns = null, int onceNum = 100)
        {
            if (list == null || list.Count == 0) throw new Exception("没有设置要插入的数据");
            int obj = 0;
            StringBuilder sqlBuiler = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] propertys = t.GetProperties();
            if (propertys == null || propertys.Length == 0) return 0;
            List<PropertyInfo> propertys_insert = new List<PropertyInfo>();
            if (columns == null || columns.Length == 0)
            {
                //指定修改字段为空时，默认修改实体中除了条件属性外的其他所有属性对应的字段
                propertys_insert = propertys.ToList();
            }
            else
            {
                string[] columnsLower = string.Join(",", columns).ToLower().Split(',');
                foreach (var item in propertys)
                {
                    if (columnsLower.Contains(item.Name.ToLower()))
                    {
                        propertys_insert.Add(item);
                    }
                }
            }
            if (propertys_insert==null||propertys_insert.Count==0) throw new Exception("没有任何要插入的列");
            int i = 0;
            StringBuilder ziduans = new StringBuilder();
            for (int j = 0; j < propertys_insert.Count; j++)
            {
                ziduans.Append($"{propertys_insert[j].Name},");
            }
            sqlBuiler.Append($" insert into {tableName} ({ziduans.ToString().TrimEnd(',')}) values");
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                MySqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                using (MySqlCommand cmd = new MySqlCommand(sqlBuiler.ToString(), conn, tran))
                {
                    foreach (T row in list)
                    {
                        i++;
                        sqlBuiler.Append($"(");
                        for (int j = 0; j < propertys_insert.Count; j++)
                        {
                            if (j == propertys_insert.Count - 1)
                            {
                                sqlBuiler.Append($"@{propertys_insert[j].Name}_{i} ");
                            }
                            else
                            {
                                sqlBuiler.Append($"@{propertys_insert[j].Name}_{i},");
                            }
                            cmd.Parameters.Add(new MySqlParameter("@" + propertys_insert[j].Name + "_" + i, propertys_insert[j].GetValue(row)));
                        }
                        sqlBuiler.Append($"),");
                        if (i % onceNum == 0 || i == list.Count)
                        {
                            try
                            {
                                //执行sql
                                cmd.CommandText = sqlBuiler.ToString().TrimEnd(',');
                                obj += cmd.ExecuteNonQuery();
                                tran.Commit();
                                if (i < list.Count)
                                {
                                    tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                                }
                                sqlBuiler.Clear();
                                sqlBuiler.Append($" insert into {tableName} ({ziduans.ToString().TrimEnd(',')}) values");
                                cmd.Parameters.Clear();
                            }
                            catch (Exception)
                            {
                                tran.Rollback();
                                throw;
                            }

                        }
                    }
                }

            }
            return obj;
        }

        public static int BatchInsert(string connectString, DataTable dt, string tableName, string[] columns = null, int onceNum = 100)
        {
            if (dt == null || dt.Rows == null || dt.Rows.Count == 0) throw new Exception("没有设置要插入的数据");
            int obj = 0;
            StringBuilder sqlBuiler = new StringBuilder();
            List<string> ufilds = new List<string>(); //所有要修改的字段
            DataColumnCollection coll = dt.Columns;
            string[] columnsLower = null;
            if (columns != null && columns.Length > 0)
            {
                columnsLower = string.Join(",", columns).ToLower().Split(',');
            }
            //指定了要插入的列时按照指定列插入，没有指定插入的列时把dt中所有列全部插入
            foreach (DataColumn item in coll)
            {
                if (columnsLower != null && columnsLower.Length > 0)
                {
                    if (columnsLower.Contains(item.ColumnName.ToLower()))
                    {
                        ufilds.Add(item.ColumnName);
                    }
                }
                else
                {
                    ufilds.Add(item.ColumnName);
                }
            }
            if (ufilds == null || ufilds.Count == 0)
            {
                throw new Exception("没有任何要插入的列");
            }
            int i = 0;
            StringBuilder ziduans = new StringBuilder();
            for (int j = 0; j < ufilds.Count; j++)
            {
                ziduans.Append($"{ufilds[j]},");
            }
            sqlBuiler.Append($" insert into {tableName} ({ziduans.ToString().TrimEnd(',')}) values");
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                MySqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                using (MySqlCommand cmd = new MySqlCommand(sqlBuiler.ToString(), conn, tran))
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        i++;
                        sqlBuiler.Append("(");
                        for (int j = 0; j < ufilds.Count; j++)
                        {
                            if (j == ufilds.Count - 1)
                            {
                                sqlBuiler.Append($"@{ufilds[j]}_{i}");
                            }
                            else
                            {
                                sqlBuiler.Append($"@{ufilds[j]}_{i},");
                            }
                            cmd.Parameters.Add(new MySqlParameter("@" + ufilds[j] + "_" + i, row[ufilds[j]]));
                        }
                        sqlBuiler.Append("),");
                        if (i % onceNum == 0 || i == dt.Rows.Count)
                        {
                            try
                            {
                                cmd.CommandText = sqlBuiler.ToString().TrimEnd(',');
                                obj += cmd.ExecuteNonQuery();
                                tran.Commit();
                                if (i < dt.Rows.Count)
                                {
                                    tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                                }
                                sqlBuiler.Clear();
                                sqlBuiler.Append($" insert into {tableName} ({ziduans.ToString().TrimEnd(',')}) values");
                                cmd.Parameters.Clear();
                            }
                            catch (Exception)
                            {
                                tran.Rollback();
                                throw;
                            }
                        }
                    }
                }
            }
            return obj;
        }

        /// <summary>
        /// 批量修改，使用了事务
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connectString">数据库连接字符串</param>
        /// <param name="list">要修改的数据集</param>
        /// <param name="tableName">要修改表名</param>
        /// <param name="primaryKeyName">修改时的条件字段，一般修改是按照主键修改的比较多</param>
        /// <param name="columns">要修改的字段名称，
        /// 如果为空则把T类型中的除primaryKeyName属性之外的所有属性和数据库字段对应进行修改，
        /// 属性名称和数据库字段对应时不区分大小写</param>
        /// <param name="onceNum">一次性提交的最大数量，因为参数有2100个的限制，所以批量时会分多次进行提交，onceNum设置每次提交的数量</param>
        /// <returns>影响数据库的条数</returns>
        public static int BatchUpdate<T>(string connectString, List<T> list, string tableName, string primaryKeyName, string[] columns = null, int onceNum = 100)
        {
            if (list == null || list.Count == 0) return 0;
            int obj = 0;
            StringBuilder sqlBuiler = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] propertys = t.GetProperties();
            if (propertys == null || propertys.Length == 0) return 0;
            PropertyInfo wherep = propertys.Where(s => s.Name.ToLower() == primaryKeyName.ToLower()).ToArray()[0]; //拼接修改条件的属性
            List<PropertyInfo> propertys_update = new List<PropertyInfo>();
            if (columns == null || columns.Length == 0)
            {
                //指定修改字段为空时，默认修改实体中除了条件属性外的其他所有属性对应的字段
                propertys_update = propertys.Where(s => s.Name.ToLower() != primaryKeyName.ToLower()).ToList();
            }
            else
            {
                string[] columnsLower = string.Join(",", columns).ToLower().Split(',');
                foreach (var item in propertys)
                {
                    if (columnsLower.Contains(item.Name.ToLower()) && item.Name.ToLower() != primaryKeyName.ToLower())
                    {
                        propertys_update.Add(item);
                    }
                }
            }
            int i = 0;
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                MySqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted); ;
                using (MySqlCommand cmd = new MySqlCommand(sqlBuiler.ToString(), conn, tran))
                {
                    foreach (T row in list)
                    {
                        i++;
                        sqlBuiler.Append($" update {tableName} Set ");
                        for (int j = 0; j < propertys_update.Count; j++)
                        {
                            if (j == propertys_update.Count - 1)
                            {
                                sqlBuiler.Append($"{propertys_update[j].Name}=@{propertys_update[j].Name}_{i} ");
                            }
                            else
                            {
                                sqlBuiler.Append($"{propertys_update[j].Name}=@{propertys_update[j].Name}_{i},");
                            }
                            cmd.Parameters.Add(new MySqlParameter("@" + propertys_update[j].Name + "_" + i, propertys_update[j].GetValue(row)));
                        }
                        sqlBuiler.Append($" where {primaryKeyName}=@{primaryKeyName}_{i};");
                        cmd.Parameters.Add(new MySqlParameter("@" + primaryKeyName + "_" + i, wherep.GetValue(row)));
                        if (i % onceNum == 0 || i == list.Count)
                        {
                            try
                            {
                                //执行sql
                                cmd.CommandText = sqlBuiler.ToString();
                                obj += cmd.ExecuteNonQuery();
                                tran.Commit();
                                if (i < list.Count)
                                {
                                    tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                                }
                                sqlBuiler.Clear();
                                cmd.Parameters.Clear();
                            }
                            catch (Exception)
                            {
                                tran.Rollback();
                                throw;
                            }

                        }
                    }
                }

            }
            return obj;
        }
        /// <summary>
        /// 批量修改，使用了事务
        /// </summary>
        /// <param name="connectString">数据库连接字符串</param>
        /// <param name="dt">要修改的数据源</param>
        /// <param name="tableName">要修改的表名</param>
        /// <param name="primaryKeyName">修改时的条件字段，一般是按照主键修改比较多</param>
        /// <param name="columns">要修改的字段名称
        /// 如果为空则把dt中所有列和数据表字段对应，修改除条件列外的所有列，
        /// 列明和数据库字段对应时不区分大小写</param>
        /// <param name="onceNum">一次性提交的最大数量，因为参数有2100个的限制，所以批量时会分多次进行提交，onceNum设置每次提交的数量</param>
        /// <returns></returns>
        public static int BatchUpdate(string connectString, DataTable dt, string tableName, string primaryKeyName, string[] columns = null, int onceNum = 100)
        {
            if (dt == null || dt.Rows == null || dt.Rows.Count == 0) return 0;
            int obj = 0;
            StringBuilder sqlBuiler = new StringBuilder();
            List<string> ufilds = new List<string>(); //所有要修改的字段
            DataColumnCollection coll = dt.Columns;
            string whereName = primaryKeyName;
            string[] columnsLower = null;
            if (columns != null && columns.Length > 0)
            {
                columnsLower = string.Join(",", columns).ToLower().Split(',');
            }

            foreach (DataColumn item in coll)
            {
                if (item.ColumnName.ToLower() != primaryKeyName.ToLower())
                {
                    if (columnsLower != null && columnsLower.Length > 0)
                    {
                        if (columnsLower.Contains(item.ColumnName.ToLower()))
                        {
                            ufilds.Add(item.ColumnName);
                        }
                    }
                    else
                    {
                        ufilds.Add(item.ColumnName);
                    }
                }
                else
                {
                    whereName = item.ColumnName;
                }
            }
            if (ufilds == null || ufilds.Count == 0)
            {
                return 0;
            }
            int i = 0;
            using (MySqlConnection conn = new MySqlConnection(connectString))
            {
                conn.Open();
                MySqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                using (MySqlCommand cmd = new MySqlCommand(sqlBuiler.ToString(), conn, tran))
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        i++;
                        sqlBuiler.Append($" update {tableName} Set ");
                        for (int j = 0; j < ufilds.Count; j++)
                        {
                            if (j == ufilds.Count - 1)
                            {
                                sqlBuiler.Append($"{ufilds[j]}=@{ufilds[j]}_{i} ");
                            }
                            else
                            {
                                sqlBuiler.Append($"{ufilds[j]}=@{ufilds[j]}_{i},");
                            }
                            cmd.Parameters.Add(new MySqlParameter("@" + ufilds[j] + "_" + i, row[ufilds[j]]));
                        }
                        sqlBuiler.Append($" where {primaryKeyName}=@{primaryKeyName}_{i};");
                        cmd.Parameters.Add(new MySqlParameter("@" + primaryKeyName + "_" + i, row[whereName]));
                        if (i % onceNum == 0 || i == dt.Rows.Count)
                        {
                            try
                            {
                                cmd.CommandText = sqlBuiler.ToString();
                                obj += cmd.ExecuteNonQuery();
                                tran.Commit();
                                if (i < dt.Rows.Count)
                                {
                                    tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                                }
                                sqlBuiler.Clear();
                                cmd.Parameters.Clear();
                            }
                            catch (Exception)
                            {
                                tran.Rollback();
                                throw;
                            }
                        }
                    }
                }
            }
            return obj;
        }

 

suiersuier
关注
mysqlHelper和sqlhelper
11-27
操作mysql和mssql的辅助类库。方便快速的操作mysql数据库 mysqlhelper.cs sqlhelper.cs
用于Mysql操作的MySqlHelper类
diehe5608的博客
08-04 437
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Text; using System.Data; using System.Collections; using MySql.Data.Common; using MySql.D...
mysql的helper_mysqlhelper
weixin_39882271的博客
01-19 481
Python3连接 MySQL 服务器使用PyMySQL，Python2中使用mysqldb。PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。数据库操作的主要方法如下：connect对象有如下方法:close()：关闭此connect对象, 关闭后无法再进行操作，除非再次创建连接commit()：提交当前事务，如果是支持事...
MySQLHelper
吴晓如
11-27 1129
引言：     最近在做积分系统，搭完框架之后，写了一条线怎么都不通，找了半天原因觉得很搞笑，我们连接的是mysql数据库，在D层写的确实sqlHelper，难怪啊，然后上网查了MySQLHelper怎么写，积累一下自己的代码库。 代码如下： using System; using System.Configuration; using MySql.Data.MySqlClient;
MySQLHelper类 C#
08-20
MySQLHelper类在C#编程中是一个非常实用的工具，它为开发者提供了方便的接口来连接和操作MySQL数据库。这个类通常包含了一系列静态方法，用于执行SQL查询、存储过程或者事务处理，大大简化了数据库交互的过程。下面...
MySqlHelper.cs
09-06
使用C# 进行连接MySQL的帮助类，可是快速实现查询、插入等操作
MysqlHelper.zip
11-11
《MySQLHelper：数据库操作的得力助手》 在软件开发中，数据库操作是不可或缺的一环。MySQL作为一款广泛应用的关系型数据库管理系统，其高效、稳定、开源的特性深受开发者喜爱。而MysqlHelper类库，正是为了简化...
MySqlHelper C#连接MYSQL类
08-05
MySqlHelper C#连接MYSQL类,自己的项目就是使用的类。
Mysql Helper 最好用的 MysqlHelper.cs （MySql.Data.dll）
08-15
最好用的 MysqlHelper.cs，Mysql Helper 本人多次改进和调整，处理了MySql.Data.dll 中的多个bug导致的程序异常，执行快速，使用方便。【来自金花工具】
MySQL Help
06-07
MySQL html help (SQL, C/PHP API, ...)
MySqlHelper.rar
11-14
此工具类用于.net/c#编程与MySql数据库的交互，直接将MySqlHelper.cs加入到项目，并添加引用MySql.Data.dll，在操作数据库时直接MySqlHelper.Execute方法即可，附件中包含了MySql.Data.dll文件和Demo.cs可供调用参考。
MysqlHelper——一个便捷的MySQL导入导出的助手类库
zjkal的专栏
09-02 513
是一个便捷的的工具,可以快速实现mysql的数据库的导入和导出.
MysqlHelper
apple151128的博客
01-21 333
using MySql.Data.MySqlClient; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; namespace HTBDataCreateH
C#中的MySqlHelper工具类及使用方法
u012767263的专栏
06-29 1726
C#中的MySqlHelper工具类及使用方法 工具类# 工具类转自C# MysqlHelper C#连接mysql数据库类库全，代码如下： using MySql.Data.MySqlClient; using System; using System.Data; namespace ConsoleApp { public class MySqlHelper { /// <summary> /// 获取一个有效的数据
mysqlhelper
weixin_34040079的博客
03-06 171
Python3连接 MySQL 服务器使用PyMySQL，Python2中使用mysqldb。PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。数据库操作的主要方法如下：connect对象有如下方法:close()：关闭此connect对象, 关闭后无法再进行操作，除非再次创建连接commit()：提...
mysql help类_MySQL 帮助类 MySQLHelper
weixin_33925126的博客
01-19 543
/// ///MySqlHelper操作类/// public sealed partial classMySQLHelper{public static readonly string DBConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[""].ToString();/// ///批量操...
c# mysql
solo2k的博客
04-16 715
using System; using System.Configuration; using System.Collections; using System.Data; using MySql.Data; using MySql.Data.MySqlClient; namespace MyData { /// /// 通用数据库类MySQL  /// public c
mysqlhelper c++
最新发布
10-29
mysqlhelper c是一个C语言编写的MySQL数据库操作工具。它提供了一系列函数和接口，使得在C语言中操作MySQL数据库变得更加方便和简单。 mysqlhelper c的主要功能包括连接MySQL数据库、执行SQL语句、获取查询结果等。通过提供封装好的函数和接口，用户可以通过简单的调用来完成各种数据库操作，无需编写冗长的代码。 对于连接数据库，mysqlhelper c提供了mysqlhelper_connect函数，可以指定主机名、用户名、密码和数据库名来建立与MySQL数据库的连接。连接建立成功后，可以通过mysqlhelper_query函数来执行SQL语句，例如插入、更新和删除等操作。而对于select查询语句，mysqlhelper c提供了mysqlhelper_query_select函数，可以获得查询结果，并通过遍历结果集来获取具体的数据。 此外，mysqlhelper c还提供了一些辅助函数，例如mysqlhelper_affected_rows函数可以获取最后一次查询影响的行数，mysqlhelper_insert_id函数可以获取最后一次插入操作生成的自增ID等。 总之，mysqlhelper c是一个简洁高效的C语言MySQL数据库操作工具，通过它可以轻松实现与MySQL数据库的交互，方便快捷地进行数据查询和操作。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值