예제 #1
0
        public ProjectComponent GetComponentById(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT 
                                          pci.ProjectComponentImageUrl, pci.Id AS PCImageId, "
                                      + ProjectComponentSqlCommandText +
                                      @" FROM ProjectComponent pc
                                        LEFT JOIN ProjectComponentImages pci ON pci.ProjectComponentId = pc.Id
                                        WHERE pc.Id = @id";
                    DbUtilities.AddParameter(cmd, "@id", id);

                    ProjectComponent component = null;
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (component == null)
                        {
                            component = DbModelBuilder.BuildProjectComponentModel(reader);
                            component.ComponentImages = new List <ProjectComponentImages>();
                        }
                        if (DbUtilities.IsNotDbNull(reader, "PCImageId"))
                        {
                            component.ComponentImages.Add(DbModelBuilder.BuildComponentImageModel(reader));
                        }
                    }
                    reader.Close();
                    return(component);
                }
            }
        }
예제 #2
0
        public void AddProject(Project project)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        INSERT INTO Project (UserProfileId, 
                                                                ProjectName, 
                                                                LocationName, 
                                                                LocationAddress, 
                                                                ProjectDescription,
                                                                Budget,
                                                                ImageLocation)
                                        OUTPUT Inserted.Id
                                        Values (@UserProfileId, @ProjectName, @LocationName, @LocationAddress, @ProjectDescription, @Budget, @ImageLocation)
                                        ";
                    DbUtilities.AddParameter(cmd, "@UserProfileId", project.UserProfileId);
                    DbUtilities.AddParameter(cmd, "@ProjectName", project.ProjectName);
                    DbUtilities.AddParameter(cmd, "@LocationName", project.LocationName);
                    DbUtilities.AddParameter(cmd, "@LocationAddress", project.LocationAddress);
                    DbUtilities.AddParameter(cmd, "@ProjectDescription", project.ProjectDescription);
                    DbUtilities.AddParameter(cmd, "@Budget", project.Budget);
                    DbUtilities.AddParameter(cmd, "@ImageLocation", project.ImageLocation);

                    project.Id = (int)cmd.ExecuteScalar();
                }
            }
        }
예제 #3
0
        public void UpdateComponent(ProjectComponent component, int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        UPDATE ProjectComponent 
                                        SET Name = @Name,
                                            ComponentDescription = @ComponentDescription,
                                            ProjectId = @ProjectId,
                                            MaterialCost = @MaterialCost
                                        WHERE Id = @id
                                        ";
                    DbUtilities.AddParameter(cmd, "@Name", component.ComponentName);
                    DbUtilities.AddParameter(cmd, "@ComponentDescription", component.ComponentDescription);
                    DbUtilities.AddParameter(cmd, "@ProjectId", component.ProjectId);
                    DbUtilities.AddParameter(cmd, "@MaterialCost", component.MaterialCost);
                    DbUtilities.AddParameter(cmd, "@id", component.Id);

                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void Add(UserProfile userProfile)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"INSERT INTO UserProfile 
                                                (FirebaseUserId, FirstName, LastName, ScreenName, 
                                                 Email, ImageLocation, IsSubcontractor)
                                        OUTPUT INSERTED.ID
                                        VALUES (@FirebaseUserId, @FirstName, @LastName, @ScreenName, 
                                                @Email, @ImageLocation, @IsSubcontractor)";

                    DbUtilities.AddParameter(cmd, "@FirstName", userProfile.FirstName);
                    DbUtilities.AddParameter(cmd, "@LastName", userProfile.LastName);
                    DbUtilities.AddParameter(cmd, "@ScreenName", userProfile.ScreenName);
                    DbUtilities.AddParameter(cmd, "@Email", userProfile.Email);
                    DbUtilities.AddParameter(cmd, "@FirebaseUserId", userProfile.FirebaseUserId);
                    DbUtilities.AddParameter(cmd, "@ImageLocation", userProfile.ImageLocation);
                    DbUtilities.AddParameter(cmd, "@IsSubcontractor", userProfile.IsSubcontractor);

                    userProfile.Id = (int)cmd.ExecuteScalar();
                }
            }
        }
