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

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

public List GetPageWebLogs()
{
SqlCommand command = new SqlCommand();
if (txtURL.Value == null)
{
command.CommandText = "SELECT URL, SearchEngine, COUNT(*) AS Anzahl FROM PagesLogs GROUP BY URL, SearchEngine HAVING(NOT(URL IS NULL))" +
"ORDER BY Anzahl DESC";
}
else
{
command.CommandText = "SELECT URL, SearchEngine, COUNT(*) AS Anzahl" +
" FROM PagesLogs WHERE URL = @URL and(DateTime > CONVERT(DATETIME,@FromDate, 102)) AND(DateTime <= CONVERT(DATETIME, @ToDate, 102))" +
" GROUP BY URL, SearchEngine HAVING(NOT(URL IS NULL)) ORDER BY Anzahl DESC";
command.Parameters.AddWithValue("@URL", txtURL.Value);
command.Parameters.AddWithValue("@FromDate", deStart.Value);
command.Parameters.AddWithValue("@Todate", deEnd.Value);
}
List elements = new List();
using (SqlConnection connection = SqlHelper.getSQL_Connection(true))
{
connection.Open();
command.Connection = connection;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
PageWebLog pagesLogs = new PageWebLog();
pagesLogs.URL = (string)reader["URL"];
pagesLogs.SearchEngine = (string)reader["SearchEngine"];
pagesLogs.Anzahl = (int)reader["Anzahl"];
elements.Add(pagesLogs);
}
}
}
return elements;
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo6y ago
hi
Your code looks like currect.But convert the datetime format may solve
command.Parameters.AddWithValue("@FromDate", deStart.Value.ToString("yyyy-MM-dd hh:mm:ss"));
command.Parameters.AddWithValue("@Todate", deEnd.Value.ToString("yyyy-MM-dd hh:mm:ss"));
0