public int Insert(CrAddress addr) { var rc = -1; const string query = "INSERT INTO dbo.Addresses (Address_Line_1,City,[State/Region],Postal_Code,Domain_ID) " + "OUTPUT INSERTED.Address_ID " + "VALUES (@Address,@City,@State,@Zip,@DomainID) "; var mp = new MpDao(); using (var cn = new SqlConnection(_connectionString)) using (var cmd = new SqlCommand(query, cn)) { cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 100).Value = addr.AddressLine1; cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = addr.City; cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = addr.State; cmd.Parameters.Add("@Zip", SqlDbType.NVarChar).Value = addr.PostalCode; cmd.Parameters.Add("@DomainID", SqlDbType.NVarChar).Value = 1; // open connection, execute INSERT, close connection cn.Open(); try { rc = (int)cmd.ExecuteScalar(); } catch (Exception ex) { // write to log log.Error(ex.Message); } cn.Close(); } return(rc); }
public bool Exists(CrAddress addr) { var rc = false; var count = 0; const string query = "SELECT count(*) FROM dbo.Addresses WHERE address_line_1 = @Address AND City = @City AND [State/Region] = @State AND Postal_Code = @Zip"; using (var cn = new SqlConnection(_connectionString)) using (var cmd = new SqlCommand(query, cn)) { cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 75).Value = addr.AddressLine1; cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = addr.City; cmd.Parameters.Add("@State", SqlDbType.NVarChar, 50).Value = addr.State; cmd.Parameters.Add("@Zip", SqlDbType.NVarChar, 15).Value = addr.PostalCode; // open connection, execute INSERT, close connection cn.Open(); try { count = (int)cmd.ExecuteScalar(); } catch (Exception ex) { // write to log log.Error(ex.Message); } cn.Close(); } rc = count > 0 ? true : false; return(rc); }
public int Insert(CrProject project, bool update = false) { if (Exists(project.ProjectName, project.InitiativeId)) { if (update) { Update(project); } return(GetProjectId(project.ProjectName)); } var addrDao = new CrAddressDao(); var addr = new CrAddress { AddressLine1 = project.Address1, City = project.City, PostalCode = project.Zip, State = project.State }; var addressID = addrDao.Insert(addr); var rc = -1; const string query = "INSERT INTO dbo.cr_Projects (Project_Name,Project_Status_ID,Location_ID,Project_Type_ID," + "Organization_ID,Initiative_ID,Minimum_Volunteers,Maximum_Volunteers," + "Absolute_Maximum_Volunteers,Domain_ID," + "Check_In_Floor,Check_In_Area,Check_In_Room_Number,Note_To_Volunteers_1," + "Note_To_Volunteers_2,Project_Parking_Location,Address_ID) " + "OUTPUT INSERTED.Project_ID " + "VALUES (@ProjectName,@ProjectStatusID,@LocationID,@ProjectTypeID," + "@OrganizationID,@InitiativeID,@MinimumVolunteers,@MaximumVolunteers," + "@AbsoluteMaximumVolunteers,@DomainID," + "@CheckInFloor,@CheckInArea,@CheckInRoomNumber,@NoteToVolunteers1," + "@NoteToVolunteers2,@ProjectParkingLocation,@Address_ID) "; var mp = new MpDao(); using (var cn = new SqlConnection(_connectionString)) using (var cmd = new SqlCommand(query, cn)) { cmd.Parameters.Add("@ProjectName", SqlDbType.NVarChar, 100).Value = project.ProjectName; cmd.Parameters.Add("@ProjectStatusID", SqlDbType.Int).Value = 1; cmd.Parameters.Add("@LocationID", SqlDbType.Int).Value = mp.GetLocationId(project.LocationName); cmd.Parameters.Add("@ProjectTypeID", SqlDbType.Int).Value = mp.GetProjectTypeId(project.ProjectTypeName); cmd.Parameters.Add("@OrganizationID", SqlDbType.Int).Value = mp.GetOrganizationId(project.OrganizationName); cmd.Parameters.Add("@InitiativeID", SqlDbType.Int).Value = project.InitiativeId; cmd.Parameters.Add("@MinimumVolunteers", SqlDbType.Int).Value = project.MinVol; cmd.Parameters.Add("@MaximumVolunteers", SqlDbType.Int).Value = project.MaxVol; cmd.Parameters.Add("@AbsoluteMaximumVolunteers", SqlDbType.Int).Value = project.AbsoluteMaxVol; cmd.Parameters.Add("@DomainID", SqlDbType.Int).Value = 1; cmd.Parameters.Add("@CheckInFloor", SqlDbType.NVarChar, 50).Value = project.CheckInFloor; cmd.Parameters.Add("@CheckInArea", SqlDbType.NVarChar, 50).Value = project.CheckInArea; cmd.Parameters.Add("@CheckInRoomNumber", SqlDbType.NVarChar, 50).Value = project.CheckInRoomNumber; cmd.Parameters.Add("@NoteToVolunteers1", SqlDbType.NVarChar, 500).Value = project.Note1; cmd.Parameters.Add("@NoteToVolunteers2", SqlDbType.NVarChar, 500).Value = project.Note2; cmd.Parameters.Add("@ProjectParkingLocation", SqlDbType.NVarChar, 500).Value = project.ParkingLocation; cmd.Parameters.Add("@Address_ID", SqlDbType.Int).Value = addressID; // open connection, execute INSERT, close connection cn.Open(); try { rc = (int)cmd.ExecuteScalar(); } catch (Exception ex) { // write to log log.Error(ex.Message); } cn.Close(); } return(rc); }