public void SetCommandParameter(string text, IDataParameterCollection Parameters, object entity = null)
{
var s = Regex.Matches(text, "@\\w+(?=[\\s|,|)])|@\\w+$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
var d = new DynamicHandlerCompiler <object>(entity);
foreach (Match a in s)
{
var name = a.Value.Replace("@", string.Empty);
if (this.where != null)
{
if (where.Contains(name))
{
var p = CreaterParamger(a.Value, where.GetValue(name)) as IDataParameter;
Parameters.Add(p);
continue;
}
}
if (entity != null)
{
var f = d.CreaterGetPropertyHandler <dynamic>(name);
if (Parameters.Contains(a.Value))
{
(Parameters[a.Value] as DbParameter).Value = f(entity) ?? string.Empty;
}
else
{
Parameters.Add(CreaterParamger(a.Value, f(entity) ?? string.Empty) as IDataParameter);
}
}
}
}
protected override void MapToParameters(Role value, IDataParameterCollection parameters)
{
if ((value != null) && (parameters != null))
{
try
{
parameters.Add(new SqlParameter("@Id", SqlDbType.Int)
{
Value = value.Id
});
parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar)
{
Value = value.Name
});
parameters.Add(new SqlParameter("@LocalizedName", SqlDbType.NVarChar, 50)
{
Value = value.LocalizedName
});
}
catch (Exception e)
{
throw new NoteDataException(e.Message);
}
}
else
{
throw new NoteArgumentException("Invalid parameter value");
}
}
public static void TranslateParameters(IDataParameterCollection parameterList, string outputManagerID, bool mainService, string URL)
{
IDbDataParameter dbParameter;
dbParameter = new OracleParameter();
dbParameter.ParameterName = "OMID_I";
dbParameter.DbType = DbTypeConvertor.ConvertToDbType(typeof(string));
dbParameter.Direction = ParameterDirection.Input;
dbParameter.Value = outputManagerID;
parameterList.Add(dbParameter);
dbParameter = new OracleParameter();
dbParameter.ParameterName = "URL_TYPE_I";
dbParameter.DbType = DbTypeConvertor.ConvertToDbType(typeof(string));
dbParameter.Direction = ParameterDirection.Input;
if (mainService)
{
dbParameter.Value = "MAIN";
}
else
{
dbParameter.Value = "FALLBACK";
}
parameterList.Add(dbParameter);
dbParameter = new OracleParameter();
dbParameter.ParameterName = "URL_I";
dbParameter.DbType = DbTypeConvertor.ConvertToDbType(typeof(string));
dbParameter.Direction = ParameterDirection.Input;
dbParameter.Value = URL;
parameterList.Add(dbParameter);
}
/// <summary>
/// Ajoute un paramètre la collection.
/// </summary>
/// <param name="parameter">Nouveau paramètre.</param>
/// <returns>Paramètre ajouté.</returns>
public SqlServerParameter Add(SqlServerParameter parameter)
{
if (parameter == null)
{
throw new ArgumentNullException("parameter");
}
_list.Add(parameter);
_innerCollection.Add(parameter.InnerParameter);
return(parameter);
}
protected override void MapToParameters(NoteReference value, IDataParameterCollection parameters)
{
parameters.Add(new SqlParameter("@Id", SqlDbType.Int)
{
Value = value.Id, Direction = ParameterDirection.InputOutput
});
parameters.Add(new SqlParameter("@NoteId", SqlDbType.Int)
{
Value = value.NoteId
});
parameters.Add(new SqlParameter("@ReferenceId", SqlDbType.Int)
{
Value = value.ReferenceId
});
}
protected virtual IDataParameter AddParameter(IDataParameterCollection parameters, ref int parameterCounter, object parameterValue, Type propertyType, int size)
{
IDataParameter parameter = CreateParameter(CreateParameterName(parameterCounter++), parameterValue, propertyType, size);
parameters.Add(parameter);
return(parameter);
}
public int AddParameter(IDbConnection dbConnection, IDataParameterCollection parameterCollection, string parameterName, object value,
DbType?dbType = null, ParameterDirection?direction = null, int?size = null, byte?precision = null,
byte?scale = null)
{
var param = CreateParameter(dbConnection);
param.ParameterName = parameterName;
if (dbType != null)
{
param.DbType = dbType.Value;
}
if (direction != null)
{
param.Direction = direction.Value;
}
if (size != null)
{
param.Size = size.Value;
}
if (precision != null)
{
param.Precision = precision.Value;
}
if (scale != null)
{
param.Scale = scale.Value;
}
param.Value = value;
return(parameterCollection.Add(param));
}
public static void AddRange(this IDataParameterCollection collection, IEnumerable <IDataParameter> newItems)
{
foreach (IDataParameter item in newItems)
{
collection.Add(item);
}
}
public int Add(object value)
{
object parameter = value;
if (parameter is IDbDataParameter)
{
object obj = ((IDbDataParameter)parameter).Value;
if (obj != null)
{
var type = obj.GetType();
if (type == typeof(XmlDocument))
{
((IDbDataParameter)parameter).Value = Db2DataParameterCollection.ToArrayBytes((XmlDocument)obj);
}
else if (type == typeof(XmlDeclaration))
{
return(-1); // skip for Dapper
}
else if (type == typeof(XmlElement))
{
((IDbDataParameter)parameter).Value = Db2DataParameterCollection.ToArrayBytes(((XmlElement)obj).OwnerDocument); // compatible with Dapper
}
}
}
return(_parameters.Add(parameter));
}
/// <summary>
/// Builds platform specific parameters.
/// </summary>
/// <returns></returns>
public void CopyToDataParameterCollection(IDataParameterCollection targetParameterCollection)
{
foreach (MySqlParameter mySqlParameter in Parameters)
{
targetParameterCollection.Add(mySqlParameter);
}
}
public override T ExecuteScalar <T>(string query, IDictionary <string, object> parameters = null)
{
IDbConnection connection = OpenConnection();
object result = null;
using (IDbCommand command = connection.CreateCommand())
{
if (IsTransaction)
{
command.Transaction = Transaction;
}
command.CommandText = query;
if (parameters != null)
{
IDataParameterCollection commandParameters = command.Parameters;
if (parameters != null)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
commandParameters.Add(CreateParameter(parameter.Key, parameter.Value));
}
}
}
result = command.ExecuteScalar();
}
if (!IsTransaction)
{
connection.Close();
}
return(result != null ? (T)result : default);
internal static void AddRange(this IDataParameterCollection parameterCollection, DbParameter[] parameters)
{
foreach (DbParameter parameter in parameters)
{
parameterCollection.Add(parameter);
}
}
public static void AddRange(this IDataParameterCollection dataParameterCollection, IEnumerable <IDbDataParameter> parameters)
{
foreach (var parameter in parameters)
{
dataParameterCollection.Add(parameter);
}
}
public void PopulateParameters(IConnection connection, IDataParameterCollection parameters, ColumnValueCollection columnValues, int parameterNameSuffix)
{
foreach (IColumnValue Fld in columnValues)
{
if (Fld.ValueCanBeParameter)
{
var Param = connection.Factory.Driver.GetParameter();
Param.ParameterName = "@" + this.GetParameterName(Fld.ColumnIdentifier, parameterNameSuffix);
if (Fld.Value is DbDateTime && Fld.Value != null)
{
Param.Value = ((DbDateTime)(Fld.Value)).Value;
}
else if (Fld.Value != null && Fld.Value.GetType().IsEnum)
{
Param.Value = System.Convert.ToInt32(Fld.Value);
}
else
{
Param.Value = Fld.Value;
}
if (Fld.DataType == Lazaro.Orm.ColumnTypes.Blob)
{
Param.DbType = System.Data.DbType.Binary;
}
// FIXME: no debería hacer una excepción para OdbcDriver
if (connection.Factory.Driver is OdbcDriver && Fld.DataType == Lazaro.Orm.ColumnTypes.Blob)
{
((System.Data.Odbc.OdbcParameter)Param).OdbcType = System.Data.Odbc.OdbcType.VarBinary;
}
parameters.Add(Param);
}
}
}
public static void AddRange(this IDataParameterCollection col, Array args)
{
foreach (var item in args)
{
col.Add(item);
}
}
public int AddParameterValue(IDbConnection dbConnection, IDataParameterCollection parameterCollection, string parameterName, object value)
{
var param = CreateParameter(dbConnection);
param.ParameterName = parameterName;
param.Value = value;
return(parameterCollection.Add(param));
}
public static int AddWithValue(this IDataParameterCollection Parameters, string name, object value)
{
IDbDataParameter Parameter = Factory.CreateDataParameter(Parameters);
Parameter.ParameterName = name;
Parameter.Value = value;
return(Parameters.Add(Parameter));
}
protected override void MapToParameters(Category value, IDataParameterCollection parameters)
{
try
{
parameters.Add(new SqlParameter("@Id", SqlDbType.Int)
{
Value = value.Id
});
parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar)
{
Value = value.Name
});
}
catch (Exception e)
{
throw new NoteDataException(e.Message);
}
}
protected override void MapToParameters(UserRole value, IDataParameterCollection parameters)
{
if (value != null)
{
parameters.Add(new SqlParameter("@Id", SqlDbType.Int)
{
Value = value.Id
});
parameters.Add(new SqlParameter("@UserId", SqlDbType.Int)
{
Value = value.UserId
});
parameters.Add(new SqlParameter("@RoleId", SqlDbType.Int)
{
Value = value.RoleId
});
}
}
public static void AddRange <T>(this IDataParameterCollection collection, IEnumerable <T> collectionToAdd)
{
var toAdd = collectionToAdd as IList <T> ?? collectionToAdd.ToList();
for (var i = 0; i < toAdd.ToList().Count(); i++)
{
collection.Add(toAdd[i]);
}
}
/// <summary>
///
/// </summary>
/// <param name="parameterCollection"></param>
/// <param name="parameterName"></param>
/// <param name="parameterValue"></param>
public static void AddWithValue <T>(this IDataParameterCollection parameterCollection,
string parameterName, object parameterValue) where T : IDataParameter, new()
{
parameterCollection.Add(new T
{
ParameterName = parameterName,
Value = parameterValue
});
}
internal void Inject(ref DbCommand command)
{
IDataParameterCollection pCol = ((DbCommand)command).Parameters;
foreach (DbParameter p in this._lst)
{
pCol.Add(p);
}
}
StringBuilder BuildWhereSql(PanelContext data, IDataParameterCollection parameters, string prefix)
{
StringBuilder sqlwehre = new StringBuilder();
for (int i = 0; i < data.QueryFields.Count; i++)
{
QueryField qf = data.QueryFields[i];
if (i == 0)
{
sqlwehre.Append(" WHERE ");
}
else
{
sqlwehre.Append(" AND ");
//sqlwehre.Append(fd.Operator == QueryMode.OR ? " OR " : " AND ");
}
string paramname = String.Format("@{0}{1}", prefix, qf.Column.Name);
switch (qf.Operator)
{
case OperationType.EQUER:
sqlwehre.AppendFormat(" {0}={1} ", qf.Column.Name, paramname);
break;
case OperationType.NOTEQUER:
sqlwehre.AppendFormat(" {0}<>{1} ", qf.Column.Name, paramname);
break;
case OperationType.LIKE:
sqlwehre.AppendFormat(" {0} like '%'+{1}+'%' ", qf.Column.Name, paramname);
break;
case OperationType.LESSTHAN:
sqlwehre.AppendFormat(" {0}<{1} ", qf.Column.Name, paramname);
break;
case OperationType.MORETHAN:
sqlwehre.AppendFormat(" {0}>{1} ", qf.Column.Name, paramname);
break;
case OperationType.LESSTHANEQURE:
sqlwehre.AppendFormat(" {0}<={1} ", qf.Column.Name, paramname);
break;
case OperationType.MORETHANEQURE:
sqlwehre.AppendFormat(" {0}>={1} ", qf.Column.Name, paramname);
break;
default:
sqlwehre.AppendFormat(" {0}={1} ", qf.Column.Name, paramname);
break;
}
parameters.Add(new MySqlParameter(paramname, qf.Value));
}
return(sqlwehre);
}
public static void TranslateParameters(IDataParameterCollection parameterList, string outputManagerID, string errorMessage)
{
IDbDataParameter dbParameter;
dbParameter = new OracleParameter();
dbParameter.ParameterName = "OMID_I";
dbParameter.DbType = DbTypeConvertor.ConvertToDbType(typeof(string));
dbParameter.Direction = ParameterDirection.Input;
dbParameter.Value = outputManagerID;
parameterList.Add(dbParameter);
dbParameter = new OracleParameter();
dbParameter.ParameterName = "ERROR_MSG_I";
dbParameter.DbType = DbTypeConvertor.ConvertToDbType(typeof(string));
((OracleParameter)dbParameter).OracleDbType = OracleDbType.Clob;
dbParameter.Direction = ParameterDirection.Input;
dbParameter.Value = errorMessage;
parameterList.Add(dbParameter);
}
public static void AddRange(this IDataParameterCollection collection, IEnumerable <IDataParameter> parameters)
{
Ensure.IsNotNull(collection);
Ensure.IsNotNull(parameters);
foreach (IDataParameter item in parameters)
{
collection.Add(item);
}
}
/// <summary>
/// 批量添加命令参数
/// </summary>
public static void AddRange(this IDataParameterCollection source, IEnumerable <IDbDataParameter> parameters)
{
if (parameters != null)
{
foreach (var p in parameters)
{
source.Add(p);
}
}
}
public static IDataParameter Add(this IDataParameterCollection paramCollection, IDbCommand cmd, string name)
{
var param = cmd.CreateParameter();
param.ParameterName = name;
paramCollection.Add(param);
return(param);
}
public IDbDataParameter Add(string paramName, Enum paramType, int paramSize, ParameterDirection paramDirection, byte precision, byte scale, string sourceColumn, object paramValue)
{
IDbDataParameter param = _command.CreateParameter();
param.ParameterName = paramName;
if (paramType is DbType)
{
param.DbType = (DbType)paramType;
}
param.Direction = paramDirection;
if (sourceColumn != null && sourceColumn != string.Empty)
{
param.SourceColumn = sourceColumn;
}
if (paramSize > 0)
{
param.Size = paramSize;
}
if (precision > 0)
{
param.Precision = precision;
}
if (scale > 0)
{
param.Scale = scale;
}
if (paramValue != null && param.Value != DBNull.Value)
{
param.Value = paramValue;
}
_parameters.Add(param);
return(param);
}
public static IDataParameterCollection AddRange(this IDataParameterCollection parameterCollection, params IDbDataParameter[] parameters)
{
if (parameters != null)
{
foreach (var p in parameters)
{
parameterCollection.Add(p);
}
}
return(parameterCollection);
}
/// <summary>
/// Adds the range.
/// </summary>
/// <param name="collection">The collection.</param>
/// <param name="parameters">The parameters.</param>
public static void AddRange(this IDataParameterCollection collection, IEnumerable <IDataParameter> parameters)
{
if (parameters == null)
{
return;
}
foreach (var parameter in parameters)
{
collection.Add(parameter);
}
}
/// <summary>
/// Parses parameter names from SQL Statement, assigns values from object array ,
/// and returns fully populated ParameterCollection.
/// </summary>
/// <param name="commandText">Sql Statement with "@param" style embedded parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns>SQLiteParameterCollection</returns>
/// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same
///order as parameter names appear in SQL statement.</remarks>
public static void DeriveParameters(IDataParameterCollection coll, string commandText, object[] paramList)
{
if (paramList == null) return;
string parmString = commandText.Substring(commandText.IndexOf("@"));
// pre-process the string so always at least 1 space after a comma.
parmString = parmString.Replace(",", " ,");
// get the named parameters into a match collection
string pattern = @"(@)\S*(.*?)\b";
Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
MatchCollection mc = ex.Matches(parmString);
string[] paramNames = new string[mc.Count];
int i = 0;
foreach (Match m in mc)
{
paramNames[i] = m.Value;
i++;
}
// now let's type the parameters
int j = 0;
Type t = null;
foreach (object o in paramList)
{
t = o.GetType();
SQLiteParameter parm = new SQLiteParameter();
switch (t.ToString())
{
case ("DBNull"):
case ("Char"):
case ("SByte"):
case ("UInt16"):
case ("UInt32"):
case ("UInt64"):
throw new SystemException("Invalid data type");
case ("System.String"):
parm.DbType = DbType.String;
parm.ParameterName = paramNames[j];
parm.Value = (string)paramList[j];
coll.Add(parm);
break;
case ("System.Byte[]"):
parm.DbType = DbType.Binary;
parm.ParameterName = paramNames[j];
parm.Value = (byte[])paramList[j];
coll.Add(parm);
break;
case ("System.Int32"):
parm.DbType = DbType.Int32;
parm.ParameterName = paramNames[j];
parm.Value = (int)paramList[j];
coll.Add(parm);
break;
case ("System.Boolean"):
parm.DbType = DbType.Boolean;
parm.ParameterName = paramNames[j];
parm.Value = (bool)paramList[j];
coll.Add(parm);
break;
case ("System.DateTime"):
parm.DbType = DbType.DateTime;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDateTime(paramList[j]);
coll.Add(parm);
break;
case ("System.Double"):
parm.DbType = DbType.Double;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDouble(paramList[j]);
coll.Add(parm);
break;
case ("System.Decimal"):
parm.DbType = DbType.Decimal;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDecimal(paramList[j]);
break;
case ("System.Guid"):
parm.DbType = DbType.Guid;
parm.ParameterName = paramNames[j];
parm.Value = (System.Guid)(paramList[j]);
break;
case ("System.Object"):
parm.DbType = DbType.Object;
parm.ParameterName = paramNames[j];
parm.Value = paramList[j];
coll.Add(parm);
break;
default:
throw new SystemException("Value is of unknown data type");
} // end switch
j++;
}
}
