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)); }