/// <summary> /// 在事务中执行查询,返回DataSet /// </summary> public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { NpgsqlCommand cmd = new NpgsqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms); NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds, "ds"); cmd.Parameters.Clear(); return ds; }
/// <summary> /// 执行查询,返回DataSet /// </summary> public DataSet ExecuteQuery(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { using (NpgsqlConnection conn = new NpgsqlConnection(connectionString)) { using (NpgsqlCommand cmd = new NpgsqlCommand()) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd)) { DataSet ds = new DataSet(); da.Fill(ds, "ds"); cmd.Parameters.Clear(); return ds; } } } }
public async Task Get_update_command_infers_parameters_with_NpgsqDbType() { using var conn = await OpenConnectionAsync(); await using var _ = await GetTempTableName(conn, out var table); await conn.ExecuteNonQueryAsync($@" CREATE TABLE {table} ( Cod varchar(5) NOT NULL, Descr varchar(40), Data date, DataOra timestamp, Intero smallInt NOT NULL, Decimale money, Singolo float, Booleano bit, Nota varchar(255), BigIntArr bigint[], VarCharArr character varying(20)[], PRIMARY KEY (Cod) ); INSERT INTO {table} VALUES('key1', 'description', '2018-07-03', '2018-07-03 07:02:00', 123, 123.4, 1234.5, B'1', 'note')"); var daDataAdapter = new NpgsqlDataAdapter( $"SELECT Cod, Descr, Data, DataOra, Intero, Decimale, Singolo, Booleano, Nota, BigIntArr, VarCharArr FROM {table}", conn); var cbCommandBuilder = new NpgsqlCommandBuilder(daDataAdapter); var dtTable = new DataTable(); daDataAdapter.InsertCommand = cbCommandBuilder.GetInsertCommand(); daDataAdapter.UpdateCommand = cbCommandBuilder.GetUpdateCommand(); daDataAdapter.DeleteCommand = cbCommandBuilder.GetDeleteCommand(); Assert.That(daDataAdapter.UpdateCommand.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar)); Assert.That(daDataAdapter.UpdateCommand.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar)); Assert.That(daDataAdapter.UpdateCommand.Parameters[2].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date)); Assert.That(daDataAdapter.UpdateCommand.Parameters[3].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp)); Assert.That(daDataAdapter.UpdateCommand.Parameters[4].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint)); Assert.That(daDataAdapter.UpdateCommand.Parameters[5].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money)); Assert.That(daDataAdapter.UpdateCommand.Parameters[6].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double)); Assert.That(daDataAdapter.UpdateCommand.Parameters[7].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit)); Assert.That(daDataAdapter.UpdateCommand.Parameters[8].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar)); Assert.That(daDataAdapter.UpdateCommand.Parameters[9].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Bigint)); Assert.That(daDataAdapter.UpdateCommand.Parameters[10].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Varchar)); Assert.That(daDataAdapter.UpdateCommand.Parameters[11].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar)); Assert.That(daDataAdapter.UpdateCommand.Parameters[13].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar)); Assert.That(daDataAdapter.UpdateCommand.Parameters[15].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date)); Assert.That(daDataAdapter.UpdateCommand.Parameters[17].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp)); Assert.That(daDataAdapter.UpdateCommand.Parameters[18].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint)); Assert.That(daDataAdapter.UpdateCommand.Parameters[20].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money)); Assert.That(daDataAdapter.UpdateCommand.Parameters[22].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double)); Assert.That(daDataAdapter.UpdateCommand.Parameters[24].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit)); Assert.That(daDataAdapter.UpdateCommand.Parameters[26].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar)); Assert.That(daDataAdapter.UpdateCommand.Parameters[28].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Bigint)); Assert.That(daDataAdapter.UpdateCommand.Parameters[30].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Varchar)); daDataAdapter.Fill(dtTable); var row = dtTable.Rows[0]; Assert.That(row[0], Is.EqualTo("key1")); Assert.That(row[1], Is.EqualTo("description")); Assert.That(row[2], Is.EqualTo(new DateTime(2018, 7, 3))); Assert.That(row[3], Is.EqualTo(new DateTime(2018, 7, 3, 7, 2, 0))); Assert.That(row[4], Is.EqualTo(123)); Assert.That(row[5], Is.EqualTo(123.4)); Assert.That(row[6], Is.EqualTo(1234.5)); Assert.That(row[7], Is.EqualTo(true)); Assert.That(row[8], Is.EqualTo("note")); dtTable.Rows[0]["Singolo"] = 1.1D; Assert.That(daDataAdapter.Update(dtTable), Is.EqualTo(1)); }
public InsEdit(string title, string name, string dbConnection) { InitializeComponent(); Title = title; tableName = name; dbConn = new NpgsqlConnection(dbConnection); tmp = new DataSet(); dbConn.Open(); switch (name) { case "Conference": { label1.Content = "Название:"; label2.Content = "Дата начала:"; label3.Content = "Дата окончания:"; label1.Visibility = Visibility.Visible; label2.Visibility = Visibility.Visible; label3.Visibility = Visibility.Visible; textBox1.Visibility = Visibility.Visible; datePicker1.Visibility = Visibility.Visible; datePicker2.Visibility = Visibility.Visible; break; } case "SectionLeaders": { label1.Content = "ФИО:"; label2.Content = "Кафедра:"; label3.Content = "Должность:"; label1.Visibility = Visibility.Visible; label2.Visibility = Visibility.Visible; label3.Visibility = Visibility.Visible; textBox1.Visibility = Visibility.Visible; textBox2.Visibility = Visibility.Visible; textBox3.Visibility = Visibility.Visible; break; } case "Section": { Height = 420; button1.Margin = new Thickness(70, 350, 0, 0); button2.Margin = new Thickness(209, 350, 0, 0); label1.Content = "Название:"; label2.Content = "Дата начала работы:"; label3.Content = "Дата завершения:"; label4.Content = "Конференция:"; label5.Content = "Руководители секции:"; label1.Visibility = Visibility.Visible; label2.Visibility = Visibility.Visible; label3.Visibility = Visibility.Visible; label4.Visibility = Visibility.Visible; label5.Visibility = Visibility.Visible; textBox1.Visibility = Visibility.Visible; datePicker1.Visibility = Visibility.Visible; datePicker2.Visibility = Visibility.Visible; comboBox1.Margin = new Thickness(176, 232, 0, 0); comboBox2.Margin = new Thickness(176, 295, 0, 0); comboBox1.Visibility = Visibility.Visible; comboBox2.Visibility = Visibility.Visible; tmp.Tables.Add(new DataTable("Conference")); adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Conference", dbConn); adapter.Fill(tmp.Tables["Conference"]); tmp.Tables.Add(new DataTable("SectionLeaders")); adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM SECTION_LEADER", dbConn); adapter.Fill(tmp.Tables["SectionLeaders"]); comboBox1.ItemsSource = tmp.Tables["Conference"].DefaultView; comboBox1.DisplayMemberPath = "name"; comboBox1.SelectedValuePath = "id"; comboBox2.ItemsSource = tmp.Tables["SectionLeaders"].DefaultView; comboBox2.DisplayMemberPath = "name"; comboBox2.SelectedValuePath = "id"; break; } case "Member": { Height = 500; button1.Margin = new Thickness(70, 400, 0, 0); button2.Margin = new Thickness(209, 400, 0, 0); label1.Content = "ФИО:"; label2.Content = "Место работы:"; label3.Content = "Должность:"; label4.Content = "Email:"; label5.Content = "Контактный телефон:"; label6.Content = "Адрес:"; label1.Visibility = Visibility.Visible; label2.Visibility = Visibility.Visible; label3.Visibility = Visibility.Visible; label4.Visibility = Visibility.Visible; label5.Visibility = Visibility.Visible; label6.Visibility = Visibility.Visible; textBox1.Visibility = Visibility.Visible; textBox2.Visibility = Visibility.Visible; textBox3.Visibility = Visibility.Visible; textBox4.Visibility = Visibility.Visible; textBox5.Visibility = Visibility.Visible; textBox6.Visibility = Visibility.Visible; break; } case "Lecture": { Height = 350; button1.Margin = new Thickness(70, 280, 0, 0); button2.Margin = new Thickness(209, 280, 0, 0); label1.Content = "Название доклада:"; label2.Content = "Дата выступления:"; label3.Content = "Секция:"; label4.Content = "Участник:"; label1.Visibility = Visibility.Visible; label2.Visibility = Visibility.Visible; label3.Visibility = Visibility.Visible; label4.Visibility = Visibility.Visible; textBox1.Visibility = Visibility.Visible; datePicker1.Visibility = Visibility.Visible; comboBox1.Visibility = Visibility.Visible; comboBox2.Visibility = Visibility.Visible; comboBox1.Margin = new Thickness(176, 169, 0, 0); comboBox2.Margin = new Thickness(176, 232, 0, 0); tmp.Tables.Add(new DataTable("Section")); adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Section", dbConn); adapter.Fill(tmp.Tables["Section"]); tmp.Tables.Add(new DataTable("Member")); adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Member", dbConn); adapter.Fill(tmp.Tables["Member"]); comboBox1.ItemsSource = tmp.Tables["Section"].DefaultView; comboBox1.DisplayMemberPath = "name"; comboBox1.SelectedValuePath = "id"; comboBox2.ItemsSource = tmp.Tables["Member"].DefaultView; comboBox2.DisplayMemberPath = "name"; comboBox2.SelectedValuePath = "id"; break; } } dbConn.Close(); }
private string GetDatasFromDb(string WoTitle, string query) { string Data = WoTitle + Environment.NewLine; try { DataSet ds = new DataSet(); DataTable dt = new DataTable(); using (var conn = new NpgsqlConnection(connstring)) { conn.Open(); var dataAdapter = new NpgsqlDataAdapter(query, conn); ds.Reset(); dataAdapter.Fill(ds); dt = ds.Tables[0]; } string[] columnNames = dt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray(); int i = 0; foreach (var item in columnNames) { if (!item.Contains("pic") && !item.Contains("file")) { Data += "\t- " + item + ": " + dt.Rows[0][i].ToString() + Environment.NewLine; } else { if (dt.Rows[0][i].ToString().Contains("Byte") && item.Contains("file")) { //MessageBox.Show(dt.Rows[0][i].ToString()+" "+item); Docs.Add((byte[])dt.Rows[0][i]); } if (item.Contains("filename")) { //MessageBox.Show("DOC "+WoTitle+" "+item+" "+ dt.Rows[0][i] +" "+i.ToString()); // its a file Documentums.Text += Environment.NewLine + "- " + Convert.ToString(dt.Rows[0][i]); } else if (item.Contains("pic")) { // its an image BitmapImage imgFromDb = LoadImage((byte[])dt.Rows[0][i]); if (imgFromDb == null) { break; //break if there is no pic in byte array } System.Windows.Controls.Image img = new System.Windows.Controls.Image { Margin = new Thickness(18, 10, 18, 0), Width = 200, Height = 150, ToolTip = WoTitle.Substring(3, WoTitle.Length - 4), Source = imgFromDb }; imgContainer.Children.Add(img); if (imgContainer.Children.Count == 1) { Images.Children.Add(imgContainer); } if (imgContainer.Children.Count == 4) { imgContainer = new StackPanel { Orientation = Orientation.Horizontal } } ; //MessageBox.Show("kep"+WoTitle+" "+item+" "+ dt.Rows[0][i] +" "+i); } } i++; } Data += Environment.NewLine; } catch (Exception) { return(""); } return(Data); }
private void buttonPesquisar_Click(object sender, EventArgs e) { if (maskedTextBoxPesquisa.Text == "") { MessageBox.Show("Preencha o campo de pesquisa", "Atenção!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } else if (radioButtonNome.Checked) { try { string pesquisar = maskedTextBoxPesquisa.Text; string lucas = pesquisar + "%"; //Populando dataGridView com informações do banco de dados NpgsqlConnection conn = new NpgsqlConnection("Server=localhost; Port=5432; User Id=postgres; Password=@postgre; Database=ouvidoria;"); conn.Open(); NpgsqlCommand comando = new NpgsqlCommand("SELECT * FROM manifestante WHERE nome_manifestante ILIKE @nome_manifestante ORDER BY nome_manifestante", conn); comando.Parameters.AddWithValue("@nome_manifestante", lucas); NpgsqlDataAdapter da = new NpgsqlDataAdapter(comando); DataSet ds = new DataSet(); DataTable manifestante = new DataTable(); da.Fill(manifestante); dataGridViewManifestante.DataSource = manifestante; conn.Close(); } catch { MessageBox.Show("Nome não localizado!", "Erro!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } else if (radioButtonCodigo.Checked) { try { string pesquisar = maskedTextBoxPesquisa.Text; int codPesquisar = Convert.ToInt32(pesquisar); //Populando dataGridView com informações do banco de dados NpgsqlConnection conn = new NpgsqlConnection("Server=localhost; Port=5432; User Id=postgres; Password=@postgre; Database=ouvidoria;"); conn.Open(); NpgsqlCommand comando = new NpgsqlCommand("SELECT * FROM manifestante WHERE cod_manifestante = @cod_manifestante", conn); comando.Parameters.AddWithValue("@cod_manifestante", codPesquisar); NpgsqlDataAdapter da = new NpgsqlDataAdapter(comando); DataSet ds = new DataSet(); DataTable manifestante = new DataTable(); da.Fill(manifestante); dataGridViewManifestante.DataSource = manifestante; conn.Close(); } catch { MessageBox.Show("Código não localizado!", "Erro!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } else if (radioButtonDocumento.Checked) { try { string pesquisar = maskedTextBoxPesquisa.Text; //Populando dataGridView com informações do banco de dados NpgsqlConnection conn = new NpgsqlConnection("Server=localhost; Port=5432; User Id=postgres; Password=@postgre; Database=ouvidoria;"); conn.Open(); NpgsqlCommand comando = new NpgsqlCommand("SELECT * FROM manifestante WHERE cpf_manifestante = @cpf_manifestante", conn); comando.Parameters.AddWithValue("@cpf_manifestante", pesquisar); NpgsqlDataAdapter da = new NpgsqlDataAdapter(comando); DataSet ds = new DataSet(); DataTable manifestante = new DataTable(); da.Fill(manifestante); dataGridViewManifestante.DataSource = manifestante; conn.Close(); } catch { MessageBox.Show("CPF não localizado", "Erro!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } else if (radioButtonData.Checked) { try { string pesquisar = maskedTextBoxPesquisa.Text; DateTime dataConvertida = Convert.ToDateTime(pesquisar); //Populando dataGridView com informações do banco de dados NpgsqlConnection conn = new NpgsqlConnection("Server=localhost; Port=5432; User Id=postgres; Password=@postgre; Database=ouvidoria;"); conn.Open(); NpgsqlCommand comando = new NpgsqlCommand("SELECT * FROM manifestante WHERE data_nasc_manifestante = @data_nasc_manifestante", conn); comando.Parameters.AddWithValue("@data_nasc_manifestante", dataConvertida); NpgsqlDataAdapter da = new NpgsqlDataAdapter(comando); DataSet ds = new DataSet(); DataTable manifestante = new DataTable(); da.Fill(manifestante); dataGridViewManifestante.DataSource = manifestante; conn.Close(); } catch { MessageBox.Show("Data de nascimento não localizada", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } }
public static DataTable getConsultaAlugueis(NpgsqlConnection conexao, int campo, int tipo, String descricao) { DataTable dt = new DataTable(); try { String sql = "SELECT * FROM alugueis"; String nomeCampoOrdenacao = "i_pagamentos"; switch (campo) { case 0: sql += " where cast (i_pagamentos as varchar(20)) "; nomeCampoOrdenacao = "i_pagamentos"; break; case 1: sql += " where cast (i_imoveis as varchar(20)) "; nomeCampoOrdenacao = "i_imoveis"; break; case 2: sql += " where cast (i_pessoas as varchar(20)) "; nomeCampoOrdenacao = "i_pessoas"; break; default: sql += " where cast (i_alugueis as varchar(20)) "; nomeCampoOrdenacao = "i_alugueis"; break; } switch (tipo) { case 0: sql += " like '%" + descricao + "%'"; break; case 1: sql += " like '" + descricao + "%'"; break; case 2: sql += " like '%" + descricao + "'"; break; case 3: sql += " = '" + descricao + "'"; break; case 4: sql += " >= '" + descricao + "'"; break; default: sql += " <= '" + descricao + "'"; break; } sql += " order by " + nomeCampoOrdenacao; NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conexao; cmd.CommandText = sql; NpgsqlDataAdapter dat = new NpgsqlDataAdapter(cmd); dat.Fill(dt); } catch (NpgsqlException erro) { MessageBox.Show("Erro de SQL:" + erro.Message); Console.WriteLine("Erro de SQL:" + erro.Message); } return(dt); }
private void ListBtn_Click(object sender, RoutedEventArgs e) { if (checkProductBtn.Visibility.ToString() == "Visible") { if ((sender as Button).Background.ToString() == "#FFADD8E6" || (sender as Button).Name.ToString() == "SaveBtn" || (sender as Button).Name.ToString().Contains("Tab")) { workStationCbx.SelectedIndex = workflowCbx.SelectedIndex; } else { workSteps.Remove("productQuantity"); workSteps.Add("productQuantity", "get_product_quantity(current_date,current_date)"); workStationCbx.SelectedIndex = workStationCbx.Items.Count - 1; } } Int32 Tabcount = 0; using (new WaitCursor()) { try { using (var conn = new NpgsqlConnection(connstring)) { conn.Open(); string sql = BuildSqlQueryCommand(); //MessageBox.Show(sql); var dataAdapter = new NpgsqlDataAdapter(sql, conn); dataSet.Reset(); dataAdapter.Fill(dataSet); dataTable = dataSet.Tables[0]; resultDataGrid.ItemsSource = dataTable.AsDataView(); //Hide byte [] array text where data is null for (int i = 0; i < dataTable.Columns.Count; i++) { if (dataTable.Columns[i].ColumnName.Contains("pic")) { for (int j = 0; j < dataTable.Rows.Count; j++) { byte[] blob = (byte[])dataTable.Rows[j][i]; MemoryStream stream = new MemoryStream(); if (blob.Length > 10) { // change the "byte [] array" text to something more readable. } else { dataTable.Rows[j][i] = null; } } } } // get the query result count var countcmd = new NpgsqlCommand("SELECT COUNT(*)" + SqlCommandForCounter.Substring(8, SqlCommandForCounter.Length - 8), conn); Tabcount = Convert.ToInt32(countcmd.ExecuteScalar()); resultRowCount.Content = Tabcount; } } catch (Exception msg) { MessageBox.Show(msg.Message); } if (workStationCbx.SelectedValue.ToString() == "get_product_quantity(current_date,current_date)") { int sum = 0; for (int i = 0; i < dataTable.Rows.Count; i++) { sum += int.Parse(dataTable.Rows[i][2].ToString()); } resultRowCount.Content = sum.ToString(); workFlow.Clear(); workFlow.Add("00 Transistor Dátumok", "TRANZISZTOR DÁTUM"); workFlow.Add("11 MB HS Szerelés", "MAINBOARD HEATSZINK SZERELÉS"); workFlow.Add("12 MB DSP Szerelés", "MAINBOARD DSP SZERELÉS"); workFlow.Add("21 FB ACDC Szerelés", "FILTERBOARD AC DC SZERELÉS"); workFlow.Add("22 FB EMC Szerelés", "FILTERBOARD EMC SHIELD SZERELÉS"); workFlow.Add("31 Ház Leak Teszt I.", "HÁZ LEAK TESZT"); workFlow.Add("32 Hűtőkör Leak Teszt", "COOLING LEAK TESZT"); workFlow.Add("33 Ház FB Szerelés", "HÁZ FILTERBOARD SZERELÉS"); workFlow.Add("34 Potting után Kapton", "POTTING UTÁN KAPTONOZÁS"); workFlow.Add("35 Ház Konnektor Szerelés", "HÁZ KONNEKTOR SZERELÉS"); workFlow.Add("41 Végszerelés I.", "VÉGSZERELÉS I."); workFlow.Add("42 HiPot Teszt I.", "HIPOT I. GEN"); workFlow.Add("43 Kalibráció", "KALIBRÁCIÓ GEN"); workFlow.Add("44 Végszerelés II.", "VÉGSZERELÉS II."); workFlow.Add("45 Leak Teszt II.", "LEAK TESZT VÉGSZERELÉS II. UTÁN"); workFlow.Add("46 Hipot Teszt II.", "HIPOT II."); workFlow.Add("47 EOL", "EOL"); workFlow.Add("48 Firewall", "FIREWALL"); workflowCbx.ItemsSource = workFlow; workflowCbx.DisplayMemberPath = "Value"; workflowCbx.SelectedValuePath = "Key"; workflowCbx.SelectedIndex = 0; } Tabs.Children.Clear(); for (int i = 0; i <= Tabcount / 200; i++) { Button newBtn = new Button { Background = Brushes.White, Foreground = Brushes.DarkSlateGray, BorderThickness = new Thickness(0), Focusable = false }; newBtn.Click += getOffset; newBtn.Content = (i + 1).ToString(); newBtn.Name = "Tab" + (i + 1).ToString(); newBtn.Width = 34; newBtn.Margin = new Thickness(1, 1, 1, 0); newBtn.FontSize = 20; Tabs.Children.Add(newBtn); //change back and foreground color on active button if (int.Parse(DbQueryOffset) == i * 200) { newBtn.Background = (SolidColorBrush) new BrushConverter().ConvertFrom("#FF008F95"); newBtn.Foreground = Brushes.White; } } DbQueryOffset = "0"; } }
protected override DataTable BuildSchemaTable(Boolean withGeometryColumn) { DataTable dt = null; using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); CollectionExpression <PropertyNameExpression> attributes = null; if (DefaultProviderProperties != null) { attributes = GetProviderPropertyValue <AttributesCollectionExpression, CollectionExpression <PropertyNameExpression> >( DefaultProviderProperties.ProviderProperties, null); } string columns = attributes == null ? "*" : string.Join(",", Enumerable.ToArray(Processor.Select(attributes, delegate( PropertyNameExpression o) { return (QualifyColumnName ( o. PropertyName)); }))); if (columns != "*") { if (!columns.Contains(QualifyColumnName(GeometryColumn))) { columns = string.Format("{0},{1}", QualifyColumnName(GeometryColumn), columns); } if (!columns.Contains(QualifyColumnName(OidColumn))) { columns = string.Format("{0},{1}", QualifyColumnName(OidColumn), columns); } } using ( NpgsqlCommand cmd = new NpgsqlCommand(string.Format("SELECT {0} FROM {1} LIMIT 1;", columns, QualifiedTableName), conn)) { NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.FillSchema(ds, SchemaType.Source); dt = ds.Tables["Table"]; } conn.Close(); } if (!dt.Columns.Contains("oid") && HasOids) { dt.Columns.Add(new DataColumn("oid", typeof(Int64))); DataColumn dc = dt.Columns["oid"]; dc.SetOrdinal(0); if (dt.Constraints.Count == 0) { dt.Constraints.Add("PK", dt.Columns[0], true); } } for (int i = 0; i < dt.Columns.Count; i++) { if (dt.Columns[i].ColumnName == GeometryColumn) { dt.Columns[i].DataType = typeof(Byte[]); } } if (!withGeometryColumn) { dt.Columns.Remove(GeometryColumn); } //remove Primary Key to avoid possibliy mismatched PrimaryKey of FeatureDataTable dt.PrimaryKey = null; return(dt); }
public List <string> TableScripts() { List <string> res = new List <string>(); NpgsqlConnection serverConnection = new NpgsqlConnection(ConnectString); serverConnection.Open(); List <string> tables = new List <string>(); DataTable dt = serverConnection.GetSchema("Tables"); foreach (DataRow row in dt.Rows) { string tablename = (string)row[2]; tables.Add(tablename); // SystemLog.Write(tablename); string SelectString = @"SELECT TOP 1 * FROM " + tablename; NpgsqlDataAdapter serverAdapter = new NpgsqlDataAdapter(); serverAdapter.SelectCommand = new NpgsqlCommand(SelectString, serverConnection); DataSet datasetServer = new DataSet(); serverAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; serverAdapter.Fill(datasetServer, tablename); DataColumn[] primaryKeys = datasetServer.Tables[tablename].PrimaryKey; string createTable = "CREATE TABLE " + tablename + "("; DataColumnCollection columns = datasetServer.Tables[tablename].Columns; int counter = 0; foreach (DataColumn c in columns) { string type = ""; if (c.DataType == typeof(int)) { type = "int"; } else if (c.DataType == typeof(double)) { type = "float"; } else if (c.DataType == typeof(string)) { type = "ntext"; } else if (c.DataType == typeof(DateTime)) { type = "datetime"; } else if (c.DataType == typeof(Boolean)) { type = "bit"; } else { type = "UNKWON"; } createTable += (counter == 0 ? "" : ",") + c.ToString() + " " + type; counter++; } if (primaryKeys != null && primaryKeys.Length > 0) { createTable += ", PRIMARY KEY("; counter = 0; foreach (DataColumn c in primaryKeys) { createTable += (counter == 0 ? "" : ", ") + c.ToString(); counter++; } createTable += ")"; } createTable += ")"; res.Add(createTable); } serverConnection.Close(); return(res); }
public InsEdit(string title, string name, DataRow currentRow, string dbConnectionString) { InitializeComponent(); Title = title; currentDataRow = currentRow; tableName = name; dbConn = new NpgsqlConnection(dbConnectionString); tmp = new DataSet(); dbConn.Open(); switch (name) { case "Conference": { label1.Content = "Название:"; label2.Content = "Дата начала:"; label3.Content = "Дата окончания:"; label1.Visibility = Visibility.Visible; label2.Visibility = Visibility.Visible; label3.Visibility = Visibility.Visible; textBox1.Visibility = Visibility.Visible; datePicker1.Visibility = Visibility.Visible; datePicker2.Visibility = Visibility.Visible; button1.Content = "Изменить"; textBox1.Text = currentRow["NAME"].ToString(); datePicker1.SelectedDate = DateTime.Parse(currentRow["OPENING_DATE"].ToString()); datePicker2.SelectedDate = DateTime.Parse(currentRow["CLOSING_DATE"].ToString()); break; } case "SectionLeaders": { label1.Content = "ФИО:"; label2.Content = "Кафедра:"; label3.Content = "Должность:"; label1.Visibility = Visibility.Visible; label2.Visibility = Visibility.Visible; label3.Visibility = Visibility.Visible; textBox1.Visibility = Visibility.Visible; textBox2.Visibility = Visibility.Visible; textBox3.Visibility = Visibility.Visible; button1.Content = "Изменить"; textBox1.Text = currentRow["NAME"].ToString(); textBox2.Text = currentRow["DEPARTMENT"].ToString(); textBox3.Text = currentRow["JOB_POSITION"].ToString(); break; } case "Section": { Height = 420; button1.Margin = new Thickness(70, 350, 0, 0); button2.Margin = new Thickness(209, 350, 0, 0); label1.Content = "Название:"; label2.Content = "Дата начала работы:"; label3.Content = "Дата завершения:"; label4.Content = "Конференция:"; label5.Content = "Руководители секции:"; label1.Visibility = Visibility.Visible; label2.Visibility = Visibility.Visible; label3.Visibility = Visibility.Visible; label4.Visibility = Visibility.Visible; label5.Visibility = Visibility.Visible; textBox1.Visibility = Visibility.Visible; datePicker1.Visibility = Visibility.Visible; datePicker2.Visibility = Visibility.Visible; comboBox1.Margin = new Thickness(176, 232, 0, 0); comboBox2.Margin = new Thickness(176, 295, 0, 0); comboBox1.Visibility = Visibility.Visible; comboBox2.Visibility = Visibility.Visible; tmp.Tables.Add(new DataTable("Conference")); adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Conference", dbConn); adapter.Fill(tmp.Tables["Conference"]); tmp.Tables.Add(new DataTable("SectionLeaders")); adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM SECTION_LEADER", dbConn); adapter.Fill(tmp.Tables["SectionLeaders"]); comboBox1.ItemsSource = tmp.Tables["Conference"].DefaultView; comboBox1.DisplayMemberPath = "name"; comboBox1.SelectedValuePath = "id"; comboBox1.SelectedValue = currentRow["ID_CONFERENCE"]; comboBox2.ItemsSource = tmp.Tables["SectionLeaders"].DefaultView; comboBox2.DisplayMemberPath = "name"; comboBox2.SelectedValuePath = "id"; comboBox2.SelectedValue = currentRow["ID_EMPLOYEE"]; button1.Content = "Изменить"; textBox1.Text = currentRow["NAME"].ToString(); datePicker1.SelectedDate = DateTime.Parse(currentRow["OPENING_DATE"].ToString()); datePicker2.SelectedDate = DateTime.Parse(currentRow["CLOSING_DATE"].ToString()); break; } case "Member": { Height = 500; button1.Margin = new Thickness(70, 400, 0, 0); button2.Margin = new Thickness(209, 400, 0, 0); label1.Content = "ФИО:"; label2.Content = "Место работы:"; label3.Content = "Должность:"; label4.Content = "Email:"; label5.Content = "Контактный телефон:"; label6.Content = "Адрес:"; label1.Visibility = Visibility.Visible; label2.Visibility = Visibility.Visible; label3.Visibility = Visibility.Visible; label4.Visibility = Visibility.Visible; label5.Visibility = Visibility.Visible; label6.Visibility = Visibility.Visible; textBox1.Visibility = Visibility.Visible; textBox2.Visibility = Visibility.Visible; textBox3.Visibility = Visibility.Visible; textBox4.Visibility = Visibility.Visible; textBox5.Visibility = Visibility.Visible; textBox6.Visibility = Visibility.Visible; button1.Content = "Изменить"; textBox1.Text = currentRow["NAME"].ToString(); textBox2.Text = currentRow["JOB_PLACE"].ToString(); textBox3.Text = currentRow["JOB_POSITION"].ToString(); textBox4.Text = currentRow["EMAIL"].ToString(); textBox5.Text = currentRow["PHONE_NUMBER"].ToString(); textBox6.Text = currentRow["ADDRESS"].ToString(); break; } case "Lecture": { Height = 350; button1.Margin = new Thickness(70, 280, 0, 0); button2.Margin = new Thickness(209, 280, 0, 0); label1.Content = "Название доклада:"; label2.Content = "Дата выступления:"; label3.Content = "Секция:"; label4.Content = "Участник:"; label1.Visibility = Visibility.Visible; label2.Visibility = Visibility.Visible; label3.Visibility = Visibility.Visible; label4.Visibility = Visibility.Visible; textBox1.Visibility = Visibility.Visible; datePicker1.Visibility = Visibility.Visible; comboBox1.Visibility = Visibility.Visible; comboBox2.Visibility = Visibility.Visible; comboBox1.Margin = new Thickness(176, 169, 0, 0); comboBox2.Margin = new Thickness(176, 232, 0, 0); tmp.Tables.Add(new DataTable("Section")); adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Section", dbConn); adapter.Fill(tmp.Tables["Section"]); tmp.Tables.Add(new DataTable("Member")); adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Member", dbConn); adapter.Fill(tmp.Tables["Member"]); comboBox1.ItemsSource = tmp.Tables["Section"].DefaultView; comboBox1.DisplayMemberPath = "name"; comboBox1.SelectedValuePath = "id"; comboBox1.SelectedValue = currentRow["ID_SECTION"]; comboBox2.ItemsSource = tmp.Tables["Member"].DefaultView; comboBox2.DisplayMemberPath = "name"; comboBox2.SelectedValuePath = "id"; comboBox2.SelectedValue = currentRow["ID_MEMBER"]; button1.Content = "Изменить"; textBox1.Text = currentRow["NAME"].ToString(); datePicker1.SelectedDate = DateTime.Parse(currentRow["PERFORMANCE_DATE"].ToString()); break; } } dbConn.Close(); }
/// <summary> /// Execute a query. /// </summary> /// <param name="query">Database query defined outside of the database client.</param> /// <returns>A DataTable containing the results.</returns> public DataTable Query(string query) { if (String.IsNullOrEmpty(query)) { throw new ArgumentNullException(query); } DataTable result = new DataTable(); if (DebugRawQuery) { Console.WriteLine("RawQuery: " + query); } switch (_DbType) { case DbTypes.MsSql: #region Mssql using (SqlConnection conn = new SqlConnection(ConnectionString)) { conn.Open(); SqlDataAdapter sda = new SqlDataAdapter(query, conn); sda.Fill(result); conn.Dispose(); conn.Close(); } break; #endregion case DbTypes.MySql: #region Mysql using (MySqlConnection conn = new MySqlConnection(ConnectionString)) { conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = query; MySqlDataAdapter sda = new MySqlDataAdapter(cmd); DataSet ds = new DataSet(); sda.Fill(ds); if (ds != null) { if (ds.Tables != null) { if (ds.Tables.Count > 0) { result = ds.Tables[0]; } } } conn.Close(); } break; #endregion case DbTypes.PgSql: #region Pgsql using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString)) { conn.Open(); NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, conn); DataSet ds = new DataSet(); da.Fill(ds); if (ds != null && ds.Tables != null && ds.Tables.Count > 0) { result = ds.Tables[0]; } conn.Close(); } break; #endregion } if (DebugResultRowCount) { if (result != null) { Console.WriteLine("RawQuery: returning " + result.Rows.Count + " row(s)"); } else { Console.WriteLine("RawQery: returning null"); } } return(result); }
public List <DomainModel.RecommenderAction> GetWeeklyActivities() { List <DomainModel.RecommenderAction> actions = new List <DomainModel.RecommenderAction>(); NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandText = "select userid, productid from viewedproducts where time>@t union select userid, productid from viewedproducts where " + "time>=@tp and time<=@tk"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new NpgsqlParameter("@t", DateTime.Now.AddDays(-21))); if (DateTime.Now.Month >= 3 && DateTime.Now.Month <= 5) { cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 1, 3, 1))); cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 5, 31))); } else if (DateTime.Now.Month >= 6 && DateTime.Now.Month <= 8) { cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 1, 6, 1))); cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 8, 31))); } else if (DateTime.Now.Month >= 9 && DateTime.Now.Month <= 11) { cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 1, 9, 1))); cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 11, 30))); } else { cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 2, 12, 1))); cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 2, 28))); } da = new NpgsqlDataAdapter(cmd); dt = new DataTable(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "View"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid, grade from reviewedproducts where time>@t union select userid, productid, grade from reviewedproducts where " + "time>=@tp and time<=@tk"; dt.Clear(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "Review"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); action.Grade = int.Parse(dr["grade"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid from boughtproducts where time>@t union select userid, productid from boughtproducts where " + "time>=@tp and time<=@tk"; dt.Clear(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "Buy"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid from seenreviews where time>@t union select userid, productid from seenreviews where " + "time>=@tp and time<=@tk"; dt.Clear(); da.Fill(dt); MongodbFunctions mongo = new MongodbFunctions(); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "SeeReviews"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); action.Grade = mongo.AverageGrade(action.ProductId)[0]; actions.Add(action); } cmd.Dispose(); return(actions); }
public List <DomainModel.RecommenderAction> GetMonthlyActivities(int months) { List <DomainModel.RecommenderAction> actions = new List <DomainModel.RecommenderAction>(); NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandText = "select userid, productid from viewedproducts where time>@t"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new NpgsqlParameter("@t", DateTime.Now.AddMonths(-1 * months))); da = new NpgsqlDataAdapter(cmd); dt = new DataTable(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "View"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid, grade from reviewedproducts where time>@t"; dt.Clear(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "Review"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); action.Grade = int.Parse(dr["grade"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid from boughtproducts where time>@t"; dt.Clear(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "Buy"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid from seenreviews where time>@t"; dt.Clear(); da.Fill(dt); MongodbFunctions mongo = new MongodbFunctions(); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "SeeReviews"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); action.Grade = mongo.AverageGrade(action.ProductId)[0]; actions.Add(action); } cmd.Dispose(); return(actions); }
public string Get(string tkn, string argsr) { NpgsqlDataAdapter da = new NpgsqlDataAdapter(); DataTable dt = new DataTable(); DataTable cdt = new DataTable("cshttl"); DataTable cddt = new DataTable("cshdet");; DataSet argsds; DataSet csrpds = new DataSet(); string rst = ""; if (argsr == null) { return("error"); } try { argsds = JsonConvert.DeserializeObject <DataSet>(argsr); } catch (System.Exception ex) { _logger.LogError("error : Ctrl-CshRpt,mthd-Get. Error parse args: {errmsg}", ex.Message); return("error: " + ex.Message); } NpgsqlConnectionStringBuilder csb = new NpgsqlConnectionStringBuilder(); var cnf = _configuration["DBConn:DBHost"]; csb.Host = cnf.ToString(); csb.Port = 6432; csb.Username = "******"; csb.Database = "micro"; csb.CommandTimeout = 1200; csb.MaxPoolSize = 500; csb.Timeout = 180; cnf = _configuration["DBConn:DBPass"]; csb.Password = cnf.ToString(); string fst = csb.ConnectionString; conn = new NpgsqlConnection(fst); conn.Open(); NpgsqlCommand nsc = new NpgsqlCommand("SELECT * FROM users WHERE name=split_part('" + tkn + "',':',1) AND pass=split_part('" + tkn + "',':',2)", conn); rst = ""; da.SelectCommand = nsc; da.Fill(dt); if (dt.Rows.Count > 0) { if (((bool)dt.Rows[0]["super"]) || ((bool)dt.Rows[0]["boss"]) || (Convert.ToInt32(dt.Rows[0]["id"]) == 921)) { nsc = new NpgsqlCommand("SELECT * FROM vw_dt_cash('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "') ORDER BY cdate desc,posid", conn); da.SelectCommand = nsc; da.Fill(cdt); nsc = new NpgsqlCommand("select * from get_cshr_det('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "', (SELECT array_agg(id) FROM poses)) order by pid", conn); da.SelectCommand = nsc; da.Fill(cddt); } else { if (((bool)dt.Rows[0]["posadm"])) { nsc = new NpgsqlCommand("set session datestyle to 'Euro,DMY'; SELECT * FROM vw_dt_cash('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "') WHERE posid IN (" + dt.Rows[0]["psls"].ToString() + ") ORDER BY cdate desc,posid", conn); da.SelectCommand = nsc; da.Fill(cdt); nsc = new NpgsqlCommand("set session datestyle to 'Euro,DMY'; select * from get_cshr_det('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "',array[" + dt.Rows[0]["psls"].ToString() + "]) order by pid", conn); da.SelectCommand = nsc; da.Fill(cddt); } else { nsc = new NpgsqlCommand("SELECT * FROM vw_dt_cash('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "') WHERE posid =" + dt.Rows[0]["posid"].ToString() + " ORDER BY cdate desc,posid", conn); da.SelectCommand = nsc; da.Fill(cdt); nsc = new NpgsqlCommand("select * from get_cshr_det('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "',array[" + dt.Rows[0]["posid"].ToString() + "]) order by pid", conn); da.SelectCommand = nsc; da.Fill(cddt); } } CultureInfo current = CultureInfo.CurrentCulture; CultureInfo frc; frc = new CultureInfo("ru-RU"); csrpds.Tables.AddRange(new DataTable[] { cdt, cddt }); JsonSerializerSettings microsoftDateFormatSettings = new JsonSerializerSettings { DateFormatHandling = DateFormatHandling.IsoDateFormat }; rst = JsonConvert.SerializeObject(csrpds, new IsoDateTimeConverter { DateTimeFormat = "dd.MM.yyyy" }); _logger.LogInformation("Method CshRpt-Get {tkn}. Success", tkn.Split(':')[0]); } else { _logger.LogInformation("Method CshRpt-Get {tkn}. No such sucker", tkn.Split(':')[0]); rst = "error"; } conn.Close(); return(rst); }
static public Library.Cliente FindById(long idCliente) { NpgsqlConnection conexao = null; NpgsqlDataAdapter dap = null; DataSet ds = null; Library.Cliente cliente = null; try { conexao = new NpgsqlConnection(global::Connection.Connection.StringPostgree()); dap = new NpgsqlDataAdapter("SELECT * FROM \"Cliente\" WHERE id='" + idCliente + "'", conexao); ds = new DataSet(); dap.Fill(ds, "Cliente"); if (ds.Tables["Cliente"].Rows.Count == 1) { DateTime date = DateTime.MinValue; DateTime.TryParse(ds.Tables["Cliente"].Rows[0]["nascimento"].ToString(), out date); DateTime dataCadastro = DateTime.MinValue; DateTime.TryParse(ds.Tables["Cliente"].Rows[0]["dataCadastro"].ToString(), out dataCadastro); cliente = new Cliente(); cliente.Id = (long)ds.Tables["Cliente"].Rows[0]["id"]; cliente.Bairro = ds.Tables["Cliente"].Rows[0]["bairro"].ToString(); cliente.Celular = ds.Tables["Cliente"].Rows[0]["celular"].ToString(); cliente.Cep = ds.Tables["Cliente"].Rows[0]["cep"].ToString(); cliente.Cpf = ds.Tables["Cliente"].Rows[0]["cpf"].ToString(); cliente.Cnpj = ds.Tables["Cliente"].Rows[0]["cnpj"].ToString(); cliente.Cidade = ds.Tables["Cliente"].Rows[0]["cidade"].ToString(); cliente.Email = ds.Tables["Cliente"].Rows[0]["email"].ToString(); cliente.Endereco = ds.Tables["Cliente"].Rows[0]["endereco"].ToString(); cliente.EnderecoNumero = ds.Tables["Cliente"].Rows[0]["enderecoNumero"].ToString(); cliente.Estado = ds.Tables["Cliente"].Rows[0]["estado"].ToString(); cliente.Fax = ds.Tables["Cliente"].Rows[0]["fax"].ToString(); cliente.InscricaoEstadual = ds.Tables["Cliente"].Rows[0]["inscricaoEstadual"].ToString(); cliente.Nascimento = date; cliente.Nome = ds.Tables["Cliente"].Rows[0]["nome"].ToString(); cliente.NomeContato = ds.Tables["Cliente"].Rows[0]["nomeContato"].ToString(); cliente.NomeFantasia = ds.Tables["Cliente"].Rows[0]["nomeFantasia"].ToString(); cliente.Observacoes = ds.Tables["Cliente"].Rows[0]["observacoes"].ToString(); cliente.Pessoa = ds.Tables["Cliente"].Rows[0]["pessoa"].ToString(); cliente.Rg = ds.Tables["Cliente"].Rows[0]["Rg"].ToString(); cliente.ReferenciaComercial = ds.Tables["Cliente"].Rows[0]["referenciaComercial"].ToString(); cliente.Site = ds.Tables["Cliente"].Rows[0]["site"].ToString(); cliente.Telefone = ds.Tables["Cliente"].Rows[0]["telefone"].ToString(); cliente.DataCadastro = dataCadastro; return(cliente); } else { return(null); } } catch (Exception ex) { Library.Classes.Logger.Error(ex);; } finally { conexao.Close(); dap.Dispose(); ds.Dispose(); } return(null); }
public List <Seller> readAll(NpgsqlConnection conn) { List <Seller> result = new List <Seller>(); string sql = "SELECT dim_sellers.seller_id,sms,ap_id,agent_code,agent_name,email,dim_sellers.cellphone,weekly_goal, " + "id_empresa ,sum(fact_sales.sold_week) AS sold_week ,sum(fact_sales.sold_month) AS sold_month, sum(fact_sales.sold_today) AS sold_day "+ "FROM dim_sellers LEFT JOIN fact_sales ON dim_sellers.seller_id = fact_sales.seller_id WHERE sms = true " + "GROUP BY dim_sellers.seller_id,sms,ap_id,agent_code,agent_name,email,dim_sellers.cellphone,weekly_goal,id_empresa"; DataTable dt = new DataTable(); NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn); da.Fill(dt); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { Seller seller = new Seller(); seller.ID = long.Parse(dt.Rows[i][0].ToString()); seller.SMS = bool.Parse(dt.Rows[i][1].ToString()); seller.AP_ID = long.Parse(dt.Rows[i][2].ToString()); seller.Code = dt.Rows[i][3].ToString(); seller.Name = dt.Rows[i][4].ToString(); seller.Email = dt.Rows[i][5].ToString(); seller.CellPhone = dt.Rows[i][6].ToString(); seller.WeeklyGoal = float.Parse(dt.Rows[i][7].ToString()); seller.Enterprise_ID = long.Parse(dt.Rows[i][8].ToString()); if (dt.Rows[i][9].ToString() == "") { seller.CumplimientoSemana = 0; } else { seller.CumplimientoSemana = float.Parse(dt.Rows[i][9].ToString()); } if (dt.Rows[i][10].ToString() == "") { seller.CumplimientoMensual = 0; } else { seller.CumplimientoMensual = float.Parse(dt.Rows[i][10].ToString()); } if (dt.Rows[i][11].ToString() == "") { seller.CumplimientoDiario = 0; } else { seller.CumplimientoDiario = float.Parse(dt.Rows[i][11].ToString()); } result.Add(seller); } } da.Dispose(); listSellers = result; return(result); }
override internal void LoadForTable() { NpgsqlConnection cn = null; try { string query = "select * from information_schema.columns where table_catalog = '" + this.Table.Database.Name + "' and table_schema = '" + this.Table.Schema + "' and table_name = '" + this.Table.Name + "' order by ordinal_position"; cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name); DataTable metaData = new DataTable(); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn); adapter.Fill(metaData); metaData.Columns["udt_name"].ColumnName = "TYPE_NAME"; metaData.Columns["data_type"].ColumnName = "TYPE_NAMECOMPLETE"; if (metaData.Columns.Contains("TYPE_NAME")) { f_TypeName = metaData.Columns["TYPE_NAME"]; } if (metaData.Columns.Contains("TYPE_NAMECOMPLETE")) { f_TypeNameComplete = metaData.Columns["TYPE_NAMECOMPLETE"]; } PopulateArray(metaData); // IsAutoKey logic // query = "SELECT a.attname AS column_name, c2.relname AS seq_name " + // "FROM pg_class c1, pg_class c2, pg_namespace n, pg_depend d, pg_attribute a " + // "WHERE n.nspname = '" + this.Table.Schema + "' AND c1.relname = '" + this.Table.Name + "' " + // "AND c2.relkind = 'S' AND c1.relnamespace = n.oid " + // "AND d.refobjid = c1.oid AND c2.oid = d.objid " + // "AND a.attrelid = c1.oid AND d.refobjsubid = a.attnum"; query = @"SELECT a.attname AS column_name, substring(pg_get_expr(ad.adbin, c.oid) " + @"FROM '[\'""]+(.+?)[\'""]+') AS seq_name " + "FROM pg_class c, pg_namespace n, pg_attribute a, pg_attrdef ad " + "WHERE n.nspname = '" + this.Table.Schema + "' AND c.relname = '" + this.Table.Name + "' " + "AND c.relnamespace = n.oid " + "AND a.attrelid = c.oid AND a.atthasdef = true " + "AND ad.adrelid = c.oid AND ad.adnum = a.attnum " + @"AND pg_get_expr(ad.adbin, c.oid) LIKE 'nextval(%'"; DataTable seqData = new DataTable(); adapter = new NpgsqlDataAdapter(query, cn); adapter.Fill(seqData); DataRowCollection rows = seqData.Rows; if (rows.Count > 0) { string colName; for (int i = 0; i < rows.Count; i++) { colName = rows[i]["column_name"] as string; PostgreSQL8Column col = this[colName] as PostgreSQL8Column; col._isAutoKey = true; query = "SELECT min_value, increment_by FROM \"" + rows[i]["seq_name"] + "\""; adapter = new NpgsqlDataAdapter(query, cn); DataTable autokeyData = new DataTable(); adapter.Fill(autokeyData); Int64 a; a = (Int64)autokeyData.Rows[0]["min_value"]; col._autoInc = Convert.ToInt32(a); a = (Int64)autokeyData.Rows[0]["increment_by"]; col._autoSeed = Convert.ToInt32(a); } } cn.Close(); } catch { if (cn != null) { if (cn.State == ConnectionState.Open) { cn.Close(); } } } }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { DataSet dataSet = new DataSet(); string sqlString = "Server = localhost; Port = 5432; User Id = postgres; Password = drazen2814mia; Database = postgres;"; NpgsqlConnection remoteConnection = new NpgsqlConnection(sqlString); if (remoteConnection.State.ToString() == "Closed") { remoteConnection.Open(); } NpgsqlDataAdapter da = new NpgsqlDataAdapter("CREATE DATABASE db" + DateTime.Now.Year.ToString() + " WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'Croatian_Croatia.1250' LC_CTYPE = 'Croatian_Croatia.1250' CONNECTION LIMIT = -1", remoteConnection); dataSet.Reset(); da.Fill(dataSet); remoteConnection.Close(); string sql = ""; string br = ""; string data_type = ""; DataTable DT = classSQL.select_settings("select table_name from information_schema.tables where TABLE_TYPE <> 'VIEW'", "Table").Tables[0]; //dataGridView1.DataSource = DT; for (int i = 0; i < DT.Rows.Count; i++) { DataTable DTkey = classSQL.select_settings("SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='" + DT.Rows[i]["TABLE_NAME"].ToString() + "'", "INFORMATION_SCHEMA.KEY_COLUMN_USAGE").Tables[0]; DataTable DTT = classSQL.select_settings("SELECT AUTOINC_INCREMENT,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION FROM information_schema.columns WHERE TABLE_NAME ='" + DT.Rows[i]["TABLE_NAME"].ToString() + "'", "Table").Tables[0]; sql += "CREATE TABLE " + DT.Rows[i]["TABLE_NAME"].ToString() + "(\n"; for (int y = 0; y < DTT.Rows.Count; y++) { if (DTT.Rows[y]["CHARACTER_MAXIMUM_LENGTH"].ToString() != "" && DTT.Rows[y]["DATA_TYPE"].ToString() != "ntext") { br = "(" + DTT.Rows[y]["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")"; } else { br = ""; } if (DTT.Rows[y]["DATA_TYPE"].ToString() == "int") { data_type = "integer"; } else if (DTT.Rows[y]["DATA_TYPE"].ToString() == "nvarchar") { data_type = "character varying"; } else if (DTT.Rows[y]["DATA_TYPE"].ToString() == "datetime") { data_type = "timestamp without time zone"; } else if (DTT.Rows[y]["DATA_TYPE"].ToString() == "ntext") { data_type = "text"; } else { data_type = DTT.Rows[y]["DATA_TYPE"].ToString(); } if (DTT.Rows[y]["AUTOINC_INCREMENT"].ToString() == "1") { if (DTkey.Rows.Count > 0) { AutoNumber = DTkey.Rows[0]["COLUMN_NAME"].ToString(); data_type = "serial"; } } sql += DTT.Rows[y]["COLUMN_NAME"].ToString() + " " + data_type + br + ",\n"; } if (DTkey.Rows.Count > 0) { if (DTkey.Rows[0]["COLUMN_NAME"].ToString() != "") { sql += "CONSTRAINT " + DT.Rows[i]["TABLE_NAME"].ToString() + "_primary_key" + " PRIMARY KEY (" + DTkey.Rows[0]["COLUMN_NAME"].ToString() + "),\n"; } } sql = sql.Remove(sql.Length - 2, 2); sql += ")\n\n"; sqlString = "Server = localhost; Port = 5432; User Id = postgres; Password = drazen2814mia; Database = db" + DateTime.Now.Year.ToString() + ";"; //sqlString = SQL.claasConnectDatabase.GetRemoteConnectionString(); remoteConnection = new NpgsqlConnection(sqlString); if (remoteConnection.State.ToString() == "Closed") { remoteConnection.Open(); } da = new NpgsqlDataAdapter(sql, remoteConnection); //richTextBox1.Text = sql.ToString(); dataSet.Reset(); da.Fill(dataSet); remoteConnection.Close(); sql = ""; DataTable DTcompact = classSQL.select_settings("SELECT * FROM " + DT.Rows[i]["TABLE_NAME"].ToString(), DT.Rows[i]["TABLE_NAME"].ToString()).Tables[0]; //CREATE DATABASE newdb WITH TEMPLATE originaldb; for (int r = 0; r < DTcompact.Rows.Count; r++) { string columnName = ""; string values = ""; sql = "INSERT INTO " + DT.Rows[i]["TABLE_NAME"].ToString() + " "; for (int ii = 0; ii < DTcompact.Columns.Count; ii++) { if (AutoNumber != DTcompact.Columns[ii].ToString()) { columnName += DTcompact.Columns[ii].ToString() + ","; values += "'" + DTcompact.Rows[r][DTcompact.Columns[ii]].ToString().Replace("'", "") + "'" + ","; values = values.Replace("\"", ""); values = values.Replace("[", ""); values = values.Replace("]", ""); values = values.Replace("(", ""); values = values.Replace(")", ""); values = values.Replace("\\", ""); } else { AutoNumber = ""; } } columnName = columnName.Remove(columnName.Length - 1, 1); values = values.Remove(values.Length - 1, 1); sql += " ( " + columnName + " ) " + " VALUES " + " ( " + values + " ) "; remoteConnection = new NpgsqlConnection(sqlString); if (remoteConnection.State.ToString() == "Closed") { remoteConnection.Open(); } da = new NpgsqlDataAdapter(sql, remoteConnection); dataSet.Reset(); da.Fill(dataSet); remoteConnection.Close(); sql = ""; } } MessageBox.Show("Spremljeno"); }
public void Start(string requestId, bool viewSQL) { try { if (Settings.Count == 0) { throw new Exception(CommonMessages.Exception_MissingSettings); } status.Code = RequestStatus.StatusCode.InProgress; string server = Settings.GetAsString("Server", ""); string port = Settings.GetAsString("Port", ""); string userId = Settings.GetAsString("UserID", ""); string password = Settings.GetAsString("Password", ""); string database = Settings.GetAsString("Database", ""); string dataSourceName = Settings.GetAsString("DataSourceName", ""); string connectionTimeout = Settings.GetAsString("ConnectionTimeout", "15"); string commandTimeout = Settings.GetAsString("CommandTimeout", "120"); log.Debug("Connection timeout: " + connectionTimeout + ", Command timeout: " + commandTimeout); if (!Settings.ContainsKey("DataProvider")) { throw new Exception(CommonMessages.Exception_MissingDataProviderType); } string connectionString = string.Empty; switch ((Lpp.Dns.DataMart.Model.Settings.SQLProvider)Enum.Parse(typeof(Lpp.Dns.DataMart.Model.Settings.SQLProvider), Settings.GetAsString("DataProvider", ""), true)) { case Lpp.Dns.DataMart.Model.Settings.SQLProvider.ODBC: if (string.IsNullOrWhiteSpace(dataSourceName)) { throw new Exception(CommonMessages.Exception_MissingODBCDatasourceName); } using (OdbcConnection cn = new OdbcConnection(string.Format("DSN={0}", dataSourceName))) try { OdbcDataAdapter da = new OdbcDataAdapter(query, cn); da.Fill(resultDataset); } finally { cn.Close(); } break; case Lpp.Dns.DataMart.Model.Settings.SQLProvider.PostgreSQL: if (string.IsNullOrEmpty(server)) { throw new Exception(CommonMessages.Exception_MissingDatabaseServer); } if (string.IsNullOrEmpty(database)) { throw new Exception(CommonMessages.Exception_MissingDatabaseName); } if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password)) { throw new Exception(CommonMessages.Exception_MissingDatabasePassword); } if (port == null || port == string.Empty) { port = "5432"; } connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};Timeout={5};CommandTimeout={6}", server, port, userId, password, database, connectionTimeout, commandTimeout); // Making connection with Npgsql provider using (NpgsqlConnection connnection = new NpgsqlConnection(connectionString)) { try { connnection.Open(); NpgsqlCommand command = new NpgsqlCommand(query, connnection); NpgsqlDataAdapter da = new NpgsqlDataAdapter(command); resultDataset.Reset(); da.Fill(resultDataset); } catch (Exception ex) { throw ex; } finally { connnection.Close(); } } break; case Lpp.Dns.DataMart.Model.Settings.SQLProvider.SQLServer: if (string.IsNullOrEmpty(server)) { throw new Exception(CommonMessages.Exception_MissingDatabaseServer); } if (string.IsNullOrEmpty(database)) { throw new Exception(CommonMessages.Exception_MissingDatabaseName); } if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password)) { throw new Exception(CommonMessages.Exception_MissingDatabasePassword); } if (port != null && port != string.Empty) { server += ", " + port; } connectionString = userId != null && userId != string.Empty ? String.Format("server={0};User ID={1};Password={2};Database={3}; Connection Timeout={4}", server, userId, password, database, connectionTimeout) : String.Format("server={0};integrated security=True;Database={1}; Connection Timeout={2}", server, database, connectionTimeout); using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString)) { try { connection.Open(); System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(query, connection); command.CommandTimeout = int.Parse(commandTimeout); System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command); resultDataset.Reset(); da.Fill(resultDataset); } catch (Exception ex) { throw ex; } finally { connection.Close(); } } break; //case Lpp.Dns.DataMart.Model.Settings.SQLProvider.Oracle: // // TODO: Implement this provider // //throw new NotImplementedException("Oracle client not implemented yet"); // //connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};;Timeout={5};CommandTimeout={6}", server, port, userId, password, database, connectionTimeout, commandTimeout); // //// TODO: Upgrade Oracle client // //using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionString)) // //{ // // try // // { // // connection.Open(); // // System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(query, connection); // // System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(command); // // resultDataset.Reset(); // // da.Fill(resultDataset); // // } // // catch (Exception ex) // // { // // throw ex; // // } // // finally // // { // // connection.Close(); // // } // //} // break; default: throw new Exception(CommonMessages.Exception_InvalidDataProviderType); } status.Code = RequestStatus.StatusCode.Complete; status.Message = ""; } catch (Exception e) { status.Code = RequestStatus.StatusCode.Error; status.Message = e.Message; throw e; } }
private PostgresDatabase() { _adapter = new NpgsqlDataAdapter(); _cmd_builder = new NpgsqlCommandBuilder(); }
protected void refreshImageList() { //used to re-load images based on either clicking left or right arrow //-9 for back arrow, +9 for forward arrow; if position < 9, make left arrow unclickable; if dt.Rows.Count //if (given number from string (NOT TOTAL ROW NUMBER)) % 9 != 0), make right arrow unclickable String LocalPhotoId, LocalTitle, LocalFilename, LocalAuthor, LocalUser, LocalPrice, LocalDate, LocalCollege, LocalDescription; //String stmt = "SELECT photoid, title, filename FROM photo limit 9 offset " + offSetNum.ToString(); String stmt = "SELECT photoid, title, filename, author, \"user\".username, price, \"dateSubmitted\", college, description FROM photo left join \"user\" ON \"user\".userid = photo.userid ORDER BY photoid DESC limit 9 offset " + Session["offSetNum"].ToString(); int counter = 0; String[] photoIdArray = new string[9]; String[] titleArray = new string[9]; fileNArray = new String[9]; String[] authorArray = new string[9]; String[] userArray = new string[9]; String[] priceArray = new string[9]; String[] dateArray = new string[9]; String[] collegeArray = new string[9]; String[] descArray = new string[9]; using (NpgsqlConnection conn2 = new NpgsqlConnection(connString)) { using (NpgsqlCommand comm2 = new NpgsqlCommand()) { comm2.Connection = conn2; comm2.CommandType = CommandType.Text; comm2.CommandText = stmt; try { conn2.Open(); NpgsqlDataAdapter nsda = new NpgsqlDataAdapter(comm2); nsda.Fill(dt); if (dt.Rows.Count != 0) { foreach (DataRow row in dt.Rows) { if (counter > 8) { counter = 0; Array.Clear(photoIdArray, 0, photoIdArray.Length); Array.Clear(titleArray, 0, titleArray.Length); Array.Clear(fileNArray, 0, fileNArray.Length); Array.Clear(authorArray, 0, authorArray.Length); Array.Clear(userArray, 0, userArray.Length); Array.Clear(priceArray, 0, priceArray.Length); Array.Clear(dateArray, 0, dateArray.Length); Array.Clear(collegeArray, 0, collegeArray.Length); Array.Clear(descArray, 0, descArray.Length); } LocalPhotoId = ""; LocalTitle = ""; LocalFilename = ""; LocalAuthor = ""; LocalUser = ""; LocalPrice = ""; LocalDate = ""; LocalCollege = ""; LocalDescription = ""; foreach (DataColumn col in dt.Columns) { switch (col.Caption) { case "photoid": LocalPhotoId = row[col].ToString(); break; case "title": LocalTitle = row[col].ToString(); break; case "filename": LocalFilename = row[col].ToString(); break; case "author": LocalAuthor = "Author: " + row[col].ToString(); break; case "username": LocalUser = "******" + row[col].ToString(); break; case "price": LocalPrice = row[col].ToString(); if (LocalPrice.Contains(".")) { LocalPrice = "Price: $" + string.Format("{0:G29}", decimal.Parse(LocalPrice)); } else { if (!String.IsNullOrEmpty(LocalPrice)) { LocalPrice = "Price: $" + LocalPrice; } else { LocalPrice = "Price: $0"; } } break; case "dateSubmitted": LocalDate = "Date Posted: " + row[col].ToString(); break; case "college": LocalCollege = "University: " + row[col].ToString(); break; case "description": LocalDescription = "Description: " + row[col].ToString(); break; default: break; } } photoIdArray[counter] = LocalPhotoId; titleArray[counter] = LocalTitle; fileNArray[counter] = LocalFilename; authorArray[counter] = LocalAuthor; userArray[counter] = LocalUser; priceArray[counter] = LocalPrice; dateArray[counter] = LocalDate; collegeArray[counter] = LocalCollege; descArray[counter] = LocalDescription; counter++; } imageDisplayTitle1.Text = titleArray[0]; imageDisplayTitle2.Text = titleArray[1]; imageDisplayTitle3.Text = titleArray[2]; imageDisplayTitle4.Text = titleArray[3]; imageDisplayTitle5.Text = titleArray[4]; imageDisplayTitle6.Text = titleArray[5]; imageDisplayTitle7.Text = titleArray[6]; imageDisplayTitle8.Text = titleArray[7]; imageDisplayTitle9.Text = titleArray[8]; imageDisplay1Price.Text = priceArray[0]; imageDisplay2Price.Text = priceArray[1]; imageDisplay3Price.Text = priceArray[2]; imageDisplay4Price.Text = priceArray[3]; imageDisplay5Price.Text = priceArray[4]; imageDisplay6Price.Text = priceArray[5]; imageDisplay7Price.Text = priceArray[6]; imageDisplay8Price.Text = priceArray[7]; imageDisplay9Price.Text = priceArray[8]; imageDisplay1Description.Text = descArray[0]; imageDisplay2Description.Text = descArray[1]; imageDisplay3Description.Text = descArray[2]; imageDisplay4Description.Text = descArray[3]; imageDisplay5Description.Text = descArray[4]; imageDisplay6Description.Text = descArray[5]; imageDisplay7Description.Text = descArray[6]; imageDisplay8Description.Text = descArray[7]; imageDisplay9Description.Text = descArray[8]; imageDisplay1Date.Text = dateArray[0]; imageDisplay2Date.Text = dateArray[1]; imageDisplay3Date.Text = dateArray[2]; imageDisplay4Date.Text = dateArray[3]; imageDisplay5Date.Text = dateArray[4]; imageDisplay6Date.Text = dateArray[5]; imageDisplay7Date.Text = dateArray[6]; imageDisplay8Date.Text = dateArray[7]; imageDisplay9Date.Text = dateArray[8]; imageDisplay1College.Text = collegeArray[0]; imageDisplay2College.Text = collegeArray[1]; imageDisplay3College.Text = collegeArray[2]; imageDisplay4College.Text = collegeArray[3]; imageDisplay5College.Text = collegeArray[4]; imageDisplay6College.Text = collegeArray[5]; imageDisplay7College.Text = collegeArray[6]; imageDisplay8College.Text = collegeArray[7]; imageDisplay9College.Text = collegeArray[8]; imageDisplay1User.Text = userArray[0]; imageDisplay2User.Text = userArray[1]; imageDisplay3User.Text = userArray[2]; imageDisplay4User.Text = userArray[3]; imageDisplay5User.Text = userArray[4]; imageDisplay6User.Text = userArray[5]; imageDisplay7User.Text = userArray[6]; imageDisplay8User.Text = userArray[7]; imageDisplay9User.Text = userArray[8]; imageDisplay1Author.Text = authorArray[0]; imageDisplay2Author.Text = authorArray[1]; imageDisplay3Author.Text = authorArray[2]; imageDisplay4Author.Text = authorArray[3]; imageDisplay5Author.Text = authorArray[4]; imageDisplay6Author.Text = authorArray[5]; imageDisplay7Author.Text = authorArray[6]; imageDisplay8Author.Text = authorArray[7]; imageDisplay9Author.Text = authorArray[8]; for (int k = 0; k < 9; k++) { displayImage(k); } /* * String image1 = filePath + fileNArray[0].ToString(); * String image2 = filePath + fileNArray[1].ToString(); * String image3 = filePath + fileNArray[2].ToString(); * String image4 = filePath + fileNArray[3].ToString(); * String image5 = filePath + fileNArray[4].ToString(); * String image6 = filePath + fileNArray[5].ToString(); * String image7 = filePath + fileNArray[6].ToString(); * String image8 = filePath + fileNArray[7].ToString(); * String image9 = filePath + fileNArray[8].ToString(); * * ImageDisplay1.ImageUrl = image1; * ImageDisplay2.ImageUrl = image2; * ImageDisplay3.ImageUrl = image3; * ImageDisplay4.ImageUrl = image4; * ImageDisplay5.ImageUrl = image5; * ImageDisplay6.ImageUrl = image6; * ImageDisplay7.ImageUrl = image7; * ImageDisplay8.ImageUrl = image8; * ImageDisplay9.ImageUrl = image9; */ } dt.Clear(); nsda.Dispose(); } catch (NpgsqlException ex) { } } conn2.Close(); } CloseConn(); }
/// <summary> /// This method will load all records for this investor that exist on our Projects_Entrepreneur_Investor_XREF table, because records on that table are what model this relationship. /// </summary> private void load_PROCS() { // make query to get a dataset from our Data Base string query = "SELECT PIX.proc_investor_xref_id, PM.proc_master_id, PIX.create_date_time, PIX.investor_accepted, PM.proc_begin_date, PM.proc_end_date, PM.profit_percentage, PM.project_accepted FROM proc_investor_xref PIX JOIN proc_master PM ON PIX.proc_master_id = PM.proc_master_id WHERE PIX.investor_master_id = @InvestorID"; NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, conn); da.SelectCommand.Parameters.AddWithValue("InvestorID", InvestorID); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds); } catch (Exception e) { } finally { conn.Close(); } // if our query resturned no rows we should probably inform the user instead of making an empty table try { if (ds.Tables[0].Rows.Count < 0) { PROCResults = "<h3>No PROCS were found for your account... perhaps you should find some projects and start investing</h3>"; } else // start building what we will render onto the page by placing an open table bracket and table head to be shown { PROCResults += "<h3>Projects</h3><br />"; PROCResults += "<table id='tableProjectAccounts' class='table table-hover table-striped table-condensed'>"; PROCResults += "<thead><tr><th>Date Created</th><th>% of net Profit</th><th>Project Accepted</th><th>Investor Accepted</th><th>Begin Date</th><th>End Date</th></tr></thead>"; PROCResults += "<tbody>"; foreach (DataRow i in ds.Tables[0].Rows) { PROCResults += "<tr>"; PROCResults += "<td><a href='" + "ViewPROC/" + i["proc_master_id"].ToString() + "'>" + i["create_date_time"].ToString() + "</a></td>"; PROCResults += "<td>" + i["profit_percentage"].ToString() + "</td>"; try { if ((bool)i["project_accepted"]) { PROCResults += "<td>Accepted</td>"; } else { PROCResults += "<td>Denied</td>"; } } catch (InvalidCastException e) { PROCResults += "<td>Denied</td>"; } try { if ((bool)i["investor_accepted"]) { PROCResults += "<td>Accepted</td>"; } else { PROCResults += "<td>Denied</td>"; } } catch (InvalidCastException e) { PROCResults += "<td>Denied</td>"; } PROCResults += "<td>" + i["proc_begin_date"].ToString() + "</td>"; PROCResults += "<td>" + i["proc_end_date"].ToString() + "</td>"; PROCResults += "</tr>"; } PROCResults += "</tbody>"; PROCResults += "</table>"; } } catch (Exception e) { PROCResults = "<h3><Something went wrong attempting to get PROC tied to your profile!/h3>"; } // loop over results set of our query for each row add a row to our html table // after loop, now add the closing table tags }
private void DoSomthing(object nobject) { try { NameObject nameObject = nobject as NameObject; string quanpin = nameObject.quanpin; string shouZim = nameObject.shouZim; string hanzi = nameObject.hanzi; string tableName = nameObject.tableName; string msg2 = "开始获取所有数据信息"; if (this.OnProcessNotify != null) { this.OnProcessNotify(msg2, 0); } string sqlString = "SELECT gid," + hanzi + "," + quanpin + "," + shouZim + " FROM " + tableName + " where " + hanzi + " is not null"; NpgsqlDataAdapter da = new NpgsqlDataAdapter(sqlString, dbcon); da.UpdateCommand = new NpgsqlCommand(); da.UpdateCommand.Parameters.Add(new NpgsqlParameter("@" + quanpin, DbType.String, 254, quanpin)); da.UpdateCommand.Parameters.Add(new NpgsqlParameter("@" + shouZim, DbType.String, 254, shouZim)); NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(da); DataSet dataset = new DataSet(); da.Fill(dataset); msg2 = "完成获取所有数据信息"; if (this.OnProcessNotify != null) { this.OnProcessNotify(msg2, 0); } DataColumn hanziCol = dataset.Tables[0].Columns[hanzi]; DataColumn quanpinCol = dataset.Tables[0].Columns[quanpin]; DataColumn shouZimCol = dataset.Tables[0].Columns[shouZim]; int j = 0; int count = dataset.Tables[0].Rows.Count; foreach (DataRow row in dataset.Tables[0].Rows) { j++; string sql = ""; try { string hanziValue = row[hanziCol].ToString().Trim(); row[quanpinCol] = Hz2Py.GetPinyin(hanziValue); row[shouZimCol] = Hz2Py.GetFirstPinyin(hanziValue); sql = "update " + tableName + " set " + quanpin + "='" + row[quanpinCol].ToString() + "'," + shouZim + "='" + row[shouZimCol] + "' where gid=" + row[0].ToString(); NpgsqlCommand objCommand = new NpgsqlCommand(sql, dbcon); objCommand.ExecuteNonQuery(); } catch { LogManager.writeLog("异常:<" + sql + ">语法错误"); } string msg = "已处理路网数据" + j.ToString() + "条,共" + count.ToString() + "条"; if (this.OnProcessNotify != null) { this.OnProcessNotify(msg, (j * 100) / count); } } string msg1 = "处理完成"; if (this.OnProcessNotify != null) { this.OnProcessNotify(msg1, 100); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void get_SearchResults(string arg) { // Define string array to hold both of our queries string[] queries = new string[2]; /// <SQL> Easy to read sql string /// SELECT EM.entrepreneur_master_id, EM.entrepreneur_profile_name, IMGM.image_file /// FROM entrepreneur_master EM /// LEFT OUTER JOIN entrepreneur_image_xref EMXREF /// ON EMXREF.entrepreneur_master_id = EM.entrepreneur_master_id /// LEFT OUTER JOIN image_master IMGM /// ON IMGM.image_master_id = EMXREF.image_master_id /// WHERE EM.entrepreneur_profile_name LIKE @arg /// AND EM.entrepreneur_public = TRUE /// </SQL> queries[0] = "SELECT EM.entrepreneur_master_id, EM.entrepreneur_profile_name, IMGM.image_file FROM entrepreneur_master EM LEFT OUTER JOIN entrepreneur_image_xref EMXREF ON EMXREF.entrepreneur_master_id = EM.entrepreneur_master_id LEFT OUTER JOIN image_master IMGM ON IMGM.image_master_id = EMXREF.image_master_id WHERE EM.entrepreneur_profile_name LIKE @arg AND EM.entrepreneur_public = TRUE"; /// <SQL> /// SELECT project_master_id, project_description /// FROM project_master /// WHERE project_description LIKE @arg /// </SQL> queries[1] = "SELECT project_master_id, project_description FROM project_master WHERE project_description LIKE @arg"; NpgsqlDataAdapter da1 = new NpgsqlDataAdapter(queries[0], conn); da1.SelectCommand.Parameters.AddWithValue("@arg", "%" + arg + "%"); DataSet ds1 = new DataSet(); try { conn.Open(); da1.Fill(ds1); } catch (Exception e) { } finally { conn.Close(); } NpgsqlDataAdapter da2 = new NpgsqlDataAdapter(queries[1], conn); da2.SelectCommand.Parameters.AddWithValue("@arg", string.Format("%{0}%", arg)); DataSet ds2 = new DataSet(); try { conn.Open(); da2.Fill(ds2); } catch (Exception e) { } finally { conn.Close(); } DataSet ds = new DataSet(); ds.Tables.Add(ds1.Tables[0].Copy()); ds2.Tables[0].TableName = "Table1"; ds.Tables.Add(ds2.Tables[0].Copy()); SearchDS = ds; }
//Save to Excel private void SaveBtn_Click(object sender, RoutedEventArgs e) { String resultat = ""; String result = ""; using (var conn = new NpgsqlConnection(connstring)) { conn.Open(); for (int i = 0; i < Tabs.Children.Count; i++) { //fill datagrid string sql = BuildSqlQueryCommand(); var dataAdapter = new NpgsqlDataAdapter(sql, conn); dataSet.Reset(); dataAdapter.Fill(dataSet); dataTable = dataSet.Tables[0]; resultDataGrid.ItemsSource = dataTable.AsDataView(); DbQueryOffset = ((i + 1) * 200).ToString(); // concatenate the tabs resultDataGrid.SelectAllCells(); if (i == 0) { resultDataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader; } else { resultDataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.ExcludeHeader; } ApplicationCommands.Copy.Execute(null, resultDataGrid); resultat += (string)Clipboard.GetData(DataFormats.CommaSeparatedValue); result += (string)Clipboard.GetData(DataFormats.Text); resultDataGrid.UnselectAllCells(); // set back the default result if (i == Tabs.Children.Count - 1) { foreach (var item in Tabs.Children) { if ((item as Button).Foreground == Brushes.White) { DbQueryOffset = (int.Parse((item as Button).Content.ToString()) * 200 - 200).ToString(); } } ListBtn_Click(sender, e as RoutedEventArgs); } } } SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel |*.xls"; //saveFileDialog.DefaultExt = "xls"; //saveFileDialog.AddExtension = true; if (saveFileDialog.ShowDialog() == true) { StreamWriter file1 = new StreamWriter(saveFileDialog.FileName); file1.WriteLine(result.Replace(',', ' ')); file1.Close(); } }
public static string GetInfo(string target, int port) { string toReturn = ""; // Thanks Metasploit! List <string> userList = new List <string>() { "postgres", "scott", "admin" }; List <string> passList = new List <string>() { "tiger", "postgres", "password", "admin" }; bool toBreak = false; foreach (string username in userList) { if (toBreak) { break; } foreach (string password in passList) { if (toBreak) { break; } using (NpgsqlConnection conn = new NpgsqlConnection($"Host={target};Port={port};User Id={username};Password={password};Database=template1;")) { try { // Try connect conn.Open(); // We're connect - Creds are correct! toReturn += "- Login creds found: " + username + ":" + password + Environment.NewLine; toBreak = true; // Pull version info try { NpgsqlCommand cmd = new NpgsqlCommand("SELECT version()", conn); DataSet ds = new DataSet(); NpgsqlDataAdapter da = new NpgsqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); foreach (DataRow r in ds.Tables[0].Rows) { toReturn += "- Version: " + r["version"].ToString() + Environment.NewLine; } } catch (Exception ex) { toReturn += "- Unable to pull Version info - Bug Reelix: " + ex.Message + Environment.NewLine; } try { NpgsqlCommand cmd = new NpgsqlCommand("SELECT usename, passwd, usesuper FROM pg_shadow", conn); DataSet ds = new DataSet(); NpgsqlDataAdapter da = new NpgsqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); foreach (DataRow r in ds.Tables[0].Rows) { string dbUser = r["usename"].ToString(); string dbPass = r["passwd"].ToString(); bool superUser = bool.Parse(r["usesuper"].ToString()); toReturn += "- pg_shadow User: "******" - " + dbPass + " - Super User: "******"-- If a pg_shadow user password is cracked, you can get a shell with: use exploit/multi/postgres/postgres_copy_from_program_cmd_exec"; } catch (Exception ex) { toReturn += "- Unable to pull pg_shadow info - Bug Reelix: " + ex.Message + Environment.NewLine; } } catch (Exception ex) { if (ex.Message.Trim().StartsWith("28P01")) { // Invalid Password } else if (ex.Message == "Dependency unixODBC with minimum version 2.3.1 is required") { toReturn += "- Dependency missing. Download from: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#ubuntu17"; toBreak = true; } else { toReturn += "- Fatal Error in PostgreSQL.GetInfo - Bug Reelix: " + ex.Message; if (ex.InnerException != null) { Console.WriteLine("Inner exception is not null: " + ex.InnerException.Message); } toBreak = true; } } } } } if (toReturn == "") { toReturn = "- Unable to find credentials"; } return(toReturn.Trim(Environment.NewLine.ToCharArray())); }
private void btnAceptar_Click(object sender, EventArgs e) { //Verifico que existan productos en la lista de venta if (dataGridView2.Rows.Count > 0) { //Capturo los datos para la insercion en tabla venta string fecha = dateTimePicker1.Value.ToShortDateString(); string comprobante = lblComprobante.Text; //Realizo insercion en tabla venta try { conn.Open(); string query = "INSERT INTO venta(comprobante,fecha, id_usuario) values("; query += "'" + comprobante + "'"; query += ",'" + fecha + "'"; query += "," + id_usuario + ")"; NpgsqlCommand cmd = new NpgsqlCommand(query, conn); if (cmd.ExecuteNonQuery() > 0) { //Si ya se registro la venta ahora registro el detalle de la venta //Primero obtengo el id del ultimo registro en la tabla venta NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("SELECT * FROM venta ORDER BY id DESC LIMIT 1;", conn); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); DataRow dataRow = dataTable.Rows[0]; string idVenta = dataRow.ItemArray[0].ToString(); //Realizo la insercion por cada fila en la lista de venta foreach (DataGridViewRow row in dataGridView2.Rows) { //Capturamos los datos string id_producto = row.Cells[0].Value.ToString(); string cantidad_producto = row.Cells[4].Value.ToString(); string precio_producto = row.Cells[3].Value.ToString().Replace(',', '.'); //Realizamos insercion query = "INSERT INTO detalle_venta(id_venta,id_producto,cantidad,precio) VALUES("; query += idVenta + ","; query += id_producto + ","; query += cantidad_producto + ","; query += precio_producto + ")"; NpgsqlCommand command = new NpgsqlCommand(query, conn); if (command.ExecuteNonQuery() <= 0) { MessageBox.Show("Error al guardar detalle de compra"); } } //Cuando toda la transaccion se completo de forma exitosa muestro menaje y limpio componentes MessageBox.Show("Venta registrada"); DataTable dtaux = dataGridView2.DataSource as DataTable; if (dtaux != null) { dtaux.Rows.Clear(); } lblComprobante.Text = obtenerComprobante(); } else { MessageBox.Show("Error al registrar venta"); } conn.Close(); //Actualizamos lista de productos disponibles cargarInventario(); } catch (Exception er) { MessageBox.Show(er.Message); } } else { MessageBox.Show("Aun no ha agregado productos a la lista de venta"); } }
public Ver_empleados(Ejecutor exec) { InitializeComponent(); ejec = exec; sda = new NpgsqlDataAdapter(); }
private void LPSELoad(string type) { NpgsqlConnection pgConnection = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["PGConnectionString"].ConnectionString); string ssql = string.Empty; string hardCodeHandler = string.Empty; if (type == "ANGGOTA") { if (UserName == "197005231997031003") { hardCodeHandler = "and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%19700523199703100%' "; } else if (UserName == "197505272006041015") { hardCodeHandler = "and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%19750527200604101%' "; } else if (UserName == "197904192008011003") { hardCodeHandler = "and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%19790419200801100%' "; } else if (UserName == "198210032009122002") { hardCodeHandler = "and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%198210032009022002%' "; } else { hardCodeHandler = "and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%"+ UserName + "%' "; } ssql = "select distinct paket.pkt_nama Paket, tahap.thp_nama Tahapan, jadwal.dtj_tglawal Awal, jadwal.dtj_tglakhir Akhir " + //"peg_nama Nama, ltrim(rtrim(replace(peg_nip, ' ', ''))) NIP " + "from jadwal " + "inner join tahap on tahap.thp_id = jadwal.thp_id " + "inner join lelang_seleksi on lelang_seleksi.lls_id = jadwal.lls_id and lelang_seleksi.lls_status = 1 " + "inner join paket on paket.pkt_id = lelang_seleksi.pkt_id and paket.pkt_status = 1 " + "inner join paket_panitia on paket.pkt_id = paket_panitia.pkt_id " + "inner join anggota_panitia on anggota_panitia.pnt_id = paket_panitia.pnt_id " + "inner join pegawai on pegawai.peg_id = anggota_panitia.peg_id " + "where 1=1 " + "and date_part('day', jadwal.dtj_tglakhir) = date_part('day', now()) AND date_part('month', jadwal.dtj_tglakhir) = date_part('month', now()) AND date_part('year', jadwal.dtj_tglakhir) = date_part('year', now()) " + "and jadwal.lls_id = lelang_seleksi.lls_id " + hardCodeHandler + "order by jadwal.dtj_tglawal, jadwal.dtj_tglakhir"; //jadwal.lls_id, } else { ssql = "select distinct paket.pkt_nama Paket, tahap.thp_nama Tahapan, jadwal.dtj_tglawal Awal, jadwal.dtj_tglakhir Akhir " + //"peg_nama Nama, ltrim(rtrim(replace(peg_nip, ' ', ''))) NIP " + "from jadwal " + "inner join tahap on tahap.thp_id = jadwal.thp_id " + "inner join lelang_seleksi on lelang_seleksi.lls_id = jadwal.lls_id and lelang_seleksi.lls_status = 1 " + "inner join paket on paket.pkt_id = lelang_seleksi.pkt_id and paket.pkt_status = 1 " + "inner join paket_panitia on paket.pkt_id = paket_panitia.pkt_id " + "inner join anggota_panitia on anggota_panitia.pnt_id = paket_panitia.pnt_id " + "inner join pegawai on pegawai.peg_id = anggota_panitia.peg_id " + "where 1=1 " + "and date_part('day', jadwal.dtj_tglakhir) = date_part('day', now()) AND date_part('month', jadwal.dtj_tglakhir) = date_part('month', now()) AND date_part('year', jadwal.dtj_tglakhir) = date_part('year', now()) " + "and jadwal.lls_id = lelang_seleksi.lls_id " + //"and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%" + UserName + "%' " + "order by jadwal.dtj_tglawal, jadwal.dtj_tglakhir"; //jadwal.lls_id, } //NpgsqlCommand myCommand = new NpgsqlCommand(); //myCommand.CommandText = ssql; //myCommand.CommandType = CommandType.Text; //myCommand.Connection = pgConnection; try { pgConnection.Open(); //NpgsqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); /* * while (myReader.Read()) * { * * } */ dset = new DataSet("npdata"); NpAdapter = new NpgsqlDataAdapter(); NpAdapter.SelectCommand = new NpgsqlCommand(ssql, pgConnection); NpAdapter.Fill(dset, "npdata"); dtsource = dset.Tables["npdata"]; gridJadwal.DataSource = dtsource; gridJadwal.DataBind(); //gridJadwal.SetDataBinding(dset, "npdata"); //myReader.Close(); lpseStatusLabel.Text = "Tersambung"; pgParameterLabel.Text = ConfigurationManager.ConnectionStrings["PGConnectionString"].ConnectionString.Substring(0, 20); } catch (Exception ex) { lpseStatusLabel.Text = "Terputus" + " (Pesan Kesalahan: " + ex.Message + ")"; pgParameterLabel.Text = ConfigurationManager.ConnectionStrings["PGConnectionString"].ConnectionString; } }
private void progressTimer_Tick(object sender, EventArgs e) { if (!isTaskStart) { isTaskStart = true; sw = System.Diagnostics.Stopwatch.StartNew(); Task exportTk = Task.Run(() => { NpgsqlConnection lclConn = null; try { lclConn = table.Owner.Conn.CloneWith(table.Owner.Conn.ConnectionString); using (StreamWriter sw = new StreamWriter(fileName)) { string hdr = ""; for (int i = 1; i < table.AssociatiedForm.MainDataGrid.ColumnCount; i++) { hdr += table.AssociatiedForm.MainDataGrid.Columns[i].Name + ", "; } hdr = hdr.Substring(0, hdr.Length - 2); sw.WriteLine(hdr); for (int pageId = 1; pageId <= table.NumPages; pageId++) { string strSql = String.Format(@"SELECT * FROM {0} ORDER BY {1} {2} LIMIT {3} OFFSET {4}", table.Name, table.OrderBy, table.Order, TableManager.RECORDS_PER_PAGE, (pageId - 1) * TableManager.RECORDS_PER_PAGE); try { NpgsqlDataAdapter lclDa = new NpgsqlDataAdapter(strSql, lclConn); DataSet lclDs = new DataSet(); lclDa.Fill(lclDs); DataTable lclDt = lclDs.Tables[0]; for (int recId = 0; recId < lclDt.Rows.Count; recId++) { string v = ""; for (int i = 1; i < lclDt.Columns.Count; i++) { v += lclDt.Rows[recId][i] + ", "; } v = v.Substring(0, v.Length - 2); sw.WriteLine(v); } Interlocked.Increment(ref pageExported); } catch (Exception exe) { lclConn.Open(); } } } } catch (Exception exc) { progressTimer.Stop(); MessageBox.Show(exc.Message); if (lclConn != null) { lclConn.Close(); } table.IsBeingExported = false; isTaskStart = false; Close(); } }); } lblProgress.Text = String.Format(@"{0} page(s) exported / {1} page(s) in total", pageExported, table.NumPages); if (pageExported == table.NumPages) { progressTimer.Stop(); sw.Stop(); MessageBox.Show(String.Format(@"Table exported successfully! [Total Time Used {0}s]", (double)sw.ElapsedMilliseconds / 1000.0)); table.IsBeingExported = false; isTaskStart = false; Close(); } }
void connection(String a) { DataSet ds = new DataSet(); DataTable dt = new DataTable(); using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(ConfigurationSettings.AppSettings["ConnectionString"])) { connection.Open(); String chart_select = "SELECT title FROM movie WHERE id = " + a; NpgsqlDataAdapter da = new NpgsqlDataAdapter(chart_select, connection); connection.Close(); IMDb imdb = new IMDb("The Godfather", true); String tittl = imdb.Id; Label1.Text = Label1.Text + imdb.Title; } }
protected void Page_Load(object sender, EventArgs e) { if (Page.IsPostBack) { return; } filterdiv.Visible = false; var ds = new DataSet(); var dt = new DataTable(); // för filtrering var medarbetare = new List <Person>(); var avdelningar = new List <string>(); string connstr = ConfigurationManager.ConnectionStrings["postgres connection"].ConnectionString; using (var conn = new NpgsqlConnection(connstr)) { conn.Open(); var sqlquery = "select avtal.id, avtalstyp, enligt_avtal, diarienummer, startdate, enddate, status, motpartstyp, sbk_avtal.ansvarig_avd.namn as ansvarig_avd, avtalstecknare.first_name || ' ' || avtalstecknare.last_name as \"Avtalstecknare\", ansvarig_sbk.first_name || ' ' || ansvarig_sbk.last_name as \"ansvarig_sbk\", avtalskontakt.first_name || ' ' || avtalskontakt.last_name as \"Avtalskontakt\", ansvarigsbk.first_name as \"Ansvarig SBK - Förnamn\", ansvarigsbk.last_name as \"Ansvarig SBK - efternamn\", ansvarigsbk.epost as \"Ansvarig SBK - epost\", data.first_name as \"datakontakt förnamn\", data.last_name as \"datakontakt efternamn\", data.epost as \"datakontakt epost\", kontakt.last_name as \"Avtalskontakt efternamn\", orgnummer, (select string_agg(beskrivning, ', ') from sbk_avtal.avtalsinnehall left join sbk_avtal.map_avtal_innehall on map_avtal_innehall.avtalsinnehall_id = avtalsinnehall.id where avtal.id = map_avtal_innehall.avtal_id) as avtalsinnehall from sbk_avtal.avtal left join sbk_avtal.person data on data.id=sbk_avtal.avtal.datakontakt left join sbk_avtal.person kontakt on kontakt.id=sbk_avtal.avtal.avtalskontakt left join sbk_avtal.person ansvarigsbk on ansvarigsbk.id=sbk_avtal.avtal.ansvarig_sbk left join sbk_avtal.person avtalstecknare on avtalstecknare.id=sbk_avtal.avtal.avtalstecknare left join sbk_avtal.person avtalskontakt on avtalskontakt.id=sbk_avtal.avtal.avtalskontakt left join sbk_avtal.person ansvarig_sbk on ansvarig_sbk.id=sbk_avtal.avtal.ansvarig_sbk left join sbk_avtal.ansvarig_avd on sbk_avtal.ansvarig_avd.id = sbk_avtal.avtal.ansvarig_avd;"; using (var da = new NpgsqlDataAdapter(sqlquery, conn)) { da.Fill(ds); } var avdelningsquery = "select namn from sbk_avtal.ansvarig_avd;"; using (var cmd = new NpgsqlCommand(avdelningsquery, conn)) { using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { avdelningar.Add(reader.GetString(0)); } } } var medarbetarequery = "select person.id, person.first_name, person.last_name from sbk_avtal.medarbetare_sbk left join sbk_avtal.person on sbk_avtal.medarbetare_sbk.person_id = sbk_avtal.person.id;"; using (var cmd = new NpgsqlCommand(medarbetarequery, conn)) { using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { medarbetare.Add(new Person { id = reader.GetInt32(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), }); } } } } dt = ds.Tables[0]; // filtrerar bort inaktiva avtal från AvtalTableDataSource, men behåller dem i "OriginalDataSource Session.Add("OriginalDataSource", dt); dt = dt.AsEnumerable() .Where(row => row.Field <string>("status") == "Aktivt") .CopyToDataTable(); Session.Add("AvtalTableDataSource", dt); AvtalTable.DataSource = dt; AvtalTable.DataBind(); // filtrerings-rullistor avdelningdd.Items.Add("Alla avdelningar"); medarbetaredd.Items.Add("Alla"); foreach (var avd in avdelningar) { avdelningdd.Items.Add(avd); } for (int i = 0; i < medarbetare.Count; i++) { var person = medarbetare[i]; person.dropdownindex = i + 1; medarbetaredd.Items.Add(string.Format("{0} {1}", person.FirstName, person.LastName)); } //Session.Add("AvtalTableDataSource", dt); Session.Add("medarbetare", medarbetare); Session.Add("avdelningar", avdelningar); }