public static SqlDB GetSqlDBInstance(this BaseDoc _BaseDoc)
{
return(SqlDB.GetInstance(_BaseDoc));
}
private void Submit_Internal(string DocData, string DocSubmittedBy, string RelayUrl = null, bool?DocStatus = null, DateTime?SubmittedDate = null, Dictionary <string, string> DocKeys = null, string DocTitle = null)
{
DocData = BaseController.PIRewrite(DocData, DocStatus, SubmittedDate, DocKeys, DocTitle, DocSubmittedBy);
// let the BaseDoc parse it's string just as normal
BaseDoc _SubmittedBaseDoc = DocInterpreter.Instance.Read(DocData, true);
Type _SqlBaseDocType = ReverseEngineerCodeFirst(_SubmittedBaseDoc);
BaseDoc _SqlBaseDoc = (BaseDoc)Activator.CreateInstance(_SqlBaseDocType);
IQueryable _SqlList = ListInternal(_SqlBaseDocType, _SubmittedBaseDoc.DocIdKeys.ToNameValueCollection()).AsQueryable();
if (_SqlList.Any())
{
foreach (var o in _SqlList)
{
_SqlBaseDoc = (BaseDoc)o;
}
}
if (_SqlBaseDoc.DocChecksum != _SubmittedBaseDoc.DocChecksum)
{
JObject _SubmittedJObject = JObject.FromObject(_SubmittedBaseDoc, _JsonSerializer);
if (_SqlBaseDoc.Id > 0)
{
// Serialize the _ExistingBaseDoc Id property values only (the rest of the values will be merged in from the _SubmittedBaseDoc)
// keep in mind that EF lazy loading will kick in & load to ensure all the child objects & there Id(s) are serialized here
string _SqlIdsAsJson = JsonConvert.SerializeObject(_SqlBaseDoc, SqlIdsOnlySerializeContractResolver.MyJsonSerializerSettings);
JObject _SqlJObject = JObject.Parse(_SqlIdsAsJson);
// take the SQL Id(s) from the existing data & the new textual content from the new submission & combine them
// in order to make a clean up to the SQL database
_SubmittedJObject.Merge(_SqlJObject, _JsonMergeSettings);
_SqlBaseDoc = (BaseDoc)JsonConvert.DeserializeObject(
_SubmittedJObject.ToString(),
_SqlBaseDoc.GetType(),
TextualShouldSerializeContractResolver.MyJsonSerializerSettings);
// Utilize PropertyOverlay.Overlay(topO,bottomO,true), set those combined objects of business data & SQL Ids to the _ExistingBaseDoc
// ensure the PropertyOverlay.Overlay sizeToTop is set to true in order to force child List item counts (one-to-many) to agree with
// what the user has submitted. This results in Insert/Update/Delete statements shaping the existing dataset (_ExistingBaseDoc)
// to the _SubmittedBaseDoc.
}
else
{
_SqlBaseDoc = (BaseDoc)JsonConvert.DeserializeObject(
_SubmittedJObject.ToString(),
_SqlBaseDoc.GetType(),
TextualShouldSerializeContractResolver.MyJsonSerializerSettings);
MetaTable _docKeyTable = GetDocKeyTable(_SqlBaseDoc);
foreach (KeyValuePair <string, string> _Item in _SubmittedBaseDoc.DocIdKeys)
{
DocKey _DocKeyEntry = (DocKey)Activator.CreateInstance(_docKeyTable.EntityType, null);
_DocKeyEntry.Id = _SqlBaseDoc.Id;
_DocKeyEntry.KeyName = _Item.Key;
_DocKeyEntry.KeyVal = _Item.Value;
SqlDB.GetInstance(_SqlBaseDoc).UnderlyingDbContext.Set(_docKeyTable.EntityType).Add(_DocKeyEntry);
}
}
_SqlBaseDoc.Save();
}
}
private static MetaTable GetDocKeyTable(BaseDoc o)
{
return(SqlDB.GetInstance(o).GetTable("DocKey"));
}
private IEnumerable ListInternal(BaseDoc filter, NameValueCollection docKeyFilters = null, int PageSize = 150, int PageIndex = 0)
{
docKeyFilters = docKeyFilters ?? new NameValueCollection();
string propInfoPredicate = string.Empty;
//TODO:Add logic to filter forms based on the documentKey(s) passed
Dictionary <string, string> DocKeys = filter.DocIdKeys;
foreach (string key in filter.DocIdKeys.Keys)
{
docKeyFilters.Add(key, DocKeys[key]);
}
foreach (string key in DocKeys.Keys)
{
docKeyFilters.Add(key, DocKeys[key]);
}
filter.DocId = null;
// predicate formed from PropertyInfo[] of the form business object
// FormHandlerNavigation filterFormHandlerNavigation = new FormHandlerNavigation(filter);
// List<object> parms = filterFormHandlerNavigation.RenderNavigateUrlParameters().ToList<object>();
List <object> parms = new List <object>();
StringBuilder predicateStringBuilder = new StringBuilder();
MetaTable _table = GetFormTable(filter);
// run through all the properties suitable for SQL/EF-mapping
foreach (PropertyInfo _PropertyInfo in filter.GetFormObjectNavProperties(true).Where(m => !Attribute.IsDefined(m, typeof(NotMappedAttribute))))
{
Type parmType = ExpressionParser.GetNonNullableType(_PropertyInfo.PropertyType) ?? _PropertyInfo.PropertyType;
bool IsNullable = parmType != _PropertyInfo.PropertyType;
parms.Add(Convert.ChangeType(_PropertyInfo.GetValue(filter, null), parmType, null));
bool Null_Value = false;
if (!Null_Value)
{
Null_Value = IsNullable && parmType.IsPrimitive;
}
if (!Null_Value)
{
Null_Value = IsNullable && parmType == typeof(DateTime);
}
predicateStringBuilder
.Append(_PropertyInfo.Name)
.Append(Null_Value ? ".Value" : string.Empty)
.Append(".Equals(@")
.Append(parms.Count - 1)
.Append(") && ");
}
propInfoPredicate = predicateStringBuilder.ToString().Trim(' ', '&');
// Merge the docKeys & NameValueCollection items
// Remove dictionary items that exist in the namevalue collection first
MetaTable _docKeyTable = GetDocKeyTable(filter);
StringBuilder DocKeyMatchSQLPredicate = new StringBuilder();
foreach (string key in docKeyFilters.Keys)
{
DocKeyMatchSQLPredicate
.AppendFormat(@" OR ( N'{0}' = KeyName AND ( ", key)
.Append(string.Join(" or ", docKeyFilters.GetValues(key).Select(m => "N'" + m + "'=Keyval").Distinct().ToArray()))
.Append(") )");
}
/* note this reference to CamelCase is also done in the .tt file to make the SQL column names pretty.
* technically we should be reading the properties Column attribute value to get the CamelCase
* version of the property's name */
string docKeyMatchSQL = "";
docKeyMatchSQL = docKeyFilters.Count == 0
? string.Empty
: string.Format(
@"
SELECT TOP 1 Id
FROM {1}.{2} /* The docKey table for the given entity */
WHERE {3} /* The predicate */
GROUP BY Id HAVING COUNT(*) = {4}",
"Id",
string.IsNullOrWhiteSpace(propInfoPredicate)
? filter.DocTypeName
: "(" + ((ObjectQuery)_table.GetQuery().Where(propInfoPredicate, parms.ToArray())).ToTraceString() + ")", /* Notice we use the MetaTable that has access to the underlying ObjectContext to yield our object-SQL */
_docKeyTable.Name,
DocKeyMatchSQLPredicate.ToString().Replace(" OR ", " || ").Trim(' ', '|').Replace(" || ", " OR "),
docKeyFilters.Keys.Count);
// locate the keys
object[] keyValues = !string.IsNullOrWhiteSpace(docKeyMatchSQL)
? SqlDB.GetInstance(filter).UnderlyingDbContext.Database.SqlQuery <int>(docKeyMatchSQL).Cast <object>().ToArray()
: new object[]
{};
filter.DocId = DocKey.DocIdFromKeys(DocKeys);
return(!string.IsNullOrWhiteSpace(docKeyMatchSQL)
? keyValues.Length == 0
? new List <object>() : new List <object>
{
SqlDB.GetInstance(filter).UnderlyingDbContext.Set(_table.EntityType).Find(keyValues)
}
: !string.IsNullOrWhiteSpace(propInfoPredicate)
? (IEnumerable)_table.GetQuery().Where(propInfoPredicate, parms.ToArray()).Skip(PageIndex).Take(PageSize)
: new List <BaseDoc>().AsEnumerable());
}
/// <summary>
/// Applies the strict naming conventions practiced though out the dCForm solution
/// to resolve the MetaTable the baseForm Code First class spawned. The meta table itself
/// was materialized by DynamicData
/// </summary>
/// <param name="o"></param>
/// <returns>An in memory MetaTable representing a physical SQL table observed</returns>
private static MetaTable GetFormTable(BaseDoc o)
{
return(SqlDB.GetInstance(o).GetTable(o.DocTypeName));
}
