public DataSet ExecuteCommand(PgSqlCommand command)
{
command.Connection = connection;
var adapter = new PgSqlDataAdapter(command);
var ds = new DataSet();
adapter.Fill(ds);
return(ds);
}
/// <summary>
/// Regresa un DataTable con la información de la tabla consultada.
/// </summary>
/// <param name="consulta">Querey Select</param>
/// <param name="nombreTabla">Nombre del DataTable a retornar</param>
/// <returns>DataTable con la información consultada</returns>
public DataTable Consulta(string consulta)
{
DataTable dt = new DataTable();
try
{
using (PgSqlConnection cn = new PgSqlConnection(this.sConexion))
{
PgSqlDataAdapter puente = new PgSqlDataAdapter(consulta, cn);
puente.Fill(dt);
}
}
catch (Exception)
{
throw;
}
return(dt);
}
private void btnNext_Click(object sender, RoutedEventArgs e)
{
try
{
string server = txtServer.Text;
string userName = txtUserName.Text;
string password = txtPassword.Password;
string database = txtDatabase.Text;
string port = txtPort.Text;
string connectionString = "";
if (port == "")
{
port = "5432";
}
connectionString = "User ID=" + userName + ";Password="******";Host=" + server + ";Port=" + port + ";Database=" + database + ";Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;";
//Data Source=username/password@//myserver:1521/my.service.com;
//Data Source=username/password@myserver/myservice:dedicated/instancename;
PgSqlConnection con = new PgSqlConnection(connectionString);
PgSqlCommand cmd = new PgSqlCommand("SELECT * FROM pg_catalog.pg_tables where schemaname != 'pg_catalog' AND schemaname != 'information_schema'", con);
PgSqlDataAdapter adp = new PgSqlDataAdapter(cmd);
DataTable tables = new DataTable();
adp.Fill(tables);
C.postgresqlConnection = con;
C.dt = new DataTable();
C.dt = tables;
gthis.postgresqlScreen.Visibility = Visibility.Hidden;
gthis.chooseFields.Visibility = Visibility.Visible;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void Test()
{
string connectionString;
string sqlQuery;
PgSqlDataAdapter adapter;
DataSet dataSet = null;
connectionString =
"host=localhost;" +
"dbname=test;" +
"user=postgres";
sqlQuery = "select * from pg_tables";
System.Console.WriteLine("new PgSqlDataAdapter...");
adapter = new PgSqlDataAdapter(sqlQuery,
connectionString);
System.Console.WriteLine("new DataSet...");
dataSet = new DataSet();
try {
System.Console.WriteLine("Fill...");
adapter.Fill(dataSet);
}
catch (NotImplementedException e) {
Console.WriteLine("Exception Caught: " + e);
}
System.Console.WriteLine("get row...");
if (dataSet != null)
{
foreach (DataRow row in dataSet.Tables["Table"].Rows)
{
Console.WriteLine("tablename: " + row["tablename"]);
}
System.Console.WriteLine("Done.");
}
}
public static void Test()
{
string connectionString;
string sqlQuery;
PgSqlDataAdapter adapter;
DataSet dataSet = null;
connectionString =
"host=localhost;" +
"dbname=test;" +
"user=postgres";
sqlQuery = "select * from pg_tables";
System.Console.WriteLine ("new PgSqlDataAdapter...");
adapter = new PgSqlDataAdapter (sqlQuery,
connectionString);
System.Console.WriteLine ("new DataSet...");
dataSet = new DataSet ();
try {
System.Console.WriteLine("Fill...");
adapter.Fill (dataSet);
}
catch (NotImplementedException e) {
Console.WriteLine("Exception Caught: " + e);
}
System.Console.WriteLine ("get row...");
if (dataSet != null) {
foreach (DataRow row in dataSet.Tables["Table"].Rows)
Console.WriteLine("tablename: " + row["tablename"]);
System.Console.WriteLine("Done.");
}
}
/// <summary>
/// Consulta a la Bd y retorna la información en un DataSet
/// </summary>
/// <param name="sCadenaConexion">Cadena de conexion</param>
/// <param name="ds">recibe de referencia un dataset</param>
public DataSet ConsultaSqlDataSet(string sConsulta)
{
DataSet dts = new DataSet();
try
{
using (PgSqlConnection cn = new PgSqlConnection(this.sConexion))
{
PgSqlDataAdapter da = new PgSqlDataAdapter(); //instanciando adaptador
da.SelectCommand = new PgSqlCommand(); //instanciando comando
da.SelectCommand.CommandText = sConsulta; //asignando consulta
da.SelectCommand.CommandTimeout = 0; //asigando timeout en 0
da.SelectCommand.CommandType = CommandType.Text; //tipo del comando
da.SelectCommand.Connection = cn;
da.Fill(dts); //llenando el dataset
}
}
catch (Exception)
{
throw;
}
return(dts);
}
private void UserControl_IsVisibleChanged(object sender, DependencyPropertyChangedEventArgs e)
{
if (((UserControl)sender).Visibility == Visibility.Visible)
{
if (C.selectedDatabase == "Text File" && C.enterChooseFields == false)
{
dt = C.dt;
cmbTables.Items.Clear();
cmbTables.Items.Add(C.safeFileName);
cmbTables.SelectedIndex = 0;
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, " " + "(" + C.safeFileName + ">" + dt.Columns[i].Caption + ")"));
}
}
else if (C.selectedDatabase == "Microsoft Excel" && C.enterChooseFields == false)
{
List <string> pages = GetExcelOrAccessTablesNames(C.fileName, true);
if (pages == null)
{
gthis.Close();
}
cmbTables.Items.Clear();
for (int i = 0; i < pages.Count; i++)
{
cmbTables.Items.Add(pages[i]);
}
cmbTables.SelectedIndex = 0;
cmbTables.IsEnabled = true;
dt = ImportExcelorAccesstoDatatable(C.fileName, cmbTables.SelectedItem.ToString(), true);
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, " " + "(" + C.safeFileName + ">" + dt.Columns[i].Caption + ")"));
}
}
else if (C.selectedDatabase == "Microsoft Access" && C.enterChooseFields == false)
{
List <string> pages = GetExcelOrAccessTablesNames(C.fileName, false);
if (pages == null)
{
gthis.Close();
}
cmbTables.Items.Clear();
for (int i = 0; i < pages.Count; i++)
{
cmbTables.Items.Add(pages[i]);
}
cmbTables.SelectedIndex = 0;
cmbTables.IsEnabled = true;
dt = ImportExcelorAccesstoDatatable(C.fileName, cmbTables.SelectedItem.ToString(), false);
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, " " + "(" + C.safeFileName + ">" + dt.Columns[i].Caption + ")"));
}
}
else if (C.selectedDatabase == "MySQL")
{
dt = C.dt;
try
{
cmbTables.Items.Clear();
}
catch (Exception ex)
{
}
for (int i = 0; i < dt.Rows.Count; i++)
{
cmbTables.Items.Add(dt.Rows[i]["TABLE_NAME"]);
}
cmbTables.SelectedIndex = 0;
cmbTables.IsEnabled = true;
MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + cmbTables.SelectedItem.ToString(), C.mysqlConnection);
MySqlDataAdapter adp = new MySqlDataAdapter(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, ""));
}
}
else if (C.selectedDatabase == "Microsoft SQL Server")
{
dt = C.dt;
try
{
cmbTables.Items.Clear();
}
catch (Exception ex)
{
}
for (int i = 0; i < dt.Rows.Count; i++)
{
cmbTables.Items.Add(dt.Rows[i]["TABLE_NAME"]);
}
cmbTables.SelectedIndex = 0;
cmbTables.IsEnabled = true;
SqlCommand cmd = new SqlCommand("SELECT * FROM " + cmbTables.SelectedItem.ToString(), C.sqlserverConnection);
SqlDataAdapter adp = new SqlDataAdapter(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, ""));
}
}
else if (C.selectedDatabase == "Oracle")
{
dt = C.dt;
try
{
cmbTables.Items.Clear();
}
catch (Exception ex)
{
}
for (int i = 0; i < dt.Rows.Count; i++)
{
cmbTables.Items.Add(dt.Rows[i]["TABLE_NAME"]);
}
cmbTables.SelectedIndex = 0;
cmbTables.IsEnabled = true;
OracleCommand cmd = new OracleCommand("SELECT * FROM " + cmbTables.SelectedItem.ToString(), C.oracleConnection);
OracleDataAdapter adp = new OracleDataAdapter(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, ""));
}
}
else if (C.selectedDatabase == "PostgreSQL")
{
dt = C.dt;
try
{
cmbTables.Items.Clear();
}
catch (Exception ex)
{
}
for (int i = 0; i < dt.Rows.Count; i++)
{
cmbTables.Items.Add(dt.Rows[i]["tablename"]);
}
cmbTables.SelectedIndex = 0;
cmbTables.IsEnabled = true;
//SELECT * FROM pg_catalog.pg_tables where schemaname != 'pg_catalog' AND schemaname != 'information_schema'
PgSqlCommand cmd = new PgSqlCommand("SELECT * FROM " + cmbTables.SelectedItem.ToString(), C.postgresqlConnection);
PgSqlDataAdapter adp = new PgSqlDataAdapter(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, ""));
}
}
else if (C.selectedDatabase == "Firebird")
{
dt = C.dt;
try
{
cmbTables.Items.Clear();
}
catch (Exception ex)
{
}
for (int i = 0; i < dt.Rows.Count; i++)
{
cmbTables.Items.Add(dt.Rows[i]["RDB$RELATION_NAME"]);
}
cmbTables.SelectedIndex = 0;
cmbTables.IsEnabled = true;
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, ""));
}
}
else if (C.selectedDatabase == "WSDL")
{
dt = C.dt;
try
{
cmbTables.Items.Clear();
}
catch (Exception ex)
{
}
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, ""));
}
}
C.enterChooseFields = true;
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;
}
}
public DataSet FetchData(string Query)
{
try {
connection = new PgSqlConnection(ConfigurationManager.ConnectionStrings["ProviderService"].ConnectionString);
dataAdapter = new PgSqlDataAdapter("", connection);
switch (Query)
{
case "all employees":
dataAdapter.SelectCommand.CommandText = "SELECT * FROM emp";
break;
case "all departments":
dataAdapter.SelectCommand.CommandText = "SELECT * FROM dept";
break;
case "employees having commission":
dataAdapter.SelectCommand.CommandText = "SELECT * FROM emp WHERE comm > 0";
break;
case "average salary by departments":
dataAdapter.SelectCommand.CommandText = "SELECT dname AS department, AVG(sal) AS average_salary FROM dept, emp WHERE dept.deptno=emp.deptno GROUP BY dname";
break;
case "employees' hire by years":
dataAdapter.SelectCommand.CommandText = "SELECT TO_CHAR(hiredate, 'YYYY') AS year, COUNT(empno) AS quantity FROM emp GROUP BY 1 ORDER BY 1";
break;
case "employees' number by departments":
dataAdapter.SelectCommand.CommandText = "SELECT dname, COUNT(empno) FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno GROUP BY dname";
break;
case "employees having no subordinates":
dataAdapter.SelectCommand.CommandText = "SELECT e1.* FROM emp e1 LEFT JOIN emp e2 ON e1.empno = e2.mgr WHERE e2.mgr IS NULL";
break;
case "employees having subordinates and manager":
dataAdapter.SelectCommand.CommandText = "SELECT * FROM emp WHERE (mgr IS NOT NULL) AND (empno IN (SELECT DISTINCT mgr FROM emp))";
break;
case "employees with minimal salary by departments":
dataAdapter.SelectCommand.CommandText = "SELECT dname, ename, sal FROM dept, emp WHERE sal = (SELECT MIN(sal) from emp WHERE dept.deptno = emp.deptno)";
break;
case "seniority-salary trend":
dataAdapter.SelectCommand.CommandText = "SELECT TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'YYYY'), 'S9999')-TO_NUMBER(TO_CHAR(hiredate, 'YYYY'), 'S9999') AS seniority, AVG(sal) AS average_salary FROM emp GROUP BY 1 ORDER BY 1";
break;
default:
return(null);
}
dataAdapter.Fill(dataSet);
}
catch (Exception ex) {
throw new Exception(ex.Message);
}
finally {
if (connection != null)
{
connection.Close();
}
}
return(dataSet);
}