Exemplo n.º 1
0
        //----------------------------------------------------------------------------------------------------
        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());
        }
Exemplo n.º 2
0
        //----------------------------------------------------------------------------------------------------
        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);
        }
Exemplo n.º 3
0
        //----------------------------------------------------------------------------------------------------
        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();
        }