예제 #5
0
        public List <ProjectComponentImages> GetComponentImages(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        SELECT Id, ProjectComponentId, ProjectComponentImageUrl
                                        FROM ProjectComponentImages
                                        WHERE ProjectComponentId = @id
                                        ";
                    DbUtilities.AddParameter(cmd, "@id", id);

                    var reader = cmd.ExecuteReader();
                    var images = new List <ProjectComponentImages>();
                    while (reader.Read())
                    {
                        var anImage = new ProjectComponentImages()
                        {
                            Id = DbUtilities.GetInt(reader, "Id"),
                            ProjectComponentId       = DbUtilities.GetInt(reader, "ProjectComponentId"),
                            ProjectComponentImageUrl = DbUtilities.GetString(reader, "ProjectComponentImageUrl")
                        };

                        images.Add(anImage);
                    }

                    reader.Close();

                    return(images);
                }
            }
        }
        public void Update(UserProfile userProfile)
        {
            using (var conn = Connection)
            {
                conn.Open();

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        UPDATE UserProfile
                                        SET 
                                            FirstName = @firstName,
                                            LastName = @lastName,
                                            ScreenName = @ScreenName,
                                            Email = @email,
                                            FirebaseUserId = @firebaseUserId,
                                            ImageLocation = @imageLocation,
                                            IsSubcontractor = @IsSubcontractor
                                    
                                        WHERE Id = @Id;";
                    DbUtilities.AddParameter(cmd, "@Id", userProfile.Id);
                    DbUtilities.AddParameter(cmd, "@FirstName", userProfile.FirstName);
                    DbUtilities.AddParameter(cmd, "@LastName", userProfile.LastName);
                    DbUtilities.AddParameter(cmd, "@ScreenName", userProfile.ScreenName);
                    DbUtilities.AddParameter(cmd, "@Email", userProfile.Email);
                    DbUtilities.AddParameter(cmd, "@FirebaseUserId", userProfile.FirebaseUserId);
                    DbUtilities.AddParameter(cmd, "@ImageLocation", userProfile.ImageLocation);
                    DbUtilities.AddParameter(cmd, "@IsSubcontractor", userProfile.IsSubcontractor);
                    cmd.ExecuteNonQuery();
                }
            }
        }
예제 #7
0
        public Project CheckComponentProjectForDeleteAuth(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        SELECT p.UserProfileId 
                                        FROM ProjectComponent pc
                                        LEFT JOIN Project p on p.Id = pc.ProjectId
                                        WHERE pc.Id = @id
                                        ";
                    DbUtilities.AddParameter(cmd, "@id", id);

                    var reader = cmd.ExecuteReader();

                    var authcheck = new Project();

                    if (reader.Read())
                    {
                        authcheck.UserProfileId = DbUtilities.GetInt(reader, "UserProfileId");
                    }
                    reader.Close();
                    return(authcheck);
                }
            }
        }
        public List <SubContractor> SearchByType(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        SELECT sc.Id AS SubId, sc.SubContractorBusinessName, sc.SubContractorImageUrl
                                        FROM SubContractor sc
                                        LEFT JOIN SubContractorJobType jt ON jt.SubContractorId = sc.Id
                                        LEFT JOIN SubContractorType st ON jt.SubContractorTypeId = st.Id
                                        WHERE st.Id = @id
                                        ";
                    DbUtilities.AddParameter(cmd, "@id", id);

                    var reader     = cmd.ExecuteReader();
                    var subsOfType = new List <SubContractor>();

                    while (reader.Read())
                    {
                        var sub = new SubContractor()
                        {
                            Id = DbUtilities.GetInt(reader, "SubId"),
                            SubcontractorBusinessName  = DbUtilities.GetString(reader, "SubContractorBusinessName"),
                            SubContractorImageLocation = DbUtilities.GetString(reader, "SubContractorImageUrl")
                        };
                        subsOfType.Add(sub);
                    }
                    reader.Close();

                    return(subsOfType);
                }
            }
        }
