private string getCustomQuery(SqlBuilderTask.QueryTypeEnum queryType, IPocoSqlMapping map)
{
string customQuery = null;
var customMappings = map.GetCustomMappings();
if (customMappings != null)
{
switch (queryType)
{
case SqlBuilderTask.QueryTypeEnum.Select:
customQuery = customMappings.SelectQuery;
break;
case SqlBuilderTask.QueryTypeEnum.Insert:
customQuery = customMappings.InsertQuery;
break;
case SqlBuilderTask.QueryTypeEnum.Update:
customQuery = customMappings.UpdateQuery;
break;
case SqlBuilderTask.QueryTypeEnum.Delete:
customQuery = customMappings.DeleteQuery;
break;
}
}
return(customQuery);
}
private IPocoSqlMapping getMapping(object obj)
{
IPocoSqlMapping map = null;
string key = obj.GetType().FullName;
if (Configuration.HasMap(key))
{
map = Configuration.GetMap(key);
}
return(map);
}
private string getQueryFields(SqlBuilderTask.QueryTypeEnum queryType, IPocoSqlMapping map)
{
IEnumerable <PropertyInfo> propertyInfos = _obj.GetType().GetRuntimeProperties();
string primaryKey = null;
if (queryType == SqlBuilderTask.QueryTypeEnum.Update || queryType == SqlBuilderTask.QueryTypeEnum.Insert)
{
primaryKey = getPrimaryKey(_obj);
}
StringBuilder allFields = new StringBuilder();
StringBuilder insertValues = new StringBuilder();
foreach (PropertyInfo propertyInfo in propertyInfos.Where(p => p.PropertyType.FullName.StartsWith("System") && !p.PropertyType.FullName.StartsWith("System.Collections"))) // only loop on objects that are not custom class
{
string
dbSelect = null,
fieldName = null,
dbColumnName = null;
if (map != null)
{
PropertyMap propertyMap = map.GetMapping(propertyInfo.Name);
if (propertyMap != null)
{
if (!propertyMap.Ignored)
{
if (!propertyMap.ColumnName.Equals(propertyInfo.Name))
{
if (queryType == SqlBuilderTask.QueryTypeEnum.Select)
{
dbSelect = String.Format("{0} as {1}",
propertyMap.ColumnName,
propertyInfo.Name);
}
fieldName = propertyInfo.Name;
dbColumnName = propertyMap.ColumnName;
}
}
else
{
dbSelect = String.Empty;
}
}
}
if (String.IsNullOrEmpty(dbSelect))
{
dbSelect = fieldName = dbColumnName = propertyInfo.Name;
}
if (fieldName == primaryKey)
{
if (queryType == SqlBuilderTask.QueryTypeEnum.Update)
{
var property = _obj.GetType().GetRuntimeProperty(dbColumnName);
string val = "@" + primaryKey;
if (Configuration.ValuesInQueies)
{
property.GetValue(_obj).ToString();
}
this.Where(primaryKey + " = " + val);
}
if (queryType == SqlBuilderTask.QueryTypeEnum.Update || (queryType == SqlBuilderTask.QueryTypeEnum.Insert && map.GetPrimaryAutoGenerated()))
{
continue;
}
}
switch (queryType)
{
case SqlBuilderTask.QueryTypeEnum.Select:
allFields.Append((allFields.Length > 0 && !String.IsNullOrEmpty(fieldName) ? ", " : String.Empty) + dbSelect);
break;
case SqlBuilderTask.QueryTypeEnum.Insert:
if (!String.IsNullOrEmpty(fieldName))
{
string insertVal;
if (Configuration.ValuesInQueies)
{
insertVal = getPropertyValueAsSql(_obj, fieldName);
}
else
{
insertVal = "@" + fieldName;
}
allFields.Append((allFields.Length > 0 ? ", " : String.Empty) + dbSelect);
insertValues.Append((insertValues.Length > 0 ? ", " : String.Empty) + insertVal);
}
break;
case SqlBuilderTask.QueryTypeEnum.Update:
case SqlBuilderTask.QueryTypeEnum.StoredProcedure:
string updateVal;
if (Configuration.ValuesInQueies)
{
updateVal = getPropertyValueAsSql(_obj, fieldName, true);
}
else
{
updateVal = "@" + fieldName;
}
allFields.Append((allFields.Length > 0 && !String.IsNullOrEmpty(fieldName) ? ", " : String.Empty) + dbColumnName + " = " + updateVal);
break;
case SqlBuilderTask.QueryTypeEnum.Delete:
break;
}
}
if (queryType == SqlBuilderTask.QueryTypeEnum.Insert)
{
return(String.Format("({0}) values({1})", allFields, insertValues));
}
else
{
return(allFields.ToString());
}
}
//private SqlBuilder buildSqlStatement(object obj, SqlBuilderTask.QueryTypeEnum queryType, string tableName, bool? fullGraph, bool? pluralizeTableNames, IPocoSqlMapping map)
private SqlBuilder buildSqlStatement(object obj, SqlBuilderTask task)
{
if (task.QueryType == SqlBuilderTask.QueryTypeEnum.SelectScopeIdentity)
{
task.SqlResult = "select scope_identity()";
return(this);
}
else if (task.QueryType == SqlBuilderTask.QueryTypeEnum.SelectIdentity)
{
task.SqlResult = "select @@identity";
return(this);
}
IPocoSqlMapping map = null;
string tableName = null;
if (Configuration.Comment)
{
task.SqlResult = getComment(obj);
}
if (task.Map == null)
{
map = getMapping(obj);
}
if (map != null)
{
//
// Custom query
//
var customQuery = getCustomQuery(task.QueryType, map);
if (!String.IsNullOrEmpty(customQuery))
{
task.SqlResult += customQuery;
if (task.SqlResult.EndsWith(";"))
{
currentTask.EndWithSemicolon = false;
}
return(this); // if it's a custom query no other thing needs (the user knew in advanced what he wanted) to be done and the result is returned
}
//
// Stored Procedures mappings
//
var spMappings = map.GetStoredProceduresMappings();
if (spMappings != null)
{
PocoSqlStoredProcedureMap spMap = getStoredProcedureMap(spMappings, task.QueryType);
if (spMap != null)
{
string spName = getStoredProcedureName(spMap, obj, task.QueryType);
task.SqlResult += String.Format("{0}{1}{2}",
spMap.Execution ? "exec " : String.Empty,
spName,
spMap.Parameters ? getQueryFields(task.QueryType, map) : String.Empty);
if (!spMap.Execution && !spMap.Parameters)
{
currentTask.EndWithSemicolon = false;
}
return(this);
}
}
if (task.QueryType != SqlBuilderTask.QueryTypeEnum.Select && map.GetIsVirtual())
{
throw new CantUpdateVirtualException();
}
if (String.IsNullOrEmpty(task.TableName))
{
tableName = map.GetTableName();
}
}
if (String.IsNullOrEmpty(tableName))
{
tableName = obj.GetType().Name;
}
if ((map == null || (map != null && String.IsNullOrEmpty(map.GetTableName()))) && Configuration.IsPluralizeTableNames)
{
tableName = tableName.Pluralize();
}
switch (task.QueryType)
{
case SqlBuilderTask.QueryTypeEnum.Select:
task.SqlResult += String.Format("select {0} from {1}", getQueryFields(SqlBuilderTask.QueryTypeEnum.Select, map), tableName);
break;
case SqlBuilderTask.QueryTypeEnum.Insert:
task.SqlResult += String.Format("insert into {0} {1}", tableName, getQueryFields(SqlBuilderTask.QueryTypeEnum.Insert, map));
break;
case SqlBuilderTask.QueryTypeEnum.Update:
task.SqlResult += String.Format("update {0} set {1}", tableName, getQueryFields(SqlBuilderTask.QueryTypeEnum.Update, map));
break;
case SqlBuilderTask.QueryTypeEnum.Delete:
task.SqlResult += String.Format("delete from {0}", tableName);
string primaryKey = getPrimaryKey(_obj);
string deleteWhereValue = "@" + primaryKey;
if (Configuration.ValuesInQueies)
{
deleteWhereValue = getPropertyValueAsSql(_obj, primaryKey);
}
this.Where(String.Format("{0} = {1}", primaryKey, deleteWhereValue));
break;
case SqlBuilderTask.QueryTypeEnum.StoredProcedure:
var queryFields = String.Empty;
var execCmd = String.Empty;
task.SqlResult += String.Format("exec {0} {1}", tableName, getQueryFields(SqlBuilderTask.QueryTypeEnum.StoredProcedure, map));
break;
}
return(this);
}
