public static int UpdateRestorer(NpgsqlConnection connection, WorkflowRuntimeModel status, Guid oldLock)
{
var command = String.Format("UPDATE {0} SET \"RestorerId\" = @restorer, \"Lock\" = @newlock WHERE \"RuntimeId\" = @id AND \"Lock\" = @oldlock", ObjectName);
var p1 = new NpgsqlParameter("restorer", NpgsqlDbType.Varchar)
{
Value = status.RestorerId
};
var p2 = new NpgsqlParameter("newlock", NpgsqlDbType.Uuid)
{
Value = status.Lock
};
var p3 = new NpgsqlParameter("id", NpgsqlDbType.Varchar)
{
Value = status.RuntimeId
};
var p4 = new NpgsqlParameter("oldlock", NpgsqlDbType.Uuid)
{
Value = oldLock
};
return(ExecuteCommand(connection, command, p1, p2, p3, p4));
}
public async Task <bool> DeleteFoldersBySite(Guid siteGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Varchar, 36);
arParams[0].Value = siteGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_sitefolders ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid = :siteguid ");
sqlCommand.Append(";");
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public void FillPatient(CPatient patient)
{
conn.Open();
NpgsqlTransaction t = conn.BeginTransaction();
NpgsqlCommand comm = new NpgsqlCommand("\"FillPatient2\"", conn);
NpgsqlParameter pid = new NpgsqlParameter("pid", NpgsqlTypes.NpgsqlDbType.Integer);
pid.Direction = ParameterDirection.Input;
comm.Parameters.Add(pid);
comm.Parameters[0].Value = patient.pid;
comm.CommandType = CommandType.StoredProcedure;
try
{
var result = comm.ExecuteScalar();
}
finally
{
t.Commit();
conn.Close();
}
}
/// <summary>
/// if mysql return MySqlParameter[] pars
/// if sqlerver return SqlParameter[] pars ...
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public override IDataParameter[] ToIDbDataParameter(params SugarParameter[] parameters)
{
if (parameters == null || parameters.Length == 0)
{
return(null);
}
NpgsqlParameter[] result = new NpgsqlParameter[parameters.Length];
int index = 0;
foreach (var parameter in parameters)
{
if (parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
var sqlParameter = new NpgsqlParameter();
sqlParameter.ParameterName = parameter.ParameterName;
sqlParameter.Size = parameter.Size;
sqlParameter.Value = parameter.Value;
sqlParameter.DbType = parameter.DbType;
sqlParameter.Direction = parameter.Direction;
if (sqlParameter.Direction == 0)
{
sqlParameter.Direction = ParameterDirection.Input;
}
result[index] = sqlParameter;
if (sqlParameter.Direction.IsIn(ParameterDirection.Output, ParameterDirection.InputOutput, ParameterDirection.ReturnValue))
{
if (this.OutputParameters == null)
{
this.OutputParameters = new List <IDataParameter>();
}
this.OutputParameters.RemoveAll(it => it.ParameterName == sqlParameter.ParameterName);
this.OutputParameters.Add(sqlParameter);
}
++index;
}
return(result);
}
//*********************************************************************
//
// GetSingleModuleDefinition Method
//
// The GetSingleModuleDefinition method returns a NpgsqlDataReader containing details
// about a specific module definition from the ModuleDefinitions table.
//
// Other relevant sources:
// + <a href="GetSingleModuleDefinition.htm" style="color:green">GetSingleModuleDefinition Stored Procedure</a>
//
//*********************************************************************
public NpgsqlDataReader GetSingleModuleDefinition(int defId)
{
// Create Instance of Connection and Command Object
NpgsqlConnection myConnection = new NpgsqlConnection(ConfigurationSettings.AppSettings["NpgsqlConnectionString"]);
NpgsqlCommand myCommand = new NpgsqlCommand("GetSingleModuleDefinition(:ModuleDefID)", myConnection);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
NpgsqlParameter parameterModuleDefID = new NpgsqlParameter("ModuleDefID", DbType.Int32);
parameterModuleDefID.Value = defId;
myCommand.Parameters.Add(parameterModuleDefID);
// Execute the command
myConnection.Open();
NpgsqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
// Return the datareader
return(result);
}
/// <summary>
/// Hàm lấy quyền người dùng theo mã nhân viên và ký hiệu chức năng
/// </summary>
/// <param name="stridnhanvien">Mã nhân viên kiểu string</param>
/// <param name="strmenuname">Ký hiệu chức năng kiểu string</param>
/// <returns>quyennguoidung</returns>
public quyennguoidung GetByIDNhanVienvsMenuName(string stridnhanvien, string strmenuname)
{
quyennguoidung objquyennguoidung = new quyennguoidung();
string strFun = "fn_quyennguoidung_checkexitrolebymenuname";
try
{
NpgsqlParameter[] prmArr = new NpgsqlParameter[2];
prmArr[0] = new NpgsqlParameter("idnhanvien", NpgsqlDbType.Varchar);
prmArr[0].Value = stridnhanvien;
prmArr[1] = new NpgsqlParameter("kyhieucn", NpgsqlDbType.Varchar);
prmArr[1].Value = strmenuname;
DataSet dsquyennguoidung = mDataAccess.ExecuteDataSet(strFun, CommandType.StoredProcedure, prmArr);
if ((dsquyennguoidung != null) && (dsquyennguoidung.Tables.Count > 0))
{
if (dsquyennguoidung.Tables[0].Rows.Count > 0)
{
DataRow dr = dsquyennguoidung.Tables[0].Rows[0];
try { objquyennguoidung.id = Convert.ToInt32("0" + dr["id"].ToString()); }
catch { objquyennguoidung.id = 0; }
objquyennguoidung.idnhanvien = dr["idnhanvien"].ToString();
try { objquyennguoidung.idmenu = Convert.ToInt32("0" + dr["idmenu"].ToString()); }
catch { objquyennguoidung.idmenu = 0; }
objquyennguoidung.kyhieucn = dr["kyhieucn"].ToString();
objquyennguoidung.tenquyendl = dr["tenquyendl"].ToString();
objquyennguoidung.quyendl = dr["quyendl"].ToString();
objquyennguoidung.status = Convert.ToBoolean(dr["status"].ToString());
objquyennguoidung.mota = dr["mota"].ToString();
return(objquyennguoidung);
}
return(null);
}
return(null);
}
catch
{
return(null);
}
}
/// <summary>
/// Selects all Files for a Player from the mp_MediaFile table.
/// </summary>
/// <param name="trackID">The ID of the Player.</param>
/// <returns>An IDataReader containing the Media File(s) data.</returns>
public static IDataReader SelectByPlayer(int playerId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_mediafile ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("trackid IN (SELECT trackid FROM mp_mediatrack WHERE playerid = :playerid) ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("playerid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = playerId;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Deletes a row from the mp_SurveyQuestionOptions table. Returns true if row deleted.
/// </summary>
/// <param name="questionOptionGuid"> questionOptionGuid </param>
/// <returns>bool</returns>
public static bool Delete(
Guid questionOptionGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_surveyquestionoptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("questionoptionguid = :questionoptionguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("questionoptionguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionOptionGuid.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public void GetStringWithQuoteWithParameter()
{
NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_text = :value;", _conn);
String test = "Text with ' single quote";
NpgsqlParameter param = new NpgsqlParameter();
param.ParameterName = "value";
param.DbType = DbType.String;
//param.NpgsqlDbType = NpgsqlDbType.Text;
param.Size = test.Length;
param.Value = test;
command.Parameters.Add(param);
NpgsqlDataReader dr = command.ExecuteReader();
dr.Read();
String result = dr.GetString(1);
Assert.AreEqual(test, result);
}
public async Task <bool> DeleteUserRolesByRole(int roleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_userroles ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("roleid = :roleid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Value = roleId;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Executes non query with multipal output.
/// </summary>
/// <param name="storedProcedureName">Strored procedure name.</param>
/// <param name="inputParamColl">Accept Key Value collection for parameters.</param>
/// <param name="outputParamColl">Output Key Value collection for parameters.</param>
/// <returns>List Key Value collection</returns>
public List <KeyValuePair <string, object> > ExecuteNonQueryWithMultipleOutputStringObject(string storedProcedureName, List <KeyValuePair <string, object> > inputParamColl, List <KeyValuePair <string, object> > outputParamColl)
{
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = conn;
command.CommandText = storedProcedureName;
command.CommandType = CommandType.StoredProcedure;
foreach (KeyValuePair <string, object> kvp in inputParamColl)
{
NpgsqlParameter sqlParaMeter = new NpgsqlParameter();
sqlParaMeter.IsNullable = true;
sqlParaMeter.ParameterName = kvp.Key;
sqlParaMeter.Value = kvp.Value;
command.Parameters.Add(sqlParaMeter);
}
foreach (KeyValuePair <string, object> kvp in outputParamColl)
{
NpgsqlParameter sqlParaMeter = new NpgsqlParameter();
sqlParaMeter.IsNullable = true;
sqlParaMeter.ParameterName = kvp.Key;
sqlParaMeter.Value = kvp.Value;
sqlParaMeter.Direction = ParameterDirection.InputOutput;
sqlParaMeter.Size = 256;
command.Parameters.Add(sqlParaMeter);
}
conn.Open();
command.ExecuteNonQuery();
List <KeyValuePair <string, object> > lstRetValues = new List <KeyValuePair <string, object> >();
for (int i = 0; i < outputParamColl.Count; i++)
{
lstRetValues.Add(new KeyValuePair <string, object>(i.ToString(), command.Parameters[inputParamColl.Count + i].Value.ToString()));
}
return(lstRetValues);
}
}
/// <summary>
/// Executes non query.
/// </summary>
/// <param name="storedProcedureName">Store procedure name.</param>
/// <param name="parameterCollection">Accept key value collection for parameters. <KeyValuePair<string, string>> </param>
public void ExecuteNonQuery(string storedProcedureName, List <KeyValuePair <string, object> > parameterCollection)
{
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = conn;
command.CommandText = storedProcedureName;
command.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < parameterCollection.Count; i++)
{
NpgsqlParameter sqlParaMeter = new NpgsqlParameter();
sqlParaMeter.IsNullable = true;
sqlParaMeter.ParameterName = parameterCollection[i].Key;
sqlParaMeter.Value = parameterCollection[i].Value;
command.Parameters.Add(sqlParaMeter);
}
conn.Open();
command.ExecuteNonQuery();
}
}
public static bool DeleteBySite(int siteId)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_contactformmessage ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid IN (SELECT siteguid FROM mp_sites WHERE siteid = :siteid) ");
sqlCommand.Append(";");
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public async Task <User> GetAccountAsync(Guid accountGuid, CancellationToken ct = default)
{
await using var cmd = this.m_authorization.Connection.CreateCommand();
if (cmd.Connection.State != ConnectionState.Open)
{
await cmd.Connection.OpenAsync(ct).ConfigureAwait(false);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = NetworkApi_GetAccountByID;
var param = new NpgsqlParameter("userid", NpgsqlDbType.Uuid)
{
Value = accountGuid
};
cmd.Parameters.Add(param);
await using var reader = await cmd.ExecuteReaderAsync(ct).ConfigureAwait(false);
return(await this.GetAccountAsync(reader, ct).ConfigureAwait(false));
}
public void GetValueFromEmptyResultset()
{
var command = new NpgsqlCommand("select * from data where field_text = :value;", Conn);
const string test = "Text single quote";
var param = new NpgsqlParameter();
param.ParameterName = "value";
param.DbType = DbType.String;
//param.NpgsqlDbType = NpgsqlDbType.Text;
param.Size = test.Length;
param.Value = test;
command.Parameters.Add(param);
using (var dr = command.ExecuteReader())
{
dr.Read();
// This line should throw the invalid operation exception as the datareader will
// have an empty resultset.
Console.WriteLine(dr.IsDBNull(1));
}
}
public async Task <User> GetAccountByEmailAsync(string emailAddress, CancellationToken ct = default)
{
await using var cmd = this.m_authorization.Connection.CreateCommand();
if (cmd.Connection.State != ConnectionState.Open)
{
await cmd.Connection.OpenAsync(ct).ConfigureAwait(false);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = NetworkApi_GetAccountByEmail;
var email = new NpgsqlParameter("email", NpgsqlDbType.Text)
{
Value = emailAddress
};
cmd.Parameters.Add(email);
await using var reader = await cmd.ExecuteReaderAsync(ct).ConfigureAwait(false);
return(await this.GetAccountAsync(reader, ct).ConfigureAwait(false));
}
/// <summary>
/// Hàm lấy hóa đơn nhập mới nhất theo ngày tạo(chặn cuối) và mã nhà cung cấp
/// </summary>
/// <param name="sngaytao">Ngày tạo kiểu string format: yyyy/MM/dd</param>
/// <param name="sidnhacc">Mã nhà cung cấp kiểu string</param>
/// <returns>tblphieunhapkho</returns>
public tblphieunhapkho GetNewFirstByNgayTaovsIDKH(string sngaytao, string sidnhacc)
{
tblphieunhapkho objtblphieunhapkho = new tblphieunhapkho();
string strFun = "fn_tblphieunhapkho_getnewfirst";
try
{
NpgsqlParameter[] prmArr = new NpgsqlParameter[2];
prmArr[0] = new NpgsqlParameter("ngaytao", NpgsqlDbType.Varchar);
prmArr[0].Value = sngaytao;
prmArr[1] = new NpgsqlParameter("iid_nhacc", NpgsqlDbType.Varchar);
prmArr[1].Value = sidnhacc;
DataSet dstblhoadonban = mDataAccess.ExecuteDataSet(strFun, CommandType.StoredProcedure, prmArr);
if ((dstblhoadonban != null) && (dstblhoadonban.Tables.Count > 0))
{
if (dstblhoadonban.Tables[0].Rows.Count > 0)
{
DataRow dr = dstblhoadonban.Tables[0].Rows[0];
objtblphieunhapkho.id = dr["id"].ToString();
objtblphieunhapkho.id_nguoicap = dr["id_nguoicap"].ToString();
try { objtblphieunhapkho.ngaytao = Convert.ToDateTime(dr["ngaytao"].ToString()); }
catch { objtblphieunhapkho.ngaytao = new DateTime(1900, 1, 1); }
try { objtblphieunhapkho.tienthanhtoan = Convert.ToDouble("0" + dr["tienthanhtoan"].ToString()); }
catch { objtblphieunhapkho.tienthanhtoan = 0; }
objtblphieunhapkho.noinhap = dr["noinhap"].ToString();
objtblphieunhapkho.ghichu = dr["ghichu"].ToString();
try { objtblphieunhapkho.chietkhau = Convert.ToDouble("0" + dr["chietkhau"].ToString()); }
catch { objtblphieunhapkho.chietkhau = 0; }
return(objtblphieunhapkho);
}
return(null);
}
return(null);
}
catch
{
return(null);
}
}
/// <summary>
/// Gets an IDataReader with the row from the doan_MediaPlayers table that exists for a Module.
/// </summary>
/// <param name="moduleID">The ID of the Module.</param>
/// <returns>The data for the Medai Player as an IDataReader object.</returns>
public static IDataReader SelectByModule(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_mediaplayer ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleid = :moduleid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Hàm lấy tiền còn nợ của các toa trước
/// </summary>
/// <param name="dtngaytao">Ngày tạo hóa đơn kiểu DateTime</param>
/// <returns>double: Số tiền còn nợ của các toa trước</returns>
public double GetTienConNo(string sidnhacc, DateTime dtngaytao)
{
string strProc = "fn_tblphieunhapkho_gettienconno";
try
{
NpgsqlParameter[] prmArr = new NpgsqlParameter[3];
prmArr[0] = new NpgsqlParameter("idnhacc", NpgsqlDbType.Varchar);
prmArr[0].Value = sidnhacc;
prmArr[1] = new NpgsqlParameter("ngaytao", NpgsqlDbType.Varchar);
prmArr[1].Value = dtngaytao.ToString("yyyy/MM/dd HH:mm:ss");
prmArr[2] = new NpgsqlParameter("ireturn", NpgsqlDbType.Text);
prmArr[2].Direction = ParameterDirection.Output;
mDataAccess.ExecuteQuery(strProc, CommandType.StoredProcedure, prmArr);
string sKQ = prmArr[2].Value.ToString().Trim();
return(Convert.ToDouble(sKQ));
}
catch
{
return(0);
}
}
//
// MODULE DEFINITIONS
//
//*********************************************************************
//
// GetModuleDefinitions() Method <a name="GetModuleDefinitions"></a>
//
// The GetModuleDefinitions method returns a list of all module type
// definitions for the portal.
//
// Other relevant sources:
// + <a href="GetModuleDefinitions.htm" style="color:green">GetModuleDefinitions Stored Procedure</a>
//
//*********************************************************************
public NpgsqlDataReader GetModuleDefinitions(int portalId)
{
// Create Instance of Connection and Command Object
NpgsqlConnection myConnection = new NpgsqlConnection(ConfigurationSettings.AppSettings["NpgsqlConnectionString"]);
NpgsqlCommand myCommand = new NpgsqlCommand("GetModuleDefinitions(:PortalID)", myConnection);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
NpgsqlParameter parameterPortalId = new NpgsqlParameter("PortalID", DbType.Int32);
parameterPortalId.Value = portalId;
myCommand.Parameters.Add(parameterPortalId);
// Open the database connection and execute the command
myConnection.Open();
NpgsqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
// Return the datareader
return(dr);
}
public static async Task <int> DeleteByProcessIdAsync(NpgsqlConnection connection, Guid processId, List <string> timersIgnoreList = null, NpgsqlTransaction transaction = null)
{
var pProcessId = new NpgsqlParameter("processid", NpgsqlDbType.Uuid)
{
Value = processId
};
if (timersIgnoreList != null && timersIgnoreList.Any())
{
// ReSharper disable once BitwiseOperatorOnEnumWithoutFlags
var pTimerIgnoreList = new NpgsqlParameter("timerIgnoreList", NpgsqlDbType.Array | NpgsqlDbType.Varchar) //-V3059
{
Value = timersIgnoreList.ToArray()
};
return(await ExecuteCommandAsync(connection,
$"DELETE FROM {ObjectName} WHERE \"ProcessId\" = @processid AND \"Name\" != ALL(@timerIgnoreList)", transaction, pProcessId, pTimerIgnoreList).ConfigureAwait(false));
}
return(await ExecuteCommandAsync(connection,
$"DELETE FROM {ObjectName} WHERE \"ProcessId\" = @processid", transaction, pProcessId).ConfigureAwait(false));
}
/* последовательная версия*/
public void UpdateProtocol(CProtocol protocol, DateTime date)
{
conn.Open();
NpgsqlTransaction t = conn.BeginTransaction();
NpgsqlCommand comm = new NpgsqlCommand("\"UpdateProtocol\"", conn);
NpgsqlParameter pid = new NpgsqlParameter("pid", NpgsqlTypes.NpgsqlDbType.Integer);
NpgsqlParameter dat = new NpgsqlParameter("dat", NpgsqlTypes.NpgsqlDbType.Date);
NpgsqlParameter text = new NpgsqlParameter("text", NpgsqlTypes.NpgsqlDbType.Varchar);
pid.Direction = ParameterDirection.Input;
dat.Direction = ParameterDirection.Input;
text.Direction = ParameterDirection.Input;
comm.Parameters.Add(pid);
comm.Parameters.Add(dat);
comm.Parameters.Add(text);
comm.Parameters[0].Value = protocol.sid;
comm.Parameters[1].Value = date.Date;
comm.Parameters[2].Value = protocol.text;
comm.CommandType = CommandType.StoredProcedure;
try
{
var result = comm.ExecuteScalar();
//protocol.text = (string)result;
}
finally
{
t.Commit();
conn.Close();
}
return; // ???
}
/// <summary>
/// Gets a count of rows in the mp_ContentTemplate table.
/// </summary>
public static int GetCount(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_contenttemplate ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid = :siteguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(Convert.ToInt32(NpgsqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams)));
}
/* извлекает список пациентов за указанную дату, вызов хранимой процедуры*/
public void SPCheckDB()
{
conn.Open();
NpgsqlTransaction t = conn.BeginTransaction();
NpgsqlCommand comm = new NpgsqlCommand("\\", conn);
NpgsqlParameter prm = new NpgsqlParameter("pid", NpgsqlTypes.NpgsqlDbType.Integer);
prm.Direction = ParameterDirection.Input;
comm.Parameters.Add(prm);
comm.Parameters[0].Value = 15;
comm.CommandType = CommandType.StoredProcedure;
int pid = 0;
try
{
pid = (int)comm.ExecuteScalar();
}
finally
{
conn.Close();
}
}
public static bool DeleteBySite(Guid siteGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_paypallog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid = :siteguid ");
sqlCommand.Append(";");
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public async Task Array_of_single_bits()
{
using var conn = await OpenConnectionAsync();
using var cmd = new NpgsqlCommand("SELECT @p::BIT(1)[]", conn);
var expected = new[] { true, false };
var p = new NpgsqlParameter("p", NpgsqlDbType.Array | NpgsqlDbType.Bit)
{
Value = expected
};
cmd.Parameters.Add(p);
p.Value = expected;
using var reader = await cmd.ExecuteReaderAsync();
reader.Read();
var x = reader.GetValue(0);
Assert.That(reader.GetValue(0), Is.EqualTo(expected));
Assert.That(reader.GetFieldValue <bool[]>(0), Is.EqualTo(expected));
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof(Array)));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_PayPalLog table.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
public static IDataReader GetOne(Guid rowGuid)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("rowguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_paypallog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("rowguid = :rowguid ");
sqlCommand.Append(";");
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public async Task <DbDataReader> GetOne(string folderName)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("foldername", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[0].Value = folderName;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_sitefolders ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("foldername = :foldername ");
sqlCommand.Append(";");
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Validates and calculates the length, but does not consult the length cache or stores
/// in it. Used by other type handlers which delegate to TextHandler.
/// </summary>
internal int DoValidateAndGetLength(object value, NpgsqlParameter parameter = null)
{
var asString = value as string;
if (asString != null)
{
return(parameter == null || parameter.Size == 0 || parameter.Size >= asString.Length
? Encoding.UTF8.GetByteCount(asString)
: Encoding.UTF8.GetByteCount(asString.ToCharArray(), 0, parameter.Size));
}
var asCharArray = value as char[];
if (asCharArray != null)
{
return(parameter == null || parameter.Size == 0 || parameter.Size >= asCharArray.Length
? Encoding.UTF8.GetByteCount(asCharArray)
: Encoding.UTF8.GetByteCount(asCharArray, 0, parameter.Size));
}
throw new InvalidCastException("Can't write type as text: " + value.GetType());
}
public async Task GlobalMapping()
{
using var adminConn = await OpenConnectionAsync();
await using var _ = await GetTempTypeName(adminConn, out var type);
using (var conn = await OpenConnectionAsync())
{
await conn.ExecuteNonQueryAsync($"CREATE TYPE {type} AS ENUM ('sad', 'ok', 'happy')");
NpgsqlConnection.GlobalTypeMapper.MapEnum <Mood>(type);
conn.ReloadTypes();
const Mood expected = Mood.Ok;
using (var cmd = new NpgsqlCommand($"SELECT @p::{type}", conn))
{
var p = new NpgsqlParameter {
ParameterName = "p", Value = expected
};
cmd.Parameters.Add(p);
using (var reader = await cmd.ExecuteReaderAsync())
{
reader.Read();
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof(Mood)));
Assert.That(reader.GetFieldValue <Mood>(0), Is.EqualTo(expected));
Assert.That(reader.GetValue(0), Is.EqualTo(expected));
}
}
}
// Unmap
NpgsqlConnection.GlobalTypeMapper.UnmapEnum <Mood>(type);
using (var conn = await OpenConnectionAsync())
{
// Enum should have been unmapped and so will return as text
Assert.That(await conn.ExecuteScalarAsync($"SELECT 'ok'::{type}"), Is.EqualTo("ok"));
}
}
public void NoOutputParameters()
{
var p = new NpgsqlParameter("p", DbType.String) { Direction = ParameterDirection.Output };
_params.Add(p);
Assert.That(() => SqlQueryParser.ParseRawQuery("SELECT @p", true, _params, _queries), Throws.Exception);
}
public void MultiqueryWithParams()
{
var p1 = new NpgsqlParameter("p1", DbType.String);
_params.Add(p1);
var p2 = new NpgsqlParameter("p2", DbType.String);
_params.Add(p2);
var p3 = new NpgsqlParameter("p3", DbType.String);
_params.Add(p3);
SqlQueryParser.ParseRawQuery("SELECT @p3, @p1; SELECT @p2, @p3", true, _params, _queries);
Assert.That(_queries, Has.Count.EqualTo(2));
Assert.That(_queries[0].InputParameters[0], Is.SameAs(p3));
Assert.That(_queries[0].InputParameters[1], Is.SameAs(p1));
Assert.That(_queries[1].InputParameters[0], Is.SameAs(p2));
Assert.That(_queries[1].InputParameters[1], Is.SameAs(p3));
}
/// <summary>
/// Creates input parameter
/// </summary>
/// <param name="paramName"></param>
/// <param name="dbType"></param>
/// <param name="objValue"></param>
/// <returns></returns>
private static NpgsqlParameter CreateInputParam(string paramName, NpgsqlDbType dbType, object objValue)
{
NpgsqlParameter param = new NpgsqlParameter(paramName, dbType);
if (objValue == null)
{
objValue = String.Empty;
}
param.Value = objValue;
return param;
}
/// <summary>
/// Finds inactive Profiles by user name
/// </summary>
/// <param name="authenticationOption"></param>
/// <param name="usernameToMatch"></param>
/// <param name="userInactiveSinceDate"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecords"></param>
/// <returns></returns>
public override ProfileInfoCollection FindInactiveProfilesByUserName(ProfileAuthenticationOption authenticationOption,
string usernameToMatch,
DateTime userInactiveSinceDate, int pageIndex,
int pageSize, out int totalRecords)
{
StringBuilder insertQuery = GenerateTempInsertQueryForGetProfiles(authenticationOption);
insertQuery.Append(" AND u.UserName LIKE LOWER(@UserName) AND u.LastActivityDate <= @InactiveSinceDate");
NpgsqlParameter[] args = new NpgsqlParameter[2];
args[0] = CreateInputParam("@InactiveSinceDate", NpgsqlDbType.Timestamp, userInactiveSinceDate.ToUniversalTime());
args[1] = CreateInputParam("@UserName", NpgsqlDbType.Text, usernameToMatch);
return GetProfilesForQuery(args, pageIndex, pageSize, insertQuery, out totalRecords);
}
/// <summary>
/// Finds Profiles by user name
/// </summary>
/// <param name="authenticationOption"></param>
/// <param name="usernameToMatch"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecords"></param>
/// <returns></returns>
public override ProfileInfoCollection FindProfilesByUserName(ProfileAuthenticationOption authenticationOption,
string usernameToMatch, int pageIndex, int pageSize,
out int totalRecords)
{
StringBuilder insertQuery = GenerateTempInsertQueryForGetProfiles(authenticationOption);
insertQuery.Append(" AND u.UserName LIKE LOWER(@UserName)");
NpgsqlParameter[] args = new NpgsqlParameter[1];
args[0] = CreateInputParam("@UserName", NpgsqlDbType.Text, usernameToMatch);
return GetProfilesForQuery(args, pageIndex, pageSize, insertQuery, out totalRecords);
}
/// <summary>
/// Gets Profiles for Query
/// </summary>
/// <param name="insertArgs"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="insertQuery"></param>
/// <param name="totalRecords"></param>
/// <returns></returns>
private ProfileInfoCollection GetProfilesForQuery(NpgsqlParameter[] insertArgs, int pageIndex, int pageSize,
StringBuilder insertQuery, out int totalRecords)
{
if (pageIndex < 0)
{
throw new ArgumentException("pageIndex");
}
if (pageSize < 1)
{
throw new ArgumentException("pageSize");
}
long lowerBound = (long) pageIndex*pageSize;
long upperBound = lowerBound + pageSize - 1;
if (upperBound > Int32.MaxValue)
{
throw new ArgumentException("pageIndex and pageSize");
}
NpgsqlConnection conn = null;
NpgsqlDataReader reader = null;
NpgsqlCommand cmd = null;
try
{
conn = new NpgsqlConnection(_NpgsqlConnectionString);
conn.Open();
StringBuilder cmdStr = new StringBuilder(200);
// Create a temp table TO store the select results
cmd =
new NpgsqlCommand(
"CREATE TABLE #PageIndexForProfileUsers(IndexId int IDENTITY (0, 1) NOT NULL, UserId varchar(36))", conn);
cmd.CommandTimeout = CommandTimeout;
cmd.ExecuteBlind();
cmd.Dispose();
insertQuery.Append(" ORDER BY UserName");
cmd = new NpgsqlCommand(insertQuery.ToString(), conn);
cmd.CommandTimeout = CommandTimeout;
if (insertArgs != null)
{
foreach (NpgsqlParameter arg in insertArgs)
{
cmd.Parameters.Add(arg);
}
}
cmd.ExecuteBlind();
cmd.Dispose();
cmdStr = new StringBuilder(200);
cmdStr.Append("SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate FROM vw_aspnet_Users u, ").
Append(_table);
cmdStr.Append(" p, #PageIndexForProfileUsers i WHERE u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= ");
cmdStr.Append(lowerBound).Append(" AND i.IndexId <= ").Append(upperBound);
cmd = new NpgsqlCommand(cmdStr.ToString(), conn);
cmd.CommandTimeout = CommandTimeout;
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
ProfileInfoCollection profiles = new ProfileInfoCollection();
while (reader.Read())
{
string username;
DateTime dtLastActivity, dtLastUpdated = DateTime.UtcNow;
bool isAnon;
username = reader.GetString(0);
isAnon = reader.GetBoolean(1);
dtLastActivity = DateTime.SpecifyKind(reader.GetDateTime(2), DateTimeKind.Utc);
dtLastUpdated = DateTime.SpecifyKind(reader.GetDateTime(3), DateTimeKind.Utc);
profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, 0));
}
totalRecords = profiles.Count;
if (reader != null)
{
reader.Close();
reader = null;
}
cmd.Dispose();
cmd = new NpgsqlCommand("DROP TABLE #PageIndexForProfileUsers", conn);
cmd.ExecuteBlind();
return profiles;
}
finally
{
if (reader != null)
{
reader.Close();
}
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
conn.Close();
conn = null;
}
}
}
