// This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { services.AddDbContext <LeaderboardContext>(o => o.UseNpgsql(DBConnectionHelper.GetConnectionString())); services.AddControllers(); services.AddScoped <LeaderboardRepository>(); }
public void FriendUsersTest() { for (int i = 0; i < 2; i++) { DBConnectionHelper.ExecuteNonQuery(DBConnectionHelper.MakeSQLInsertQuery(DBConnectionHelper._tableUser, new String[] { DBConnectionHelper._columnName, DBConnectionHelper._columPasword, DBConnectionHelper._columnEmail, DBConnectionHelper._columnGuid }, new String[] { "Username" + i.ToString("00"), "Password" + i.ToString("00"), "email" + i.ToString("00") + "@mail.de", Guid.NewGuid().ToString() })); } Thread.Sleep(1000); List <Record> list = DBConnectionHelper.ReadUserRecords(); int userID1 = Convert.ToInt32(list[0].Entries["ID"]); int userID2 = Convert.ToInt32(list[1].Entries["ID"]); bool re = DBConnectionHelper.FriendUsers(userID1, userID2); Assert.AreEqual(true, re); List <int> actual = DBConnectionHelper.ReadBuddiesFromUser(userID1); Assert.AreEqual(1, actual.Count); Assert.AreEqual(userID2, actual[0]); actual = DBConnectionHelper.ReadBuddiesFromUser(userID2); Assert.AreEqual(1, actual.Count); Assert.AreEqual(userID1, actual[0]); }
public void ClearTableTest() { DBConnectionHelper.Init(_ProjectPath.projectPath + @"\DB\Database_Test.mdb"); string table = DBConnectionHelper._tableBuddy; DBConnectionHelper.ClearTable(table); Thread.Sleep(1000); List <Record> list = DBConnectionHelper.ReadUserRecords(); int before = list.Count; if (before == 0) { DBConnectionHelper.ExecuteNonQuery(DBConnectionHelper.MakeSQLInsertQuery(DBConnectionHelper._tableUser, new String[] { DBConnectionHelper._columnName, DBConnectionHelper._columPasword, DBConnectionHelper._columnEmail, DBConnectionHelper._columnGuid }, new String[] { "Username", "Password", "*****@*****.**", Guid.NewGuid().ToString() })); Thread.Sleep(1000); before = (list = DBConnectionHelper.ReadUserRecords()).Count; } Assert.IsTrue(before > 0, "No entries in table"); table = DBConnectionHelper._tableUser; DBConnectionHelper.ClearTable(table); Thread.Sleep(1000); int after = (list = DBConnectionHelper.ReadUserRecords()).Count; Assert.AreEqual(0, after); }
public bool UpdateBulkDOFAFormData(DataTable data, int DataCaptYM, string DeptID, string empDept = "") { SqlConnection connection = null; try { Dictionary <string, object> sqlParamDictionary = new Dictionary <string, object>(); sqlParamDictionary.Add("dofaData", data); sqlParamDictionary.Add("DataCaptYM", DataCaptYM); sqlParamDictionary.Add("DeptID", DeptID); sqlParamDictionary.Add("empDept", empDept); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParamDictionary, _Bulk_Update_DOFA_Data); connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; int result = command.ExecuteNonQuery(); if (result > 0) { return(true); } else { return(false); } } catch (Exception ex) { throw ex; } finally { DBConnectionHelper.CloseSqlConnection(connection); } }
public bool UpdateBulkDFNPData(DataTable data, int DataCaptYM) { SqlConnection connection = null; try { Dictionary <string, object> sqlParamDictionary = new Dictionary <string, object>(); data.Columns.Remove("EmpDept"); sqlParamDictionary.Add("FinInfoData", data); sqlParamDictionary.Add("DataCaptYM", DataCaptYM); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParamDictionary, _Bulk_Update_DFNP_Data); connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; int result = command.ExecuteNonQuery(); if (result > 0) { return(true); } else { return(false); } } catch (Exception ex) { throw ex; } finally { DBConnectionHelper.CloseSqlConnection(connection); } }
/// <summary> /// /// </summary> /// <param name="info2"></param> /// <returns></returns> public bool InsertTostInfo2(stInfo2 info2) { IDbCommand command = new SqlCommand().GetCommandWithParameters(info2, _Insert_INFO2); SqlConnection connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; command.ExecuteNonQuery(); DBConnectionHelper.CloseSqlConnection(connection); return(true); }
/// <summary> /// /// </summary> /// <param name="info2"></param> /// <returns></returns> public bool UpdateToLibInfo(LibInfo libInfo) { IDbCommand command = new SqlCommand().GetCommandWithParameters(libInfo, _Update_LIBINFO); SqlConnection connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; command.ExecuteNonQuery(); DBConnectionHelper.CloseSqlConnection(connection); return(true); }
public void InitTest() { string dbPath = string.Empty; // TODO: Passenden Wert initialisieren DBConnectionHelper.Init(dbPath); Assert.IsFalse(String.IsNullOrEmpty(DBConnectionHelper._columnBuddyBuddy), "_columnBuddyBuddy is null or empty. config was not read"); Assert.IsFalse(String.IsNullOrEmpty(DBConnectionHelper._columnBuddyUser), "_columnBuddyUser is null or empty. config was not read"); Assert.IsFalse(String.IsNullOrEmpty(DBConnectionHelper._columnEmail), "_columnEmail is null or empty. config was not read"); Assert.IsFalse(String.IsNullOrEmpty(DBConnectionHelper._columnGuid), "_columnGuid is null or empty. config was not read"); Assert.IsFalse(String.IsNullOrEmpty(DBConnectionHelper._columnName), "_columnName is null or empty. config was not read"); Assert.IsFalse(String.IsNullOrEmpty(DBConnectionHelper._columPasword), "_columPasword is null or empty. config was not read"); Assert.IsFalse(String.IsNullOrEmpty(DBConnectionHelper._tableBuddy), "_tableBuddy is null or empty. config was not read"); Assert.IsFalse(String.IsNullOrEmpty(DBConnectionHelper._tableUser), "_tableUser is null or empty. config was not read"); }
public List <TPDept> GetTPDeptFormDataByID(int DataCaptYM) { Dictionary <string, object> sqlParamDictionary = new Dictionary <string, object>(); sqlParamDictionary.Add("DataCaptYM", DataCaptYM); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParamDictionary, _SELECT_TPDept_Data); SqlConnection connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; List <TPDept> tPDepts = EntityMapper.MapCollection <TPDept>(command.ExecuteReader()).ToList(); DBConnectionHelper.CloseSqlConnection(connection); return(tPDepts); }
public FinInfo GetDFNPDataByID(int DataCaptYM, string DeptId) { Dictionary <string, object> sqlParamDictionary = new Dictionary <string, object>(); sqlParamDictionary.Add("DataCaptYM", DataCaptYM); sqlParamDictionary.Add("DeptID", DeptId); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParamDictionary, _SELECT_DFNP_Data); SqlConnection connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; FinInfo finInfo = EntityMapper.MapSingle <FinInfo>(command.ExecuteReader()); DBConnectionHelper.CloseSqlConnection(connection); return(finInfo); }
public List <DofaInfo> GetDOFAFormDataByID(int DataCaptYM, string DeptID, string empDept = "") { Dictionary <string, object> sqlParamDictionary = new Dictionary <string, object>(); sqlParamDictionary.Add("DataCaptYM", DataCaptYM); sqlParamDictionary.Add("DeptID", DeptID); sqlParamDictionary.Add("empDept", empDept); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParamDictionary, _SELECT_DOFA_Data); SqlConnection connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; List <DofaInfo> dofaInfos = EntityMapper.MapCollection <DofaInfo>(command.ExecuteReader()).ToList(); DBConnectionHelper.CloseSqlConnection(connection); return(dofaInfos); }
/// <summary> /// /// </summary> /// <param name="DataCaptYM"></param> /// <param name="DeptID"></param> /// <param name="MenuID"></param> /// <returns></returns> public stInfo2 GetADIRFormDataByID(int DataCaptYM, string DeptID, string MenuID) { Dictionary <string, object> sqlParamDictionary = new Dictionary <string, object>(); sqlParamDictionary.Add("DataCaptYM", DataCaptYM); sqlParamDictionary.Add("DeptID", DeptID); sqlParamDictionary.Add("MenuID", string.IsNullOrWhiteSpace(MenuID) ? "ADIR" : MenuID); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParamDictionary, _SELECT_DOAA1_INFO); SqlConnection connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; stInfo2 stInfo2 = EntityMapper.MapSingle <stInfo2>(command.ExecuteReader()); DBConnectionHelper.CloseSqlConnection(connection); return(stInfo2); }
public void ExecuteNonQueryTest() { string sql = "INSERT INTO TUsers (CName, CPassword, CEmail, CGuid) VALUES ('Testname', 'Testpassword', 'Testemail', 'Testguid')"; DBConnectionHelper.ExecuteNonQuery(sql); List <Record> list = DBConnectionHelper.ReadUserRecords(); Assert.AreEqual(1, list.Count); Assert.AreEqual("Testname", list[0].Entries[DBConnectionHelper._columnName]); Assert.AreEqual("Testpassword", list[0].Entries[DBConnectionHelper._columPasword]); Assert.AreEqual("Testemail", list[0].Entries[DBConnectionHelper._columnEmail]); Assert.AreEqual("Testguid", list[0].Entries[DBConnectionHelper._columnGuid]); sql = "DELETE FROM TUsers WHERE ID=" + list[0].Entries["ID"]; DBConnectionHelper.ExecuteNonQuery(sql); list = DBConnectionHelper.ReadUserRecords(); Assert.AreEqual(0, list.Count); }
public void DeleteUserTest() { FriendUsersTest(); List <Record> list = DBConnectionHelper.ReadUserRecords(); Assert.AreEqual(2, list.Count); int id = Convert.ToInt32(list[0].Entries["ID"]); bool actual = DBConnectionHelper.DeleteUser(id); Assert.AreEqual(true, actual); list = DBConnectionHelper.ReadUserRecords(); Assert.AreEqual(1, list.Count); List <int> buddies = DBConnectionHelper.ReadBuddiesFromUser(id); Assert.AreEqual(0, buddies.Count); }
public void MakeSQLInsertQueryTest() { string table = "TUsers"; string[] columns = { "Name", "Password", "Email" }; string[] values = { "Testname", "Testpassword", "Testemail" }; string expected = "INSERT INTO TUsers (Name, Password, Email) VALUES ('Testname', 'Testpassword', 'Testemail')"; string actual; actual = DBConnectionHelper.MakeSQLInsertQuery(table, columns, values); Assert.AreEqual(expected, actual); table = "TUsers"; columns = new string[] { "Name" }; values = new string[] { "Testname" }; expected = "INSERT INTO TUsers (Name) VALUES ('Testname')"; actual = DBConnectionHelper.MakeSQLInsertQuery(table, columns, values); Assert.AreEqual(expected, actual); }
public void MakeSQLDeleteQueryTest() { string table = "Users"; string[] key = { "CUsers", "CBuddy" }; string[] value = { "20", "21" }; string expected = "DELETE FROM Users WHERE CUsers=20 AND CBuddy=21"; string actual; actual = DBConnectionHelper.MakeSQLDeleteQuery(table, key, value); Assert.AreEqual(expected, actual); table = "Users"; key = new string[] { "CUsers" }; value = new string[] { "20" }; expected = "DELETE FROM Users WHERE CUsers=20"; actual = DBConnectionHelper.MakeSQLDeleteQuery(table, key, value); Assert.AreEqual(expected, actual); }
public DataSet GetReportData(int DataCaptYM, string MenuID, string DeptID = "") { Dictionary <string, object> sqlParamDictionary = new Dictionary <string, object>(); sqlParamDictionary.Add("DataCaptYM", DataCaptYM); sqlParamDictionary.Add("MenuID", MenuID); sqlParamDictionary.Add("DeptID", DeptID); SqlDataAdapter dataAdapter = new SqlDataAdapter(); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParamDictionary, _Rpt_SELECT_BY_DataCaptYM_DeptID); SqlConnection sqlConnection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = sqlConnection; dataAdapter.SelectCommand = (SqlCommand)command; DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); return(dataSet); }
public void ReadBuddiesFromUserTest() { int id = 1; int expected = 0; List <int> actual; actual = DBConnectionHelper.ReadBuddiesFromUser(id); Assert.IsNotNull(actual); Assert.AreEqual(expected, actual.Count); int count = 3; for (int i = 0; i < count; i++) { DBConnectionHelper.ExecuteNonQuery(DBConnectionHelper.MakeSQLInsertQuery(DBConnectionHelper._tableUser, new String[] { DBConnectionHelper._columnName, DBConnectionHelper._columPasword, DBConnectionHelper._columnEmail, DBConnectionHelper._columnGuid }, new String[] { "Username" + i.ToString("00"), "Password" + i.ToString("00"), "email" + i.ToString("00") + "@mail.de", Guid.NewGuid().ToString() })); } Thread.Sleep(1000); List <Record> list = DBConnectionHelper.ReadUserRecords(); for (int i = 0; i < count - 1; i++) { DBConnectionHelper.FriendUsers(Convert.ToInt32(list[0].Entries["ID"]), Convert.ToInt32(list[i + 1].Entries["ID"])); } actual = DBConnectionHelper.ReadBuddiesFromUser(Convert.ToInt32(list[0].Entries["ID"])); for (int i = 0; i < count - 1; i++) { Assert.IsTrue(actual.Contains(Convert.ToInt32(list[i + 1].Entries["ID"])), "Expected ID " + list[i + 1].Entries["ID"] + " not found"); } Assert.AreEqual(count - 1, actual.Count); }
public void Setup() { /*ConfigurationManager.OpenExeConfiguration(_ProjectPath.projectPath + "\\Web.config"); * Thread.Sleep(1000); * ConfigurationManager.RefreshSection("appSettings"); * foreach (String item in ConfigurationManager.AppSettings.AllKeys) { * String value = ConfigurationManager.AppSettings[item]; * } */ DBConnectionHelper.Init(_ProjectPath.projectPath + @"\DB\Database_Test.mdb"); DBConnectionHelper._columnBuddyBuddy = "CUser"; DBConnectionHelper._columnBuddyUser = "******"; DBConnectionHelper._columnEmail = "CEmail"; DBConnectionHelper._columnGuid = "CGuid"; DBConnectionHelper._columnName = "CName"; DBConnectionHelper._columPasword = "CPassword"; DBConnectionHelper._tableBuddy = "TBuddies"; DBConnectionHelper._tableUser = "******"; DBConnectionHelper.ClearTable(DBConnectionHelper._tableBuddy); DBConnectionHelper.ClearTable(DBConnectionHelper._tableUser); }
public DataTable GetTableDetail(string TableName) { SqlConnection connection = null; try { Dictionary <string, object> sqlParameterDictionary = new Dictionary <string, object>(); sqlParameterDictionary.Add("TableName", TableName); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParameterDictionary, _get_Table_Detail); connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; SqlDataAdapter dataAdapter = new SqlDataAdapter(); DataSet TableDetailDataset = new DataSet(); dataAdapter.SelectCommand = (SqlCommand)command; dataAdapter.Fill(TableDetailDataset); return(TableDetailDataset.Tables[0]); } finally { DBConnectionHelper.CloseSqlConnection(connection); } }
public bool CreateAccount([FromBody] LoginModel login) { DBConnectionHelper dbHelp = new DBConnectionHelper("digitaloceanmvc"); Tuple <string, string> loginInfo = SHAHash.HashPassword(login.username, login.password); Console.WriteLine(loginInfo.Item1 + loginInfo.Item2);// Item 1 Contains the password * item2 contains the salt //store username, password, email, salt in db dbHelp.IsConnect(); try { dbHelp.InsertUser(login.username, loginInfo.Item1, login.email, loginInfo.Item2); }catch (Exception ex) { Console.WriteLine(ex); } dbHelp.Connection.Close(); return(true); }
public Tuple <List <InfraDept>, List <InfraInfo> > GetInfraFormDataByID(int DataCaptYM, string DeptId) { Dictionary <string, object> sqlParamDictionary = new Dictionary <string, object>(); sqlParamDictionary.Add("DataCaptYM", DataCaptYM); sqlParamDictionary.Add("DeptId", DeptId); SqlDataAdapter dataAdapter = new SqlDataAdapter(); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParamDictionary, _SELECT_INFRA_Data); SqlConnection connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; dataAdapter.SelectCommand = (SqlCommand)command; DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); DBConnectionHelper.CloseSqlConnection(connection); List <InfraDept> infraDepts = EntityMapper.MapCollection <InfraDept>(dataSet.Tables[0]).ToList(); List <InfraInfo> infraInfos = EntityMapper.MapCollection <InfraInfo>(dataSet.Tables[1]).ToList(); return(new Tuple <List <InfraDept>, List <InfraInfo> >(infraDepts, infraInfos)); }
public RankMesg GetRankMsg() { SqlConnection connection = null; try { Dictionary <string, object> sqlParameterDictionary = new Dictionary <string, object>(); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParameterDictionary, _Get_Rank_Info_Msg); connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; SqlDataAdapter dataAdapter = new SqlDataAdapter(); DataSet RankMesgDataset = new DataSet(); dataAdapter.SelectCommand = (SqlCommand)command; dataAdapter.Fill(RankMesgDataset); List <RankMesg> rankUserMenu1 = EntityMapper.MapCollection <RankMesg>(RankMesgDataset.Tables[0]).ToList(); RankMesg rankMesg = rankUserMenu1.Count > 0 ? rankUserMenu1.FirstOrDefault() : new RankMesg(); return(rankMesg); } finally { DBConnectionHelper.CloseSqlConnection(connection); } }
public List <RankUserMenu> GetCurrentUserMenuByUserId(string UserID) { SqlConnection connection = null; try { Dictionary <string, object> sqlParameterDictionary = new Dictionary <string, object>(); sqlParameterDictionary.Add("UserID", UserID); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParameterDictionary, _Get_Current_User_Menu); connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; SqlDataAdapter dataAdapter = new SqlDataAdapter(); DataSet RankUserMenuDataset = new DataSet(); dataAdapter.SelectCommand = (SqlCommand)command; dataAdapter.Fill(RankUserMenuDataset); List <RankUserMenu> rankUserMenu = EntityMapper.MapCollection <RankUserMenu>(RankUserMenuDataset.Tables[0]).ToList(); return(rankUserMenu); } finally { DBConnectionHelper.CloseSqlConnection(connection); } }
public void UnfriendUsersTest() { FriendUsersTest(); List <Record> list = DBConnectionHelper.ReadUserRecords(); Assert.AreEqual(2, list.Count); int userID1 = Convert.ToInt32(list[0].Entries["ID"]); int userID2 = Convert.ToInt32(list[1].Entries["ID"]); bool actual = DBConnectionHelper.UnfriendUsers(userID1, userID2); Assert.AreEqual(true, actual); list = DBConnectionHelper.ReadUserRecords(); Assert.AreEqual(2, list.Count); List <int> buddies = DBConnectionHelper.ReadBuddiesFromUser(userID1); Assert.AreEqual(0, buddies.Count); buddies = DBConnectionHelper.ReadBuddiesFromUser(userID2); Assert.AreEqual(0, buddies.Count); }
public bool UpdateBulkInfraFormData(DataTable infraDeptData, DataTable infraInfoData, int DataCaptYM, bool IsSaveInfraInfo) { SqlConnection connection = null; try { Dictionary <string, object> sqlParamDictionary = new Dictionary <string, object>(); sqlParamDictionary.Add("InfraDeptData", infraDeptData); if (infraInfoData != null) { sqlParamDictionary.Add("InfraInfoData", infraInfoData); } sqlParamDictionary.Add("DataCaptYM", DataCaptYM); sqlParamDictionary.Add("IsSaveInfraInfo", IsSaveInfraInfo); IDbCommand command = new SqlCommand().GetCommandWithParameters(sqlParamDictionary, _Bulk_Update_INFRA_Data); connection = DBConnectionHelper.OpenNewSqlConnection(this.ConnectionString); command.Connection = connection; int result = command.ExecuteNonQuery(); if (result > 0) { return(true); } else { return(false); } } catch (Exception ex) { throw ex; } finally { DBConnectionHelper.CloseSqlConnection(connection); } }
public void ReadUserRecordsTest() { int expected = 0; // List <Record> actual; actual = DBConnectionHelper.ReadUserRecords(); Assert.IsNotNull(actual); Assert.AreEqual(expected, actual.Count); int count = 3; for (int i = 0; i < count; i++) { DBConnectionHelper.ExecuteNonQuery(DBConnectionHelper.MakeSQLInsertQuery(DBConnectionHelper._tableUser, new String[] { DBConnectionHelper._columnName, DBConnectionHelper._columPasword, DBConnectionHelper._columnEmail, DBConnectionHelper._columnGuid }, new String[] { "Username" + i.ToString("00"), "Password" + i.ToString("00"), "email" + i.ToString("00") + "@mail.de", Guid.NewGuid().ToString() })); } Thread.Sleep(1000); actual = DBConnectionHelper.ReadUserRecords(); Assert.IsNotNull(actual); Assert.AreEqual(count, actual.Count); for (int i = 0; i < count; i++) { Record record = actual[i]; Assert.AreEqual("Username" + i.ToString("00"), record.Entries[DBConnectionHelper._columnName]); Assert.AreEqual("Password" + i.ToString("00"), record.Entries[DBConnectionHelper._columPasword]); Assert.AreEqual("email" + i.ToString("00") + "@mail.de", record.Entries[DBConnectionHelper._columnEmail]); } }
public void Teardown() { DBConnectionHelper.ClearTable(DBConnectionHelper._tableBuddy); DBConnectionHelper.ClearTable(DBConnectionHelper._tableUser); }
public void Setup() { DBConnectionHelper.Init(_ProjectPath.projectPath + @"\DB\Database_Test.mdb"); DBConnectionHelper.ClearTable(DBConnectionHelper._tableBuddy); DBConnectionHelper.ClearTable(DBConnectionHelper._tableUser); }
public Bus_Templates() { DBConnectionHelper connectionHelper = new DBConnectionHelper(); _objLst = connectionHelper.database.GetCollection <Templates>("Templates"); }