//SELECT
public static DataSet select(string sql, string table, bool caffe = false)
{
if (caffe)
{
SetCaffeRemoteString();
}
else
{
remoteConnectionString = SQL.claasConnectDatabase.GetRemoteConnectionString();
remoteConnection = new NpgsqlConnection(remoteConnectionString);
}
try
{
if (remoteConnectionString == "")
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
dataSet = new DataSet();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(sql.Replace("zbroj", "+"), connectionString);
adapter.Fill(dataSet, table);
connection.Close();
return(dataSet);
}
else
{
dataSet = new DataSet();
if (remoteConnection.State == ConnectionState.Closed)
{
remoteConnection.Open();
}
string comm = sql;
if (!sql.ToLower().Contains("setval"))
{
comm = sql.Replace("+", "||").Replace("[", "\"").Replace("]", "\"").Replace("zbroj", "+");
}
NpgsqlDataAdapter da = new NpgsqlDataAdapter(comm, remoteConnection);
dataSet.Reset();
da.Fill(dataSet);
remoteConnection.Close();
return(dataSet);
}
}
catch (NpgsqlException ex)
{
OstaleFunkcije.SetInLog(sql + "\r\n" + ex.ToString(), "65", "RemoteDB");
remoteConnection.Close();
return(dataSet);
}
}
//UPDATE
public static string update(string sql)
{
//sql = sql.Replace('\'', ' ');
if (remoteConnectionString == "")
{
try
{
{
if (connection.State.ToString() == "Closed")
{
connection.Open();
}
dataSet = new DataSet();
adapter.SelectCommand = new SqlCeCommand(sql, connection);
SqlCeCommandBuilder builder = new SqlCeCommandBuilder(adapter);
adapter.Fill(dataSet);
connection.Close();
}
return("");
}
catch (Exception ex)
{
connection.Close();
OstaleFunkcije.SetInLog(sql + "\r\n" + ex.ToString(), "65", "RemoteDB");
return(ex.ToString());
}
}
else
{
try
{
if (remoteConnection.State.ToString() == "Closed")
{
remoteConnection.Open();
}
NpgsqlCommand comm = new NpgsqlCommand(sql, remoteConnection);
comm.CommandTimeout = 1200;
comm.ExecuteNonQuery();
remoteConnection.Close();
return("");
}
catch (NpgsqlException ex)
{
remoteConnection.Close();
OstaleFunkcije.SetInLog(sql + "\r\n" + ex.ToString(), "65", "RemoteDB");
return(ex.ToString());
}
}
}
//INSERT
public static string insert(string sql)
{
//sql = sql.Replace('\'', ' ');
if (remoteConnectionString == "")
{
try
{
if (connection.State.ToString() == "Closed")
{
connection.Open();
}
SqlCeTransaction tx = connection.BeginTransaction();
SqlCeCommand cmd = connection.CreateCommand();
cmd.Transaction = tx;
cmd.Connection = connection;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
tx.Commit();
connection.Close();
return("");
}
catch (Exception msg)
{
connection.Close();
OstaleFunkcije.SetInLog(sql + "\r\n" + msg.ToString(), "65", "RemoteDB");
return(msg.ToString());
}
}
else
{
try
{
if (remoteConnection.State.ToString() == "Closed")
{
remoteConnection.Open();
}
NpgsqlCommand comm = new NpgsqlCommand(sql, remoteConnection);
comm.CommandTimeout = 60;
comm.ExecuteNonQuery();
remoteConnection.Close();
return("");
}
catch (NpgsqlException ex)
{
remoteConnection.Close();
OstaleFunkcije.SetInLog(sql + "\r\n" + ex.ToString(), "65", "RemoteDB");
return(ex.ToString());
}
}
}
public static string transaction(string sql)
{
switch (sql.Trim().ToUpper())
{
case "BEGIN":
case "BEGIN;":
if (transtring != "")
{
MessageBox.Show("Prethodni dokument nije pravilno spremljen", "Upozorenje", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
goto case "ROLLBACK;";
case "ROLLBACK":
case "ROLLBACK;":
transtring = "";
return("");
case "COMMIT":
case "COMMIT;":
try
{
if (remoteConnection.State.ToString() == "Closed")
{
remoteConnection.Open();
}
NpgsqlCommand comm = new NpgsqlCommand(transtring, remoteConnection);
comm.ExecuteNonQuery();
transtring = "";
remoteConnection.Close();
return("");
}
catch (NpgsqlException ex)
{
remoteConnection.Close();
OstaleFunkcije.SetInLog(transtring + "\r\n" + ex.ToString(), "65", "RemoteDB");
transtring = "";
return(ex.ToString());
}
default:
transtring += sql + "; ";
return("");
}
}
//SELECT SETTINGS
public static DataSet select_settings(string sql, string table)
{
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
dataSet = new DataSet();
SqlCeDataAdapter adapter = new SqlCeDataAdapter(sql, connectionString);
adapter.Fill(dataSet, table);
connection.Close();
return(dataSet);
}
catch (Exception ex)
{
OstaleFunkcije.SetInLog(sql + "\r\n" + ex.ToString(), "65", "RemoteDB");
return(dataSet);
}
}
public static string Setings_Update(string sql)
{
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlCeCommand comm = new SqlCeCommand(sql, connection);
comm.ExecuteNonQuery();
connection.Close();
return("");
}
catch (NpgsqlException ex)
{
connection.Close();
OstaleFunkcije.SetInLog(sql + "\r\n" + ex.ToString(), "65", "RemoteDB");
return(ex.ToString());
}
}
public static NpgsqlDataAdapter NpgAdatpter(string sql)
{
try
{
if (remoteConnection.State.ToString() == "Closed")
{
remoteConnection.Open();
}
dataSet = new DataSet();
Npgadapter = new NpgsqlDataAdapter();
string sqlll = sql.Replace("+", "||").Replace("[", "\"").Replace("]", "\"").Replace("zbroj", "+");
Npgadapter.SelectCommand = new NpgsqlCommand(sql.Replace("+", "||").Replace("[", "\"").Replace("]", "\"").Replace("zbroj", "+"), remoteConnection);
//string debug = sql.Replace("+", "||").Replace("[", "\"").Replace("]", "\"").Replace("zbroj", "+");
NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(Npgadapter);
//remoteConnection.Close();
return(Npgadapter);
}
catch (NpgsqlException ex)
{
OstaleFunkcije.SetInLog(sql + "\r\n" + ex.ToString(), "65", "RemoteDB");
remoteConnection.Close();
return(Npgadapter);
}
}