public bool PutCollection(int collectionId, CollectionModel data)
{
try
{
string xml = Helper.XmlSerializer <CollectionModel>(data);
using (var context = new SQLContext())
{
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@collectionId", SqlDbType.Int);
param[0].Value = collectionId;
param[1] = new SqlParameter("@data", SqlDbType.Xml);
param[1].Value = xml;
param[2] = new SqlParameter("@userId", SqlDbType.Int);
param[2].Value = 0;
context.Database.ExecuteSqlCommand(
"[dbo].[s_put_collection] @collectionId, @data, @userId",
param);
return(true);
}
}
catch (Exception ex)
{
//_exception.Set(ExceptionType.CATCH, ex);
return(false);
}
}
public ExceptionModel PostUser(UserModel data)
{
ExceptionModel oResult = new ExceptionModel();
try
{
string xml = Helper.XmlSerializer <UserModel>(data);
using (var context = new SQLContext())
{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@data", SqlDbType.Xml);
param[0].Value = xml;
var reader = context.Database.SqlQuery <ExceptionModel>(
"[dbo].[s_post_user] @data",
param);
oResult = reader.FirstOrDefault();
}
}
catch (Exception ex)
{
oResult = oException.Set(ex);
}
return(oResult);
}
public ExceptionModel PutUser(int userId, UserModel data)
{
ExceptionModel oResult = new ExceptionModel();
try
{
string xml = Helper.XmlSerializer <UserModel>(data);
using (var context = new SQLContext())
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@userId", SqlDbType.Int);
param[0].Value = userId;
param[1] = new SqlParameter("@data", SqlDbType.Xml);
param[1].Value = xml;
context.Database.ExecuteSqlCommand(
"[dbo].[s_put_users] @userId, @data",
param);
}
}
catch (Exception ex)
{
oResult = oException.Set(ex);
}
return(oResult);
}
public ICollection <LookupModel> GetLookupsSetting(string category)
{
using (var conn = new SQLContext().Database.Connection)
{
using (var reader = conn.QueryMultiple("dbo.s_get_lookups_setting", new { category = category }, commandType: System.Data.CommandType.StoredProcedure))
{
var result = reader.Read <LookupModel>().ToList();
return(result);
}
}
}
public ICollection <LookupModel> GetLookups(string type, string description, string category, int?status = null)
{
using (var conn = new SQLContext().Database.Connection)
{
using (var reader = conn.QueryMultiple("dbo.s_get_lookups", new { type = type, description = description, category = category, status = status }, commandType: System.Data.CommandType.StoredProcedure))
{
var result = reader.Read <LookupModel>().ToList();
return(result);
}
}
}
public ICollection <UserModel> GetUsers(string userEmail, string userFullName, int?status)
{
using (var conn = new SQLContext().Database.Connection)
{
using (var reader = conn.QueryMultiple("dbo.s_get_users", new { userEmail = userEmail, userFullName = userFullName, status = status }, commandType: System.Data.CommandType.StoredProcedure))
{
var result = reader.Read <UserModel>().ToList();
return(result);
}
}
}
public ICollection <Select2Model> GetLookupsCategory(int?status)
{
using (var conn = new SQLContext().Database.Connection)
{
using (var reader = conn.QueryMultiple("dbo.s_get_lookup_category", new { status = status },
commandType: CommandType.StoredProcedure))
{
var result = reader.Read <Select2Model>().ToList();
return(result);
}
}
}
public bool DeleteLookup(List <int?> id, int?userId)
{
using (var conn = new SQLContext().Database.Connection)
{
string ids = string.Join(",", id);
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@lookupid", SqlDbType.Int);
param[0].Value = ids;
param[1] = new SqlParameter("@userId", SqlDbType.Int);
param[1].Value = 0;
var context = new SQLContext().Database.ExecuteSqlCommand("s_delete_lookups @lookupId, @userId", param);
return(true);
}
}
public bool DeleteTestimonial(List <int?> ids)
{
using (var conn = new SQLContext().Database.Connection)
{
foreach (var id in ids)
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@testimonialid", SqlDbType.Int);
param[0].Value = id.Value;
param[1] = new SqlParameter("@userId", SqlDbType.Int);
param[1].Value = 0;
var context = new SQLContext().Database.ExecuteSqlCommand("s_delete_testimonial @testimonialid, @userId", param);
}
return(true);
}
}
public bool PutLookup(int lookupId, LookupModel data)
{
string xml = Helper.XmlSerializer <LookupModel>(data);
using (var context = new SQLContext())
{
SqlParameter[] param = new SqlParameter[9];
param[0] = new SqlParameter("@data", SqlDbType.Xml);
param[0].Value = xml;
param[1] = new SqlParameter("@userid", SqlDbType.Int);
param[1].Value = 0;
context.Database.ExecuteSqlCommand(
"s_put_lookups @data, @userid",
param);
return(true);
}
}
private UserInformationModel GetValidateUser(string userName)
{
UserInformationModel oResponse = new UserInformationModel();
UserModel rdUser = new UserModel();
db = new SQLContext();
using (var conn = db.Database.Connection)
{
using (var reader = conn.QueryMultiple("dbo.s_get_login_users",
new
{
userName = userName
},
commandType: CommandType.StoredProcedure))
{
//get user header and details
rdUser = reader.Read <UserModel>().FirstOrDefault();
}
}
if (rdUser != null)
{
oResponse.User = rdUser;
oResponse.Exception = new ExceptionModel()
{
ErrorCode = Convert.ToInt16(LoginMessage.SUCCESS),
ErrorDesc = LoginMessage.SUCCESS.GetStringValue()
};
}
else
{
oResponse.Exception = new ExceptionModel()
{
ErrorCode = Convert.ToInt16(LoginMessage.USER_NOT_EXISTS),
ErrorDesc = LoginMessage.USER_NOT_EXISTS.GetStringValue()
};
}
return(oResponse);
}
public bool PutTestimonial(int testimonialId, TestimonialModel data)
{
string xml = Helper.XmlSerializer <TestimonialModel>(data);
using (var context = new SQLContext())
{
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@testimonialid", SqlDbType.Int);
param[0].Value = testimonialId;
param[1] = new SqlParameter("@data", SqlDbType.Xml);
param[1].Value = xml;
param[2] = new SqlParameter("@userid", SqlDbType.Int);
param[2].Value = 0;
context.Database.ExecuteSqlCommand(
"[dbo].[s_put_testimonial] @testimonialid, @data, @userid",
param);
return(true);
}
}
public bool PutBlog(int blogId, BlogModel data)
{
string xml = Helper.XmlSerializer <BlogModel>(data);
using (var context = new SQLContext())
{
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@blogid", SqlDbType.Int);
param[0].Value = blogId;
param[1] = new SqlParameter("@data", SqlDbType.Xml);
param[1].Value = xml;
param[2] = new SqlParameter("@userid", SqlDbType.Int);
param[2].Value = 0;
context.Database.ExecuteSqlCommand(
"[dbo].[s_put_blog] @blogid, @data, @userid",
param);
return(true);
}
}
public LookupModel PostLookup(LookupModel data)
{
string xml = Helper.XmlSerializer <LookupModel>(data);
using (var context = new SQLContext())
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@data", SqlDbType.Xml);
param[0].Value = xml;
param[1] = new SqlParameter("@userid", SqlDbType.Int);
param[1].Value = 0;
var insert = context.Database.SqlQuery <LookupModel>("s_post_lookups @data, @userid", param);
LookupModel result = insert.FirstOrDefault();
if (result != null)
{
return(result);
}
return(data);
}
}
public GeneralServices()
{
db = new SQLContext();
oException = new ExceptionManagement();
}