예제 #9
0
        public void UpdateProject(Project project)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        UPDATE Project
                                        SET ProjectName = @ProjectName,
                                            LocationName = @LocationName,
                                            LocationAddress = @LocationAddress,
                                            ProjectDescription = @ProjectDescription,
                                            Budget = @Budget,
                                            ImageLocation = @ImageLocation
                                        WHERE Id = @id
                                        ";
                    DbUtilities.AddParameter(cmd, "@ProjectName", project.ProjectName);
                    DbUtilities.AddParameter(cmd, "@LocationName", project.LocationName);
                    DbUtilities.AddParameter(cmd, "@LocationAddress", project.LocationAddress);
                    DbUtilities.AddParameter(cmd, "@ProjectDescription", project.ProjectDescription);
                    DbUtilities.AddParameter(cmd, "@Budget", project.Budget);
                    DbUtilities.AddParameter(cmd, "@ImageLocation", project.ImageLocation);
                    DbUtilities.AddParameter(cmd, "@id", project.Id);

                    cmd.ExecuteNonQuery();
                }
            }
        }
        public SubContractor GetById(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        SELECT Id as SubId, 
                                                UserProfileId, 
                                                SubContractorBusinessName, 
                                                SubContractorImageUrl
                                        FROM SubContractor
                                        WHERE Id = @id
                                        ";
                    DbUtilities.AddParameter(cmd, "@id", id);

                    var           reader        = cmd.ExecuteReader();
                    SubContractor subContractor = null;

                    if (reader.Read())
                    {
                        subContractor = DbModelBuilder.BuildSubContractorModel(reader);
                    }

                    reader.Close();

                    return(subContractor);
                }
            }
        }
예제 #11
0
        public ProjectComponent GetSingleComponent(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT "
                                      + ProjectComponentSqlCommandText +
                                      @"FROM ProjectComponent pc
                                        WHERE pc.Id = @Id";
                    DbUtilities.AddParameter(cmd, "@Id", id);

                    var reader = cmd.ExecuteReader();

                    ProjectComponent component = null;
                    if (reader.Read())
                    {
                        component = DbModelBuilder.BuildProjectComponentModel(reader);
                    }

                    reader.Close();
                    return(component);
                }
            }
        }
예제 #12
0
        public List <SubContractorBid> GetBidBySubcontractor(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        SELECT Id AS BidId, ProjectComponentId, SubContractorId, UserProfileId, Fee, SubAccepted, OwnerComment
                                        FROM SubContractorBid
                                        WHERE SubContractorId = @Id
                                        ";
                    DbUtilities.AddParameter(cmd, "@Id", id);

                    var reader = cmd.ExecuteReader();

                    var bids = new List <SubContractorBid>();

                    while (reader.Read())
                    {
                        var aBid = DbModelBuilder.BuildSubContractorBidModel(reader);
                        bids.Add(aBid);
                    }

                    reader.Close();

                    return(bids);
                }
            }
        }
예제 #13
0
        public void DeleteProject(Project project)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    string hasComponentsToDelete = "";
                    if (project.Components.Count > 0)
                    {
                        var    componentBeingDeleted = project.Components;
                        string recurrsiveDeletes     = "";
                        for (int i = 0; i < componentBeingDeleted.Count; i++)
                        {
                            int comId = componentBeingDeleted[i].Id;

                            //Add the Sql '@' to the 'In' chain for this index of the Component List
                            string deleteParam = "@deleteId" + i;
                            recurrsiveDeletes += deleteParam;
                            //Add a comma between the statement if its not the last one in the list
                            if (i != componentBeingDeleted.Count - 1)
                            {
                                recurrsiveDeletes += ", ";
                            }
                            //Tie this Id value to the command text as it is being built
                            DbUtilities.AddParameter(cmd, deleteParam, comId);
                        }
                        hasComponentsToDelete = @"
                                        DELETE FROM SubContractorBid
                                        WHERE ProjectComponentId IN (  "
                                                + recurrsiveDeletes + " ) " +
                                                @"DELETE FROM ProjectComponentImages
                                        WHERE ProjectComponentId IN ( "
                                                + recurrsiveDeletes + " ) ";
                    }

                    cmd.CommandText = hasComponentsToDelete +
                                      @"DELETE FROM ProjectComponent
                                        WHERE ProjectId = @Id
                        
                                        DELETE FROM Project
                                        WHERE Id = @Id
                                        ";
                    DbUtilities.AddParameter(cmd, "@Id", project.Id);

                    cmd.ExecuteNonQuery();
                }
            }
        }
예제 #14
0
        public void AddComponentImage(ProjectComponentImages image)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        INSERT INTO ProjectComponentImages ( ProjectComponentId, ProjectComponentImageUrl)
                                        OUTPUT Inserted.Id
                                        Values (@ProjectComponentId, @ProjectComponentImageUrl)
                                        ";
                    DbUtilities.AddParameter(cmd, "@ProjectComponentId", image.ProjectComponentId);
                    DbUtilities.AddParameter(cmd, "@ProjectComponentImageUrl", image.ProjectComponentImageUrl);

                    image.Id = (int)cmd.ExecuteScalar();
                }
            }
        }
