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;
}
}