public static void getWords() { #region Npgsql NpgsqlConnection conn; conn = new NpgsqlConnection("Server=" + Settings.DBSrvIP + ";Port=" + Settings.DBSrvPort + ";User Id=" + Settings.DBconnectID + ";Password="******";Database=endoDB;" + Settings.sslSetting); try { conn.Open(); } catch (NpgsqlException) { MessageBox.Show(Properties.Resources.CouldntOpenConn, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); conn.Close(); } catch (System.IO.IOException) { MessageBox.Show(Properties.Resources.ConnClosed, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); conn.Close(); } #endregion string sql = "SELECT no, words1, words2, words3, operator, word_order FROM words ORDER BY word_order"; NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn); da.Fill(localDB, "words"); if (localDB.Tables["words"].Rows.Count == 0) { MessageBox.Show("[words]" + Properties.Resources.NoRecord, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } conn.Close(); }
public DataTable ExecuteSelectQuery(NpgsqlCommand command, NpgsqlConnection conn) { try { if (conn == null) { return null; } DataTable dataTable = new DataTable(); DataSet dataSet = new DataSet(); NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(command); dataSet.Reset(); dataAdapter.Fill(dataSet); dataTable = dataSet.Tables[0]; conn.Close(); return dataTable.Rows.Count <= 0 ? null : dataTable; } catch (NpgsqlException ex) { Console.WriteLine(ex.Message); return null; } }
public List <Models.TestSet.Test_Set> GetTestSet(Guid ID) { List <Models.TestSet.Test_Set> list = new List <Models.TestSet.Test_Set>(); con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"select * from qadata.ref_testset where row_idnumber = @ID;", con); cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = ID; da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); foreach (DataRow DR in ds.Tables[0].Rows) { Models.TestSet.Test_Set item = new Models.TestSet.Test_Set { Id = new Guid(Convert.ToString(DR["row_idnumber"])), Title = Convert.ToString(DR["testset_title"]), Description = Convert.ToString(DR["testset_description"]), testset_id = Convert.ToInt32(DR["testset_id"]), Status = Convert.ToInt32(DR["status"]) }; list.Add(item); } return(list); }
public List <Models.TestSet.Test_Mapping> GetTestSetsCases(Guid TestSet_IDNUM) { List <Models.TestSet.Test_Mapping> list = new List <Models.TestSet.Test_Mapping>(); con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"SELECT MAPP.*, tc.test_case_title FROM qadata.testset_case_mapping MAPP join qadata.testcasemain tc on MAPP.test_idnum = tc.row_idnum where MAPP.testset_idnum = @TestSet_IDNUM and MAPP.status >= 1 order by order_number;", con); cmd.Parameters.Add("@TestSet_IDNUM", NpgsqlTypes.NpgsqlDbType.Uuid).Value = TestSet_IDNUM; da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); foreach (DataRow DR in ds.Tables[0].Rows) { Models.TestSet.Test_Mapping item = new Models.TestSet.Test_Mapping { Id = new Guid(Convert.ToString(DR["row_idnum"])), TestCase_Title = Convert.ToString(DR["test_case_title"]), Order_Number = Convert.ToInt32(DR["order_number"]), Test_Id = new Guid(Convert.ToString(DR["test_idnum"])), Test_Set_Id = new Guid(Convert.ToString(DR["testset_idnum"])), Status = Convert.ToInt32(DR["status"]), }; list.Add(item); } return(list); }
public List <Models.TestLevels.TestStructures> GetTopStructures() { List <Models.TestLevels.TestStructures> list = new List <Models.TestLevels.TestStructures>(); con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"select * from qadata.ref_structure where parent_idnumber = '00000000-0000-0000-0000-000000000000' order by name;", con); da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); foreach (DataRow DR in ds.Tables[0].Rows) { Models.TestLevels.TestStructures item = new Models.TestLevels.TestStructures(); item.id = new Guid(Convert.ToString(DR["row_idnumber"])); item.Name = Convert.ToString(DR["name"]); item.ShortName = Convert.ToString(DR["srtname"]); item.Parent_IDNumber = new Guid(Convert.ToString(DR["parent_idnumber"])); list.Add(item); } return(list); }
//Йнкции пользователя /// <summary> /// добавление\удаление пользователя /// добавление записи /// удаление своей незакрытой записи /// проверка /// изменение прав пользователя /// /// удаление закрытой записи /// редактирование своей записи /// редактирование чужой записи /// /// </summary> public RegisterForm() { InitializeComponent(); divList = new List<string>(); staList = new List<string>(); speList = new List<string>(); NpgsqlConnection cdbo = DBExchange.Inst.connectDb; NpgsqlDataAdapter div = new NpgsqlDataAdapter("Select trim(name) from divisions order by division ASC", cdbo); DataTable divTable = new DataTable(); div.Fill(divTable); foreach (DataRow roww in divTable.Rows ) { divList.Add((string)roww[0]); } otdel.DataSource = divList; otdel.SelectedIndex = 1; NpgsqlDataAdapter sta = new NpgsqlDataAdapter("Select trim(name) from posts order by post ASC", cdbo); DataTable staTable = new DataTable(); sta.Fill(staTable); foreach (DataRow roww in staTable.Rows) { staList.Add((string)roww[0]); } status.DataSource = staList; status.SelectedIndex = 39; //special.SelectedIndex = 0; }
private List<string> getFreeDoctors() { DTDoctors = new DataTable(); string gfd = "Select doc_id, trim(family_name) as family_name, trim(first_name) as first_name from doctors where special < 4"; fDoc = new List<string>(); if (this.comboBox1.Items.Count > 0) { int _trial = (int)DTuserTrials.Rows[this.comboBox1.SelectedIndex]["tr_id"]; gfd = "Select doc_id, trim(family_name) as family_name, trim(first_name) as first_name from doctors ds where ds.special < 4 and ds.doc_id not in " +"(select doc_id from trial_role where trial_id = '"+_trial+"')"; } NpgsqlDataAdapter getfd = new NpgsqlDataAdapter(gfd,DBExchange.Inst.connectDb); try { getfd.Fill(DTDoctors); } catch (Exception exception) { System.Windows.Forms.MessageBox.Show(exception.Message.ToString() + " getFreeDoctors"); } foreach (DataRow ro in DTDoctors.Rows) { fDoc.Add(ro["family_name"] + " " + ro["first_name"]); } return fDoc; }
protected void OnExecuteActionActivated(object sender, System.EventArgs e) { string connectionString = "Server=localhost;Database=aula;User Id=aula;Password=clase" ; IDbConnection dbConnection = new NpgsqlConnection(connectionString); IDbCommand selectCommand = dbConnection.CreateCommand(); selectCommand.CommandText = "select * from articulo where id=4"; IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter(); dbDataAdapter.SelectCommand = selectCommand; DataSet dataSet = new DataSet(); dbDataAdapter.Fill (dataSet); Console.WriteLine("Tables.Count={0}", dataSet.Tables.Count); foreach (DataTable dataTable in dataSet.Tables) show (dataTable); DataRow dataRow = dataSet.Tables[0].Rows[0]; dataRow["nombre"] = DateTime.Now.ToString (); Console.WriteLine("Tabla con los cambios"); show (dataSet.Tables[0]); dbDataAdapter.Update (dataSet); }
protected void Page_Load(object sender, EventArgs e) { try { string connstring = "Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;" + "User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;ssl=true"; NpgsqlConnection conn = new NpgsqlConnection(connstring); conn.Open(); string sql = "SELECT c.name as name1, a.movie, a.id id, b.all, trunc(100*a.movie/b.all,6) procent from genre c, (select count(*) as all from movie_genre) b, (select count(movie_id) as movie, genre_id as id from movie_genre group by genre_id) a where c.id = a.id"; NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn); da.Fill(ds, "movie_genre"); dt = ds.Tables[0]; foreach (DataRow row in ds.Tables[0].Rows) { PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue { Category = row["name1"].ToString(), Data = Convert.ToDecimal(row["procent"]) }); } conn.Close(); } catch (Exception msg) { } }
protected void OnPropertiesActionActivated(object sender, System.EventArgs e) { string connectionString = "Server=localhost; Database=pruebaBD; Id=ximo; password=admin"; IDbConnection dbConnection = new NpgsqlConnection(connectionString); IDbCommand selectCommand = dbConnection.CreateCommand(); selectCommand.CommandText = "select * BaseDatos1.from articulo where id=1"; IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter(); dbDataAdapter.SelectCommand = selectCommand; DataSet dataSet = new DataSet(); dbDataAdapter.Fill (dataSet); Console.WriteLine("Tables.Count={0}, dataSet.Tables.Count"); foreach (DataTable dataTable in dataSet.Tables){ Show(dataTable); DataRow dataRow = dataSet.Tables[0].Rows[0]; dataRow["Nombre"] = DateTime.Now.ToString(); Console.WriteLine("Tabla con los cambios: "); Show (dataSet.Tables[0]); dbDataAdapter.Update (dataSet); } }
public List <Models.Automation.Test_Run_Notes> GetRunNote(Guid ID) { List <Models.Automation.Test_Run_Notes> list = new List <Models.Automation.Test_Run_Notes>(); con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"select * from automation.runnotes where row_idnum = @ID;", con); cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = ID; da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); foreach (DataRow DR in ds.Tables[0].Rows) { Models.Automation.Test_Run_Notes item = new Models.Automation.Test_Run_Notes { idnum = new Guid(Convert.ToString(DR["row_idnum"])), run_idnum = new Guid(Convert.ToString(DR["run_idnum"])), testset_idnum = new Guid(Convert.ToString(DR["testset_idnum"])), test_idnum = new Guid(Convert.ToString(DR["test_idnum"])), id = Convert.ToInt32(DR["id"]), notes = Convert.ToString(DR["notes"]), method_name = Convert.ToString(DR["method_name"]), Status = Convert.ToInt32(DR["status"]) }; list.Add(item); } return(list); }
protected void Page_Load(object sender, EventArgs e) { string rola; FormsIdentity id = (FormsIdentity)HttpContext.Current.User.Identity; FormsAuthenticationTicket bilet = id.Ticket; Label1.Text = "Zalogowany jako: " + User.Identity.Name; // Get the stored user-data, in this case, our roles rola = bilet.UserData; if (rola != "admins") Response.Redirect("index.aspx"); DataSet ds = new DataSet(); DataTable dt = new DataTable(); NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;"); conn.Open(); // quite complex sql statement string sql = "SELECT * FROM sala"; // data adapter making request from our connection NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn); // i always reset DataSet before i do // something with it.... i don't know why :-) ds.Reset(); // filling DataSet with result from NpgsqlDataAdapter da.Fill(ds); // since it C# DataSet can handle multiple tables, we will select first dt = ds.Tables[0]; // connect grid to DataTable GridView1.DataSource = dt; GridView1.DataBind(); // since we only showing the result we don't need connection anymore conn.Close(); }
public bool ExecuteQuery(out DataTable dt) { dt = new DataTable(); try { if (!string.IsNullOrEmpty(Script)) { Conectar(); using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(Script, Connection)) { Parametros.ForEach(x => cmd.Parameters.AddWithValue(x.Parameter.ToString(), x.Valor)); Npgsql.NpgsqlDataAdapter da = new Npgsql.NpgsqlDataAdapter(cmd); da.Fill(dt); } return(true); } msg = "Comando inválido."; return(false); } catch (NpgsqlException ex) { msg = ex.Message; return(false); } finally { if (Transacao == false) { Desconectar(); } } }
public static DataSet ExecuteDataset(string connectionString, string commandText) { NpgsqlConnection conn = new NpgsqlConnection(connectionString); try { conn.Open(); NpgsqlCommand command = new NpgsqlCommand(commandText, conn); var da = new Npgsql.NpgsqlDataAdapter(command); DataSet _ds = new DataSet(); da.Fill(_ds); conn.Close(); return(_ds); } catch (Exception ex) { Common.log.Error(ex.ToString()); if ((conn != null)) { conn.Close(); } return(new DataSet()); } finally { if ((conn != null)) { conn.Dispose(); } } }
protected void Page_Load(object sender, EventArgs e) { string sQuery = "SELECT title, vote_average from movie order by vote_average DESC, release_date ASC LIMIT 20"; string sConn = String.Format("Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;Protocol=3;SSL=true;SslMode=Require;"); NpgsqlConnection Conn = new NpgsqlConnection(sConn); Conn.Open(); DataSet DS = new DataSet("DS1"); NpgsqlDataAdapter DA = new NpgsqlDataAdapter(); DA.SelectCommand = new NpgsqlCommand(sQuery, Conn); DA.Fill(DS, "DS1"); DS.Tables[0].Columns.Add("Linki", typeof(string)); foreach (DataRow item in DS.Tables[0].Rows) { string link="http://www.omdbapi.com/?t=" + item[0].ToString(); //string link=String.Format("<a href=\"http://www.omdbapi.com/?t={0}\">Opis</a>", item[0].ToString()); item[2] = link; } GridView1.DataSource = DS; GridView1.DataBind(); Conn.Close(); }
protected void OnExecuteActionActivated(Object sender, System.EventArgs e) { String connectionString ="Server=localhost;Database=dbprueba;User Id=dbprueba;Password=1234"; NpgsqlCommand selectCommand = dbConnection.CreateCommand (); selectCommand.CommandText = "select * from categoria"; NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter(); new NpgsqlCommandBuilder(dbDataAdapter); dbDataAdapter.SelectCommand = selectCommand; DataSet dataset = new Dataset(); dbDataAdapter.Fill (DataSet); //Console.WriteLine("Tables.Count={0}", dataSet.Tables.Count); foreach(DataTable dataTable in dataset.Tables) show (dataTable); DataRow dataRow = dataset.Tables[0].Rows[0]; dataRow["Nombre"] = DateTime.Now.ToString(); Console.WriteLine("Cambios"); show (dataset.Tables[0]); dbDataAdapter.Update(dataset); }
public static IEnumerable<TerrainService.shPoint> getPolygonPoints(string polygon_guid) { List<TerrainService.shPoint> pntsList = new System.Collections.Generic.List<TerrainService.shPoint>(); string sql = "select * from polygon_points where polygon_guid='" + polygon_guid + "' order by point_num"; using (NpgsqlConnection connection = new NpgsqlConnection(strPostGISConnection)) using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, connection)) { DataSet ds = new DataSet(); DataTable dt = new DataTable(); ds.Reset(); da.Fill(ds); dt = ds.Tables[0]; if (dt == null || dt.Rows == null || dt.Rows.Count == 0) { return null; } foreach (DataRow row in dt.Rows) { TerrainService.shPoint mp = new TerrainService.shPoint(); mp.x = System.Convert.ToDouble(row["pointx"]); mp.y = System.Convert.ToDouble(row["pointy"]); pntsList.Add(mp); } } return pntsList; }
protected void OnExecuteActionActivated(object sender, System.EventArgs e) { string connectionString = "Server=localhost; Database=dbprueba; Id=javi; password=sistemas"; NpgsqlConnection dbConnection = new NpgsqlConnection(connectionString); NpgsqlCommand selectCommand = dbConnection.CreateCommand(); selectCommand.CommandText = "select * from articulo"; NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter(); new NpgsqlCommandBuilder((NpgsqlDataAdapter)dbDataAdapter); dbDataAdapter.SelectCommand = selectCommand; DataSet dataSet = new DataSet(); dbDataAdapter.Fill (dataSet); Console.WriteLine("Tables.Count={0}, dataSet.Tables.Count"); foreach (DataTable dataTable in dataSet.Tables){ show (dataTable); DataRow dataRow = dataSet.Tables[0].Rows[0]; dataRow["nombre"] = DateTime.Now.ToString(); Console.WriteLine("Tabla con los cambios:"); show (dataSet.Tables[0]); // dbDataAdapter.RowUpdated +- delegate(object dbDadapterSender, NpgsqlRowUpdatingEventArgs eventArgs){ // Console.WriteLine("RowUpdating command.CommandText= {0}", EventArgs.Command.CommantText); // // foreach (IDataParameter dataParameter in EventArgs.Command.Parameters) // Console.WriteLine ("{0}={1}", dataParameter.ParameterName, dataParameter.Value); }; dbDataAdapter.Update(dataSet.Tables[0]); }
protected void Page_Load(object sender, EventArgs e) { try { string connstring = "Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;" + "User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;ssl=true"; NpgsqlConnection conn = new NpgsqlConnection(connstring); conn.Open(); string sql = "SELECT id, original_title, vote_average FROM movie ORDER BY vote_average DESC, release_date ASC LIMIT 20"; NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn); da.Fill(ds,"movie"); dt = ds.Tables[0]; this.GridView1.AutoGenerateColumns = false; GridView1.DataSource = dt; GridView1.DataBind(); this.GridView1.Columns[1].Visible = false; conn.Close(); } catch (Exception msg) { } }
public void CreateNodesVendor() { try { Postgres.ConnectionString = "Server=" + Properties.Settings.Default.Server + ";Port=" + Properties.Settings.Default.Port + ";User Id=" + Properties.Settings.Default.UserId + ";Password="******";Database=" + Properties.Settings.Default.Database + ";"; Postgres.Open(); var ds = new DataSet(); var commandVendors = new NpgsqlCommand("SELECT * FROM \"GPL\".\"VENDORS\";", Postgres); var dataAdapterV = new NpgsqlDataAdapter(commandVendors); dataAdapterV.Fill(ds, "VENDORS"); foreach (DataRow dr in ds.Tables["VENDORS"].Rows) { cbVendor.Items.Add(dr["VENDOR"].ToString()); } cbVendor.Sorted = true; ds.Clear(); Postgres.Close(); } catch (Exception e) { throw new Exception(@"Ошибка: ", e); } }
public DataTable[] ExecuteDataset(CommandType commandType, string query, NpgsqlParameter[] npgsqlParameters) { using (NpgsqlCommand command = GetCommand(query, npgsqlParameters, commandType)) { try { DataSet myDS = new DataSet(); NpgsqlTransaction t = command.Connection.BeginTransaction(); NpgsqlDataAdapter da = new NpgsqlDataAdapter(command); da.Fill(myDS); t.Commit(); DataTable[] tables = new DataTable[myDS.Tables.Count]; myDS.Tables.CopyTo(tables, 0); return tables; } catch (Exception Ex) { throw Ex; } finally { command.Connection.Close(); } } }
public static DataTable GetTable(string catalog, string sql, Collection<ReportParameter> parameters) { using (NpgsqlConnection connection = new NpgsqlConnection(GetConnectionString(catalog))) { using (NpgsqlCommand command = new NpgsqlCommand(sql, connection)) { foreach (ReportParameter parameter in parameters) { if (parameter != null && !string.IsNullOrWhiteSpace(parameter.Name)) { command.Parameters.AddWithValue(parameter.Name, parameter.TestValue); } } using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command)) { using (DataTable dataTable = new DataTable()) { dataTable.Locale = Thread.CurrentThread.CurrentCulture; dataTable.TableName = "Report"; adapter.Fill(dataTable); return dataTable; } } } } }
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { if (comboBox1.SelectedValue.ToString() == "Quartiers") { grd.ClearSelection(); cmd = new n.NpgsqlCommand("select code,nom,densite from quartier", cnx); ada = new n.NpgsqlDataAdapter(cmd); dt = new DataTable(); ada.Fill(dt); grd.DataSource = dt; } else { if (comboBox1.SelectedValue.ToString() == "Antennes") { grd.ClearSelection(); cmd = new n.NpgsqlCommand("select num,nom,puissance from antenne", cnx); ada = new n.NpgsqlDataAdapter(cmd); dt = new DataTable(); ada.Fill(dt); grd.DataSource = dt; } else { dt = new DataTable(); grd.DataSource = dt; } } }
public Guid Maintain_RunNotes(Models.Automation.Test_Run_Notes item, string username) { con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"SELECT automation.maintain_runnote(@IdNum, @run_IdNum, @testset_idnum,@test_IdNum, @ID,@method_name, @notes, @username, @status);", con); cmd.Parameters.Add("@IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.idnum; cmd.Parameters.Add("@run_IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.run_idnum; cmd.Parameters.Add("@testset_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.testset_idnum; cmd.Parameters.Add("@test_IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.test_idnum; cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.id; cmd.Parameters.Add("@method_name", NpgsqlTypes.NpgsqlDbType.Text).Value = item.method_name ?? ""; cmd.Parameters.Add("@notes", NpgsqlTypes.NpgsqlDbType.Text).Value = item.notes ?? ""; cmd.Parameters.Add("@username", NpgsqlTypes.NpgsqlDbType.Text).Value = username; cmd.Parameters.Add("@status", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.Status; da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); Guid IDNUM = new Guid(Convert.ToString(ds.Tables[0].Rows[0]["maintain_runnote"])); return(IDNUM); }
private void pay_Load(object sender, EventArgs e) { NpgsqlCommand acc_info = new NpgsqlCommand("SELECT DISTINCT " + " places_cnt1.name, " + "places_cnt2.name, " + "public.acc_info.flat " + "FROM " + "public.places_cnt places_cnt2 " + "INNER JOIN public.places_cnt places_cnt1 ON (places_cnt2.parent_id = places_cnt1.plc_id) " + "INNER JOIN public.places_cnt ON (places_cnt1.parent_id = public.places_cnt.plc_id) " + "INNER JOIN public.acc_info ON (places_cnt2.plc_id = public.acc_info.plc_id) " + "WHERE " + " public.acc_info.acc_id = " + all_user.acc_code + "", conn); conn.Open(); NpgsqlDataReader reader = acc_info.ExecuteReader(); while (reader.Read()) { this.Text = "Оплаты по ЛС: " + all_user.acc_code + " Адрес: " + reader[0].ToString() + " " + reader[1].ToString() + " " + reader[2].ToString(); } conn.Close(); DataTable dt = new DataTable(); NpgsqlDataAdapter da = new NpgsqlDataAdapter("SELECT * FROM public.pay WHERE acc_id = " + all_user.acc_code + " ORDER BY date_pay", conn); conn.Open(); da.Fill(dt); conn.Close(); dataGridView1.DataSource = dt; }
public static DataSet actualShow(String channelNumber) { string sSel; bool exist; if (channelNumber != "") { sSel = "SELECT * "; sSel += "FROM \"tbl_Program\" p, \"tbl_Channel\" c, \"tbl_Schedule\" s "; sSel += "WHERE s.\"idProgram\" = p.\"idProgram\" AND s.\"idChannel\" = c.\"idChannel\" AND '" + DateTime.Now.ToString() + "' BETWEEN \"startDate\" AND \"endDate\" AND \"endDate\"<> '" + DateTime.Now.ToString() + "' AND c.\"channelNumber\"=" + channelNumber + " "; } else { sSel = ""; return null; } NpgsqlDataAdapter da; DataSet dt = new DataSet(); try { da = new NpgsqlDataAdapter(sSel, sConexion); da.Fill(dt); if (dt.Tables[0].Rows[0][0].ToString() == "0") exist = false; else exist = true; } catch (Exception) { return null; } return dt; }
public static User[] GetChatUsers() { if (ConnectToDatabase()) { string query = "SELECT \"Mail\", \"Username\", \"LastLogin\", \"IsAdmin\", \"ID\" FROM User"; NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, mDatabaseConnection); ds.Reset(); da.Fill(ds); dt = ds.Tables[0]; User[] users = new User[dt.Rows.Count]; int count = 0; foreach (DataRow row in dt.Rows) { User u = new User(); u.UserID = row.Field<int>(dt.Columns[4]); u.Email = row.Field<string>(dt.Columns[0]); u.Username = row.Field<string>(dt.Columns[1]); u.IsAdmin = row.Field<bool>(dt.Columns[3]); u.LastOnline = row.Field<DateTime>(dt.Columns[2]); users[count] = u; count++; } DisconnectFromDatabase(); return users; } return null; }
public List <Models.Application> GetApplications() { con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"select * from qadata.ref_structure where parent_idnumber = @NoParent order by name;", con); cmd.Parameters.Add("@NoParent", NpgsqlTypes.NpgsqlDbType.Uuid).Value = new Guid(); da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); List <Models.Application> LI = new List <Models.Application>(); foreach (DataRow I in ds.Tables[0].Rows) { Models.Application application = new Models.Application(); application.id = new Guid(Convert.ToString(I["row_idnumber"])); application.Name = Convert.ToString(I["name"]); application.ShortName = Convert.ToString(I["srtname"]); LI.Add(application); } return(LI); }
public static User GetChatUser(string pEmail, string pPassword) { if (ConnectToDatabase()) { try { string query = "SELECT * FROM \"User\" WHERE \"Mail\"=\'" + pEmail + "\' AND \"Password\"=\'" + pPassword + "\'"; NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, mDatabaseConnection); ds.Reset(); da.Fill(ds); dt = ds.Tables[0]; User u = new User(); DataRow row = dt.Rows[0]; u.UserID = row.Field<int>(dt.Columns[0]); u.Email = row.Field<string>(dt.Columns[1]); u.Username = row.Field<string>(dt.Columns[3]); u.IsAdmin = row.Field<bool>(dt.Columns[5]); //u.LastOnline = row.Field<DateTime>(dt.Columns[4]); return u; } catch (Exception ex) { LoggingService.LogToFile(ex); return null; } finally { DisconnectFromDatabase(); } } return null; }
public List <Models.TestCase.Test_step> GetTestSteps(Guid Test_ID) { List <Models.TestCase.Test_step> list = new List <Models.TestCase.Test_step>(); con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"select * from qadata.test_steps where test_idnum = @Test_ID order by step_number;", con); cmd.Parameters.Add("@Test_ID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = Test_ID; da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); foreach (DataRow DR in ds.Tables[0].Rows) { Models.TestCase.Test_step item = new Models.TestCase.Test_step(); item.Expected_result = Convert.ToString(DR["expected_reult"]); item.Id = new Guid(Convert.ToString(DR["row_idnum"])); item.Status = Convert.ToInt32(DR["status"]); item.Step = Convert.ToString(DR["step"]); item.Step_num = Convert.ToInt32(DR["step_number"]); item.Test_case_id = new Guid(Convert.ToString(DR["test_idnum"])); list.Add(item); } return(list); }
/// <summary> /// 执行SELECT语句 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataTable GetData(String sql) { var datatable = new DataTable(); var adapter = new NpgsqlDataAdapter(sql, Conn); adapter.Fill(datatable); return datatable; }
/// <summary> /// 将数据读取到 DataSet 中. /// </summary> public void ReadDataToDataSet() { Console.WriteLine("使用DataAdapter,将数据填充到DataSet中,然后脱离数据库,直接对DataSet进行处理。"); // 建立数据库连接. NpgsqlConnection conn = new NpgsqlConnection(connString); // 创建一个适配器 NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(SQL, conn); // 创建DataSet,用于存储数据. DataSet testDataSet = new DataSet(); // 执行查询,并将数据导入DataSet. adapter.Fill(testDataSet, "result_data"); // 关闭数据库连接. conn.Close(); // 处理DataSet中的每一行数据. foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows) { // 将检索出来的数据,输出到屏幕上. Console.WriteLine("NO:{0} ; Date:{1} ; Money:{2} ", testRow["NO"], testRow["SALE_DATE"], testRow["SUM_MONEY"] ); } }
/// <summary> /// Metodo para obtener los valores de la cadena de conexion de la empresa /// </summary> /// <param name="compania"></param> /// <returns></returns> public DataTable EmpresaConexionETL(Int64 idEmpresa) { string add = "SELECT id ," + " usuario_etl ," + " nombre ," //+ " contrasenia_etl ," + " host ," + " puerto_compania ," + " bd_name ," + " contra_bytes ," + " llave ," + " apuntador " + " FROM empresa" + " WHERE id = " + idEmpresa; try { conP.Open(); comP = new Npgsql.NpgsqlCommand(add, conP); Npgsql.NpgsqlDataAdapter daP = new Npgsql.NpgsqlDataAdapter(comP); DataTable dt = new DataTable(); daP.Fill(dt); return(dt); } catch (Exception ex) { conP.Close(); string error = ex.Message; throw; } }
override internal void LoadAll() { try { string query = "SELECT CAST(current_database() AS character varying) AS table_catalog, " + "CAST(nc.nspname AS character varying) AS table_schema, " + "CAST(c.relname AS character varying) AS table_name, " + "CAST('BASE TABLE' AS character varying) AS table_type, d.description " + "FROM pg_namespace nc, pg_user u, pg_class c LEFT OUTER JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0 " + "WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner AND c.relkind= 'r'"; if(!this.dbRoot.ShowSystemData) { query += " AND nc.nspname <> 'pg_catalog'"; } query += " ORDER BY c.relname"; NpgsqlConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Database.Name); DataTable metaData = new DataTable(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn); adapter.Fill(metaData); cn.Close(); PopulateArray(metaData); } catch {} }
/// <summary> /// Method gets both person table and testoccasion table. /// </summary> /// <returns>Returns datatable with one row holding all info regarding a user and what test he or she have done</returns> public DataTable GetPersonAndTestInfo(string userName) { NpgsqlConnection conn = new NpgsqlConnection("Database=kompetensportal;Server=localhost;User Id=postgres;Password=anna;"); try { conn.Open(); NpgsqlCommand cmdGetUserInfo = new NpgsqlCommand("SELECT * FROM person " + "INNER JOIN testoccasion ON person.id = id_user " + "WHERE person.id = (SELECT id FROM person WHERE username = @userName) " + "ORDER BY date DESC LIMIT 1; ", conn); cmdGetUserInfo.Parameters.AddWithValue("@userName", userName); NpgsqlDataAdapter nda = new NpgsqlDataAdapter(); nda.SelectCommand = cmdGetUserInfo; DataSet ds = new DataSet(); nda.Fill(ds); DataTable dt = ds.Tables[0]; return dt; } catch (NpgsqlException ex) { NpgsqlException = ex.Message; return null; } finally { conn.Close(); } }
protected void Button1_Click(object sender, EventArgs e) { int pojemnosc = System.Int32.Parse(TextBox1.Text.ToString()); string typ_sali = DropDownList1.SelectedItem.ToString(); DataSet ds1 = new DataSet(); DataTable dt1 = new DataTable(); NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;"); string sql = "SELECT id_sali FROM sala WHERE typ_sali = '" + typ_sali + "' AND pojemnosc >= '" + pojemnosc + "'"; int ilosc_wyposazen = CheckBoxList1.Items.Count; for (int i = 0; i < ilosc_wyposazen; i++) { if (CheckBoxList1.Items[i].Selected) { string id_wyp = CheckBoxList1.Items[i].Value.ToString(); sql += "INTERSECT SELECT id_sali FROM wyp_sali where id_wyp = '" + id_wyp + "'"; } } conn.Open(); NpgsqlDataAdapter da1 = new NpgsqlDataAdapter(sql, conn); da1.Fill(ds1); dt1 = ds1.Tables[0]; GridView1.DataSource = dt1; GridView1.DataBind(); conn.Close(); }
public void refresh_table() { string text_sql = @"SELECT public.service.id, public.acc_info.acc_id, public.tarif.tarif_text, public.tarif.tarif_taxa, public.service.date_open, public.service.date_close, public.service.comment FROM public.service INNER JOIN public.tarif ON (public.service.id_tarif = public.tarif.id) INNER JOIN public.acc_info ON (public.service.acc_id = public.acc_info.acc_id) WHERE public.acc_info.acc_id =" + all_user.acc_code + ""; DataTable dt_serv = new DataTable(); NpgsqlDataAdapter all_serv = new NpgsqlDataAdapter(text_sql, conn); conn.Open(); all_serv.Fill(dt_serv); conn.Close(); dataGridView1.DataSource = dt_serv; dataGridView1.Columns[0].HeaderText = "Номер"; dataGridView1.Columns[1].HeaderText = "Лицевйо счет"; dataGridView1.Columns[2].HeaderText = "Тариф(описание)"; dataGridView1.Columns[3].HeaderText = "Такса"; dataGridView1.Columns[4].HeaderText = "Дата открытия услуги"; dataGridView1.Columns[5].HeaderText = "Дата закрытия услуги"; dataGridView1.Columns[6].HeaderText = "Комментарий"; }
public void update_table_period() { NpgsqlDataAdapter sql_table = new NpgsqlDataAdapter("SELECT * FROM period ORDER BY id DESC ", conn); DataTable table = new DataTable(); conn.Open(); sql_table.Fill(table); dataGridView1.DataSource = table; conn.Close(); DateTime commentDate = table.Rows[0].Field<DateTime>("date_cnt"); textBox1.Text = commentDate.ToString("dd.MM.yyyy"); textBox2.Text = table.Rows[0].Field<string>("name"); dataGridView1.Columns[0].Width = 60; dataGridView1.Columns[0].HeaderText = "id"; dataGridView1.Columns[1].Width = 170; dataGridView1.Columns[1].HeaderText = "Название периода"; dataGridView1.Columns[2].Width = 100; dataGridView1.Columns[2].HeaderText = "Начальная дата"; dataGridView1.Columns[3].Width = 100; dataGridView1.Columns[3].HeaderText = "Конечная дата"; dataGridView1.Columns[4].Width = 100; dataGridView1.Columns[4].HeaderText = "Дата периода"; dataGridView1.Columns[5].Width = 100; dataGridView1.Columns[5].HeaderText = "Доп."; }
protected void Button1_Click(object sender, EventArgs e) { try { string connstring = "Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;" + "User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;ssl=true"; NpgsqlConnection conn = new NpgsqlConnection(connstring); conn.Open(); string sql = "SELECT a.original_title, a.vote_average FROM movie a, movie_genre b WHERE a.id=b.movie_id AND b.genre_id = " + DropDownList1.SelectedValue +" AND a.vote_average > "+ DropDownList2.SelectedValue + " ORDER by a.vote_average ASC"; NpgsqlDataAdapter da1 = new NpgsqlDataAdapter(sql, conn); da1.Fill(ds1,"movie"); dt1 = ds1.Tables[0]; GridView1.DataSource = dt1; GridView1.DataBind(); conn.Close(); } catch (Exception msg) { } }
public DataTable ListaDeAgendasDaSala(int IdSala) { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from agenda where idsala = " + Convert.ToString(IdSala), Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
public DataTable ListaDeAgendasDoPeriodo(DateTime DataHoraIni, DateTime DataHoraFim) { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from agenda where datahorareserva >= \'" + Dados.Model_Dao_Dados.ConverterDataToStr(DataHoraIni, false) + "\' and datahorareserva <= \'" + Dados.Model_Dao_Dados.ConverterDataToStr(DataHoraFim, true) + "\' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
public DataTable ListaDeAgendas() { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from agenda", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
public DataTable ListaDeContasARecebersPeriodo(DateTime dtIni, DateTime dtFim) { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from contasareceber where datahoracriacao >= '" + Dao.Dados.Model_Dao_Dados.ConverterDataToStr(dtIni, false) + "' and datahoracriacao <= '" + Dao.Dados.Model_Dao_Dados.ConverterDataToStr(dtFim, true) + "' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
public DataTable ListaDeMovimentacaoEstoquesDoProduto(int IdProduto) { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from movimentacaoestoque where idproduto = " + Convert.ToString(IdProduto), Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
public DataTable ListaDeMovimentacaoEstoquesDoPeriodo(DateTime DataHoraIni, DateTime DataHoraFim) { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from movimentacaoestoque where datahora >= '" + Dados.Model_Dao_Dados.ConverterDataToStr(DataHoraIni, false) + "' and datahora <= '" + Dados.Model_Dao_Dados.ConverterDataToStr(DataHoraFim, true) + "' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
public DataTable MovimentacaoEstoqueAgenda(int idAgenda) { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from movimentacaoestoque where idreservaorigem = " + Convert.ToString(idAgenda) + " ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
public DataTable ListaDeMovimentacaoEstoques() { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from movimentacaoestoque", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
public DataTable ListaDeLivroCaixasPeriodo(DateTime dtIni, DateTime dtFim) { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from livrocaixa where datahora >= '" + Dao.Dados.Model_Dao_Dados.ConverterDataToStr(dtIni, false) + "' and datahora <= '" + Dao.Dados.Model_Dao_Dados.ConverterDataToStr(dtFim, true) + "' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
public List <Models.TestCase.testcase> GetStructuresTestCases(Guid StructureID) { List <Models.TestCase.testcase> list = new List <Models.TestCase.testcase>(); con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"select * from qadata.testcasemain where structure_idnum = @StructureID order by testcasenum;", con); cmd.Parameters.Add("@StructureID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = StructureID; da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); foreach (DataRow DR in ds.Tables[0].Rows) { Models.TestCase.testcase item = new Models.TestCase.testcase(); item.aprovedby = Convert.ToString(DR["aprovedby"]); item.aproveddate = Convert.ToDateTime(DR["aproveddate"]); item.assigned_to = Convert.ToString(DR["assigned_to"]); item.automated_yes_no = Convert.ToString(DR["automated_yes_no"]); item.automation_script_name = Convert.ToString(DR["automation_script_name"]); item.automation_test_idnum = new Guid(Convert.ToString(DR["automation_test_idnum"])); item.automation_test_status = Convert.ToInt16(DR["automation_test_status"]); item.br_idnum = new Guid(Convert.ToString(DR["br_idnum"])); item.client_idnum = new Guid(Convert.ToString(DR["client_idnum"])); item.feature = Convert.ToString(DR["feature"]); item.feature_idnumber = new Guid(Convert.ToString(DR["feature_idnumber"])); item.feature_type = Convert.ToString(DR["feature_type"]); item.id = new Guid(Convert.ToString(DR["row_idnum"])); item.iteration = Convert.ToString(DR["iteration"]); item.module = Convert.ToString(DR["module"]); item.notes = Convert.ToString(DR["notes"]); item.prd = Convert.ToString(DR["prd"]); item.qatestcasenum = Convert.ToInt32(DR["qatestcasenum"]); item.qa_test_idnum = new Guid(Convert.ToString(DR["qa_test_idnum"])); item.regression_yes_no = Convert.ToString(DR["regression_yes_no"]); item.requirement_id = Convert.ToString(DR["requirement_id"]); item.rorv_idnum = new Guid(Convert.ToString(DR["rorv_idnum"])); item.standard_test_idnum = new Guid(Convert.ToString(DR["standard_test_idnum"])); item.status = Convert.ToInt16(DR["status"]); item.structure_idnum = new Guid(Convert.ToString(DR["structure_idnum"])); item.testcasenum = Convert.ToInt32(DR["testcasenum"]); item.testtype = Convert.ToString(DR["testtype"]); item.test_case = Convert.ToString(DR["test_case"]); item.test_case_description = Convert.ToString(DR["test_case_description"]); item.test_case_id = Convert.ToString(DR["test_case_id"]); item.test_case_title = Convert.ToString(DR["test_case_title"]); item.test_condition = Convert.ToString(DR["test_condition"]); item.ticket_mapping_idnum = new Guid(Convert.ToString(DR["ticket_mapping_idnum"])); item.use_case = Convert.ToString(DR["use_case"]); list.Add(item); } return(list); }
public DataTable Pesquisar(int idCliente) { DataTable tabela = new DataTable(); string pesquisaSql = string.Format("select * from cliente where id = {0}", idCliente); SqlDataAdapter da = new SqlDataAdapter(pesquisaSql, Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
private void Form1_Load(object sender, EventArgs e) { conexionbd claseC = new conexionbd(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); ds.Reset(); Npgsql.NpgsqlDataAdapter da = claseC.comando("select * from ganancia_diaria"); da.Fill(ds); dt = ds.Tables[0]; dataGridV1.DataSource = dt; }
public void Button1_Click(object sender, EventArgs e) { try { const string pgConstring = "Server=localhost;Port=5432;Database=postgres;User Id=postgres;Password=1234;"; NpgsqlConnection conx = new Npgsql.NpgsqlConnection(pgConstring); if (conx.State != ConnectionState.Open) { conx.Open(); } if (conx.State == ConnectionState.Open) { string cmd1 = string.Format("SELECT user_id, user_pass, firstname, lastname, user_name FROM public.user_login WHERE user_name ='{0}';" , t3.Text ); var rder = new Npgsql.NpgsqlDataAdapter(cmd1, conx); DataSet ds = new DataSet(); rder.Fill(ds); var usr = ConvertDataTable <user_login>(ds.Tables[0]); if (usr.Count <= 0 || usr == null) { if (t1.Text == "" || t2.Text == "" || t3.Text == "" || t4.Text == "" || t5.Text == "") { ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + "กรุณากรอกข้อมูลให้ครบ" + "');", true); } else { string text = string.Format("INSERT INTO user_login( user_pass, firstname, lastname, user_name,user_grade) VALUES ('{0}','{1}','{2}','{3}','{4}');" , t4.Text , t1.Text , t2.Text , t3.Text , t5.Text); NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(text, conx); cmd.ExecuteNonQuery(); Response.Redirect("register2.aspx"); } } else { ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + "มีuserนี้อยู่ในระบบอยู่แล้ว" + "');", true); } } } catch (Exception ex) { Response.Write(ex.Message); } }
public Guid Maintain_TestCase(Models.TestCase.testcase item, string username) { con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"SELECT qadata.maintain_testcase( @v_row_idnum, @v_client_idnum, @v_structure_idnum, @v_br_idnum, @v_testtype, @v_iteration, @v_prd, @v_requirement_id, @v_use_case, @v_test_case, @v_test_case_title, @v_test_case_description, @v_module, @v_feature, @v_feature_idnumber, @v_feature_type, @v_test_case_id, @v_test_condition, @v_assigned_to, @v_notes, @v_automated_yes_no, @v_automation_script_name, @v_automation_test_status, @v_regression_yes_no, @v_aproveddate, @v_aprovedby, @v_testcasenum, @v_rorv_idnum, @v_ticket_mapping_idnum, @v_automation_test_idnum, @v_standard_test_idnum, @v_qa_test_idnum, @v_qatestcasenum, @q_username, @v_status);", con); cmd.Parameters.Add("@v_row_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.id; cmd.Parameters.Add("@v_client_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.client_idnum; cmd.Parameters.Add("@v_structure_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.structure_idnum; cmd.Parameters.Add("@v_br_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.br_idnum; cmd.Parameters.Add("@v_testtype", NpgsqlTypes.NpgsqlDbType.Text).Value = item.testtype ?? ""; cmd.Parameters.Add("@v_iteration", NpgsqlTypes.NpgsqlDbType.Text).Value = item.iteration ?? ""; cmd.Parameters.Add("@v_prd", NpgsqlTypes.NpgsqlDbType.Text).Value = item.prd ?? ""; cmd.Parameters.Add("@v_requirement_id", NpgsqlTypes.NpgsqlDbType.Text).Value = item.requirement_id ?? ""; cmd.Parameters.Add("@v_use_case", NpgsqlTypes.NpgsqlDbType.Text).Value = item.use_case ?? ""; cmd.Parameters.Add("@v_test_case", NpgsqlTypes.NpgsqlDbType.Text).Value = item.test_case ?? ""; cmd.Parameters.Add("@v_test_case_title", NpgsqlTypes.NpgsqlDbType.Text).Value = item.test_case_title ?? ""; cmd.Parameters.Add("@v_test_case_description", NpgsqlTypes.NpgsqlDbType.Text).Value = item.test_case_description ?? ""; cmd.Parameters.Add("@v_module", NpgsqlTypes.NpgsqlDbType.Text).Value = item.module ?? ""; cmd.Parameters.Add("@v_feature", NpgsqlTypes.NpgsqlDbType.Text).Value = item.feature ?? ""; cmd.Parameters.Add("@v_feature_idnumber", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.feature_idnumber; cmd.Parameters.Add("@v_feature_type", NpgsqlTypes.NpgsqlDbType.Text).Value = item.feature_type ?? ""; cmd.Parameters.Add("@v_test_case_id", NpgsqlTypes.NpgsqlDbType.Text).Value = item.test_case_id ?? ""; cmd.Parameters.Add("@v_test_condition", NpgsqlTypes.NpgsqlDbType.Text).Value = item.test_condition ?? ""; cmd.Parameters.Add("@v_assigned_to", NpgsqlTypes.NpgsqlDbType.Text).Value = item.assigned_to ?? ""; cmd.Parameters.Add("@v_notes", NpgsqlTypes.NpgsqlDbType.Text).Value = item.notes ?? ""; cmd.Parameters.Add("@v_automated_yes_no", NpgsqlTypes.NpgsqlDbType.Text).Value = item.automated_yes_no ?? ""; cmd.Parameters.Add("@v_automation_script_name", NpgsqlTypes.NpgsqlDbType.Text).Value = item.automation_script_name ?? ""; cmd.Parameters.Add("@v_automation_test_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.automation_test_status; cmd.Parameters.Add("@v_regression_yes_no", NpgsqlTypes.NpgsqlDbType.Text).Value = item.regression_yes_no ?? ""; cmd.Parameters.Add("@v_aproveddate", NpgsqlTypes.NpgsqlDbType.Date).Value = item.aproveddate; cmd.Parameters.Add("@v_aprovedby", NpgsqlTypes.NpgsqlDbType.Text).Value = item.aprovedby ?? ""; cmd.Parameters.Add("@v_testcasenum", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.testcasenum; cmd.Parameters.Add("@v_rorv_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.rorv_idnum; cmd.Parameters.Add("@v_ticket_mapping_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.ticket_mapping_idnum; cmd.Parameters.Add("@v_automation_test_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.automation_test_idnum; cmd.Parameters.Add("@v_standard_test_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.standard_test_idnum; cmd.Parameters.Add("@v_qa_test_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.qa_test_idnum; cmd.Parameters.Add("@v_qatestcasenum", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.qatestcasenum; cmd.Parameters.Add("@q_username", NpgsqlTypes.NpgsqlDbType.Text).Value = username; cmd.Parameters.Add("@v_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.status; da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); Guid IDNUM = new Guid(Convert.ToString(ds.Tables[0].Rows[0]["maintain_testcase"])); return(IDNUM); }
/** * Pega o extrato de todas salas para a data solicitada, no formado yyyy-mm-yy, ou yyyy-mm, ou yyyy * * String sala com nome da sala ou null para todas as salas **/ public static DataTable getExtrato(String data, String sala) { String q = queryBase.Replace("@data", data); if (sala != null) { q += " AND sala.nome='" + sala + "'"; } DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(q, Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); }
public DataTable ListaDeSalasCom(String psParametro) { try { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from sala where cao like '%" + psParametro + "%' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); } catch (Exception ex) { //throw new Exception(ex.Message); } return(null); }
public DataTable GetAgendaPorDados(int pIdCliente, int pIdSala, DateTime pdDataHora) { try { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from agenda where idcliente = " + Convert.ToString(pIdCliente) + " and idsala = " + Convert.ToString(pIdSala) + " and datahorareserva = '" + Dados.Model_Dao_Dados.ConverterDataToStr(pdDataHora, false) + "' ", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); } catch (Exception ex) { //throw new Exception(ex.Message); } return(null); }
public DataTable GetProduto(int pid) { try { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from produto where id = " + Convert.ToString(pid), Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); } catch (Exception ex) { //throw new Exception(ex.Message); } return(null); }
public DataTable ListaDeProdutos() { try { DataTable tabela = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select * from produto", Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao()); da.Fill(tabela); return(tabela); } catch (Exception ex) { //throw new Exception(ex.Message); } return(null); }
public static DataSet executeQuery(string statement) { /* * Jordan: * Every piece of this DSN needs to be derived from the configs. It can't stay * hard-coded here. */ string dsn = "Server=127.0.0.1;port=5432;User Id=admin;Password=admin;Database=pokertracker;"; DataSet result = null; using (NpgsqlConnection conn = new NpgsqlConnection(dsn)) { try { conn.Open(); using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(statement, conn)) { da.Fill(result); } } catch (Exception e) { /* * Log this exception. We don't have a logging module, so maybe this is a component we can * add..? Take a look at NLog, whoever wants to take this on. This is a component that cross- * cuts through the entire project, since emission of logs is important for every component. */ } finally { conn.Close(); } } return result; }
protected void OnExecuteActionActivated(object sender, System.EventArgs e) { string connectionString = "Server=localhost;Database=dbprueba2;User Id=dbprueba;Password=sistemas;"; IDbConnection dbConnection = new NpgsqlConnection(connectionString); IDbCommand selectCommand = dbConnection.CreateCommand(); selectCommand.CommandText = "select * from articulos"; IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter(); dbDataAdapter.SelectCommand = selectCommand; NpgsqlCommandBuilder commandBuilder = new NpgsqlCommandBuilder((NpgsqlDataAdapter)dbDataAdapter); dbConnection.Open(); DataSet dataSet = new DataSet(); dbDataAdapter.Fill(dataSet); Console.WriteLine("Table.Count={0}\n", dataSet.Tables.Count); foreach (DataTable dataTable in dataSet.Tables) show (dataTable); DataRow dataRow = dataSet.Tables[0].Rows[0]; dataRow["nombre"] = DateTime.Now.ToString(); Console.WriteLine ("\nTabla con los cambios:"); show (dataSet.Tables[0]); IDbCommand comando = commandBuilder.GetUpdateCommand (dataSet.Tables[0].Rows[0]); //COSAS DE HOY DIA 18 comando.ExecuteNonQuery (); }