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(); } }
/// <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); }
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); }
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); } }
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; } }
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; }