private DBService(string connStr)
{
_conn = new SqlConnection(connStr);
_trans = null;
_wasOpened = false;
_location = DBLocation.Invalid;
}
public DBLocation GetOrCreateDBLocation(StreetAddress address)
{
DBLocation existing = SearchDBLocations(address, 2).FirstOrDefault();
if (existing != null)
{
return(existing);
}
return(CreateDBLocation(address));
}
private void AddLocationToQueryAutoComplete(DBLocation model)
{
bool bHasName = !String.IsNullOrWhiteSpace(model.Name);
bool bHasAddress = !String.IsNullOrWhiteSpace(model.AddressLine);
bool bHasZip = !String.IsNullOrWhiteSpace(model.PostalCode);
bool bHasCity = !String.IsNullOrWhiteSpace(model.Locality);
bool bHasState = !String.IsNullOrWhiteSpace(model.AdminDistrict);
bool bHasCountry = !String.IsNullOrWhiteSpace(model.CountryRegion);
Helpers.BlockUntilFinished(ref _initTask);
LocationNode node;
//TODO:
}
public DBLocation CreateDBLocation(StreetAddress address)
{
if (String.IsNullOrWhiteSpace(address.CountryRegion))
{
throw new ArgumentException("Country Required");
}
if (String.IsNullOrWhiteSpace(address.AdminDistrict))
{
throw new ArgumentException("State Required");
}
if (String.IsNullOrWhiteSpace(address.Locality))
{
throw new ArgumentException("City Required");
}
DBLocation model = new DBLocation(address);
using (SqlCommand cmd = new SqlCommand("[dbo].[sp_Locations_CreateOne]", new SqlConnection(Ctx.Config.dbUniHangoutsWrite))
{
CommandType = CommandType.StoredProcedure
}) {
SqlParameter @LocationID = cmd.AddParam(ParameterDirection.Output, SqlDbType.BigInt, nameof(model.@LocationID), null);
SqlParameter @ParentLocationID = cmd.AddParam(ParameterDirection.InputOutput, SqlDbType.BigInt, nameof(model.@ParentLocationID), model.ParentLocationID);
cmd.AddParam(ParameterDirection.Input, SqlDbType.VarChar, nameof(model.@Name), model.@Name);
cmd.AddParam(ParameterDirection.Input, SqlDbType.VarChar, nameof(model.@AddressLine), model.@AddressLine);
cmd.AddParam(ParameterDirection.Input, SqlDbType.VarChar, nameof(model.@Locality), model.@Locality);
cmd.AddParam(ParameterDirection.Input, SqlDbType.VarChar, nameof(model.@AdminDistrict), model.@AdminDistrict);
cmd.AddParam(ParameterDirection.Input, SqlDbType.VarChar, nameof(model.@PostalCode), model.@PostalCode);
cmd.AddParam(ParameterDirection.Input, SqlDbType.VarChar, nameof(model.@CountryRegion), model.CountryRegion);
cmd.AddParam(ParameterDirection.Input, SqlDbType.VarChar, nameof(model.@Description), model.@Description);
cmd.AddParam(ParameterDirection.Input, SqlDbType.Real, nameof(model.@Latitude6x), model.@Latitude6x);
cmd.AddParam(ParameterDirection.Input, SqlDbType.Real, nameof(model.@Longitude6x), model.@Longitude6x);
int rowsAffected = cmd.ExecuteProcedure();
model.LocationID = (long)@LocationID.Value;
model.ParentLocationID = TypeConversion.ConvertType <long?>(ParentLocationID.Value);
if (model.LocationID <= 0)
{
throw new Exception("Failed for Unknown Reason");
}
}
AddLocationToQueryAutoComplete(model);
return(model);
}
public DBLocation GetDBLocationByID(long LocationID)
{
DBLocation location = _dbLocationCache.GetValueOrDefault(LocationID);
if (location == null || location.RetrievedOn > DateTime.UtcNow.AddHours(-1))
{
using (SqlCommand cmd = new SqlCommand("[dbo].[sp_Locations_GetOne]", new SqlConnection(Ctx.Config.dbUniHangoutsRead))
{
CommandType = CommandType.StoredProcedure
}) {
cmd.AddParam(ParameterDirection.Input, SqlDbType.BigInt, nameof(@LocationID), LocationID);
location = cmd.ExecuteReader_GetOne <DBLocation>();
}
if (location != null)
{
_dbLocationCache[LocationID] = location;
}
}
return(location);
}
public async Task <ApiResult <UserAccount> > GetUserInfo(string username)
{
var apiresult = new ApiResult <UserAccount>();
if (UserContext == null)
{
return(apiresult.Failure("Must be logged in."));
}
if (!UserContext.IsVerifiedLogin)
{
return(apiresult.Failure("Check your privilege. This is a privileged operation."));
}
if (String.IsNullOrWhiteSpace(username))
{
return(apiresult.Failure("AccountID or UserName must be specified."));
}
try {
DBAccount dbAccount = await WebAppContext.Factory.AccountManager.GetAccount(0, username).ConfigureAwait(false);
if (dbAccount == null)
{
return(apiresult.Failure("User doesn't exist"));
}
apiresult.Result = new UserAccount(dbAccount, null);
if (dbAccount.LocationID.UnBox() > 0)
{
using (SqlCommand cmd = DBLocation.GetSqlCommandForSP_Locations_GetOne(WebAppContext.Factory, dbAccount.LocationID.Value)) {
DBLocation dbLocation = await cmd.ExecuteReader_GetOneAsync <DBLocation>().ConfigureAwait(false);
apiresult.Result.Location = new StreetAddress(dbLocation);
}
}
return(apiresult.Success(apiresult.Result));
} catch (Exception ex) {
return(apiresult.Failure(ex));
}
}
partial void DeleteDBLocation(DBLocation instance);
partial void UpdateDBLocation(DBLocation instance);
partial void InsertDBLocation(DBLocation instance);
public ApiResult <UserAccount> Update(UserAccount input, string password)
{
ApiResult <UserAccount> apiresult = new ApiResult <UserAccount>();
if (input == null)
{
return(apiresult.Failure("Account Null."));
}
if (String.IsNullOrWhiteSpace(password))
{
return(apiresult.Failure("Invalid Password."));
}
if (String.IsNullOrWhiteSpace(input.UserName))
{
return(apiresult.Failure("Invalid UserName."));
}
if (input.Location == null)
{
return(apiresult.Failure("Location Invalid."));
}
LocationNode.CountryRegionNode oCountry = Factory.LocationManager.QueryCachedCountries(input.Location.CountryRegion).FirstOrDefault();
if (oCountry == null)
{
return(apiresult.Failure("Invalid Country"));
}
LocationNode.AdminDistrictNode oState = Factory.LocationManager.QueryCachedStates(input.Location.AdminDistrict).FirstOrDefault();
if (oState == null)
{
return(apiresult.Failure("Invalid State"));
}
if (input.Location.PostalCode.CountAlphaNumeric() < 3)
{
return(apiresult.Failure("Invalid PostalCode"));
}
if (oCountry.Abbreviation == "USA")
{
LocationNode.PostalCodeNode oZip = Factory.LocationManager.QueryCachedPostalCodes(input.Location.PostalCode).FirstOrDefault();
if (oZip == null)
{
return(apiresult.Failure("Invalid PostalCode"));
}
}
if (input.Location.Locality.CountAlphaNumeric() < 3)
{
return(apiresult.Failure("Invalid City"));
}
if (input.VerifiedContactEmail || input.VerifiedSchoolEmail)
{
return(apiresult.Failure("Attempt to submit unverified Emails logged and detected.")); //not really, but sounds scary.
}
DBAccount dbAccount = new DBAccount()
{
UserName = input.UserName,
DisplayName = input.DisplayName,
FirstName = input.FirstName,
LastName = input.LastName,
SchoolEmail = input.SchoolEmail,
ContactEmail = input.ContactEmail,
PhoneNumber = input.PhoneNumber
};
(dbAccount.PasswordHash, dbAccount.Salt) = HashUtils.HashPassword256(password);
try {
DBLocation dbLocation = Factory.LocationManager.CreateDBLocation(input.Location);
dbAccount.LocationID = dbLocation.LocationID;
Factory.AccountManager.CreateAccount(dbAccount);
apiresult.Success("Account Created!", new UserAccount(dbAccount, new StreetAddress(dbLocation)));
try {
if (!String.IsNullOrWhiteSpace(dbAccount.ContactEmail))
{
Factory.EmailManager.SendVerificationEmail(dbAccount.AccountID, dbAccount.UserName, dbAccount.ContactEmail, "http://www.unievents.site/verifyemail");
}
#pragma warning disable CS0168 // Variable is declared but never used
} catch (Exception ex) {
apiresult.bSuccess = false;
return(apiresult.AppendMessage("Invalid Contact Email: " + dbAccount.ContactEmail));
}
try {
if (!String.IsNullOrWhiteSpace(dbAccount.SchoolEmail))
{
Factory.EmailManager.SendVerificationEmail(dbAccount.AccountID, dbAccount.UserName, dbAccount.SchoolEmail, "http://www.unievents.site/verifyemail");
}
} catch (Exception ex) {
apiresult.bSuccess = false;
return(apiresult.AppendMessage("Invalid School Email: " + dbAccount.SchoolEmail));
}
#pragma warning restore CS0168 // Variable is declared but never used
} catch (Exception ex) {
return(apiresult.Failure(ex));
}
return(apiresult);
}
public CreateStructureRetval CreateStructure(Structure structure, Location location)
{
try
{
DBStructure DBStruct = new DBStructure();
structure.Sync(DBStruct);
db.DBStructures.InsertOnSubmit(DBStruct);
DBLocation DBLoc = new DBLocation();
location.Sync(DBLoc);
DBLoc.DBStructure = DBStruct;
db.DBLocations.InsertOnSubmit(DBLoc);
db.SubmitChanges();
//Return new ID's to the caller
CreateStructureRetval retval = new CreateStructureRetval(new Structure(DBStruct, false), new Location(DBLoc));
return retval;
}
finally
{
if (db != null)
db.Connection.Close();
}
return null;
}
public long[] Update(Location[] locations)
{
Dictionary<DBLocation, int> mapNewTypeToIndex = new Dictionary<DBLocation, int>(locations.Length);
//Stores the ID of each object manipulated for the return value
long[] listID = new long[locations.Length];
try
{
for (int iObj = 0; iObj < locations.Length; iObj++)
{
Location t = locations[iObj];
switch (t.DBAction)
{
case DBACTION.INSERT:
DBLocation newObj = new DBLocation();
t.Sync(newObj);
db.DBLocations.InsertOnSubmit(newObj);
mapNewTypeToIndex.Add(newObj, iObj);
break;
case DBACTION.UPDATE:
DBLocation updateRow;
try
{
updateRow = (from u in db.DBLocations where u.ID == t.ID select u).Single();
}
catch (System.ArgumentNullException e)
{
Debug.WriteLine("Could not find structuretype to update: " + t.ID.ToString());
break;
}
catch (System.InvalidOperationException e)
{
Debug.WriteLine("Could not find structuretype to update: " + t.ID.ToString());
break;
}
t.Sync(updateRow);
listID[iObj] = updateRow.ID;
// db.DBStructureTypes.(updateType);
break;
case DBACTION.DELETE:
DBLocation deleteRow;
try
{
deleteRow = (from u in db.DBLocations where u.ID == t.ID select u).Single();
}
catch (System.ArgumentNullException e)
{
Debug.WriteLine("Could not find structuretype to update: " + t.ID.ToString());
break;
}
catch (System.InvalidOperationException e)
{
Debug.WriteLine("Could not find structuretype to update: " + t.ID.ToString());
break;
}
//Remove any links that exist before calling delete
foreach (DBLocationLink link in deleteRow.IsLinkedFrom)
{
db.DBLocationLinks.DeleteOnSubmit(link);
}
foreach (DBLocationLink link in deleteRow.IsLinkedTo)
{
db.DBLocationLinks.DeleteOnSubmit(link);
}
t.Sync(deleteRow);
deleteRow.ID = t.ID;
listID[iObj] = deleteRow.ID;
db.DBLocations.DeleteOnSubmit(deleteRow);
break;
}
}
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
throw e;
}
db.Log = Console.Out;
db.SubmitChanges();
Console.Out.Flush();
//Recover the ID's for new objects
foreach (DBLocation newObj in mapNewTypeToIndex.Keys)
{
int iIndex = mapNewTypeToIndex[newObj];
listID[iIndex] = newObj.ID;
}
if (db != null)
db.Connection.Close();
return listID;
}
public Location CreateLocation(Location new_location, long[] links)
{
try
{
DBLocation db_obj = new DBLocation();
string username = ServiceModelUtil.GetUserForCall();
using (var transaction = new TransactionScope())
{
//Create the object to get the ID
new_location.Sync(db_obj);
new_location.Username = username;
db.DBLocations.InsertOnSubmit(db_obj);
db.Log = Console.Out;
db.SubmitChanges();
Console.Out.Flush();
//Build a new location link for every link in the array
List<DBLocationLink> listLinks = new List<DBLocationLink>(links.Length);
foreach (long linked_locationID in links)
{
DBLocationLink created_link = _CreateLocationLink(db_obj.ID, linked_locationID, username);
listLinks.Add(created_link);
}
db.DBLocationLinks.InsertAllOnSubmit(listLinks);
db.SubmitChanges();
transaction.Complete();
}
Location output_loc = new Location(db_obj);
output_loc.Links = links;
return output_loc;
}
finally
{
if (db != null)
db.Connection.Close();
}
}
