private void FillComboBox() { try { connection.Open(); string sqlQuery = "select * from tip_partnera"; NuoDbCommand command = new NuoDbCommand(sqlQuery, connection); tipCbox.Items.Clear(); rdr = command.ExecuteReader(); tipCbox.DisplayMember = "Text"; tipCbox.ValueMember = "Value"; idList = new List <int>(); while (rdr.Read()) { tipCbox.Items.Add(new { Text = rdr["naziv"].ToString(), Value = rdr["idtip_partnera"].ToString() }); idList.Add(int.Parse(rdr["idtip_partnera"].ToString())); } rdr.Close(); } catch (NuoDbSqlException ex) { Console.WriteLine("Error: {0}", ex.ToString()); } finally { if (connection != null) { connection.Close(); } } }
private void GetStavke() { try { if (racuniDGV.SelectedRows.Count > 0) { connection.Open(); string sqlQuery = "select * from stavka_racuna join roba on stavka_racuna.roba_idroba = roba.idroba where stavka_racuna.racun_idracun = " + int.Parse(racuniDGV.CurrentRow.Cells[0].Value.ToString()); NuoDbCommand command = new NuoDbCommand(sqlQuery, connection); stavkaDGV.Rows.Clear(); rdr = command.ExecuteReader(); while (rdr.Read()) { decimal cijena = decimal.Parse(rdr["jedinicna_cijena"].ToString()); int kolicina = int.Parse(rdr[2].ToString()); decimal uk_cijena = cijena * kolicina; string[] row = new string[] { rdr["idroba"].ToString(), rdr["naziv"].ToString(), rdr["opis"].ToString(), rdr["jedinicna_cijena"].ToString(), rdr[2].ToString(), uk_cijena.ToString() }; stavkaDGV.Rows.Add(row); } rdr.Close(); } } catch (NuoDbSqlException ex) { Console.WriteLine("Error: {0}", ex.ToString()); } finally { if (connection != null) { connection.Close(); } } }
private void GetRacuni() { try { connection.Open(); string sqlQuery = "select * from racun join zaposlenik on racun.zaposlenik_idzaposlenik = zaposlenik.idzaposlenik join poslovni_partner on racun.poslovni_partner_idposlovni_partner = poslovni_partner.idposlovni_partner;"; NuoDbCommand command = new NuoDbCommand(sqlQuery, connection); racuniDGV.Rows.Clear(); rdr = command.ExecuteReader(); while (rdr.Read()) { string[] row = new string[] { rdr["idracun"].ToString(), rdr["datum_izdavanja"].ToString(), rdr["ukupna_cijena"].ToString(), rdr["kontrola"].ToString(), rdr[7].ToString(), rdr[17].ToString() }; racuniDGV.Rows.Add(row); } rdr.Close(); } catch (NuoDbSqlException ex) { Console.WriteLine("Error: {0}", ex.ToString()); } finally { if (connection != null) { connection.Close(); } } }
private void GetSkladiste() { try { connection.Open(); string sqlQuery = "select * from skladiste"; NuoDbCommand command = new NuoDbCommand(sqlQuery, connection); skladisteDGV.Rows.Clear(); rdr = command.ExecuteReader(); while (rdr.Read()) { string[] row = new string[] { rdr["idskladiste"].ToString(), rdr["naziv"].ToString(), rdr["adresa"].ToString() }; skladisteDGV.Rows.Add(row); } rdr.Close(); } catch (NuoDbSqlException ex) { Console.WriteLine("Error: {0}", ex.ToString()); } finally { if (connection != null) { connection.Close(); } } }
private void FillBoxes() { try { connection.Open(); string sqlQuery = "select * from pozicija where idpozicija = " + idPozicija + ";"; NuoDbCommand command = new NuoDbCommand(sqlQuery, connection); rdr = command.ExecuteReader(); while (rdr.Read()) { nazivTbox.Text = rdr["naziv"].ToString(); opisTbox.Text = rdr["opis"].ToString(); razinaTbox.Text = rdr["razina_prava"].ToString(); } rdr.Close(); } catch (NuoDbSqlException ex) { Console.WriteLine("Error: {0}", ex.ToString()); } finally { if (connection != null) { connection.Close(); } } }
public void TestMultipleReturnResultSets() { using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString)) { connection.Open(); new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery(); try { new NuoDbCommand("create procedure nunit_test() " + " returns table t1(field1 string, field2 integer), t2(column1 string, column2 string, column3 integer) " + " as " + " insert into t1 values ('rset 1, row1', 0), ('rset 1, row2',1); " + " insert into t2 values ('rset 2, row1', 'first', 100), ('rset 2, row2','second', 101); " + " end_procedure", connection).ExecuteNonQuery(); } catch (NuoDbSqlException e) { if (e.Code.Code == -1) { // the server doesn't support multiple result sets as return value for procedures return; } else { throw; } } NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection); cmd.CommandType = CommandType.StoredProcedure; using (DbDataReader reader = cmd.ExecuteReader()) { Assert.IsTrue(reader.Read()); Assert.AreEqual("rset 1, row1", reader["field1"]); Assert.AreEqual(0, reader["field2"]); Assert.IsTrue(reader.Read()); Assert.AreEqual("rset 1, row2", reader["field1"]); Assert.AreEqual(1, reader["field2"]); Assert.IsFalse(reader.Read()); Assert.IsTrue(reader.NextResult()); Assert.IsTrue(reader.Read()); Assert.AreEqual("rset 2, row1", reader["column1"]); Assert.AreEqual("first", reader["column2"]); Assert.AreEqual(100, reader["column3"]); Assert.IsTrue(reader.Read()); Assert.AreEqual("rset 2, row2", reader["column1"]); Assert.AreEqual("second", reader["column2"]); Assert.AreEqual(101, reader["column3"]); Assert.IsFalse(reader.Read()); Assert.IsFalse(reader.NextResult()); } } }
private string GeneralAlterFunction(string alterCommand) { try { connectionString = "Server=localhost;Database=test;User="******";Password="******"Success"); } catch (NuoDbSqlException e) { return(e.Message); } }
private string GeneralDeleteSQLFunction(string objectType, string objectName, string schema) { try { connectionString = "Server=localhost;Database=test;User="******";Password="******";Schema=" + schema; connection = new NuoDbConnection(connectionString); command = new NuoDbCommand("Drop " + objectType + " " + schema + "." + objectName, connection); connection.Open(); reader = command.ExecuteReader(); return("Success"); } catch (NuoDbSqlException e) { return(e.Message); } }
private void prijavaButton_Click(object sender, EventArgs e) { string lozinka = ""; int razina_prava = 999; int idzaposlenik = 999; try { connection.Open(); string sqlQuery = "select * from zaposlenik join pozicija on zaposlenik.pozicija_idpozicija = pozicija.idpozicija where korisnicko_ime = '" + korimeTbox.Text + "'"; NuoDbCommand command = new NuoDbCommand(sqlQuery, connection); rdr = command.ExecuteReader(); while (rdr.Read()) { lozinka = rdr["lozinka"].ToString(); razina_prava = (int)rdr["razina_prava"]; idzaposlenik = (int)rdr["idzaposlenik"]; } rdr.Close(); } catch (NuoDbSqlException ex) { Console.WriteLine("Error: {0}", ex.ToString()); } finally { if (connection != null) { connection.Close(); } } if (lozinka == LozinkaTbox.Text && LozinkaTbox.Text != "") { GlavnaForm forma = new GlavnaForm(razina_prava, idzaposlenik); this.Hide(); forma.ShowDialog(); this.Show(); } else if (lozinka == "") { MessageBox.Show("Nepostojeće korisničko ime"); } else { MessageBox.Show("Kriva lozinka"); } }
public void DB4329() { using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString)) { connection.Open(); Utils.DropTable(connection, "ExpenseTest"); DbCommand createCommand = new NuoDbCommand("Create table ExpenseTest" + "(" + "SourceExpenseId int," + "ExpenseAmount numeric(15,2)" + ")", connection); createCommand.ExecuteNonQuery(); DbCommand insertCommand = new NuoDbCommand("Insert Into ExpenseTest(SourceExpenseId, ExpenseAmount) Values (?,?)", connection); insertCommand.Prepare(); insertCommand.Parameters[0].Value = -1254524; insertCommand.Parameters[1].Value = -135.35; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 100100100; insertCommand.Parameters[1].Value = -1325465.35; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 100100101; insertCommand.Parameters[1].Value = 200000.35; insertCommand.ExecuteNonQuery(); DbCommand selectCommand = new NuoDbCommand("select SourceExpenseId, ExpenseAmount from ExpenseTest", connection); using (DbDataReader reader = selectCommand.ExecuteReader()) { bool hasNext = reader.Read(); Assert.IsTrue(hasNext); Assert.AreEqual(-1254524, reader[0]); Assert.AreEqual(-135.35, reader[1]); hasNext = reader.Read(); Assert.IsTrue(hasNext); Assert.AreEqual(100100100, reader[0]); Assert.AreEqual(-1325465.35, reader[1]); hasNext = reader.Read(); Assert.IsTrue(hasNext); Assert.AreEqual(100100101, reader[0]); Assert.AreEqual(200000.35, reader[1]); } } }
public bool CreateUser(string username, string password) { try { connectionString = "Server=localhost;Database=test;User="******";Password="******";Schema=" + currentSchema; connection = new NuoDbConnection(connectionString); command = new NuoDbCommand("Create user " + username + " PASSWORD '" + password + "'; " + "GRANT system.dba to " + username + ";" + "Grant system.administrator to " + username + "; ", connection); connection.Open(); reader = command.ExecuteReader(); return(true); } catch (Exception e) { return(false); } }
public void TestReaderNoStoredProcedure() { using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString)) { connection.Open(); new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery(); new NuoDbCommand("create procedure nunit_test(in p1 integer) returns table(id integer, value string) as var i = 0; while(i<p1) insert into table values (i, 'xx'); i = i+1; end_while; end_procedure", connection).ExecuteNonQuery(); NuoDbCommand cmd = new NuoDbCommand("call nunit_test(10)", connection); int n = 0; using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Assert.AreEqual(n++, reader[0]); Assert.AreEqual("xx", reader[1]); } Assert.AreEqual(10, n); } } }
private List <String> GeneralSelectSQLFunction(string sqlCommand) { List <String> list = new List <String>(); connectionString = "Server=localhost;Database=test;User="******";Password="******";"; connection = new NuoDbConnection(connectionString); command = new NuoDbCommand(sqlCommand, connection); connection.Open(); reader = command.ExecuteReader(); while (reader.Read()) { list.Add(reader[0].ToString()); } connection.Close(); reader.Close(); return(list); }
private static Project getProjectById(int id) { Project p = new Project(); using (var connection = new NuoDbConnection(connectionString)) { using (var cmd = new NuoDbCommand($"SELECT Id, Name FROM Project WHERE Id = {id}", connection)) { connection.Open(); using (DbDataReader dr = cmd.ExecuteReader()) { if (dr.Read()) { p.Id = Convert.ToInt32(dr["Id"]); p.Name = Convert.ToString(dr["Name"]); } } connection.Close(); } } return(p); }
protected override void Init() { if (Initialized) { return; } HashSet <String> passedRelationNames = new HashSet <string>(); HashSet <String> passedSchemas = new HashSet <string>(); HashSet <String> excludedOwners = new HashSet <string>(); excludedOwners.Add("System".ToUpper()); if (Connection.State != ConnectionState.Open) { Connection.Open(); } NuoDbCommand command; try { DataTable views = null; DataTable tables = null; if ((SupportedElementTypes & DbConnectionElementTypes.Table) != 0) { tables = (Connection as NuoDbConnection).GetSchema("Tables"); DataProviderHelper.LogDataTableStructure(Logger, tables); } if ((SupportedElementTypes & DbConnectionElementTypes.View) != 0) { views = (Connection as NuoDbConnection).GetSchema("Views"); DataProviderHelper.LogDataTableStructure(Logger, views); } Connection.Close(); for (int pass = 0; pass < 2; pass++) { DataTable currentTable; switch (pass) { case 0: currentTable = views; break; case 1: currentTable = tables; break; default: Debug.Assert(false); currentTable = null; break; } if (currentTable == null) { continue; } foreach (DataRow dr in currentTable.Rows) { string schema = dr["TABLE_SCHEMA"].ToString(); string name = dr["TABLE_NAME"].ToString(); if (SuppressAddTableOrRelation(name, schema)) { continue; } if (excludedOwners.Contains(schema)) { continue; } // Get schema from connection string NuoDbConnection connection = (NuoDbConnection)Connection; NuoDbConnectionStringBuilder _parsedConnectionString = new NuoDbConnectionStringBuilder(Connection.ConnectionString); string usedSchema; if (Connection.ConnectionString.Contains("schema")) { usedSchema = _parsedConnectionString.Schema; } else { usedSchema = String.Empty; } // If no schema is specified in the connectionString, get all tables of all schemas, // otherwise just get the tables of the specified schema if ((schema != usedSchema.ToUpper()) && !String.IsNullOrEmpty(usedSchema)) { continue; } // No schema specified, add them to list, to build relations if (!passedSchemas.Contains(schema)) { passedSchemas.Add(schema); } ICloneable cloneable = (ICloneable)Connection; Debug.Assert(cloneable != null); if (cloneable != null) { NuoDbConnection newConnection = (NuoDbConnection)cloneable.Clone(); command = new NuoDbCommand("Select * From " + (String.IsNullOrEmpty(schema) ? name + "" : schema + "." + "\"" + name) + "\"", newConnection); AddCommand(command, name, "\"{0}\"", "?"); } else { throw new LL_BadDatabaseStructure_Exception("The passed connection doesn't implement the ICloneable interface. Contact NuoDB support for an updated version."); } } } //get relations string commandText = String.Format(CultureInfo.InvariantCulture, "Select Distinct b.Tablename as PrimaryTable, c.Field as PrimaryField, " + " b2.Tablename as ForeignTable, c2.Field as ForeignField, a.Numberkeys as NumberKeys, b.schema, b2.schema " + "From System.Foreignkeys a " + "Left outer join System.Tables b " + "on a.PrimaryTableId =b.Tableid " + "Left outer join System.Fields c " + "on a.PrimaryFieldId = c.FieldId " + "Left outer join System.Tables b2 " + "on a.ForeignTableid =b2.TableId " + "Left outer join System.Fields c2 " + "on a.ForeignFieldId =c2.FieldId " + "where b.Tablename =c.Tablename " + "and " + "b2.Tablename = c2.Tablename "); using (command = new NuoDbCommand(commandText, Connection as NuoDbConnection)) { string lastRelationChildColumnName = ""; string lastRelationParentColumnName = ""; int counter = 0; Connection.Open(); DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { if (!reader.IsDBNull(0) && !reader.IsDBNull(1)) { string childColumnName = reader.GetString(3); string parentColumnName = reader.GetString(1); string childTableName = reader.GetString(2); string parentTableName = reader.GetString(0); string parentSchema = reader.GetString(5); string childSchema = reader.GetString(6); if (excludedOwners.Contains(parentSchema) || excludedOwners.Contains(childSchema)) { continue; } if (SuppressAddTableOrRelation(parentTableName, parentSchema) || SuppressAddTableOrRelation(childTableName, childSchema)) { continue; } //check whether shared primary key if (reader.GetInt16(4) > 1) { ++counter; //first time i am empty if (counter == 1) { lastRelationParentColumnName = parentColumnName; lastRelationChildColumnName = childColumnName; } else { lastRelationChildColumnName += '\t' + childColumnName; lastRelationParentColumnName += '\t' + parentColumnName; } if (counter == reader.GetInt16(4)) { parentColumnName = lastRelationParentColumnName; childColumnName = lastRelationChildColumnName; counter = 0; } else { continue; } } string relationName = parentTableName + "2" + childTableName; int relationIndex = 1; string formatString = relationName + "{0}"; while (passedRelationNames.Contains(relationName)) { relationName = String.Format(CultureInfo.InvariantCulture, formatString, relationIndex); relationIndex++; } passedRelationNames.Add(relationName); AddRelation(relationName, parentTableName, childTableName, parentColumnName, childColumnName); } } reader.Close(); } } finally { Connection.Close(); Initialized = true; } }