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