public string Save(DdtJournalDay obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_journal_day SET " +
"dsid_hospitality_session = @HospitalitySession, " +
"dsid_patient = @Patient, " +
"dsdt_admission_date = @AdmissionDate, " +
"dsid_doctor = @Doctor, " +
"dsi_journal_type = @JournalType, " +
"dss_diagnosis = @Diagnosis, " +
"dss_name = @Name " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@AdmissionDate", obj.AdmissionDate);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name);
cmd.Parameters.AddWithValue("@JournalType", obj.JournalType);
cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_journal_day(dsid_hospitality_session,dsid_patient,dsdt_admission_date,dsid_doctor,dss_name,dsi_journal_type,dss_diagnosis) " +
"VALUES(@HospitalitySession,@Patient,@AdmissionDate,@Doctor,@Name,@JournalType,@Diagnosis) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@HospitalitySession", obj.HospitalitySession);
cmd.Parameters.AddWithValue("@Patient", obj.Patient);
cmd.Parameters.AddWithValue("@AdmissionDate", obj.AdmissionDate);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name);
cmd.Parameters.AddWithValue("@JournalType", obj.JournalType);
cmd.Parameters.AddWithValue("@Diagnosis", obj.Diagnosis == null ? "" : obj.Diagnosis);
return((string)cmd.ExecuteScalar());
}
}
}
}
//save phone user to DB
public void SavePhoneUser(PhoneUser phoneuser)
{
string query;
bool isUpdate = false;
// check for insert or update
if (phoneuser.PhoneUserId > 0)
{
query = UserUpdateQuery;
isUpdate = true;
}
else
{
query = UserInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("extension", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("firstname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("middlename", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("lastname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("crmuserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("voicemailid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("email", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("date_created", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("extension_server_uuid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("username", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("password", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = phoneuser.Extension;
command.Parameters[1].Value = phoneuser.FirstName;
command.Parameters[2].Value = phoneuser.MiddleName;
command.Parameters[3].Value = phoneuser.LastName;
command.Parameters[4].Value = phoneuser.CRMUserId;
command.Parameters[5].Value = phoneuser.AccountId;
command.Parameters[6].Value = phoneuser.VoiceMailId;
command.Parameters[7].Value = phoneuser.Email;
command.Parameters[8].Value = phoneuser.Date_Created;
command.Parameters[9].Value = phoneuser.Extension_Server_UUID;
command.Parameters[10].Value = phoneuser.UserName;
command.Parameters[11].Value = phoneuser.Password;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
internal void delete(int id)
{
var sql_con = new Npgsql.NpgsqlConnection("Host=localhost;Username=postgres;Password=SmartWork@123;Database=postgres");
sql_con.Open();
Npgsql.NpgsqlCommand dbcmd = sql_con.CreateCommand();
var commandText = "delete from resources where id =@id";
var cmd = new Npgsql.NpgsqlCommand(commandText, sql_con);
dbcmd.CommandText = commandText;
dbcmd.Parameters.AddWithValue("@id", id);
dbcmd.ExecuteNonQuery();
}
public bool ExecuteNonQuery(string sqlQuery)
{
_cmd = new Npgsql.NpgsqlCommand(sqlQuery, _cnn);
try
{
_cmd.ExecuteNonQuery();
return(true);
}
catch (Exception e)
{
return(false);
}
}
public void DeleteHistory(TicketHistory TicketHistory)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(TicketHistoryDeleteQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("historyid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = TicketHistory.HistoryId;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void SaveHistory(TicketHistory TicketHistory)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (TicketHistory.HistoryId > 0)
{
query = TicketHistoryUpdateQuery;
isUpdate = true;
}
else
{
query = TicketHistoryInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("hticketid", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("historydate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("userworked", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("haction", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("comment", NpgsqlTypes.NpgsqlDbType.Text));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("historyid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = TicketHistory.TicketId;
command.Parameters[1].Value = TicketHistory.HistoryDate;
command.Parameters[2].Value = TicketHistory.UserWorked;
command.Parameters[3].Value = TicketHistory.Action;
command.Parameters[4].Value = TicketHistory.Comment;
if (isUpdate)
{
command.Parameters[5].Value = TicketHistory.HistoryId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void DeleteAccounts(Domain.Account account)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(AccountDeleteQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = account.AccountId;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void DeleteZone(Zone zone)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(ZoneDeleteQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("zoneid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = zone.ZoneId;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void DeleteCalendarEvent(CalendarEvent calendarEvent)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EventDeleteQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("eventid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = calendarEvent.id;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void DeleteRecipe(Recipe recipe)
{
_conn.Open();
// Insert some data
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = _conn;
cmd.CommandText = "DELETE from recipes where id = @id";
cmd.Parameters.AddWithValue("id", recipe.GetID());
cmd.ExecuteNonQuery();
}
_conn.Close();
}
public void TestMethod1()
{
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(ApplicationEnv.Env.ConnectionString))
{
con.Open();
using (Npgsql.NpgsqlCommand command = con.CreateCommand())
{
command.CommandText = "DELETE FROM tran_event_overview WHERE event_id='test1'";
command.ExecuteNonQuery();
}
con.Close();
}
DatabaseManager.Executor.InsertEventOverview("test1", "test1");
}
private void button1_Click(object sender, EventArgs e)
{
if (this.richTextBox1.Text.Trim().Length > 2)
{
if (InsInBase == true)
{
Npgsql.NpgsqlCommand nci = new Npgsql.NpgsqlCommand("update error_log set user_descr = '" + this.richTextBox1.Text.Trim() + "' where err_id = '"+errorLogId+"' ", DBExchange.Inst.connectDb);
nci.ExecuteNonQuery();
}
else
{
if (!(System.IO.Directory.Exists(Application.StartupPath + "\\Errors\\")))
{
System.IO.Directory.CreateDirectory(Application.StartupPath + "\\Errors\\");
}
FileStream fs = new FileStream(Application.StartupPath + "\\Errors\\errlog.txt", FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter s = new StreamWriter(fs);
s.Close();
fs.Close();
FileStream fs1 = new FileStream(Application.StartupPath + "\\Errors\\errlog.txt", FileMode.Append, FileAccess.Write);
StreamWriter s1 = new StreamWriter(fs1);
s1.Write("Date/Time: " + DateTime.Now.ToString());
s1.Write("\nКомментарий: " + this.richTextBox1.Text.Trim());
s1.Write("\n=========================================================================================== \n\n");
s1.Close();
fs1.Close();
}
}
FormRegisterError.ActiveForm.Close();
}
internal void update(int id, Resources value)
{
var sql_con = new Npgsql.NpgsqlConnection("Host=localhost;Username=postgres;Password=SmartWork@123;Database=postgres");
sql_con.Open();
Npgsql.NpgsqlCommand dbcmd = sql_con.CreateCommand();
var commandText = "update resources set \"name\"=:name,\"salery\"=:salery,\"age\"=:age,\"place\"=:place where id = " + id + "";
var cmd = new Npgsql.NpgsqlCommand(commandText, sql_con);
dbcmd.CommandText = commandText;
dbcmd.Parameters.AddWithValue("name", value.name);
dbcmd.Parameters.AddWithValue("age", value.age);
dbcmd.Parameters.AddWithValue("salery", value.salery);
dbcmd.Parameters.AddWithValue("place", value.place);
dbcmd.ExecuteNonQuery();
}
public bool Update(DataProtectionKey dataProtectionKey)
{
using (var con = new Npgsql.NpgsqlConnection(_connectionString))
{
var sql = @"update public.""DataProtectionKeys"" set ""XmlData""=@XmlData where ""FriendlyName""=@FriendlyName;";
using (var cmd = new Npgsql.NpgsqlCommand(sql, con))
{
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("@FriendlyName", dataProtectionKey.FriendlyName));
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("@XmlData", dataProtectionKey.XmlData));
con.Open();
var result = cmd.ExecuteNonQuery() > 0;
con.Close();
return(result);
}
}
}
/// <summary>
/// add dataProtectionKey
/// </summary>
/// <param name="dataProtectionKey">Data Protection Key</param>
/// <returns></returns>
public bool Add(DataProtectionKey dataProtectionKey)
{
using (var con = new Npgsql.NpgsqlConnection(_connectionString))
{
var sql = @"INSERT INTO public.""DataProtectionKeys""(""FriendlyName"", ""XmlData"") VALUES(@FriendlyName, @XmlData);";
using (var cmd = new Npgsql.NpgsqlCommand(sql, con))
{
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("@FriendlyName", dataProtectionKey.FriendlyName));
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("@XmlData", dataProtectionKey.XmlData));
con.Open();
var result = cmd.ExecuteNonQuery() > 0;
con.Close();
return(result);
}
}
}
internal void insert(Resources EmpData)
{
var sql_con = new Npgsql.NpgsqlConnection("Host=localhost;Username=postgres;Password=SmartWork@123;Database=postgres");
sql_con.Open();
Npgsql.NpgsqlCommand dbcmd = sql_con.CreateCommand();
var commandText = "insert into resources (id,name,salery,age,place) values(@id,@name,@age,@salery,@place)";
var cmd = new Npgsql.NpgsqlCommand(commandText, sql_con);
dbcmd.CommandText = commandText;
dbcmd.Parameters.AddWithValue("@id", EmpData.id);
dbcmd.Parameters.AddWithValue("@name", EmpData.name);
dbcmd.Parameters.AddWithValue("@age", EmpData.age);
dbcmd.Parameters.AddWithValue("@salery", EmpData.salery);
dbcmd.Parameters.AddWithValue("@place", EmpData.place);
dbcmd.ExecuteNonQuery();
}
public void CreateRecipe(Recipe recipe)
{
_conn.Open();
// Insert some data
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = _conn;
cmd.CommandText = "INSERT INTO recipes (name,ingredients,instructions) VALUES (@name, @ingr, @instr)";
cmd.Parameters.AddWithValue("name", recipe.GetName());
cmd.Parameters.AddWithValue("ingr", recipe.GetIngredients());
cmd.Parameters.AddWithValue("instr", recipe.GetInstructions());
cmd.ExecuteNonQuery();
}
_conn.Close();
}
public void UpdateRecipe(Recipe recipe)
{
_conn.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = _conn;
cmd.CommandText = "UPDATE recipes SET (user_id,name,ingredients,instructions) = (@uid, @name, @ingr, @instr) WHERE id = @id";
cmd.Parameters.AddWithValue("id", recipe.GetID());
cmd.Parameters.AddWithValue("name", recipe.GetName());
cmd.Parameters.AddWithValue("ingr", recipe.GetIngredients());
cmd.Parameters.AddWithValue("instr", recipe.GetInstructions());
cmd.ExecuteNonQuery();
}
_conn.Close();
}
private void button3_Click(object sender, EventArgs e)
{
string _conStr = ConfigurationManager.AppSettings["DB_SK"];
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(_conStr);
connection.Open();
Npgsql.NpgsqlTransaction et = connection.BeginTransaction();
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand("nolstedt01(:p_personnr,:p_contactchannel_id)", connection, et);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("p_personnr", "196402230319");
cmd.Parameters.AddWithValue("p_contactchannel_id", "48AF6816C15B49A9956079BD6D8DE561");
cmd.Prepare();
cmd.ExecuteNonQuery();
et.Save("apa");
et.Commit();
connection.Close();
}
public bool UpdateObject(IDBObject dbObject)
{
Npgsql.NpgsqlConnection conn = null;
Npgsql.NpgsqlTransaction trans = null;
Npgsql.NpgsqlConnection readerConn = null;
try
{
if (dbObject != null)
{
conn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection();
readerConn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection();
object result = this.GetDataReader(dbObject.GetObjectByIdQuery(), readerConn);
if (result != null)
{
string strSql = dbObject.GetUpdateStatement();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSql);
command.Connection = conn;
trans = conn.BeginTransaction();
command.ExecuteNonQuery();
trans.Commit();
return(true);
}
}
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
}
if (readerConn != null)
{
readerConn.Close();
}
}
return(false);
}
/// <summary>
/// Runs a query and returns void
/// </summary>
/// <param name="name">Stored function name</param>
/// <param name="param">List of parameter to use. Set null for no parameters.</param>
/// <param name="connectionString">Connection sting to use</param>
/// <returns>boolean</returns>
public static bool Void(string name, IEnumerable <Npgsql.NpgsqlParameter> param, string connectionString)
{
if (Generics.StringExtensions.IsEmpty(name) || Generics.StringExtensions.IsEmpty(connectionString))
{
return(false);
}
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString))
{
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(name, connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
if (param != null)
{
foreach (var item in param)
{
command.Parameters.Add(item);
}
}
else
{
command.Parameters.Clear();
}
try
{
connection.Open();
var transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
}
catch (AggregateException e)
{
ExceptionDispatchInfo.Capture(e.InnerException).Throw();
}
finally
{
connection.Close();
}
}
}
return(true);
}
public void SaveEquipment(Domain.Equipment equipment)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (equipment.EquipmentId > 0)
{
query = EquipmentUpdateQuery;
isUpdate = true;
}
else
{
query = EquipmentInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("type", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("active", NpgsqlTypes.NpgsqlDbType.Boolean));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("equipmentid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = equipment.Name;
command.Parameters[1].Value = equipment.Type;
command.Parameters[2].Value = equipment.Active;
if (isUpdate)
{
command.Parameters[3].Value = equipment.EquipmentId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public string Save(DdtConsiliumGroupMember obj)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(obj.ObjectId) != null)
{
string sql = "UPDATE ddt_consilium_group_member SET " +
"dsid_group = @Group, " +
"dss_name = @Name, " +
"dsid_doctor = @Doctor, " +
"dsid_group = @Group " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Group", obj.Group);
cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@Group", obj.Group);
cmd.Parameters.AddWithValue("@ObjectId", obj.ObjectId);
cmd.ExecuteNonQuery();
}
return(obj.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_consilium_group_member(dsid_group,dss_name,dsid_doctor,dsid_group) " +
"VALUES(@Group,@Name,@Doctor,@Group) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Group", obj.Group);
cmd.Parameters.AddWithValue("@Name", obj.Name == null ? "" : obj.Name);
cmd.Parameters.AddWithValue("@Doctor", obj.Doctor);
cmd.Parameters.AddWithValue("@Group", obj.Group);
return((string)cmd.ExecuteScalar());
}
}
}
}
/// <summary>
/// create DataProtectionKeys
/// </summary>
void CreateTable()
{
using (var con = new Npgsql.NpgsqlConnection(_connectionString))
{
var sql = @"CREATE TABLE if not exists public.""DataProtectionKeys""
(
""FriendlyName"" character varying(256) COLLATE pg_catalog.""default"" NOT NULL,
""XmlData"" text COLLATE pg_catalog.""default"",
CONSTRAINT ""DataProtectionKeys_pkey"" PRIMARY KEY(""FriendlyName"")
)";
using (var cmd = new Npgsql.NpgsqlCommand(sql, con))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
private void InsertAccountUploadedFile(int ID, string path, string query)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.AddWithValue(UploadedfileTable.filename, System.IO.Path.GetFileName(path));
//todo: determine specific mime type
command.Parameters.AddWithValue(UploadedfileTable.filetype, "image");
command.Parameters.AddWithValue(UploadedfileTable.filepath, path);
command.Parameters.AddWithValue(UploadedfileTable.accountid, ID);
command.ExecuteNonQuery();
}
}
}
public bool Delete()
{
string sQuery = " DELETE FROM tbm_booking WHERE bookingid=@bid";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.Parameters.Add("@bid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = customerid;
cmd.CommandText = sQuery;
try
{
cmd.ExecuteNonQuery();
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
}
public void SaveZone(Zone zone)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (zone.ZoneId > 0)
{
query = ZoneUpdateQuery;
isUpdate = true;
}
else
{
query = ZoneInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("zone_number", NpgsqlTypes.NpgsqlDbType.Integer));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("zone_id", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = zone.ZoneNumber;
if (isUpdate)
{
command.Parameters[2].Value = zone.ZoneId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void create_server_with_existing_instance_id_and_table_test()
{
Guid instanceId = Guid.NewGuid();
using (var server = new MysticMind.PostgresEmbed.PgServer(
"9.5.5.1",
PG_USER,
addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION,
instanceId: instanceId))
{
server.Start();
// assert if instance id drectory exists
Assert.True(Directory.Exists(server.InstanceDir));
// Note: set pooling to false to prevent connecting issues
// https://github.com/npgsql/npgsql/issues/939
string connStr = string.Format(CONN_STR, server.PgPort, PG_USER);
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd =
new Npgsql.NpgsqlCommand(
"CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
using (var server = new MysticMind.PostgresEmbed.PgServer(
"9.5.5.1",
PG_USER,
addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION,
instanceId: instanceId,
clearInstanceDirOnStop: true))
{
server.Start();
// assert if instance id drectory exists
Assert.True(Directory.Exists(server.InstanceDir));
}
}
public string Save(DdtRelation relation)
{
using (dynamic connection = connectionFactory.GetConnection())
{
if (GetById(relation.ObjectId) != null)
{
string sql = "UPDATE ddt_relation SET " +
"dsid_parent = @Parent, " +
"dsid_child = @Child, " +
"dss_child_type = @ChildType " +
"WHERE r_object_id = @ObjectId";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Parent", relation.Parent);
cmd.Parameters.AddWithValue("@Child", relation.Child);
cmd.Parameters.AddWithValue("@ChildType", relation.ChildType);
cmd.Parameters.AddWithValue("@ObjectId", relation.ObjectId);
cmd.ExecuteNonQuery();
}
return(relation.ObjectId);
}
else
{
string sql = "INSERT INTO ddt_relation(dsid_parent,dsid_child,dss_child_type) " +
"VALUES(@Parent,@Child,@ChildType) RETURNING r_object_id";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Parent", relation.Parent);
cmd.Parameters.AddWithValue("@Child", relation.Child);
cmd.Parameters.AddWithValue("@ChildType", relation.ChildType);
return((string)cmd.ExecuteScalar());
}
}
}
}
protected static void RunStatement(string statement, IDictionary <string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
if (parameters == null)
{
parameters = new Dictionary <string, Npgsql.NpgsqlParameter>();
}
using (var cmd = new Npgsql.NpgsqlCommand(statement, conn, trans))
{
foreach (var parameter in parameters.Keys)
{
if (statement.Contains(parameter))
{
var value = parameters[parameter];
Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value));
cmd.Parameters.Add(value);
}
}
cmd.ExecuteNonQuery();
}
}
public bool SoftDelete()
{
string sQuery = "Update tbm_booking set dlt=true,opedit=@opedit,pcedit=@pcedit,luedit=now()" +
"where bookingid=@bid";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
cmd.Parameters.Add("@bid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = bookingid;
cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = clsGlobal.strUserName;
cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = System.Windows.Forms.SystemInformation.ComputerName;
try
{
cmd.ExecuteNonQuery();
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
}
public void SaveZipCode(int zoneid, int zipcode)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(ZipInsertquery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("zipcode", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zonenumber", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
var storezip = zipcode.ToString().Substring(0, 5);
command.Parameters[0].Value = storezip;
command.Parameters[1].Value = zoneid;
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public override bool InsertRow(string table, Row row)
{
StringBuilder sql = new StringBuilder();
lock (DatabaseLock)
{
try
{
if (!IsConnected)
{
Syslog.DebugLog("Postponing request to insert a row into database which is not connected");
//lock(unwritten.PendingRows)
//{
// unwritten.PendingRows.Add(new SerializedRow(table, row));
//}
//FlushRows();
return false;
}
Npgsql.NpgsqlCommand s = new Npgsql.NpgsqlCommand();
s.Connection = this.connection;
sql.Append("INSERT INTO ");
sql.Append(table);
// let's check if there are headers
bool headers = true;
string header = "";
foreach (Row.Value value in row.Values)
{
if (value.Column == null)
{
headers = false;
break;
}
header += value.Column + ", ";
}
if (header.EndsWith(", "))
header = header.Substring(0, header.Length - 2);
if (headers)
{
sql.Append(" (" + header + ")");
}
sql.Append(" VALUES (");
int cv = 0;
foreach (Row.Value value in row.Values)
{
sql.Append(":v" + cv.ToString() + ", ");
switch (value.Type)
{
case DataType.Boolean:
s.Parameters.Add(new Npgsql.NpgsqlParameter("v" + cv.ToString(), NpgsqlTypes.NpgsqlDbType.Boolean));
s.Parameters[cv].Value = bool.Parse(value.Data);
break;
case DataType.Integer:
s.Parameters.Add(new Npgsql.NpgsqlParameter("v" + cv.ToString(), NpgsqlTypes.NpgsqlDbType.Integer));
s.Parameters[cv].Value = int.Parse(value.Data);
break;
case DataType.Varchar:
s.Parameters.Add(new Npgsql.NpgsqlParameter("v" + cv.ToString(), NpgsqlTypes.NpgsqlDbType.Varchar));
s.Parameters[cv].Value = value.Data;
break;
case DataType.Text:
s.Parameters.Add(new Npgsql.NpgsqlParameter("v" + cv.ToString(), NpgsqlTypes.NpgsqlDbType.Text));
s.Parameters[cv].Value = value.Data;
break;
case DataType.Date:
s.Parameters.Add(new Npgsql.NpgsqlParameter("v" + cv.ToString(), NpgsqlTypes.NpgsqlDbType.Timestamp));
s.Parameters[cv].Value = DateTime.Parse(value.Data);
break;
}
cv++;
}
if (sql.ToString().EndsWith(", "))
{
sql.Remove(sql.Length - 2, 2);
}
sql.Append(");");
s.CommandText = sql.ToString();
SystemHooks.OnSQL(LocalName, sql.ToString());
s.ExecuteNonQuery();
return true;
}
catch (Npgsql.NpgsqlException me)
{
ErrorBuffer = me.Message;
Syslog.Log("Error while storing a row to DB " + me, true);
Syslog.DebugLog("SQL: " + sql.ToString());
HandleError(sql.ToString());
/*lock(unwritten.PendingRows)
{
unwritten.PendingRows.Add(new SerializedRow(table, row));
}
FlushRows();
*/
return false;
}
}
}
public override void Commit()
{
SystemHooks.OnSQL(LocalName, "commit;");
Npgsql.NpgsqlCommand c = new Npgsql.NpgsqlCommand("commit;", this.connection);
c.ExecuteNonQuery();
}
public override int Delete(string table, string query)
{
int result = 0;
string sql = "DELETE FROM " + table;
if (!String.IsNullOrEmpty(query))
sql += " " + query;
SystemHooks.OnSQL(LocalName, sql);
lock (DatabaseLock)
{
try
{
Npgsql.NpgsqlCommand SqlCommand = new Npgsql.NpgsqlCommand(sql, this.connection);
result = SqlCommand.ExecuteNonQuery();
}
catch (Npgsql.NpgsqlException me)
{
ErrorBuffer = me.Message;
HandleError(sql);
}
}
return result;
}
public override void ExecuteNonQuery(string sql, List<Bind> bind_var = null)
{
if (!this.IsConnected)
throw new WmibException("The database is not connected");
try
{
Npgsql.NpgsqlCommand c = new Npgsql.NpgsqlCommand(sql, this.connection);
if (bind_var != null)
BindVars(sql, bind_var, c);
SystemHooks.OnSQL(LocalName, sql);
c.ExecuteNonQuery();
}
catch (Npgsql.NpgsqlException me)
{
ErrorBuffer = me.Message;
HandleError(sql, bind_var);
}
}
protected static void RunScript(string script, IDictionary<string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
if (parameters == null) parameters = new Dictionary<string, Npgsql.NpgsqlParameter>();
foreach (var commandText in script.Split(';'))
{
var sql = commandText + ";";
Log.Debug(d => d("Script command: {0}", sql));
using (var cmd = new Npgsql.NpgsqlCommand(sql, conn, trans))
{
foreach (var parameter in parameters.Keys)
{
if (sql.Contains(parameter))
{
var value = parameters[parameter];
Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value));
cmd.Parameters.Add(value);
}
}
cmd.ExecuteNonQuery();
}
}
Log.Debug(d => d("Script complete."));
}
protected static void RunStatement(string statement, IDictionary<string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
if (parameters == null) parameters = new Dictionary<string, Npgsql.NpgsqlParameter>();
using (var cmd = new Npgsql.NpgsqlCommand(statement, conn, trans))
{
foreach (var parameter in parameters.Keys)
{
if (statement.Contains(parameter))
{
var value = parameters[parameter];
Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value));
cmd.Parameters.Add(value);
}
}
cmd.ExecuteNonQuery();
}
}
protected static void SetDBVersion(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans, string versionNumber)
{
using (var cmd = new Npgsql.NpgsqlCommand("update versions set version = @version where name = 'application';", conn, trans))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@version", NpgsqlTypes.NpgsqlDbType.Varchar, 15).Value = versionNumber;
cmd.ExecuteNonQuery();
}
}