예제 #15
0
        public void AddCompleteDateToComponent(ProjectComponent component)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        UPDATE ProjectComponent 
                                        SET DateComplete = @DateComplete
                                        WHERE Id = @id
                                        ";
                    DbUtilities.AddParameter(cmd, "@DateComplete", component.DateComplete);
                    DbUtilities.AddParameter(cmd, "@id", component.Id);

                    cmd.ExecuteNonQuery();
                }
            }
        }
예제 #16
0
        public void AcceptBid(SubContractorBid bid)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        UPDATE SubContractorBid
                                        SET SubAccepted = @SubAccepted
                                        WHERE Id = @id
                                        ";
                    DbUtilities.AddParameter(cmd, "@SubAccepted", bid.SubAccepted);
                    DbUtilities.AddParameter(cmd, "@id", bid.Id);

                    cmd.ExecuteNonQuery();
                }
            }
        }
        public List <SubContractorJob> GetSubContractorJobs(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT 
                                          pci.ProjectComponentImageUrl, pci.Id AS PCImageId, p.LocationName, p.LocationAddress,
                                          p.ProjectName, up.ScreenName, up.ImageLocation, scb.Fee, "
                                      + ProjectComponentSqlCommandText +
                                      @" FROM ProjectComponent pc
                                        LEFT JOIN ProjectComponentImages pci ON pci.ProjectComponentId = pc.Id
                                        LEFT JOIN Project p ON p.Id = pc.ProjectId
                                        LEFT JOIN UserProfile up on p.UserProfileId = up.Id
                                        LEFT JOIN SubContractorBid scb on scb.ProjectComponentId = pc.Id
                                        WHERE pc.SubContractorId = @id AND scb.SubContractorId = @id";
                    DbUtilities.AddParameter(cmd, "@id", id);

                    var jobs   = new List <SubContractorJob>();
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var componentId = DbUtilities.GetInt(reader, "ProjectComponentId");
                        var existingJob = jobs.FirstOrDefault(j => j.Id == componentId);
                        if (existingJob == null)
                        {
                            existingJob = DbModelBuilder.BuildSubcontractorJobModel(reader);
                            existingJob.ComponentImages = new List <ProjectComponentImages>();
                            jobs.Add(existingJob);
                        }
                        if (DbUtilities.IsNotDbNull(reader, "PCImageId"))
                        {
                            existingJob.ComponentImages.Add(DbModelBuilder.BuildComponentImageModel(reader));
                        }
                    }
                    reader.Close();
                    return(jobs);
                }
            }
        }
        public UserProfile GetByFirebaseId(string firebaseId)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        SELECT sc.SubContractorBusinessName, sc.SubContractorImageUrl, sc.Id AS SubContractorId, "
                                      + UserProfileSqlCommandText +
                                      @"FROM UserProfile up
                                        LEFT JOIN SubContractor sc ON sc.UserProfileId = up.Id
                                        WHERE up.FirebaseUserId = @FirebaseUserId
                                        ";
                    DbUtilities.AddParameter(cmd, "@FirebaseUserId", firebaseId);

                    var         reader = cmd.ExecuteReader();
                    UserProfile aUser  = null;
                    if (reader.Read())
                    {
                        aUser = DbModelBuilder.BuildUserProfileModel(reader);
                        //If the user is a registered Subcontractor, get that info.
                        if (aUser.IsSubcontractor)
                        {
                            aUser.contractor    = new SubContractor();
                            aUser.contractor.Id = DbUtilities.GetInt(reader, "SubContractorId");
                            if (DbUtilities.IsNotDbNull(reader, "SubContractorBusinessName"))
                            {
                                aUser.contractor.SubcontractorBusinessName = DbUtilities.GetString(reader, "SubContractorBusinessName");
                            }
                            if (DbUtilities.IsNotDbNull(reader, "SubContractorImageUrl"))
                            {
                                aUser.contractor.SubContractorImageLocation = DbUtilities.GetString(reader, "SubContractorImageUrl");
                            }
                        }
                    }
                    reader.Close();
                    return(aUser);
                }
            }
        }
        public void AddSubcontractor(SubContractor subContractor)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        INSERT INTO Subcontractor 
                                            ( UserProfileId, SubContractorBusinessName, SubContractorImageUrl )
                                        OUTPUT Inserted.Id
                                            Values ( @UserProfileId, @SubContractorBusinessName, @SubContractorImageUrl )
                                        ";
                    DbUtilities.AddParameter(cmd, "@UserProfileId", subContractor.UserProfileId);
                    DbUtilities.AddParameter(cmd, "@SubContractorBusinessName", subContractor.SubcontractorBusinessName);
                    DbUtilities.AddParameter(cmd, "@SubContractorImageUrl", subContractor.SubContractorImageLocation);

                    subContractor.Id = (int)cmd.ExecuteScalar();
                }
            }
        }
