Exemple #1
0
        //----------------------------------------------------------------------------------------------------
        public static bool SetImpersonationUser(int userId)
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            var userSession = GetUser();

            if (userSession != null && userSession.IsLoggedIn && userSession.Impersonation_IsAllowed && userSession.Impersonation_IsOn)
            {
                string sql = "select * from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users] where [UserId] = @UserId";
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.AddParameter("@UserId", userId);
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                    {
                        if (reader.Read())
                        {
                            userSession.UserInfo = new UserInfo()
                            {
                                UserId    = reader.Get("UserId", -1),
                                Username  = reader.Get("Username", ""),
                                Email     = reader.Get("Email", ""),
                                FirstName = reader.Get("FirstName", ""),
                                LastName  = reader.Get("LastName", ""),
                                IsAdmin   = reader.Get("IsAdmin", false)
                            };
                            userSession.UserInfo.AllPermissions = LoadAllPermissions(userSession.UserInfo.UserId, userSession.UserInfo.IsAdmin);
                        }
                    }
                }
                return(true);
            }
            return(false);
        }
Exemple #2
0
        //----------------------------------------------------------------------------------------------------
        private static void ASPdb_View__SyncViewsWithProperties_Helper1__GetAndPopulate(int connectionId, List <ASPdb_View> viewsList, Dictionary <string, ASPdb_View> dict)
        {
            string sql = "select * from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Views] where [ConnectionId] = @ConnectionId order by [ViewName]";

            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
            {
                command.AddParameter("@ConnectionId", connectionId);
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    while (reader.Read())
                    {
                        string db_ViewName      = reader.Get("ViewName", "");
                        string db_Schema        = reader.Get("Schema", "");
                        string db_UniqueNameKey = db_Schema.ToLower().Trim() + "." + db_ViewName.ToLower().Trim();
                        if (dict.ContainsKey(db_UniqueNameKey))
                        {
                            ASPdb_View item = dict[db_UniqueNameKey];
                            item.ViewId       = reader.Get("ViewId", -1);
                            item.ConnectionId = reader.Get("ConnectionId", -1);
                            item.ViewName     = db_ViewName;
                            item.Schema       = db_Schema;
                            item.Hide         = reader.Get("Hide", false);
                        }
                    }
                }
            }
        }
Exemple #3
0
        //----------------------------------------------------------------------------------------------------
        /// <summary>
        /// Key in return Dictionary is lowercase index name.
        /// Does not include sub columns infos.
        /// </summary>
        private static Dictionary <string, Index> Index__GetAll_InConnection(int connectionId)
        {
            var    rtn = new Dictionary <string, Index>();
            string sql = SQLServer_SQLBuilders.BuildSql__Index_SelectAll();

            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionId, sql))
            {
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    while (reader.Read())
                    {
                        var index = new Index()
                        {
                            ConnectionId = connectionId,
                            Schema       = reader.Get("Schema", ""),
                            TableName    = reader.Get("TableName", ""),
                            IndexName    = reader.Get("IndexName", ""),
                            Columns      = null,
                            IsUnique     = reader.Get("IsUnique", false)
                        };
                        string key_IndexName = index.IndexName.ToLower();
                        if (!rtn.ContainsKey(key_IndexName))
                        {
                            rtn.Add(key_IndexName, index);
                        }
                    }
                }
            }
            return(rtn);
        }
Exemple #4
0
        //----------------------------------------------------------------------------------------------------
        public static ASPdb_Value[] Get(string key)
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            var rtn = new List <ASPdb_Value>();

            try
            {
                string sql = String.Format("select * from [{0}].[ASPdb_Values] where [Key] = @Key order by [ValueId]",
                                           Config.SystemProperties.AppSchema);
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.AddParameter("@Key", key);
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                    {
                        while (reader.Read())
                        {
                            rtn.Add(new ASPdb_Value()
                            {
                                ValueId = reader.Get("ValueId", -1),
                                Key     = reader.Get("Key", ""),
                                Value   = reader.Get("Value", "")
                            });
                        }
                    }
                }
            }
            catch { }
            return(rtn.ToArray());
        }
Exemple #5
0
        //----------------------------------------------------------------------------------------------------
        public static SchemaInfo[] Schemas__GetAll(int connectionId)
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            var    rtn = new List <SchemaInfo>();
            string connectionString = DataAccess.SQLObjectsCRUD.ASPdb_Connection__Get(connectionId).GetConnectionString();
            string sql = @"
                SELECT t1.Schema_Id, t1.principal_id,
                t2.Catalog_Name, t2.Schema_Name, t2.Schema_Owner FROM sys.schemas t1
                Inner Join INFORMATION_SCHEMA.SCHEMATA t2 on t1.name = t2.SCHEMA_NAME 
                where t2.SCHEMA_OWNER = 'dbo'
                order by t1.name
                ";

            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionString, sql))
            {
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    while (reader.Read())
                    {
                        var schemaInfo = new SchemaInfo()
                        {
                            SchemaId    = reader.Get("Schema_Id", -1),
                            PrincipalId = reader.Get("Principal_Id", -1),
                            CatalogName = reader.Get("Catalog_Name", ""),
                            SchemaName  = reader.Get("Schema_Name", ""),
                            SchemaOwner = reader.Get("Schema_Owner", "")
                        };
                        rtn.Add(schemaInfo);
                    }
                }
            }
            return(rtn.ToArray());
        }
Exemple #6
0
        //----------------------------------------------------------------------------------------------------
        /// <summary>
        /// Always uses cache (if already stored in cache).
        /// Does not perform table sync.
        /// </summary>
        public static ASPdb_View ASPdb_View__Get(int viewId)
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            var cache = ASPdatabaseNET.Memory.AppCache.Get();

            if (cache.ASPdb_View_Dictionary2 != null)
            {
                if (cache.ASPdb_View_Dictionary2.ContainsKey(viewId) && cache.ASPdb_View_Dictionary2[viewId] != null)
                {
                    return(cache.ASPdb_View_Dictionary2[viewId]); // return from cache if it's there
                }
            }
            ASPdb_View rtn = null;

            int connectionId = -1;
            string sql       = "select [ConnectionId] from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Views] where [ViewId] = @ViewId";
            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
            {
                command.AddParameter("@ViewId", viewId);
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    if (reader.Read())
                    {
                        connectionId = reader.Get("ConnectionId", -1);
                    }
                    if (connectionId < 0)
                    {
                        return(null);
                    }
                }
            }
            var aspdb_Views = ASPdb_View__GetAll(connectionId);
            for (int i = 0; i < aspdb_Views.Count; i++)
            {
                if (aspdb_Views[i].ViewId == viewId)
                {
                    rtn = aspdb_Views[i];
                    i   = aspdb_Views.Count + 1;
                }
            }

            // store in cache before returning
            if (rtn != null)
            {
                if (cache.ASPdb_View_Dictionary2 == null)
                {
                    cache.ASPdb_View_Dictionary2 = new Dictionary <int, ASPdb_View>();
                }
                if (cache.ASPdb_View_Dictionary2.ContainsKey(viewId))
                {
                    cache.ASPdb_View_Dictionary2[viewId] = rtn;
                }
                else
                {
                    cache.ASPdb_View_Dictionary2.Add(viewId, rtn);
                }
            }
            return(rtn);
        }
