Ejemplo n.º 1
0
        public virtual void MakeDistinct(DatabaseOperationArgs args, DiscoveredTable discoveredTable)
        {
            var server = discoveredTable.Database.Server;

            //if it's got a primary key they it's distinct! job done
            if (discoveredTable.DiscoverColumns().Any(c => c.IsPrimaryKey))
            {
                return;
            }

            var tableName = discoveredTable.GetFullyQualifiedName();
            var tempTable = discoveredTable.Database.ExpectTable(discoveredTable.GetRuntimeName() + "_DistinctingTemp").GetFullyQualifiedName();


            using (var con = args.TransactionIfAny == null
                ? server.BeginNewTransactedConnection()
                :                                               //start a new transaction
                             args.GetManagedConnection(server)) //or continue the ongoing transaction
            {
                using (var cmdDistinct =
                           server.GetCommand(
                               string.Format("CREATE TABLE {1} AS SELECT distinct * FROM {0}", tableName, tempTable), con))
                    args.ExecuteNonQuery(cmdDistinct);

                //this is the point of no return so don't cancel after this point
                using (var cmdTruncate = server.GetCommand(string.Format("DELETE FROM {0}", tableName), con))
                {
                    cmdTruncate.CommandTimeout = args.TimeoutInSeconds;
                    cmdTruncate.ExecuteNonQuery();
                }

                using (var cmdBack = server.GetCommand(string.Format("INSERT INTO {0} (SELECT * FROM {1})", tableName, tempTable), con))
                {
                    cmdBack.CommandTimeout = args.TimeoutInSeconds;
                    cmdBack.ExecuteNonQuery();
                }

                using (var cmdDropDistinctTable = server.GetCommand(string.Format("DROP TABLE {0}", tempTable), con))
                {
                    cmdDropDistinctTable.CommandTimeout = args.TimeoutInSeconds;
                    cmdDropDistinctTable.ExecuteNonQuery();
                }

                //if we opened a new transaction we should commit it
                if (args.TransactionIfAny == null)
                {
                    con.ManagedTransaction?.CommitAndCloseConnection();
                }
            }
        }
Ejemplo n.º 2
0
        public virtual void MakeDistinct(DiscoveredTable discoveredTable, int timeoutInSeconds)
        {
            var server = discoveredTable.Database.Server;

            //note to future developers, this method has horrible side effects e.g. column defaults might be recalculated, foreign key CASCADE Deletes might happen
            //to other tables we can help the user not make such mistakes with this check.
            if (discoveredTable.DiscoverColumns().Any(c => c.IsPrimaryKey))
            {
                throw new NotSupportedException("Table " + discoveredTable + " has primary keys, why are you calling MakeDistinct on it!");
            }

            var tableName = discoveredTable.GetFullyQualifiedName();
            var tempTable = discoveredTable.Database.ExpectTable(discoveredTable.GetRuntimeName() + "_DistinctingTemp").GetFullyQualifiedName();

            using (var con = server.BeginNewTransactedConnection())
            {
                try
                {
                    var cmdDistinct = server.GetCommand(string.Format("CREATE TABLE {1} AS SELECT distinct * FROM {0}", tableName, tempTable), con);
                    cmdDistinct.CommandTimeout = timeoutInSeconds;
                    cmdDistinct.ExecuteNonQuery();

                    var cmdTruncate = server.GetCommand(string.Format("DELETE FROM {0}", tableName), con);
                    cmdTruncate.CommandTimeout = timeoutInSeconds;
                    cmdTruncate.ExecuteNonQuery();

                    var cmdBack = server.GetCommand(string.Format("INSERT INTO {0} (SELECT * FROM {1})", tableName, tempTable), con);
                    cmdBack.CommandTimeout = timeoutInSeconds;
                    cmdBack.ExecuteNonQuery();

                    var cmdDropDistinctTable = server.GetCommand(string.Format("DROP TABLE {0}", tempTable), con);
                    cmdDropDistinctTable.CommandTimeout = timeoutInSeconds;
                    cmdDropDistinctTable.ExecuteNonQuery();

                    con.ManagedTransaction.CommitAndCloseConnection();
                }
                catch (Exception)
                {
                    con.ManagedTransaction.AbandonAndCloseConnection();
                    throw;
                }
            }
        }
Ejemplo n.º 3
0
 public string GetForeignKeyConstraintNameFor(DiscoveredTable foreignTable, DiscoveredTable primaryTable)
 {
     return(GetForeignKeyConstraintNameFor(foreignTable.GetRuntimeName(), primaryTable.GetRuntimeName()));
 }