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