public async Task <int> AddToSystem(int systemID, int partID)
        {
            var DatabaseManager = Shared.DBconnection.GetManager();

            try
            {
                if (!SystemIDExists(systemID).Result&& !PartIDExists(partID).Result)
                {
                    var systemIDpair = new KeyValuePair <System.String, object>("systemID", systemID);
                    var partIDpair   = new KeyValuePair <System.String, object>("partID", partID);
                    var values       = new[] { systemIDpair, partIDpair };

                    MSSQLQueryBuilder QBuilder    = new MSSQLQueryBuilder();
                    Query             insertQuery = QBuilder.BuildInsertQuery("systemParts", values);
                    DatabaseManager.ExecuteNonQueryAsync(DatabaseManager.GetConnection(), insertQuery.QueryString, insertQuery.Parameters).Wait();
                }
                else if (await PartIDExists(partID))
                {
                    return(1);
                }
                else if (await SystemIDExists(systemID))
                {
                    return(2);
                }

                return(0);
            }
            finally
            {
                DatabaseManager.GetConnection().Close();
            }
        }
        public async Task <bool> AddSystem(String name, String SKU, String serialNumber, int systemTemplateID)
        {
            var DatabaseManager = Shared.DBconnection.GetManager();

            try
            {
                if (!await SerialNumberExists(serialNumber))
                {
                    return(false);
                }

                var namePair             = new KeyValuePair <String, object>("name", name);
                var SKUpair              = new KeyValuePair <String, object>("SKU", SKU);
                var serialNumberPair     = new KeyValuePair <String, object>("serialNumber", serialNumber);
                var systemTemplateIDpair = new KeyValuePair <String, object>("systemTemplateID", systemTemplateID);
                var values = new[] { namePair, SKUpair, serialNumberPair, systemTemplateIDpair };

                MSSQLQueryBuilder QBuilder    = new MSSQLQueryBuilder();
                Query             insertQuery = QBuilder.BuildInsertQuery("systems", values);
                DatabaseManager.ExecuteNonQueryAsync(DatabaseManager.GetConnection(), insertQuery.QueryString, insertQuery.Parameters).Wait();

                return(true);
            }
            finally
            {
                DatabaseManager.GetConnection().Close();
            }
        }
        public static void AddEquipment(String name, String location, String notes = null)
        {
            var DatabaseManager = Shared.DBconnection.GetManager();

            KeyValuePair <String, object>[] values;

            try
            {
                var namePair     = new KeyValuePair <String, object>("name", name);
                var locationPair = new KeyValuePair <String, object>("location", location);
                if (!(notes == null))
                {
                    var notesPair = new KeyValuePair <String, object>("notes", notes);
                    values = new[] { namePair, locationPair, notesPair };
                }
                else
                {
                    values = new[] { namePair, locationPair }
                };

                MSSQLQueryBuilder QBuilder    = new MSSQLQueryBuilder();
                Query             insertQuery = QBuilder.BuildInsertQuery("equipment", values);
                DatabaseManager.ExecuteNonQueryAsync(DatabaseManager.GetConnection(), insertQuery.QueryString, insertQuery.Parameters).Wait();
            }
            finally
            {
                DatabaseManager.GetConnection().Close();
            }
        }
        public static async Task Synchronize(int personID, int itemID, string itemType, string dateSold, string PO)
        {
            var     DatabaseManager = Shared.DBconnection.GetManager();
            Boolean isException     = false;

            try
            {
                string type;
                var    systemIDpair = new KeyValuePair <String, object>();
                var    partIDpair   = new KeyValuePair <String, object>();

                if (itemType.Equals("system"))
                {
                    type = "system";
                }
                else if (itemType.Equals("parts"))
                {
                    type = "part";
                }
                else
                {
                    isException = true;
                    throw new CustomException("Item type is not 'part' or 'system'");
                }

                var personIDpair = new KeyValuePair <String, object>("personID", personID);
                if (type.Equals("system"))
                {
                    systemIDpair = new KeyValuePair <String, object>("system", itemID);
                    partIDpair   = new KeyValuePair <String, object>("partID", "NA");
                }
                else
                {
                    systemIDpair = new KeyValuePair <String, object>("system", "NA");
                    partIDpair   = new KeyValuePair <String, object>("partID", itemID);
                }
                //TODO verify date format
                var dateSoldPair = new KeyValuePair <String, object>("dateSold", dateSold);
                var POpair       = new KeyValuePair <String, object>("PO", PO);
                var values       = new[] { personIDpair, systemIDpair, partIDpair, dateSoldPair, POpair };

                if (!isException)
                {
                    MSSQLQueryBuilder QBuilder = new MSSQLQueryBuilder();
                    Query             query    = QBuilder.BuildInsertQuery("parts", values);
                }
            }
            catch (CustomException ex)
            {
                isException = true;
                Console.WriteLine(ex.Message);
                //todo exeption handling
            }
            finally
            {
                DatabaseManager.GetConnection().Close();
            }
        }
            public static bool Exists(MSSQLConnector conn)
            {
                using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
                {
                    bl.TableExists(TableName);

                    return ((IDataConnector)conn).Execute(bl);
                }
            }
            public static bool Exists(MSSQLConnector conn)
            {
                using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
                {
                    bl.TableExists(TableName);

                    return(((IDataConnector)conn).Execute(bl));
                }
            }
            public static bool Create(MSSQLConnector conn)
            {
                using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
                {
                    bl.TableCreate(TableName, Columns);

                    return(((IDataConnector)conn).ExecuteNonQuery(bl) > 0);
                }
            }
            public static bool Create(MSSQLConnector conn)
            {
                using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
                {
                    bl.TableCreate(TableName, Columns);

                    return ((IDataConnector)conn).ExecuteNonQuery(bl) > 0;
                }
            }
        public static long Insert(MSSQLConnector conn, long groupId, long permissionId)
        {
            using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                bl.InsertInto(TableDefinition.TableName,
                    new DataParameter(TableDefinition.ColumnNames.GroupId, groupId),
                    new DataParameter(TableDefinition.ColumnNames.PermissionId, permissionId)
                );

                return ((IDataConnector)conn).ExecuteInsert(bl);
            }
        }
        public static long Insert(MSSQLConnector conn, string node, Permission permission)
        {
            using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                bl.InsertInto(TableDefinition.TableName,
                    new DataParameter(TableDefinition.ColumnNames.Node, node),
                    new DataParameter(TableDefinition.ColumnNames.Permission, permission)
                );

                return ((IDataConnector)conn).ExecuteInsert(bl);
            }
        }
        public static long Insert(MSSQLConnector conn, string node, Permission permission)
        {
            using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                bl.InsertInto(TableDefinition.TableName,
                              new DataParameter(TableDefinition.ColumnNames.Node, node),
                              new DataParameter(TableDefinition.ColumnNames.Permission, permission)
                              );

                return(((IDataConnector)conn).ExecuteInsert(bl));
            }
        }
        bool IPermissionHandler.AddNodeToUser(string username, string node, Permission permission)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ExecuteProcedure(Procedures.AddNodeToUser, "prm",
                    new DataParameter("UserName", username),
                    new DataParameter("Node", node),
                    new DataParameter("Permission", permission)
                );

                return Storage.ExecuteScalar<Int64>(sb) > 0;
            }
        }
        bool IPermissionHandler.AddOrUpdateGroup(string name, bool applyToGuests, string parent, byte r, byte g, byte b, string prefix, string suffix)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ExecuteProcedure(Procedures.AddOrUpdateGroup, "prm",
                    new DataParameter("Name", name),
                    new DataParameter("ApplyToGuests", applyToGuests),
                    new DataParameter("Parent", parent),
                    new DataParameter("R", r),
                    new DataParameter("G", g),
                    new DataParameter("B", b),
                    new DataParameter("Prefix", prefix),
                    new DataParameter("Suffix", suffix)
                );

                return Storage.ExecuteScalar<Int64>(sb) > 0;
            }
        }
        //TODO: character restrictions
        public static void EnterInfo(string userName, string hashedString)
        {
            var databaseManager = Shared.DBconnection.GetManager();

            try
            {
                var UN     = new KeyValuePair <string, object>("userName", userName);
                var HS     = new KeyValuePair <String, Object>("hashString", hashedString);
                var values = new[] { UN, HS };
                MSSQLQueryBuilder QBuilder = new MSSQLQueryBuilder();
                Query             query    = QBuilder.BuildInsertQuery("users", values);
                databaseManager.ExecuteNonQueryAsync(databaseManager.GetConnection(), query.QueryString, query.Parameters).Wait();
            }
            finally
            {
                databaseManager.GetConnection().Close();
            }
        }
