private static IEnumerable <TResult> ExecuteReadColumnIEnumerable <TResult>(string commandText, CommandType commandType, string columnName, TResult defaultValue,
params SqlParameter[] parameters) where TResult : IConvertible
{
using (var db = new SQLDataAccess())
{
db.Inititialize(commandText, commandType, parameters);
db.cnOpen();
using (var reader = db.cmd.ExecuteReader())
{
while (reader.Read())
{
yield return(SQLDataHelper.GetValue(reader, columnName, defaultValue));
}
}
db.cnClose();
}
}
public static List <TResult> ExecuteReadColumn <TResult>(string commandText, CommandType commandType, string columnName, TResult defaultValue,
params SqlParameter[] parameters) where TResult : IConvertible
{
var res = new List <TResult>();
using (var db = new SQLDataAccess())
{
db.Inititialize(commandText, commandType, parameters);
db.cnOpen();
using (var reader = db.cmd.ExecuteReader())
{
while (reader.Read())
{
res.Add(SQLDataHelper.GetValue(reader, columnName, defaultValue));
}
}
db.cnClose();
}
return(res);
}
public static Dictionary <TKey, TValue> ExecuteReadDictionary <TKey, TValue>(string commandText, CommandType commandType,
string keyColumnName, TKey defaultkey, Func <SqlDataReader, TValue> function,
params SqlParameter[] parameters) where TKey : IConvertible
{
var res = new Dictionary <TKey, TValue>();
using (var db = new SQLDataAccess())
{
db.Inititialize(commandText, commandType, parameters);
db.cnOpen();
using (var reader = db.cmd.ExecuteReader())
{
while (reader.Read())
{
res.Add(SQLDataHelper.GetValue(reader, keyColumnName, defaultkey), function(reader));
}
}
}
return(res);
}
public List <T> ItemsUnionIds <T>(string idName) where T : IConvertible
{
//get
//{
var cmd = new List <SqlParameter>();
string seachConditionUnionTable = string.Empty;
if (!string.IsNullOrEmpty(TableNameForUnion))
{
bool first = true;
foreach (Field f in FieldsUnionTable.Values)
{
if (f.Filter != null)
{
if (first)
{
seachConditionUnionTable = "WHERE ";
first = false;
}
else
{
seachConditionUnionTable += " AND ";
}
seachConditionUnionTable += f.Filter.GetSqlCondition(f.FilterExpression);
f.Filter.SetSqlConditionParameter(cmd);
}
}
}
string query = string.Empty;
if (!string.IsNullOrEmpty(TableNameForUnion))
{
query = "SELECT " + idName + " FROM " + TableNameForUnion + seachConditionUnionTable;
}
if (_listParam != null)
{
cmd.AddRange(_listParam.Select(param => new SqlParameter {
ParameterName = param.ParamName, Value = param.ParamValue
}));
}
var itemsIds = new List <T>();
if (string.IsNullOrEmpty(query))
{
return(itemsIds);
}
itemsIds = SQLDataAccess.ExecuteReadList(query, CommandType.Text, reader => SQLDataHelper.GetValue <T>(reader, "ID"), cmd.ToArray());
return(itemsIds);
//}
}
public List <T> ItemsIds <T>(string idName) where T : IConvertible
{
//get
//{
var cmd = new List <SqlParameter>();
var searchCondition = string.Empty;
bool first = true;
foreach (var f in Fields.Values)
{
if (f.Filter != null)
{
if (first)
{
searchCondition = " WHERE ";
first = false;
}
else
{
searchCondition += " AND ";
}
searchCondition += f.Filter.GetSqlCondition(f.FilterExpression);
f.Filter.SetSqlConditionParameter(cmd);
}
}
if (!string.IsNullOrWhiteSpace(ExtensionWhere))
{
searchCondition += " " + ExtensionWhere;
}
string query = "SELECT " + idName + " FROM " + TableName + searchCondition;
if (_listParam != null)
{
cmd.AddRange(_listParam.Select(param => new SqlParameter {
ParameterName = param.ParamName, Value = param.ParamValue
}));
}
List <T> itemsIds = SQLDataAccess.ExecuteReadList(query, CommandType.Text, reader => SQLDataHelper.GetValue <T>(reader, "ID"), cmd.ToArray());
return(itemsIds);
//}
}
public List <T> ItemsUnionIds <T>(string idName) where T : IConvertible
{
var cmd = new List <SqlParameter>();
var seachConditionUnionTable = string.Empty;
var query = string.Empty;
if (!string.IsNullOrEmpty(TableNameForUnion))
{
query = "SELECT " + idName + " FROM " + TableNameForUnion + seachConditionUnionTable;
}
if (_listParam != null)
{
cmd.AddRange(_listParam.Select(param => new SqlParameter {
ParameterName = param.ParameterName, Value = param.Value
}));
}
var itemsIds = new List <T>();
if (string.IsNullOrEmpty(query))
{
return(itemsIds);
}
itemsIds = SQLDataAccess.ExecuteReadList(query, CommandType.Text, reader => SQLDataHelper.GetValue <T>(reader, "ID"), cmd.ToArray());
return(itemsIds);
}
public List <T> ItemsIds <T>(string idName) where T : IConvertible
{
var cmd = new List <SqlParameter>();
var searchCondition = new StringBuilder();
SetConditionByFieldsValues(Fields.Values.Where(f => f.Filter != null), searchCondition, cmd);
if (!string.IsNullOrWhiteSpace(ExtensionWhere))
{
searchCondition.Append(" " + ExtensionWhere);
}
var query = "SELECT " + idName + " FROM " + TableName + searchCondition;
if (_listParam != null)
{
cmd.AddRange(_listParam.Select(param => new SqlParameter {
ParameterName = param.ParameterName, Value = param.Value
}));
}
var itemsIds = SQLDataAccess.ExecuteReadList(query, CommandType.Text, reader => SQLDataHelper.GetValue <T>(reader, "ID"), cmd.ToArray());
return(itemsIds);
}
public List <T> ItemsIds <T>(string idName) where T : IConvertible
{
var query = "SELECT " + idName + " FROM "
+ _tablename
+ _joinTable.Aggregate(" ", (a, b) => a + " " + b)
+ _whereCondition.Aggregate(" WHERE ", (a, b) => a + " " + b);
var itemsIds = SQLDataAccess.ExecuteReadList(query, CommandType.Text, reader => SQLDataHelper.GetValue <T>(reader, "ID"), _listParams.Select(x => new SqlParameter {
Value = x.Value, ParameterName = x.ParameterName
}).ToArray());
return(itemsIds);
}