/// <summary>
/// 输出异常信息
/// </summary>
/// <param name="cmd">NpgsqlCommand 对象</param>
/// <param name="ex">异常信息</param>
protected virtual void ExceptionOutPut(NpgsqlCommand cmd, Exception ex)
{
NpgsqlParameterCollection coll = cmd.Parameters;
ex.Data["host"] = cmd.Connection.Host;
ex.Data["port"] = cmd.Connection.Port;
string ps = string.Empty;
if (coll != null)
{
for (int i = 0; i < coll.Count; i++)
{
var item = coll[i];
ps += $"{ item.ParameterName}:{item.Value},";
}
}
string sql = cmd.CommandText;
for (int i = 0; i < coll.Count; i++)
{
var para = coll[i];
var isString = IsString(para.NpgsqlDbType);
var val = string.Format("{0}{1}{0}", isString ? "'" : "", para.Value.ToString());
sql = sql.Replace("@" + para.ParameterName, val);
}
if (_logger != null)
{
_logger.LogError(new EventId(111111), ex, "数据库执行出错：===== \n {0}\n{1}\n{2}", sql, coll, ps);
}
else
{
Console.WriteLine("数据库执行出错：===== \n {0}\n{1}\n{2}", sql, coll, ps);
}
}
protected TResult ExecuteQuery <TResult>(Func <NpgsqlCommand, TResult> func)
{
var sql = string.Empty;
NpgsqlParameterCollection sqlParams = null;
var result = default(TResult);
using (var connection = new NpgsqlConnection(_connectionString))
{
try
{
connection.Open();
using (var command = connection.CreateCommand())
{
sql = command.CommandText;
sqlParams = command.Parameters;
command.CommandType = CommandType.Text;
result = func(command);
}
}
catch (NpgsqlException e)
{
var paramMessage = (sqlParams != null) ?
$"Параметры {string.Join(',', sqlParams.Select(x => x.Value.ToString().ToArray()))}" :
string.Empty;
_logger.LogError(e, $"Ошибка при выполнении запроса в базу. " +
$"Текст запроса:\n {sql}\n {paramMessage}");
}
finally
{
connection.Close();
}
}
return(result);
}
public static void AddValue(this NpgsqlParameterCollection collection, string key, string value, int size)
{
var param = new NpgsqlParameter(key, NpgsqlDbType.Varchar, size);
param.Value = string.IsNullOrEmpty(value) ? DBNull.Value : (object)value;
collection.Add(param);
}
public static void AddValue(this NpgsqlParameterCollection collection, string key, OperationType value)
{
var param = new NpgsqlParameter(key, NpgsqlDbType.Smallint);
param.Value = (byte)value;
collection.Add(param);
}
public override void AddParameters(NpgsqlParameterCollection collection)
{
base.AddParameters(collection);
collection.AddValue("@p2_1", Login, 16);
collection.AddValue("@p2_2", JsonData);
}
public static void AddValue(this NpgsqlParameterCollection collection, string key, decimal value, int size = 20)
{
var param = new NpgsqlParameter(key, NpgsqlDbType.Numeric, size);
param.Value = value;
collection.Add(param);
}
public static void AddValue(this NpgsqlParameterCollection collection, string key, byte[] value)
{
var param = new NpgsqlParameter(key, NpgsqlDbType.Bytea);
param.Value = value;
collection.Add(param);
}
public static void AddValue(this NpgsqlParameterCollection collection, string key, long?value)
{
var param = new NpgsqlParameter(key, NpgsqlDbType.Bigint);
param.Value = value.HasValue ? (object)value : DBNull.Value;
collection.Add(param);
}
public static void AddValue(this NpgsqlParameterCollection collection, string key, DateTime value, NpgsqlDbType parameterType = NpgsqlDbType.Timestamp)
{
var param = new NpgsqlParameter(key, parameterType);
param.Value = value;
collection.Add(param);
}
public static NpgsqlParameter AddSafeWithValue(
this NpgsqlParameterCollection collection,
string parameterName,
object value)
{
return(collection.AddWithValue(parameterName, value ?? DBNull.Value));
}
public static NpgsqlParameterCollection AddSessionItemLongParameter(this NpgsqlParameterCollection pc, int length, byte[] buf)
{
NpgsqlParameter sqlParameter = new NpgsqlParameter(string.Format("@{0}", (object)SqlParameterName.SessionItemLong), NpgsqlDbType.Bytea, length);
sqlParameter.Value = (object)buf;
pc.Add(sqlParameter);
return(pc);
}
public static NpgsqlParameterCollection AddExpiresTimeParameter(this NpgsqlParameterCollection pc, int timeout)
{
NpgsqlParameter sqlParameter = new NpgsqlParameter(string.Format("@{0}", (object)SqlParameterName.Expires), NpgsqlDbType.Timestamp);
sqlParameter.Value = DateTime.UtcNow.AddMinutes(timeout);
pc.Add(sqlParameter);
return(pc);
}
public static NpgsqlParameterCollection AddSessionIdParameter(this NpgsqlParameterCollection pc, string id)
{
NpgsqlParameter sqlParameter = new NpgsqlParameter(string.Format("@{0}", (object)SqlParameterName.SessionId), NpgsqlDbType.Varchar, SqlSessionStateRepositoryUtil.IdLength);
sqlParameter.Value = (object)id;
pc.Add(sqlParameter);
return(pc);
}
public static NpgsqlParameterCollection AddLockDateLocalParameter(this NpgsqlParameterCollection pc)
{
NpgsqlParameter sqlParameter = new NpgsqlParameter(string.Format("@{0}", (object)SqlParameterName.LockDateLocal), NpgsqlDbType.Timestamp);
sqlParameter.Value = DateTime.Now;
pc.Add(sqlParameter);
return(pc);
}
public static NpgsqlParameterCollection AddActionFlagsParameter(this NpgsqlParameterCollection pc)
{
NpgsqlParameter sqlParameter = new NpgsqlParameter(string.Format("@{0}", (object)SqlParameterName.ActionFlags), NpgsqlDbType.Integer);
sqlParameter.Direction = ParameterDirection.Output;
sqlParameter.Value = Convert.DBNull;
pc.Add(sqlParameter);
return(pc);
}
public static NpgsqlParameterCollection AddTimeoutParameter(this NpgsqlParameterCollection pc, int timeout)
{
NpgsqlParameter sqlParameter = new NpgsqlParameter(string.Format("@{0}", (object)SqlParameterName.Timeout), NpgsqlDbType.Integer);
sqlParameter.Value = (object)timeout;
pc.Add(sqlParameter);
AddExpiresTimeParameter(pc, timeout);
return(pc);
}
public static void AddValue(this NpgsqlParameterCollection collection, string key, Guid value)
{
var param = new NpgsqlParameter <Guid>(key, NpgsqlDbType.Uuid)
{
TypedValue = value
};
collection.Add(param);
}
public static void AddWithValueAsEnum(this NpgsqlParameterCollection parameters, string name, string value, string enumName)
{
parameters.Add(new NpgsqlParameter
{
ParameterName = name,
Value = value,
DataTypeName = enumName,
});
}
public static void AddValue(this NpgsqlParameterCollection collection, string key, int value)
{
var param = new NpgsqlParameter <int>(key, NpgsqlDbType.Integer)
{
TypedValue = value
};
collection.Add(param);
}
public static void AddValue(this NpgsqlParameterCollection collection, string key, long value)
{
var param = new NpgsqlParameter <long>(key, NpgsqlDbType.Bigint)
{
TypedValue = value
};
collection.Add(param);
}
public static void AddValue(this NpgsqlParameterCollection collection, string key, decimal value)
{
var param = new NpgsqlParameter <decimal>(key, NpgsqlDbType.Numeric)
{
TypedValue = value
};
collection.Add(param);
}
private static void AddParameters(this NpgsqlParameterCollection sqlparam, Dictionary <string, object> vals)
{
foreach (KeyValuePair <string, object> kvp in vals)
{
if (kvp.Value != null)
{
AddParameter(sqlparam, "@v_" + kvp.Key, kvp.Value);
}
}
}
public override void AddParameters(NpgsqlParameterCollection collection)
{
base.AddParameters(collection);
collection.AddValue("@p2_1", OperationType);
collection.AddValue("@p2_2", From, 16);
collection.AddValue("@p2_3", To, 16);
collection.AddValue("@p2_4", Quantity);
collection.AddValue("@p2_5", AssetNum);
collection.AddValue("@p2_6", Memo);
}
private string GetUniqueParameterName(NpgsqlParameterCollection parameters, string Prefix, int InitialPostfix){
int Postfix = InitialPostfix;
string ReturnValue = String.Empty;
bool IsInside = true;
while(IsInside){
ReturnValue = String.Concat(Prefix, Postfix.ToString());
if(parameters == null)
break;
IsInside = parameters.Contains(ReturnValue);
Postfix++;
}
return ReturnValue;
}
public static NpgsqlParameterCollection AddWithValue(
this NpgsqlParameterCollection parameters,
string parameterName,
NpgsqlDbType dbType,
object value)
{
var parameter = new NpgsqlParameter(parameterName, dbType);
parameter.Value = value;
parameters.Add(parameter);
parameter.ResetDbType();
return(parameters);
}
public static List <MWithdrawal> WithdrawalRequest(NpgsqlConnection con, NpgsqlCommand command, string query)
{
List <MWithdrawal> withdrawalRequest = new List <MWithdrawal>();
//Get Given Parameters Collection
NpgsqlParameterCollection parameters = command.Parameters;
//Pass Parameter Collection into Current parameter
NpgsqlCommand cmd = new NpgsqlCommand(query, con);
foreach (NpgsqlParameter parameter in parameters)
{
cmd.Parameters.AddWithValue(parameter.ParameterName, parameter.NpgsqlValue);
}
//Fetch Withdrawal Table
DataTable dt = Classes.DB.Select(con, query, cmd);
if (dt.Rows.Count > 0)
{
foreach (var item in dt.AsEnumerable())
{
var i = item.Field <int?>("id");
if (i != null)
{
var wr = new MWithdrawal();
wr.Id = item.Field <int>("id");
wr.WalletId = item.Field <string>("wallet_id");
wr.UserId = item.Field <int>("user_id");
wr.ClientName = item.Field <string>("client_name");
wr.DocumentId = item.Field <string>("document_id");
wr.Status = item.Field <string>("credited_status");
wr.DocumentStatus = (DocumentStatus)item.Field <int>("document_status");
wr.DocuLink = item.Field <string>("document_link");
wr.CreatedDate = item.Field <DateTime>("created_date");
wr.Amount = item.Field <decimal>("amount");
wr.UsdConversion = item.Field <decimal?>("usd_conversion");
wr.ServiceFee = item.Field <decimal?>("service_fee");
wr.refference_hash = !string.IsNullOrEmpty(item.Field <string>("refference_hash")) ? item.Field <string>("refference_hash") : "";
wr.transaction_currency = !string.IsNullOrEmpty(item.Field <string>("transaction_currency")) ? item.Field <string>("transaction_currency") : "";
wr.html = !string.IsNullOrEmpty(item.Field <string>("html")) ? item.Field <string>("html") : "";
wr.signature = !string.IsNullOrEmpty(item.Field <string>("signature")) ? item.Field <string>("signature") : "";
wr.is_sign = !string.IsNullOrEmpty(item.Field <string>("is_sign")) ? item.Field <string>("is_sign") : "NO";
withdrawalRequest.Add(wr);
}
}
}
return(withdrawalRequest.OrderByDescending(o => o.CreatedDate).ToList());;
}
public static NpgsqlParameterCollection AddSlidingExpirationInSeconds(
this NpgsqlParameterCollection parameters,
TimeSpan?value)
{
if (value.HasValue)
{
return(parameters.AddParamWithValue(
Columns.Names.SlidingExpirationInSeconds, NpgsqlTypes.NpgsqlDbType.Double, value.Value.TotalSeconds));
}
else
{
return(parameters.AddParamWithValue(Columns.Names.SlidingExpirationInSeconds, NpgsqlTypes.NpgsqlDbType.Double, DBNull.Value));
}
}
public static NpgsqlParameter AddWithNullableValue(
this NpgsqlParameterCollection collection,
string parameterName,
object?value)
{
if (value == null)
{
return(collection.AddWithValue(parameterName, DBNull.Value));
}
else
{
return(collection.AddWithValue(parameterName, value));
}
}
public static void AddWithJsonOrNull <T>(this NpgsqlParameterCollection parameters, string name, T value)
{
if (value == null)
{
parameters.AddWithValue(name, DBNull.Value);
}
else
{
parameters.Add(new NpgsqlParameter(name, NpgsqlDbType.Jsonb)
{
Value = value
});
}
}
public static NpgsqlParameterCollection AddAbsoluteExpiration(
this NpgsqlParameterCollection parameters,
DateTimeOffset?utcTime)
{
if (utcTime.HasValue)
{
return(parameters.AddParamWithValue(
Columns.Names.AbsoluteExpiration, NpgsqlTypes.NpgsqlDbType.TimestampTz, utcTime.Value));
}
else
{
return(parameters.AddParamWithValue(
Columns.Names.AbsoluteExpiration, NpgsqlTypes.NpgsqlDbType.TimestampTz, DBNull.Value));
}
}
public static NpgsqlParameterCollection AddCacheItemValue(this NpgsqlParameterCollection parameters, byte[] value)
{
if (value != null && value.Length < DefaultValueColumnWidth)
{
return(parameters.AddParamWithValue(
Columns.Names.CacheItemValue,
NpgsqlTypes.NpgsqlDbType.Bytea,
DefaultValueColumnWidth,
value));
}
else
{
// do not mention the size
return(parameters.AddParamWithValue(Columns.Names.CacheItemValue, NpgsqlTypes.NpgsqlDbType.Bytea, value));
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query, a <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>, and the <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>.
/// </summary>
/// <param name="cmdText">The text of the query.</param>
/// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
/// <param name="transaction">The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see> in which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.</param>
public NpgsqlCommand(String cmdText, NpgsqlConnection connection, NpgsqlTransaction transaction)
{
parameters = new NpgsqlParameterCollection();
planName = String.Empty;
text = cmdText;
this.connection = connection;
if (this.connection != null)
{
this.m_Connector = connection.Connector;
}
type = CommandType.Text;
this.Transaction = transaction;
SetCommandTimeout();
}
/// <summary>
/// Used to execute internal commands.
/// </summary>
internal NpgsqlCommand(string cmdText, NpgsqlConnector connector)
{
parameters = new NpgsqlParameterCollection();
planName = string.Empty;
text = cmdText;
this.m_Connector = connector;
type = CommandType.Text;
// Removed this setting. It was causing too much problem.
// Do internal commands really need different timeout setting?
// Internal commands aren't affected by command timeout value provided by user.
// timeout = 20;
}
public NpgsqlParametersEditor(Type type) : base(type){
this.parameters = null;
}
public override object EditValue(System.ComponentModel.ITypeDescriptorContext context, IServiceProvider provider, object value) {
this.parameters = value as NpgsqlParameterCollection;
return base.EditValue (context, provider, value);
}
public void SetUp()
{
_queries = new List<QueryDetails>();
_params = new NpgsqlParameterCollection();
}
public void SetUp()
{
_queries = new List<NpgsqlStatement>();
_params = new NpgsqlParameterCollection();
}