Exemple #7
0
        //----------------------------------------------------------------------------------------------------
        public static List <ASPdb_Connection> ASPdb_Connection__GetAll(bool useCache)
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            var cache = ASPdatabaseNET.Memory.AppCache.Get();

            if (useCache)
            {
                if (cache.ASPdb_Database_List != null && cache.ASPdb_Database_List.Count > 0)
                {
                    return(cache.ASPdb_Database_List);
                }
            }

            var    rtn = new List <ASPdb_Connection>();
            string sql = @"
                select T1.*, T2.Username from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections] as T1 
                left join [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users] as T2 on T1.CreatedByUserId = T2.UserId
                order by [ConnectionName]";

            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
            {
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    DateTime?nullDateTime = null;
                    while (reader.Read())
                    {
                        var item = new ASPdb_Connection()
                        {
                            ConnectionId           = reader.Get("ConnectionId", -1),
                            SiteId                 = reader.Get("SiteId", -1),
                            ConnectionName         = reader.Get("ConnectionName", ""),
                            ConnectionType         = reader.Get("ConnectionType", ""),
                            ParametersType         = reader.Get("ParametersType", ""),
                            Active                 = reader.Get("Active", false),
                            DateTimeCreated        = reader.Get("DateTimeCreated", nullDateTime),
                            CreatedByUserId        = reader.Get("CreatedByUserId", -1),
                            CreatedByUsername      = reader.Get("Username", ""),
                            Param_ServerAddress    = reader.Get("Param_ServerAddress", ""),
                            Param_DatabaseName     = reader.Get("Param_DatabaseName", ""),
                            Param_U                = reader.Get("Param_U", ""),
                            Param_P                = ASPdb_Connection__GetDecryptedPassword_OrNull(reader.Get("Param_P", "")),
                            Param_ConnectionString = reader.Get("Param_ConnectionString", "")
                        };
                        rtn.Add(item);
                    }
                }
            }
            if (useCache)
            {
                cache.ASPdb_Database_List = rtn;
                foreach (var item in rtn)
                {
                    item.Param_P = ""; // don't leave passwords text in cache
                }
            }
            return(rtn);
        }
Exemple #8
0
        //----------------------------------------------------------------------------------------------------
        public static PrimaryKey PrimaryKey__Get(TableStructure tableStructure)
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            PrimaryKey rtn = null;


            var colDict = new Dictionary <string, Column>();

            foreach (var item in tableStructure.Columns)
            {
                if (!colDict.ContainsKey(item.ColumnName.ToLower()))
                {
                    colDict.Add(item.ColumnName.ToLower(), item);
                }
            }

            var    primaryKeyColumns_List = new List <PrimaryKeyColumn>();
            string sql = SQLServer_SQLBuilders.BuildSql__PrimaryKey_Select(tableStructure);

            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
            {
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    while (reader.Read())
                    {
                        if (rtn == null)
                        {
                            rtn = new PrimaryKey()
                            {
                                ConnectionId   = tableStructure.ConnectionId,
                                TableId        = tableStructure.TableId,
                                Schema         = reader.Get("Schema", ""),
                                TableName      = reader.Get("TableName", ""),
                                ConstraintName = reader.Get("ConstraintName", "")
                            }
                        }
                        ;
                        var primaryKeyItem = new PrimaryKeyColumn()
                        {
                            ColumnName      = reader.Get("ColumnName", ""),
                            OrdinalPosition = reader.Get("OrdinalPosition", 0)
                        };
                        if (colDict.ContainsKey(primaryKeyItem.ColumnName.ToLower()))
                        {
                            primaryKeyItem.Identity = colDict[primaryKeyItem.ColumnName.ToLower()].Identity;
                        }
                        primaryKeyColumns_List.Add(primaryKeyItem);
                    }
                }
            }
            if (rtn != null)
            {
                rtn.Columns = primaryKeyColumns_List.ToArray();
            }
            return(rtn);
        }
Exemple #9
0
        //----------------------------------------------------------------------------------------------------
        public static int Get_HistoryCount(int tableId, string[] keyValue)
        {
            int    rtn                 = -1;
            string keyValueStr         = PrimaryKey_ToString(keyValue);
            string keyValueStr_Orig    = keyValueStr;
            bool   keyValueIsTruncated = (keyValueStr.Length > 50);

            if (keyValueIsTruncated)
            {
                keyValueStr = keyValueStr.Substring(0, 50);
            }

            if (!keyValueIsTruncated)
            {
                string sql = String.Format("select count(*) as [Count1] from [{0}].[{1}] where [TableId] = @TableId and [KeyValue] = @KeyValue and [KeyValueIsTruncated] = @KeyValueIsTruncated",
                                           Config.SystemProperties.AppSchema, "ASPdb_History");
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(History_ConnectionString(), sql))
                {
                    command.AddParameter("@TableId", tableId);
                    command.AddParameter("@KeyValue", keyValueStr);
                    command.AddParameter("@KeyValueIsTruncated", false);
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        if (reader.Read())
                        {
                            rtn = reader.Get("Count1", -1);
                        }
                }
            }
            else
            {
                rtn = 0;
                string sql = String.Format("select [HistoryId], [HistoryJSON] from [{0}].[{1}] where [TableId] = @TableId and [KeyValue] = @KeyValue and [KeyValueIsTruncated] = @KeyValueIsTruncated",
                                           Config.SystemProperties.AppSchema, "ASPdb_History");
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(History_ConnectionString(), sql))
                {
                    command.AddParameter("@TableId", tableId);
                    command.AddParameter("@KeyValue", keyValueStr);
                    command.AddParameter("@KeyValueIsTruncated", true);
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        if (reader.Read())
                        {
                            try
                            {
                                int historyId      = reader.Get("HistoryId", -1);
                                var historyJsonObj = (new JavaScriptSerializer()).Deserialize <HistoryJsonObj>(reader.Get("HistoryJSON", ""));
                                if (historyJsonObj.KeyValue == keyValueStr_Orig)
                                {
                                    rtn++;
                                }
                            }
                            catch { }
                        }
                }
            }
            return(rtn);
        }
Exemple #10
0
        //----------------------------------------------------------------------------------------------------
        public static Index[] Index__Get(TableStructure tableStructure)
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            var    rtn = new Dictionary <string, Index>(); // key : indexName_L
            string sql = SQLServer_SQLBuilders.BuildSql__Index_Select(tableStructure);

            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql))
            {
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    while (reader.Read())
                    {
                        string schema      = reader.Get("Schema", "");
                        string tableName   = reader.Get("TableName", "");
                        string indexName   = reader.Get("IndexName", "");
                        string indexName_L = indexName.ToLower();
                        bool   isUnique    = reader.Get("IsUnique", false);

                        int    columnId     = reader.Get("ColumnId", 0);
                        string columnName   = reader.Get("ColumnName", "");
                        bool   isDescending = reader.Get("IsDescending", false);

                        Index index;
                        if (rtn.ContainsKey(indexName_L))
                        {
                            index = rtn[indexName_L];
                        }
                        else
                        {
                            index = new Index();
                            rtn.Add(indexName_L, index);
                            index.ConnectionId = tableStructure.ConnectionId;
                            index.Schema       = schema;
                            index.TableName    = tableName;
                            index.IndexName    = indexName;
                            index.IsUnique     = isUnique;
                            index.Columns      = new IndexColumn[0];
                        }
                        var indexColumnsList = index.Columns.ToList();
                        var indexColumn      = new IndexColumn()
                        {
                            ColumnId      = columnId,
                            ColumnName    = columnName,
                            SortDirection = IndexColumn.E_SortTypes.Ascending
                        };
                        if (isDescending)
                        {
                            indexColumn.SortDirection = IndexColumn.E_SortTypes.Descending;
                        }
                        indexColumnsList.Add(indexColumn);
                        index.Columns = indexColumnsList.ToArray();
                    }
                }
            }
            return(rtn.Values.ToArray());
        }
