public ActionResult GetData()
{
DBContainer Db = new DBContainer();
string sql = " select UserCode,UserName from [dbo].[User]";
System.Data.Entity.Infrastructure.DbRawSqlQuery <Models.User> list = Db.Database.SqlQuery <Models.User>(sql);
return(Json(list));
}
public IQueryable <T> GetByProcedureName(T entity, string spName)
{
EmployeeContext _dbContext = new EmployeeContext();
SqlParameter parameter1 = new SqlParameter("@SqlParameter1", 1234567);
// IQueryable<T>
System.Data.Entity.Infrastructure.DbRawSqlQuery <T> result =
_dbContext.Database.SqlQuery <T>(spName); // + " @SqlParameter1", parameter1); // as IQueryable<T>;
return(result as IQueryable <T>);
}
//protected void CrearFilaTabla(DataTable estructura, object parametro)
//{
// var newRow = estructura.NewRow();
// var properties = parametro.GetType().GetProperties();
// foreach (var property in properties)
// {
// var value = property.GetValue(parametro);
// if (value != null)
// {
// if (value is DateTime)
// newRow[property.Name] = ((DateTime)value).ToString("MM/dd/yyyy");
// else if (value is string)
// newRow[property.Name] = value;
// else
// {
// if (value is decimal?)
// {
// decimal valueInt = Convert.ToDecimal(value);
// if (valueInt != 0)
// newRow[property.Name] = value;
// }
// else
// {
// int valueInt = Convert.ToInt32(value);
// if (valueInt != 0)
// newRow[property.Name] = value;
// }
// }
// }
// }
// estructura.Rows.Add(newRow);
//}
protected async Task <IList <object> > _ConsultarAsync(string storeName, Type entityType, params SqlParameter[] parametros)
{
try
{
using (var db = new ICPruebaEntities())
{
string sentencia = "exec " + storeName + " ";
if (parametros != null && parametros.Count() > 0)
{
foreach (var item in parametros)
{
sentencia += item.ParameterName + ",";
}
}
sentencia = sentencia.Substring(0, sentencia.Length - 1);
System.Data.Entity.Infrastructure.DbRawSqlQuery result = null;
if (parametros != null && parametros.Count() > 0)
{
result = db.Database.SqlQuery(entityType, sentencia, parametros);
}
else
{
result = db.Database.SqlQuery(entityType, sentencia);
}
IList <object> result1 = await result.ToListAsync();
return(result1);
}
}
catch
{
return(null);
}
}
/*
* Method getting comment by comment ID
* commentId: ID of product (from product detail page)
* cms_db: Controll class from DataMode.DataStore
*/
public ObjContentComment GetCommentById(long commentId, Ctrl cms_db, long userId)
{
try
{
ObjContentComment comment = new ObjContentComment();
string query = "SELECT cm.*, mc.ThumbURL AS UserAvatar, ur.Id AS UserId FROM ContentComment cm LEFT JOIN dbo.[User] ur "
+ "ON cm.EmailAddress = ur.EMail LEFT JOIN MediaContent mc ON ur.Id = mc.ContentObjId WHERE cm.CommentId = '" + commentId + "' ORDER BY cm.CrtdDT DESC";
using (var context = new alluneedbEntities())
{
System.Data.Entity.Infrastructure.DbRawSqlQuery <ObjContentComment> data = db.Database.SqlQuery <ObjContentComment>(query);
comment = data.FirstOrDefault();
}
return(comment);
}
catch (Exception ex)
{
Core core = new Core();
core.AddToExceptionLog("GetCommentById", "ProductController", "Get comment by ID Error: " + ex.Message, userId);
return(null);
}
}
/*
* Method getting latest comment of a product
* productId: ID of product (from product detail page)
* objTypeId: Object type id (This can retrive comment for many kind like: product, media, news, videos etc...)
* cms_db: Controll class from DataMode.DataStore
*/
public List <ObjContentComment> GetLatestCommentByProductId(int productId, int objTypeId, Ctrl cms_db)
{
Product product = cms_db.GetObjProductById(productId);
if (product != null)
{
try
{
List <ObjContentComment> comments = new List <ObjContentComment>();
string query = "SELECT top (1) cm.*, mc.ThumbURL AS UserAvatar, ur.Id AS UserId FROM ContentComment cm LEFT JOIN dbo.[User] ur "
+ "ON cm.EmailAddress = ur.EMail LEFT JOIN MediaContent mc ON ur.Id = mc.ContentObjId WHERE cm.ContentObjId = '" + productId + "' ORDER BY cm.CrtdDT DESC";
using (var context = new alluneedbEntities())
{
System.Data.Entity.Infrastructure.DbRawSqlQuery <ObjContentComment> data = db.Database.SqlQuery <ObjContentComment>(query);
comments = data.ToList();
}
if (comments.Count > 0)
{
return(comments);
}
else
{
return(null);
}
}
catch (Exception ex)
{
string message = ex.Message;
return(null);
}
}
else
{
return(null);
}
}
public ICollection <TakeDocModel.View_DocumentExtended> Search(string title, string reference, Guid typeDocumentId, ICollection <TakeDocModel.Dto.Document.SearchMetadata> searchs, Guid userId, Guid entityId)
{
StringBuilder sqlVersion = new StringBuilder("SELECT VersionId FROM dbo.Version v WHERE v.VersionId = dx.VersionId AND v.EtatDeleteData = 0");
// test value of metadata
foreach (TakeDocModel.Dto.Document.SearchMetadata search in searchs)
{
StringBuilder sqlMeta = new StringBuilder("SELECT MetaDataId FROM dbo.MetaData m WHERE m.MetaDataVersionId = v.VersionId AND m.EtatDeleteData = 0 ");
if (search.DataField.DataFieldType.DataFieldInputType.ToUpper() == "ION-TOGGLE")
{
sqlMeta.AppendFormat("AND m.MetaDataName = '{0}' AND (m.MetaDataValue LIKE 'TRUE' OR m.MetaDataText LIKE '{1}%')", search.MetaDataName, search.MetaDataValue);
}
else if (search.Condition.ToUpper() == TakeDocModel.Dto.Document.SearchCondition.Start)
{
sqlMeta.AppendFormat("AND m.MetaDataName = '{0}' AND (m.MetaDataValue LIKE '{1}%' OR m.MetaDataText LIKE '{2}%')", search.MetaDataName, search.MetaDataValue, search.MetaDataValue);
}
sqlVersion.AppendFormat("AND EXISTS({0})", sqlMeta);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat("SELECT * FROM dbo.View_DocumentExtended dx WHERE EXISTS({0}) ", sqlVersion);
if (entityId.Equals(Guid.Empty) == false)
{
sql.AppendFormat("AND dx.EntityId = '{0}' ", entityId);
}
if (typeDocumentId.Equals(Guid.Empty) == false)
{
sql.AppendFormat("AND EXISTS (SELECT d.DocumentId FROM dbo.Document d WHERE d.DocumentId = dx.DocumentId AND d.DocumentTypeId = '{0}' AND d.EtatDeleteData = 0)", typeDocumentId);
}
if (userId.Equals(System.Guid.Empty) == false)
{
sql.Append("AND ( ");
// document who i am owner
sql.AppendFormat("(dx.DocumentOwnerId = '{0}' )", userId);
// document who i am the manager of owner
sql.AppendFormat("OR EXISTS (SELECT UserTkId FROM dbo.UserTK WHERE UserTkManagerId = '{0}' AND UserTkId = dx.DocumentOwnerId) ", userId);
// document who i am manager/backoffice
sql.AppendFormat("OR EXISTS (SELECT * FROM dbo.BackOfficeTypeDocument WHERE EntityId = '{0}' " +
"AND EtatDeleteData = 0 AND UserTkId = '{1}' AND TypeDocumentId = '{2}' AND dx.TypeDocumentId = TypeDocumentId)", entityId, userId, typeDocumentId);
sql.Append(")");
}
if (string.IsNullOrEmpty(title) == false)
{
sql.AppendFormat("AND dx.DocumentLabel LIKE '{0}%' ", title);
}
if (string.IsNullOrEmpty(reference) == false)
{
sql.AppendFormat("AND dx.DocumentReference LIKE '%{0}' ", reference);
}
System.Data.Entity.Infrastructure.DbRawSqlQuery <TakeDocModel.View_DocumentExtended> data = this.ctx.Database.SqlQuery <TakeDocModel.View_DocumentExtended>(sql.ToString());
return(data.ToList <TakeDocModel.View_DocumentExtended>());
}