private IParamBuilder InternalCreateLikeSql(TkDbContext context, IFieldInfo fieldName,
string likeValue, string exceptValue)
{
string fieldName1 = fieldName.FieldName + "1";
string fieldName2 = fieldName.FieldName + "2";
fSql = string.Format(ObjectUtil.SysCulture, "{0} LIKE {1} AND {0} <> {2}",
context.EscapeName(fieldName.FieldName), context.GetSqlParamName(fieldName1),
context.GetSqlParamName(fieldName2));
fParams.Add(fieldName1, fieldName.DataType, likeValue);
fParams.Add(fieldName2, fieldName.DataType, exceptValue);
return(this);
}
/// <summary>
/// Executes query stored procedures for 10 lists - Async.
/// </summary>
/// <typeparam name="TFirst">Type of first list</typeparam>
/// <typeparam name="TSecond">Type of second list</typeparam>
/// <typeparam name="TThird">Type of third list</typeparam>
/// <typeparam name="TForth">Type of forth list</typeparam>
/// <typeparam name="TFifth">Type of fifth list</typeparam>
/// <typeparam name="TSixth">Type of sixth list</typeparam>
/// <typeparam name="TSeventh">Type of seventh list</typeparam>
/// <typeparam name="TEighth">Type of eighth list</typeparam>
/// <typeparam name="TNineth">Type of nineth list</typeparam>
/// <typeparam name="TTenth">Type of tenth list</typeparam>
/// <param name="storedProcedureName">Name of the stored procedure</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>QueryMultipleListsReturnItem</returns>
public virtual async Task <QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth> > ExecuteQueryMultipleStoredProcedureAsync <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth>(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true)
{
QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth> returnItem = new QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth>();
IDbParameterList returnParameterList = new DbParameterList();
DynamicParameters parameters = new DynamicParameters();
foreach (DbDataParameter parameter in parametersCollection)
{
parameters.Add(parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput)
{
returnParameterList.Add(parameter);
}
}
if (isReturnValueExists)
{
parameters.Add(ReturnValueParameterName, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue, size: ReturnValueSize);
}
OpenConnectionForSingleTransaction();
using (GridReader gridReader = await ExecuteQueryMultipleAsync(storedProcedureName, CommandType.StoredProcedure, parameters))
{
returnItem.FirstCollection = gridReader.Read <TFirst>();
returnItem.SecondCollection = gridReader.Read <TSecond>();
returnItem.ThirdCollection = gridReader.Read <TThird>();
returnItem.FourthCollection = gridReader.Read <TForth>();
returnItem.FifthCollection = gridReader.Read <TFifth>();
returnItem.SixthCollection = gridReader.Read <TSixth>();
returnItem.SeventhCollection = gridReader.Read <TSeventh>();
returnItem.EighthCollection = gridReader.Read <TEighth>();
returnItem.NinethCollection = gridReader.Read <TNineth>();
returnItem.TenthCollection = gridReader.Read <TTenth>();
}
CloseConnectionForSingleTransaction();
if (returnParameterList.Count > 0)
{
foreach (DbDataParameter parameter in returnParameterList)
{
parameter.Value = GetOutputParameterValue(parameter.ParameterName, parameter.DbType, parameters);
}
returnItem.ReturnParametersCollection = returnParameterList;
}
if (isReturnValueExists)
{
returnItem.ReturnValue = parameters.Get <int>(ReturnValueParameterName);
}
return(returnItem);
}
public IDbDataParameter CreateParam(string paramName, object paramValue, ParameterDirection paraDirect)
{
IDbDataParameter _param = new MySqlParameter();
_param.ParameterName = paramName;
_param.Value = paramValue == null ? DBNull.Value : paramValue;
_param.Direction = paraDirect;
_params.Add(_param);
return(_param);
}
public IDbDataParameter CreateParam(string paramName, object paramValue, ParameterDirection paraDirect)
{
IDbDataParameter dbParam = new SqlParameter();
dbParam.ParameterName = string.Format("@{0}", paramName);
dbParam.Value = paramValue == null ? DBNull.Value : paramValue;;
dbParam.Direction = paraDirect;
_params.Add(dbParam);
return(dbParam);
}
/// <summary>
/// Executes query stored procedures for multiple datasets - Async.
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>QueryMultipleReturnItem</returns>
public virtual async Task <QueryMultipleReturnItem> ExecuteQueryMultipleStoredProcedureAsync(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true)
{
QueryMultipleReturnItem returnItem = new QueryMultipleReturnItem();
IDbParameterList returnParameterList = new DbParameterList();
DynamicParameters parameters = new DynamicParameters();
foreach (DbDataParameter parameter in parametersCollection)
{
parameters.Add(parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput)
{
returnParameterList.Add(parameter);
}
}
if (isReturnValueExists)
{
parameters.Add(ReturnValueParameterName, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue, size: ReturnValueSize);
}
IList <dynamic> returnedLists = new List <dynamic>();
OpenConnectionForSingleTransaction();
using (GridReader gridReader = await ExecuteQueryMultipleAsync(storedProcedureName, CommandType.StoredProcedure, parameters))
{
while (!gridReader.IsConsumed)
{
returnedLists.Add(gridReader.Read());
}
}
CloseConnectionForSingleTransaction();
returnItem.DataLists = returnedLists;
if (returnParameterList.Count > 0)
{
foreach (DbDataParameter parameter in returnParameterList)
{
parameter.Value = GetOutputParameterValue(parameter.ParameterName, parameter.DbType, parameters);
}
returnItem.ReturnParametersCollection = returnParameterList;
}
if (isReturnValueExists)
{
returnItem.ReturnValue = parameters.Get <int>(ReturnValueParameterName);
}
return(returnItem);
}
/// <summary>
/// Executes query sql text for 5 lists.
/// </summary>
/// <typeparam name="TFirst">Type of first list</typeparam>
/// <typeparam name="TSecond">Type of second list</typeparam>
/// <typeparam name="TThird">Type of third list</typeparam>
/// <typeparam name="TForth">Type of forth list</typeparam>
/// <typeparam name="TFifth">Type of fifth list</typeparam>
/// <param name="sqlQueryText">SQL Query Text</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>QueryMultipleListsReturnItem</returns>
public virtual QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth> ExecuteQueryMultipleSqlText <TFirst, TSecond, TThird, TForth, TFifth>(string sqlQueryText, IDbParameterList parametersCollection, bool isReturnValueExists = false)
{
QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth> returnItem = new QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth>();
IDbParameterList returnParameterList = new DbParameterList();
DynamicParameters parameters = new DynamicParameters();
foreach (DbDataParameter parameter in parametersCollection)
{
parameters.Add(parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput)
{
returnParameterList.Add(parameter);
}
}
if (isReturnValueExists)
{
parameters.Add(ReturnValueParameterName, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue, size: ReturnValueSize);
}
OpenConnectionForSingleTransaction();
using (GridReader gridReader = ExecuteQueryMultiple(sqlQueryText, CommandType.Text, parameters))
{
returnItem.FirstCollection = gridReader.Read <TFirst>();
returnItem.SecondCollection = gridReader.Read <TSecond>();
returnItem.ThirdCollection = gridReader.Read <TThird>();
returnItem.FourthCollection = gridReader.Read <TForth>();
returnItem.FifthCollection = gridReader.Read <TFifth>();
}
CloseConnectionForSingleTransaction();
if (returnParameterList.Count > 0)
{
foreach (DbDataParameter parameter in returnParameterList)
{
parameter.Value = GetOutputParameterValue(parameter.ParameterName, parameter.DbType, parameters);
}
returnItem.ReturnParametersCollection = returnParameterList;
}
if (isReturnValueExists)
{
returnItem.ReturnValue = parameters.Get <int>(ReturnValueParameterName);
}
return(returnItem);
}
/// <summary>
/// Executes query stored procedures for item with list.
/// </summary>
/// <typeparam name="TFirst">Type of first item</typeparam>
/// <typeparam name="TSecond">Type of second list</typeparam>
/// <param name="storedProcedureName">Name of the stored procedure</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>QueryMultipleListsReturnItem</returns>
public virtual QueryMultipleSingleAndListReturnItem <TFirst, TSecond> QueryMultipleSingleWithListStoredProcedure <TFirst, TSecond>(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true)
{
QueryMultipleSingleAndListReturnItem <TFirst, TSecond> returnItem = new QueryMultipleSingleAndListReturnItem <TFirst, TSecond>();
IDbParameterList returnParameterList = new DbParameterList();
DynamicParameters parameters = new DynamicParameters();
foreach (DbDataParameter parameter in parametersCollection)
{
parameters.Add(parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput)
{
returnParameterList.Add(parameter);
}
}
if (isReturnValueExists)
{
parameters.Add(ReturnValueParameterName, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue, size: ReturnValueSize);
}
OpenConnectionForSingleTransaction();
using (GridReader gridReader = ExecuteQueryMultiple(storedProcedureName, CommandType.StoredProcedure, parameters))
{
returnItem.FirstItem = gridReader.ReadSingleOrDefault <TFirst>();
returnItem.SecondCollection = gridReader.Read <TSecond>();
}
CloseConnectionForSingleTransaction();
if (returnParameterList.Count > 0)
{
foreach (DbDataParameter parameter in returnParameterList)
{
parameter.Value = GetOutputParameterValue(parameter.ParameterName, parameter.DbType, parameters);
}
returnItem.ReturnParametersCollection = returnParameterList;
}
if (isReturnValueExists)
{
returnItem.ReturnValue = parameters.Get <int>(ReturnValueParameterName);
}
return(returnItem);
}
/// <summary>
/// Executes query stored procedure for a list - Async.
/// </summary>
/// <typeparam name="T">Type of the list of returned model</typeparam>
/// <param name="storedProcedureName">Name of the stored procedure</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>QueryReturnItem</returns>
public virtual async Task <QueryReturnItem <T> > ExecuteQueryStoredProcedureAsync <T>(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true)
{
QueryReturnItem <T> returnItem = new QueryReturnItem <T>();
IDbParameterList returnParameterList = new DbParameterList();
DynamicParameters parameters = new DynamicParameters();
foreach (DbDataParameter parameter in parametersCollection)
{
parameters.Add(parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput)
{
returnParameterList.Add(parameter);
}
}
if (isReturnValueExists)
{
parameters.Add(ReturnValueParameterName, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue, size: ReturnValueSize);
}
OpenConnectionForSingleTransaction();
returnItem.DataItemList = await ExecuteQueryAsync <T>(storedProcedureName, CommandType.StoredProcedure, parameters);
CloseConnectionForSingleTransaction();
if (returnParameterList.Count > 0)
{
foreach (DbDataParameter parameter in returnParameterList)
{
parameter.Value = GetOutputParameterValue(parameter.ParameterName, parameter.DbType, parameters);
}
returnItem.ReturnParametersCollection = returnParameterList;
}
if (isReturnValueExists)
{
returnItem.ReturnValue = parameters.Get <int>(ReturnValueParameterName);
}
return(returnItem);
}
/// <summary>
/// Executes non-query sql text.
/// </summary>
/// <param name="sqlQueryText">SQL Query Text</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>NonQueryReturnItem</returns>
public virtual NonQueryReturnItem ExecuteNonQuerySqlText(string sqlQueryText, IDbParameterList parametersCollection, bool isReturnValueExists = false)
{
NonQueryReturnItem returnItem = new NonQueryReturnItem();
IDbParameterList returnParameterList = new DbParameterList();
DynamicParameters parameters = new DynamicParameters();
foreach (DbDataParameter parameter in parametersCollection)
{
parameters.Add(parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput)
{
returnParameterList.Add(parameter);
}
}
if (isReturnValueExists)
{
parameters.Add(ReturnValueParameterName, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue, size: ReturnValueSize);
}
OpenConnectionForSingleTransaction();
ExecuteNonQuery(sqlQueryText, CommandType.Text, parameters);
CloseConnectionForSingleTransaction();
if (returnParameterList.Count > 0)
{
foreach (DbDataParameter parameter in returnParameterList)
{
parameter.Value = GetOutputParameterValue(parameter.ParameterName, parameter.DbType, parameters);
}
returnItem.ReturnParametersCollection = returnParameterList;
}
if (isReturnValueExists)
{
returnItem.ReturnValue = parameters.Get <int>(ReturnValueParameterName);
}
return(returnItem);
}
/// <summary>
/// Get Dynamic Parameters And Return Db Parameters By DbParameters
/// </summary>
/// <param name="parametersCollection">Given DB parameters list</param>
/// <param name="isReturnValueExists">Return value is required or not</param>
/// <param name="dynamicParameters">Output. Dynamic Parameters</param>
/// <param name="returnParameterCollection">Output. Return DB parameter list</param>
protected void GetDynamicParametersAndReturnDbParametersByDbParameters(IDbParameterList parametersCollection,
bool isReturnValueExists, out DynamicParameters dynamicParameters, out IDbParameterList returnParameterCollection)
{
returnParameterCollection = new DbParameterList();
dynamicParameters = new DynamicParameters();
foreach (DbDataParameter parameter in parametersCollection)
{
dynamicParameters.Add(parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput)
{
returnParameterCollection.Add(parameter);
}
}
if (isReturnValueExists)
{
dynamicParameters.Add(ReturnValueParameterName, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue, size: ReturnValueSize);
}
}