Beispiel #15
0
        /// <summary>
        /// 提供对应数据库的数据库语句构建类
        /// </summary>
        /// <param name="session"></param>
        /// <returns></returns>
        public static IDbQueryBuilder GetDbQueryBuilder(this DbSession session)
        {
            IDbQueryBuilder result;

            switch (session.DatabaseType)
            {
            case EDatabaseType.MSSQL:
                result = new MSSQLQueryBuilder();
                break;

            case EDatabaseType.MySQL:
            case EDatabaseType.Oracle:
            //TODO 未支持多数据库
            default:
                throw new NotImplementedException("未实现该类别的QueryBuilder" + session.DatabaseType);
            }
            return(result);
        }
Beispiel #16
0
            public static bool Create(MSSQLConnector conn)
            {
                try
                {
                    using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
                    {
                        bl.TableCreate(TableName, Columns);

                        ((IDataConnector)conn).ExecuteNonQuery(bl);
                    }

                    //Set defaults
                    var pc = CommandParser.GetAvailableCommands(AccessLevel.PLAYER);
                    var ad = CommandParser.GetAvailableCommands(AccessLevel.OP);
                    var op = CommandParser.GetAvailableCommands(AccessLevel.CONSOLE); //Funny how these have now changed

                    CreateGroup("Guest", true, null, 255, 255, 255, pc
                                .Where(x => !String.IsNullOrEmpty(x.Value.Node))
                                .Select(x => x.Value.Node)
                                .Distinct()
                                .ToArray(), conn);
                    CreateGroup("Admin", false, "Guest", 240, 131, 77, ad
                                .Where(x => !String.IsNullOrEmpty(x.Value.Node))
                                .Select(x => x.Value.Node)
                                .Distinct()
                                .ToArray(), conn);
                    CreateGroup("Operator", false, "Admin", 77, 166, 240, op
                                .Where(x => !String.IsNullOrEmpty(x.Value.Node))
                                .Select(x => x.Value.Node)
                                .Distinct()
                                .ToArray(), conn);

                    return(true);
                }
                catch (Exception e)
                {
                    ProgramLog.Log(e);
                    return(false);
                }
            }
            public static bool Create(MSSQLConnector conn)
            {
                try
                {
                    using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
                    {
                        bl.TableCreate(TableName, Columns);

                        ((IDataConnector)conn).ExecuteNonQuery(bl);
                    }

                    //Set defaults
                    var pc = CommandParser.GetAvailableCommands(AccessLevel.PLAYER);
                    var ad = CommandParser.GetAvailableCommands(AccessLevel.OP);
                    var op = CommandParser.GetAvailableCommands(AccessLevel.CONSOLE); //Funny how these have now changed

                    CreateGroup("Guest", true, null, 255, 255, 255, pc
                        .Where(x => !String.IsNullOrEmpty(x.Value.Node))
                        .Select(x => x.Value.Node)
                        .Distinct()
                        .ToArray(), conn);
                    CreateGroup("Admin", false, "Guest", 240, 131, 77, ad
                        .Where(x => !String.IsNullOrEmpty(x.Value.Node))
                        .Select(x => x.Value.Node)
                        .Distinct()
                        .ToArray(), conn);
                    CreateGroup("Operator", false, "Admin", 77, 166, 240, op
                        .Where(x => !String.IsNullOrEmpty(x.Value.Node))
                        .Select(x => x.Value.Node)
                        .Distinct()
                        .ToArray(), conn);

                    return true;
                }
                catch (Exception e)
                {
                    ProgramLog.Log(e);
                    return false;
                }
            }
        public static void AddPart(System.String name, System.String SKU, string serialNumber, int count)
        {
            var DatabaseManager = Shared.DBconnection.GetManager();

            try
            {
                //TODO check for name and serialNumber duplication
                var namePair         = new KeyValuePair <string, object>("name", name);
                var SKUpair          = new KeyValuePair <System.String, object>("SKU", SKU);
                var serialNumberPair = new KeyValuePair <System.String, object>("serialNumber", serialNumber);
                var countPair        = new KeyValuePair <System.String, object>("count", count);
                var values           = new[] { namePair, SKUpair, serialNumberPair, countPair };

                MSSQLQueryBuilder QBuilder    = new MSSQLQueryBuilder();
                Query             insertQuery = QBuilder.BuildInsertQuery("parts", values);
                DatabaseManager.ExecuteNonQueryAsync(DatabaseManager.GetConnection(), insertQuery.QueryString, insertQuery.Parameters).Wait();
            }
            finally
            {
                DatabaseManager.GetConnection().Close();
            }
        }
        public async Task <bool> Authenticate(string userName, string hashPass)
        {
            var DatabaseManager = Shared.DBconnection.GetManager();

            try
            {
                MSSQLQueryBuilder QBuilder = new MSSQLQueryBuilder();
                Query             query    = QBuilder.BuildQuery("users", new[] { "userName", "hashString" });
                object[][]        table    = await DatabaseManager.ExecuteTableAsync(DatabaseManager.GetConnection(), query.QueryString).ConfigureAwait(false);

                for (int i = 0; i < table.Length; i++)
                {
                    if (table[i][0].Equals(userName) && table[i][1].Equals(hashPass))
                    {
                        return(true);
                    }
                }
            }
            finally
            {
                DatabaseManager.GetConnection().Close();
            }
            return(false);
        }
