/// <summary> /// Adds to [Account] if the type doesn't already exists. /// Also adds reference to account type into [User_Accounts]. (Links the account type to the user) /// </summary> /// <param name="AccoutTypes"></param> /// <param name="UserEmail"></param> public void AddAccountTypes(List <AccountTypeModel> AccoutTypes, string UserEmail) { SqlDataProvider db = new SqlDataProvider(); try { using (SqlConnection connection = (SqlConnection)db.GetConnection()) { connection.Open(); string preCheck = "SELECT ID FROM [dbo].[Account] WHERE Type LIKE {0};"; string preCheckConditions = ""; string query = ""; for (int i = 0; i < AccoutTypes.Count; i++) { preCheckConditions += ("'" + AccoutTypes[i].ID + "'"); if (AccoutTypes.Count > 1 && i < AccoutTypes.Count - 1) { preCheckConditions += " OR "; } } query = string.Format(preCheck, preCheckConditions); SqlCommand preCheckCommand = db.CreateCommand(query, connection); SqlDataReader preCheckReader = db.ExecuteReader(preCheckCommand); List <int> PotentialDuplicateAccountTypeIDs = new List <int>(); while (preCheckReader.Read()) { PotentialDuplicateAccountTypeIDs.Add(Utilities.ParseInt(preCheckReader["ID"].ToString())); } preCheckReader.Close(); List <AccountTypeModel> NonDuplicateAccoutTypes = new List <AccountTypeModel>(); for (int i = 0; i < AccoutTypes.Count; i++) { if (!PotentialDuplicateAccountTypeIDs.Contains(AccoutTypes[i].ID)) { NonDuplicateAccoutTypes.Add(AccoutTypes[i]); } } List <int> AccountTypeIDs = new List <int>(); if (NonDuplicateAccoutTypes.Count > 0) { string queryTemplate = "INSERT INTO [dbo].[Account] VALUES ('{0}');"; query = ""; foreach (AccountTypeModel Account in NonDuplicateAccoutTypes) { //AccountType query += string.Format(queryTemplate, Account.AccountType); } //Returns the ID of the AccoutTypes we just created query += " SELECT SCOPE_IDENTITY() AS [NewIDs];"; SqlCommand command = db.CreateCommand(query, connection); SqlDataReader reader = db.ExecuteReader(command); while (reader.Read()) { AccountTypeIDs.Add(Utilities.ParseInt(reader["NewIDs"].ToString())); } reader.Close(); } string template = "INSERT INTO [dbo].[User_Accounts] VALUES('{0}',{1}); "; string UAQuery = ""; string UserUID = Utilities.GetUsersUID(UserEmail); foreach (int id in AccountTypeIDs) { UAQuery += string.Format(template, UserUID, id); } SqlCommand UTcommand = db.CreateCommand(UAQuery, connection); UTcommand.ExecuteScalar(); preCheckReader.Close(); } } catch (Exception ex) { throw ex; } }