Esempio n. 1
0
        /// <summary>
        ///     Executes a SQL statement.
        ///     WARNING: only use for program-generated SQL statements. Statements
        ///     which include user input should be executed using prepared
        ///     statements to avoid possible injection attacks.
        /// </summary>
        /// <param name="sqlStmt">The SQL statement to execute</param>
        /// <param name="conn">The open connection object to the database</param>
        internal static int ExecuteSql(string sqlStmt, NpgsqlConnection conn)
        {
            NpgsqlCommand cmd = null;

            try
            {
                cmd = new NpgsqlCommand(sqlStmt, conn);
                return cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException ex)
            {
                Log.Write('E', ' ', sqlStmt);
                Log.WriteEx('E', Constants.LogTsType, ex);
                return -1;
            }
            finally {
                cmd?.Dispose();
            }
        }
Esempio n. 2
0
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sw"></param>
        /// <param name="schema"></param>
        /// <param name="table"></param>
        /// <param name="conn"></param>
        private static int CreateImportFile(
			this TextWriter sw,
			string schema,
			string table,
			NpgsqlConnection conn)
        {
            var limit = int.Parse(_params[Parameters.PostgresLimit].ToString());
            string sql;

            if (limit > 0)
            {
                sql = string.Format(
                    CultureInfo.InvariantCulture,
                    "SELECT * FROM \"{0}\".\"{1}\" LIMIT {2}",
                    schema, table, limit);
            }
            else
            {
                sql = string.Format(
                    CultureInfo.InvariantCulture,
                    "SELECT * FROM \"{0}\".\"{1}\"",
                    schema, table);
            }

            var criteria = string.Format(
                CultureInfo.InvariantCulture,
                "schema_name = '{0}' AND table_name = '{1}'",
                schema, table);

            var tblDict = ((Dictionary<string, DataTable>) _params[Constants.PgTables]);
            var dt = tblDict[Constants.PgSchemaTable];
            NpgsqlCommand cmd = null;
            NpgsqlDataReader reader = null;
            var colInfo = dt.Select(criteria, "column_index");
            var recCount = 0;

            try
            {
                cmd = new NpgsqlCommand(sql, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    sw.WriteLine(ProcessRow(reader, colInfo));
                    recCount++;
                    //if (recCount%100000 == 0) {
                    //	sw.Flush();
                    //	_log.Write('T', Constants.LogTsType, recCount.ToString("N0"), 2);
                    //}
                }
            }
            catch (NpgsqlException ex) {
                _log.WriteEx('F', Constants.LogTsType, ex);
                throw;
            }
            finally
            {
                reader?.Dispose();
                cmd?.Dispose();
            }

            var pad = (49 - schema.Length - table.Length);
            string qualName;

            if (pad > 0) qualName = schema + "." + table + new string('.', pad);
            else qualName = schema + "." + table;

            _log.Write(
                'I',
                Constants.LogTsType,
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0}: {1,13:n0}",
                    qualName, recCount),
                1);

            return recCount;
        }