public string CreateSelectJoinByParamsSQL(string[] paramList, Dictionary <string, bool> orderByList, string joinTable)
{
Type type = typeof(T);
string tableName = type.Name;
if ((type.GetCustomAttributes(typeof(TableAttribute), false).Length) > 0)
{
TableAttribute db = (TableAttribute)type.GetCustomAttributes(typeof(TableAttribute), false)[0];
if (db.Name.Length > 0)
{
tableName = db.Name;
}
}
StringBuilder sb = new StringBuilder();
StringBuilder sbVals = new StringBuilder();
sb.Append("SELECT ");
int amnt = props.Length;
int cnt = 0;
string primaryKey = FindPrimaryKey();
foreach (PropertyInfo param in props)
{
if (param.PropertyType.Namespace != "System.Collections.Generic")
{
string colName = param.Name;
DBAttribute db = DataHelper.FindAttribute <DBAttribute>(param);
if (db != null && db.ColumnName != null && db.ColumnName.Length > 0)
{
colName = db.ColumnName;
}
addSqlParamsWithJoin("a", string.Empty, colName, amnt, cnt, param.PropertyType.BaseType.Name, ref sb, ref sbVals);
}
cnt++;
}
sb.AppendFormat(" FROM [{3}].[{0}] a LEFT JOIN [{3}].[{1}] b ON a.[{2}] = b.[{2}] ", tableName, joinTable, primaryKey, _sqlSchema);
if (paramList.Length > 0)
{
sb.Append("WHERE ");
}
amnt = paramList.Length;
cnt = 0;
foreach (string param in paramList)
{
if (cnt < amnt - 1)
{
sb.Append("b.[" + param + "] = @" + param + " AND ");
}
else
{
sb.Append("b.[" + param + "] = @" + param);
}
cnt++;
}
if (orderByList != null)
{
if (orderByList.Count > 0)
{
sb.Append(" ORDER BY ");
amnt = orderByList.Count;
cnt = 0;
foreach (KeyValuePair <string, bool> param in orderByList)
{
string ascDesc = " ASC";
if (!param.Value)
{
ascDesc = " DESC";
}
if (cnt < amnt - 1)
{
sb.Append("[" + param.Key + "]" + ascDesc + ", ");
}
else
{
sb.Append("[" + param.Key + "]" + ascDesc);
}
cnt++;
}
}
}
else
{
sb.AppendFormat(" ORDER BY [{0}] ASC", tableName + "ID");
}
return(sb.ToString());
}
protected void UpdateCollectionManyMany(IList <T> collection, string parentKey, long parentId, string manyTableName, bool hasOrder)
{
string primaryKey = commHelper.FindPrimaryKey();
long[] primaryIds = new long[collection.Count];
int cnt = 0;
foreach (var thing in collection)
{
object primaryKeyVal = thing.GetType().GetProperty(primaryKey).GetValue(thing, null);
if (Int64.Parse(primaryKeyVal.ToString()) > 0)
{
Update(thing); // Needed to delete or add to collections like imageVersions
primaryIds[cnt] = long.Parse(primaryKeyVal.ToString());
}
else
{
object newObj = Create(thing);
primaryIds[cnt] = long.Parse(newObj.GetType().GetProperty(primaryKey).GetValue(newObj, null).ToString());
}
cnt++;
}
Type type = typeof(T);
TableAttribute tAttr = (TableAttribute)DataHelper.FindAttribute(typeof(TableAttribute), type);
bool isReadOnly = false;
if (tAttr != null && tAttr.ReadOnly)
{
isReadOnly = true;
}
if (!isReadOnly)
{
if (primaryIds.Length > 0)
{
SqlCommand updateCMD = new SqlCommand();
if (hasOrder)
{
updateCMD.CommandText = commHelper.CreateInsertManyManyOrderSQL(parentKey, parentId, primaryIds, manyTableName);
}
else
{
updateCMD.CommandText = commHelper.CreateInsertManyManySQL(parentKey, parentId, primaryIds, manyTableName);
}
updateCMD.CommandType = CommandType.Text;
using (updateCMD.Connection = conn.ConnObj)
{
updateCMD.Connection.Open();
updateCMD.ExecuteNonQuery();
}
}
//Delete orphaned children
SqlCommand deleteCMD = new SqlCommand();
deleteCMD.CommandText = commHelper.DeleteManyManyOrphanSQL(parentKey, parentId, primaryIds, manyTableName, primaryKey);
deleteCMD.CommandType = CommandType.Text;
using (deleteCMD.Connection = conn.ConnObj)
{
deleteCMD.Connection.Open();
deleteCMD.ExecuteNonQuery();
}
}
}
public string CreateSelectByParamsSQL(Dictionary <string, object> whereVals, Dictionary <string, bool> orderByList)
{
Type type = typeof(T);
string tableName = type.Name;
if ((type.GetCustomAttributes(typeof(TableAttribute), false).Length) > 0)
{
TableAttribute db = (TableAttribute)type.GetCustomAttributes(typeof(TableAttribute), false)[0];
if (db.Name.Length > 0)
{
tableName = db.Name;
}
}
StringBuilder sb = new StringBuilder();
StringBuilder sbVals = new StringBuilder();
sb.Append("SELECT ");
int amnt = props.Length;
int cnt = 0;
// string primaryKey = FindPrimaryKey();
foreach (PropertyInfo param in props)
{
if (param.PropertyType.Namespace != "System.Collections.Generic")
{
string colName = param.Name;
DBAttribute db = DataHelper.FindAttribute <DBAttribute>(param);
if (db != null && db.ColumnName != null && db.ColumnName.Length > 0)
{
colName = db.ColumnName;
}
addSqlParams(string.Empty, colName, amnt, cnt, param.PropertyType.BaseType.Name, ref sb, ref sbVals);
}
cnt++;
}
sb.Append(" FROM [" + _sqlSchema + "].[" + tableName + "] ");
if (whereVals != null)
{
if (whereVals.Count > 0)
{
sb.Append("WHERE ");
}
amnt = whereVals.Count;
cnt = 0;
foreach (var param in whereVals)
{
string and = " AND ";
if (cnt == (amnt - 1))
{
and = "";
}
if (param.Value == DBNull.Value)
{
sb.AppendFormat("ISNull({0}, -1) = ISNull(@{0}, -1) {1}", param.Key, and);
}
else
{
sb.AppendFormat("{0} = @{0} {1}", param.Key, and);
}
cnt++;
}
}
if (orderByList != null)
{
if (orderByList.Count > 0)
{
sb.Append(" ORDER BY ");
amnt = orderByList.Count;
cnt = 0;
foreach (KeyValuePair <string, bool> param in orderByList)
{
string ascDesc = " ASC";
if (!param.Value)
{
ascDesc = " DESC";
}
if (cnt < amnt - 1)
{
sb.Append("[" + param.Key + "]" + ascDesc + ", ");
}
else
{
sb.Append("[" + param.Key + "]" + ascDesc);
}
cnt++;
}
}
}
return(sb.ToString());
}