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)); }