/* * static void Main(string[] args) * { * //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;Pooling=false;"; //works, pg1 without failover, due to implicity TargetSessionAttributes=Any * //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Any"; //works, pg1 without failover * //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Primary;"; //works, straight to pg1 * //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Secondary;"; //works, tests and fails over to pg2 * //var connectionString = "Host=PG2,PG1;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Secondary;"; //works, straight to pg2 * //var connectionString = "Host=PG2;username=robtest_login;password=robtest_password;Database=robtest;"; * //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Primary;Pooling=true;KeepAlive=5;"; //works, straight to pg1 * var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Primary;Pooling=true;KeepAlive=5;Enlist=false;Minimum Pool Size=4;"; //works, straight to pg1 * * do * { * try * { * using (var pgConnection = new NpgsqlConnection(connectionString)) * { * pgConnection.Open(); * using (var pgTransaction = pgConnection.BeginTransaction()) * { * try * { * using (var command = new NpgsqlCommand("SELECT state,COUNT(1),pg_is_in_recovery(),inet_server_addr()::text FROM pg_stat_activity GROUP BY state;", pgConnection, pgTransaction)) * { * using (var reader = command.ExecuteReader()) * { * while (reader.Read()) * { * Console.WriteLine("{0} {1} {2} {3}", reader.GetString(0), reader.GetInt64(1), reader.GetBoolean(2), reader.GetString(3)); * } * } * } * } * finally * { * pgTransaction.Rollback(); * } * } * } * Console.WriteLine("q to quit, anything else to run query again!"); * } * catch(Exception e) * { * Console.Error.WriteLine(e); * } * * } while (true || Console.ReadLine() != "q"); * } */ static void Main(string[] args) { //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;Pooling=false;"; //works, pg1 without failover, due to implicity TargetSessionAttributes=Any //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Any"; //works, pg1 without failover //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Primary;"; //works, straight to pg1 //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Secondary;"; //works, tests and fails over to pg2 //var connectionString = "Host=PG2,PG1;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Secondary;"; //works, straight to pg2 //var connectionString = "Host=PG2;username=robtest_login;password=robtest_password;Database=robtest;"; //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Primary;Pooling=true;KeepAlive=5;"; //works, straight to pg1 //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Primary;Pooling=false"; // //var connectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Primary;Pooling=false;KeepAlive=5"; var primaryConnectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Primary;HostRecheckSeconds=1;Pooling=true;KeepAlive=5;Enlist=false;Minimum Pool Size=4;Multiplexing=false;"; var secondaryConnectionString = "Host=PG1,PG2;username=robtest_login;password=robtest_password;Database=robtest;TargetSessionAttributes=Secondary;HostRecheckSeconds=1;Pooling=true;KeepAlive=5;Enlist=false;Minimum Pool Size=4;Multiplexing=false;"; //var primaryConnectionString = connectionString; do { try { Console.WriteLine("-------------------------------------"); using (var primaryConnection = new NpgsqlConnection(primaryConnectionString)) using (var secondaryConnection = new NpgsqlConnection(secondaryConnectionString)) { primaryConnection.Open(); secondaryConnection.Open(); using (var pgPrimaryTransaction = primaryConnection.BeginTransaction()) using (var pgSecondaryTransaction = secondaryConnection.BeginTransaction()) { try { using (var primaryCommand = new NpgsqlCommand("SELECT pg_is_in_recovery(),inet_server_addr()::text;", primaryConnection, pgPrimaryTransaction)) { using (var reader = primaryCommand.ExecuteReader()) { while (reader.Read()) { Console.WriteLine("primary {0} {1}", reader.GetBoolean(0), reader.GetString(1)); } } } using (var secondaryCommand = new NpgsqlCommand("SELECT pg_is_in_recovery(),inet_server_addr()::text;", secondaryConnection, pgSecondaryTransaction)) { using (var reader = secondaryCommand.ExecuteReader()) { while (reader.Read()) { Console.WriteLine("seconda {0} {1}", reader.GetBoolean(0), reader.GetString(1)); } } } } finally { pgPrimaryTransaction.Rollback(); pgSecondaryTransaction.Rollback(); } } Console.WriteLine("q to quit, anything else to run query again!"); } } catch (Exception e) { Console.Error.WriteLine(e); //Debugger.Break(); } } while (Console.ReadLine().ToLower() != "q"); }
public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection) { string username = (string)context["UserName"]; bool isAuthenticated = (bool)context["IsAuthenticated"]; if (string.IsNullOrEmpty(username)) { return; } if (collection.Count < 1) { return; } if (!ProfileExists(username)) { CreateProfileForUser(username, isAuthenticated); } using (NpgsqlConnection dbConn = new NpgsqlConnection(m_connectionString)) { using (NpgsqlCommand deleteCommand = dbConn.CreateCommand(), insertCommand = dbConn.CreateCommand()) { deleteCommand.CommandText = string.Format(CultureInfo.InvariantCulture, "DELETE FROM \"{0}\" WHERE \"Name\" = @Name AND \"Profile\" = (SELECT \"pId\" FROM \"{1}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsAnonymous\" = @IsAuthenticated)", s_profileDataTableName, s_profilesTableName); deleteCommand.Parameters.Add("@Name", NpgsqlDbType.Varchar, 255); deleteCommand.Parameters.Add("@Username", NpgsqlDbType.Varchar, 255).Value = username; deleteCommand.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar, 255).Value = m_applicationName; deleteCommand.Parameters.Add("@IsAuthenticated", NpgsqlDbType.Boolean).Value = !isAuthenticated; insertCommand.CommandText = string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"pId\", \"Profile\", \"Name\", \"ValueString\", \"ValueBinary\") VALUES (@pId, (SELECT \"pId\" FROM \"{1}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsAnonymous\" = @IsAuthenticated), @Name, @ValueString, @ValueBinary)", s_profileDataTableName, s_profilesTableName); insertCommand.Parameters.Add("@pId", NpgsqlDbType.Uuid); insertCommand.Parameters.Add("@Name", NpgsqlDbType.Varchar, 255); insertCommand.Parameters.Add("@ValueString", NpgsqlDbType.Text); insertCommand.Parameters["@ValueString"].IsNullable = true; insertCommand.Parameters.Add("@ValueBinary", NpgsqlDbType.Bytea); insertCommand.Parameters["@ValueBinary"].IsNullable = true; insertCommand.Parameters.Add("@Username", NpgsqlDbType.Varchar, 255).Value = username; insertCommand.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar, 255).Value = m_applicationName; insertCommand.Parameters.Add("@IsAuthenticated", NpgsqlDbType.Boolean).Value = !isAuthenticated; NpgsqlTransaction dbTrans = null; try { dbConn.Open(); deleteCommand.Prepare(); insertCommand.Prepare(); using (dbTrans = dbConn.BeginTransaction()) { foreach (SettingsPropertyValue item in collection) { if (!item.IsDirty) { continue; } deleteCommand.Parameters["@Name"].Value = item.Name; insertCommand.Parameters["@pId"].Value = Guid.NewGuid().ToString(); insertCommand.Parameters["@Name"].Value = item.Name; if (item.Property.SerializeAs == SettingsSerializeAs.String) { insertCommand.Parameters["@ValueString"].Value = SerializationHelper.SerializeToBase64(item.PropertyValue); insertCommand.Parameters["@ValueBinary"].Value = DBNull.Value; } else if (item.Property.SerializeAs == SettingsSerializeAs.Xml) { item.SerializedValue = SerializationHelper.SerializeToXml <object>(item.PropertyValue, s_serializationNamespace); insertCommand.Parameters["@ValueString"].Value = item.SerializedValue; insertCommand.Parameters["@ValueBinary"].Value = DBNull.Value; } else if (item.Property.SerializeAs == SettingsSerializeAs.Binary) { item.SerializedValue = SerializationHelper.SerializeToBinary(item.PropertyValue); insertCommand.Parameters["@ValueString"].Value = DBNull.Value; insertCommand.Parameters["@ValueBinary"].Value = item.SerializedValue; } deleteCommand.ExecuteNonQuery(); insertCommand.ExecuteNonQuery(); } // Attempt to commit the transaction dbTrans.Commit(); } } catch (NpgsqlException e) { Trace.WriteLine(e.ToString()); try { // Attempt to roll back the transaction Trace.WriteLine(Properties.Resources.LogRollbackAttempt); dbTrans.Rollback(); } catch (NpgsqlException re) { // Rollback failed Trace.WriteLine(Properties.Resources.ErrRollbackFailed); Trace.WriteLine(re.ToString()); } throw new ProviderException(Properties.Resources.ErrOperationAborted); } finally { if (dbConn != null) { dbConn.Close(); } } } } UpdateActivityDates(username, isAuthenticated, false); }
// PQMテーブルへの一括登録 public bool sqlMultipleInsertMeasurementToPqmTable(string model, string process, string inspect, DateTime lot, DateTime inspectdate, string line, DataTable dt, double upper, double lower) { int res1; bool res2 = false; conStringPqmDb = decideConnectionString(model); connection = new NpgsqlConnection(conStringPqmDb); connection.Open(); NpgsqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); try { string serno = string.Empty; string site = "NCVC"; string factory = "QA"; string tjudge = "0"; string tstatus = "IPQC"; double inspectdata = 0; //= double.Parse(dt.Rows[0]["m1"].ToString()); string judge = "0"; string pqmTable1 = decideReferenceTable(model, lot); string pqmTable2 = pqmTable1 + "data"; // DBテーブルに既に存在するデータを削除し、登録。ただし例外発生時、削除も登録もロールバック。 // 削除①: string sql1 = "delete from " + pqmTable1 + " where " + "model ='" + model + "' and " + "process ='" + process + "' and " + "lot ='" + lot + "' and " + "inspectdate ='" + inspectdate + "' and " + "line ='" + line + "'"; System.Diagnostics.Debug.Print(sql1); NpgsqlCommand command1 = new NpgsqlCommand(sql1, connection); command1.ExecuteNonQuery(); // 削除②: string sql2 = "delete from " + pqmTable2 + " where " + "inspect ='" + inspect + "' and " + "lot ='" + lot + "' and " + "inspectdate ='" + inspectdate + "'"; System.Diagnostics.Debug.Print(sql2); NpgsqlCommand command2 = new NpgsqlCommand(sql2, connection); command2.ExecuteNonQuery(); // 登録①: string sql3 = "INSERT INTO " + pqmTable1 + " (serno, lot, model, site, factory, line, process, inspectdate, tjudge, tstatus) " + "VALUES (:serno, :lot, :model, :site, :factory, :line, :process, :inspectdate, :tjudge, :tstatus)"; NpgsqlCommand command3 = new NpgsqlCommand(sql3, connection); command3.Parameters.Add(new NpgsqlParameter("serno", NpgsqlTypes.NpgsqlDbType.Varchar)); command3.Parameters.Add(new NpgsqlParameter("lot", NpgsqlTypes.NpgsqlDbType.Varchar)); command3.Parameters.Add(new NpgsqlParameter("model", NpgsqlTypes.NpgsqlDbType.Varchar)); command3.Parameters.Add(new NpgsqlParameter("site", NpgsqlTypes.NpgsqlDbType.Varchar)); command3.Parameters.Add(new NpgsqlParameter("factory", NpgsqlTypes.NpgsqlDbType.Varchar)); command3.Parameters.Add(new NpgsqlParameter("line", NpgsqlTypes.NpgsqlDbType.Varchar)); command3.Parameters.Add(new NpgsqlParameter("process", NpgsqlTypes.NpgsqlDbType.Varchar)); command3.Parameters.Add(new NpgsqlParameter("inspectdate", NpgsqlTypes.NpgsqlDbType.TimestampTZ)); command3.Parameters.Add(new NpgsqlParameter("tjudge", NpgsqlTypes.NpgsqlDbType.Varchar)); command3.Parameters.Add(new NpgsqlParameter("tstatus", NpgsqlTypes.NpgsqlDbType.Varchar)); string[] fldAry = { "m1", "m2", "m3", "m4", "m5" }; int k = 1; for (int i = 0; i < dt.Rows.Count; i++) { // 天秤用、2つの値の差異のみ登録する string qcuser = dt.Rows[i]["qc_user"].ToString(); if (qcuser == "1. Upper" || qcuser == "2. Lower") { continue; } for (int j = 0; j < 5; j++) { object value = dt.Rows[i][(fldAry[j])]; if (value == DBNull.Value) { continue; } inspectdata = (double)value; if (inspectdata >= lower && inspectdata <= upper) { tjudge = "0"; } else { tjudge = "1"; } command3.Parameters[0].Value = k.ToString(); command3.Parameters[1].Value = lot; command3.Parameters[2].Value = model; command3.Parameters[3].Value = site; command3.Parameters[4].Value = factory; command3.Parameters[5].Value = line; command3.Parameters[6].Value = process; command3.Parameters[7].Value = inspectdate; command3.Parameters[8].Value = tjudge; command3.Parameters[9].Value = tstatus; k++; res1 = command3.ExecuteNonQuery(); if (res1 == -1) { res2 = true; } } } // 登録②: string sql4 = "INSERT INTO " + pqmTable2 + " (serno, lot, inspectdate, inspect, inspectdata, judge) " + "VALUES (:serno, :lot, :inspectdate, :inspect, :inspectdata, :judge)"; NpgsqlCommand command4 = new NpgsqlCommand(sql4, connection); command4.Parameters.Add(new NpgsqlParameter("serno", NpgsqlTypes.NpgsqlDbType.Varchar)); command4.Parameters.Add(new NpgsqlParameter("lot", NpgsqlTypes.NpgsqlDbType.Varchar)); command4.Parameters.Add(new NpgsqlParameter("inspectdate", NpgsqlTypes.NpgsqlDbType.TimestampTZ)); command4.Parameters.Add(new NpgsqlParameter("inspect", NpgsqlTypes.NpgsqlDbType.Varchar)); command4.Parameters.Add(new NpgsqlParameter("inspectdata", NpgsqlTypes.NpgsqlDbType.Double)); command4.Parameters.Add(new NpgsqlParameter("judge", NpgsqlTypes.NpgsqlDbType.Varchar)); k = 1; for (int i = 0; i < dt.Rows.Count; i++) { // 天秤用、2つの値の差異のみ登録する string qcuser = dt.Rows[i]["qc_user"].ToString(); if (qcuser == "1. Upper" || qcuser == "2. Lower") { continue; } for (int j = 0; j < 5; j++) { object value = dt.Rows[i][(fldAry[j])]; if (value == DBNull.Value) { continue; } inspectdata = (double)value; if (inspectdata >= lower && inspectdata <= upper) { judge = "0"; } else { judge = "1"; } command4.Parameters[0].Value = k.ToString(); command4.Parameters[1].Value = lot; command4.Parameters[2].Value = inspectdate; command4.Parameters[3].Value = inspect; command4.Parameters[4].Value = inspectdata; command4.Parameters[5].Value = judge; k++; res1 = command4.ExecuteNonQuery(); if (res1 == -1) { res2 = true; } } } if (!res2) { transaction.Commit(); connection.Close(); return(true); } else { transaction.Rollback(); MessageBox.Show("Not successful!", "Database Responce", MessageBoxButtons.OK, MessageBoxIcon.Warning); connection.Close(); transaction.Rollback(); return(false); } } catch (Exception ex) { transaction.Rollback(); MessageBox.Show("Not successful!" + System.Environment.NewLine + ex.Message , "Database Responce", MessageBoxButtons.OK, MessageBoxIcon.Warning); connection.Close(); return(false); } }
/// <summary> /// Добавление нового глоссария. /// </summary> /// <param name="userId">Идентификатор пользователя.</param> /// <param name="glossary">Новый глоссарий.</param> /// <returns></returns> public async Task AddAsync(Guid userId, GlossariesForEditingDTO glossary) { try { using (var dbConnection = new NpgsqlConnection(connectionString)) { dbConnection.Open(); using (IDbTransaction transaction = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted)) { var newGlossaryFileId = await this.fr.AddAsync(new File() { id_localization_project = null, // glossary.Localization_Projects_Ids.First(x => x.HasValue).Value, name_text = glossary.Name_text, date_of_change = DateTime.Now, is_folder = false, is_last_version = true, visibility = true }, dbConnection, transaction); glossary.ID_File = newGlossaryFileId; //---------------- //Добавление нового глоссария var newGlossaries = new { name_text = glossary.Name_text, description = glossary.Description, id_file = glossary.ID_File }; /*var query = new Query("glossaries").AsInsert(newGlossaries, * true); //true - вернуть сгенерированный id нового объекта * var compiledQuery = _compiler.Compile(query); * LogQuery(compiledQuery); * * * //После выполнение запроса получаем сгенерированный id нового объекта * var idOfNewGlossary = await dbConnection * .ExecuteScalarAsync<Guid>( * sql: compiledQuery.Sql, * param: compiledQuery.NamedBindings, transaction: transaction); */ var sql = " INSERT INTO glossaries(name_text, description, id_file) VALUES('" + newGlossaries.name_text + "', '" + newGlossaries.description + "', '" + newGlossaries.id_file + "') RETURNING glossaries.id"; LogQuery(sql); var idOfNewGlossary = await dbConnection .ExecuteScalarAsync <Guid>( sql, transaction : transaction); //Добавление в таблицу "GlossariesLocales" записей связи глоссария с языками перевода (Glossaries с Locales) var loc = ConvertData.ConverLocale(glossary.Locales_Ids); await EditGlossariesLocalesAsync(idOfNewGlossary, loc, glossary.ID_File, dbConnection, transaction, false); //Добавление в таблицу "localization_projectsGlossaries" записей связи глоссария с проектами локализации (Glossaries с localization_projects) await EditGlossariesLocalizationProjectsAsync(userId, idOfNewGlossary, glossary.Localization_Projects_Ids, dbConnection, transaction, false); transaction.Commit(); } } } catch (NpgsqlException exception) { _loggerError.WriteLn($"Ошибка в {nameof(GlossariesRepository)}.{nameof(GlossariesRepository.AddAsync)} {nameof(NpgsqlException)} ", exception); } catch (Exception exception) { _loggerError.WriteLn($"Ошибка в {nameof(GlossariesRepository)}.{nameof(GlossariesRepository.AddAsync)} {nameof(Exception)} ", exception); } }
public List <AraPayment> ReadAllPaymentsBetweenDates(DateTime initDate, DateTime endDate) { conn = DAO.getConnection(); AraPayment readPayment = new AraPayment(); List <AraPayment> payments = new List <AraPayment>(); int id; DateTime paymentDate; DateTime expirationDate; String paypalTransactionId; Double amount; Section section; Student student; try { conn = DAO.getConnection(); NpgsqlTransaction tran = conn.BeginTransaction(); NpgsqlCommand command = new NpgsqlCommand(DAOReportResource.PaymentListSP, conn); NpgsqlParameter parameter = new NpgsqlParameter(); NpgsqlParameter parameter_2 = new NpgsqlParameter(); parameter.ParameterName = DAOReportResource.IniDate; parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date; parameter.Direction = ParameterDirection.Input; parameter.Value = initDate; command.Parameters.Add(parameter); parameter_2.ParameterName = DAOReportResource.EndDate; parameter_2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date; parameter_2.Direction = ParameterDirection.Input; parameter_2.Value = endDate; command.Parameters.Add(parameter_2); command.CommandType = CommandType.StoredProcedure; NpgsqlDataReader dr = command.ExecuteReader(); try { while (dr.Read()) { id = dr.GetInt32(0); paymentDate = dr.GetDateTime(1); expirationDate = dr.GetDateTime(2); paypalTransactionId = dr.GetString(3); amount = dr.GetDouble(4); int remaingClasses = dr.GetInt32(5); int sectionId = dr.GetInt32(6); String studentId = dr.GetString(7); section = new Section(); section.Id = sectionId; student = new Student(); student.Email = studentId; readPayment = new AraPayment(id, paymentDate, expirationDate, paypalTransactionId, amount, remaingClasses, section, student); payments.Add(readPayment); } dr.Close(); tran.Commit(); return(payments); } catch (Exception ex) { throw ex; } } catch (NpgsqlException ex2) { throw ex2; } finally { conn.Close(); } }
// 分銷商註冊 public static void InsCompany(RegisterModel reg) { NpgsqlConnection conn = new NpgsqlConnection(Website.Instance.SqlConnectionString); NpgsqlTransaction trans = null; try { conn.Open(); trans = conn.BeginTransaction(); string sqlStmt = @"INSERT INTO b2b.b2d_company( status, comp_coop_mode, payment_type, manager_account_xid, comp_name, comp_url, comp_license, comp_license_2, comp_country, comp_locale, comp_currency, comp_invoice, comp_tel_country_code, comp_tel, contact_user_email, comp_address, charge_man_first, charge_man_last, contact_user, crt_user, crt_datetime, charge_man_gender, comp_timezone) VALUES (:status, :comp_coop_mode, :payment_type, :manager_account_xid, :comp_name, :comp_url, :comp_license, :comp_license_2, :comp_country, :comp_locale, :comp_currency, :comp_invoice, :comp_tel_country_code, :comp_tel, :contact_user_email, :comp_address, :charge_man_first, :charge_man_last, :contact_user, :crt_user, now(), :charge_man_gender, :comp_timezone); SELECT currval('b2b.b2d_company_xid_seq') AS new_comp_xid ; "; NpgsqlParameter[] sqlParams = new NpgsqlParameter[] { new NpgsqlParameter("status", "00"), //審核狀態(00已申請/01審核中/02待補件/03已核准/04未核准) new NpgsqlParameter("comp_coop_mode", "02"), //合作方式(00全開/01串接API/02Web平台) new NpgsqlParameter("payment_type", "01"), //付款方式(01逐筆結/02額度付款) new NpgsqlParameter("manager_account_xid", 1), new NpgsqlParameter("comp_name", reg.COMPANY_NAME), new NpgsqlParameter("comp_url", reg.URL), new NpgsqlParameter("comp_license", reg.LICENCSE_1), new NpgsqlParameter("comp_license_2", reg.LICENCSE_2), new NpgsqlParameter("comp_locale", reg.LOCALE), new NpgsqlParameter("comp_currency", reg.CURRENCY), new NpgsqlParameter("comp_invoice", reg.INVOICE), new NpgsqlParameter("comp_country", reg.COUNTRY_CODE), new NpgsqlParameter("comp_tel_country_code", reg.TEL_CODE), new NpgsqlParameter("comp_tel", reg.TEL), new NpgsqlParameter("contact_user_email", reg.EMAIL), new NpgsqlParameter("comp_address", reg.ADDRESS), new NpgsqlParameter("charge_man_first", reg.NAME_FIRST), new NpgsqlParameter("charge_man_last", reg.NAME_LAST), new NpgsqlParameter("contact_user", string.Empty), new NpgsqlParameter("crt_user", "system"), new NpgsqlParameter("charge_man_gender", reg.GENDER_TITLE), new NpgsqlParameter("comp_timezone", Convert.ToInt32(reg.TIMEZONE)) }; var new_comp_xid = NpgsqlHelper.ExecuteScalar(trans, CommandType.Text, sqlStmt, sqlParams); var new_acc_xid = InsAccount(trans, reg, new_comp_xid); UpdManagerXid(trans, new_comp_xid, new_acc_xid); trans.Commit(); conn.Close(); } catch (Exception ex) { Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace); if (trans != null) { trans.Rollback(); } conn.Close(); throw ex; } }
/// <summary> /// Funcion: ObtenerUsuarioLogin /// Obtiene los datos del usuario que se valida. /// </summary> /// <param name="usuario">Usuario a Validar</param> /// <returns>Detalle del Usuario</returns> public BEUsuario ObtenerUsuarioLogin(string usuario) { BEUsuario oUsuario = new BEUsuario(); using (NpgsqlConnection ocn = new NpgsqlConnection(Util.getConnection())) { ocn.Open(); NpgsqlTransaction tran = ocn.BeginTransaction(); using (NpgsqlCommand ocmd = new NpgsqlCommand("public.func_obtener_doc_usuario_Login", ocn)) { ocmd.CommandType = CommandType.StoredProcedure; ocmd.Parameters.Add("@p_cod_usuario", NpgsqlDbType.Varchar).Value = usuario; using (NpgsqlDataReader odr = ocmd.ExecuteReader()) { while (odr.Read()) { if (!Convert.IsDBNull(odr["cod_usuario"])) { oUsuario.cod_usuario = odr["cod_usuario"].ToString(); } if (!Convert.IsDBNull(odr["ape_paterno"])) { oUsuario.ape_paterno = odr["ape_paterno"].ToString(); } if (!Convert.IsDBNull(odr["ape_materno"])) { oUsuario.ape_materno = odr["ape_materno"].ToString(); } if (!Convert.IsDBNull(odr["nombres"])) { oUsuario.nombres = odr["nombres"].ToString(); } if (!Convert.IsDBNull(odr["cod_area"])) { oUsuario.cod_area = Convert.ToInt32(odr["cod_area"]); } if (!Convert.IsDBNull(odr["gls_area"])) { oUsuario.gls_area = odr["gls_area"].ToString(); } if (!Convert.IsDBNull(odr["cod_rol"])) { oUsuario.cod_rol = Convert.ToInt32(odr["cod_rol"]); } if (!Convert.IsDBNull(odr["gls_rol"])) { oUsuario.gls_rol = odr["gls_rol"].ToString(); } if (!Convert.IsDBNull(odr["correo"])) { oUsuario.correo = odr["correo"].ToString(); } } odr.Close(); } } tran.Commit(); ocn.Close(); } return(oUsuario); }
public List <Student> ReadAllStudentBySectionBetweenDates(DateTime initDate, DateTime endDate, Section section) { conn = DAO.getConnection(); Student readStudent = new Student(); List <Student> students = new List <Student>(); String email; String password; String name; String lastName; DateTime birthDate; String phone; String country; int gradeId; String gradeName; Char status; try { conn = DAO.getConnection(); NpgsqlTransaction tran = conn.BeginTransaction(); NpgsqlCommand command = new NpgsqlCommand(DAOReportResource.ReadStudentsBySectionPeriodSP, conn); NpgsqlParameter parameter = new NpgsqlParameter(); NpgsqlParameter parameter_2 = new NpgsqlParameter(); NpgsqlParameter parameter_3 = new NpgsqlParameter(); parameter.ParameterName = DAOReportResource.IniDate; parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date; parameter.Direction = ParameterDirection.Input; parameter.Value = initDate; command.Parameters.Add(parameter); parameter_2.ParameterName = DAOReportResource.EndDate; parameter_2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date; parameter_2.Direction = ParameterDirection.Input; parameter_2.Value = endDate; command.Parameters.Add(parameter_2); parameter_3.ParameterName = DAOReportResource.Id; parameter_3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer; parameter_3.Direction = ParameterDirection.Input; parameter_3.Value = section.Id; command.Parameters.Add(parameter_3); command.CommandType = CommandType.StoredProcedure; NpgsqlDataReader dr = command.ExecuteReader(); try { while (dr.Read()) { email = dr.GetString(0); password = dr.GetString(1); name = dr.GetString(2); lastName = dr.GetString(3); birthDate = dr.GetDateTime(4); phone = dr.GetString(5); country = dr.GetString(6); status = dr.GetChar(7); //gradeId = dr.GetInt32(8); //gradeName = dr.GetString(9); Grade grade = new Grade(); readStudent = new Student(email, password, name, lastName, birthDate, phone, country, grade); readStudent.Status = status; students.Add(readStudent); } dr.Close(); tran.Commit(); return(students); } catch (Exception ex) { throw ex; } } catch (NpgsqlException ex2) { throw ex2; } finally { conn.Close(); } }
public bool AddSale(string from_station, string to_station, int route_id, int traveller_id, short amount, string ticket_name) { conn.Open(); NpgsqlTransaction transaction = conn.BeginTransaction(); try { RouteSubrouteRepository routeSubrouteRepository = new RouteSubrouteRepository(); List <RouteSubroute> routeParts = routeSubrouteRepository.GetRoutePart(route_id, from_station, to_station); foreach (var routePart in routeParts) { if (routePart.seats_amount <= 0) { throw new Exception(); } } int saleId = NextId(); Sale sale = new Sale(); sale.id = saleId; sale.payment_status = true; sale.sale_date = DateTime.Now; sale.to_station = to_station; sale.from_station = from_station; sale.route_id = route_id; sale.traveller_id = traveller_id; NpgsqlCommand addSale = new NpgsqlCommand(" insert into sale " + "(id, payment_status, from_station, to_station, route_id, traveller_id) " + "values(:id,:payment_status, :from_station, :to_station, :route_id, :traveller_id); ", conn); var id_db_sale = new NpgsqlParameter(":id", DbType.Int32); id_db_sale.Value = sale.id; addSale.Parameters.Add(id_db_sale); var payment_status_db = new NpgsqlParameter(":payment_status", DbType.Boolean); payment_status_db.Value = sale.payment_status; addSale.Parameters.Add(payment_status_db); var to_station_db = new NpgsqlParameter(":to_station", DbType.String); to_station_db.Value = sale.to_station; addSale.Parameters.Add(to_station_db); var from_station_db = new NpgsqlParameter(":from_station", DbType.String); from_station_db.Value = sale.from_station; addSale.Parameters.Add(from_station_db); var route_id_db = new NpgsqlParameter(":route_id", DbType.Int32); route_id_db.Value = sale.route_id; addSale.Parameters.Add(route_id_db); var traveller_id_db = new NpgsqlParameter(":traveller_id", DbType.Int32); traveller_id_db.Value = sale.traveller_id; addSale.Parameters.Add(traveller_id_db); addSale.Prepare(); //throw new Exception(); <-- TEST NpgsqlCommand selectTicket = new NpgsqlCommand("SELECT id FROM ticket" + " WHERE name=:ticket_name", conn); var ticket_name_db = new NpgsqlParameter(":ticket_name", DbType.String); ticket_name_db.Value = ticket_name; selectTicket.Parameters.Add(ticket_name_db); selectTicket.Prepare(); int ticketId = (int)selectTicket.ExecuteScalar(); SaleTicket saleTicket = new SaleTicket { sale_id = saleId, ticket_id = ticketId, amount = amount }; NpgsqlCommand addSaleTicket = new NpgsqlCommand("insert into sale_ticket (amount,sale_id,ticket_id) " + " values(:amount, :sale_id, :ticket_id); ", conn); var amount_db = new NpgsqlParameter(":amount", DbType.Int32); amount_db.Value = saleTicket.amount; addSaleTicket.Parameters.Add(amount_db); var sale_id_db = new NpgsqlParameter(":sale_id", DbType.Int32); sale_id_db.Value = saleTicket.sale_id; addSaleTicket.Parameters.Add(sale_id_db); var ticket_id_db = new NpgsqlParameter(":ticket_id", DbType.Int32); ticket_id_db.Value = saleTicket.ticket_id; addSaleTicket.Parameters.Add(ticket_id_db); addSaleTicket.Prepare(); int rowsAddedToSale = addSale.ExecuteNonQuery(); int rowsAddedToTicketSale = addSaleTicket.ExecuteNonQuery(); for (int i = 0; i < routeParts.Count; i++) { routeParts[i].seats_amount -= 1; NpgsqlCommand updateRouteSubroute = new NpgsqlCommand("update route_subroute set seats_amount = :seats_amount " + "where id = :id;", conn); var seats_amount_db = new NpgsqlParameter(":seats_amount", DbType.Int32); seats_amount_db.Value = routeParts[i].seats_amount; updateRouteSubroute.Parameters.Add(seats_amount_db); var id_db = new NpgsqlParameter(":id", DbType.Int32); id_db.Value = routeParts[i].id; updateRouteSubroute.Parameters.Add(id_db); updateRouteSubroute.Prepare(); int rowsUpdated = updateRouteSubroute.ExecuteNonQuery(); } } catch (Exception ex) { transaction.Rollback(); conn.Close(); return(false); } transaction.Commit(); conn.Close(); return(true); }
/// <summary> /// Método para actualizar un Rol en la base de datos. /// </summary> /// <param name="infoRol"></param> /// <returns></returns> public MensajesRoles ActualizacionRol(Roles infoRol) { string nombreRol = string.Empty; MensajesRoles msjRoles = new MensajesRoles(); try { NpgsqlTransaction tran = conn_BD.BeginTransaction(); if (infoRol.NombreRolAntiguo != null) { nombreRol = infoRol.NombreRol; using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format("ALTER GROUP {0} RENAME TO {1};", infoRol.NombreRolAntiguo, infoRol.NombreRol), conn_BD)) { cmd.ExecuteNonQuery(); } } else { nombreRol = infoRol.NombreRol; } using (NpgsqlCommand cmd = new NpgsqlCommand("UPDATE public.dcicc_roles SET nombre_rol=@nr, activos_rol=@pa, maquinasvirtuales_rol=@pm, tickets_rol=@pt, reportes_rol=@pr, descripcion_rol=@dr, habilitado_rol=@hr WHERE id_rol=@ir;", conn_BD)) { cmd.Parameters.Add("nr", NpgsqlTypes.NpgsqlDbType.Varchar).Value = nombreRol.ToLower().Trim(); cmd.Parameters.Add("pa", NpgsqlTypes.NpgsqlDbType.Boolean).Value = infoRol.PermisoActivos; cmd.Parameters.Add("pm", NpgsqlTypes.NpgsqlDbType.Boolean).Value = infoRol.PermisoMaqVirtuales; cmd.Parameters.Add("pt", NpgsqlTypes.NpgsqlDbType.Boolean).Value = infoRol.PermisoTickets; cmd.Parameters.Add("pr", NpgsqlTypes.NpgsqlDbType.Boolean).Value = infoRol.PermisoReportes; cmd.Parameters.Add("dr", NpgsqlTypes.NpgsqlDbType.Varchar).Value = !string.IsNullOrEmpty(infoRol.DescripcionRol) ? (object)infoRol.DescripcionRol.Trim() : DBNull.Value; cmd.Parameters.Add("hr", NpgsqlTypes.NpgsqlDbType.Boolean).Value = infoRol.HabilitadoRol; cmd.Parameters.Add("ir", NpgsqlTypes.NpgsqlDbType.Integer).Value = infoRol.IdRol; cmd.ExecuteNonQuery(); } foreach (var item in sentencias_Revocacion) { using (NpgsqlCommand cmd = new NpgsqlCommand(item, conn_BD)) { cmd.ExecuteNonQuery(); } } foreach (var item in sentencias_Generales) { using (NpgsqlCommand cmd = new NpgsqlCommand(item, conn_BD)) { cmd.ExecuteNonQuery(); } } if (infoRol.PermisoActivos) { foreach (var item in sentencias_Activos) { using (NpgsqlCommand cmd = new NpgsqlCommand(item, conn_BD)) { cmd.ExecuteNonQuery(); } } } if (infoRol.PermisoMaqVirtuales) { foreach (var item in sentencias_MaqVirtuales) { using (NpgsqlCommand cmd = new NpgsqlCommand(item, conn_BD)) { cmd.ExecuteNonQuery(); } } } if (infoRol.PermisoTickets) { foreach (var item in sentencias_Tickets) { using (NpgsqlCommand cmd = new NpgsqlCommand(item, conn_BD)) { cmd.ExecuteNonQuery(); } } } if (infoRol.PermisoReportes) { foreach (var item in sentencias_Reportes) { using (NpgsqlCommand cmd = new NpgsqlCommand(item, conn_BD)) { cmd.ExecuteNonQuery(); } } } tran.Commit(); conn_BD.Close(); msjRoles.OperacionExitosa = true; } catch (Exception e) { conn_BD.Close(); msjRoles.OperacionExitosa = false; msjRoles.MensajeError = e.Message; } return(msjRoles); }
public void BeginTransactionBeforeOpen() { using (var conn = new NpgsqlConnection()) Assert.That(() => conn.BeginTransaction(), Throws.Exception.TypeOf <InvalidOperationException>()); }
public DapperFixture() { Connection = new NpgsqlConnection(Configuration["DbConnection"]); Connection.Open(); Transaction = Connection.BeginTransaction(); }
// // RoleProvider.RemoveUsersFromRoles // public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames) { foreach (string rolename in rolenames) { if (!RoleExists(rolename)) { throw new ProviderException("Role name not found."); } } foreach (string username in usernames) { foreach (string rolename in rolenames) { if (!IsUserInRole(username, rolename)) { throw new ProviderException("User is not in role."); } } } NpgsqlConnection conn = new NpgsqlConnection(connectionString); NpgsqlCommand cmd = new NpgsqlCommand( "DELETE FROM " + usersInRolesTable + "" + " WHERE user_name = @user_name AND role_name = @role_name AND application_name = @application_name", conn); NpgsqlParameter userParm = cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255); NpgsqlParameter roleParm = cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255); cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName; NpgsqlTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; foreach (string username in usernames) { foreach (string rolename in rolenames) { userParm.Value = username; roleParm.Value = rolename; cmd.ExecuteBlind(); } } tran.Commit(); } catch (NpgsqlException e) { try { if (tran != null) { tran.Rollback(); } } catch { } if (WriteExceptionsToEventLog) { WriteToEventLog(e, "RemoveUsersFromRoles"); } else { throw e; } } finally { cmd.Dispose(); conn.Close(); } }
public JsonResponse<string> updateToApproveProfile(ApprovedImageModel approveModel) { JsonResponse<string> jsonResponse = new JsonResponse<string>(); string folderName; DirectoryInfo di; try { using (IDbConnection dbConnection = new NpgsqlConnection(_ConnectionStringService.Value)) { dbConnection.Open(); using (var transaction = dbConnection.BeginTransaction()) { CandidateImageLogger imageDetails = dbConnection.Query<CandidateImageLogger>("select * from candidate_image_logger where id = @p0", new { p0 = approveModel.imageLoggedId }).FirstOrDefault(); if (imageDetails.is_profile_pic == true) { folderName = Path.Combine("Resources", "Images", "User", imageDetails.user_id.ToString(), "Candidates", imageDetails.candidate_id.ToString(), "Profile Image"); var path = Path.Combine(Directory.GetCurrentDirectory(), folderName); if (Directory.Exists(path)) { di = new DirectoryInfo(path); foreach (FileInfo fi in di.GetFiles()) { if (fi.Name != imageDetails.image_name) { fi.Delete(); } } } dbConnection.Query<CandidateImageLogger>("update candidate_image_logger set is_deleted = true where user_id = @p0 and candidate_id = @p1 and is_approved = true and is_profile_pic = true and is_deleted = false and id != @p2", new { p0 = imageDetails.user_id, @p1 = imageDetails.candidate_id, @p2 = imageDetails.id }); dbConnection.Query<CandidateImageLogger>("update candidate_image_logger set is_approved = true , is_profile_pic = true where id = @p0", new { p0 = approveModel.imageLoggedId }); } else if (imageDetails.is_from_other_three_photos == true) { dbConnection.Query<CandidateImageLogger>("update candidate_image_logger set is_approved = true where id = @p0", new { p0 = approveModel.imageLoggedId }); } transaction.Commit(); } } jsonResponse.IsSuccess = true; jsonResponse.Message = "success"; return jsonResponse; } catch (Exception e) { jsonResponse.IsSuccess = false; jsonResponse.Message = "fail"; return jsonResponse; } }
/// <summary> /// Listado de usuarios /// </summary> /// <returns>Lista de usuarios</returns> public List <BEUsuario> ListarUsuarios() { List <BEUsuario> oListado = new List <BEUsuario>(); BEUsuario oItem; using (NpgsqlConnection cnx = new NpgsqlConnection(Util.getConnection())) { cnx.Open(); NpgsqlTransaction tran = cnx.BeginTransaction(); using (NpgsqlCommand cmd = new NpgsqlCommand("public.func_listar_doc_usuarios", cnx)) { cmd.CommandType = CommandType.StoredProcedure; using (NpgsqlDataReader odr = cmd.ExecuteReader()) { while (odr.Read()) { oItem = new BEUsuario(); if (!Convert.IsDBNull(odr["cod_usuario"])) { oItem.cod_usuario = Convert.ToString(odr["cod_usuario"]); } if (!Convert.IsDBNull(odr["ape_paterno"])) { oItem.ape_paterno = Convert.ToString(odr["ape_paterno"]); } if (!Convert.IsDBNull(odr["ape_materno"])) { oItem.ape_materno = Convert.ToString(odr["ape_materno"]); } if (!Convert.IsDBNull(odr["nombres"])) { oItem.nombres = Convert.ToString(odr["nombres"]); } if (!Convert.IsDBNull(odr["correo"])) { oItem.correo = Convert.ToString(odr["correo"]); } if (!Convert.IsDBNull(odr["cod_area"])) { oItem.cod_area = Convert.ToInt32(odr["cod_area"]); } if (!Convert.IsDBNull(odr["gls_area"])) { oItem.gls_area = Convert.ToString(odr["gls_area"]); } if (!Convert.IsDBNull(odr["cod_rol"])) { oItem.cod_rol = Convert.ToInt32(odr["cod_rol"]); } if (!Convert.IsDBNull(odr["gls_rol"])) { oItem.gls_rol = Convert.ToString(odr["gls_rol"]); } if (!Convert.IsDBNull(odr["cod_estado_registro"])) { oItem.cod_estado_registro = Convert.ToInt32(odr["cod_estado_registro"]); } if (!Convert.IsDBNull(odr["aud_usr_ingreso"])) { oItem.aud_usr_ingreso = odr["aud_usr_ingreso"].ToString(); } if (!Convert.IsDBNull(odr["aud_fec_ingreso"])) { oItem.aud_fec_ingreso = Convert.ToDateTime(odr["aud_fec_ingreso"]); } oListado.Add(oItem); } odr.Close(); } } tran.Commit(); cnx.Close(); return(oListado); } }
// // RoleProvider.DeleteRole // public override bool DeleteRole(string rolename, bool throwOnPopulatedRole) { if (!RoleExists(rolename)) { throw new ProviderException("Role does not exist."); } if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0) { throw new ProviderException("Cannot delete a populated role."); } NpgsqlConnection conn = new NpgsqlConnection(connectionString); NpgsqlCommand cmd = new NpgsqlCommand( "DELETE FROM " + rolesTable + "" + " WHERE role_name = @role_name AND application_name = @application_name", conn); cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename; cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName; NpgsqlCommand cmd2 = new NpgsqlCommand( "DELETE FROM " + usersInRolesTable + "" + " WHERE role_name = @role_name AND application_name = @application_name", conn); cmd2.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename; cmd2.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName; NpgsqlTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; cmd2.Transaction = tran; cmd2.ExecuteBlind(); cmd.ExecuteBlind(); tran.Commit(); } catch (NpgsqlException e) { try { if (tran != null) { tran.Rollback(); } } catch { } if (WriteExceptionsToEventLog) { WriteToEventLog(e, "DeleteRole"); return false; } else { throw e; } } finally { cmd.Dispose(); conn.Close(); } return true; }
public int CreateSection(Section section) { conn = DAO.getConnection(); NpgsqlCommand command = new NpgsqlCommand(DAOSectionResource.CreateSectionSP, conn); NpgsqlTransaction transaction = conn.BeginTransaction(); NpgsqlParameter name = new NpgsqlParameter(); NpgsqlParameter amount = new NpgsqlParameter(); NpgsqlParameter description = new NpgsqlParameter(); NpgsqlParameter fkcourse = new NpgsqlParameter(); name.ParameterName = DAOSectionResource.Name; amount.ParameterName = DAOSectionResource.Amount; description.ParameterName = DAOSectionResource.Description; fkcourse.ParameterName = DAOSectionResource.CourseID; name.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar; amount.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Double; description.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar; fkcourse.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar; name.Direction = ParameterDirection.Input; amount.Direction = ParameterDirection.Input; description.Direction = ParameterDirection.Input; fkcourse.Direction = ParameterDirection.Input; name.Value = section.Name; amount.Value = section.Amount; description.Value = section.Description; fkcourse.Value = section.Course.Id; command.Parameters.Add(name); command.Parameters.Add(amount); command.Parameters.Add(description); command.Parameters.Add(fkcourse); command.CommandType = CommandType.StoredProcedure; int response = 500; NpgsqlDataReader dr = command.ExecuteReader(); try { while (dr.Read()) { response = dr.GetInt32(0); } dr.Close(); transaction.Commit(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(response); }
public List <ClassMeeting> ReadClassByTeacherBetweenDates(DateTime initDate, DateTime endDate, Teacher teacher) { conn = DAO.getConnection(); ClassMeeting meeting = new ClassMeeting(); List <ClassMeeting> meetings = new List <ClassMeeting>(); String id; String uuId; String hostId; String hostEmail; String topic; int type; String status; DateTime startTime; int duration; String timezone; String agenda; DateTime createdAt; String startUrl; String joinUrl; String password; String h323Password; String pstnPassword; String encryptedPassword; String comment; Section section; try { conn = DAO.getConnection(); NpgsqlTransaction tran = conn.BeginTransaction(); NpgsqlCommand command = new NpgsqlCommand(DAOReportResource.ReadClassByTeacherPeriodSP, conn); NpgsqlParameter parameter = new NpgsqlParameter(); NpgsqlParameter parameter_2 = new NpgsqlParameter(); NpgsqlParameter parameter_3 = new NpgsqlParameter(); parameter.ParameterName = DAOReportResource.IniDate; parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date; parameter.Direction = ParameterDirection.Input; parameter.Value = initDate; command.Parameters.Add(parameter); parameter_2.ParameterName = DAOReportResource.EndDate; parameter_2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date; parameter_2.Direction = ParameterDirection.Input; parameter_2.Value = endDate; command.Parameters.Add(parameter_2); parameter_3.ParameterName = DAOReportResource.Id; parameter_3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar; parameter_3.Direction = ParameterDirection.Input; parameter_3.Value = teacher.Email; command.Parameters.Add(parameter_3); command.CommandType = CommandType.StoredProcedure; NpgsqlDataReader dr = command.ExecuteReader(); try { while (dr.Read()) { id = dr.GetString(0); uuId = dr.GetString(1); hostId = dr.GetString(2); hostEmail = dr.GetString(3); topic = dr.GetString(4); type = dr.GetInt32(5); status = dr.GetString(6); startTime = dr.GetDateTime(7); duration = dr.GetInt32(8); timezone = dr.GetString(9); agenda = dr.GetString(10); createdAt = dr.GetDateTime(11); startUrl = dr.GetString(12); joinUrl = dr.GetString(13); password = dr.GetString(14); h323Password = dr.GetString(15); pstnPassword = dr.GetString(16); encryptedPassword = dr.GetString(17); comment = dr.GetString(18); int sectionId = dr.GetInt32(19); String sectionName = dr.GetString(20); String teacherId = dr.GetString(21); String teacherName = dr.GetString(22); String teacherLastname = dr.GetString(23); section = new Section(); teacher = new Teacher(); section.Id = sectionId; section.Name = sectionName; teacher.Email = teacherId; teacher.Name = teacherName; teacher.LastName = teacherLastname; meeting = new ClassMeeting(id, uuId, hostId, hostEmail, topic, type, status, startTime, duration, timezone, agenda, createdAt, startUrl, joinUrl, password, h323Password, pstnPassword, encryptedPassword, comment, section, teacher); meetings.Add(meeting); } dr.Close(); tran.Commit(); return(meetings); } catch (Exception ex) { throw ex; } } catch (NpgsqlException ex2) { throw ex2; } finally { conn.Close(); } throw new NotImplementedException(); }
public void RegisterUserAsync(User user, Profile profile) { Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Entrance, $"user: {user} - profile: {profile}"); Check.NotNull(user, nameof(user)); Check.NotNull(profile, nameof(profile)); using (var connection_ = new NpgsqlConnection(RepoOptions.PgsqlPassword)) { Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"openning connection to database"); connection_.Open(); Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"openned connection to database"); var transaction = connection_.BeginTransaction(); try { var createUserCommand = CreateStoreProceduceCmd("CALL create_user(@email_val, @password_val, @new_user_id, @error_message)"); createUserCommand.Connection = connection_; var emailParam = createUserCommand.Parameters.AddWithValue("email_val", user.Email); emailParam.Direction = System.Data.ParameterDirection.Input; var pwdParam = createUserCommand.Parameters.AddWithValue("password_val", NpgsqlTypes.NpgsqlDbType.Varchar, user.Password); pwdParam.Direction = System.Data.ParameterDirection.Input; var uidParam = createUserCommand.Parameters.AddWithValue("new_user_id", 0); uidParam.Direction = System.Data.ParameterDirection.InputOutput; var errorMessageParam = createUserCommand.Parameters.AddWithValue("error_message", ""); errorMessageParam.Direction = System.Data.ParameterDirection.InputOutput; /** * user_id_val profiles.user_id%TYPE,IN occupation_val profiles.occupation%TYPE, */ var createProfileCommand = CreateStoreProceduceCmd("CALL create_user_profile(@user_id_val,@occupation_val,@first_name_val,@last_name_val,@birth_val,@gender_val,@marital_status_val,@profile_id_val,@error_message)"); createProfileCommand.Connection = connection_; var occupationParam = createProfileCommand.Parameters.AddWithValue("occupation_val", profile.Occupation); occupationParam.Direction = System.Data.ParameterDirection.Input; var firstNameParam = createProfileCommand.Parameters.AddWithValue("first_name_val", profile.FirstName); firstNameParam.Direction = System.Data.ParameterDirection.Input; var lastNameParam = createProfileCommand.Parameters.AddWithValue("last_name_val", profile.LastName); lastNameParam.Direction = System.Data.ParameterDirection.Input; var birthParam = createProfileCommand.Parameters.AddWithValue("birth_val", NpgsqlTypes.NpgsqlDbType.Date, profile.Birth); birthParam.Direction = System.Data.ParameterDirection.Input; var genderParam = createProfileCommand.Parameters.AddWithValue("gender_val", profile.Gender.ToString()); genderParam.Direction = System.Data.ParameterDirection.Input; var maritalStatusParam = createProfileCommand.Parameters.AddWithValue("marital_status_val", profile.MaritalStatus.ToString()); maritalStatusParam.Direction = System.Data.ParameterDirection.Input; Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"executing create user command."); createUserCommand.ExecuteNonQuery(); Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"executed create user command."); var createdUserId = uidParam.Value; var errorMessage = errorMessageParam.Value?.ToString().Trim(); Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"createdUserId: {createdUserId},errorMessage {errorMessage} "); if (createdUserId == null || createdUserId.ToString() == "0" || errorMessage.Length > 0) { Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"Rolling back transaction and throwing exception"); throw new BackendException(Convert.ToInt32(HttpStatusCode.BadRequest), errorMessage); } var userIdParam = createProfileCommand.Parameters.AddWithValue("user_id_val", createdUserId); userIdParam.Direction = System.Data.ParameterDirection.Input; var profileIdParam = createProfileCommand.Parameters.AddWithValue("profile_id_val", 0); profileIdParam.Direction = System.Data.ParameterDirection.InputOutput; var errorMessageParam2 = createProfileCommand.Parameters.AddWithValue("error_message", ""); errorMessageParam2.Direction = System.Data.ParameterDirection.InputOutput; Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"executing create profile command."); createProfileCommand.ExecuteNonQuery(); Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"executed create profile command."); var createdProfileId = uidParam.Value; errorMessage = errorMessageParam.Value?.ToString().Trim(); Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"createdProfileId: {createdProfileId},errorMessage {errorMessage} "); if (createdProfileId == null || createdProfileId.ToString() == "0" || errorMessage.Length > 0) { Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"Rolling back transaction and throwing exception"); transaction.Rollback(); throw new BackendException(Convert.ToInt32(HttpStatusCode.BadRequest), errorMessage); } transaction.Commit(); } catch (Exception exception) { Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Body, $"Exception {exception}", LogLevel.Warning); transaction.Rollback(); if (exception is BackendException) { throw exception; } throw new BackendException(Convert.ToInt32(HttpStatusCode.InternalServerError), exception.Message); } } Check.CallerLog <SecurityRepository>(Logger, LoggerExecutionPositions.Exit); }
public List <Teacher> CalculateTeacherSalaryBetweenDates(DateTime initDate, DateTime endDate) { conn = DAO.getConnection(); ClassMeeting meeting = new ClassMeeting(); int salary = -1; Teacher teacher = new Teacher(); List <Teacher> teachers = new List <Teacher>(); String id; String uuId; String hostId; String hostEmail; String topic; int type; String status; DateTime startTime; int duration; String timezone; String agenda; DateTime createdAt; String startUrl; String joinUrl; String password; String h323Password; String pstnPassword; String encryptedPassword; String comment; Section section; try { conn = DAO.getConnection(); NpgsqlTransaction tran = conn.BeginTransaction(); NpgsqlCommand command = new NpgsqlCommand(DAOReportResource.TotalHoursTeacher, conn); NpgsqlParameter parameter = new NpgsqlParameter(); NpgsqlParameter parameter_2 = new NpgsqlParameter(); NpgsqlParameter parameter_3 = new NpgsqlParameter(); parameter.ParameterName = DAOReportResource.IniDate; parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date; parameter.Direction = ParameterDirection.Input; parameter.Value = initDate; command.Parameters.Add(parameter); parameter_2.ParameterName = DAOReportResource.EndDate; parameter_2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date; parameter_2.Direction = ParameterDirection.Input; parameter_2.Value = endDate; command.Parameters.Add(parameter_2); command.CommandType = CommandType.StoredProcedure; NpgsqlDataReader dr = command.ExecuteReader(); try { while (dr.Read()) { teacher = new Teacher(); teacher.Salary = dr.GetInt32(0); teacher.Email = dr.GetString(1); teacher.Name = dr.GetString(2); teacher.LastName = dr.GetString(3); teachers.Add(teacher); } dr.Close(); tran.Commit(); return(teachers); } catch (Exception ex) { throw ex; } } catch (NpgsqlException ex2) { throw ex2; } finally { conn.Close(); } throw new NotImplementedException(); }
/// <summary> /// Create an asset in database, or update it if existing. /// </summary> /// <param name="asset">Asset UUID to create</param> /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks> public void StoreAsset(AssetBase asset) { // m_log.DebugFormat("[XASSETS DB]: Storing asset {0} {1}", asset.Name, asset.ID); lock (m_dbLock) { using (NpgsqlConnection dbcon = new NpgsqlConnection(m_connectionString)) { dbcon.Open(); using (NpgsqlTransaction transaction = dbcon.BeginTransaction()) { string assetName = asset.Name; if (asset.Name.Length > 64) { assetName = asset.Name.Substring(0, 64); m_log.WarnFormat( "[XASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add", asset.Name, asset.ID, asset.Name.Length, assetName.Length); } string assetDescription = asset.Description; if (asset.Description.Length > 64) { assetDescription = asset.Description.Substring(0, 64); m_log.WarnFormat( "[XASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add", asset.Description, asset.ID, asset.Description.Length, assetDescription.Length); } if (m_enableCompression) { MemoryStream outputStream = new MemoryStream(); using (GZipStream compressionStream = new GZipStream(outputStream, CompressionMode.Compress, false)) { // Console.WriteLine(WebUtil.CopyTo(new MemoryStream(asset.Data), compressionStream, int.MaxValue)); // We have to close the compression stream in order to make sure it writes everything out to the underlying memory output stream. compressionStream.Close(); byte[] compressedData = outputStream.ToArray(); asset.Data = compressedData; } } byte[] hash = hasher.ComputeHash(asset.Data); UUID asset_id; UUID.TryParse(asset.ID, out asset_id); // m_log.DebugFormat( // "[XASSET DB]: Compressed data size for {0} {1}, hash {2} is {3}", // asset.ID, asset.Name, hash, compressedData.Length); try { using (NpgsqlCommand cmd = new NpgsqlCommand( @"insert INTO XAssetsMeta(id, hash, name, description, ""AssetType"", local, temporary, create_time, access_time, asset_flags, creatorid) Select :ID, :Hash, :Name, :Description, :AssetType, :Local, :Temporary, :CreateTime, :AccessTime, :AssetFlags, :CreatorID where not exists( Select id from XAssetsMeta where id = :ID); update XAssetsMeta set id = :ID, hash = :Hash, name = :Name, description = :Description, ""AssetType"" = :AssetType, local = :Local, temporary = :Temporary, create_time = :CreateTime, access_time = :AccessTime, asset_flags = :AssetFlags, creatorid = :CreatorID where id = :ID; ", dbcon)) { // create unix epoch time int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); cmd.Parameters.Add(m_database.CreateParameter("ID", asset_id)); cmd.Parameters.Add(m_database.CreateParameter("Hash", hash)); cmd.Parameters.Add(m_database.CreateParameter("Name", assetName)); cmd.Parameters.Add(m_database.CreateParameter("Description", assetDescription)); cmd.Parameters.Add(m_database.CreateParameter("AssetType", asset.Type)); cmd.Parameters.Add(m_database.CreateParameter("Local", asset.Local)); cmd.Parameters.Add(m_database.CreateParameter("Temporary", asset.Temporary)); cmd.Parameters.Add(m_database.CreateParameter("CreateTime", now)); cmd.Parameters.Add(m_database.CreateParameter("AccessTime", now)); cmd.Parameters.Add(m_database.CreateParameter("CreatorID", asset.Metadata.CreatorID)); cmd.Parameters.Add(m_database.CreateParameter("AssetFlags", (int)asset.Flags)); cmd.ExecuteNonQuery(); } } catch (Exception e) { m_log.ErrorFormat("[ASSET DB]: PGSQL failure creating asset metadata {0} with name \"{1}\". Error: {2}", asset.FullID, asset.Name, e.Message); transaction.Rollback(); return; } if (!ExistsData(dbcon, transaction, hash)) { try { using (NpgsqlCommand cmd = new NpgsqlCommand( @"INSERT INTO XAssetsData(hash, data) VALUES(:Hash, :Data)", dbcon)) { cmd.Parameters.Add(m_database.CreateParameter("Hash", hash)); cmd.Parameters.Add(m_database.CreateParameter("Data", asset.Data)); cmd.ExecuteNonQuery(); } } catch (Exception e) { m_log.ErrorFormat("[XASSET DB]: PGSQL failure creating asset data {0} with name \"{1}\". Error: {2}", asset.FullID, asset.Name, e.Message); transaction.Rollback(); return; } } transaction.Commit(); } } } }
private void alternative7() { string errorMsg = null; while (true) { Console.Clear(); Console.WriteLine("Creation of a new movie in the database.(write go back to go back)\n"); if (errorMsg != null) { Console.WriteLine("Error: " + errorMsg); } Console.Write("Please write the title:"); String title = Console.ReadLine(); if (title == "go back") { return; } Console.Write("Please write the year:"); String year = Console.ReadLine(); if (year == "go back") { return; } Console.Write("Please write the age restriction:"); String ageRestriction = Console.ReadLine(); if (ageRestriction == "go back") { return; } Console.Write("Please write it's price:"); String price = Console.ReadLine(); if (price == "go back") { return; } NpgsqlTransaction transaction = null; NpgsqlConnection connection = null; try { connection = new NpgsqlConnection(connection_string); connection.Open(); transaction = connection.BeginTransaction(); Movie movie = new Movie(title, Int32.Parse(year), Int32.Parse(ageRestriction), Int32.Parse(price)); movie.Save(); Copy copy = new Copy(true, movie.Id); copy.Save(); transaction.Commit(); } catch (Exception e) { errorMsg = e.Message; transaction?.Rollback(); continue; } finally { connection?.Close(); } Console.WriteLine("Victory! Movie was created!"); break; } while (true) { Console.Write("\n\nPress ESC to go back..."); if (Console.ReadKey().Key == ConsoleKey.Escape) { return; } } }
public int CreateOffer(Offer offer) { conn = DAO.getConnection(); NpgsqlCommand command = new NpgsqlCommand(DAOOfferResource.CreateOfferSP, conn); NpgsqlTransaction transaction = conn.BeginTransaction(); NpgsqlParameter name = new NpgsqlParameter(); NpgsqlParameter minArticles = new NpgsqlParameter(); NpgsqlParameter percentage = new NpgsqlParameter(); NpgsqlParameter initDate = new NpgsqlParameter(); NpgsqlParameter endDate = new NpgsqlParameter(); NpgsqlParameter description = new NpgsqlParameter(); name.ParameterName = DAOOfferResource.Name; minArticles.ParameterName = DAOOfferResource.MinArticles; percentage.ParameterName = DAOOfferResource.Percentage; initDate.ParameterName = DAOOfferResource.InitDate; endDate.ParameterName = DAOOfferResource.EndDate; description.ParameterName = DAOOfferResource.Description; name.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar; minArticles.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer; percentage.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer; initDate.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date; endDate.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date; description.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar; name.Direction = ParameterDirection.Input; minArticles.Direction = ParameterDirection.Input; percentage.Direction = ParameterDirection.Input; initDate.Direction = ParameterDirection.Input; endDate.Direction = ParameterDirection.Input; description.Direction = ParameterDirection.Input; name.Value = offer.Name; minArticles.Value = offer.MinArticles; percentage.Value = offer.Percentage; initDate.Value = offer.InitDate; endDate.Value = offer.EndDate; description.Value = offer.Description; command.Parameters.Add(name); command.Parameters.Add(minArticles); command.Parameters.Add(percentage); command.Parameters.Add(initDate); command.Parameters.Add(endDate); command.Parameters.Add(description); command.CommandType = CommandType.StoredProcedure; int response = 500; NpgsqlDataReader dr = command.ExecuteReader(); try { while (dr.Read()) { response = dr.GetInt32(0); } dr.Close(); transaction.Commit(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(response); }
public void IniciarTransaccion() { tran = conexion.BeginTransaction(); transOK = true; }
public Task <IDbTransaction> BeginTransactionAsync() => Task.FromResult <IDbTransaction>(_connection.BeginTransaction());
public static bool CreateArticle(Article info, List<Comment> comment) { int rlt = 0; DataTable dt = new DataTable(); NpgsqlConnection cnn = new NpgsqlConnection(PostGreSqlConnectionString); NpgsqlCommand cm = new NpgsqlCommand(); cm.Connection = cnn; cnn.Open(); DbTransaction trans = cnn.BeginTransaction(); try { string sql = @"insert into article (id, title, content, publish_time, syn_forum, syn_weixin, forum_id, media_id, create_time, creater_id, creater, hits, unit_id, status, last_modifier, modify_time, industry_id, industry_name, summary, is_publish, has_picture, comments, image_url,tags,search_tags,images,url) values (@id,@title,@content,@publish_time, @syn_forum, @syn_weixin, @forum_id, @media_id, @create_time, @creater_id, @creater, @hits, @unit_id, @status, @last_modifier, @modify_time, @industry_id, @industry_name, @summary, @is_publish, @has_picture, @comments, @image_url,@tags,@search_tags,@images,@url )"; //tags, source_cate_id @tags, @source_cate_id DbParameter[] parms = { PostgreHelper.MakeInParam("@id", NpgsqlDbType.Bigint, -1, info.Id), //PostgreHelper.MakeInParam("@source_art_id", NpgsqlDbType.Integer, -1, info.Source_Art_Id), //PostgreHelper.MakeInParam("@source_name", NpgsqlDbType.Varchar, 50, info.Source_Name), //PostgreHelper.MakeInParam("@source_url", NpgsqlDbType.Varchar, 500, info.Source_Url), PostgreHelper.MakeInParam("@title", NpgsqlDbType.Varchar, 255, info.Title), PostgreHelper.MakeInParam("@content", NpgsqlDbType.Text, -1, info.Content), PostgreHelper.MakeInParam("@publish_time", NpgsqlDbType.TimestampTZ, -1, info.Publish_Time), PostgreHelper.MakeInParam("@syn_forum", NpgsqlDbType.Boolean, -1, info.Syn_Forum), PostgreHelper.MakeInParam("@syn_weixin", NpgsqlDbType.Boolean, -1, info.Syn_Weixin), PostgreHelper.MakeInParam("@forum_id", NpgsqlDbType.Integer, -1, info.Forum_Id), PostgreHelper.MakeInParam("@media_id", NpgsqlDbType.Varchar, 80, info.Media_Id), PostgreHelper.MakeInParam("@create_time", NpgsqlDbType.TimestampTZ, -1, info.Create_Time), PostgreHelper.MakeInParam("@creater_id", NpgsqlDbType.Integer, -1, info.Creater_Id), PostgreHelper.MakeInParam("@creater", NpgsqlDbType.Varchar, 50, info.Creater), PostgreHelper.MakeInParam("@hits", NpgsqlDbType.Integer, -1, info.Hits), PostgreHelper.MakeInParam("@unit_id", NpgsqlDbType.Integer, -1, info.Unit_Id), PostgreHelper.MakeInParam("@status", NpgsqlDbType.Integer, -1, info.Status), PostgreHelper.MakeInParam("@last_modifier", NpgsqlDbType.Varchar, 50, info.Last_Modifier), PostgreHelper.MakeInParam("@modify_time", NpgsqlDbType.TimestampTZ, -1, info.Modify_Time), //PostgreHelper.MakeInParam("@is_del", NpgsqlDbType.Boolean, -1, info.Is_Del), //PostgreHelper.MakeInParam("@del_time", NpgsqlDbType.TimestampTZ, -1, info.Del_Time), PostgreHelper.MakeInParam("@industry_id", NpgsqlDbType.Integer, -1, info.Industry_Id), PostgreHelper.MakeInParam("@industry_name", NpgsqlDbType.Varchar, 20, info.Industry_Name), PostgreHelper.MakeInParam("@summary", NpgsqlDbType.Varchar, 500, info.Summary), PostgreHelper.MakeInParam("@is_publish", NpgsqlDbType.Boolean, -1, info.Is_Publish), PostgreHelper.MakeInParam("@has_picture", NpgsqlDbType.Boolean, -1, info.Has_Picture), PostgreHelper.MakeInParam("@comments", NpgsqlDbType.Integer, -1, info.Comments), PostgreHelper.MakeInParam("@image_url", NpgsqlDbType.Varchar, 500, info.Image_Url), PostgreHelper.MakeInParam("@tags", NpgsqlDbType.Array | NpgsqlDbType.Varchar, 20, info.Tags), PostgreHelper.MakeInParam("@search_tags", NpgsqlDbType.Array | NpgsqlDbType.Varchar , 20, info.Search_Tags), PostgreHelper.MakeInParam("@images", NpgsqlDbType.Array | NpgsqlDbType.Varchar , 500, info.Images), PostgreHelper.MakeInParam("@url", NpgsqlDbType.Varchar, 200, info.Url), //PostgreHelper.MakeInParam("@source_cate_id", NpgsqlDbType.Integer, -1,info.Source_cate_id), }; rlt = PostgreHelper.ExecuteNonQuery(trans, CommandType.Text, sql, parms); // return rlt > 0; //PostgreHelper.ExecuteNonQuery(PostGreSqlConnectionString, CommandType.Text, sql, parms); foreach (var info_comm in comment) { sql = @"insert into article_comment (article_id, comm_content, comm_username, comm_time, ip, auditor, audit_time, is_valid, shield_words, forum_topicid, forum_threadid, last_modifier, modify_time) values (@article_id, @comm_content, @comm_username, @comm_time, @ip, @auditor, @audit_time, @is_valid, @shield_words, @forum_topicid, @forum_threadid, @last_modifier, @modify_time)"; DbParameter[] parms1 = { PostgreHelper.MakeInParam("@article_id", NpgsqlDbType.Bigint, -1, info_comm.article_id), PostgreHelper.MakeInParam("@comm_content", NpgsqlDbType.Varchar, 200, info_comm.comm_content), PostgreHelper.MakeInParam("@comm_username", NpgsqlDbType.Varchar, 50, info_comm.comm_username), PostgreHelper.MakeInParam("@comm_time", NpgsqlDbType.TimestampTZ, -1, info_comm.comm_time), PostgreHelper.MakeInParam("@ip", NpgsqlDbType.Varchar, 15, info_comm.ip), PostgreHelper.MakeInParam("@auditor", NpgsqlDbType.Varchar, 50, info_comm.auditor), PostgreHelper.MakeInParam("@audit_time", NpgsqlDbType.TimestampTZ, -1, info_comm.audit_time), PostgreHelper.MakeInParam("@is_valid", NpgsqlDbType.Boolean, -1, info_comm.is_valid), PostgreHelper.MakeInParam("@shield_words", NpgsqlDbType.Varchar, 200, info_comm.shield_words), PostgreHelper.MakeInParam("@forum_topicid", NpgsqlDbType.Integer, -1, info_comm.forum_topicid), PostgreHelper.MakeInParam("@forum_threadid", NpgsqlDbType.Integer, -1, info_comm.forum_threadid), PostgreHelper.MakeInParam("@last_modifier", NpgsqlDbType.Varchar, 50, info_comm.last_modifier), PostgreHelper.MakeInParam("@modify_time", NpgsqlDbType.TimestampTZ, -1, info_comm.modify_time), }; rlt = PostgreHelper.ExecuteNonQuery(trans, CommandType.Text, sql, parms1); } trans.Commit(); } catch(Exception ex) { throw; rlt = 0; trans.Rollback(); } finally { cnn.Close(); trans.Dispose(); cnn.Dispose(); } return rlt > 0; }
public bool SaveInsurenceConfigDetails(InsurenceConfigDTO InsurenceConfigDetails, string ConnectionString) { StringBuilder Sbinsert = new StringBuilder(); StringBuilder QueryUpdate = new StringBuilder(); bool IsSaved = false; string Recordid = string.Empty; try { con = new NpgsqlConnection(ConnectionString); if (con.State != ConnectionState.Open) { con.Open(); } trans = con.BeginTransaction(); if (InsurenceConfigDetails.lstInsurenceConfigartionDetails != null) { for (int i = 0; i < InsurenceConfigDetails.lstInsurenceConfigartionDetails.Count; i++) { if (InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pTypeofOperation.Trim().ToUpper() != "CREATE") { if (string.IsNullOrEmpty(Recordid)) { Recordid = InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].precordid.ToString(); } else { Recordid = Recordid + "," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].precordid.ToString(); } } if (InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pTypeofOperation.Trim().ToUpper() == "CREATE") { Sbinsert.Append("insert into tblmstinsuranceconfigdetails(insuranceschemeconfigid,insuranceschemename,membertypeid,membertype,applicanttype,agefrom,ageto,insuranceduration,premiumamountpayable,premiumpayin,insuranceclaimpayoutevent,insuranceclaimamount,premiumrefund,statusid,createdby,createddate)VALUES(" + InsurenceConfigDetails.pInsurenceconfigid + ",'" + ManageQuote(InsurenceConfigDetails.pInsurencename) + "'," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pMembertypeid + ",'" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pMembertype) + "','" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pApplicanttype) + "'," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pAgefrom + "," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pAgeto + ",'" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceduration) + "'," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumamountpayable + ",'" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumpayin) + "','" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceclaimpayoutevent) + "'," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceclaimamount + "," + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumrefund + "," + Convert.ToInt32(Status.Active) + "," + InsurenceConfigDetails.pCreatedby + ",current_timestamp);"); } else if (InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pTypeofOperation.Trim().ToUpper() == "UPDATE") { Sbinsert.Append("update tblmstinsuranceconfigdetails set membertypeid=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pMembertypeid + ",membertype='" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pMembertype) + "',applicanttype='" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pApplicanttype) + "',agefrom=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pAgefrom + ",ageto=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pAgeto + ",insuranceduration='" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceduration) + "',premiumamountpayable=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumamountpayable + ",premiumpayin='" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumpayin) + "',insuranceclaimpayoutevent='" + ManageQuote(InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceclaimpayoutevent) + "',insuranceclaimamount=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pInsuranceclaimamount + ",premiumrefund=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].pPremiumrefund + ",modifiedby=" + InsurenceConfigDetails.pCreatedby + ",modifieddate=current_timestamp,statusid=" + Convert.ToInt32(Status.Active) + " where recordid=" + InsurenceConfigDetails.lstInsurenceConfigartionDetails[i].precordid + ";"); } } } if (!string.IsNullOrEmpty(Recordid)) { QueryUpdate.Append("update tblmstinsuranceconfigdetails set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + InsurenceConfigDetails.pCreatedby + ",modifieddate=current_timestamp where insuranceschemeconfigid=" + InsurenceConfigDetails.pInsurenceconfigid + " and recordid not in(" + Recordid + ");"); } else { if (InsurenceConfigDetails.lstInsurenceConfigartionDetails.Count == 0) { QueryUpdate.Append("update tblmstinsuranceconfigdetails set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + InsurenceConfigDetails.pCreatedby + ",modifieddate=current_timestamp where insuranceschemeconfigid=" + InsurenceConfigDetails.pInsurenceconfigid + ";"); } } if (!string.IsNullOrEmpty(Sbinsert.ToString())) { NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, QueryUpdate.ToString() + "" + Sbinsert.ToString()); } trans.Commit(); IsSaved = true; } catch (Exception ex) { trans.Rollback(); throw ex; } finally { if (con.State == ConnectionState.Open) { con.Dispose(); con.Close(); con.ClearPool(); trans.Dispose(); } } return(IsSaved); }
public IDbTransaction BeginTransaction() { return(_impl.BeginTransaction()); }
private void Option7() { string errorMsg = null; while (true) { Console.Clear(); Console.WriteLine("Creation of a new movie.(write exit to go back)\n"); if (errorMsg != null) { Console.WriteLine("Error: " + errorMsg); } Console.Write("Please write a title:"); String title = Console.ReadLine(); if (title == "exit") { return; } Console.Write("Please write a year:"); String year = Console.ReadLine(); if (year == "exit") { return; } Console.Write("Please write an age restriction:"); String ageRestriction = Console.ReadLine(); if (ageRestriction == "exit") { return; } Console.Write("Please write a price:"); String price = Console.ReadLine(); if (price == "exit") { return; } NpgsqlTransaction transaction = null; NpgsqlConnection connection = null; try { connection = new NpgsqlConnection(Configuration.CONNECTION_STRING); connection.Open(); transaction = connection.BeginTransaction(); Movie movie = new Movie(title, Int32.Parse(year), Int32.Parse(ageRestriction), Int32.Parse(price)); movie.Save(); Copy copy = new Copy(true, movie.Id); copy.Save(); transaction.Commit(); } catch (Exception e) { errorMsg = e.Message; transaction?.Rollback(); continue; } finally { connection?.Close(); } Console.WriteLine("Success! Movie was created!"); break; } while (true) { Console.Write("\n\nPress ESC to go back..."); if (Console.ReadKey().Key == ConsoleKey.Escape) { return; } } }
public JsonResponse<List<CandidateImagaeModel>> GetUploadedPhotos(UserCandidateIDModel IDModel) { List<CandidateImageLogger> candidateImages = new List<CandidateImageLogger>(); JsonResponse<List<CandidateImagaeModel>> jsonResponse = new JsonResponse<List<CandidateImagaeModel>>(); jsonResponse.Data = new List<CandidateImagaeModel>(); FileInfo[] filesForApproval = new FileInfo[100]; DirectoryInfo di; var folderName = Path.Combine("Resources", "Images"); var pathofImages = Path.Combine(Directory.GetCurrentDirectory(), folderName); if (!Directory.Exists(pathofImages)) { di = Directory.CreateDirectory(pathofImages); } di = new DirectoryInfo(pathofImages); FileInfo[] files = di.GetFiles("*.*", SearchOption.AllDirectories); CandidateImagaeModel imagemodel; int i = 0; try { using (IDbConnection dbConnection = new NpgsqlConnection(_ConnectionStringService.Value)) { if (files.Length > 0) { dbConnection.Open(); using (var transaction = dbConnection.BeginTransaction()) { candidateImages = dbConnection.Query<CandidateImageLogger>("SELECT * FROM candidate_image_logger where is_from_other_three_photos = true and is_deleted = false and user_id = @p0 and candidate_id = @p1 ", new { p0 = IDModel.user_id, p1 = IDModel.candidate_id }).ToList(); List<CandidateImageLogger> profiles = dbConnection.Query<CandidateImageLogger>("SELECT * FROM candidate_image_logger where is_profile_pic = true and is_deleted = false and user_id = @p0 and candidate_id = @p1 ", new { p0 = IDModel.user_id, p1 = IDModel.candidate_id }).ToList(); if (profiles != null) { foreach (CandidateImageLogger profile in profiles) { if (profiles.Count == 1) { candidateImages.Add(profile); break; } else { if (profile.is_approved == false) { candidateImages.Add(profile); break; } } } } foreach (CandidateImageLogger image in candidateImages) { foreach (FileInfo file in files) { if (file.Name == image.image_name) { using (FileStream fs = new FileStream(file.FullName, FileMode.Open, FileAccess.Read)) { byte[] ImageData = File.ReadAllBytes(file.FullName); string base64String = Convert.ToBase64String(ImageData, 0, ImageData.Length); imagemodel = new CandidateImagaeModel(); imagemodel.is_profile = image.is_profile_pic; imagemodel.is_from_other_three_photos = image.is_from_other_three_photos; imagemodel.imageLoggedid = image.id; imagemodel.user = dbConnection.Query<User>("Select * from user_table where id = @id", new { id = image.user_id }).FirstOrDefault(); imagemodel.candidate = dbConnection.Query<Candidate>("Select * from candidate where id = @id", new { id = image.candidate_id }).FirstOrDefault(); imagemodel.image = "data:image/jpeg;base64," + base64String; jsonResponse.Data.Add(imagemodel); break; } } } } transaction.Commit(); } } } jsonResponse.IsSuccess = true; jsonResponse.Message = "success"; return jsonResponse; } catch (Exception e) { jsonResponse.IsSuccess = false; jsonResponse.Message = "fail"; return jsonResponse; } }
public static string getBCIdent() { JArray idents = new JArray(); try { connection.ConnectionString = connectionString; connection.Open(); NpgsqlTransaction tran = connection.BeginTransaction(); NpgsqlCommand command = new NpgsqlCommand("select idbagcart from bagcarts", connection); NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { idents.Add(dr[0].ToString()); //Console.Write("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6} \n", dr[0], dr[1], dr[2], dr[3], dr[4], dr[5], dr[6]); //Console.Write("{0} \n", dr[0]); } //Console.WriteLine("Success"); } catch (Exception ex) { Debug.WriteLine("Fail"); Debug.WriteLine(ex); } try { if (connection.State == System.Data.ConnectionState.Open) { connection.Close(); Console.WriteLine("Cerrada"); } } catch (Exception ex) { Console.WriteLine(ex); Console.WriteLine("Fallo Cerrar"); } JObject response = new JObject(); response["data"] = idents; Debug.WriteLine(response.ToString()); return(response.ToString()); }
public JsonResponse<string> UploadImages(IFormFileCollection Files, int user_id, int candidate_id, Boolean isProfile) { CandidateImageLogger candidateImageLogger = new CandidateImageLogger(); JsonResponse<string> jsonResponse = new JsonResponse<string>(); string folderName; DirectoryInfo di; List<string> image_names = new List<string>(); try { using (IDbConnection dbConnection = new NpgsqlConnection(_ConnectionStringService.Value)) { dbConnection.Open(); using (var transaction = dbConnection.BeginTransaction()) { if (Files.Count > 0) { if (isProfile == true) { folderName = Path.Combine("Resources", "Images", "User", user_id.ToString(), "Candidates", candidate_id.ToString(), "Profile Image"); image_names = dbConnection.Query<string>("select image_name from candidate_image_logger where user_id = @p0 and candidate_id = @p1 and is_approved = false and is_profile_pic = true ", new { p0 = user_id, @p1 = candidate_id }).ToList(); dbConnection.Query<CandidateImageLogger>("update candidate_image_logger set is_deleted = true where user_id = @p0 and candidate_id = @p1 and is_approved = false and is_profile_pic = true ", new { p0 = user_id, @p1 = candidate_id }); } else { folderName = Path.Combine("Resources", "Images", "User", user_id.ToString(), "Candidates", candidate_id.ToString(), "Other Images"); } var pathToSave = Path.Combine(Directory.GetCurrentDirectory(), folderName); if (!Directory.Exists(pathToSave)) { di = Directory.CreateDirectory(pathToSave); } else { di = new DirectoryInfo(pathToSave); if (isProfile && image_names != null && image_names.Count > 0) { foreach (string image_name in image_names) { foreach (var fi in di.GetFiles()) { if (fi.Name == image_name) { fi.Delete(); break; } } } } } for (int i = 0; i < Files.Count; i++) { var file = Files[i]; if (file.Length > 0) { var fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"'); var fullPath = Path.Combine(pathToSave, fileName); var dbPath = Path.Combine(folderName, fileName); using (var stream = new FileStream(fullPath, FileMode.Create)) { file.CopyTo(stream); } candidateImageLogger.user_id = user_id; candidateImageLogger.candidate_id = candidate_id; candidateImageLogger.image_name = file.FileName; candidateImageLogger.image_path = dbPath; candidateImageLogger.content_type = file.ContentType; candidateImageLogger.image_upload_time = DateTime.Now; candidateImageLogger.is_approved = false; candidateImageLogger.is_deleted = false; if (isProfile) { candidateImageLogger.is_profile_pic = true; candidateImageLogger.is_from_other_three_photos = false; } else { candidateImageLogger.is_from_other_three_photos = true; candidateImageLogger.is_profile_pic = false; } dbConnection.Insert<CandidateImageLogger>(candidateImageLogger, transaction); } } } transaction.Commit(); jsonResponse.Data = "Profile images uploaded successfully"; jsonResponse.IsSuccess = true; jsonResponse.Message = "Success"; } } return jsonResponse; } catch (System.Exception ex) { jsonResponse.Data = "Profile images upload fail"; jsonResponse.IsSuccess = false; jsonResponse.Message = "fail"; return jsonResponse; } }
// // System.Web.Security.RoleProvider methods. // // // RoleProvider.AddUsersToRoles // public override void AddUsersToRoles(string[] usernames, string[] rolenames) { foreach (string rolename in rolenames) { if (!RoleExists(rolename)) { throw new ProviderException("Role name not found."); } } foreach (string username in usernames) { if (username.IndexOf(',') > 0) { throw new ArgumentException("User names cannot contain commas."); } foreach (string rolename in rolenames) { if (IsUserInRole(username, rolename)) { throw new ProviderException("User is already in role."); } } } NpgsqlConnection conn = new NpgsqlConnection(connectionString); NpgsqlCommand cmd = new NpgsqlCommand( "INSERT INTO " + usersInRolesTable + "" + " (user_name, role_name, application_name) " + " Values(@user_name, @role_name, @application_name)", conn); NpgsqlParameter userParm = cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255); NpgsqlParameter roleParm = cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255); cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName; NpgsqlTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; foreach (string username in usernames) { foreach (string rolename in rolenames) { userParm.Value = username; roleParm.Value = rolename; cmd.ExecuteBlind(); } } tran.Commit(); } catch (NpgsqlException e) { try { if (tran != null) { tran.Rollback(); } } catch { } if (WriteExceptionsToEventLog) { WriteToEventLog(e, "AddUsersToRoles"); } else { throw e; } } finally { cmd.Dispose(); conn.Close(); } }