Exemple #11
0
        //----------------------------------------------------------------------------------------------------
        public static UserInfo Login(string username, string password)
        {
            if (!Subscription.SubscriptionAppState.ValidateActiveSubscribers())
            {
                throw new Exception("Validation Error");
            }

            string errorMsg = "Invalid Login Credentials";

            try
            {
                string sql = "select * from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users] where [Username] = @Username and [Active] = 1";
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.AddParameter("@Username", username);
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                    {
                        if (reader.Read())
                        {
                            if (BCrypt.Net.BCrypt.Verify(password, reader.Get("Password", "")))
                            {
                                var userSession = GetUser();
                                userSession.UserInfo = new UserInfo()
                                {
                                    UserId    = reader.Get("UserId", -1),
                                    Username  = reader.Get("Username", ""),
                                    Email     = reader.Get("Email", ""),
                                    FirstName = reader.Get("FirstName", ""),
                                    LastName  = reader.Get("LastName", ""),
                                    Active    = reader.Get("Active", false),
                                    IsAdmin   = reader.Get("IsAdmin", false)
                                };
                                userSession.Impersonation_IsAllowed = userSession.UserInfo.IsAdmin;
                                SaveLastLoginTime(userSession.UserInfo.UserId);

                                userSession.UserInfo.AllPermissions = LoadAllPermissions(userSession.UserInfo.UserId, userSession.UserInfo.IsAdmin);


                                if (userSession.Impersonation_IsAllowed)
                                {
                                    userSession.Impersonation_ActualUser = userSession.UserInfo;
                                }
                                return(userSession.UserInfo);
                            }
                        }
                    }
                }
            }
            catch (Exception exc) { errorMsg = "Error in Login"; ASPdb.Framework.Debug.RecordException(exc); }

            throw new Exception(errorMsg);
        }
Exemple #12
0
        //----------------------------------------------------------------------------------------------------
        public static UserInfo[] GetImpersonationList()
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            var rtn         = new List <UserInfo>();
            var userSession = GetUser();

            if (userSession.Impersonation_ActualUser == null)
            {
                return(null);
            }

            var actualUser = userSession.Impersonation_ActualUser;

            rtn.Add(new UserInfo()
            {
                UserId    = actualUser.UserId,
                LastName  = "You",
                FirstName = "",
                Username  = actualUser.Username,
                Active    = actualUser.Active,
                Email     = actualUser.Email
            });


            string sql = "select * from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users] where [UserId] > -1 order by [Active] desc, [LastName], [FirstName], [Username]";

            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
            {
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    while (reader.Read())
                    {
                        var userInfo = new UserInfo()
                        {
                            UserId    = reader.Get("UserId", -1),
                            Username  = reader.Get("Username", ""),
                            FirstName = reader.Get("FirstName", ""),
                            LastName  = reader.Get("LastName", ""),
                            Email     = reader.Get("Email", ""),
                            Active    = reader.Get("Active", false)
                        };
                        if (userInfo.UserId != actualUser.UserId)
                        {
                            rtn.Add(userInfo);
                        }
                    }
                }
            }

            return(rtn.ToArray());
        }
Exemple #13
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());
        }
Exemple #14
0
        //----------------------------------------------------------------------------------------------------
        private static void ASPdb_Connection__SaveInsert(ASPdb_Connection connectionInfo)
        {
            ASPdb_Connection__CheckForDuplicateConnectionName(connectionInfo);
            int?userId = null;

            try { userId = Users.UserSessionLogic.GetUser().UserInfo.UserId; } catch { }

            string sql = @" insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections]
                                (SiteId, ConnectionName, Active, DateTimeCreated)
                            values (@SiteId, @ConnectionName, @Active, @DateTimeCreated)";

            if (userId.HasValue)
            {
                sql = @"    insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections]
                                (SiteId, ConnectionName, Active, DateTimeCreated, CreatedByUserId)
                            values (@SiteId, @ConnectionName, @Active, @DateTimeCreated, @CreatedByUserId)";
            }
            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
            {
                command.AddParameter("@SiteId", connectionInfo.SiteId);
                command.AddParameter("@ConnectionName", connectionInfo.ConnectionName);
                command.AddParameter("@Active", false);
                command.AddParameter("@DateTimeCreated", DateTime.Now);
                if (userId.HasValue)
                {
                    command.AddParameter("@CreatedByUserId", userId);
                }
                command.Command.ExecuteNonQuery();
            }
            sql = "select [ConnectionId] from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections] where [SiteId] = @SiteId and [ConnectionName] = @ConnectionName";
            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
            {
                command.AddParameter("@SiteId", connectionInfo.SiteId);
                command.AddParameter("@ConnectionName", connectionInfo.ConnectionName);
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    if (reader.Read())
                    {
                        connectionInfo.ConnectionId = reader.Get("ConnectionId", -1);
                    }
                }
            }
            if (connectionInfo.ConnectionId < 0)
            {
                throw new Exception("Error while attempting to save new database connection.  Please check connections list and try again.");
            }

            ASPdb_Connection__SaveUpdate(connectionInfo);
        }
Exemple #15
0
        //----------------------------------------------------------------------------------------------------
        public static List <BaseTableInfo> ASPdb_Table__GetRawList_NoCache(ASPdb_Connection aspdb_Connection)
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            var rtn = new List <BaseTableInfo>();

            if (aspdb_Connection.E_ConnectionType == ASPdb_Connection.Enum_ConnectionTypes.SQLServer ||
                aspdb_Connection.E_ConnectionType == ASPdb_Connection.Enum_ConnectionTypes.SQLServerAzure)
            {
                string sql = @"
                    select 
	                    t2.schema_id,
	                    t2.[name] [SchemaName], 
	                    t1.[name] [TableName], 
	                    t1.object_id,
	                    t1.create_date, 
	                    t1.modify_date
                    from sys.tables t1
                    inner join 
	                    sys.schemas t2 on t1.schema_id = t2.schema_id
                    order by t2.[name], t1.[name]
                    ";

                //sql = sql.Replace("[", "`").Replace("]", "`");

                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(aspdb_Connection.GetConnectionString(), sql))
                {
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                    {
                        DateTime?nullDate = null;
                        while (reader.Read())
                        {
                            var item = new BaseTableInfo();
                            item.SchemaId   = reader.Get("schema_id", "");
                            item.SchemaName = reader.Get("SchemaName", "");
                            item.TableName  = reader.Get("TableName", "");
                            item.ObjectId   = reader.Get("object_id", "");
                            item.CreateDate = reader.Get("create_date", nullDate);
                            item.ModifyDate = reader.Get("modify_date", nullDate);
                            rtn.Add(item);
                        }
                    }
                }
            }
            return(rtn);
        }
Exemple #16
0
        //----------------------------------------------------------------------------------------------------
        public static DefaultConstraint DefaultConstraint__Get(Column column)
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            var    dict = new Dictionary <string, DefaultConstraint>();
            string sql  = SQLServer_SQLBuilders.BuildSql__DefaultConstraint_Select(column);

            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(column.ConnectionId, sql))
            {
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    while (reader.Read())
                    {
                        string columnName   = reader.Get("ColumnName", "");
                        string columnName_L = columnName.ToLower();

                        var defaultConstraint = new DefaultConstraint()
                        {
                            ConnectionId          = column.ConnectionId,
                            Schema                = column.Schema,
                            TableName             = column.TableName,
                            ColumnName            = reader.Get("ColumnName", ""),
                            DefaultConstraintName = reader.Get("DefaultConstraintName", ""),
                            DefaultDefinition     = reader.Get("DefaultDefinition", "")
                        };

                        if (!dict.ContainsKey(columnName_L))
                        {
                            dict.Add(columnName_L, defaultConstraint);
                        }
                    }
                }
            }
            if (dict.ContainsKey(column.ColumnName.ToLower()))
            {
                return(dict[column.ColumnName.ToLower()]);
            }
            else
            {
                return(null);
            }
        }
