1
Answer

How could I filter Data based on URL or Date Time range ?

Abdalla Omran

Abdalla Omran
5y
723
1
 How could I filter Data based on URL or Date Time range or BOTH OF THEM ?
 
I am working with ASP.Net WebForms and i want to write only ONE query that do the following :
load all records after that i have comobox of URL and Date time Range from -to and i want to filter the records based on URL with datetime range or just based on datetime range without URL that's meaning when i load the whole data in GridView then i can filter them based on Datetime range and if the user want could filter the records based on (URL with Date Time range ) here what i tried and noted that i just want Query do all that : 
 
1- load all record 
2 - filter based on only date time range  
3 - filter based on URL with Date Time range if the user want that 
 
for any help i will be very glad  
  1. public List<PageWebLog> GetPageWebLogs()  
  2.        {  
  3.            SqlCommand command = new SqlCommand();  
  4.   
  5.            if (txtURL.Value == null)  
  6.            {  
  7.                command.CommandText = "SELECT  URL, SearchEngine, COUNT(*) AS Anzahl FROM  PagesLogs GROUP BY URL, SearchEngine HAVING(NOT(URL IS NULL))" +  
  8.                  "ORDER BY Anzahl DESC";  
  9.            }  
  10.            else  
  11.            {  
  12.                command.CommandText = "SELECT URL, SearchEngine, COUNT(*) AS Anzahl" +  
  13.                    " FROM  PagesLogs WHERE URL = @URL and(DateTime > CONVERT(DATETIME,@FromDate, 102)) AND(DateTime <= CONVERT(DATETIME, @ToDate, 102))" +  
  14.                    " GROUP BY URL, SearchEngine HAVING(NOT(URL IS NULL)) ORDER BY Anzahl DESC";  
  15.                command.Parameters.AddWithValue("@URL", txtURL.Value);  
  16.                command.Parameters.AddWithValue("@FromDate", deStart.Value);  
  17.                command.Parameters.AddWithValue("@Todate", deEnd.Value);  
  18.            }  
  19.   
  20.            List<PageWebLog> elements = new List<PageWebLog>();  
  21.            using (SqlConnection connection = SqlHelper.getSQL_Connection(true))  
  22.            {  
  23.                connection.Open();  
  24.                command.Connection = connection;  
  25.                using (SqlDataReader reader = command.ExecuteReader())  
  26.                {  
  27.                    while (reader.Read())  
  28.                    {  
  29.                        PageWebLog pagesLogs = new PageWebLog();  
  30.                        pagesLogs.URL = (string)reader["URL"];  
  31.                        pagesLogs.SearchEngine = (string)reader["SearchEngine"];  
  32.                        pagesLogs.Anzahl = (int)reader["Anzahl"];  
  33.                        elements.Add(pagesLogs);  
  34.                    }  
  35.                }  
  36.            }  
  37.            return elements;  
  38.        }  
 
Answers (1)
Next Recommended Forum