public int GetCount <T>(string sql, string filterExpression, string columns)
{
T ViewModel = Reflect.GetInstance <T>();
filterExpression = getFilter(filterExpression, columns, ViewModel);
string baked_sql = "SELECT COUNT(*) FROM ( {sqlExpression} ) as DerivedName WHERE {filterExpression} ";
baked_sql = Base.Extension.StringExtension.StringExtension.NamedStringFormat(baked_sql, new { sqlExpression = sql, filterExpression = filterExpression });
DBHelper.SQLServer.DirectDB db = new DBHelper.SQLServer.DirectDB(_connectionString);
int count = int.Parse(db.ExecuteScalar(CommandType.Text, baked_sql));
db.Close();
return(count);
}
private static void WriteLog(LogStru stru)
{
if (LogProvider == LoggerProviersType.FILE)
{
File.AppendAllText(PATH, "\r\n" + string.Format("User:{0} Date:{1} Time:{2} LogTypeIdentifier:{3}\nLogMessage:{4}{5}", stru.UserName, stru.DateF, stru.Time, stru.LogTypeIdentifier, stru.LogMessage, LogSeparator));
}
if (LogProvider == LoggerProviersType.DB)
{
string sqlTemplate = " insert into tblLog values(N'{0}','{1}','{2}',N'{3}',N'{4}',N'{5}') ";
StringBuilder sqlStr = new StringBuilder();
sqlStr.AppendFormat(sqlTemplate, stru.UserName.Replace("'", "\""), stru.DateF, stru.Time, stru.LogTypeIdentifier.Replace("'", "\""), stru.LogMessage.Replace("'", "\""), stru.StackTrace.Replace("'", "\""));
IDirectDB DB = new DBHelper.SQLServer.DirectDB(DBConnection);
DB.SQL_Log(System.Data.CommandType.Text, sqlStr.ToString());
DB.Close();
}
}
private static void WriteExceptionLog(List <LogStru> struList)
{
if (LogProvider == LoggerProviersType.FILE)
{
StringBuilder str = new StringBuilder();
bool first = true;
foreach (LogStru stru in struList)
{
string Message = stru.LogMessage;
string pre = "";
if (!first)
{
pre = "\t";
Message = "----------------------InnerException---------------------------";
}
str.AppendLine("");
str.AppendLine(pre + "User : "******"Time : " + stru.Time);
str.AppendLine(pre + "LogTypeIdentifier : " + stru.LogTypeIdentifier);
str.AppendLine(pre + "LogMessage : " + Message);
str.AppendLine(pre + "StackTrace :\r\n" + stru.StackTrace);
str.AppendLine("");
first = false;
}
File.AppendAllText(PATH, str.ToString() + LogSeparator);
}
if (LogProvider == LoggerProviersType.DB)
{
string sqlTemplate = " insert into tblLog values(N'{0}','{1}','{2}',N'{3}',N'{4}',N'{5}') ";
StringBuilder sqlStr = new StringBuilder();
foreach (LogStru stru in struList)
{
sqlStr.AppendFormat(sqlTemplate, stru.UserName, stru.DateF, stru.Time, stru.LogTypeIdentifier, stru.LogMessage, stru.StackTrace);
}
IDirectDB DB = new DBHelper.SQLServer.DirectDB(DBConnection);
DB.SQL_Log(System.Data.CommandType.Text, sqlStr.ToString());
DB.Close();
}
}
/// <summary>
/// Get Sql query with paging parametes and sortExpression and return IList of T type given
/// </summary>
/// <typeparam name="T">Input Type</typeparam>
/// <param name="sql">Sql Expression</param>
/// <param name="startRowIndex">Current Page</param>
/// <param name="maximumRows">Num Rows Displayed on Page</param>
/// <param name="sortExpression">Sort Column Name and Direction(Asc/Desc)</param>
/// <param name="filterExpression">Filter expression send from search UserControl</param>
/// <param name="columns">Searchable fields list (Grid Colums sort expression_</param>
/// <param name="noPaging">If true no paging done</param>
/// <returns>returns IList of input type</returns>
public IList <T> GetList <T>(string sql, int startRowIndex, int maximumRows, string sortExpression, string filterExpression, string columns, bool noPaging = false)
{
if (string.IsNullOrEmpty(sortExpression))
{
throw new Exception("sortExpression must be something");
}
T ViewModel = Reflect.GetInstance <T>();
DBHelper.Attributes.ViewModelPropertyAttribute attr = Reflect.GetViewModelProperty <T>(ViewModel, sortExpression);
if (attr.IsDesc)
{
sortExpression = attr.SortExpression + " DESC ";
}
else
{
sortExpression = attr.SortExpression;
}
bool IsReplaced = false;
if (!noPaging)
{
if (sql.Contains("FROM "))
{
IsReplaced = true;
sql = sql.Replace("FROM ", ",ROW_NUMBER() OVER(ORDER BY {sortExpression}) as RowNum FROM ");
}
if (sql.Contains("from "))
{
IsReplaced = true;
sql = sql.Replace("from ", ",ROW_NUMBER() OVER(ORDER BY {sortExpression}) as RowNum FROM ");
}
if (!IsReplaced)
{
throw new Exception("Your sql expression should contains 'FROM ' or 'from ' word (without quoates)");
}
}
filterExpression = getFilter(filterExpression, columns, ViewModel);
string baked_sql =
@"SELECT * FROM
({sqlExpression} AND {filterExpression}
) as DerivedName ";
if (!(sql.ToLower().Contains("where\n") || sql.ToLower().Contains("where ")))
{
baked_sql =
@"SELECT * FROM
({sqlExpression} WHERE {filterExpression}
) as DerivedName ";
}
if (!noPaging)
{
baked_sql += "\n WHERE RowNum BETWEEN {startRowIndex}+1 AND ({startRowIndex} + {maximumRows})";
}
//WHERE RowNum BETWEEN {startRowIndex} AND ({startRowIndex} + {maximumRows}) - 1 ";
baked_sql = Base.Extension.StringExtension.StringExtension.NamedStringFormat(baked_sql,
new
{
sqlExpression = sql,
startRowIndex = startRowIndex,
maximumRows = maximumRows
,
filterExpression = filterExpression,
sortExpression = sortExpression
});
DBHelper.SQLServer.DirectDB db = new DBHelper.SQLServer.DirectDB(_connectionString);
DataTable dt = db.ExecuteQuery(CommandType.Text, baked_sql);
db.Close();
return(Reflect.DataTable2ObjectList <T>(dt));
}
