public IPolarisAccountHolder GetSingleAccountByGuidEF(string accountGuid)
{
//List<IPolarisAccountHolder> AccountsList = new List<IPolarisAccountHolder>();
IPolarisAccountHolder accountHolder = new PolarisAccountHolder();
try
{
PolarisEFContext efContext = new PolarisEFContext(connectionString);
//Get all the rows
var theRow = (from rws in efContext.PolarisAccounts
.Where(rws => rws.AccountGuid.Equals(Guid.Parse(accountGuid)))
select new
{
//Need to cast guid to string or will get
//invalid cast error.
accountGuid = rws.AccountGuid.ToString(),
accountHolder = rws.AccountHolder,
accountType = rws.AccountType
}).FirstOrDefault();
accountHolder.AccountGuid = Guid.Parse(theRow.accountGuid);
accountHolder.AccountHolder = theRow.accountHolder;
accountHolder.AccountType = theRow.accountType;
//return the account
return(accountHolder);
}
catch (Exception exception)
{
throw exception;
}
}
public IPolarisAccountHolder GetSingleAccountInfoByGuid(string sqlCommandText)
{
SqlConnection sqlConnection = ConnectToSqlServer(connectionString);
PolarisAccountHolder accountholder = new PolarisAccountHolder();
try
{
//open the connection
//sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(sqlCommandText, sqlConnection);
//Use a DataReader
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
accountholder.AccountGuid = Guid.Parse(dataReader[0].ToString());
accountholder.AccountHolder = dataReader[1].ToString();
accountholder.AccountType = dataReader[2].ToString();
}
//return the account
return(accountholder);
}
catch (Exception exception)
{
throw exception;
}
finally
{
sqlConnection.Close();
}
}
public bool CreateNewAccountHolderAddAccountGuid(IPolarisAccountHolder accountHolder)
{
bool createSuccessful = false;
try
{
PolarisEFContext efContext = new PolarisEFContext(connectionString);
var polarisAccountHolder = new PolarisAccountHolder
{
//Create a new guid
AccountGuid = Guid.NewGuid(),
AccountHolder = accountHolder.AccountHolder,
AccountType = accountHolder.AccountType
};
efContext.Entry(polarisAccountHolder).State = EntityState.Added;
efContext.PolarisAccounts.Add(polarisAccountHolder);
efContext.SaveChanges();
createSuccessful = true;
return(createSuccessful);
}
catch (Exception exception)
{
throw exception;
}
}
public Task <List <IPolarisAccountHolder> > GetAllAccounts(string polarisCommandText)
{
//open the connection
SqlConnection sqlConnection = ConnectToSqlServer(connectionString);
List <IPolarisAccountHolder> AccountsList = new List <IPolarisAccountHolder>();
try
{
#region Entity Framework
PolarisEFContext efContext = new PolarisEFContext(connectionString);
var theRows = (from rws in efContext.PolarisAccounts
select new
{
accountGuid = rws.AccountGuid.ToString(),
accountHolder = rws.AccountHolder,
accountType = rws.AccountType
}).ToList();
foreach (var zfRow in theRows)
{
string acctGuid = zfRow.accountGuid;
string acctName = zfRow.accountHolder;
string accType = zfRow.accountType;
}
#endregion
SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection);
//Use a DataReader
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
IPolarisAccountHolder accountHolder = new PolarisAccountHolder();
//accountHolder accountInfo = new AccountInfo();
accountHolder.AccountGuid = Guid.Parse(dataReader[0].ToString());
accountHolder.AccountHolder = dataReader[1].ToString();
accountHolder.AccountType = dataReader[2].ToString();
AccountsList.Add(accountHolder);
}
//return the account
return(Task.FromResult(AccountsList));
}
catch (Exception exception)
{
throw exception;
}
}
public Task <List <IPolarisAccountHolder> > GetAllAccountsEF()
{
//open the connection
List <IPolarisAccountHolder> AccountsList = new List <IPolarisAccountHolder>();
try
{
PolarisEFContext efContext = new PolarisEFContext(connectionString);
//Get all the rows
var theRows = (from rws in efContext.PolarisAccounts
select new
{
//Need to cast guid to string or will get
//invalid cast error.
accountGuid = rws.AccountGuid.ToString(),
accountHolder = rws.AccountHolder,
accountType = rws.AccountType
}).ToList();
//Put the rows into the PolarisAccountHolder list
foreach (var zfRow in theRows)
{
IPolarisAccountHolder accountHolder = new PolarisAccountHolder();
//Pass AccountGuid back as Guid instead of string
accountHolder.AccountGuid = Guid.Parse(zfRow.accountGuid);
accountHolder.AccountHolder = zfRow.accountHolder;
accountHolder.AccountType = zfRow.accountType;
AccountsList.Add(accountHolder);
}
//return the account
return(Task.FromResult(AccountsList));
}
catch (Exception exception)
{
throw exception;
}
}
public void PostAccountHolderNoAccountGuidField([FromBody] PolarisAccountHolder polarisAccountHolder)
{
try
{
string connectionString = config.GetSection("ConnectionStrings:PolarisDatabase").Value;
#region EF Testing
polarisEfDataLibrary = new PolarisEFDataLibrary(connectionString);
IPolarisAccountHolder accountHolder = new PolarisAccountHolder
{
AccountGuid = Guid.NewGuid(),
AccountHolder = polarisAccountHolder.AccountHolder,
AccountType = polarisAccountHolder.AccountType
};
bool isCreated = polarisEfDataLibrary.CreateNewAccountHolderAddAccountGuid(accountHolder);
#endregion
}
catch (Exception)
{
throw;
}
}
public bool DeleteAccountHolderByAccountGuid(string accountGuid)
{
bool deleteSuccessful = false;
try
{
PolarisEFContext efContext = new PolarisEFContext(connectionString);
var polarisAccount = new PolarisAccountHolder
{
AccountGuid = Guid.Parse(accountGuid)
};
efContext.Entry(polarisAccount).State = EntityState.Deleted;
efContext.PolarisAccounts.Remove(polarisAccount);
//efContext.SaveChanges(); //Removed as it was throwing error
return(deleteSuccessful);
}
catch (Exception exception)
{
throw exception;
}
}
public void PostAccountHolder([FromBody] PolarisAccountHolder polarisAccountHolder)
{
try
{
string connectionString = config.GetSection("ConnectionStrings:PolarisDatabase").Value;
#region SQL Testing
//Guid accountHolderGuid = Guid.Parse(polarisAccountHolder.AccountGuid.ToString());
//string accountHolder = polarisAccountHolder.AccountHolder;
//string accountType = polarisAccountHolder.AccountType;
//string commandText =
// $"INSERT INTO PolarisAccounts (AccountGuid,AccountHolder,AccountType) " +
// $"VALUES('{accountHolderGuid}','{accountHolder}','{accountType}')";
//polarisLibrary = new PolarisDataLibrary(connectionString, commandText);
//polarisLibrary.CreateAccountHolder();
#endregion
#region EF Testing
polarisEfDataLibrary = new PolarisEFDataLibrary(connectionString);
IPolarisAccountHolder accountHolder = new PolarisAccountHolder
{
AccountGuid = Guid.NewGuid(),
AccountHolder = polarisAccountHolder.AccountHolder,
AccountType = polarisAccountHolder.AccountType
};
bool isCreated = polarisEfDataLibrary.CreateNewAccountHolder(accountHolder);
#endregion
}
catch (Exception)
{
throw;
}
}
