/// <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(); } }
/// <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; }