Loop Through Database Records (C#, Razor)

Here’s a code sample showing a quick and easy way to loop through the results of a SQL query and display them on a web page in a list.

@{
    var db = Database.Open("YourDatabase");
    var ListDataItems = db.Query("SELECT SomeField FROM SomeTable");
}
<!DOCTYPE html>
<html lang="en">
<meta charset="utf-8" />
    <head>
        <meta charset="utf-8" />
        <title>ForEach Query Test Page</title>
    </head>
    <body>
        <ul>
        @foreach(var ListItem in ListDataItems){
	<li>@ListItem.SomeField</li>
        }
        </ul>
    </body>
</html>

This is pretty basic but let’s break it down.

In this example I have an existing database in my project’s /App_Data/ folder named “YourDatabase.sdf” (it’s a SQL CE database). I reference that database in line 2 and then run my query in line 3. The results of that query are placed into a recordset stored in the ListDataItems variable also defined on line 3.

Most of the rest is basic HTML but on line 14 I start a look that iterates through each item in ListDataItems and puts the rows – one at a time – into the ListItem variable.

On line 15 I reference the row (using ListItem) and also the specific field I want to display – SomeField in this case. I could have easily had multiple items returned per row in my query (line 3) and in that case I would have been able to reference whichever, or multiples, using their specific field name.

Line 16 just closes out the code block for the loop.

Brad on Google+

Sending Email Code Sample (Razor, C#)

A *really* common and popular thing to do with web pages it to send email. That email might be a feedback form, or an order confirmation, or a variety of other functions. Below is the complete contents of a page I named SendEmail.cshtml. I’ll throw it out there right at the start in case you want to copy/paste it and start playing around. After the code I’ll break things down a bit to explain.

**NOTE: You need a working SMTP Server to send the email. In my code sample I used GMail’s SMTP service, which requires SMTP-AUTH of an existing username/password. If you have a GMail account and want to use this same SMTP server, just be sure to put your username (email address) and password in the appropriate places. If you want to use a different SMTP server, then you’ll need the name of that server and security requirements (SMTP-AUTH, IP-AUTH, etc.) and adjust your code as needed.

Oh, let me also say up-front here – DO NOT just use this code sample and put it out on a public web page. It won’t take a spammer very long to find the page (I don’t know how they do – but they do!) and start abusing it. As soon as that happens you are going to be on the hook for a lot of trouble since your specific identity is known via the authentication.

Okay, here’s the code:

@{
    var errorMessage = "";

    if (IsPost)
    {
        var toEmail = Request["toEmail"];
        var fromEmail = Request["fromEmail"];
        var subject = Request["subject"];
        var body = Request["body"];

        try {
            WebMail.SmtpServer = "smtp.gmail.com";
            WebMail.SmtpPort = 25;
            WebMail.EnableSsl = true;
            WebMail.UserName = "*YOU*@gmail.com";
            WebMail.Password = "*YourGMailPassword*";
            WebMail.From = fromEmail;
            WebMail.Send(to: toEmail,
                subject: subject,
                body: body
            );
        }
        catch (Exception ex ) {
            errorMessage = ex.Message;
        }
    }
}
@if (errorMessage !="") {
<div>Error: @errorMessage

}
<form method="post">
 From Address: <input type="text" name="fromEmail" />
 To Address: <input type="text" name="toEmail" />
 Subject: <input type="text" name="subject" />
 Body: <textarea id="body" cols="40" rows="10"></textarea>
 <input id="Submit1" type="submit" value="submit" />
</form>

Okay, let’s break this down a bit. In line two I’m defining a variable that can be used to capture any error message that’s returned. I actually added that after I started working on this sample because I was throwing a number of different errors every time I fixed one. :)

Starting at line 4 I check to see if this is a postback to the page or a first load. If it’s a first load then I ignore the code and show the form. If it’s a postback then I define some variables and load them with values posted from the form.

Then on line 11 we start the try block that wraps the email-sending code. I find that it’s pretty easy to have errors working with email code (a previous post I wrote years ago had over 100 comments from people with various challenges) so wrapping the code in a Try-Catch block and showing the error in a user-friendly format is helpful.

Starting with line 12 we define everything we need to send the email. First I define the SMTP server. This can be “localhost” if you have an SMTP service running on your machine (like Smtp4dev or even the Microsoft SMTP service (if you are running Windows Server 2008 R2 or earlier – Windows 8.1 and Server 2012 no longer support the Microsoft SMTP service). In my sample code I’m using my GMail account so using GMail’s SMTP server. Then I clarify the port number (some ISPs use a non-standard port). GMail’s SMTP server requires an encrypted connection so I specifically set EnableSsl to true (this is ISP dependent – check with whoever manages your SMTP server). The new two lines set the username and password if SMTP-AUTH is requires by your SMTP server (GMail does require it to track spammers). Lastly I set the From address and then execute the Send method.

If there is an error it will be noticed by the Catch statement on line 23 and assigned to the errorMessage variable for display on line 29.

That’s about it. The rest is a really basic HTML form so I won’t go through that specifically.

Brad on Google+

Razor Hello There Sample!

This is my version of a Hello World application. I don’t want to just write “Hello World!” to the screen in a web page because, well, that’s just too basic. So what I’ll do is write a very simple form, post the form back to itself (the same page), collect data from the form, then display the data.

While extremely simple, this demonstrates a few different key functions of a web application – collecting and posting data, gathering that data, then doing something with the data. It also gives a simple demonstration of code logic mixed inline with HTML – a common practice when using Razor pages.

Like most of my code sample posts, I’m not specifically writing for efficiency, performance, or security here – just showing one (of many) way to accomplish something.

The below is the full text of a page I named HiThere.cshtml.

@{
    string userName = Request.Form["userName"];
}
<div>
    @if (!IsPost) {
    <form method="post">
        What's your name? <input name="userName" type="text" />
        <input type="submit" />
    </form>
    } else {
        <div>hi @userName!</div>
        }
</div>

Even though its simple, let’s break it down.

Lines 1-3 define a local variable of type string and assign it to whatever was posted to this page with a form input name of userName. If nothing was posted to the page then the variable of course will be null.

Line 5 checks to see if this is the first load of the page or if it was posted-to. If it was *not* posted-to (IsPost is a boolean check for a postback, ! means not) then I want the form to display and ask the user what their name is – and give them an opportunity to enter it and submit the form.

Line 10 is an extension of that check… if the form *was* posted-to, then don’t show the form and only show Hi <whatever>!.

That’s it! I said it was simple and it is! :)

Happy coding!

Brad on Google+