/// <summary>
/// Adds a nullable value to the end of the <see cref="System.Data.SqlClient.SqlParameterCollection" />
/// </summary>
/// <param name="parameters">The <see cref="System.Data.SqlClient.SqlParameterCollection" /> to add the value</param>
/// <param name="parameterName">The name of the parameter</param>
/// <param name="value">The nullable value to be added</param>
public static void AddWithNullableValue(this MySqlParameterCollection parameters, string parameterName, object value)
{
if (value == null || (value is string && (string.IsNullOrEmpty((string)value))))
{
parameters.AddWithValue(parameterName, DBNull.Value);
}
else
{
parameters.AddWithValue(parameterName, value);
}
}
public static void AddWithNullableValue(this MySqlParameterCollection collection, string parameterName, object value)
{
if (value == null)
{
collection.AddWithValue(parameterName, DBNull.Value);
return;
}
var type = value.GetType();
var defaultValue = type.IsValueType ? Activator.CreateInstance(type) : null;
collection.AddWithValue(parameterName, value.Equals(defaultValue) ? DBNull.Value : value);
}
public static MySqlParameterCollection AddSlidingExpirationInSeconds(
this MySqlParameterCollection parameters,
TimeSpan?value)
{
if (value.HasValue)
{
return(parameters.AddWithValue(
Columns.Names.SlidingExpirationInSeconds, MySqlDbType.Int64, value.Value.TotalSeconds));
}
else
{
return(parameters.AddWithValue(Columns.Names.SlidingExpirationInSeconds, MySqlDbType.Int64, DBNull.Value));
}
}
public static MySqlParameterCollection AddAbsoluteExpirationMono(
this MySqlParameterCollection parameters,
DateTimeOffset?utcTime)
{
if (utcTime.HasValue)
{
return(parameters.AddWithValue(
Columns.Names.AbsoluteExpiration, MySqlDbType.DateTime, utcTime.Value.UtcDateTime));
}
else
{
return(parameters.AddWithValue(
Columns.Names.AbsoluteExpiration, MySqlDbType.DateTime, DBNull.Value));
}
}
private static void AddParameters(MySqlParameterCollection sqlParams, params object[] parameters)
{
for (int i = 0; i < parameters.Length; i++)
{
sqlParams.AddWithValue($"@{i}", parameters[i]);
}
}
private static void MapParameter(this MySqlParameterCollection param, Dictionary <string, Object> dic)
{
foreach (KeyValuePair <string, Object> entry in dic)
{
param.AddWithValue(entry.Key, dic[entry.Key] ?? DBNull.Value);
}
}
public static MySqlParameterCollection AddCacheItemValue(this MySqlParameterCollection parameters, byte[] value)
{
if (value != null && value.Length < DefaultValueColumnWidth)
{
return(parameters.AddWithValue(
Columns.Names.CacheItemValue,
MySqlDbType.VarBinary,
DefaultValueColumnWidth,
value));
}
else
{
// do not mention the size
return(parameters.AddWithValue(Columns.Names.CacheItemValue, MySqlDbType.VarBinary, value));
}
}
/// <summary>
/// A MySqlParameterCollection extension method that adds a range with value to 'values'.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="values">The values.</param>
public static void AddRangeWithValue(this MySqlParameterCollection @this, Dictionary <string, object> values)
{
foreach (var keyValuePair in values)
{
@this.AddWithValue(keyValuePair.Key, keyValuePair.Value);
}
}
private static void SetupParameters(MySqlParameterCollection collection, params object[] parameters)
{
for (int i = 0; i < parameters.Length; i++)
{
collection.AddWithValue($"@{i}", parameters[i]);
}
}
public void ReturnDerivedTypes()
{
using MySqlTransaction transaction = m_database.Connection.BeginTransaction();
using MySqlCommand command = m_database.Connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = "select @param + @param2";
MySqlParameter parameter = command.CreateParameter();
parameter.ParameterName = "param";
parameter.Value = 1;
MySqlParameterCollection parameterCollection = command.Parameters;
parameterCollection.Add(parameter);
MySqlParameter parameter2 = parameterCollection.AddWithValue("param2", 2);
MySqlParameter parameterB = parameterCollection[0];
Assert.Same(parameter, parameterB);
MySqlParameter parameter2B = parameterCollection["param2"];
Assert.Same(parameter2, parameter2B);
using (MySqlDataReader reader = command.ExecuteReader())
{
Assert.True(reader.Read());
Assert.Equal(3L, Convert.ToInt64(reader.GetValue(0)));
Assert.False(reader.Read());
}
transaction.Rollback();
}
private String SetParameter(T entity, List <FieldInfo> pis, MySqlParameterCollection parameter, bool notflag)
{
StringBuilder where = new StringBuilder();
foreach (FieldInfo pi in pis)
{
if (where.Length == 0)
{
where.Append(" WHERE ");
}
else
{
where.Append(" AND ");
}
Column cn = pi.GetCustomAttribute(typeof(Column)) as Column;
String temp = "@" + cn.ColumnName;
where = where.Append(" ").Append(cn.ColumnName);
if (notflag)
{
where = where.Append(" <> ");
}
else
{
where = where.Append(" = ");
}
where = where.Append(temp);
parameter.AddWithValue(temp, cn.ColumnType).Value = pi.GetValue(entity);
}
return(where.ToString());
}
private static void AddQueryParameters(ReadCommand command, MySqlParameterCollection parameters)
{
var pkNames = GetPkNames(command.TableSchema);
for (var i = 0; i < pkNames.Count; i++)
{
parameters.AddWithValue($"from_{i}", command.FromPrimaryKey[i]);
}
if (command.ToPrimaryKey != null)
{
for (var i = 0; i < pkNames.Count; i++)
{
parameters.AddWithValue($"to_{i}", command.ToPrimaryKey[i]);
}
}
}
public void Bug563(string sql)
{
var parameters = new MySqlParameterCollection();
parameters.AddWithValue("@b", 123);
var parsedSql = GetParsedSql(sql, parameters);
Assert.Equal(sql.Replace("@b", "123"), parsedSql);
}
public void QuotedParameters(string sql, string parameterName)
{
var parameters = new MySqlParameterCollection();
parameters.AddWithValue(parameterName, 123);
var parsedSql = GetParsedSql(sql, parameters);
Assert.Equal("SELECT 123;", parsedSql);
}
public void Bug589(string sql)
{
var parameters = new MySqlParameterCollection();
parameters.AddWithValue("@foo", 22);
var parsedSql = GetParsedSql(sql, parameters, StatementPreparerOptions.AllowUserVariables);
Assert.Equal(sql.Replace("@foo", "22") + ";", parsedSql);
}
/// <summary>
/// Adds a parameter and its value if it does not exist in the parameter collection;
/// otherwiese it overwrites the current value.
/// </summary>
/// <param name="parameters">The <see cref="System.Data.SqlClient.SqlParameterCollection" /> to add or set the value</param>
/// <param name="parameterName">The name of the parameter</param>
/// <param name="value">The nullable value to be added</param>
public static void AddOrSetWithValue(this MySqlParameterCollection parameters, string parameterName, object value)
{
if (parameters.Contains(parameterName))
{
parameters[parameterName].Value = value;
}
else
{
parameters.AddWithValue(parameterName, value);
}
}
private static void FillParameters(MySqlParameterCollection parameters, BaseModel model)
{
PropertyInfo[] pinfos = model.GetType().GetProperties();
pinfos.ToList().ForEach(pi =>
{
Attribute sqlParamName = pi.GetCustomAttribute(typeof(SqlParameterNameAttribute));
if (sqlParamName != null)
{
SqlParameterNameAttribute sqlParameterNameAttribute = (SqlParameterNameAttribute)sqlParamName;
parameters.AddWithValue(sqlParameterNameAttribute.ParameterName, pi.GetValue(model));
}
});
}
private void AddSqlParamaterValues(MySqlParameterCollection parameters, XmlNode moneyPaymentNode, Dictionary <string, string> logElements, bool updateMode)
{
foreach (KeyValuePair <string, string> kvp in logElements)
{
string parameterName = String.Format("@{0}", kvp.Key);
string parameterValue = GetXmlValueString(moneyPaymentNode, kvp.Value);
//no value then do not add if in update mode
if ((updateMode == false) || (parameterValue.Length > 0))
{
parameters.AddWithValue(parameterName, parameterValue);
}
}
}
private static MySqlParameterCollection RetornaParametros(MySqlParameterCollection pMySql, List <ParametrosBD> param)
{
if (param == null || param.Count == 0 || pMySql == null)
{
return(null);
}
foreach (ParametrosBD p in param)
{
pMySql.AddWithValue(p.NomeParametro, p.ValorParametro);
}
return(pMySql);
}
private void button1_Click(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(txtItemId.Text))
{
//paramCollection.Add("item_id", txtItemId.Text);
paramCollection.AddWithValue("item_id", txtItemId.Text);
}
/*paramCollection.Add("item_name", txtItemName.Text);
* paramCollection.Add("description", txtDescription.Text);*/
BuildSQL();
//searchSql = searchSql + " WHERE item_id = '" + txtItemId.Text + "';";
//this.DialogResult = System.Windows.Forms.DialogResult.OK;
//this.Close();
}
GetMySqlConnectionAndCommand
(
String connectionString,
String storedProcedureName,
Int32 commandTimeoutSeconds,
out MySqlConnection mySqlConnection,
out MySqlCommand mySqlCommand,
params Object [] nameValuePairs
)
{
Debug.Assert(!String.IsNullOrEmpty(connectionString));
Debug.Assert(!String.IsNullOrEmpty(storedProcedureName));
Debug.Assert(commandTimeoutSeconds > 0);
Debug.Assert(nameValuePairs.Length % 2 == 0);
mySqlConnection = new MySqlConnection(connectionString);
mySqlCommand = new MySqlCommand(storedProcedureName, mySqlConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.CommandTimeout = commandTimeoutSeconds;
MySqlParameterCollection parameters = mySqlCommand.Parameters;
for (Int32 i = 0; i < nameValuePairs.Length; i += 2)
{
Debug.Assert(nameValuePairs[i + 0] is String);
String name = (String)nameValuePairs[i + 0];
Debug.Assert(!String.IsNullOrEmpty(name));
Debug.Assert(name[0] == '@');
Object value = nameValuePairs[i + 1];
if (value == null)
{
// You cannot set a MySqlParameter to null. It must be
// DBNull.Value.
value = DBNull.Value;
}
parameters.AddWithValue(name, value);
}
}
public void AddWithValue(string key, object value, bool check)
{
if (value == null)
{
value = DBNull.Value;
}
if (check && !SqlHelper.CheckSqlParameter(value.ToString()))
{
throw new SqlParameterException(key, value.ToString());
}
// Need to prevent adding a duplicate key
int index = pm.IndexOf(key);
if (index == -1)
{
pm.AddWithValue(key, value);
}
else
{
pm[index].Value = value;
}
}
public void ajouteparametreCol(string nompara, object valeur)
{
col.AddWithValue(nompara, valeur);
}
public static void AddParameter(this MySqlParameterCollection mysqlparam, string key, object value)
{
var t = value?.GetType();
if (t == typeof(Vector3))
{
var v = (Vector3)value;
mysqlparam.AddWithValue(key + "X", v.X);
mysqlparam.AddWithValue(key + "Y", v.Y);
mysqlparam.AddWithValue(key + "Z", v.Z);
}
else if (t == typeof(GridVector))
{
var v = (GridVector)value;
mysqlparam.AddWithValue(key + "X", v.X);
mysqlparam.AddWithValue(key + "Y", v.Y);
}
else if (t == typeof(Quaternion))
{
var v = (Quaternion)value;
mysqlparam.AddWithValue(key + "X", v.X);
mysqlparam.AddWithValue(key + "Y", v.Y);
mysqlparam.AddWithValue(key + "Z", v.Z);
mysqlparam.AddWithValue(key + "W", v.W);
}
else if (t == typeof(Color))
{
var v = (Color)value;
mysqlparam.AddWithValue(key + "Red", v.R);
mysqlparam.AddWithValue(key + "Green", v.G);
mysqlparam.AddWithValue(key + "Blue", v.B);
}
else if (t == typeof(ColorAlpha))
{
var v = (ColorAlpha)value;
mysqlparam.AddWithValue(key + "Red", v.R);
mysqlparam.AddWithValue(key + "Green", v.G);
mysqlparam.AddWithValue(key + "Blue", v.B);
mysqlparam.AddWithValue(key + "Alpha", v.A);
}
else if (t == typeof(EnvironmentController.WLVector2))
{
var vec = (EnvironmentController.WLVector2)value;
mysqlparam.AddWithValue(key + "X", vec.X);
mysqlparam.AddWithValue(key + "Y", vec.Y);
}
else if (t == typeof(EnvironmentController.WLVector4))
{
var vec = (EnvironmentController.WLVector4)value;
mysqlparam.AddWithValue(key + "Red", vec.X);
mysqlparam.AddWithValue(key + "Green", vec.Y);
mysqlparam.AddWithValue(key + "Blue", vec.Z);
mysqlparam.AddWithValue(key + "Value", vec.W);
}
else if (t == typeof(bool))
{
mysqlparam.AddWithValue(key, (bool)value ? 1 : 0);
}
else if (t == typeof(UUID) || t == typeof(UGUI) || t == typeof(UGUIWithName) || t == typeof(UGI) || t == typeof(Uri) || t == typeof(UEI))
{
mysqlparam.AddWithValue(key, value.ToString());
}
else if (t == typeof(ParcelID))
{
UUID i = new UUID(((ParcelID)value).GetBytes(), 0);
mysqlparam.AddWithValue(key, i.ToString());
}
else if (t == typeof(AnArray))
{
using (var stream = new MemoryStream())
{
LlsdBinary.Serialize((AnArray)value, stream);
mysqlparam.AddWithValue(key, stream.ToArray());
}
}
else if (t == typeof(Date))
{
mysqlparam.AddWithValue(key, ((Date)value).AsULong);
}
else if (t.IsEnum)
{
mysqlparam.AddWithValue(key, Convert.ChangeType(value, t.GetEnumUnderlyingType()));
}
else
{
mysqlparam.AddWithValue(key, value);
}
}
/// <summary>
/// Metodo que vai adicionar um paramentro a coleção;
/// </summary>
/// <param name="name">nome do parametro</param>
/// <param name="value">Valor do parametro</param>
public void AddParameter(string name, object value)
{
parametros.AddWithValue(name, value);
}
/// <summary>
/// Adds a parameter to the parameterized MySQL query.
/// Use this before executing the query.
/// </summary>
/// <param name="name">The name of the parameter</param>
/// <param name="param">The value of the parameter</param>
public static void AddParams(string name, object param)
{
parameters.AddWithValue(name, param);
}
public static MySqlParameterCollection AddCacheItemId(this MySqlParameterCollection parameters, string value)
{
return(parameters.AddWithValue(Columns.Names.CacheItemId, MySqlDbType.VarChar, CacheItemIdColumnWidth, value));
}
//Metodo Para criar parametros(aqui a coleção de parametro recebe um novo parametro (no caos uma string que sera usada em caso de string de sql
//e um object que é se você for usar uma procedure))
public void AdicionarParametros(string nomeParametro, object valorParametro)
{
mySqlParameterCollection.AddWithValue(nomeParametro, valorParametro);
}
public static void AddWithValueCheckNull(this MySqlParameterCollection parameters, string parameterName, object value)
{
parameters.AddWithValue(parameterName, value == null ? DBNull.Value : value);
}
public static MySqlParameterCollection AddExpiresAtTimeMono(
this MySqlParameterCollection parameters,
DateTimeOffset utcTime)
{
return(parameters.AddWithValue(Columns.Names.ExpiresAtTime, MySqlDbType.DateTime, utcTime.UtcDateTime));
}