/// <summary>
/// The entity management select.
/// </summary>
/// <param name="sqlCommand">
/// The sql command.
/// </param>
/// <returns>
/// The <see cref="List"/>.
/// </returns>
/// <exception cref="Exception">
/// </exception>
public static List <T> Select(SqlCommand sqlCommand)
{
var dataTable = new System.Data.DataTable();
try
{
using (var connection = DBHelper.Connection())
{
using (var adapter = new SqlDataAdapter(sqlCommand.CommandText, connection))
{
connection.Open();
adapter.Fill(dataTable);
}
connection.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
var anonimT = DataTableConvert.DataTableToList <T>(dataTable);
return(anonimT);
}
/// <summary>
/// The entity management select.
/// </summary>
/// <param name="table">
/// The table.
/// </param>
/// <returns>
/// The <see cref="List"/>.
/// </returns>
/// <exception cref="Exception">
/// </exception>
public static List <T> Select(string table)
{
var dataTable = new System.Data.DataTable();
try
{
using (var connection = DBHelper.Connection())
{
var sql = string.Format("{0} * {1} {2} ", SqlQueryEnum.SELECT, SqlQueryEnum.FROM, table);
using (var adapter = new SqlDataAdapter(sql, connection))
{
connection.Open();
adapter.Fill(dataTable);
}
connection.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
var anonimT = DataTableConvert.DataTableToList <T>(dataTable);
return(anonimT);
}
public static List <T> ScoopSqlCommand(string sql, SqlParameter[] parameter = null)
{
var dataTable = new System.Data.DataTable();
try
{
dataTable = DataTableProses.GetDataTableSqlDataAdapter(sql, parameter, dataTable);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
var anonimT = DataTableConvert.DataTableToList <T>(dataTable);
return(anonimT);
}
public static List <T> Select(List <ParamValue> paramValues, string table = null)
{
System.Data.DataTable dataTable;
try
{
if (string.IsNullOrEmpty(table))
{
table = (typeof(T)).BaseType.Name == "Object" ? typeof(T).Name : (typeof(T)).BaseType.Name;
}
using (var connection = DBHelper.Connection())
{
var whereSucces = paramValues.FirstOrDefault(p => p.Where != null && p.Where.Count != 0);
var likeSucces = paramValues.FirstOrDefault(p => p.Like != null && p.Like.Count != 0);
var sql = SqlOperatorler.SqlWhere(paramValues, table, whereSucces);
var firstOrDefault =
paramValues.FirstOrDefault(p => !string.IsNullOrEmpty(p.Between) && p.Where != null && p.Where.Count > 0);
sql = SqlOperatorler.SqlAnd(firstOrDefault, sql);
sql = SqlOperatorler.SqlBetween(paramValues, sql);
sql = SqlOperatorler.SqlOrderByAsc(paramValues, sql);
sql = SqlOperatorler.SqlLike(paramValues, likeSucces, whereSucces, sql);
sql = SqlOperatorler.SqlOrderByDesc(paramValues, sql);
sql = SqlOperatorler.SqlPagging(paramValues, sql);
dataTable = DataTableProses.DataTableFill(paramValues, sql, connection);
connection.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
var anonimT = DataTableConvert.DataTableToList <T>(dataTable);
return(anonimT);
}
/// <summary>
/// The entity management select filter.
/// </summary>
/// <param name="paramValues">
/// The param values.
/// </param>
/// <param name="table">
/// The table.
/// </param>
/// <typeparam name="T">
/// </typeparam>
/// <returns>
/// The <see cref="List"/>.
/// </returns>
/// <exception cref="Exception">
/// </exception>
public static List <T> Filter <T>(List <ParamValue> paramValues, string table)
where T : new()
{
var dataTable = new System.Data.DataTable();
try
{
using (var connection = DBHelper.Connection())
{
var whereSucces = paramValues.FirstOrDefault(p => p.Where.Count != 0);
var sql = string.Format("{0} * {1} {2} {3} {4} ", SqlQueryEnum.SELECT, SqlQueryEnum.FROM, table,
whereSucces != null ? SqlQueryEnum.WHERE.ToString() : string.Empty,
string.Join(SqlQueryEnum.AND.ToString(),
paramValues.Select(p => p.Where)
.SelectMany(whr => whr)
.Select(p => string.Format(" {0}=@{1} ", p.Key, p.Key))));
#region düzenlenmesi gerekiyor
var firstOrDefault = paramValues.FirstOrDefault(p => p.Between != string.Empty);
if (firstOrDefault != null && firstOrDefault.Where.Count > 0 && firstOrDefault.Between.Any())
{
sql += " AND " + firstOrDefault.Between;
}
else if (firstOrDefault != null && firstOrDefault.Between.Any())
{
sql += " WHERE " + firstOrDefault.Between;
}
if (firstOrDefault != null && firstOrDefault.OrderByAsc != null &&
firstOrDefault.OrderByAsc != string.Empty)
{
sql += " ORDER BY " + firstOrDefault.OrderByAsc + " ASC ";
}
var like = paramValues.Select(value => value.Like).FirstOrDefault();
if (like != null && like.Any())
{
sql += " LIKE '%" + like + "%'";
}
var orderByDesc = paramValues.Select(value => value.OrderByDescending).ToList();
if (orderByDesc.FirstOrDefault() != null && orderByDesc.Any())
{
var desc = string.Empty;
foreach (var item in orderByDesc.FirstOrDefault())
{
desc += item + " DESC,";
}
sql += string.Format("ORDER BY {0} ", desc.TrimEnd(','));
}
#endregion
using (var adapter = new SqlDataAdapter(sql.Trim(), connection))
{
connection.Open();
foreach (var item in paramValues)
{
foreach (var row in item.Where)
{
var prm = string.Format("@{0}", row.Key);
adapter.SelectCommand.Parameters.AddWithValue(prm, row.Value);
}
}
adapter.Fill(dataTable);
}
connection.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
var anonimT = DataTableConvert.DataTableToList <T>(dataTable);
return(anonimT);
}
