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