////---------------------------------------------------------------------------------------------------- //public static GridResponse Load_View(GridRequest gridRequest) //{ // var rtn = new GridResponse() { TableType = GridRequest.TableTypes.View }; // // TASK : Check permissions // var userSessionInfo = ASPdatabaseNET.Users.UserSessionLogic.GetUser(); // var usersPermissions = userSessionInfo.UserInfo.AllPermissions; // var tableStructure = DbInterfaces.SQLServerInterface.Views__Get(gridRequest.Id, true, false); // var aspdb_Connection = DataAccess.SQLObjectsCRUD.ASPdb_Connection__Get(tableStructure.ConnectionId); // if (!aspdb_Connection.Active) // throw new Exception("This connection is inactive."); // return rtn; //} //---------------------------------------------------------------------------------------------------- public static bool DeleteRecords(string[] keysToDelete) { var allPermissions = ASPdatabaseNET.Users.UserSessionLogic.GetUser().UserInfo.AllPermissions; var dict_ByTableId = new Dictionary <int, List <UniqueRowKey> >(); //var uniqueRowKeys_List = new List<UniqueRowKey>(); foreach (var keyString in keysToDelete) { var uniqueRowKey = UniqueRowKey.GetFrom_Base64Json(keyString); if (uniqueRowKey.IsValid && uniqueRowKey.ActionType != UniqueRowKey.ActionTypes.New) { if (uniqueRowKey.TableType == GridRequest.TableTypes.Table) { if (!dict_ByTableId.ContainsKey(uniqueRowKey.Id)) { dict_ByTableId.Add(uniqueRowKey.Id, new List <UniqueRowKey>()); } dict_ByTableId[uniqueRowKey.Id].Add(uniqueRowKey); //uniqueRowKeys_List.Add(uniqueRowKey); } } } foreach (int tableId in dict_ByTableId.Keys) { var list = dict_ByTableId[tableId]; if (list != null && list.Count() > 0) { var tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(tableId, false, false, false); if (!allPermissions.CheckPermissions(tableStructure.ConnectionId, tableStructure.Schema, tableStructure.TableId).Delete) { throw new Exception("You do not have delete permission on this table."); } var primaryKeyNames = new string[0]; if (tableStructure.PrimaryKey != null) { primaryKeyNames = (from c in tableStructure.PrimaryKey.Columns select c.ColumnName.Trim()).ToArray(); } var whereStatements = new List <string>(); var sqlParameters = new List <object[]>(); int p = 1; foreach (var item in list) { if (primaryKeyNames.Length != item.Values.Length) { throw new Exception("Invalid PrimaryKey."); } string tmpWhere = ""; for (int i = 0; i < item.Values.Length; i++) { if (tmpWhere != "") { tmpWhere += " and "; } tmpWhere += String.Format(" [{0}] = @Param{1} ", primaryKeyNames[i], p); sqlParameters.Add(new object[] { "@Param" + p, item.Values[i] }); p++; } whereStatements.Add(tmpWhere); } string sqlWhere = ""; foreach (string wherePart in whereStatements) { if (sqlWhere != "") { sqlWhere += " or "; } sqlWhere += "(" + wherePart + ")"; } string sql = String.Format("delete from [{0}].[{1}] where {2}", tableStructure.Schema, tableStructure.TableName, sqlWhere); // -- Save History: foreach (int tmpTableId in dict_ByTableId.Keys) { foreach (var uniqueRowKey in dict_ByTableId[tmpTableId]) { var historyRecord = new UI.PageParts.Record.Objs_History.HistoryRecord() { HistoryId = -1, TableId = tmpTableId, KeyValue = uniqueRowKey.Values, HistoryType = PageParts.Record.Objs_History.HistoryRecord.HistoryTypes.Delete, IsPartial = false }; UI.PageParts.Record.Backend.HistoryLogic.Initialize_HistoryRecord_AllFields(historyRecord, "Left"); UI.PageParts.Record.Backend.HistoryLogic.Save_HistoryRecord(historyRecord); } } //ASPdb.Framework.Debug.WriteLine(sql); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) { foreach (object[] parameter in sqlParameters) { command.AddParameter(parameter[0].ToString(), parameter[1]); } command.ExecuteNonQuery(); } } } return(true); }
//---------------------------------------------------------------------------------------------------- public static string Save__OLD(RecordInfo recordInfo) { if (recordInfo.UniqueRowObj.TableType != TableGrid.Objs.GridRequest.TableTypes.Table) { throw new Exception("TableType not supported."); } var tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(recordInfo.UniqueRowObj.Id, false, true, false); var userSessionInfo = ASPdatabaseNET.Users.UserSessionLogic.GetUser(); var permission = userSessionInfo.UserInfo.AllPermissions.CheckPermissions(tableStructure.ConnectionId, tableStructure.Schema, tableStructure.TableId); if (recordInfo.FieldValues == null || recordInfo.FieldValues.Length < 1) { throw new Exception("Nothing to save.\n\nPlease edit a field before saving."); } string sql = ""; var sqlParameters = new List <object[]>(); int p = 1; bool doUpdate = (recordInfo.ActionType == TableGrid.Objs.UniqueRowKey.ActionTypes.Get); if (doUpdate) // -- update { if (!permission.Edit) { throw new Exception("You do not have edit permission on this table."); } string set = "", where = ""; for (int i = 0; i < recordInfo.FieldValues.Length; i++) { var column = recordInfo.Columns[recordInfo.FieldValues[i].Index]; if (!column.IsIdentity) { if (set != "") { set += ", "; } set += String.Format("[{0}] = @Param{1}", column.ColumnName, p); object value = recordInfo.FieldValues[i].Value; if (recordInfo.FieldValues[i].IsNull || value == null) { value = DBNull.Value; } sqlParameters.Add(new object[] { "@Param" + p, value }); p++; } } for (int i = 0; i < recordInfo.UniqueRowObj.Values.Length; i++) { string primaryKeyName = tableStructure.PrimaryKey.Columns[i].ColumnName; if (where != "") { where += " and "; } where += String.Format(" ([{0}] = @Param{1} ) ", primaryKeyName, p); sqlParameters.Add(new object[] { "@Param" + p, recordInfo.UniqueRowObj.Values[i] }); p++; } sql = String.Format(@" update [{0}].[{1}] set {2} where {3} ", tableStructure.Schema, tableStructure.TableName, set, where); } else // -- insert new { if (!permission.Insert) { throw new Exception("You do not have insert permission on this table."); } string columnsSQL = "", valuesSQL = ""; for (int i = 0; i < recordInfo.FieldValues.Length; i++) { var column = recordInfo.Columns[recordInfo.FieldValues[i].Index]; if (!column.IsIdentity) { if (columnsSQL != "") { columnsSQL += ", "; valuesSQL += ", "; } columnsSQL += "[" + column.ColumnName + "]"; valuesSQL += "@Param" + p; object value = recordInfo.FieldValues[i].Value; if (recordInfo.FieldValues[i].IsNull || value == null) { value = DBNull.Value; } sqlParameters.Add(new object[] { "@Param" + p, value }); p++; } } sql = String.Format(@" insert into [{0}].[{1}] ( {2} ) values ( {3} ) ", tableStructure.Schema, tableStructure.TableName, columnsSQL, valuesSQL); } //ASPdb.Framework.Debug.WriteLine(sql); UI.PageParts.Record.Objs_History.HistoryRecord historyRecord1 = null; UI.PageParts.Record.Objs_History.HistoryRecord historyRecord2 = null; var keyValue = recordInfo.UniqueRowObj.Values; if (doUpdate) { historyRecord1 = new Objs_History.HistoryRecord() { HistoryId = -1, TableId = tableStructure.TableId, KeyValue = keyValue, HistoryType = PageParts.Record.Objs_History.HistoryRecord.HistoryTypes.Update, IsPartial = false }; HistoryLogic.Initialize_HistoryRecord_AllFields(historyRecord1, "Left"); } using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) { foreach (object[] parameter in sqlParameters) { command.AddParameter(parameter[0].ToString(), parameter[1]); } command.ExecuteNonQuery(); } if (doUpdate) { historyRecord2 = new Objs_History.HistoryRecord() { HistoryId = -1, TableId = tableStructure.TableId, KeyValue = keyValue, HistoryType = PageParts.Record.Objs_History.HistoryRecord.HistoryTypes.Update, IsPartial = false }; HistoryLogic.Initialize_HistoryRecord_AllFields(historyRecord2, "Right"); var newValuesDict = new Dictionary <string, Objs_History.Item>(); foreach (var item in historyRecord2.HistoryJsonObj.Fields) { if (!newValuesDict.ContainsKey(item.cn)) { newValuesDict.Add(item.cn, item); } } int historyCount = HistoryLogic.Get_HistoryCount(tableStructure.TableId, keyValue); historyRecord1.IsPartial = (historyCount > 0); var fieldsList = new List <Objs_History.Item>(); foreach (var item1 in historyRecord1.HistoryJsonObj.Fields) { var item2 = new Objs_History.Item(); if (newValuesDict.ContainsKey(item1.cn)) { item2 = newValuesDict[item1.cn]; } item1.v2 = item2.v2; if (item1.v1 == item1.v2) { item1.match = true; item1.v2 = null; } if (historyRecord1.IsPartial == false || item1.match == false) { fieldsList.Add(item1); } } historyRecord1.HistoryJsonObj.Fields = fieldsList.ToArray(); HistoryLogic.Save_HistoryRecord(historyRecord1); } if (recordInfo.UniqueRowObj.ActionType != TableGrid.Objs.UniqueRowKey.ActionTypes.Get) { return(""); } else { return(recordInfo.UniqueRowObj.To_Base64Json()); } }