C# StreamWriter.WriteCommitTrans方法代码示例

        /// <summary>
        ///     Generate the CREATE TYPE and DROP TYPE statements.
        /// </summary>
        /// <param name="dtTypes"></param>
        /// <param name="dtSeq"></param>
        private static void GenerateTypeScripts(DataTable dtTypes, DataTable dtSeq)
        {
            var createPath = Path.Combine(
                _params[Parameters.OtherWorkPath].ToString(), Constants.CreateTypes);
            var dropPath = Path.Combine(
                _params[Parameters.OtherWorkPath].ToString(), Constants.CreateDropTypes);

            StreamWriter swCreate = null;
            StreamWriter swDrop = null;

            try
            {
                swCreate = new StreamWriter(createPath, false, Encoding.Default);
                swDrop = new StreamWriter(dropPath, false, Encoding.Default);

                swCreate.WriteBeginTrans();
                swDrop.WriteBeginTrans();

                // Write CREATE TYPE statements.
                foreach (DataRow row in dtTypes.Rows)
                {
                    var schema = row["schema_name"].ToString().Equals(Constants.PgDefaultSchema)
                        ? Constants.SsDefaultSchema
                        : row["schema_name"].ToString();

                    var typeName = "[" + schema + "].[" + row["type_name"] + "]";

                    swCreate.WriteLine("CREATE TYPE " + typeName);

                    var dataType = Postgres.SsDataType(row["regtype"].ToString());
                    swCreate.WriteLine(
                        "FROM [" + dataType + "]" +
                        GenerateColumnDimDef(row, dataType) + ";");
                    swCreate.WriteLine();

                    swDrop.WriteLine("DROP TYPE " + typeName + ";");
                }

                // Write CREATE SEQUENCE statements.
                foreach (DataRow row in dtSeq.Rows)
                {
                    var schema = row["schema_name"].ToString().Equals(Constants.PgDefaultSchema)
                        ? Constants.SsDefaultSchema
                        : row["schema_name"].ToString();
                    var seqName = "[" + schema + "].[" + row["seq_name"] + "]";
                    var dataType = Postgres.SsDataType(row["regtype"].ToString());

                    var maxValue = Postgres.GetMaxValByType(dataType);
                    var maxValueRec = (long) row["max_value"];
                    maxValue = maxValueRec > maxValue ? maxValue : maxValueRec;

                    swCreate.WriteLine("CREATE SEQUENCE " + seqName);
                    swCreate.WriteLine(Constants.Tab + "AS [" + dataType + "]");
                    swCreate.Write(Constants.Tab + "START WITH ");
                    swCreate.WriteLine(row["start_value"]);
                    swCreate.Write(Constants.Tab + "INCREMENT BY ");
                    swCreate.WriteLine(row["increment_by"]);
                    swCreate.Write(Constants.Tab + "MINVALUE ");
                    swCreate.WriteLine(row["min_value"]);
                    swCreate.Write(Constants.Tab + "MAXVALUE ");
                    swCreate.WriteLine(maxValue);

                    swCreate.WriteLine(
                        Constants.Tab + ((bool) row["is_cycled"] ? "CYCLE" : "NO CYCLE"));

                    swCreate.Write(Constants.Tab + "CACHE ");
                    swCreate.WriteLine(row["cache_value"]);

                    swCreate.WriteLine(";");
                    swCreate.WriteLine();

                    swDrop.WriteLine("DROP SEQUENCE " + seqName + ";");
                }

                swCreate.WriteCommitTrans();
                swDrop.WriteCommitTrans();
            }
            catch (Exception ex) {
                _log.WriteEx('E', Constants.LogTsType, ex);
            }
            finally
            {
                swCreate?.Dispose();
                swDrop?.Dispose();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        private static void GenerateFkConstraints(DataTable dt)
        {
            var path = Path.Combine(
                _params[Parameters.OtherWorkPath].ToString(), Constants.CreateForeignKeys);

            using (var sw = new StreamWriter(path, false, Encoding.Default))
            {
                var saveFkName = "";
                var cnt = 0;

                sw.WriteBeginTrans();

                foreach (DataRow row in dt.Rows)
                {
                    if (saveFkName != row["fk_name"].ToString())
                    {
                        saveFkName = row["fk_name"].ToString();
                        cnt = 0;
                    }
                    else cnt++;
                    sw.WriteFkStatement(row, cnt);
                }
                sw.WriteCommitTrans();
            }
        }

        /// <summary>
        ///     Generate the SQL Server scripts.
        /// </summary>
        /// <param name="schemaTable">DataTable with all the PostgreSQL schema/table/column info.</param>
        /// <param name="seqTable"></param>
        /// <param name="conn">PG connection.</param>
        private static void GenerateTableScripts(
			DataTable schemaTable,
			DataTable seqTable, 
			NpgsqlConnection conn)
        {
            var createPath = Path.Combine(
                _params[Parameters.OtherWorkPath].ToString(), Constants.CreateTables);
            var dropPath = Path.Combine(
                _params[Parameters.OtherWorkPath].ToString(), Constants.CreateDropTables);
            var truncPath = Path.Combine(
                _params[Parameters.OtherWorkPath].ToString(), Constants.CreateSTruncateTables);

            StreamWriter swCreate = null;
            StreamWriter swDrop = null;
            StreamWriter swTrunc = null;

            try
            {
                swCreate = new StreamWriter(createPath, false, Encoding.Default);
                swDrop = new StreamWriter(dropPath, false, Encoding.Default);
                swTrunc = new StreamWriter(truncPath, false, Encoding.Default);

                swCreate.WriteBeginTrans();
                swDrop.WriteBeginTrans();
                swTrunc.WriteBeginTrans();
                swCreate.PrepCreateTable();

                var savedSchema = "";
                var savedTable = "";
                var colInfo = new List<ColumnInfo>();

                foreach (DataRow row in schemaTable.Rows)
                {
                    var schema = row["schema_name"].ToString();
                    var table = row["table_name"].ToString();

                    // Schema or table changed: close table defenition.
                    if (!savedSchema.Equals(schema) || !savedTable.Equals(table))
                    {
                        if (!string.IsNullOrEmpty(savedTable))
                        {
                            CloseCreateTable(swCreate, colInfo);
                            UpdateSeqTable(colInfo, seqTable);
                            colInfo.Clear();
                        }
                        savedSchema = schema;
                        savedTable = table;

                        swCreate.OpenCreateTable(schema, table);
                        swDrop.WriteDropCommand(schema, table);
                        swTrunc.WriteTruncateCommand(schema, table);
                    }
                    else swCreate.WriteLine(",");

                    // Generate column definition.
                    ColumnInfo tmpColInfo;

                    swCreate.GenerateColumn(row, out tmpColInfo);
                    if (!string.IsNullOrEmpty(tmpColInfo.Schema))
                    {
                        colInfo.Add(tmpColInfo);
                    }
                }

                if (string.IsNullOrEmpty(savedSchema)) return;

                // Complete last writes.
                swCreate.CloseCreateTable(colInfo);
                swCreate.WriteTableDesc(conn);
                swCreate.WriteCommitTrans();
                swDrop.WriteCommitTrans();
                swTrunc.WriteCommitTrans();
            }
            catch (Exception ex) {
                _log.WriteEx('E', Constants.LogTsType, ex);
            }
            finally
            {
                swCreate?.Dispose();
                swDrop?.Dispose();
                swTrunc?.Dispose();
            }
        }

        /// <summary>
        ///     Write the different create indeces/key constraint statements. 
        /// </summary>
        /// <param name="conn"></param>
        private static void GenerateBuildIndexes(NpgsqlConnection conn)
        {
            var indexPath = Path.Combine(
                _params[Parameters.OtherWorkPath].ToString(), Constants.CreateIndexesEtAl);

            // option column values:
            // INDOPTION_DESC			0x0001 = values are in reverse order (DESC)
            // INDOPTION_NULLS_FIRST	0x0002 = NULLs are first instead of last
            const string sql =
                @"SELECT n.nspname AS schema_name, c.relname AS table_name,
                        d.relname AS index_name, i.indisprimary,
                        i.indisunique, a.attname AS column_name,
                        i.indoption[a.attnum - 1] as option
                FROM	pg_index i
                        LEFT JOIN pg_attribute a
                              ON a.attrelid = i.indrelid
                                 AND a.attnum = ANY ( i.indkey )
                        LEFT JOIN pg_class c
                              ON c.oid = i.indrelid
                        LEFT JOIN pg_class d
                              ON d.oid = i.indexrelid
                        LEFT JOIN pg_namespace n
                              ON n.oid = c.relnamespace
                WHERE  n.nspname NOT IN('pg_toast', 'pg_catalog', 'information_schema', 'public')
                ORDER  BY n.nspname ASC, c.relname ASC, d.relname ASC, a.attnum";

            using (var cmd = new NpgsqlCommand(sql, conn))
            using (var sw = new StreamWriter(indexPath, false, Encoding.Default))
            {
                sw.WriteBeginTrans();

                var savedSchema = "";
                var savedTable = "";
                var savedIndex = "";
                var savedType = ' ';

                using (var reader = cmd.ExecuteReader())
                {
                    var sb = new StringBuilder();

                    while (reader.Read())
                    {
                        var schema = reader["schema_name"].ToString();
                        var table = reader["table_name"].ToString();
                        var index = reader["index_name"].ToString();

                        // Schema, table or index changed: close index defenition.
                        if (!savedSchema.Equals(schema) || !savedTable.Equals(table)
                            || !savedIndex.Equals(index))
                        {
                            if (sb.Length > 0)
                                sw.WriteIndex(
                                    savedSchema,
                                    savedTable,
                                    savedIndex,
                                    savedType,
                                    sb.ToString());
                            sb.Clear();

                            savedSchema = schema;
                            savedTable = table;
                            savedIndex = index;

                            if ((bool) reader["indisprimary"]) { savedType = 'P'; }
                            else if ((bool) reader["indisunique"]) { savedType = 'U'; }
                            else savedType = 'I';
                        }
                        else sb.Append(", ");

                        sb.Append("[" + reader["column_name"] + "]");

                        if (reader["option"] == DBNull.Value) continue;
                        switch ((short) reader["option"])
                        {
                            case 0:
                                sb.Append(" ASC");
                                break;
                            case 1:
                                sb.Append(" DESC");
                                break;
                        }
                    }

                    if (sb.Length > 0)
                        sw.WriteIndex(
                            savedSchema,
                            savedTable,
                            savedIndex,
                            savedType,
                            sb.ToString());

                    sw.WriteCommitTrans();
                }
            }
        }