public static List <User> GetUsers(string userType, string userName, ActiveStatusEnum active, UserOrderEnum order, OrderEnum orderDirection)
{
try
{
List <User> retVal = new List <User>();
using (SqlConnection connection = new SqlConnection(DBFunctions.ConnectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = String.Format(@"
SELECT
{0}
FROM
[user].[User]
JOIN [user].[UserType] ON [User].[UserTypeId] = [UserType].[Id]
WHERE
(@UserType IS NULL OR [user].[UserType].Name LIKE @UserType) AND
(@UserName IS NULL OR [user].[User].Name LIKE @UserName) AND
(@Active IS NULL OR [user].[User].Active = @Active)
", AllColumnSelect);
command.Parameters.Add("@UserType", SqlDbType.NVarChar);
command.Parameters.Add("@UserName", SqlDbType.NVarChar);
command.Parameters.Add("@Active", SqlDbType.Bit);
command.Parameters["@UserType"].Value = CreateLikeQueryString(userType);
command.Parameters["@UserName"].Value = CreateLikeQueryString(userName);
switch (active)
{
case ActiveStatusEnum.Active:
command.Parameters["@Active"].Value = true;
break;
case ActiveStatusEnum.Inactive:
command.Parameters["@Active"].Value = false;
break;
case ActiveStatusEnum.All:
command.Parameters["@Active"].Value = DBNull.Value;
break;
}
System.Diagnostics.Debug.WriteLine(command.CommandText);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
retVal.Add(ReadRow(reader));
}
}
}
return(retVal);
}
catch (Exception ex)
{
Logger.WriteLog(ex);
throw ErrorResponse.ErrorMessage(HttpStatusCode.BadRequest, ex);
}
}
public static List <Comment> GetCommentsByPostId(ActiveStatusEnum active, int id)
{
try
{
List <Comment> retVal = new List <Comment>();
using (SqlConnection connection = new SqlConnection(DBFunctions.ConnectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = String.Format(@"
SELECT {0}
FROM [dbo].[Comment]
WHERE ([dbo].[Comment].PostId = @Id)
AND ([dbo].[Comment].Active = @Active)
", AllColumnSelect);
command.AddParameter("@Id", SqlDbType.NVarChar, id);
command.Parameters.Add("@Active", SqlDbType.Bit);
switch (active)
{
case ActiveStatusEnum.Active:
command.Parameters["@Active"].Value = true;
break;
case ActiveStatusEnum.Inactive:
command.Parameters["@Active"].Value = false;
break;
case ActiveStatusEnum.All:
command.Parameters["@Active"].Value = DBNull.Value;
break;
}
System.Diagnostics.Debug.WriteLine(command.CommandText);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
retVal.Add(ReadRow(reader));
}
}
}
return(retVal);
}
catch (Exception ex)
{
Logger.WriteLog(ex);
return(null);
throw ErrorResponse.ErrorMessage(HttpStatusCode.BadRequest, ex);
}
}
public static List <Post> GetPostsByUserId(Guid id, ActiveStatusEnum active)
{
try
{
List <Post> retVal = new List <Post>();
using (SqlConnection connection = new SqlConnection(DBFunctions.ConnectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = String.Format(@"
SELECT {0} FROM [post].[Post]
WHERE @Active IS NULL OR [post].[Post].Active = @Active
AND [post].[Post].user_id = @UserId
ORDER BY
CONVERT(DateTime, date_created,101) DESC
", AllColumnSelect);
command.Parameters.Add("@Active", SqlDbType.Bit);
command.AddParameter("@UserId", SqlDbType.UniqueIdentifier, id);
switch (active)
{
case ActiveStatusEnum.Active:
command.Parameters["@Active"].Value = true;
break;
case ActiveStatusEnum.Inactive:
command.Parameters["@Active"].Value = false;
break;
case ActiveStatusEnum.All:
command.Parameters["@Active"].Value = DBNull.Value;
break;
}
System.Diagnostics.Debug.WriteLine(command.CommandText);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
retVal.Add(ReadRow(reader));
}
}
}
return(retVal);
}
catch (Exception ex)
{
Logger.WriteLog(ex);
throw ErrorResponse.ErrorMessage(HttpStatusCode.BadRequest, ex);
}
}
public static List <UserAddress> GetUserAddresses(ActiveStatusEnum active = ActiveStatusEnum.Active)
{
try
{
List <UserAddress> retVal = new List <UserAddress>();
using (SqlConnection connection = new SqlConnection(DBFunctions.ConnectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = String.Format(@"
SELECT
{0}
FROM
[user].[UserAddress]
WHERE
[Active] = @Active
", AllColumnSelect);
command.AddParameter("@Active", SqlDbType.Bit, active);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
retVal.Add(ReadUserAddressRow(reader));
}
}
}
return(retVal);
}
catch (Exception ex)
{
Logger.WriteLog(ex);
throw ErrorResponse.ErrorMessage(HttpStatusCode.BadRequest, ex);
}
}
public IEnumerable <Comment> GetCommentsByPostId(int id, [FromUri] ActiveStatusEnum active = ActiveStatusEnum.Active)
{
return(CommentDB.GetCommentsByPostId(active, id));
}
public IEnumerable GetAllUsers([FromUri] ActiveStatusEnum active = ActiveStatusEnum.Active)
{
return(UserDB.GetAllUsers(active));
}
public IEnumerable <ProductCategory> GetProductCategories([FromUri] int?parentId = null, [FromUri] ActiveStatusEnum active = ActiveStatusEnum.Active)
{
return(ProductCategoryDB.GetProductCategories(parentId, active));
}
public IEnumerable <Post> GetPostsByUserId(Guid id, [FromUri] ActiveStatusEnum active = ActiveStatusEnum.Active)
{
return(PostDB.GetPostsByUserId(id, active));
}
public IEnumerable <Post> GetAllPosts([FromUri] ActiveStatusEnum active = ActiveStatusEnum.Active)
{
return(PostDB.GetAllPosts(active));
}
public List <Product> GetProducts([FromUri] int?parentId = null, [FromUri] int?productCategoryId = null, [FromUri] string filter = null, [FromUri] ActiveStatusEnum active = ActiveStatusEnum.Active)
{
return(ProductDB.GetProducts(parentId, productCategoryId, filter, active));
}
public IEnumerable <User> GetUsers([FromUri] string userType = null, [FromUri] string userName = null, [FromUri] ActiveStatusEnum active = ActiveStatusEnum.Active,
[FromUri] UserOrderEnum order = UserOrderEnum.Id,
[FromUri] OrderEnum orderDirection = OrderEnum.Asc)
{
return(UserDB.GetUsers(userType, userName, active, order, orderDirection));
}
public static List <ProductCategory> GetProductCategories(int?parentId, ActiveStatusEnum active)
{
try
{
List <ProductCategory> retVal = new List <ProductCategory>();
using (SqlConnection connection = new SqlConnection(DBFunctions.ConnectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = $@"
SELECT
{ AllColumnSelect() }
FROM
[product].[ProductCategory]
WHERE
(@Active IS NULL OR [product].[ProductCategory].Active = @Active) AND
(@ParentCategoryId IS NULL OR [product].[ProductCategory].ParentCategoryId = @ParentCategoryId)
";
command.Parameters.Add("@ParentCategoryId", SqlDbType.Int);
if (parentId != null)
{
command.Parameters["@ParentCategoryId"].Value = parentId;
}
else
{
command.Parameters["@ParentCategoryId"].Value = DBNull.Value;
}
command.Parameters.Add("@Active", SqlDbType.Bit);
switch (active)
{
case ActiveStatusEnum.Active:
command.Parameters["@Active"].Value = true;
break;
case ActiveStatusEnum.Inactive:
command.Parameters["@Active"].Value = false;
break;
case ActiveStatusEnum.All:
command.Parameters["@Active"].Value = DBNull.Value;
break;
}
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
retVal.Add(ReadRow(reader));
}
}
}
return(retVal);
}
catch (Exception ex)
{
Logger.WriteLog(ex);
throw ErrorResponse.ErrorMessage(HttpStatusCode.BadRequest, ex);
}
}
public IEnumerable <Location> GetAllLocations([FromUri] ActiveStatusEnum active = ActiveStatusEnum.Active)
{
return(LocationDB.GetLocations(active));
}
public static List <Product> GetProducts(int?parentId, int?productCategoryId, string filter, ActiveStatusEnum active)
{
try
{
List <Product> retVal = new List <Product>();
using (SqlConnection connection = new SqlConnection(DBFunctions.ConnectionString))
{
string addFilter = "";
SqlCommand command = connection.CreateCommand();
command.Parameters.Add("@Filter", SqlDbType.NVarChar);
if (!String.IsNullOrEmpty(filter))
{
addFilter = @" AND ([Product].[Name] LIKE '%' + @Filter + '%' OR
[Product].[Description] LIKE '%' + @Filter + '%')";
command.Parameters["@Filter"].Value = filter;
}
else
{
command.Parameters["@Filter"].Value = DBNull.Value;
}
command.CommandText = $@"
SELECT
{ AllColumnSelect() }
FROM
[product].[Product]
WHERE
(@ParentProductId IS NULL OR [product].[Product].ParentProductId = @ParentProductId) AND
(@ProductCategoryId IS NULL OR [product].[Product].ProductCategoryId = @ProductCategoryId) AND
(@Active IS NULL OR [product].[Product].Active = @Active)
{ addFilter }
";
command.Parameters.Add("@ParentProductId", SqlDbType.Int);
command.Parameters.Add("@ProductCategoryId", SqlDbType.Int);
command.Parameters.Add("@Active", SqlDbType.Bit);
if (parentId != null)
{
command.Parameters["@ParentProductId"].Value = parentId;
}
else
{
command.Parameters["@ParentProductId"].Value = DBNull.Value;
}
if (productCategoryId != null)
{
command.Parameters["@ProductCategoryId"].Value = productCategoryId;
}
else
{
command.Parameters["@ProductCategoryId"].Value = DBNull.Value;
}
switch (active)
{
case ActiveStatusEnum.Active:
command.Parameters["@Active"].Value = true;
break;
case ActiveStatusEnum.Inactive:
command.Parameters["@Active"].Value = false;
break;
case ActiveStatusEnum.All:
command.Parameters["@Active"].Value = DBNull.Value;
break;
}
System.Diagnostics.Debug.WriteLine(command.CommandText);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
retVal.Add(ReadRow(reader));
}
}
}
return(retVal);
}
catch (Exception ex)
{
Logger.WriteLog(ex);
throw ErrorResponse.ErrorMessage(HttpStatusCode.BadRequest, ex);
}
}