Exemple #17
0
        //----------------------------------------------------------------------------------------------------
        public static void SavePassword(int userId, string oldPassword, string newPassword)
        {
            try
            {
                string sql = String.Format("select * from [{0}].[ASPdb_Users] where [UserId] = @UserId", Config.SystemProperties.AppSchema);
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.AddParameter("@UserId", userId);
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                    {
                        reader.Read();
                        if (!BCrypt.Net.BCrypt.Verify(oldPassword, reader.Get("Password", "")))
                        {
                            throw new Exception("");
                        }
                    }
                }
            }
            catch { throw new Exception("Incorrect Old Password"); }

            SavePassword(userId, newPassword);
        }
Exemple #18
0
        //----------------------------------------------------------------------------------------------------
        private static void ASPdb_Connection__CheckForDuplicateConnectionName(ASPdb_Connection connectionInfo)
        {
            bool   isADuplicate = false;
            string sql          = "select [ConnectionId], [ConnectionName] from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections] where SiteId = @SiteId and ConnectionName = @ConnectionName";

            try
            {
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.AddParameter("@SiteId", connectionInfo.SiteId);
                    command.AddParameter("@ConnectionName", connectionInfo.ConnectionName);
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                    {
                        if (reader.Read())
                        {
                            int    connectionId   = reader.Get("ConnectionId", -1);
                            string connectionName = reader.Get("ConnectionName", "").Trim().ToLower();
                            if (connectionInfo.ConnectionName.Trim().ToLower() == connectionName)
                            {
                                if (connectionInfo.ConnectionId != connectionId)
                                {
                                    isADuplicate = true;
                                }
                                else if (connectionId <= 0)
                                {
                                    isADuplicate = true;
                                }
                            }
                        }
                    }
                }
            }
            catch { }
            if (isADuplicate)
            {
                throw new Exception(String.Format("The Database Connection Name \"{0}\" already exists." +
                                                  "\n\nPlease choose a different name.", connectionInfo.ConnectionName.Trim()));
            }
        }
Exemple #19
0
        //----------------------------------------------------------------------------------------------------
        /// <summary>
        /// Key == lowercase ConstraintName.
        /// Value == ConstraintName.
        /// </summary>
        public static Dictionary <string, string> Constraint__GetAll_InConnection(int connectionId)
        {
            AjaxService.ASPdatabaseService.GetSetVal();
            var    rtn = new Dictionary <string, string>();
            string sql = SQLServer_SQLBuilders.BuildSql__Constraint_SelectAll();

            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionId, sql))
            {
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    while (reader.Read())
                    {
                        string constraintName   = reader.Get("ConstraintName", "");
                        string constraintName_L = constraintName.ToLower();
                        if (!rtn.ContainsKey(constraintName_L))
                        {
                            rtn.Add(constraintName_L, constraintName);
                        }
                    }
                }
            }
            return(rtn);
        }
Exemple #20
0
        //----------------------------------------------------------------------------------------------------
        public static UsersMenuInfo GetMenuInfo()
        {
            if (!ASPdatabaseNET.Subscription.SubscriptionAppState.ValidateActiveSubscribers())
            {
                throw new Exception("Validation Error");
            }

            var rtn = new UsersMenuInfo();

            rtn.UserSubscriptions_Total = ASPdatabaseNET.Subscription.SubscriptionAppState.GetSubscribersCount();
            //rtn.UserSubscriptions_Active = 7;
            rtn.UserSubscriptions_Message = "User Subscriptions Message";

            var users_Active    = new List <UsersMenuItem>();
            var users_Inactive  = new List <UsersMenuItem>();
            var groups_Active   = new List <UsersMenuItem>();
            var groups_Inactive = new List <UsersMenuItem>();

            string sql = String.Format(@"
                select [UserId], [Username], [FirstName], [LastName], [Active] 
                from [{0}].[ASPdb_Users] 
                order by [Active], [LastName], [FirstName], [Username]", Config.SystemProperties.AppSchema);

            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
            {
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    while (reader.Read())
                    {
                        var item = new UsersMenuItem()
                        {
                            Id = reader.Get("UserId", -1), Active = reader.Get("Active", false), MenuType = UsersMenuItem.MenuTypes.User
                        };
                        string firstName = reader.Get("FirstName", "");
                        string lastName  = reader.Get("LastName", "");
                        string username  = reader.Get("Username", "?");

                        if (firstName.Length > 0 && lastName.Length > 0)
                        {
                            item.DisplayName = String.Format("<b>{0}, {1}</b> <br />({2})", lastName, firstName, username);
                        }
                        else //if(firstName.Length > 0 || lastName.Length > 0)
                        {
                            item.DisplayName = String.Format("<b>{0} {1}</b> <br />({2})", lastName, firstName, username);
                        }
                        //else
                        //    item.DisplayName = String.Format("({2})", lastName, firstName, username);

                        if (item.Active)
                        {
                            users_Active.Add(item);
                        }
                        else
                        {
                            users_Inactive.Add(item);
                        }
                    }
                }
            }
            sql = "select [GroupId], [GroupName], [Active] from [" + Config.SystemProperties.AppSchema + "].[ASPdb_UserGroups] order by [Active], [GroupName]";
            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
            {
                using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                {
                    while (reader.Read())
                    {
                        var item = new UsersMenuItem()
                        {
                            Id = reader.Get("GroupId", -1), DisplayName = reader.Get("GroupName", "?"), Active = reader.Get("Active", false), MenuType = UsersMenuItem.MenuTypes.Group
                        };
                        if (item.Active)
                        {
                            groups_Active.Add(item);
                        }
                        else
                        {
                            groups_Inactive.Add(item);
                        }
                    }
                }
            }
            rtn.Users_Active    = users_Active.ToArray();
            rtn.Users_Inactive  = users_Inactive.ToArray();
            rtn.Groups_Active   = groups_Active.ToArray();
            rtn.Groups_Inactive = groups_Inactive.ToArray();
            return(rtn);
        }
