public ShareEntity Get(int entityId)
{
string strSql = @"SELECT S.*, TypeID = ST.ID, TypeTitle = ST.[Title], TypeCreatedBy = ST.[CreatedBy],
TypeCreatedOn = ST.[CreatedOn],TypeType = ST.[Type] ,Files =
(select CAST(FileID AS NVARCHAR)+ '_' + FileTitle + [ContentType]+'|' from [Files]
WHERE [SourceType] = 7 AND [SourceID] = S.ID AND IsDelete = 0 FOR XML PATH('') )
FROM [Share] S LEFT JOIN [ShareType] ST ON S.[Type] = ST.ID
Where S.[IsDeleted] = 0 AND S.ID = @ID";
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql))
{
try
{
db.AddInParameter(dbCommand, "ID", DbType.Int32, entityId);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
if (dataReader.Read())
{
return(ShareEntity.ReaderBind(dataReader));
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]"
, strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message));
return(null);
}
}
return(null);
}
public SearchShareResponse GetShares(SearchShareRequest request)
{
string strWhere = "";
if (request.CreatedBy > 0)
{
strWhere += " AND CreatedBy = @CreatedBy ";
}
if (request.Type > 0)
{
strWhere += " AND Type = @Type ";
}
if (!string.IsNullOrEmpty(request.Keyword))
{
int tmpTicketId;
int.TryParse(request.Keyword, out tmpTicketId);
strWhere += string.Format(" AND( Note like '%{0}%' OR TicketID ={1})", request.Keyword.FilterSqlString(), tmpTicketId);
}
if (request.StartDate > MinDate)
{
strWhere += " And CreatedOn >= @StartDate ";
}
else
{
request.StartDate = MinDate;
}
if (request.EndDate > MinDate)
{
strWhere += " And CreatedOn <= @EndDate ";
}
else
{
request.EndDate = MinDate;
}
string strSql = string.Format(@"SELECT BASICFILTER.*,
TypeID = ST.ID, TypeTitle = ST.[Title], TypeCreatedBy = ST.[CreatedBy],
TypeCreatedOn = ST.[CreatedOn],TypeType = ST.[Type] FROM (
SELECT * ,Files =
(select CAST(FileID AS NVARCHAR)+ '_' + FileTitle + [ContentType]+'|' from [Files]
WHERE [SourceType] = 7 AND [SourceID] = S.ID AND IsDelete = 0 FOR XML PATH('') )
,Row_Number() OVER(ORDER BY {0} {1}) AS INDEX_ FROM [Share] S
Where [IsDeleted] = 0 {2}
) AS BASICFILTER LEFT JOIN [ShareType] ST ON BASICFILTER.[Type] = ST.ID
", request.OrderExpression, request.OrderDirection, strWhere);
if (request.IsPageModel)
{
strSql += string.Format("WHERE BASICFILTER.INDEX_ BETWEEN {0} AND {1} ;",
(request.CurrentPage - 1) * request.PageCount + 1,
request.CurrentPage * request.PageCount)
;
strSql += string.Format(@"SELECT Count(1) as [Count] FROM [Share]
Where [IsDeleted] = 0 {0}", strWhere);
}
var response = new SearchShareResponse();
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand dbCommand = db.GetSqlStringCommand(strSql))
{
try
{
db.AddInParameter(dbCommand, "CreatedBy", DbType.Int32, request.CreatedBy);
db.AddInParameter(dbCommand, "Type", DbType.Int32, request.Type);
db.AddInParameter(dbCommand, "StartDate", DbType.DateTime, request.StartDate);
db.AddInParameter(dbCommand, "EndDate", DbType.DateTime, request.EndDate);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
response.Dataset.Add(ShareEntity.ReaderBind(dataReader));
}
if (dataReader.NextResult() && dataReader.Read())
{
response.Count = dataReader.GetInt32(0);
}
}
}
catch (Exception ex)
{
WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2},\r\nSourceCode:{3}]"
, strSql, base.FormatParameters(dbCommand.Parameters), ex.Message, ex.StackTrace));
return(null);
}
}
return(response);
}