public static List<RequestInfo> GetRequestInfo(string type, string date, string order = "", bool desc = true, int page = 1, int size = 30) { String orderBy = ""; String where = ""; if (!string.IsNullOrWhiteSpace(order)) { if (order.Equals("Id", StringComparison.OrdinalIgnoreCase)) { orderBy = " Order by Id " + (desc ? "DESC" : "ASC"); } else if (order.Equals("CurrentDate", StringComparison.OrdinalIgnoreCase)) { orderBy = " Order by CurrentDate" + (desc ? "DESC" : "ASC"); } } if (!string.IsNullOrWhiteSpace(type)) { where += "WHERE Type=@Type "; } if (!string.IsNullOrWhiteSpace(date)) { if (!string.IsNullOrWhiteSpace(where)) { where += " AND CurrentDate=@CurrentDate "; } else { where += "WHERE CurrentDate=@CurrentDate "; } } List<RequestInfo> requestInfos = new List<RequestInfo>(); string sqlSaveChannel = "SELECT * FROM RequestInfo " + where + orderBy + " LIMIT " + size + " OFFSET " + (page - 1) * size; using (var sql_con = new SQLiteConnection(ReportConnectString)) { sql_con.Open(); using (var sql_cmd = sql_con.CreateCommand()) { sql_cmd.CommandText = sqlSaveChannel; var myParameters = sql_cmd.Parameters; if (!string.IsNullOrWhiteSpace(type)) { myParameters.AddWithValue("@Type", type); } if (!string.IsNullOrWhiteSpace(date)) { myParameters.AddWithValue("@CurrentDate", date); } using (var reader = sql_cmd.ExecuteReader()) { while (reader.Read()) { var requestInfo = new RequestInfo(); requestInfo.Id = Int32.Parse((reader["Id"] ?? "").ToString()); requestInfo.CurrentDate = (reader["CurrentDate"] ?? "").ToString(); requestInfo.Type = (reader["Type"] ?? "").ToString(); requestInfo.IsFailed = (reader["IsFailed"] ?? "").ToString(); requestInfo.RequestLink = (reader["RequestLink"] ?? "").ToString(); requestInfos.Add(requestInfo); } } } sql_con.Close(); } return requestInfos; }
public static List<RequestInfo> GetRequestInfoStatistic(string type, string fromDate, string toDate) { String where = ""; if (!string.IsNullOrWhiteSpace(type)) { where += "WHERE Type=@Type "; } if (!string.IsNullOrWhiteSpace(fromDate)) { if (!string.IsNullOrWhiteSpace(where)) { where += " AND CurrentDate>=@FromDate "; } else { where += "WHERE CurrentDate>=@FromDate "; } } if (!string.IsNullOrWhiteSpace(toDate)) { if (!string.IsNullOrWhiteSpace(where)) { where += " AND CurrentDate<=@ToDate "; } else { where += "WHERE CurrentDate<=@ToDate "; } } List<RequestInfo> requestInfos = new List<RequestInfo>(); string sqlSaveChannel = "SELECT * FROM RequestInfo " + where; using (var sql_con = new SQLiteConnection(ReportConnectString)) { sql_con.Open(); using (var sql_cmd = sql_con.CreateCommand()) { sql_cmd.CommandText = sqlSaveChannel; var myParameters = sql_cmd.Parameters; if (!string.IsNullOrWhiteSpace(type)) { myParameters.AddWithValue("@Type", type); } if (!string.IsNullOrWhiteSpace(fromDate)) { myParameters.AddWithValue("@FromDate", fromDate); } if (!string.IsNullOrWhiteSpace(toDate)) { myParameters.AddWithValue("@ToDate", toDate); } using (var reader = sql_cmd.ExecuteReader()) { while (reader.Read()) { var requestInfo = new RequestInfo(); requestInfo.Id = Int32.Parse((reader["Id"] ?? "").ToString()); requestInfo.CurrentDate = (reader["CurrentDate"] ?? "").ToString(); requestInfo.Type = (reader["Type"] ?? "").ToString(); requestInfo.IsFailed = (reader["IsFailed"] ?? "").ToString(); requestInfo.RequestLink = (reader["RequestLink"] ?? "").ToString(); requestInfos.Add(requestInfo); } } } sql_con.Close(); } return requestInfos; }