Beispiel #20
0
            static void CreateGroup(string name, bool guest, string parent, byte r, byte g, byte b, string[] nodes, MSSQLConnector conn)
            {
                long id;

                using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
                {
                    bl.InsertInto(TableName,
                                  new DataParameter(ColumnNames.Name, name),
                                  new DataParameter(ColumnNames.ApplyToGuests, guest),
                                  new DataParameter(ColumnNames.Parent, parent),
                                  new DataParameter(ColumnNames.Chat_Red, r),
                                  new DataParameter(ColumnNames.Chat_Green, g),
                                  new DataParameter(ColumnNames.Chat_Blue, b)
                                  );

                    id = ((IDataConnector)conn).ExecuteInsert(bl);
                }

                foreach (var nd in nodes)
                {
                    var nodeId = PermissionTable.Insert(conn, nd, Permission.Permitted);
                    GroupPermissions.Insert(conn, id, nodeId);
                }
            }
Beispiel #21
0
        static void Main(string[] args)
        {
            // VPN conn
            string connection = "Server=mssql.fhict.local;Database=dbi409368;User Id=dbi409368;Password=Heclepra9;";
            // Local conn
            //string connection = "Data Source=DESKTOP-1JEGGKM\\SQLEXPRESS;Initial Catalog=DBGenCore;Integrated Security=true;";

            IQueryBuilder builder = new MSSQLQueryBuilder();
            IHandler      handler = new MSSQLDatabaseHandler(connection);
            IParser       parser  = new DataRowParser();

            Repository <User> repo = new Repository <User>(new MSSQLContext <User>(builder, handler, parser));

            //// Insert test
            repo.Insert(new User()
            {
                Name      = "TEST USER 2.0 AAAA",
                BirthDate = DateTime.Now,
                Email     = "*****@*****.**",
                Password  = "******"
            });

            // Update test
            //repo.Update(new User()
            //{
            //    Id = 1005,
            //    Email = "*****@*****.**",
            //    Name = "TEEEST",
            //    BirthDate = DateTime.Now,
            //    Password = "******"
            //});

            // Delete test
            // By Id
            //repo.Delete(new User()
            //{
            //    Id = 1002
            //});
            // By custom object
            //repo.Delete(new User()
            //{
            //    Name = "Kevin",
            //    Email = "*****@*****.**"
            //});

            // Select test
            //var selects = new List<string>()
            //{
            //    //"distinct Name"
            //};
            //var filters = new List<ColumnFilter>()
            //{

            //};
            //var order = new List<KeyValuePair<string, string>>()
            //{

            //};
            //List<User> items = repo.GetWithFilter(selects, filters, order);
            //foreach (User u in items)
            //{
            //    Console.WriteLine(u.Name);
            //}
            Console.WriteLine("Done");
            Console.ReadKey();
        }
        Group IPermissionHandler.FindGroup(string name)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ExecuteProcedure(Procedures.FindGroup, "prm",
                    new DataParameter("Name", name)
                );

                var arr = Storage.ExecuteArray<Group>(sb);
                if (arr != null && arr.Length > 0)
                    return arr[0];
            }

            return null;
        }
        private Permission IsPermitted(string node, bool isGuest, string authentication = null)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ExecuteProcedure(Procedures.IsPermitted, "prm",
                    new DataParameter("Node", node),
                    new DataParameter("IsGuest", isGuest),
                    new DataParameter("Authentication", authentication)
                );

                return (Permission)Storage.ExecuteScalar<Int32>(sb);
            }
        }
        /// <summary>
        /// Gets the first guest group from the database
        /// </summary>
        /// <returns>The guest group.</returns>
        Group IPermissionHandler.GetGuestGroup()
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.SelectAll(GroupTable.TableDefinition.TableName, new WhereFilter[]
                {
                    new WhereFilter(GroupTable.TableDefinition.ColumnNames.ApplyToGuests, true)
                });

                var arr = Storage.ExecuteArray<Group>(sb);
                if (arr != null) return arr.FirstOrDefault();
            }

            return null;
        }
        string[] IPermissionHandler.GroupList()
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ExecuteProcedure(Procedures.GroupList);

                var lst = Storage.ExecuteArray<GroupList>(sb);
                if (lst != null)
                    return lst.Select(x => x.Name).ToArray();
            }
            return null;
        }
        NodePermission[] IPermissionHandler.UserNodes(string username)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ExecuteProcedure(Procedures.UserNodes, "prm",
                    new DataParameter("UserName", username)
                );

                return Storage.ExecuteArray<NodePermission>(sb);
            }
        }
        public bool Create(MSSQLConnector conn)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                var proc = PluginContent.GetResource("TDSM.Data.MSSQL.Procedures.Files." + Name + ".sql");

                sb.CommandType = System.Data.CommandType.Text;
                sb.CommandText = proc;

                return ((IDataConnector)conn).Execute(sb);
            }
        }
        bool IPermissionHandler.RemoveGroup(string name)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ExecuteProcedure(Procedures.RemoveGroup, "prm",
                    new DataParameter("Name", name)
                );

                return Storage.ExecuteNonQuery(sb) > 0;
            }
        }
        bool IPermissionHandler.RemoveGroupNode(string groupName, string node, Permission permission)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ExecuteProcedure(Procedures.RemoveGroupNode, "prm",
                    new DataParameter("GroupName", groupName),
                    new DataParameter("Node", node),
                    new DataParameter("Permission", permission)
                );

                return Storage.ExecuteScalar<Int64>(sb) > 0;
            }
        }
        public bool Exists(MSSQLConnector conn)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ProcedureExists(Name);

                return ((IDataConnector)conn).Execute(sb);
            }
        }
        bool IPermissionHandler.AddUserToGroup(string username, string groupName)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ExecuteProcedure(Procedures.AddUserToGroup, "prm",
                    new DataParameter("UserName", username),
                    new DataParameter("GroupName", groupName)
                );

                return Storage.ExecuteScalar<Int64>(sb) > 0;
            }
        }
            static void CreateGroup(string name, bool guest, string parent, byte r, byte g, byte b, string[] nodes, MSSQLConnector conn)
            {
                long id;
                using (var bl = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
                {
                    bl.InsertInto(TableName,
                        new DataParameter(ColumnNames.Name, name),
                        new DataParameter(ColumnNames.ApplyToGuests, guest),
                        new DataParameter(ColumnNames.Parent, parent),
                        new DataParameter(ColumnNames.Chat_Red, r),
                        new DataParameter(ColumnNames.Chat_Green, g),
                        new DataParameter(ColumnNames.Chat_Blue, b)
                    );

                    id = ((IDataConnector)conn).ExecuteInsert(bl);
                }

                foreach (var nd in nodes)
                {
                    var nodeId = PermissionTable.Insert(conn, nd, Permission.Permitted);
                    GroupPermissions.Insert(conn, id, nodeId);
                }
            }
        string[] IPermissionHandler.UserGroupList(string username)
        {
            using (var sb = new MSSQLQueryBuilder(SqlPermissions.SQLSafeName))
            {
                sb.ExecuteProcedure(Procedures.UserGroupList, "prm",
                    new DataParameter("UserName", username)
                );

                var lst = Storage.ExecuteArray<UserGroupList>(sb);
                if (lst != null)
                    return lst.Select(x => x.Name).ToArray();
            }
            return null;
        }