public bool CreateDataBaseTable(SPSite site, string tableName, SPFieldCollection fields) { string dataBaseTableName = this.Adapter.BuildTableName(tableName); CacheTableStructXml cacheTableStructXml = new CacheTableStructXml { TableName = dataBaseTableName }; foreach (SPField field in fields) { if (field.Hidden && field.Id != SPBuiltInFieldId.ID && this.Adapter.IncludedFields.All(f => f != field.InternalName)) { continue; } string columnType = this.Adapter.GetDataBaseType(field.Type); Columns column = new Columns { ColumnName = field.Id == SPBuiltInFieldId.ID ? Constants.ColumnSpPrefix + field.InternalName : field.InternalName, ColumnType = columnType }; cacheTableStructXml.Columns.Add(column); } ArchiveCacheCRUD syncCrud = new ArchiveCacheCRUD(site); ErrorCode result = syncCrud.CreateOrUpdateTable(cacheTableStructXml); return(result == ErrorCode.NoError); }
private static void RemoveNotAvalibleColumns(CacheTableStructXml cacheTableStructXml, IEnumerable <string> columnNotAvalibleNames) { foreach ( Columns column in columnNotAvalibleNames.Select( s => cacheTableStructXml.Columns.Where( c => string.Equals(c.ColumnName, s, StringComparison.InvariantCultureIgnoreCase)) .ToList()).SelectMany(invalidColumns => invalidColumns)) { cacheTableStructXml.Columns.Remove(column); } }
public ErrorCode CreateOrUpdateValues(CacheTableStructXml cacheTableStructXml) { RemoveNotAvalibleColumns(cacheTableStructXml, new[] { "Id" }); const string sql = "EXECUTE [dbo].[P_CacheDb_UpdateValues] @CacheTableStructXml"; string xmlString = cacheTableStructXml.ToString(); ErrorCode result = this.StoredProcedureVoid(sql, new { CacheTableStructXml = xmlString }); return(result); }
public ErrorCode CreateOrUpdateTable(CacheTableStructXml cacheTableStructXml) { RemoveNotAvalibleColumns(cacheTableStructXml, new[] { "Id", Constants.BarCodeFieldName, "ItemUrl" }); const string sql = "EXECUTE [dbo].[P_CacheDb_CreateTable] @CacheTableStructXml"; string xmlString = cacheTableStructXml.ToString(); ErrorCode result = this.StoredProcedureVoid(sql, new { CacheTableStructXml = xmlString }); return(result); }
public bool UpdateDataTableColumns(SPList spList, string fieldXml, bool isDeleted) { string tableNameSetting = SettingsProvider.Instance(spList.ParentWeb).TableName(spList); string tableName = this.Adapter.BuildTableName(tableNameSetting); if (string.IsNullOrWhiteSpace(tableName)) { //Logger.WriteError("tableName is null, feature activaiting?", "UpdateDataTableColumns(SPList spList, string fieldXml, bool isDeleted)"); return(true); } CacheTableStructXml cacheTableStructXml = new CacheTableStructXml { TableName = tableName }; XmlDocument doc = new XmlDocument(); doc.LoadXml(fieldXml); const string fXmlTypeName = "Type"; const string xStaticNameName = "StaticName"; SPFieldType spFieldType; XmlElement root = doc.DocumentElement; Enum.TryParse(root.Attributes[fXmlTypeName].Value, true, out spFieldType); string internalName = root.Attributes[xStaticNameName].Value; string columnType = this.Adapter.GetDataBaseType(spFieldType); Columns column = new Columns { ColumnName = internalName, ColumnType = columnType, ColumnIsDeleted = isDeleted }; cacheTableStructXml.Columns.Add(column); ArchiveCacheCRUD syncCrud = new ArchiveCacheCRUD(spList.ParentWeb.Site); ErrorCode result = syncCrud.CreateOrUpdateTable(cacheTableStructXml); return(result == ErrorCode.NoError); }
public IEnumerable <dynamic> GetCacheTable(List <int> userGroups, int userId, CacheTableStructXml cacheTableStructXml, string sqlFilterString = null, long rowLimit = 0, SPWeb currentWeb = null) { if (currentWeb == null) { currentWeb = SPContext.Current.Web; } if (!userGroups.Contains(userId)) { userGroups.Add(userId); } // собираем колонки string sqlColums = ""; foreach (Columns column in cacheTableStructXml.Columns) { if (!string.IsNullOrWhiteSpace(sqlColums)) { sqlColums += ","; } sqlColums += Constants.SqlScriptTablePrefix + "." + column.ColumnName + " as [" + column.ColumnName + "]"; } // собираем запрос на пермишены по пользователю и его группам string permissionsFilter = string.Empty; if (userGroups.Count > 1) { foreach (int uId in userGroups) { if (permissionsFilter.EndsWith(")")) { permissionsFilter += " OR "; } permissionsFilter += "([UserGroupId] = " + uId + ")"; } } else { permissionsFilter = "[UserGroupId] = " + userGroups.FirstOrDefault(); } StringBuilder sqlStringBuilder = new StringBuilder(); sqlStringBuilder.Append("SELECT"); if (rowLimit > 0) { sqlStringBuilder.AppendFormat(" TOP {0} ", rowLimit); } sqlStringBuilder.AppendFormat(" {0} ", sqlColums); sqlStringBuilder.Append("FROM"); sqlStringBuilder.AppendFormat(" {0} as {1} ", cacheTableStructXml.TableName, Constants.SqlScriptTablePrefix); sqlStringBuilder.AppendFormat("WHERE ([{0}]=1) ", SP.SqlCache.Common.Box.Fields.IsUpdated); if (!currentWeb.UserIsSiteAdmin && !currentWeb.UserIsWebAdmin) { //Если в талице Permissions не найдено строк для текущих групп пользователя, то считаем, что доступ есть. sqlStringBuilder.AppendFormat(" AND ([{0}] IN (SELECT [{0}] FROM [{1}] WHERE ({2})) OR NOT EXISTS(SELECT P.* FROM [{1}] as P WHERE P.[{0}] = D.[{0}])", Constants.ItemUniqueIDFieldName, Constants.PermissionsDataTableName, permissionsFilter); sqlStringBuilder.Append(")"); } string sqlString = sqlStringBuilder.ToString(); // получаем баез доп. фильтров if (string.IsNullOrWhiteSpace(sqlFilterString)) { IEnumerable <dynamic> rows = this.GetDynamicList(sqlString); return(rows); } // получаем c доп. фильтрами else { IEnumerable <dynamic> rows = this.GetDynamicList(sqlString + " AND (" + sqlFilterString + ")"); return(rows); } }