/// <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(); }