Example #1
0
        /*
         * 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);
        }
Example #3
0
        // 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);
            }
        }
Example #4
0
        /// <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);
            }
        }
Example #5
0
        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();
            }
        }
Example #6
0
        // 分銷商註冊
        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;
            }
        }
Example #7
0
        /// <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);
        }
Example #8
0
        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();
            }
        }
Example #9
0
        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);
        }
Example #11
0
 public void BeginTransactionBeforeOpen()
 {
     using (var conn = new NpgsqlConnection())
         Assert.That(() => conn.BeginTransaction(), Throws.Exception.TypeOf <InvalidOperationException>());
 }
Example #12
0
 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;
            }
        }
Example #15
0
        /// <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;
		}
Example #17
0
        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);
        }
Example #18
0
        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();
        }
Example #19
0
        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);
        }
Example #20
0
        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();
        }
Example #21
0
        /// <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;
                }
            }
        }
Example #23
0
        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);
        }
Example #24
0
 public void IniciarTransaccion()
 {
     tran    = conexion.BeginTransaction();
     transOK = true;
 }
Example #25
0
 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);
        }
Example #28
0
 public IDbTransaction BeginTransaction()
 {
     return(_impl.BeginTransaction());
 }
Example #29
0
        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;
            }
        }
Example #31
0
        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();
			}
		}