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; }
public void UseDataAdapter(PgSqlConnection pgConnection) { PgSqlDataAdapter myAdapter = new PgSqlDataAdapter("SELECT DeptNo, DName FROM Test.Dept", pgConnection); myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; DataSet myDataSet = new DataSet(); myAdapter.Fill(myDataSet, "Departments"); object[] rowVals = new object[2]; rowVals[0] = 40; rowVals[1] = "Operations"; myDataSet.Tables["Departments"].Rows.Add(rowVals); myAdapter.InsertCommand = new PgSqlCommand("INSERT INTO Test.Dept (DeptNo, DName) " + "VALUES (:DeptNo, :DName)", pgConnection); myAdapter.InsertCommand.Parameters.Add("DeptNo", PgSqlType.Int, 0, "DeptNo"); myAdapter.InsertCommand.Parameters.Add("DName", PgSqlType.VarChar, 15, "DName"); myAdapter.Update(myDataSet, "Departments"); //Get all data from all tables within the dataset foreach (DataTable myTable in myDataSet.Tables) { foreach (DataRow myRow in myTable.Rows) { foreach (DataColumn myColumn in myTable.Columns) { Console.Write(myRow[myColumn] + "\t"); } Console.WriteLine(); } Console.WriteLine(); } }
//===================================== // METODOS PostgreSQL //===================================== public DataTable BD_PostgreSQL(string EndServidor, string NomeBD, string NomeTabela, string Login, string Senha, string Porta, string ClauSelect, string ClauWhere) { DataSet ds = new DataSet(); try { string strConexao = "Server=" + EndServidor + ";Port=" + Porta + ";Database=" + NomeBD + ";User Id=" + Login + ";Password="******"SELECT " + ClauSelect + " FROM " + NomeTabela + " WHERE " + ClauWhere + ";", Postgreconn); Postgreda.Fill(ds); } catch (PgSqlException Ex) { MessageBox.Show("Não foi possível carregar o banco de dados PostgreSQL.\r\n" + Ex.Message + "\r\n" + Ex.DetailMessage + "\r\n" + Ex.ErrorCode.ToString()); ds.Tables.Add(); } catch (Exception Ex) { MessageBox.Show("Não foi possível carregar o banco de dados PostgreSQL.\r\n" + Ex.Message); ds.Tables.Add(); } return(ds.Tables[0]); }
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); }