示例#1
0
        public void DataEdit()
        {
            var dataList    = CommonHelper.GetTestListDto(5000);
            var dataListTwo = CommonHelper.GetTestListDtoTwo(5000);
            var table       = SqlBulkCopyHelper.ListToTable(dataList, "BulkTest");
            var tableTwo    = SqlBulkCopyHelper.ListToTable(dataListTwo, "BulkTestTwo");

            var tableUpdate    = SqlCoreHelper.ExecuteDataSetText("select top 5000 Id,FieldD from BulkTest order by FieldU asc", null).Tables[0];
            var tableTwoUpdate = SqlCoreHelper.ExecuteDataSetText("select top 5000 Id,FieldD from BulkTestTwo order by FieldU asc", null).Tables[0];

            foreach (DataRow item in tableUpdate.Rows)
            {
                item["FieldD"] = "555";
            }
            foreach (DataRow item in tableTwoUpdate.Rows)
            {
                item["FieldD"] = "666";
            }
            tableUpdate.TableName    = "BulkTest";
            tableTwoUpdate.TableName = "BulkTestTwo";
            Stopwatch sw = new Stopwatch();

            sw.Start();
            SqlBulkCopyHelper.BulkEditTables(new List <DataTable>()
            {
                table, tableTwo
            }, new List <DataTable>()
            {
                tableUpdate, tableTwoUpdate
            });
            sw.Stop();
            string bb = sw.Elapsed.TotalSeconds.ToString();

            Console.WriteLine(bb);
        }
