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