예제 #20
0
        //This method nests the list of components in the project
        public List <Project> GetOwnerProjects(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "Select "
                                      + ProjectSqlCommandText + ", "
                                      + ProjectComponentSqlCommandText +
                                      @" FROM Project p
                                        LEFT JOIN ProjectComponent pc ON pc.ProjectId = p.Id
                                        WHERE UserProfileId = @Id";

                    DbUtilities.AddParameter(cmd, "@Id", id);

                    var reader = cmd.ExecuteReader();

                    var projects = new List <Project>();

                    while (reader.Read())
                    {
                        var projectId       = DbUtilities.GetInt(reader, "ProjectId");
                        var existingProject = projects.FirstOrDefault(p => p.Id == projectId);
                        if (existingProject == null)
                        {
                            existingProject = DbModelBuilder.BuildProjectModel(reader);
                            projects.Add(existingProject);
                        }
                        if (DbUtilities.IsNotDbNull(reader, "ProjectComponentId"))
                        {
                            existingProject.Components.Add(DbModelBuilder.BuildProjectComponentModel(reader));
                        }
                    }

                    reader.Close();
                    return(projects);
                }
            }
        }
예제 #21
0
        public void StartBid(SubContractorBid bid)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        INSERT INTO SubContractorBid (ProjectComponentId, SubContractorId, UserProfileId, Fee, OwnerComment)
                                        OUTPUT Inserted.Id
                                        Values ( @ProjectComponentId, @SubContractorId, @UserProfileId, @Fee, @OwnerComment)
                                        ";
                    DbUtilities.AddParameter(cmd, "@ProjectComponentId", bid.ProjectComponentId);
                    DbUtilities.AddParameter(cmd, "@SubContractorId", bid.SubContractorId);
                    DbUtilities.AddParameter(cmd, "@UserProfileId", bid.UserProfileId);
                    DbUtilities.AddParameter(cmd, "@Fee", bid.Fee);
                    DbUtilities.AddParameter(cmd, "@OwnerComment", bid.OwnerComment);

                    bid.Id = (int)cmd.ExecuteScalar();
                }
            }
        }
예제 #22
0
        //This method nests the list of components in the project
        public Project GetSingleProjectById(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "Select "
                                      + ProjectSqlCommandText + ", "
                                      + ProjectComponentSqlCommandText +
                                      @" FROM Project p
                                        LEFT JOIN ProjectComponent pc ON pc.ProjectId = p.Id
                                        WHERE p.Id = @Id";

                    DbUtilities.AddParameter(cmd, "@Id", id);

                    var reader = cmd.ExecuteReader();

                    Project aProject = null;

                    while (reader.Read())
                    {
                        if (aProject == null)
                        {
                            aProject = DbModelBuilder.BuildProjectModel(reader);
                        }

                        if (DbUtilities.IsNotDbNull(reader, "ProjectComponentId"))
                        {
                            aProject.Components.Add(DbModelBuilder.BuildProjectComponentModel(reader));
                        }
                    }

                    reader.Close();
                    return(aProject);
                }
            }
        }
예제 #23
0
        public void DeleteComponent(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        DELETE FROM ProjectComponentImages
                                        WHERE ProjectComponentId = @id

                                        DELETE FROM SubContractorBid
                                        WHERE ProjectComponentId = @id

                                        DELETE FROM ProjectComponent
                                        WHERE Id = @Id
                                        ";
                    DbUtilities.AddParameter(cmd, "@Id", id);

                    cmd.ExecuteNonQuery();
                }
            }
        }
