//----------------------------------------------------------------------------------------------------
public static GridResponse Load_Table(GridRequest gridRequest, bool truncateValues, bool isAView)
{
var userSessionInfo = ASPdatabaseNET.Users.UserSessionLogic.GetUser();
var usersPermissions = userSessionInfo.UserInfo.AllPermissions;
DbInterfaces.TableObjects.TableStructure tableStructure = null;
if (!isAView)
{
tableStructure = DbInterfaces.SQLServerInterface.Tables__Get(gridRequest.Id, false, true, false);
}
else
{
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.");
}
var uniqueRowKey_ForNew = new UniqueRowKey()
{
TableType = GridRequest.TableTypes.Table, Id = tableStructure.TableId, ActionType = UniqueRowKey.ActionTypes.New
};
var rtn = new GridResponse()
{
TableType = GridRequest.TableTypes.Table,
ConnectionId = tableStructure.ConnectionId,
Id = gridRequest.Id,
TableName = tableStructure.TableName,
Schema = tableStructure.Schema,
UniqueKey_ForNewRecord = uniqueRowKey_ForNew.To_Base64Json(),
PermissionValues = usersPermissions.CheckPermissions(tableStructure.ConnectionId, tableStructure.Schema, tableStructure.TableId),
IsAdmin = usersPermissions.IsAdmin
};
if (isAView)
{
rtn.TableType = GridRequest.TableTypes.View;
}
if (rtn.PermissionValues.View == false)
{
throw new Exception("You do not have permission to view this table/view.");
}
Helper1__PopulateAdditionalTableName(rtn);
//var columnsDict = new Dictionary<string, DbInterfaces.TableObjects.Column>();
//foreach (var item in tableStructure.Columns)
// if (!columnsDict.ContainsKey(item.ColumnName.Trim().ToLower()))
// columnsDict.Add(item.ColumnName.Trim().ToLower(), item);
var primaryKeyNames_L = new string[0];
if (tableStructure.PrimaryKey != null)
{
primaryKeyNames_L = (from c in tableStructure.PrimaryKey.Columns select c.ColumnName.Trim().ToLower()).ToArray();
}
var columnNames_L = (from c in tableStructure.Columns select c.ColumnName.Trim().ToLower()).ToArray();
List <object[]> sqlParameters;
string sqlPart1, sqlPart2_OrderBy;
Load_Table__BuildBottomSQL(gridRequest, rtn, true, columnNames_L, out sqlParameters, out sqlPart1, out sqlPart2_OrderBy);
string top = String.Format(" top {0} ", gridRequest.DisplayTopNRows);
if (gridRequest.DisplayTopNRows < 1)
{
top = " top 100 ";
}
if (gridRequest.DisplayTopNRows == -2)
{
top = " ";
}
string sql_Select = String.Format(@"
select {0}
*
{1}
{2}
"
, top
, sqlPart1
, sqlPart2_OrderBy);
string sql_TotalCount = String.Format(@"
select count(*) as [Count1]
{0}
"
, sqlPart1);
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(rtn.ConnectionId, sql_Select))
{
foreach (var param in sqlParameters)
{
command.AddParameter(param[0].ToString(), param[1]);
}
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
// build header
var headersList = new List <GridHeaderItem>();
var tmp1 = new List <string>(); var tmp2 = new List <int>();
for (int i = 0; i < reader.FieldCount; i++)
{
var headerItem = new GridHeaderItem();
headerItem.IndexPosition = i;
headerItem.FieldName = reader.GetName(i);
headerItem.DataTypeName = reader.GetDataTypeName(i);
headersList.Add(headerItem);
string key_L = headerItem.FieldName.Trim().ToLower();
if (primaryKeyNames_L.Contains(key_L))
{
headerItem.IsPrimaryKey = true;
tmp1.Add(headerItem.FieldName);
tmp2.Add(headerItem.IndexPosition);
}
}
rtn.HeaderItems = headersList.ToArray();
rtn.PrimaryKeyNames = tmp1.ToArray();
rtn.PriamryKeyIndexPositions = tmp2.ToArray();
int truncateLength = 100;
var rowsList = new List <GridRow>();
while (reader.Read())
{
var row = new GridRow();
row.Values = new string[rtn.HeaderItems.Length];
for (int i = 0; i < rtn.HeaderItems.Length; i++)
{
string value = reader.Get(i, "");
value = HttpContext.Current.Server.HtmlEncode(value);
if (truncateValues)
{
if (value.Length > truncateLength + 1)
{
value = value.Substring(0, truncateLength);
var chopLength = value.Split(new char[] { ' ' }).Last().Length;
if (chopLength < 21)
{
value = value.Substring(0, value.Length - chopLength) + "...";
}
else
{
value += " ...";
}
}
}
row.Values[i] = value;
}
var uniqueRowKey = Get_UniqueRowKey("t", rtn.Id, row, rtn.PriamryKeyIndexPositions);
row.UniqueKey = uniqueRowKey.To_Base64Json();
rowsList.Add(row);
}
rtn.Rows = rowsList.ToArray();
rtn.Count_DisplayItems = rtn.Rows.Length;
}
}
using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(rtn.ConnectionId, sql_TotalCount))
{
foreach (var param in sqlParameters)
{
command.AddParameter(param[0].ToString(), param[1]);
}
using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
{
if (reader.Read())
{
rtn.Count_TotalItems = reader.Get("Count1", -1);
}
}
}
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();
}