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(); } }