SqlHelper类的封装

最新推荐文章于 2022-12-21 17:27:37 发布
最新推荐文章于 2022-12-21 17:27:37 发布
阅读量333 收藏
点赞数
文章标签： c# 数据库 ui
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yu240956419/article/details/124625495
版权
这是一个.NET框架下的数据库操作类库，提供了包括创建事务、执行SQL（NonQuery、Query、Scalar）、分页查询以及SqlBulkCopy批量更新数据的功能。类库支持多种数据库操作，如在事务中执行SQL语句，返回受事务影响的行数，执行查询并返回DataSet或DataTable，以及执行分页查询等。此外，还包含了对数据库连接、事务处理和错误处理的逻辑。
摘要由CSDN通过智能技术生成 
using System;
using System.Data;
using System.Data.Common;
using System.Text;
 
using System.Collections.Generic;
using System.Reflection;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.ComponentModel;

namespace ConsoleApp3
{
    /// <summary>
    /// 数据库操作基类
    /// </summary>
    public class SqlHelper  
    {
        #region Transaction
        public DbTransaction CreateDbTransaction(string connectionString)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            return tran;
        }
        #endregion

        #region ExequteNonQuery
        /// <summary>
        /// 执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                int val = 0;
                SqlCommand cmd = conn.CreateCommand();
                if (cmdType == CommandType.StoredProcedure)
                {

                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    val = cmd.ExecuteNonQuery();
                }
                else
                {
                    SqlTransaction tran = conn.BeginTransaction();
                    try
                    {
                        PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                        val = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        throw ex;
                    }
                }
                return val;
            }
        }


        /// <summary>
        /// 在事务中执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        #endregion

        #region ExecuteQuery
        /// <summary>
        /// 执行查询，返回DataSet
        /// </summary>
        public DataSet ExecuteQuery(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        return ds;
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询，返回DataTable
        /// </summary>
        public DataTable ExecuteQueryToDataTable(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        da.Fill(ds);
                        cmd.Parameters.Clear();
                        return ds;
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询，返回DataTable
        /// </summary>
        public DataTable ExecuteQueryToDataTable(string connectionString, CommandType cmdType, string cmdText, bool withKey,
            params DbParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataTable ds = new DataTable();
                        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                        da.Fill(ds);
                        cmd.Parameters.Clear();
                        return ds;
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询，返回DataTable
        /// </summary>
        public void ExecuteQueryToDataTable(string connectionString, DataTable dt, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                    }
                }
            }
        }

        /// <summary>
        /// 在事务中执行查询，返回DataSet
        /// </summary>
        public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "ds");
            cmd.Parameters.Clear();
            return ds;
        }
        #endregion

        #region Execute Reader
        /// <summary>
        /// 执行查询，返回DataReader
        /// </summary>
        public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return rdr;
                }
                catch (Exception e)
                {
                    conn.Close();
                    throw e;
                }
            }
        }
        /// <summary>
        /// 在事务中执行查询，返回DataReader
        /// </summary>
        public DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;
        }

        #endregion

        #region Execute Scalar
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 在事务中执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        public object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
        #endregion

        #region Page List
        /// <summary>
        /// 获取分页SQL
        /// </summary>
        /// <param name="strCondition">条件</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="fldSort">排序字段（最后一个不需要填写正序还是倒序，例如：id asc, name）</param>
        /// <param name="tblName">表名</param>
        /// <param name="sort">最后一个排序字段的正序或倒序（true为倒序，false为正序）</param>
        /// <returns>返回用于分页的SQL语句</returns>
        private string GetPagerSQL(string condition, int pageSize, int pageIndex, string fldSort,
            string tblName, bool sort)
        {
            string strSort = sort ? " DESC" : " ASC";

            if (pageIndex == 1)
            {
                return "select top " + pageSize.ToString() + " * from " + tblName.ToString()
                    + ((string.IsNullOrEmpty(condition)) ? string.Empty : (" where " + condition))
                    + " order by " + fldSort.ToString() + strSort;
            }
            else
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat("select top {0} * from {1} ", pageSize, tblName);
                strSql.AppendFormat(" where {1} not in (select top {0} {1} from {2} ", pageSize * (pageIndex - 1),
                    (fldSort.Substring(fldSort.LastIndexOf(',') + 1, fldSort.Length - fldSort.LastIndexOf(',') - 1)), tblName);
                if (!string.IsNullOrEmpty(condition))
                {
                    strSql.AppendFormat(" where {0} order by {1}{2}) and {0}", condition, fldSort, strSort);
                }
                else
                {
                    strSql.AppendFormat(" order by {0}{1}) ", fldSort, strSort);
                }
                strSql.AppendFormat(" order by {0}{1}", fldSort, strSort);
                return strSql.ToString();
            }
        }
        /// <summary>
        /// Top 分页For Sql server 2000
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="tblName"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="fldSort"></param>
        /// <param name="sort"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetPageListTable(string connectionString, string tblName, int pageSize,
            int pageIndex, string fldSort, bool sort, string condition, out int pageCount, out int count)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT count(0) from " + tblName + ";");
            strSql.AppendLine(GetPagerSQL(condition, pageSize, pageIndex, fldSort, tblName, sort));
            DataSet ds = ExecuteQuery(connectionString, CommandType.Text, strSql.ToString());
            count = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            pageCount = Convert.ToInt32(Math.Ceiling((double)count / (double)pageSize));
            return ds.Tables[1];

        }
        /// <summary>
        /// 分页获取数据
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="tblName">表名</param>
        /// <param name="fldName">字段名</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="fldSort">排序字段</param>
        /// <param name="sort">升序{False}/降序(True)</param>
        /// <param name="condition">条件(不需要where)</param>
        public DbDataReader GetPageList(string connectionString, string tblName, int pageSize,
            int pageIndex, string fldSort, bool sort, string condition)
        {
            string sql = GetPagerSQL(condition, pageSize, pageIndex, fldSort, tblName, sort);
            return ExecuteReader(connectionString, CommandType.Text, sql, null);
        }
        /// <summary>
        /// 分页获取数据(Sql Server 2005) for ROW_NUMBER()
        /// </summary>
        /// <param name="connectionString">数据库链接</param>
        /// <param name="sql">获取数据集的Sql</param>
        /// <param name="fldSort">排序字段,可以多个</param>
        /// <param name="pageSize">每页显示多少条</param>
        /// <param name="curPage">当前页码</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="count">总记录数</param>
        ///<param name="cmdParms">DbParameter</param>
        /// <returns>DataTable</returns>
        public DataTable PageList(string connectionString, string sql, string fldSort, int pageSize, int curPage, out int pageCount, out int count, params DbParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(@"SELECT count(0) from ({0}) as MyTableCount;
                                    select * from (
                                    SELECT ROW_NUMBER() OVER({1}) PageRowNumber,* 
                                    from ({0}) mytable
                                    ) mytable2
                                    where PageRowNumber between {2} and {3}"
                               , sql, fldSort, Convert.ToString((curPage - 1) * pageSize + 1), Convert.ToString((curPage * pageSize)));

            DataSet ds = ExecuteQuery(connectionString, CommandType.Text, strSql.ToString(), cmdParms);
            count = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            pageCount = Convert.ToInt32(Math.Ceiling((double)count / (double)pageSize));
            return ds.Tables[1];
        }

        /// <summary>
        /// 分页获取数据(Sql Server 2005) for ROW_NUMBER()
        /// </summary>
        /// <param name="connectionString">数据库链接</param>
        /// <param name="sql">获取数据集的Sql</param>
        /// <param name="fldSort">排序字段,可以多个</param>
        /// <param name="pageSize">每页显示多少条</param>
        /// <param name="curPage">当前页码</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="count">总记录数</param>
        ///<param name="cmdParms">DbParameter</param>
        /// <returns>DataTable</returns>
        public DataTable PageListTask(string connectionString, string sql, string fldSort, int pageSize, int curPage, out int pageCount, out int count, params DbParameter[] cmdParms)
        {
            DataTable dt = null;
            int _count = 0;
            int _pageCount = 0;
            var taskList = new List<Task>();
            taskList.Add(Task.Run(() => {
                StringBuilder strSqlCount = new StringBuilder();
                strSqlCount.AppendFormat(@"SELECT count(0) from ({0}) as MyTableCount;"
                                   , sql);
                DataSet dsCount = ExecuteQuery(connectionString, CommandType.Text, strSqlCount.ToString(), cmdParms);
                _count = Convert.ToInt32(dsCount.Tables[0].Rows[0][0]);
                _pageCount = Convert.ToInt32(Math.Ceiling((double)_count / (double)pageSize));
            }));
            taskList.Add(Task.Run(() => {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendFormat(@"select * from (
                                    SELECT ROW_NUMBER() OVER({1}) PageRowNumber,* 
                                    from ({0}) mytable
                                    ) mytable2
                                    where PageRowNumber between {2} and {3}"
                                   , sql, fldSort, Convert.ToString((curPage - 1) * pageSize + 1), Convert.ToString((curPage * pageSize)));

                DataSet ds = ExecuteQuery(connectionString, CommandType.Text, strSql.ToString(), cmdParms);
                dt = ds.Tables[0];
            }));
            Task.WaitAll(taskList.ToArray());
            count = _count;
            pageCount = _pageCount;
            return dt;
        }

        /// <summary>
        /// 分页获取数据(Sql Server 2005) for ROW_NUMBER()
        /// </summary>
        /// <param name="connectionString">数据库链接</param>
        /// <param name="sql">获取数据集的Sql</param>
        /// <param name="fldSort">排序字段,可以多个</param>
        /// <param name="pageSize">每页显示多少条</param>
        /// <param name="curPage">当前页码</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="count">总记录数</param>
        ///<param name="cmdParms">DbParameter</param>
        /// <returns>DataTable</returns>
        public DataTable PageListTest(string connectionString, string sql, string fldSort, int pageSize, int curPage, out int pageCount, out int count, params DbParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(@"
                                    SELECT count(0) from ({0}) as MyTableCount;
                                    select *
                                    from ({0}) a
                                    {1}
                                    offset {2} rows
                                    fetch next {3} rows only"
                               , sql, fldSort, Convert.ToString((curPage - 1) * pageSize), pageSize);

            DataSet ds = ExecuteQuery(connectionString, CommandType.Text, strSql.ToString(), cmdParms);
            count = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            pageCount = Convert.ToInt32(Math.Ceiling((double)count / (double)pageSize));
            return ds.Tables[1];
        }
        /// <summary>
        /// PageList for DataReader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sql"></param>
        /// <param name="pageSize"></param>
        /// <param name="curPage"></param>
        /// <param name="pageCount"></param>
        /// <param name="count"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public DataTable PageListReader(string connectionString, CommandType cmdType, string cmdSql, int pageSize, int curPage, out int pageCount, out int count, params DbParameter[] cmdParms)
        {
            int first = 0;
            int last = 0;
            int fieldCount = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                // Log.LogHelper.Instance.Info(cmdSql);
                PrepareCommand(cmd, conn, null, cmdType, cmdSql, cmdParms);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                DataTable dt = new DataTable();
                fieldCount = reader.FieldCount;
                for (int i = 0; i < fieldCount; i++)
                {
                    DataColumn col = new DataColumn();
                    col.ColumnName = reader.GetName(i);
                    col.DataType = reader.GetFieldType(i);
                    dt.Columns.Add(col);
                }
                DataColumn PageRowNumber = new DataColumn();
                PageRowNumber.ColumnName = "PageRowNumber";
                PageRowNumber.DataType = typeof(int);
                dt.Columns.Add(PageRowNumber);

                count = 0;
                first = (curPage - 1) * pageSize + 1;
                last = curPage * pageSize;
                while (reader.Read())
                {
                    count++;
                    if (count >= first && last >= count)
                    {
                        DataRow r = dt.NewRow();
                        for (int i = 0; i < fieldCount; i++)
                        {
                            r[i] = reader[i];
                        }
                        r["PageRowNumber"] = count;
                        dt.Rows.Add(r);
                    }
                }
                reader.Close();
                if (pageSize != 0)
                    pageCount = Convert.ToInt32(Math.Ceiling((double)count / (double)pageSize));
                else
                    pageCount = 0;
                dt.AcceptChanges();
                return dt;
            }
        }



        /// <summary>
        /// PageList for DataReader
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="sql"></param>
        /// <param name="pageSize"></param>
        /// <param name="curPage"></param>
        /// <param name="pageCount"></param>
        /// <param name="count"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public List<T> PageListReader<T>(string connectionString, string sql, int pageSize, int curPage, out int pageCount, out int count, params DbParameter[] cmdParms) where T : class, new()
        {
            int first = 0;
            int last = 0;
            int fieldCount = 0;
            List<T> itemlist = null;
            itemlist = new List<T>();
            T item = null;
            Type targettype = typeof(T);
            Type ptype = null;
            Object value = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, CommandType.Text, sql, cmdParms);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                fieldCount = reader.FieldCount;
                count = 0;
                first = (curPage - 1) * pageSize + 1;
                last = curPage * pageSize;
                List<KeyValuePair<string, int>> Columns = new List<KeyValuePair<string, int>>();
                for (int i = 0; i < fieldCount; i++)
                {

                    Columns.Add(new KeyValuePair<string, int>(reader.GetName(i).ToLower(), i));
                }
                KeyValuePair<string, int> columnName;
                while (reader.Read())
                {
                    count++;
                    if (count >= first && last >= count)
                    {
                        item = new T();
                        foreach (PropertyInfo pi in targettype.GetProperties())
                        {
                            columnName = Columns.Find(t => t.Key == pi.Name.ToLower());
                            if (pi.CanWrite && columnName.Value >= 0)
                            {
                                ptype = Type.GetType(pi.PropertyType.FullName);
                                value =  TypeExtensions.ChangeType(reader[columnName.Value], ptype);
                                pi.SetValue(item, value, null);
                            }
                        }
                        itemlist.Add(item);
                    }
                }
                reader.Close();
                if (pageSize != 0)
                    pageCount = Convert.ToInt32(Math.Ceiling((double)count / (double)pageSize));
                else
                    pageCount = 0;
                return itemlist;
            }
        }


        #endregion

        #region Other
        /// <summary>
        /// 得到数据条数
        /// </summary>
        public int GetCount(string connectionString, string tblName, string condition)
        {
            StringBuilder sql = new StringBuilder("select count(0) from " + tblName);
            if (!string.IsNullOrEmpty(condition))
                sql.Append(" where " + condition);

            object count = ExecuteScalar(connectionString, CommandType.Text, sql.ToString(), null);
            return int.Parse(count.ToString());
        }
        /// <summary>
        /// 生成要执行的命令
        /// </summary>
        private void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType,
            string cmdText, DbParameter[] cmdParms)
        {
            // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
            //if (cmdParms != null && cmdParms.Length > 0)
            //    cmdText = cmdText.Replace("?", "@").Replace(":", "@");

            if (conn.State != ConnectionState.Open)

                conn.Open();

            cmd.Connection = conn;
            if (CommandType.Text == cmdType)
                cmd.CommandText = DBIsolationLevelSql.GetLevelSql(Config.IsolationLevel) + cmdText;
            else
                cmd.CommandText = cmdText;
            Config.ConsolePrintSql(this.GetType().Name, cmd.CommandText);
            cmd.CommandTimeout = 1800;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    if (parm.Value == null)
                        parm.Value = DBNull.Value;

                    parm.ParameterName = parm.ParameterName.Replace("?", "@").Replace(":", "@");
                    cmd.Parameters.Add(parm);
                }
            }
        }
        /// <summary>
        /// SqlBulkCopy 批量更新数据
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="dataTable">数据集</param>
        /// <param name="crateTemplateSql">临时表创建字段</param>
        /// <param name="updateSql">更新语句</param>
        public void BulkUpdateData(string connectionString, DataTable dataTable, string crateTemplateSql, string updateSql)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                using (var command = new SqlCommand("", conn, tran))
                {

                    try
                    {
                        //数据库并创建一个临时表来保存数据表的数据
                        command.CommandText = String.Format("  CREATE TABLE #TmpTable ({0})", crateTemplateSql);
                        command.ExecuteNonQuery();

                        //使用SqlBulkCopy 加载数据到临时表中
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.Default, tran))
                        {
                            foreach (DataColumn dcPrepped in dataTable.Columns)
                            {
                                bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                            }

                            bulkCopy.BulkCopyTimeout = 3600;
                            bulkCopy.DestinationTableName = "#TmpTable";
                            bulkCopy.WriteToServer(dataTable);
                            bulkCopy.Close();
                        }

                        // 执行Command命令 使用临时表的数据去更新目标表中的数据  然后删除临时表
                        command.CommandTimeout = 3600;
                        command.CommandText = updateSql;
                        command.ExecuteNonQuery();
                        if (tran != null)
                        {
                            tran.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        if (tran != null)
                        {
                            tran.Rollback();
                        }
                        throw ex;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        public void SqlBulkCopy(string connectionString, DataTable dt, string destinationTableName)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                using (SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.Default, tran))
                {

                    try
                    {
                        sbc.BulkCopyTimeout = 0;
                        sbc.DestinationTableName = destinationTableName;
                        sbc.WriteToServer(dt);
                        if (tran != null)
                        {
                            tran.Commit();
                        }

                    }
                    catch (Exception ex)
                    {
                        if (tran != null)
                        {
                            tran.Rollback();
                        }
                        throw ex;
                    }
                }
            }
        }
        public void SqlBulkCopy(DbTransaction trans, DataTable dt, string destinationTableName)
        {
            SqlConnection conn = trans.Connection as SqlConnection;
            SqlTransaction tran = trans as SqlTransaction;
            if (conn.State != ConnectionState.Open)
                conn.Open();
            using (SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.Default, tran))
            {
                sbc.DestinationTableName = destinationTableName;
                sbc.WriteToServer(dt);
            }
        }
        public void SqlBulkCopy(DbTransaction trans, DataTable dt, string destinationTableName, string[] columns)
        {
            SqlConnection conn = trans.Connection as SqlConnection;
            SqlTransaction tran = trans as SqlTransaction;
            if (conn.State != ConnectionState.Open)
                conn.Open();
            using (SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.Default, tran))
            {
                for (int i = 0; i < columns.Length; i++)
                {
                    sbc.ColumnMappings.Add(columns[i], columns[i]);
                }
                sbc.DestinationTableName = destinationTableName;
                sbc.WriteToServer(dt);
            }
        }
        public void SqlBulkCopy(DbTransaction trans, DataTable dt, string destinationTableName, string[,] columnMappings)
        {
            SqlConnection conn = trans.Connection as SqlConnection;
            SqlTransaction tran = trans as SqlTransaction;
            if (conn.State != ConnectionState.Open)
                conn.Open();
            using (SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.Default, tran))
            {
                for (int i = 0; i < columnMappings.Length / 2; i++)
                {
                    sbc.ColumnMappings.Add(columnMappings[i, 0], columnMappings[i, 1]);
                }
                sbc.DestinationTableName = destinationTableName;
                sbc.WriteToServer(dt);
            }
        }
        public void SqlBulkCopy(string connectionString, DataTable dt, string destinationTableName, string[,] columnMappings)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                using (SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.Default, tran))
                {

                    for (int i = 0; i < columnMappings.Length / 2; i++)
                    {
                        sbc.ColumnMappings.Add(columnMappings[i, 0], columnMappings[i, 1]);
                    }
                    sbc.DestinationTableName = destinationTableName;
                    try
                    {
                        sbc.WriteToServer(dt);
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        throw ex;
                    }

                }
            }
        }



        #endregion


    }

    /// <summary>
    /// UDX日志辅助类（调用ILogger接口实现但遇到类代码为static时候勉强使用）
    /// </summary>
    public static class TypeExtensions
    {
        /// <summary>
        /// Convert.ChangeType doesnot support Nullable<>
        /// </summary>
        /// <param name="value"></param>
        /// <param name="conversionType"></param>
        /// <returns></returns>
        public static object ChangeType(this object value, Type conversionType)
        {
            try
            {
                if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                {
                    if (value != null && value != System.DBNull.Value)
                    {
                        NullableConverter nullableConverter = new NullableConverter(conversionType);
                        conversionType = nullableConverter.UnderlyingType;
                        if (conversionType.IsEnum)
                        {
                            if (value is string)
                                return Enum.Parse(conversionType, value as string);
                            else
                                return Enum.ToObject(conversionType, value);
                        }
                        else
                        {
                            return Convert.ChangeType(value, conversionType);
                        }
                    }
                }

                if (value == null && conversionType.IsGenericType) return Activator.CreateInstance(conversionType);
                if (value == null) return null;
                if (conversionType == value.GetType()) return value;
                if (value.GetType().IsEnum && conversionType == typeof(string)) return value.ToString();
                if (conversionType.IsEnum)
                {
                    if (value is string)
                        return Enum.Parse(conversionType, value as string);
                    else
                        return Enum.ToObject(conversionType, value);
                }
                if (!conversionType.IsInterface && conversionType.IsGenericType)
                {
                    Type innerType = conversionType.GetGenericArguments()[0];
                    object innerValue = ChangeType(value, innerType);
                    return Activator.CreateInstance(conversionType, new object[] { innerValue });
                }
                if (value is string && conversionType == typeof(Guid)) return new Guid(value as string);
                if (value is string && conversionType == typeof(Version)) return new Version(value as string);
                if (!(value is IConvertible)) return value;
                return Convert.ChangeType(value, conversionType);
            }
            catch (Exception ex)
            {
              
                return null;
            }

        }
        /// <summary>
        /// 判断Guid是否为null或者Empty
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public static bool IsNullOrEmpty(this Guid? guid)
        {
            return IsNullOrEmpty(guid ?? Guid.Empty);
        }

        /// <summary>
        /// 判断Guid是否为null或者Empty
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public static bool IsNullOrEmpty(this Guid guid)
        {
            return guid == Guid.Empty;
        }
    }

    /// <summary>
    /// 枚举：数据库类型
    /// </summary>

    /// <summary>
    /// 数据库操作类型
    /// </summary>
    public class DBIsolationLevelSql
    {
        public const string ReadUncommittedSql = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;";
        public static string GetLevelSql(IsolationLevel level)
        {

            if (level == IsolationLevel.ReadUncommitted)
                return ReadUncommittedSql + System.Environment.NewLine;
            else
                return "";
        }
    }
    public class Config
    {
        public static bool PrintSql = true;
        public static IsolationLevel IsolationLevel = IsolationLevel.ReadUncommitted;
        public static void ConsolePrintSql(string objName, string sql)
        {

            if (!PrintSql) return;
            Console.WriteLine();
            Console.WriteLine($"{objName} CommandText {IsolationLevel}");
            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine(sql);
            System.Diagnostics.Debug.WriteLine(sql);
            Console.ResetColor();
        }
    }
}
落叶@Henry
关注
SQLiteHelper封装辅助类
BowenXu11的博客
06-15 280
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.Entity.Infrastructure; using System.Data.SqlClient; using System.Data.SQLite; using System.Linq; using System.Text; using System.Threading.Task
通用的MySqlDBHelper(封装)
ProteaCynaroides的博客
02-22 604
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; //引用命名空间 using MySql.Data.MySqlClient; using MySql.Data; using System.Data; namespa...
SqlHelper工具类（封装了对数据库的各种操作的方法）
04-26
文件封装了所有的关于数据库的操作. 我的这个SqlHelper.cs 是同学给我的版本,可能与其它人的不太一样,但是变动不大.要连接数据库。。SqlHelper是一个基于.NET Framework的数据库操作组件。组件中包含数据库操作方法， 工具类写的时候很费劲，但一旦写完之后，以后用起来就方便多了，所以给大家分享一下，技术宅的人其实还不是很多，我们都需 努力、共勉
SqlHelper的封装
热门推荐
有道无术，术尚可求，有术无道，止于术。
07-25 1万+
前言： SqlHelper是一个基于.NETFrameWork的数据库操作组件，组件中包含操作数据库的各种方法，他封装的目的无疑是简化重复写的数据库连接，当SqlHelper封装以后，只需要给方法传入一些参数，就可以实现对数据库的访问。 SqlHelper类通过一组静态的方法来封装数据访问功能，不能被继承和实例化。SqlHelper 类中实现的每种方法的重载，每种方法的...
sqlhelper封装
创造自我、追求无我
01-24 1192
说明：         语言类型：C#              代码功能：对SQL增、删、改语句的封装                  实现：运用了配置文件 引用名称空间： using System.Data.SqlClient; using System.Configuration; sqlhelper代码： public class SQLhelper {
SQL SERVER-SqlHelper的封装
dxm809的博客
01-25 386
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespa...
数据操作的封装--sqlhelper
天道酬勤 地道酬善 人道酬诚
06-24 2122
为了提高软件的灵活性和可维护性，软件的代码需要科学的管理，我们引入了架构这个词。设计模式提醒我们，软件中重复性的代码需要封装起来。最近在做收费系统时，需要和数据库进行频繁的联系，既然是重复的使用，就需要封装，这里使用到了sql helper 。
ADO.NET自己封装SqlHelper类
04-21
下面我们将详细讨论如何使用C#语言封装一个SqlHelper类，以及其核心知识点。 首先，我们要理解ADO.NET的基础组件：Connection（连接）、Command（命令）、DataAdapter（数据适配器）和DataSet（数据集）。SqlHelper...
Sqlhelper封装类
03-07
应用于.net平台下代码编写 封装代码，方便调用 仅供参考使用
C#实现的封装CURD到SqlHelper类用法简单分析
08-28
本文将详细解析如何在C#中封装一个SqlHelper类，并结合App.config文件配置数据库连接，以及讲解其主要功能和使用方法。 首先，数据库连接字符串是访问数据库的关键信息，通常存储在项目的配置文件App.config中。在...
封装SqlHelper
10-20
封装SqlHelper,普通野，个个都有，个个都可以用，唔知岩唔岩的。
SqlHelper类1（封装好的ADO.NETSQLHELPER类）
03-29
经常调用ado.net的程序员，每次都要new Connection对象、Command对象。甚是麻烦，敲了个SQLHelper类，供初学者下载使用。 是针对SqlServer数据库的。 里头包含了五大对象、ado.net的基本操作够用了，(*^__^*) 嘻嘻…
c#中SqlHelper封装SqlDataReader的方法
09-03
下面是一个简单的SqlHelper类中封装ExecuteReader方法的例子： ```csharp public static class SqlHelper { private static string constr = "YourConnectionString"; // 这里替换为实际的数据库连接字符串 /// ...
详解使用C#编写SqlHelper类
08-29
在本文中，我们将一步步地创建一个SqlHelper类，封装了一些常用的数据库操作方法。 首先，我们需要配置连接字符串。在app.config文件中，我们需要添加以下节点： ```xml 数据库地址;uid=用户名;pwd=密码;...
C# 封装sqlhelper 数据库
最新发布
m0_47020592的博客
12-21 495
1.封装sqlhelper 类。
Sqlhelper类的封装 极简说明
adomCC的博客
03-23 281
Sqlhelper类 Sqlhelper类 简单来说就是 在类中封装一些方法来实现对数据库的一些简单操作 方便使用。 Sqlhelper类的封装 这些方法我使用的是通过执行sql语句来实现数据库的一些简单操作。 增 删 改 在这里片 ...
C#的SQLHelper封装
内事问度娘，外事问谷歌！
03-19 251
using System; using System.Collections.Generic; using System.Text; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Collections;   namespace Utility...
[SqlServer数据库]封装SqlHelper
qq_43571448的博客
07-07 269
封装SqlHelper类,为类库做准备 执行增（insert） 删（delete） 改（update） ExecuteQuery() 需要返回一个单个的值 ExecuteScalar() 需要查询多行多列 ExecuteReader() ReadOnly修饰的变量只能在初始化和构造函数时赋值，其余时候只能读不能改 添加 新建项 应用程序配置文...
C#封装SqlHelper类简化数据库操作
"C#中的SqlHelper类是一个实用工具类，用于简化与数据库的交互，特别是在使用ADO.NET时。这个类通常包含各种方法，将常见的数据库操作如...同时，由于SqlHelper类的封装，我们可以更容易地管理和维护数据库访问代码。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

落叶@Henry

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值