예제 #24
0
        public List <ProjectComponent> GetAllByProject(int id)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT 
                                          pci.ProjectComponentImageUrl, pci.Id AS PCImageId, "
                                      + ProjectComponentSqlCommandText +
                                      @" FROM ProjectComponent pc
                                        LEFT JOIN ProjectComponentImages pci ON pci.ProjectComponentId = pc.Id
                                        WHERE pc.ProjectId = @id";
                    DbUtilities.AddParameter(cmd, "@id", id);

                    var components = new List <ProjectComponent>();
                    var reader     = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var componentId       = DbUtilities.GetInt(reader, "ProjectComponentId");
                        var existingComponent = components.FirstOrDefault(p => p.Id == componentId);
                        if (existingComponent == null)
                        {
                            existingComponent = DbModelBuilder.BuildProjectComponentModel(reader);
                            existingComponent.ComponentImages = new List <ProjectComponentImages>();
                        }
                        if (DbUtilities.IsNotDbNull(reader, "PCImageId"))
                        {
                            existingComponent.ComponentImages.Add(DbModelBuilder.BuildComponentImageModel(reader));
                        }
                        components.Add(existingComponent);
                    }
                    reader.Close();
                    return(components);
                }
            }
        }
 public UserProfile GetById(int id)
 {
     using (var conn = Connection)
     {
         conn.Open();
         using (var cmd = conn.CreateCommand())
         {
             cmd.CommandText = "Select"
                               + UserProfileSqlCommandText +
                               @"FROM UserProfile up
                                 WHERE up.Id = @Id
                                 ";
             DbUtilities.AddParameter(cmd, "@Id", id);
             var         reader = cmd.ExecuteReader();
             UserProfile aUser  = null;
             if (reader.Read())
             {
                 aUser = DbModelBuilder.BuildUserProfileModel(reader);
             }
             reader.Close();
             return(aUser);
         }
     }
 }
예제 #26
0
        public void AddComponent(ProjectComponent component)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        INSERT INTO ProjectComponent (Name, 
                                                                        ComponentDescription, 
                                                                        ProjectId, 
                                                                        MaterialCost)    
                                        OUTPUT Inserted.Id
                                        Values (@Name, @ComponentDescription, @ProjectId, @MaterialCost)
                                        ";
                    DbUtilities.AddParameter(cmd, "@Name", component.ComponentName);
                    DbUtilities.AddParameter(cmd, "@ComponentDescription", component.ComponentDescription);
                    DbUtilities.AddParameter(cmd, "@ProjectId", component.ProjectId);
                    DbUtilities.AddParameter(cmd, "@MaterialCost", component.MaterialCost);

                    component.Id = (int)cmd.ExecuteScalar();
                }
            }
        }
        public List <SubContractor> SearchByMultipleTypes(string find)
        {
            using (var conn = Connection)
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    //Make an iterable list of the the requestList string
                    string[] searchParams = find.Split(",");
                    //Build the text of type @ parameters for the Sql 'IN' list using the iterable searchParams string
                    string findlist = "";
                    for (int i = 0; i < searchParams.Length; i++)
                    {
                        int aSearch;
                        //Catch any attempt to pass in a non-int
                        try
                        {
                            int.TryParse(searchParams[i], out aSearch);
                        }
                        catch
                        {
                            return(null);
                        }
                        //Add the Sql '@' to the 'In' chain for this index of the parameter string
                        string findType = "@findType" + i;
                        findlist += findType;
                        //Add a comma between the statement if its not the last one in the list
                        if (i != searchParams.Length - 1)
                        {
                            findlist += ", ";
                        }

                        //Tie this Id value to the command text as it is being built
                        DbUtilities.AddParameter(cmd, findType, aSearch);
                    }

                    cmd.CommandText = @"
                                        SELECT sc.Id AS SubId, sc.SubContractorBusinessName, sc.SubContractorImageUrl
                                        FROM SubContractor sc
                                        LEFT JOIN SubContractorJobType jt ON jt.SubContractorId = sc.Id
                                        LEFT JOIN SubContractorType st ON jt.SubContractorTypeId = st.Id
                                        WHERE st.Id IN ( "
                                      + findlist + " )";



                    var reader     = cmd.ExecuteReader();
                    var subsOfType = new List <SubContractor>();

                    while (reader.Read())
                    {
                        var sub = new SubContractor()
                        {
                            Id = DbUtilities.GetInt(reader, "SubId"),
                            SubcontractorBusinessName  = DbUtilities.GetString(reader, "SubContractorBusinessName"),
                            SubContractorImageLocation = DbUtilities.GetString(reader, "SubContractorImageUrl")
                        };
                        subsOfType.Add(sub);
                    }
                    reader.Close();

                    return(subsOfType);
                }
            }
        }