示例#2
0
        public void DataUpdate()
        {
            var table    = SqlCoreHelper.ExecuteDataSetText("select Id,FieldD from BulkTest", null).Tables[0];
            var tableTwo = SqlCoreHelper.ExecuteDataSetText("select Id,FieldD from BulkTestTwo", null).Tables[0];

            foreach (DataRow item in table.Rows)
            {
                item["FieldD"] = "111";
            }
            foreach (DataRow item in tableTwo.Rows)
            {
                item["FieldD"] = "222";
            }
            table.TableName    = "BulkTest";
            tableTwo.TableName = "BulkTestTwo";
            Stopwatch sw = new Stopwatch();

            sw.Start();
            SqlBulkCopyHelper.BulkUpdateTables(new List <DataTable>()
            {
                table, tableTwo
            });
            sw.Stop();
            string bb = sw.Elapsed.TotalSeconds.ToString();

            Console.WriteLine(bb);
        }
        /// <summary>
        /// 获取数据库表的所有列
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static List <SysColumn> GetTableColumns(string tableName)
        {
            string sql = string.Format(@"select a.name,a.colorder,c.DATA_TYPE,a.isnullable,SUBSTRING(d.text,3,1) defaultValue from 
                syscolumns a inner join sysobjects b on a.id=b.id 
                LEFT JOIN dbo.syscomments d ON a.cdefault  = d.id
                inner join information_schema.columns c  on b.name=c.TABLE_NAME and c.COLUMN_NAME=a.name 
                where b.xtype='U' and b.name='{0}' order by a.colid asc", tableName);

            List <SysColumn> columns = new List <SysColumn>();
            DataTable        dt      = SqlCoreHelper.ExecuteDataSetText(sql, null).Tables[0];

            foreach (DataRow reader in dt.Rows)
            {
                SysColumn column = new SysColumn();
                column.Name     = reader[0].ToString();
                column.ColOrder = Convert.ToInt16(reader[1]);
                column.Type     = reader[2].ToString();
                column.IsNull   = Convert.ToInt32(reader[3]);
                column.Default  = reader[4].ToString();
                columns.Add(column);
            }
            return(columns);
        }
        /// <summary>
        /// 执行SqlBulkCopy批量更新,执行事务。
        /// </summary>
        /// <param name="bulkTables"></param>
        /// <returns></returns>
        public static string BulkUpdateTables(List <DataTable> updateTables, Dictionary <string, List <string> > primaryFieldsDict = default)
        {
            Dictionary <string, List <string> > insertFieldsDict = new Dictionary <string, List <string> >(),
                                                updateFieldsDict = new Dictionary <string, List <string> >();

            if (primaryFieldsDict == default)
            {
                primaryFieldsDict = new Dictionary <string, List <string> >();
            }
            foreach (var item in updateTables)
            {
                List <string> insertFields = new List <string>(),
                         primaryFields     = new List <string>(),
                         updateFields      = new List <string>();
                if (!primaryFieldsDict.Keys.Contains(item.TableName))
                {
                    var dt = SqlCoreHelper.ExecuteDataSetText(string.Format(@"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}'", item.TableName), null).Tables[0];
                    foreach (DataRow dr in dt.Rows)
                    {
                        primaryFields.Add(dr[0].ToString());
                    }
                    primaryFieldsDict.Add(item.TableName, primaryFields);
                }
                foreach (DataColumn column in item.Columns)
                {
                    insertFields.Add(column.ColumnName);
                    if (!primaryFields.Contains(column.ColumnName))
                    {
                        updateFields.Add(column.ColumnName);
                    }
                }
                insertFieldsDict.Add(item.TableName, insertFields);
                updateFieldsDict.Add(item.TableName, updateFields);
            }

            var tempTableSuf = DateTime.Now.ToString("yyyyMMddHHmmss");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                var tran        = conn.BeginTransaction();//开启事务
                var sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran)
                {
                    BulkCopyTimeout = 600
                };

                try
                {
                    using (SqlCommand cmd = new SqlCommand(string.Empty, conn, tran)
                    {
                        CommandTimeout = 600
                    })
                    {
                        updateTables.ForEach(o =>
                        {
                            var insertFields = insertFieldsDict[o.TableName];
                            cmd.CommandText += string.Format(@"SELECT {0} into {1} from {2} A WHERE 1=2;", string.Join(',', insertFields.Select(p => "A." + p)), tempTablePre + o.TableName + tempTableSuf, o.TableName);
                        });
                        cmd.ExecuteNonQuery();

                        updateTables.ForEach(o =>
                        {
                            var insertFields = insertFieldsDict[o.TableName];
                            var primarFields = primaryFieldsDict[o.TableName];
                            var updateFields = updateFieldsDict[o.TableName];
                            sqlbulkcopy.ColumnMappings.Clear();
                            sqlbulkcopy.DestinationTableName = tempTablePre + o.TableName + tempTableSuf;
                            foreach (var item in insertFields)
                            {
                                sqlbulkcopy.ColumnMappings.Add(item, item);
                            }
                            sqlbulkcopy.WriteToServer(o);

                            StringBuilder updateSql = new StringBuilder(), onSql = new StringBuilder();
                            foreach (var column in updateFields)
                            {
                                updateSql.Append(string.Format(@"A.{0} = B.{0},", column));
                            }
                            foreach (var column in primarFields)
                            {
                                if (primarFields.IndexOf(column) == primarFields.Count - 1)
                                {
                                    onSql.Append(string.Format(@"A.{0} = B.{0}", column));
                                }
                                else
                                {
                                    onSql.Append(string.Format(@"A.{0} = B.{0} And,", column));
                                }
                            }
                            cmd.CommandText = string.Format(@"UPDATE A SET {0} FROM {1} A INNER JOIN {2} B ON {3};drop table {2};",
                                                            updateSql.ToString().Trim(','), o.TableName, tempTablePre + o.TableName + tempTableSuf, onSql.ToString());
                            cmd.ExecuteNonQuery();
                        });

                        tran.Commit();
                    }
                }
                catch (Exception ex)
                {
                    return(ex.Message);
                }
                finally
                {
                    sqlbulkcopy.Close();
                    tran.Dispose();
                }
            }
            return(string.Empty);
        }