Exemple #21
0
        //----------------------------------------------------------------------------------------------------
        public static GroupInfo GetGroup(int groupId)
        {
            GroupInfo rtn = null;

            try
            {
                string sql = "";
                if (groupId >= 0)
                {
                    sql = "select * from [" + Config.SystemProperties.AppSchema + "].[ASPdb_UserGroups] where [GroupId] = @GroupId";
                    using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                    {
                        command.AddParameter("@GroupId", groupId);
                        using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        {
                            if (reader.Read())
                            {
                                rtn = new GroupInfo()
                                {
                                    GroupId         = reader.Get("GroupId", -1),
                                    GroupName       = reader.Get("GroupName", ""),
                                    Active          = reader.Get("Active", false),
                                    TimeCreated_Str = reader.Get("TimeCreated", ""),
                                    Permissions     = new Permission[0]
                                }
                            }
                            ;
                        }
                    }
                }
                else
                {
                    rtn = new GroupInfo()
                    {
                        GroupId = -1, Active = true
                    }
                };


                if (rtn != null)
                {
                    if (!String.IsNullOrEmpty(rtn.TimeCreated_Str))
                    {
                        rtn.TimeCreated_Str = DateTime.Parse(rtn.TimeCreated_Str).ToString("M/dd/yy h:mm tt");
                    }

                    var allPermissions = new Dictionary <string, Permission>();
                    sql = @"select 
                            C.ConnectionId, C.ConnectionName, T.TableId, T.[Schema], T.TableName
                            from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Connections] C inner join [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Tables] T on C.ConnectionId = T.ConnectionId
                            where C.Active = 1 and T.Hide = 0
                            order by C.ConnectionName, T.[Schema], T.TableName";
                    using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                    {
                        using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        {
                            while (reader.Read())
                            {
                                var permission = new Permission()
                                {
                                    GroupId        = groupId,
                                    ConnectionId   = reader.Get("ConnectionId", -1),
                                    ConnectionName = reader.Get("ConnectionName", ""),
                                    TableId        = reader.Get("TableId", -1),
                                    Schema         = reader.Get("Schema", ""),
                                    TableName      = reader.Get("TableName", "")
                                };
                                string connection_Key = "C_" + permission.ConnectionId;
                                string schema_Key     = "S_" + permission.ConnectionId + "_[" + permission.Schema + "]";
                                string table_Key      = "T_" + permission.ConnectionId + "_[" + permission.Schema + "]_[" + permission.TableId + "]";

                                if (!allPermissions.ContainsKey(connection_Key))
                                {
                                    allPermissions.Add(connection_Key, new Permission(Permission.PermissionTypes.Connection, permission.PermissionId, groupId)
                                    {
                                        ConnectionId   = permission.ConnectionId,
                                        ConnectionName = permission.ConnectionName,
                                        Schema         = "",
                                        TableId        = -1,
                                        TableName      = ""
                                    });
                                }
                                if (!allPermissions.ContainsKey(schema_Key))
                                {
                                    allPermissions.Add(schema_Key, new Permission(Permission.PermissionTypes.Schema, permission.PermissionId, groupId)
                                    {
                                        ConnectionId   = permission.ConnectionId,
                                        ConnectionName = permission.ConnectionName,
                                        Schema         = permission.Schema,
                                        TableId        = -1,
                                        TableName      = ""
                                    });
                                }
                                if (!allPermissions.ContainsKey(table_Key))
                                {
                                    allPermissions.Add(table_Key, new Permission(Permission.PermissionTypes.Table, permission.PermissionId, groupId)
                                    {
                                        ConnectionId   = permission.ConnectionId,
                                        ConnectionName = permission.ConnectionName,
                                        Schema         = permission.Schema,
                                        TableId        = permission.TableId,
                                        TableName      = permission.TableName
                                    });
                                }
                            }
                        }
                    }

                    sql = @"
                        select * from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions] 
                        where [GroupId] = @GroupId 
                        order by [ConnectionId], [Schema], [TableId]";
                    using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                    {
                        command.AddParameter("@GroupId", groupId);
                        using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        {
                            while (reader.Read())
                            {
                                var permission = new Permission()
                                {
                                    GroupId        = groupId,
                                    PermissionId   = reader.Get("PermissionId", -1),
                                    PermissionType = Permission.PermissionTypes.NotSet,
                                    ConnectionId   = reader.Get("ConnectionId", -1),
                                    Schema         = reader.Get("Schema", ""),
                                    TableId        = reader.Get("TableId", -1),
                                    View           = reader.Get("View", false),
                                    Edit           = reader.Get("Edit", false),
                                    Insert         = reader.Get("Insert", false),
                                    Delete         = reader.Get("Delete", false),
                                };
                                permission.Set_PermissionType(reader.Get("PermissionType", "N"));
                                string key = permission.Get_UniqueKey();
                                if (key != null && allPermissions.ContainsKey(key))
                                {
                                    allPermissions[key].PermissionId = permission.PermissionId;
                                    allPermissions[key].View         = permission.View;
                                    allPermissions[key].Edit         = permission.Edit;
                                    allPermissions[key].Insert       = permission.Insert;
                                    allPermissions[key].Delete       = permission.Delete;
                                }
                            }
                        }
                    }
                    rtn.Permissions = (from r in allPermissions.Values where r.PermissionType == Permission.PermissionTypes.Connection orderby r.ConnectionName select r).ToArray();
                    foreach (var item_Connection in rtn.Permissions)
                    {
                        item_Connection.SubPermissions = (from r in allPermissions.Values
                                                          where r.PermissionType == Permission.PermissionTypes.Schema &&
                                                          r.ConnectionId == item_Connection.ConnectionId
                                                          select r).ToArray();
                        foreach (var item_Schema in item_Connection.SubPermissions)
                        {
                            item_Schema.SubPermissions = (from r in allPermissions.Values
                                                          where r.PermissionType == Permission.PermissionTypes.Table &&
                                                          r.ConnectionId == item_Connection.ConnectionId &&
                                                          r.Schema == item_Schema.Schema
                                                          select r).ToArray();
                        }
                    }
                }
            }
            catch (Exception exc) { ASPdb.Framework.Debug.RecordException(exc); }
            if (rtn == null)
            {
                throw new Exception("GroupId not found.");
            }
            return(rtn);
        }
Exemple #22
0
        //----------------------------------------------------------------------------------------------------
        public static UserInfo GetUser(int userId)
        {
            UserInfo rtn = null;

            try
            {
                string sql = "";
                if (userId >= 0)
                {
                    sql = "select * from [" + Config.SystemProperties.AppSchema + "].[ASPdb_Users] where [UserId] = @UserId";
                    using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                    {
                        command.AddParameter("@UserId", userId);
                        using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        {
                            if (reader.Read())
                            {
                                rtn = new UserInfo()
                                {
                                    UserId               = reader.Get("UserId", -1),
                                    Username             = reader.Get("Username", ""),
                                    FirstName            = reader.Get("FirstName", ""),
                                    LastName             = reader.Get("LastName", ""),
                                    Email                = reader.Get("Email", ""),
                                    Active               = reader.Get("Active", false),
                                    TimeCreated_Str      = reader.Get("TimeCreated", ""),
                                    LastLoginTime_Str    = reader.Get("LastLoginTime", ""),
                                    RequirePasswordReset = reader.Get("RequirePasswordReset", false),
                                    IsAdmin              = reader.Get("IsAdmin", false),
                                    UserGroups           = new UserToGroup_Assignment[0]
                                }
                            }
                            ;
                        }
                    }
                }
                else
                {
                    rtn = new UserInfo()
                    {
                        UserId = -1, Active = true
                    }
                };

                if (rtn != null)
                {
                    if (!String.IsNullOrEmpty(rtn.TimeCreated_Str))
                    {
                        rtn.TimeCreated_Str = DateTime.Parse(rtn.TimeCreated_Str).ToString("M/dd/yy h:mm tt");
                    }
                    if (!String.IsNullOrEmpty(rtn.LastLoginTime_Str))
                    {
                        rtn.LastLoginTime_Str = DateTime.Parse(rtn.LastLoginTime_Str).ToString("M/dd/yy h:mm tt");
                    }

                    var userGroups = new Dictionary <int, UserToGroup_Assignment>();
                    sql = "select * from [" + Config.SystemProperties.AppSchema + "].[ASPdb_UserGroups] where [Active] = 1 order by [GroupName]";
                    using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                    {
                        using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        {
                            while (reader.Read())
                            {
                                int groupId = reader.Get("GroupId", -1);
                                if (!userGroups.ContainsKey(groupId))
                                {
                                    userGroups.Add(groupId, new UserToGroup_Assignment()
                                    {
                                        GroupId   = groupId,
                                        GroupName = reader.Get("GroupName", ""),
                                        IsMember  = false,
                                        UserId    = rtn.UserId
                                    });
                                }
                            }
                        }
                    }

                    sql = "select * from [" + Config.SystemProperties.AppSchema + "].[ASPdb_UsersToGroups] where [UserId] = @UserId";
                    using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                    {
                        command.AddParameter("@UserId", userId);
                        using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        {
                            while (reader.Read())
                            {
                                int groupId = reader.Get("GroupId", -1);
                                if (groupId >= 0 && userGroups.ContainsKey(groupId))
                                {
                                    userGroups[groupId].IsMember = true;
                                }
                            }
                        }
                    }
                    rtn.UserGroups = userGroups.Values.ToArray();
                }
            }
            catch { }
            if (rtn == null)
            {
                throw new Exception("UserId not found.");
            }
            return(rtn);
        }
