public int Delete <T>(List <object> keys, Conditions conditions) where T : class
{
if (keys.Count > 0)
{
Type obj = typeof(T);
int keyCount = 0;
foreach (PropertyInfo objprop in obj.GetProperties().Where(x => x.CustomAttributes.Count() > 0))
{
if (objprop.CustomAttributes.Any(x => x.AttributeType.Name == "KeyAttribute"))
{
conditions.AddFilter(objprop.Name, Operator.Equals(keys[keyCount]));
keyCount++;
}
if (keyCount >= keys.Count)
{
break;
}
}
}
string tableName = GetTableName(typeof(T));
string storedProcedure = conditions.SP_DELETE;//GetStoredProcedureOfDelete(typeof(T));
if (string.IsNullOrEmpty(storedProcedure))
{
string query = "DELETE FROM " + tableName + " " + conditions.GetAllFilterParam();
return(ObjConn.Execute(query, null, ObjTrans, CommandTimeOut, CommandType.Text));
}
else
{
StoredProcedureParameter.Delete spParams = new StoredProcedureParameter.Delete();
spParams.filter = conditions.ResultFilter;
return(ObjConn.Execute(storedProcedure, spParams, ObjTrans, CommandTimeOut, CommandType.StoredProcedure));
}
}
public T ReadOne <T>(List <object> keys, Conditions conditions)
{
if (keys.Count > 0)
{
Type obj = typeof(T);
int keyCount = 0;
foreach (PropertyInfo objprop in obj.GetProperties().Where(x => x.CustomAttributes.Count() > 0))
{
if (objprop.CustomAttributes.Any(x => x.AttributeType.Name == "KeyAttribute"))
{
conditions.AddFilter(objprop.Name, Operator.Equals(keys[keyCount]));
keyCount++;
}
if (keyCount >= keys.Count)
{
break;
}
}
}
string storedProcedureName = conditions.SP_SELECT;//GetStoredProcedureOfSelect(typeof(T));
string tableName = GetTableName(typeof(T));
if (storedProcedureName != "")
{
StoredProcedureParameter.Select spParam = new StoredProcedureParameter.Select()
{
filter = conditions.GetFilterParam(),
selectedField = conditions.GetTop1SelectParam(),
groupBy = conditions.GetGroupByParam(),
orderBy = conditions.GetOrderByParam()
};
var retn = ObjConn.Query <T>(storedProcedureName, spParam, ObjTrans, true, CommandTimeOut, CommandType.StoredProcedure);
if (retn.Count() > 0)
{
return(retn.ToList()[0]);
}
else
{
return(default);
public int Update <T>(T model, bool isDirect, Conditions conditions) where T : class
{
//TODO DATETIME PROBLEM IF NULL
//Create Set Value
string SetValue = "";
string prefix = "";
var asdsadf = model.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => x.CanWrite && !x.CustomAttributes.Any(y => y.AttributeType.Name == "RequiredAttribute" || y.AttributeType.Name == "ReadOnlyAttribute"));
foreach (PropertyInfo objprop in model.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => x.CanWrite && !x.CustomAttributes
.Any(y => y.AttributeType.Name == "RequiredAttribute" || y.AttributeType.Name == "ReadOnlyAttribute")))
{
prefix = SetValue.Length == 0 ? "" : ", ";
var VAL = objprop.GetValue(model, null);
if (VAL != null)
{
if (objprop.PropertyType.Name == "DateTime")
{
if (VAL.ToString() != "1/1/0001 12:00:00 AM")
{
SetValue = SetValue + prefix + objprop.Name + "='" + VAL.ToString() + "'";
}
}
else
{
SetValue = SetValue + prefix + objprop.Name + "='" + VAL.ToString() + "'";
}
}
}
//Add Filter Based on Key Models: Auto Add Default Primary Key as Filter
if (isDirect)
{
object keyVal;
foreach (PropertyInfo objprop in model.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(x => x.CustomAttributes.Any(y => y.AttributeType.Name == "KeyAttribute")))
{
keyVal = objprop.GetValue(model, null);
if (keyVal == null)
{
break;
}
conditions.AddFilter(objprop.Name, Operator.Equals(keyVal.ToString()));
}
}
//Execute
string tableName = GetTableName(typeof(T));
string storedProcedure = conditions.SP_UPDATE; //GetStoredProcedureOfUpdate(typeof(T));
if (string.IsNullOrEmpty(storedProcedure))
{
string query = "UPDATE " + tableName + " SET " + SetValue + " " + conditions.GetAllFilterParam();
return(ObjConn.Execute(query, null, ObjTrans, CommandTimeOut, CommandType.Text));
}
else
{
StoredProcedureParameter.Update spParams = new StoredProcedureParameter.Update();
spParams.filter = conditions.ResultFilter;
spParams.setvalue = SetValue;
spParams.isDebug = true;
if (spParams.isDebug)
{
object execQuery = ObjConn.ExecuteScalar(storedProcedure, spParams, ObjTrans, CommandTimeOut, CommandType.StoredProcedure);
return(0);
}
else
{
return(ObjConn.Execute(storedProcedure, spParams, ObjTrans, CommandTimeOut, CommandType.StoredProcedure));
}
}
}
public int Insert <T>(T model, out object Keys, Conditions conditions, bool GetLastKey = false) where T : class
{
List <string> field = new List <string>();
List <string> val = new List <string>();
Dictionary <string, object> lstKey = new Dictionary <string, object>();
string qry = "INSERT INTO " + model.GetType().Name + " (";
bool isSkip;
bool isKey;
bool isRequired;
bool isReadOnly;
bool isAnyValueToInsert;
string spName = conditions.SP_INSERT;//GetStoredProcedureOfInsert(typeof(T));
foreach (PropertyInfo objprop in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
isSkip = false;
isKey = false;
isRequired = false;
isReadOnly = false;
if (objprop.CustomAttributes.Count() > 0)
{
foreach (CustomAttributeData obj in objprop.CustomAttributes.ToList())
{
isKey = (obj.AttributeType.Name == "KeyAttribute");
if (isKey)
{
var vals_ = model.GetType().GetProperty(objprop.Name).GetValue(model, null);
lstKey.Add(objprop.Name, vals_);
}
isRequired = (obj.AttributeType.Name == "RequiredAttribute");
isReadOnly = (obj.AttributeType.Name == "ReadOnlyAttribute");
}
isSkip = (isKey && !isRequired) || isReadOnly;
}
if (!isSkip)
{
var vals_ = model.GetType().GetProperty(objprop.Name).GetValue(model, null);
if (vals_ != null)
{
field.Add(objprop.Name);
val.Add(vals_.ToString());
}
}
}
isAnyValueToInsert = val.Count > 0;
if (isAnyValueToInsert)
{
StoredProcedureParameter.Insert spInsertParam = new StoredProcedureParameter.Insert();
object rets;
if (GetLastKey)
{
Dictionary <string, object> OutKeys = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(spName))
{
spInsertParam.field = "(" + string.Join(",", field) + ")";
spInsertParam.value = "('" + string.Join("','", val) + "')";
spInsertParam.getkey = true;
rets = ObjConn.ExecuteScalar(spName, spInsertParam, ObjTrans, CommandTimeOut, CommandType.StoredProcedure);
if (lstKey.Count > 1)
{
OutKeys = lstKey;
}
else
{
OutKeys.Add("Key1", rets);
}
Keys = OutKeys;
return(1);
}
else
{
qry += string.Join(",", field) + ") VALUES ('" + string.Join("','", val) + "')";
qry += ";" + "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];";
rets = ObjConn.ExecuteScalar(qry, null, ObjTrans, CommandTimeOut);
if (lstKey.Count > 1)
{
OutKeys = lstKey;
}
else
{
OutKeys.Add("Key1", rets);
}
Keys = OutKeys;
return(1);
}
}
else
{
if (!string.IsNullOrEmpty(spName))
{
spInsertParam.field = "(" + string.Join(",", field) + ")";
spInsertParam.value = "('" + string.Join("','", val) + "')";
spInsertParam.getkey = false;
rets = ObjConn.ExecuteScalar(qry, spInsertParam, ObjTrans, CommandTimeOut, CommandType.StoredProcedure);
Keys = null;
return((int)rets);
}
else
{
qry += string.Join(",", field) + ") VALUES ('" + string.Join("','", val) + "')";
Keys = null;
return(ObjConn.Execute(qry, null, ObjTrans, CommandTimeOut));
}
}
}
else
{
throw new Exception("No value param to insert");
}
}