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