public bool CreateUpdateCategory(Category category)
{
bool IsSaved = false;
SqlConnection con = new IDDbContext().GetConnection();
List <Category> objCategoryList = new List <Category>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
new SqlParameter("@categoryId", category.CategoryId),
new SqlParameter("@categoryName", category.CategoryName),
new SqlParameter("@parentCategory", category.ParentCategory),
new SqlParameter("@order", category.CategoryOrder),
new SqlParameter("@createdBy", category.CreatedBy),
new SqlParameter("@isActive", category.IsActive)
};
Params[0].Direction = ParameterDirection.Output;
int RowAffected = SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "USP_InsertUpdateSurveyCategory", Params);
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(false);
}
if (RowAffected > 0)
{
IsSaved = true;
}
return(IsSaved);
}
public bool DeleteOption(long OptionId)
{
bool IsDeleted = false;
SqlConnection con = new IDDbContext().GetConnection();
List <Option> objOptionList = new List <Option>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
new SqlParameter("@optionId", OptionId)
};
Params[0].Direction = ParameterDirection.Output;
int RowAffected = SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "USP_DeleteOption", Params);
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(false);
}
if (RowAffected > 0)
{
IsDeleted = true;
}
return(IsDeleted);
}
public bool SetCategoryActiveStatus(long CategoryId, bool ActiveStatus)
{
bool IsUpdated = false;
SqlConnection con = new IDDbContext().GetConnection();
List <Category> objCategoryList = new List <Category>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
new SqlParameter("@categoryId", CategoryId),
new SqlParameter("@activeStatus", ActiveStatus)
};
Params[0].Direction = ParameterDirection.Output;
int RowAffected = SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "USP_SetCategoryActiveStatus", Params);
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(false);
}
if (RowAffected > 0)
{
IsUpdated = true;
}
return(IsUpdated);
}
public bool CreateUpdateUser(User user)
{
bool IsSaved = false;
SqlConnection con = new IDDbContext().GetConnection();
List <User> objUserList = new List <User>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
new SqlParameter("@UserId", user.UserId),
new SqlParameter("@FirstName", user.FirstName),
new SqlParameter("@LastName", user.LastName),
new SqlParameter("@UserEmail", user.UserEmail),
new SqlParameter("@UserPassword", user.UserPassword),
new SqlParameter("@createdBy", "Qayyum")
};
Params[0].Direction = ParameterDirection.Output;
int RowAffected = SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "USP_InsertUpdateUser", Params);
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(false);
}
if (RowAffected > 0)
{
IsSaved = true;
}
return(IsSaved);
}
public bool CreateUpdateManagement(Management management)
{
bool IsSaved = false;
SqlConnection con = new IDDbContext().GetConnection();
List <Management> objManagementList = new List <Management>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
new SqlParameter("@Id", management.ManagementId),
new SqlParameter("@Comment", management.Comment),
new SqlParameter("@ManageCategory", management.ManagementCategoryId),
new SqlParameter("@RupeeOrMinutes", management.RupeeOrMinute)
};
Params[0].Direction = ParameterDirection.Output;
int RowAffected = SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "USP_InsertUpdateManagement", Params);
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(false);
}
if (RowAffected > 0)
{
IsSaved = true;
}
return(IsSaved);
}
public bool IsValidUser(string UserName, string Password)
{
bool IsValidUser = false;
int NumberOfUser = 0;
SqlConnection con = new IDDbContext().GetConnection();
List <Category> objCategoryList = new List <Category>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
new SqlParameter("@UserName", UserName),
new SqlParameter("@Password", Password)
};
Params[0].Direction = ParameterDirection.Output;
SqlDataReader rdCategories = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "USP_IsValidUser", Params);
while (rdCategories.Read())
{
NumberOfUser = Convert.ToInt32(rdCategories["NumOfUser"].ToString());
}
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(false);
}
if (NumberOfUser > 0)
{
IsValidUser = true;
}
return(IsValidUser);
}
public long GetLastGeneratedSurveyId()
{
SqlConnection con = new IDDbContext().GetConnection();
long SurveyId = 0;
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int)
};
Params[0].Direction = ParameterDirection.Output;
SqlDataReader rdCategories = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "USP_GetLastGeneratedSurveyId", Params);
while (rdCategories.Read())
{
SurveyId = Convert.ToInt64(rdCategories["LastSurveyId"].ToString());
}
rdCategories.Close();
con.Close();
if (ReturnValue < 0)
{
return(-1);
}
return(SurveyId);
}
public List <Survey> GetAllSurvey(long?SurveyId, int?CategoryId, Boolean?IsDeleted, Boolean?IsActive)
{
SqlConnection con = new IDDbContext().GetConnection();
List <Survey> objSurveyList = new List <Survey>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
new SqlParameter("@SurveyId", SurveyId),
new SqlParameter("@surveyCategoryId", CategoryId),
new SqlParameter("@isActive", IsActive)
};
Params[0].Direction = ParameterDirection.Output;
SqlDataReader rdCategories = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "USP_GetSurveyInformation", Params);
while (rdCategories.Read())
{
objSurveyList.Add(
new Survey()
{
SurveyId = Convert.ToInt16(rdCategories["SurveyId"].ToString()),
CategoryId = Convert.ToInt16(rdCategories["CategoryId"].ToString()),
CategoryName = rdCategories["CategoryName"].ToString(),
SurveyQuestion = rdCategories["SurveyQuestion"].ToString(),
SurveyDescription = rdCategories["SurveyDescription"].ToString(),
PicturePath = rdCategories["PicturePath"].ToString(),
Rating = Convert.ToDecimal(rdCategories["SurveyRating"].ToString()),
RatingCount = Convert.ToInt32(rdCategories["SurveyRatingCount"].ToString()),
CreatedDate = Convert.ToDateTime(rdCategories["SurveyCreatedDate"].ToString()),
CloseDate = rdCategories["SurveyCloseDate"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(rdCategories["SurveyCloseDate"].ToString()) : null,
ExpireDate = rdCategories["SurveyExpireDate"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(rdCategories["SurveyExpireDate"].ToString()) : null,
IsActive = Convert.ToBoolean(rdCategories["SurveyIsActive"].ToString()),
StartDate = rdCategories["SurveyStartDate"] != DBNull.Value ? (DateTime?)Convert.ToDateTime(rdCategories["SurveyStartDate"].ToString()) : null,
CreatedBy = rdCategories["SurveyCreatedBy"].ToString(),
});
}
rdCategories.Close();
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(null);
}
return(objSurveyList);
}
public List <Category> GetAllCategory(int?CategoryId, bool?IsActive)
{
SqlConnection con = new IDDbContext().GetConnection();
List <Category> objCategoryList = new List <Category>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
CategoryId != null? new SqlParameter("@categoryId", CategoryId) : new SqlParameter("@categoryId",DBNull.Value),
IsActive != null? new SqlParameter("@isActive", IsActive) : new SqlParameter("@isActive", DBNull.Value)
};
Params[0].Direction = ParameterDirection.Output;
SqlDataReader rdCategories = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "USP_GetSurveyCategory", Params);
while (rdCategories.Read())
{
objCategoryList.Add(
new Category()
{
CategoryId = Convert.ToInt16(rdCategories["categoryId"].ToString()),
CategoryName = rdCategories["categoryName"].ToString(),
ParentCategory = rdCategories["parentCategory"] != DBNull.Value ? Convert.ToInt16(rdCategories["parentCategory"].ToString()) : (short)0,
CategoryOrder = Convert.ToInt16(rdCategories["order"].ToString()),
IsActive = Convert.ToBoolean(rdCategories["isActive"].ToString())
});
}
rdCategories.Close();
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(null);
}
return(objCategoryList);
}
public List <User> GetAllUser(int?userId)
{
SqlConnection con = new IDDbContext().GetConnection();
List <User> objUserList = new List <User>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
userId != null? new SqlParameter("@UserId", userId) : new SqlParameter("@UserId", DBNull.Value)
};
Params[0].Direction = ParameterDirection.Output;
SqlDataReader rdCategories = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "USP_GetUser", Params);
while (rdCategories.Read())
{
objUserList.Add(
new User()
{
UserId = Convert.ToInt16(rdCategories["UserId"].ToString()),
FirstName = rdCategories["FirstName"].ToString(),
LastName = rdCategories["LastName"].ToString(),
UserEmail = rdCategories["UserEmail"].ToString(),
UserPassword = rdCategories["UserPassword"].ToString()
});
}
rdCategories.Close();
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(null);
}
return(objUserList);
}
public List <Option> GetAllOption(long?OptionId, bool?IsActive)
{
SqlConnection con = new IDDbContext().GetConnection();
List <Option> objOptionList = new List <Option>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
OptionId != null? new SqlParameter("@optionId", OptionId) : new SqlParameter("@optionId",DBNull.Value),
IsActive != null? new SqlParameter("@isActive", IsActive) : new SqlParameter("@isActive",DBNull.Value)
};
Params[0].Direction = ParameterDirection.Output;
SqlDataReader rdCategories = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "USP_GetOptions", Params);
while (rdCategories.Read())
{
objOptionList.Add(
new Option()
{
OptionId = Convert.ToInt64(rdCategories["OptionId"].ToString()),
OptionName = rdCategories["OptionName"].ToString(),
IsActive = Convert.ToBoolean(rdCategories["isActive"].ToString())
});
}
rdCategories.Close();
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(null);
}
return(objOptionList);
}
public List <Management> GetAllManagement(int?ManagementId)
{
SqlConnection con = new IDDbContext().GetConnection();
List <Management> objManagementList = new List <Management>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
ManagementId != null ? new SqlParameter("@Id", ManagementId) : new SqlParameter("@Id", DBNull.Value)
};
Params[0].Direction = ParameterDirection.Output;
SqlDataReader rdManagement = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "USP_GetSurveyManagement", Params);
while (rdManagement.Read())
{
objManagementList.Add(
new Management()
{
ManagementId = Convert.ToInt64(rdManagement["Id"].ToString()),
Comment = rdManagement["Comment"].ToString(),
ManagementCategoryId = Convert.ToInt16(rdManagement["ManageCategory"].ToString()),
RupeeOrMinute = Convert.ToInt16(rdManagement["RupeeOrMinutes"].ToString()),
});
}
rdManagement.Close();
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(null);
}
return(objManagementList);
}
public List <SurveyOption> GetAllSurveyOption(long SurveyId)
{
SqlConnection con = new IDDbContext().GetConnection();
List <SurveyOption> objOptionList = new List <SurveyOption>();
int ReturnValue = 1;
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
new SqlParameter("@surveyId", SurveyId),
};
Params[0].Direction = ParameterDirection.Output;
SqlDataReader rdCategories = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "USP_GetSurveyOptions", Params);
while (rdCategories.Read())
{
objOptionList.Add(
new SurveyOption()
{
Id = Convert.ToInt64(rdCategories["Id"].ToString()),
OptionId = Convert.ToInt64(rdCategories["OptionId"].ToString()),
OptionName = rdCategories["OptionName"].ToString(),
SurveyOptionCount = rdCategories["SurveyOptionCount"] != DBNull.Value ? Convert.ToInt64(rdCategories["SurveyOptionCount"].ToString()) :0
});
}
rdCategories.Close();
//ReturnValue = Convert.ToInt16(rdAdvertisement.Parameters["@EmpName"].Value.Value.ToString()); // Get Return value to check errors in DB
con.Close();
if (ReturnValue < 0)
{
return(null);
}
return(objOptionList);
}
public Boolean CreateUpdateSurvey(Survey Survey)
{
SqlConnection con = new IDDbContext().GetConnection();
Boolean IsCreated = false;
int RowAffected = 0;
List <Survey> objSurveyList = new List <Survey>();
SqlParameter[] Params =
{
new SqlParameter("@opReturnValue", SqlDbType.Int),
new SqlParameter("@surveyId", Survey.SurveyId),
new SqlParameter("@categoryId", Survey.CategoryId),
new SqlParameter("@surveyQuestion", Survey.SurveyQuestion),
new SqlParameter("@surveyDescription", Survey.SurveyDescription),
new SqlParameter("@picturePath", Survey.PicturePath != null ? Survey.PicturePath :""),
new SqlParameter("@surveyStartDate", Survey.StartDate),
new SqlParameter("@surveyCloseDate", Survey.CloseDate),
new SqlParameter("@surveyExpireDate", Survey.ExpireDate),
new SqlParameter("@surveyIsActive", Survey.IsActive),
new SqlParameter("@createdBy", Survey.CreatedBy)
};
Params[0].Direction = ParameterDirection.Output;
using (SqlCommand objSqlCommand = new SqlCommand())
{
RowAffected = SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "USP_InsertUpdateSurvey", Params);
// detach the SqlParameters from the command object, so they can be used again.
objSqlCommand.Parameters.Clear();
}
con.Close();
if (RowAffected > 0)
{
IsCreated = true;
}
return(IsCreated);
}