private void CargarDatos()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = "SELECT * FROM area_servicio.ft_view_ultima_tasa_cambio();";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
lblVenta.Text = pgDr.GetString("venta");
lblCompra.Text = pgDr.GetString("compra");
}
pgDr.Close();
sentencia = null;
pgComando.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de cargar la tasa de cambio. " + Exc.Message);
}
}
private void HabilitarDeshabilitarEmpleado(string pCodigoEmpleado, bool pHabilitar)
{
ValidarConexion();
string sentencia = @"SELECT * FROM area_servicio.ft_mant_habilitar_deshabilitar_empleado(:pCodigoEmpleado,
:pHabilitar,
:pUsuario);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("pCodigoEmpleado", PgSqlType.VarChar).Value = pCodigoEmpleado;
pgComando.Parameters.Add("pHabilitar", PgSqlType.Boolean).Value = pHabilitar;
pgComando.Parameters.Add("pUsuario", PgSqlType.VarChar).Value = Pro_Usuario;
try
{
pgComando.ExecuteNonQuery();
sentencia = null;
pgComando.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("ALGO SALIO EN EL MOMENTO DE REGISTRAR EL EMPLEADO. " + Exc.Message, "FLUCOL");
}
}
private void CargarDatos()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = "SELECT * FROM area_servicio.ft_view_agencias_servicio_disponibles_para_dashboards(:p_id_cliente);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_cliente", PgSqlType.Int).Value = Pro_ID_ClienteServicio;
try
{
dsCore1.dtAgenciasDisponibles.Clear();
new PgSqlDataAdapter(pgComando).Fill(dsCore1.dtAgenciasDisponibles);
sentencia = null;
pgComando.Dispose();
}
catch (Exception Exc)
{
sentencia = null;
pgComando.Dispose();
DepuradorExcepciones v_depurador = new DepuradorExcepciones();
v_depurador.CapturadorExcepciones(Exc,
this.Name,
"CargarDatos()");
v_depurador = null;
MessageBox.Show("Algo salió mal mientras se cargan Agencias Disponibles. ", "FLUCOL");
}
}
public DataSet Legal()
{
var sql = @"select Name from customers where Type = 'l'";
var command = new PgSqlCommand(sql);
return(Database.ExecuteCommand(command));
}
public DataSet ProjectsOrderByStart()
{
var sql = "select * from orders order by startdate";
var command = new PgSqlCommand(sql);
return(Database.ExecuteCommand(command));
}
public static DateTime ObtenerHoraServidor(PgSqlConnection pConexion)
{
DateTime v_resultado = Convert.ToDateTime(null);
if (pConexion.State != System.Data.ConnectionState.Open)
{
pConexion.Open();
}
string sentencia = "SELECT * FROM arca_tesoros_conf.ft_view_variables_tiempo();";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion);
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
v_resultado = pgDr.GetDateTime("fecha_hora_servidor");
}
pgDr.Close();
sentencia = null;
pgComando.Dispose();
pgComando = null;
return(v_resultado);
}
catch (Exception Exc)
{
Log_Excepciones.CapturadorExcepciones(Exc, "Utilidades.cs", "ObtenerHoraServidor");
return(Convert.ToDateTime(null));
}
}
private void cmbTables_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if ((C.selectedDatabase == "Microsoft Excel" || C.selectedDatabase == "Microsoft Access") && C.enterChooseFields == true)
{
dt = ImportExcelorAccesstoDatatable(C.fileName, cmbTables.SelectedItem.ToString(), true);
}
else if (C.selectedDatabase == "MySQL")
{
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + cmbTables.SelectedItem.ToString(), C.mysqlConnection);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
dt = new DataTable();
adp.Fill(dt);
}
else if (C.selectedDatabase == "Microsoft SQL Server")
{
SqlCommand cmd = new SqlCommand("SELECT * FROM " + cmbTables.SelectedItem.ToString(), C.sqlserverConnection);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
dt = new DataTable();
adp.Fill(dt);
}
else if (C.selectedDatabase == "Oracle")
{
OracleCommand cmd = new OracleCommand("SELECT * FROM " + cmbTables.SelectedItem.ToString(), C.oracleConnection);
OracleDataAdapter adp = new OracleDataAdapter(cmd);
dt = new DataTable();
adp.Fill(dt);
}
else if (C.selectedDatabase == "PostgreSQL")
{
PgSqlCommand cmd = new PgSqlCommand("SELECT * FROM " + cmbTables.SelectedItem.ToString(), C.postgresqlConnection);
PgSqlDataAdapter adp = new PgSqlDataAdapter(cmd);
dt = new DataTable();
adp.Fill(dt);
}
else if (C.selectedDatabase == "Firebird")
{
FbCommand cmd = new FbCommand("SELECT * FROM " + cmbTables.SelectedItem.ToString(), C.firebirdConnection);
FbDataAdapter adp = new FbDataAdapter(cmd);
dt = new DataTable();
adp.Fill(dt);
}
lstSelectedFields.Items.Clear();
lstAvailableFields.Items.Clear();
for (int i = 0; i < dt.Columns.Count; i++)
{
lstAvailableFields.Items.Add(new ListItemWithImage("/images/table.png", dt.Columns[i].Caption, ""));
}
gthis.chooseFields.btnAdd.IsEnabled = true;
gthis.chooseFields.btnAddAll.IsEnabled = true;
gthis.chooseFields.btnRemove.IsEnabled = false;
gthis.chooseFields.btnRemoveAll.IsEnabled = false;
gthis.chooseFields.btnNext.IsEnabled = false;
}
private void Button1_Click_1(object sender, EventArgs e)
{
int combodetalleproducto = (int)comboGrupoSeleccionado.SelectedValue; //extraer id de combobox de detalle con "Selectedvalue"
string query = @"SELECT * FROM public.ft_insert_nuevo_producto_detalle (
:p_nombre_producto,
:p_id_de_grupo,
:p_codigo_barras
)";
PgSqlCommand pg_comando = new PgSqlCommand(query, pro_conexion);
pg_comando.Parameters.Add("p_id_de_grupo", PgSqlType.Int).Value = combodetalleproducto;
pg_comando.Parameters.Add("p_nombre_producto", PgSqlType.VarChar).Value = txt_nombre_nuevo_producto.Text; // solo si hay parametros en la funcion sql
pg_comando.Parameters.Add("p_codigo_barras", PgSqlType.VarChar).Value = txt_codigo_de_barras.Text;
try
{
pg_comando.ExecuteNonQuery(); //executenonquery cuando no necesitamos retornar un valor
MessageBox.Show("Producto guardado con exito.");
txt_nombre_nuevo_producto.Clear();
txt_codigo_de_barras.Clear();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void CargarDatos()
{
v_lista_noticias.Clear();
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM area_servicio.ft_view_noticias_cliente (:p_id_cliente_servicio);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente_Servicio;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
while (pgDr.Read())
{
v_lista_noticias.Add(pgDr.GetString("texto_noticia"));
}
pgDr.Close();
sentencia = null;
pgComando.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de obtener lista de noticias. " + Exc.Message);
}
}
private void cbDatabase_DropDown(object sender, System.EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
cbDatabase.Items.Clear();
cbSchema.Text = "";
PgSqlConnection databaseConnection = new PgSqlConnection();
databaseConnection.Host = cbHost.Text;
databaseConnection.UserId = edUser.Text;
databaseConnection.Password = edPassword.Text;
databaseConnection.Port = (int)edPort.Value;
try {
databaseConnection.Open();
System.Data.IDbCommand command = new PgSqlCommand("SELECT datname FROM pg_database WHERE datallowconn = true and datname <> 'template1'", databaseConnection);
using (System.Data.IDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
cbDatabase.Items.Add(reader[0]);
}
}
}
catch (Exception exception) {
MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally {
Cursor.Current = Cursors.Default;
databaseConnection.Close();
}
}
public event EventHandler on_ProveedorAgregado; //evento personalizado
#endregion
#region BOTONES
private void btn_guardar_grupo_Click(object sender, EventArgs e)
{
string query = @"SELECT * FROM public.ft_insert_nuevo_proveedor (
:p_nombre_proveedor
)";
PgSqlCommand pg_comando = new PgSqlCommand(query, pro_conexion);
pg_comando.Parameters.Add("p_nombre_proveedor", PgSqlType.VarChar).Value = txt_nuevo_proveeor.Text; // solo si hay parametros en la funcion sql
try
{
pg_comando.ExecuteNonQuery(); //executenonquery cuando no necesitamos retornar un valor
MessageBox.Show("Proveedor guardado con exito.");
txt_nuevo_proveeor.Clear();
on_ProveedorAgregado?.Invoke(new object(), new EventArgs()); //invocacion del evento personalizado
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
DialogResult result = MessageBox.Show("¿DESEA AGREGAR OTRO PROVEEDOR?", "SALIR", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
}
else if (result == DialogResult.No)
{
this.Close();
}
}
private void CargarDatosColaborador()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM arca_tesoros.ft_view_ficha_ingreso (:p_id_colaborador)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_colaborador", PgSqlType.Int).Value = Pro_ID_Colaborador;
try
{
PgSqlDataReader pgDr = pgComando.ExecuteReader();
if (pgDr.Read())
{
txtNombreColaborador.Text = pgDr.GetString("nombre_colaborador");
txtIdentidadColaborador.Text = pgDr.GetString("numero_identidad");
txtFuerzasEspeciales.Text = pgDr.GetString("area_atencion");
v_ruta_fotografia = pgDr.GetString("direccion_fotografia");
}
pgDr.Close();
}
catch (Exception Exc)
{
Log_Excepciones.CapturadorExcepciones(Exc, this.Name, "CargarDatosColaborador");
}
}
private void RegistrarEstadoSolicitud(int p_id_colaborador_solicitud, ESTADOS_SOLICITUD p_estado_solicitud)
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM arca_tesoros.ft_mant_respuesta_solicitud (
:p_id_colaborador_solicitud,
:p_usuario,
:p_estado,
:p_observaciones_rechazo
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_colaborador_solicitud", PgSqlType.Int).Value = p_id_colaborador_solicitud;
pgComando.Parameters.Add("p_usuario", PgSqlType.VarChar).Value = Pro_Usuario;
pgComando.Parameters.Add("p_estado", PgSqlType.Int).Value = (int)p_estado_solicitud;
pgComando.Parameters.Add("p_observaciones_rechazo", PgSqlType.VarChar).Value = memoMotivosRechazo.Text;
try
{
pgComando.ExecuteNonQuery();
MessageBox.Show("Solicitud Aprobada");
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal mientras se registraba el estado de la solicitud.");
}
}
public void CargarDatos()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM area_servicio.ft_view_posiciones_disponibles_para_asignacion(:p_id_agencia_servicio,
:p_id_cliente_servicio);";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio;
pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_ClienteServicio;
try
{
dsCore1.dtPosicionesDisponibles.Clear();
new PgSqlDataAdapter(pgComando).Fill(dsCore1.dtPosicionesDisponibles);
sentencia = null;
pgComando.Dispose();
}
catch (Exception Exc)
{
MessageBox.Show("Algo salió mal en el momento de cargar las posiciones disponibles. " + Exc.Message);
}
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (!String.IsNullOrWhiteSpace(Table))
{
sqlCommand.AppendCommand($"\"{Table}\".");
}
if (Column == "*")
{
sqlCommand.AppendCommand($"*");
}
else
{
sqlCommand.AppendCommand($"\"{Column}\"");
if (DotnetType == typeof(PgSqlLTree))
{
sqlCommand.AppendCommand("::text");
}
//if (!String.IsNullOrWhiteSpace(Alias))
// sqlCommand.AppendCommand($" AS \"{Alias}\"");
}
return(sqlCommand);
}
private void CargarDatos()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
try
{
string sentencia = "SELECT * FROM arca_tesoros.ft_view_solicitudes();";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
try
{
dsCoordinador_Dia1.dtSolicitudes.Clear();
new PgSqlDataAdapter(pgComando).Fill(dsCoordinador_Dia1.dtSolicitudes);
}
catch (Exception Exc)
{
}
}
catch (Exception Exc)
{
Log_Excepciones.CapturadorExcepciones(Exc, this.Name, "CargarDatosAsistencia");
MessageBox.Show("Algo salió mal mientras se cargaba lista asistencia.");
}
}
private void GuardarCubrirAusencia()
{
if (Pro_Conexion.State != ConnectionState.Open)
{
Pro_Conexion.Open();
}
string sentencia = @"SELECT * FROM arca_tesoros.ft_proc_insertar_detalles_cubrir_ausencia (
:p_id_colaborar_asistencia_actividades,
:p_id_medio_cubrir_ausencia,
:p_id_colaborador_cubre_ausencia
)";
PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion);
pgComando.Parameters.Add("p_id_colaborar_asistencia_actividades", PgSqlType.Int).Value = Pro_ID_Colaborar_Actividad;
pgComando.Parameters.Add("p_id_medio_cubrir_ausencia", PgSqlType.Int).Value = (int)v_tipo_cubrir_ausencia;
pgComando.Parameters.Add("p_id_colaborador_cubre_ausencia", PgSqlType.Boolean).Value = v_id_colaborador_cubre_ausencia;
try
{
pgComando.ExecuteNonQuery();
Utilidades.MostrarDialogo(FindForm(), "Confirmación de Registros", "¡La asistencia se procesó correctamente!", Utilidades.BotonesDialogo.Ok);
OnCubrirAusenciaIngresada?.Invoke(new object(), new EventArgs());
}
catch (Exception Exc)
{
Log_Excepciones.CapturadorExcepciones(Exc, this.Name, "MarcarAsistencia");
MessageBox.Show("Algo salió mal mientras se marcaba asistencia del colaborador en la lista.");
}
}
public DataSet CompletedProjects()
{
var sql = "select * from orders where enddate < now()";
var command = new PgSqlCommand(sql);
return(Database.ExecuteCommand(command));
}
public UsuarioDTO Autentica(UsuarioDTO objUsuario)
{
PgSqlConnection conn = new PgSqlConnection("User Id=postgres;Password=root;host=localhost;database=NIVEL_ACESSO;");
PgSqlCommand cmd = new PgSqlCommand();
cmd.CommandText = string.Format("SELECT * FROM public.\"TRABALHADORES\" WHERE \"LOGIN\" ='{0}' AND \"SENHA\" = '{1}';", objUsuario.Login, objUsuario.Senha);
cmd.Connection = conn;
conn.Open();
int aff = cmd.ExecuteNonQuery();
PgSqlDataReader ER;
UsuarioDTO usuario = new UsuarioDTO();
ER = cmd.ExecuteReader();
if (ER.Read())
{
usuario.Cpf = Convert.ToString(ER["cpf_usu"]);
usuario.Nome = Convert.ToString(ER["nome_usu"]);
usuario.Email = Convert.ToString(ER["email_usu"]);
usuario.Login = Convert.ToString(ER["login_usu"]);
usuario.Senha = Convert.ToString(ER["senha_usu"]);
usuario.NivelDeAcesso = Convert.ToInt16(ER["nivelAcesso_usu"]);
}
conn.Close();
return(usuario);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var command = new PgSqlCommand();
var lsql = new List <string>();
foreach (var query in Queries)
{
var comm = query.GetSqlCommand(tableDefinition);
comm.Parameters.ForEach(p =>
{
if (command.Parameters.Any(par => par.UniqueId.Equals(p.UniqueId)))
{
var ng = Guid.NewGuid().ToString("N");
comm.Command = comm.Command.Replace($"@{p.UniqueId}", $"@{ng}");
p.UniqueId = ng;
command.Parameters.Add(p);
}
else
{
command.Parameters.Add(p);
}
});
lsql.Add($"({comm.Command})");
}
command.Command = String.Join(ChainOperator, lsql);
return(command);
}
public virtual void Execute()
{
log.Info("Start extracting");
int count;
int pageSize = Global.Instance.config.PageSize;
try
{
PgSqlConnection connection1 = getConnection();
string countCommandSql = "SELECT count(*) " + GetSqlBodyCommand();
string selectCommandSql = "SELECT model.id, model.urldocument " + GetSqlBodyCommand() + " order by model.id asc";
PgSqlCommand command = new PgSqlCommand(countCommandSql, connection1);
command.Parameters.AddWithValue("startDate", Global.Instance.config.StartDate);
command.Parameters.AddWithValue("endDate", Global.Instance.config.EndDate);
command.Parameters.AddWithValue("model", Global.Instance.config.PacketPatterns[0].Model);
command.Parameters.AddWithValue("pattern", Global.Instance.config.PacketPatterns[0].Pattern);
using (connection1)
using (command)
{
connection1.Open();
count = Convert.ToInt32(command.ExecuteScalar());
log.Info("Total count:" + count.ToString());
Console.WriteLine("Total count:" + command.ExecuteScalar().ToString());
}
int j = 0;
//for (int i = 0; i < count; i += pageSize)
int k = pageSize;
int startFrom = 0;
while (k == pageSize)
{
j++;
log.Info("**start iteration: " + j);
Console.WriteLine("start iteration: " + j);
PgSqlConnection connection2 = getConnection();
command.CommandText = selectCommandSql;
command.Connection = connection2;
log.Info("****start fetch ");
List<packageInfo> fileList = new List<packageInfo>();
k = 0;
using (connection2)
using (command)
{
connection2.Open();
using (PgSqlDataReader reader = command.ExecutePageReader(System.Data.CommandBehavior.SingleResult, startFrom, pageSize))
{
while (reader.Read())
{
fileList.Add(new packageInfo(reader.GetValue(0).ToString(), reader.GetValue(1).ToString()));
k++;
}
}
}
startFrom += pageSize;
log.Info("****end fetch ");
log.Info("****start coping ");
foreach (packageInfo p in fileList)
{
extractPackages(p);
}
log.Info("****end coping ");
log.Info("**end iteration: " + j);
Console.WriteLine("end iteration: " + j);
}
log.Info("Finish extracting");
}
catch (Exception ex)
{
log.Error(ex);
Console.WriteLine(ex);
}
}
static void Test(PgSqlConnection con, string sql,
CommandType cmdType, CommandBehavior behavior,
string testDesc)
{
PgSqlCommand cmd = null;
PgSqlDataReader rdr = null;
int c;
int results = 0;
Console.WriteLine("Test: " + testDesc);
Console.WriteLine("[BEGIN SQL]");
Console.WriteLine(sql);
Console.WriteLine("[END SQL]");
cmd = new PgSqlCommand(sql, con);
cmd.CommandType = cmdType;
Console.WriteLine("ExecuteReader...");
rdr = cmd.ExecuteReader(behavior);
if(rdr == null) {
Console.WriteLine("IDataReader has a Null Reference.");
}
else {
do {
// get the DataTable that holds
// the schema
DataTable dt = rdr.GetSchemaTable();
if(rdr.RecordsAffected != -1) {
// Results for
// SQL INSERT, UPDATE, DELETE Commands
// have RecordsAffected >= 0
Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
}
else if (dt == null)
Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
else {
// Results for
// SQL not INSERT, UPDATE, nor DELETE
// have RecordsAffected = -1
Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected);
// Results for a SQL Command (CREATE TABLE, SET, etc)
// will have a null reference returned from GetSchemaTable()
//
// Results for a SQL SELECT Query
// will have a DataTable returned from GetSchemaTable()
results++;
Console.WriteLine("Result Set " + results + "...");
// number of columns in the table
Console.WriteLine(" Total Columns: " +
dt.Columns.Count);
// display the schema
foreach (DataRow schemaRow in dt.Rows) {
foreach (DataColumn schemaCol in dt.Columns)
Console.WriteLine(schemaCol.ColumnName +
" = " +
schemaRow[schemaCol]);
Console.WriteLine();
}
int nRows = 0;
string output, metadataValue, dataValue;
// Read and display the rows
Console.WriteLine("Gonna do a Read() now...");
while(rdr.Read()) {
Console.WriteLine(" Row " + nRows + ": ");
for(c = 0; c < rdr.FieldCount; c++) {
// column meta data
DataRow dr = dt.Rows[c];
metadataValue =
" Col " +
c + ": " +
dr["ColumnName"];
// column data
if(rdr.IsDBNull(c) == true)
dataValue = " is NULL";
else
dataValue =
": " +
rdr.GetValue(c);
// display column meta data and data
output = metadataValue + dataValue;
Console.WriteLine(output);
}
nRows++;
}
Console.WriteLine(" Total Rows: " +
nRows);
}
} while(rdr.NextResult());
Console.WriteLine("Total Result sets: " + results);
rdr.Close();
}
}
public static void Main() {
Console.WriteLine("** Start Test...");
String connectionString = null;
connectionString =
"host=localhost;" +
"dbname=test;" +
"user=postgres";
PgSqlConnection con;
Console.WriteLine("** Creating connection...");
con = new PgSqlConnection(connectionString);
Console.WriteLine("** opening connection...");
con.Open();
string tableName = "pg_type";
string sql;
sql = "SELECT * FROM PG_TABLES WHERE TABLENAME = :inTableName";
Console.WriteLine("** Creating command...");
PgSqlCommand cmd = new PgSqlCommand(sql, con);
// add parameter for inTableName
Console.WriteLine("** Create parameter...");
PgSqlParameter parm = new PgSqlParameter("inTableName", DbType.String);
Console.WriteLine("** set dbtype of parameter to string");
parm.DbType = DbType.String;
Console.WriteLine("** set direction of parameter to input");
parm.Direction = ParameterDirection.Input;
Console.WriteLine("** set value to the tableName string...");
parm.Value = tableName;
Console.WriteLine("** add parameter to parameters collection in the command...");
cmd.Parameters.Add(parm);
PgSqlDataReader rdr;
Console.WriteLine("** ExecuteReader()...");
rdr = cmd.ExecuteReader();
Console.WriteLine("[][] And now we are going to our results [][]...");
int c;
int results = 0;
do {
results++;
Console.WriteLine("Result Set " + results + "...");
// get the DataTable that holds
// the schema
DataTable dt = rdr.GetSchemaTable();
// number of columns in the table
Console.WriteLine(" Total Columns: " +
dt.Columns.Count);
// display the schema
foreach (DataRow schemaRow in dt.Rows) {
foreach (DataColumn schemaCol in dt.Columns)
Console.WriteLine(schemaCol.ColumnName +
" = " +
schemaRow[schemaCol]);
Console.WriteLine();
}
string output, metadataValue, dataValue;
int nRows = 0;
// Read and display the rows
while(rdr.Read()) {
Console.WriteLine(" Row " + nRows + ": ");
for(c = 0; c < rdr.FieldCount; c++) {
// column meta data
DataRow dr = dt.Rows[c];
metadataValue =
" Col " +
c + ": " +
dr["ColumnName"];
// column data
if(rdr.IsDBNull(c) == true)
dataValue = " is NULL";
else
dataValue =
": " +
rdr.GetValue(c);
// display column meta data and data
output = metadataValue + dataValue;
Console.WriteLine(output);
}
nRows++;
}
Console.WriteLine(" Total Rows: " +
nRows);
} while(rdr.NextResult());
Console.WriteLine("Total Result sets: " + results);
con.Close();
}