Exemple #23
0
        //----------------------------------------------------------------------------------------------------
        public static int SaveUser(UserInfo userInfo)
        {
            // CONFIRM Username is not already taken ! ! !

            // CONFIRM At least one user is still an Admin ! ! !

            string sql      = "";
            bool   doInsert = (userInfo.UserId < 0);

            if (doInsert)
            {
                sql = @"insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users]
                        ([Username], [FirstName], [LastName], [Email], [Active], [RequirePasswordReset], [IsAdmin], [TimeCreated], [Password])
                        values
                        (@Username, @FirstName, @LastName, @Email, @Active, @RequirePasswordReset, @IsAdmin, @TimeCreated, ''); 
                        select scope_identity();
                        ";
            }
            else
            {
                sql = @"update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Users]
                        set [Username]             = @Username,
                            [FirstName]            = @FirstName,
                            [LastName]             = @LastName,
                            [Email]                = @Email,
                            [Active]               = @Active,
                            [RequirePasswordReset] = @RequirePasswordReset,
                            [IsAdmin]              = @IsAdmin
                        where [UserId] = @UserId; ";
            }
            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
            {
                command.AddParameter("@Username", userInfo.Username);
                command.AddParameter("@FirstName", userInfo.FirstName);
                command.AddParameter("@LastName", userInfo.LastName);
                command.AddParameter("@Email", userInfo.Email);
                command.AddParameter("@Active", userInfo.Active);
                command.AddParameter("@RequirePasswordReset", userInfo.RequirePasswordReset);
                command.AddParameter("@IsAdmin", userInfo.IsAdmin);
                if (doInsert)
                {
                    command.AddParameter("@TimeCreated", DateTime.Now);
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                    {
                        if (reader.Read())
                        {
                            try { userInfo.UserId = Int32.Parse(reader.Reader[0].ToString()); }
                            catch { }
                        }
                    }
                }
                else
                {
                    command.AddParameter("@UserId", userInfo.UserId);
                    command.ExecuteNonQuery();
                }
            }

            sql = "";
            if (!doInsert)
            {
                sql = @" delete from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_UsersToGroups] where [UserId] = @UserId; ";
            }
            if (userInfo.UserGroups != null && userInfo.UserGroups.Length > 0)
            {
                var groupIds = (from r in userInfo.UserGroups where r.IsMember == true orderby r.GroupId select r.GroupId).ToList();
                foreach (int groupId in groupIds)
                {
                    sql += String.Format(@" insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_UsersToGroups] ([UserId], [GroupId]) values (@UserId, {0});
                        ", groupId);
                }
            }
            if (sql.Length > 0)
            {
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.AddParameter("@UserId", userInfo.UserId);
                    command.ExecuteNonQuery();
                }
            }
            return(userInfo.UserId);
        }
        //----------------------------------------------------------------------------------------------------
        public static void Save(MyAccountInfo myAccountInfo)
        {
            var user = ASPdatabaseNET.Users.UserSessionLogic.GetUserSession_ForClient().UserInfo;

            if (user.UserId != myAccountInfo.UserId)
            {
                throw new Exception("Invalid user info.");
            }

            if (String.IsNullOrEmpty(myAccountInfo.FirstName))
            {
                throw new Exception("Please enter your first name.");
            }
            if (String.IsNullOrEmpty(myAccountInfo.LastName))
            {
                throw new Exception("Please enter your last name.");
            }
            if (String.IsNullOrEmpty(myAccountInfo.Email) && myAccountInfo.Email.Length < 5 && !myAccountInfo.Email.Contains("@"))
            {
                throw new Exception("Please enter your email address.");
            }

            try
            {
                string sql = String.Format(@"
                    update [{0}].[ASPdb_Users]
                    set  [FirstName] = @FirstName
                        ,[LastName] = @LastName
                        ,[Email] = @Email
                    where [UserId] = @UserId
                ", Config.SystemProperties.AppSchema);

                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.AddParameter("@FirstName", myAccountInfo.FirstName);
                    command.AddParameter("@LastName", myAccountInfo.LastName);
                    command.AddParameter("@Email", myAccountInfo.Email);
                    command.AddParameter("@UserId", myAccountInfo.UserId);

                    command.ExecuteNonQuery();
                }

                sql = String.Format("select * from [{0}].[ASPdb_Users] where [UserId] = @UserId", Config.SystemProperties.AppSchema);
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.AddParameter("@UserId", myAccountInfo.UserId);
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                    {
                        if (reader.Read())
                        {
                            user.FirstName = reader.Get("FirstName", "");
                            user.LastName  = reader.Get("LastName", "");
                            user.Email     = reader.Get("Email", "");
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                ASPdb.Framework.Debug.RecordException(exc);
                throw new Exception("Error while trying to save account info.");
            }
        }
Exemple #25
0
        //----------------------------------------------------------------------------------------------------
        public static InstallInfo GetInstallState()
        {
            var rtn = new InstallInfo()
            {
                InstallState = InstallInfo.InstallStates.NotSet
            };

            rtn.AboutPageInfo = UI.PageParts.About.Backend.AboutPageLogic.Get();

            string exc_Message = "";

            try { rtn.ConnectionString = ConfigurationManager.ConnectionStrings["ASPdb_AppData"].ConnectionString; }
            catch (Exception exc) { ASPdb.Framework.Debug.RecordException(exc); }
            if (String.IsNullOrEmpty(rtn.ConnectionString))
            {
                rtn.InstallState = InstallInfo.InstallStates.NoConnectionString;
            }
            else
            {
                try
                {
                    rtn.InstallState = InstallInfo.InstallStates.Installed;
                    var tableNames = new string[]
                    {
                        "ASPdb_Connections",
                        "ASPdb_Permissions",
                        "ASPdb_Tables",
                        "ASPdb_UserGroups",
                        "ASPdb_Users",
                        "ASPdb_UsersToGroups",
                        "ASPdb_Values"
                    };
                    string sql = "";
                    foreach (string table in tableNames)
                    {
                        sql = String.Format("select top 1 * from [{0}].[{1}];", Config.SystemProperties.AppSchema, table);
                        using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(rtn.ConnectionString, sql))
                        {
                            using (DbReaderWrapper reader = command.ExecuteReaderWrapper()) { if (reader.Read())
                                                                                              {
                                                                                              }
                            }
                        }
                    }
                    int usersCount = 0;
                    sql = String.Format("select count(*) as [Count1] from [{0}].[ASPdb_Users] where Len([Password]) > 0", Config.SystemProperties.AppSchema);
                    using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(rtn.ConnectionString, sql))
                    {
                        using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        {
                            if (reader.Read())
                            {
                                usersCount = reader.Get("Count1", 0);
                            }
                        }
                    }
                    if (usersCount < 1)
                    {
                        throw new Exception("Users table is empty.");
                    }
                }
                catch (Exception exc)
                {
                    ASPdb.Framework.Debug.RecordException(exc);
                    string exc_L = exc.Message.ToLower();
                    if (exc_L.Contains("cannot open database") || exc_L.Contains("login failed"))
                    {
                        rtn.InstallState = InstallInfo.InstallStates.CannotConnectToDB;
                    }
                    else
                    {
                        rtn.InstallState = InstallInfo.InstallStates.DatabaseNotReady;
                    }

                    exc_Message = exc.Message; // +"<br />" + exc.StackTrace;
                    //throw exc;
                }
            }

            var cache = Memory.AppCache.Get();

            if (cache.AnyData.ContainsKey(AppKey_IsInstalled))
            {
                cache.AnyData.Remove(AppKey_IsInstalled);
            }
            if (rtn.InstallState == InstallInfo.InstallStates.Installed)
            {
                cache.AnyData.Add(AppKey_IsInstalled, "true");
            }

            rtn.ResponseMsg = rtn.InstallState.ToString();
            switch (rtn.InstallState)
            {
            case InstallInfo.InstallStates.NoConnectionString:
                rtn.ResponseMsg = "Connection string not found.";
                break;

            case InstallInfo.InstallStates.CannotConnectToDB:
                rtn.ResponseMsg = "Could not connect to database. <hr />" + exc_Message;
                break;

            case InstallInfo.InstallStates.DatabaseNotReady:
                break;

            case InstallInfo.InstallStates.Installed:
                break;
            }

            ASPdb.Framework.Debug.WriteLine("002a..." + rtn.InstallState.ToString());

            return(rtn);
        }
