public void UpdateItems <T>(IEnumerable <T> items, string schema, string tableName, IList <ColumnMapping> properties, DbConnection storeConnection, int?batchSize, UpdateSpecification <T> updateSpecification)
        {
            var tempTableName   = "#temp_" + tableName + "_" + DateTime.Now.Ticks;
            var columnsToUpdate = updateSpecification.Properties.Select(p => p.GetPropertyName()).ToDictionary(x => x);
            var filtered        = properties.Where(p => columnsToUpdate.ContainsKey(p.NameOnObject) || p.IsPrimaryKey).ToList();
            var columns         = filtered.Select(c => "[" + c.NameInDatabase + "] " + c.DataType);
            var pkConstraint    = string.Join(", ", properties.Where(p => p.IsPrimaryKey).Select(c => "[" + c.NameInDatabase + "]"));

            var str = string.Format("CREATE TABLE {0}.[{1}]({2}, PRIMARY KEY ({3}))", schema, tempTableName, string.Join(", ", columns), pkConstraint);

            var con = storeConnection as SqlConnection;

            if (con.State != System.Data.ConnectionState.Open)
            {
                con.Open();
            }

            var setters      = string.Join(",", filtered.Where(c => !c.IsPrimaryKey).Select(c => "[" + c.NameInDatabase + "] = TEMP.[" + c.NameInDatabase + "]"));
            var pks          = properties.Where(p => p.IsPrimaryKey).Select(x => "ORIG.[" + x.NameInDatabase + "] = TEMP.[" + x.NameInDatabase + "]");
            var filter       = string.Join(" and ", pks);
            var mergeCommand = string.Format(@"UPDATE [{0}]
                SET
                    {3}
                FROM
                    [{0}] ORIG
                INNER JOIN
                     [{1}] TEMP
                ON 
                    {2}", tableName, tempTableName, filter, setters);

            var dropCommand = $@"IF Object_id('tempdb..{tempTableName}') IS NOT NULL 
    BEGIN DROP TABLE {tempTableName} END
   ELSE
    BEGIN THROW 51000,'Drop temp table {tempTableName} fail.',1; END";

            using (var createCommand = new SqlCommand(str, con))
                using (var mCommand = new SqlCommand(mergeCommand, con))
                    using (var dCommand = new SqlCommand(dropCommand, con))
                    {
                        createCommand.ExecuteNonQuery();
                        InsertItems(items, schema, tempTableName, filtered, storeConnection, batchSize);
                        mCommand.ExecuteNonQuery();
                        dCommand.ExecuteNonQuery();
                    }
        }
示例#2
0
        public void UpdateItems <T>(IEnumerable <T> items, string schema, string tableName, IList <ColumnMapping> properties, DbConnection storeConnection, int?batchSize, UpdateSpecification <T> updateSpecification, int?executeTimeout, SqlBulkCopyOptions copyOptions, DbTransaction transaction, DbConnection insertConnection)
        {
            var tempTableName   = "#" + Guid.NewGuid().ToString("N");
            var columnsToUpdate = updateSpecification.Properties.Select(p => p.GetPropertyName()).ToDictionary(x => x);
            var filtered        = properties.Where(p => columnsToUpdate.ContainsKey(p.NameOnObject) || p.IsPrimaryKey).ToList();
            var columns         = filtered.Select(c => "[" + c.NameInDatabase + "] " + c.DataType);
            var pkConstraint    = string.Join(", ", properties.Where(p => p.IsPrimaryKey).Select(c => "[" + c.NameInDatabase + "]"));

            var str = $"CREATE TABLE [{schema}].[{tempTableName}]({string.Join(", ", columns)}, PRIMARY KEY ({pkConstraint}))";

            if (storeConnection.State != System.Data.ConnectionState.Open)
            {
                storeConnection.Open();
            }

            var setters      = string.Join(",", filtered.Where(c => !c.IsPrimaryKey).Select(c => "ORIG.[" + c.NameInDatabase + "] = TEMP.[" + c.NameInDatabase + "]"));
            var pks          = properties.Where(p => p.IsPrimaryKey).Select(x => "ORIG.[" + x.NameInDatabase + "] = TEMP.[" + x.NameInDatabase + "]");
            var filter       = string.Join(" and ", pks);
            var mergeCommand = string.Format(@"UPDATE ORIG
				SET
					{4}
				FROM
					[{0}].[{1}] ORIG
				INNER JOIN
					 [{0}].[{2}] TEMP
				ON
					{3}"                    , schema, tableName, tempTableName, filter, setters);

            using (var createCommand = storeConnection.CreateCommand())
                using (var mCommand = storeConnection.CreateCommand())
                    using (var dCommand = storeConnection.CreateCommand())
                    {
                        createCommand.CommandText = str;
                        mCommand.CommandText      = mergeCommand;
                        dCommand.CommandText      = $"DROP table [{schema}].[{tempTableName}]";

                        createCommand.CommandTimeout = executeTimeout ?? 600;
                        mCommand.CommandTimeout      = executeTimeout ?? 600;
                        dCommand.CommandTimeout      = executeTimeout ?? 600;

                        createCommand.Transaction = transaction;
                        mCommand.Transaction      = transaction;
                        dCommand.Transaction      = transaction;

                        createCommand.ExecuteNonQuery();
                        InsertItems(items, schema, tempTableName, filtered, insertConnection, batchSize, executeTimeout, copyOptions, transaction);
                        mCommand.ExecuteNonQuery();
                        dCommand.ExecuteNonQuery();
                    }
        }