SQL Server CE: Select first value in column

Question

I'm trying to get the date from my database but only the first one that matches, I don't need the other ones as I only want to display it one time and otherwise the foreach writes it out multiple time depending on the number of results of course.

var getDate = "SELECT date FROM Test WHERE date = '" + inputDate + "'";

foreach (var c in db.Query(getDate)) {
    DateTime Date = c.date;
    var showDate = Date.ToString("MMMM dd, yyyy");
    <a>@showDate</a>
}

I have tried limit, first and top with no success, I've also found that it has to be SQL Server CE v.3.5+ but I don't know what I have, as I use webmatrix and cant find any info on it.

Is there any way to just display the first row of date that matches and write it out by itself all alone?

Answer 1

You can simply add a break after <a>@showDate</a>, as follow:

var getDate = "SELECT date FROM Test WHERE date = '" + inputDate + "'";

foreach (var c in db.Query(getDate)) {
    DateTime Date = c.date;
    var showDate = Date.ToString("MMMM dd, yyyy");
    <a>@showDate</a>
    break;
}

break command force the exit from a loop (foreach is a loop)

Answer 2

The proposed solution will load all the matching dates, why not use:

var getDate = "SELECT TOP 1 [date] FROM [Test] WHERE [date] = '" + inputDate + "'";