Exemplo n.º 1
0
        public DataSet ExecuteCommand(PgSqlCommand command)
        {
            command.Connection = connection;
            var adapter = new PgSqlDataAdapter(command);
            var ds      = new DataSet();

            adapter.Fill(ds);
            return(ds);
        }
Exemplo n.º 2
0
        /// <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);
            }
        }
Exemplo n.º 4
0
        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.");
            }
        }
Exemplo n.º 5
0
		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.");
			}

		}
Exemplo n.º 6
0
        /// <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;
            }
        }
Exemplo n.º 8
0
    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);
    }