Exemple #26
0
        //----------------------------------------------------------------------------------------------------
        private static void CheckForSQLStructureUpdates()
        {
            string installedVersion = Config.ASPdb_Values.GetFirstValue("InstalledVersion", "");
            string lastInstallTime  = Config.ASPdb_Values.GetFirstValue("LastInstallTime", "");

            if (installedVersion == Config.SystemProperties.Version)
            {
                return;
            }

            //--------------------
            bool add__ASPdb_Tables__AppProperties = false;
            bool add__ASPdb_Views   = false;
            bool add__ASPdb_History = false;

            //--------------------
            try
            {
                string sql = String.Format("select top 1 [{2}] from [{0}].[{1}]", Config.SystemProperties.AppSchema, "ASPdb_Tables", "AppProperties");
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        reader.Read();
            }
            catch { add__ASPdb_Tables__AppProperties = true; }
            try
            {
                string sql = String.Format("select top 1 * from [{0}].[{1}]", Config.SystemProperties.AppSchema, "ASPdb_Views");
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        reader.Read();
            }
            catch { add__ASPdb_Views = true; }
            try
            {
                string sql = String.Format("select top 1 * from [{0}].[{1}]", Config.SystemProperties.AppSchema, "ASPdb_History");
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        reader.Read();
            }
            catch { add__ASPdb_History = true; }



            //--------------------
            if (add__ASPdb_Tables__AppProperties)
            {
                string sql = String.Format(@"ALTER TABLE [{0}].[{1}] ADD [{2}] {3}", Config.SystemProperties.AppSchema, "ASPdb_Tables", "AppProperties", "ntext");
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.ExecuteNonQuery();
                }
            }
            if (add__ASPdb_Views)
            {
                string sql = String.Format(@"
                CREATE TABLE [{0}].[ASPdb_Views](
	                [ViewId] [int] IDENTITY(1,1) NOT NULL,
	                [ConnectionId] [int] NOT NULL,
	                [ViewName] [nvarchar](250) NOT NULL,
	                [Schema] [nvarchar](100) NOT NULL,
	                [Hide] [bit] NOT NULL,
	                [UserFilter] [nvarchar](250) NOT NULL,
                 CONSTRAINT [PK_ASPdb_Views__001] PRIMARY KEY CLUSTERED 
                (
	                [ViewId] ASC
                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
                ) ON [PRIMARY];
                ALTER TABLE [ASPdb].[ASPdb_Views] ADD  DEFAULT (N'dbo') FOR [Schema];
                ALTER TABLE [ASPdb].[ASPdb_Views] ADD  DEFAULT ((0)) FOR [Hide];
                ALTER TABLE [ASPdb].[ASPdb_Views] ADD  DEFAULT (N'') FOR [UserFilter];
                ALTER TABLE [ASPdb].[ASPdb_Views]  WITH CHECK ADD  CONSTRAINT [FK_ASPdb_Views_ASPdb_Connections_001] FOREIGN KEY([ConnectionId])
                REFERENCES [ASPdb].[ASPdb_Connections] ([ConnectionId]);
                ALTER TABLE [ASPdb].[ASPdb_Views] CHECK CONSTRAINT [FK_ASPdb_Views_ASPdb_Connections_001];
                ", Config.SystemProperties.AppSchema);

                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.ExecuteNonQuery();
                }
            }
            if (add__ASPdb_History)
            {
                string sql = String.Format(@"
                CREATE TABLE [{0}].[ASPdb_History](
	                [HistoryId] [int] IDENTITY(1,1) NOT NULL,
	                [TableId] [int] NOT NULL,
	                [KeyValue] [nvarchar](50) NOT NULL,
	                [KeyValueIsTruncated] [bit] NOT NULL,
                    [Revision] [int] NOT NULL,
	                [HistoryType] [nvarchar](10) NOT NULL,
	                [IsPartial] [bit] NOT NULL,
	                [TimeSaved] [datetime] NOT NULL,
	                [ByUserId] [int] NOT NULL,
	                [ByUsername] [nvarchar](50) NOT NULL,
	                [HistoryJSON] [ntext] NOT NULL,
                 CONSTRAINT [PK_ASPdb_ASPdb_History__6] PRIMARY KEY CLUSTERED 
                (
	                [HistoryId] ASC
                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
                ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

                CREATE NONCLUSTERED INDEX [IX_ASPdb_ASPdb_History_0001] ON [ASPdb].[ASPdb_History]
                (
	                [TableId] ASC,
	                [KeyValue] ASC
                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
                ", Config.SystemProperties.AppSchema);

                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.ExecuteNonQuery();
                }
            }


            Config.ASPdb_Values.Set("InstalledVersion", Config.SystemProperties.Version);
            Config.ASPdb_Values.Set("LastInstallTime", DateTime.Now.ToString());
        }
Exemple #27
0
        //----------------------------------------------------------------------------------------------------
        public static int SaveGroup(GroupInfo groupInfo)
        {
            string sql      = "";
            bool   doInsert = (groupInfo.GroupId < 0);

            if (doInsert)
            {
                sql = @"insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_UserGroups]
                        ([GroupName], [Active], [TimeCreated])
                        values
                        (@GroupName, @Active, @TimeCreated);
                        select scope_identity(); ";
            }
            else
            {
                sql = @"update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_UserGroups]
                        set [GroupName] = @GroupName,
                            [Active]    = @Active
                        where [GroupId] = @GroupId; ";
            }
            using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
            {
                command.AddParameter("@GroupName", groupInfo.GroupName);
                command.AddParameter("@Active", groupInfo.Active);
                if (doInsert)
                {
                    command.AddParameter("@TimeCreated", DateTime.Now);
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                    {
                        if (reader.Read())
                        {
                            try { groupInfo.GroupId = Int32.Parse(reader.Reader[0].ToString()); }
                            catch { }
                        }
                    }
                }
                else
                {
                    command.AddParameter("@GroupId", groupInfo.GroupId);
                    command.ExecuteNonQuery();
                }
            }


            var all_CurrentRecs   = new Dictionary <string, Permission>();
            var current_GroupInfo = GetGroup(groupInfo.GroupId);

            if (current_GroupInfo.Permissions != null)
            {
                foreach (var item1 in current_GroupInfo.Permissions) // Connections
                {
                    string key1 = item1.Get_UniqueKey();
                    if (key1 != null && !all_CurrentRecs.ContainsKey(key1))
                    {
                        all_CurrentRecs.Add(key1, item1);
                    }
                    if (item1.SubPermissions != null)
                    {
                        foreach (var item2 in item1.SubPermissions) // Schemas
                        {
                            string key2 = item2.Get_UniqueKey();
                            if (key2 != null && !all_CurrentRecs.ContainsKey(key2))
                            {
                                all_CurrentRecs.Add(key2, item2);
                            }
                            if (item2.SubPermissions != null)
                            {
                                foreach (var item3 in item2.SubPermissions) // Tables
                                {
                                    string key3 = item3.Get_UniqueKey();
                                    if (key3 != null && !all_CurrentRecs.ContainsKey(key3))
                                    {
                                        all_CurrentRecs.Add(key3, item3);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            var tmpFiltered1 = new Dictionary <string, Permission>();

            foreach (string key in all_CurrentRecs.Keys)
            {
                var item = all_CurrentRecs[key];
                if (item.View || item.Edit || item.Insert || item.Delete)
                {
                    tmpFiltered1.Add(key, item);
                }
            }
            all_CurrentRecs = tmpFiltered1;

            var all_NewRecs = new Dictionary <string, Permission>();

            if (groupInfo.Permissions != null)
            {
                foreach (var item1 in groupInfo.Permissions) // Connections
                {
                    string key1 = item1.Get_UniqueKey();
                    if (key1 != null && !all_NewRecs.ContainsKey(key1))
                    {
                        all_NewRecs.Add(key1, item1);
                    }
                    if (item1.SubPermissions != null)
                    {
                        foreach (var item2 in item1.SubPermissions) // Schemas
                        {
                            string key2 = item2.Get_UniqueKey();
                            if (key2 != null && !all_NewRecs.ContainsKey(key2))
                            {
                                all_NewRecs.Add(key2, item2);
                            }
                            if (item2.SubPermissions != null)
                            {
                                foreach (var item3 in item2.SubPermissions) // Tables
                                {
                                    string key3 = item3.Get_UniqueKey();
                                    if (key3 != null && !all_NewRecs.ContainsKey(key3))
                                    {
                                        all_NewRecs.Add(key3, item3);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            var tmpFiltered2 = new Dictionary <string, Permission>();

            foreach (string key in all_NewRecs.Keys)
            {
                var item = all_NewRecs[key];
                if (item.View || item.Edit || item.Insert || item.Delete)
                {
                    tmpFiltered2.Add(key, item);
                }
            }
            all_NewRecs = tmpFiltered2;


            var recs_ToDelete = new List <Permission>();
            var recs_ToInsert = new List <Permission>();
            var recs_ToUpdate = new List <Permission>();

            foreach (string key in all_CurrentRecs.Keys)
            {
                if (!all_NewRecs.ContainsKey(key))
                {
                    recs_ToDelete.Add(all_CurrentRecs[key]);
                }
            }

            foreach (string key in all_NewRecs.Keys)
            {
                if (!all_CurrentRecs.ContainsKey(key))
                {
                    all_NewRecs[key].GroupId = groupInfo.GroupId;
                    recs_ToInsert.Add(all_NewRecs[key]);
                }
                else // check for update
                {
                    var currentRec = all_CurrentRecs[key];
                    var newRec     = all_NewRecs[key];
                    if (!currentRec.IsEqual(newRec))
                    {
                        recs_ToUpdate.Add(newRec);
                    }
                }
            }

            foreach (var item in recs_ToDelete)
            {
                sql = "delete from [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions] where [PermissionId] = @PermissionId";
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(sql))
                {
                    command.AddParameter("@PermissionId", item.PermissionId);
                    command.ExecuteNonQuery();
                }
            }

            foreach (var item in recs_ToInsert)
            {
                sql = "";
                switch (item.PermissionType)
                {
                case Permission.PermissionTypes.Connection:
                    sql = @"insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions] 
                        ([GroupId], [PermissionType], [ConnectionId], [View], [Edit], [Insert], [Delete]) values (@GroupId, @PermissionType, @ConnectionId, @View, @Edit, @Insert, @Delete) ";
                    break;

                case Permission.PermissionTypes.Schema:
                    sql = @"insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions] 
                        ([GroupId], [PermissionType], [ConnectionId], [Schema], [View], [Edit], [Insert], [Delete]) values (@GroupId, @PermissionType, @ConnectionId, @Schema, @View, @Edit, @Insert, @Delete) ";
                    break;

                case Permission.PermissionTypes.Table:
                    sql = @"insert into [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions] 
                        ([GroupId], [PermissionType], [ConnectionId], [Schema], [TableId], [View], [Edit], [Insert], [Delete]) values (@GroupId, @PermissionType, @ConnectionId, @Schema, @TableId, @View, @Edit, @Insert, @Delete) ";
                    break;
                }
                SaveGroup__Helper(true, sql, item);
            }

            foreach (var item in recs_ToUpdate)
            {
                sql = "";
                switch (item.PermissionType)
                {
                case Permission.PermissionTypes.Connection:
                    sql = @"update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions]
                        set [GroupId] = @GroupId, [PermissionType] = @PermissionType, [ConnectionId] = @ConnectionId, [View] = @View, [Edit] = @Edit, [Insert] = @Insert, [Delete] = @Delete
                        where [PermissionId] = @PermissionId ";
                    break;

                case Permission.PermissionTypes.Schema:
                    sql = @"update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions]
                        set [GroupId] = @GroupId, [PermissionType] = @PermissionType, [ConnectionId] = @ConnectionId, [View] = @View, [Edit] = @Edit, [Insert] = @Insert, [Delete] = @Delete,
                            [Schema] = @Schema
                        where [PermissionId] = @PermissionId ";
                    break;

                case Permission.PermissionTypes.Table:
                    sql = @"update [" + Config.SystemProperties.AppSchema + @"].[ASPdb_Permissions]
                        set [GroupId] = @GroupId, [PermissionType] = @PermissionType, [ConnectionId] = @ConnectionId, [View] = @View, [Edit] = @Edit, [Insert] = @Insert, [Delete] = @Delete,
                            [Schema] = @Schema, 
                            [TableId] = @TableId
                        where [PermissionId] = @PermissionId ";
                    break;
                }
                SaveGroup__Helper(false, sql, item);
            }

            return(groupInfo.GroupId);
        }
Exemple #28
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);
        }
        //----------------------------------------------------------------------------------------------------
        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);
        }
Exemple #30
0
        //----------------------------------------------------------------------------------------------------
        public static string Save(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);


                Objs_History.HistoryRecord historyRecord1 = null;
                Objs_History.HistoryRecord historyRecord2 = null;
                var keyValue = recordInfo.UniqueRowObj.Values;
                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)) // Do Update
                {
                    foreach (object[] parameter in sqlParameters)
                    {
                        command.AddParameter(parameter[0].ToString(), parameter[1]);
                    }
                    command.ExecuteNonQuery();
                }

                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);
            }
            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} )
                OUTPUT inserted.*
                values
                ( {3} )
                ",
                                    tableStructure.Schema, tableStructure.TableName,
                                    columnsSQL,
                                    valuesSQL);
                var keyValue = new string[0];
                using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) // Do Insert
                {
                    foreach (object[] parameter in sqlParameters)
                    {
                        command.AddParameter(parameter[0].ToString(), parameter[1]);
                    }
                    //command.ExecuteNonQuery();
                    using (DbReaderWrapper reader = command.ExecuteReaderWrapper())
                        if (reader.Read())
                        {
                            var keyValue_List = new List <string>();
                            foreach (var item in tableStructure.PrimaryKey.Columns)
                            {
                                keyValue_List.Add(reader.Get(item.ColumnName, ""));
                            }
                            keyValue = keyValue_List.ToArray();
                        }
                }
                if (keyValue.Length > 0)
                {
                    var historyRecord = new Objs_History.HistoryRecord()
                    {
                        HistoryId   = -1,
                        TableId     = tableStructure.TableId,
                        KeyValue    = keyValue,
                        HistoryType = PageParts.Record.Objs_History.HistoryRecord.HistoryTypes.Insert,
                        IsPartial   = false
                    };
                    HistoryLogic.Initialize_HistoryRecord_AllFields(historyRecord, "Right");
                    HistoryLogic.Save_HistoryRecord(historyRecord);
                }
            }
            //ASPdb.Framework.Debug.WriteLine(sql);


            if (doUpdate)
            {
                return(recordInfo.UniqueRowObj.To_Base64Json());
            }
            else
            {
                return("");
            }
        }