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