//----------------------------------------------------------------------------------------------------
private static Column[] Tables__Get__GetColumns(TableStructure tableStructure)
{
var rtn = new List <Column>();
string sql = SQLServer_SQLBuilders.BuildSql__Column_SelectAll(tableStructure);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
while (reader.Read())
{
var column = new Column()
{
ConnectionId = tableStructure.ConnectionId,
Schema = reader.Get("Schema", ""),
TableName = reader.Get("TableName", ""),
OrdinalPosition = reader.Get("OrdinalPosition", 0),
ColumnName = reader.Get("ColumnName", ""),
DataType_Name = reader.Get("DataType", ""),
MaxLength = reader.Get("MaxLength", 0),
Precision = reader.Get("Precision", 0),
Scale = reader.Get("Scale", 0),
AllowNulls = reader.Get("AllowNulls", "NO").ToUpper() == "YES",
DefaultValue = reader.GetString_OrNullDefault("DefaultValue"),
IsPrimaryKey = reader.Get("IsPrimaryKey", 0) == 1,
IsIdentity = reader.Get("IsIdentity", 0) == 1,
Identity = null
};
if (column.IsIdentity)
{
column.Identity = new Identity()
{
ConnectionId = tableStructure.ConnectionId,
Schema = reader.Get("Schema", ""),
TableName = reader.Get("TableName", ""),
ColumnName = reader.Get("ColumnName", ""),
Seed = reader.Get("Identity_Seed", 0),
Increment = reader.Get("Identity_Increment", 0),
CurrentIdentity = reader.Get("Identity_CurrentIdentity", 0)
}
}
;
column.Populate__DataType_FullString();
column.Fix_DefaultValue_ForDisplay();
rtn.Add(column);
}
}
}
return(rtn.ToArray());
}
//----------------------------------------------------------------------------------------------------
public static RecordInfo Get(string uniqueRowKey)
{
var userSessionInfo = ASPdatabaseNET.Users.UserSessionLogic.GetUser();
var usersPermissions = userSessionInfo.UserInfo.AllPermissions;
var uniqueRowObj = UI.TableGrid.Objs.UniqueRowKey.GetFrom_Base64Json(uniqueRowKey);
if (!uniqueRowObj.IsValid)
{
throw new Exception("Invalid Key");
}
if (uniqueRowObj.TableType != TableGrid.Objs.GridRequest.TableTypes.Table)
{
throw new Exception("TableType not supported.");
}
var tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(uniqueRowObj.Id, false, true, false);
var aspdb_Connection = DataAccess.SQLObjectsCRUD.ASPdb_Connection__Get(tableStructure.ConnectionId);
if (!aspdb_Connection.Active)
{
throw new Exception("This connection is inactive.");
}
var rtn = new RecordInfo()
{
TableType = uniqueRowObj.TableType,
ConnectionId = tableStructure.ConnectionId,
Id = uniqueRowObj.Id,
Schema = tableStructure.Schema,
TableName = tableStructure.TableName,
Columns = tableStructure.Columns,
ActionType = uniqueRowObj.ActionType,
UniqueRowObj = uniqueRowObj,
PermissionValues = usersPermissions.CheckPermissions(tableStructure.ConnectionId, tableStructure.Schema, tableStructure.TableId),
ChangeHistory_IsOn = UI.PageParts.Record.Backend.HistoryLogic.DoSaveHistory
};
if (rtn.PermissionValues.View == false)
{
throw new Exception("You do not have permission to view this record.");
}
var tmp_PrimaryKeyNames = new List <string>();
var tmp_PriamryKeyIndexPositions = new List <int>();
for (int i = 0; i < rtn.Columns.Length; i++)
{
if (rtn.Columns[i].IsPrimaryKey)
{
tmp_PrimaryKeyNames.Add(rtn.Columns[i].ColumnName);
tmp_PriamryKeyIndexPositions.Add(i);
}
}
rtn.PrimaryKeyNames = tmp_PrimaryKeyNames.ToArray();
rtn.PriamryKeyIndexPositions = tmp_PriamryKeyIndexPositions.ToArray();
if (uniqueRowObj.ActionType == TableGrid.Objs.UniqueRowKey.ActionTypes.New)
{
rtn.FieldValues = new FieldValue[rtn.Columns.Length];
for (int i = 0; i < rtn.Columns.Length; i++)
{
var fieldValue = new FieldValue()
{
Index = i
};
string defaultValue = rtn.Columns[i].DefaultValue;
if (defaultValue != null)
{
if (defaultValue.StartsWith("'") && defaultValue.EndsWith("'"))
{
defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
}
}
if (rtn.Columns[i].DataType_Name == "bit")
{
if (defaultValue != null)
{
if (defaultValue == "1" || defaultValue.ToLower() == "true")
{
defaultValue = "true";
}
}
}
if (defaultValue != null)
{
fieldValue.Value = defaultValue;
fieldValue.IsNull = false;
}
else
{
fieldValue.Value = "";
fieldValue.IsNull = true;
}
rtn.FieldValues[i] = fieldValue;
}
}
else if (uniqueRowObj.ActionType == TableGrid.Objs.UniqueRowKey.ActionTypes.Clone)
{
}
else
{
if (rtn.PrimaryKeyNames.Length != uniqueRowObj.Values.Length)
{
throw new Exception("Invalid PrimaryKey.");
}
string tmpWhere = "";
var sqlParameters = new List <object[]>();
int p = 1;
for (int i = 0; i < uniqueRowObj.Values.Length; i++)
{
if (tmpWhere != "")
{
tmpWhere += " and ";
}
tmpWhere += String.Format(" [{0}] = @Param{1} ", rtn.PrimaryKeyNames[i], p);
sqlParameters.Add(new object[] { "@Param" + p, uniqueRowObj.Values[i] });
p++;
}
string sql = String.Format(@"
select * from [{0}].[{1}]
where {2}
", tableStructure.Schema, tableStructure.TableName, tmpWhere);
rtn.FieldValues = new FieldValue[rtn.Columns.Length];
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
foreach (object[] parameter in sqlParameters)
{
command.AddParameter(parameter[0].ToString(), parameter[1]);
}
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
for (int i = 0; i < rtn.Columns.Length; i++)
{
var fieldValue = new FieldValue()
{
Index = i
};
fieldValue.Value = reader.GetString_OrNullDefault(rtn.Columns[i].ColumnName);
if (fieldValue.Value == null)
{
fieldValue.Value = "";
fieldValue.IsNull = true;
}
rtn.FieldValues[i] = fieldValue;
}
}
else
{
//for (int i = 0; i < rtn.Columns.Length; i++)
// rtn.FieldValues[i] = new FieldValue() { Index = 1, Value = "", IsNull = true };
throw new Exception("\nData needs to be refreshed. \nTherefore the app will now return to the Records List.");
}
}
}
}
return(rtn);
}
//----------------------------------------------------------------------------------------------------
public static void Initialize_HistoryRecord_AllFields(HistoryRecord historyRecord, string populateSide)
{
var tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(historyRecord.TableId, false, true, false);
var valuesDict = new Dictionary <string, string>();
string sqlWhere = "";
int pkCount = tableStructure.PrimaryKey.Columns.Length;
for (int i = 0; i < pkCount; i++)
{
if (sqlWhere != "")
{
sqlWhere += " and ";
}
sqlWhere += String.Format(" [{0}] = @Value{1} ", tableStructure.PrimaryKey.Columns[i].ColumnName, i);
}
string sql = String.Format(@"select * from [{0}].[{1}] where {2}",
tableStructure.Schema, tableStructure.TableName, sqlWhere);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
{
for (int i = 0; i < pkCount; i++)
{
string pkValue = "";
try { pkValue = historyRecord.KeyValue[i]; }
catch { }
command.AddParameter("@Value" + i, pkValue);
}
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
string columnName = reader.GetName(i);
string columnName_L = columnName.Trim().ToLower();
string value = reader.GetString_OrNullDefault(columnName);
if (!valuesDict.ContainsKey(columnName_L))
{
valuesDict.Add(columnName_L, value);
}
}
}
}
var fieldsList = new List <Item>();
foreach (string key_L in valuesDict.Keys)
{
var item = new Item()
{
cn = key_L, v1 = null, v2 = null
};
string value = valuesDict[key_L];
if (populateSide.ToLower() == "left")
{
item.v1 = value;
}
else if (populateSide.ToLower() == "right")
{
item.v2 = value;
}
fieldsList.Add(item);
}
if (historyRecord.HistoryJsonObj == null)
{
historyRecord.HistoryJsonObj = new HistoryJsonObj();
}
historyRecord.HistoryJsonObj.Fields = fieldsList.ToArray();
}