封装List集合一个批量导入数据库的工具类

     public class CommonDal

     {

         #region 数据导入相关

         /// <summary>

         /// 批量导入数据

         /// </summary>

         /// <param name="dt">将要导入的数据源</param>

         /// <param name="TbName">目标表名称</param>

         public static void Bulk_ImportDatable(DataTable dt, string TargetTableName)

         {

             using (SqlConnection destinationConnection = DBHelper.CreateConnection())

             {

                 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))

                 {

                     try

                     {

                         bulkCopy.DestinationTableName = TargetTableName;//要插入的表的表明

                         for (int i = ; i < dt.Columns.Count; i++)

                         {

                             bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);

                         }

                         bulkCopy.WriteToServer(dt);

                     }

                     catch (Exception ex)

                     {

                         Console.WriteLine(ex.Message);

                     }

                 }

             }

         }

         #endregion

         #region 数据表操作相关

         #region 增

         /// <summary>

         /// 向数据表中插入一条数据

         /// </summary>

         /// <typeparam name="T">插入数据的参数类型,注意名称和表明保持一致</typeparam>

         /// <param name="obj">插入的数据对象</param>

         /// <returns></returns>

         public int InsertInfo<T>(T obj) where T : class

         {

             using (IDbConnection conn = DBHelper.CreateConnection())

             {

                 return conn.Insert<T>(obj);

             }

         }

         #endregion

         #region 删除

         /// <summary>

         /// 通过表名称truncate掉表数据

         /// </summary>

         /// <param name="tablename">需要清除数据的表名称</param>

         /// <returns></returns>

         public bool truncateTableByName(string tablename)

         {

             using (IDbConnection conn = DBHelper.CreateConnection())

             {

                 string sql = " TRUNCATE TABLE " + tablename;

                 DynamicParameters param = new DynamicParameters();

                 param.Add("TableName", tablename);

                 return conn.Execute(sql, param) > ;

             }

         }

         /// <summary>

         /// 将主表数据清除并且清除部分关联信息表的关联数据

         /// </summary>

         /// <param name="truncateTableName">要清除数据的主表名称</param>

         /// <param name="deleteTableName">要清除关联数据的关联信息表名称</param>

         /// <param name="sqlWhereList">清除关联信息数据的Where条件</param>

         public void truncateAndDeleteTable(string truncateTableName, string deleteTableName, List<SqlWhereModel> sqlWhereList)

         {

             truncateTableByName(truncateTableName);

             deleteTableByName(deleteTableName, sqlWhereList);

         }

         /// <summary>

         /// 通过表名称和Where条件delete掉表数据

         /// </summary>

         /// <param name="TableName">需要清除数据的表名称</param>

         /// <param name="paramModel">Where参数集合</param>

         /// <returns></returns>

         public bool deleteTableByName(string TableName, List<SqlWhereModel> paramModel)

         {

             using (IDbConnection conn = DBHelper.CreateConnection())

             {

                 //sql语句拼接时使用StringBulider 防止长度限制

                 StringBuilder sql = new StringBuilder("delete from " + TableName);

                 StringBuilder sbwhere = new StringBuilder(" where 1=1 ");

                 DynamicParameters parameters = new DynamicParameters();

                 paramModel.ForEach(p =>

                 {

                     sbwhere.AppendLine(" and " + p.ParamName + " = @" + p.ParamValue);

                     parameters.Add(p.ParamValue);

                 });

                 return conn.Execute(sql.AppendLine(sbwhere.ToString()).ToString(), parameters) > ;

             }

         }

         /// <summary>

         /// 通过表名称delete掉表数据

         /// </summary>

         /// <param name="TableName">需要清除数据的表名称</param>

         /// <returns></returns>

         public bool deleteTableByName(string TableName)

         {

             using (IDbConnection conn = DBHelper.CreateConnection())

             {

                 //sql语句拼接时使用StringBulider 防止长度限制

                 StringBuilder sql = new StringBuilder(@"delete from " + TableName);

                 return conn.Execute(sql.ToString()) > ;

             }

         }

         #endregion

         #region 修改

         #endregion

         #region 查询

         /// <summary>

         /// 通过id判断是否存在记录

         /// </summary>

         /// <param name="dc">传入id</param>

         /// <returns>返回泛型对象</returns>

         public T getExistsById<T>(long id) where T : class

         {

             using (IDbConnection coon = DBHelper.CreateConnection())

             {

                 return coon.Get<T>(id);

             }

         }

         /// <summary>

         /// 通过name查询是否存在

         /// </summary>

         /// <typeparam name="T"></typeparam>

         /// <param name="Name"></param>

         /// <returns></returns>

         public T getExistsByName<T>(string Name) where T : dtModelBase

         {

             using (IDbConnection coon = DBHelper.CreateConnection())

             {

                 var predicate = Predicates.Field<T>(f => f.Name, Operator.Eq, Name);

                 IEnumerable<T> list = coon.GetList<T>(predicate);

                 return list.FirstOrDefault();

             }

         }

         /// <summary>

         /// 通过标准名Ename查询是否存在

         /// </summary>

         /// <typeparam name="T"></typeparam>

         /// <param name="Name"></param>

         /// <returns></returns>

         public T getExistsByEName<T>(string EName, string Tbname) where T : class

         {

             using (IDbConnection coon = DBHelper.CreateConnection())

             {

                 string sql = "select * from " + Tbname + " where EName =@EName";

                 IEnumerable<T> list = coon.Query<T>(sql, new { EName });

                 return list.FirstOrDefault();

             }

         }

         /// <summary>

         /// 读取数据源

         /// </summary>

         /// <returns></returns>

         public List<T> GetSourceDt<T>() where T : class

         {

             IEnumerable<T> list = null;

             using (IDbConnection conn = DBHelper.CreateConnection())

             {

                 list = conn.GetList<T>().ToList();

             }

             return list.ToList();

         }

         #endregion

         #endregion

     }

  public class SqlWhereModel

     {

         public string ParamName { get; set; }

         public string ParamValue { get; set; }

     }

结合上一篇List和DataTable相互转换的类直接进行数据库批量插入操作调用示例

   DataTable dt = ListTranDataTableHelper.ToDataTable(listv, DatableProperty.PropertyRenameDic);

             CommonDal.Bulk_ImportDatable(dt, targetTableName);

巴特西

封装List集合一个批量导入数据库的工具类

最新文章

热门文章