Writing Dynamic Linq Queries in Linq-to-Entities

Believe it or not, L2E actually shines in this area

I am stubbornly plugging along with L2E, and I am even becoming kind of fond of it (in some areas). For example, I really like the way it handles dynamic queries, even more than L2E because you can pass in objects, not just strings! The syntax is a little different, but I think it is easier to understand and a better approach.

setup

First thing is first: you have to use the System.Linq.Dynamic.dll just like with Linq-to_SQL, you can get it from the links on ScottGu's blog. Then just make sure to throw it in your bin folder (or reference it) and include this in the program:
using System.Linq.Dynamic;

how to use 'where'

Where() is a bit different here. In L2S it just takes in a string, but here I will focus on the following overload:
(extension)IQueryable<T> IQueryable<T>
  .Where(string predicate, params object[] values)

Notice it does take in a string, but also the object array. So say I want to recreate this query:
var results = db.branch
  .Where(b => b.branch_id == 5 || b.display == "Hello");

With a dynamic query I would do this:
var results = db.branch
  .Where("branch_id == @0 || display == @1", 
    new object[] {5, "Hello"});

See how easy that was? The @0 and @1 are simply replaced with the corresponding objects in the array. Also notice that the array takes in all sorts of different object types as it does not matter what you are putting through. Now obviously you aren't going to set the array manually every time, that would defeat the purpose of the dynamic query, but you can easily see how this works.

In some programs, I have even built the string part (myQuery in this example) of the query dynamically with a running count so I know what @ to place in the string; along with the string being built I would add the objects to a List<object> myObjects and simply call the final products with:
var results = db.branch
  .Where(myQuery, myObjects.ToArray());

how to use 'select'

This is identical to L2S, very simple. If you don't use a Select(), it will just grab all the full objects. But if you want to grab select parts, or change names of some stuff, it is not difficult. A simple dynamic Select could look like this:
var results = db.branch
  .Select("new(branch_id,display)");

That would just grab the branch_id and display values of the branch items. But you can also change the name of the column they are represented in which can often be useful in dynamically produced tables and such:
var results = db.branch
  .Select("new(branch_id as BRANCH,display as NINJAS)");

Those columns will now be titled 'BRANCH' and 'NINJAS' respectively. Not too tough there. I think L2E got this dynamic query thing pretty spot on.

Getting Foreign Key Values with Linq-to-Entities

A little more complicated that just calling the property...

Once again, L2E makes life a bit tougher. Let us say that we have a database record with a foreign key 'state_id' that links to a 'states' table. In this said record, we want to get the integer value of that 'state_id' column (which is the unique identifier/primary key of the states table). BUT, we don't want to pull the state information, we just want the indentifier. Once again, in L2S, this was trivial:

linq-to-sql
var a_record = 
  dataContext.records.First(r => r.record_id == 1);
int s_id = a_record.state_id;

Doesn't get much simpler than that, and this makes perfect sense. But L2E does not allow this... why I do not know, but you can't simply call on the property 'state_id' because it is not there.

linq-to-entities
// won't work
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = record.state_id; // doesn't exist

This will not work either:

linq-to-entities
// won't work
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = record.state.state_id; // not populated

Instead, the following *will* work, but it pulls the 'state' and that is an extra pull we don't need:

linq-to-entities
// this works
var a_record = 
  entities.Include("state").records
  .First(r => r.record_id == 1);
int s_id = record.state.state_id; // populated this time

Granted this example is small, but what if that was a table with a giant-sized record?

linq-to-entities
// the proper way
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = Convert.ToInt32(record.stateReference
  .EntityKey.EntityKeyValues.First().Value.ToString());

Wow, that is a lot of code to get a simple integer. This is so aggravating to me because that integer is clearly stored in the DB, but there is no clean way to get it, sp insteada you have to go through the references EntityKey.

Instead, I often write a static extension so I can simply call some_record.stated_id() to get the integer:

public static int isff_tracker_id(this state s)
{ 
  return Convert.ToInt32(record.stateReference.EntityKey
    .EntityKeyValues.First().Value.ToString()); 
}

Now, I am not L2E master, but if someone can point out a better way to do this, I would be greatful! This forced learning of L2E reminds me daily why I will stick with L2S next time.

Parsing/Reading a PDF file with C# and Asp.Net to text

PDFs are a very ubiquitous and useful file type, but they can be a pain to work with programatically

PDFs are extensively used in my organization, and people always want programs that will extract information from them. It can be very difficult to get the information they want due to the strange format, but sometimes it's a necessity. Here is how to get a PDF into text, from there you are on your own!

download the necessary files

There are always more than one way to skin a cat when it comes to programming, but the easiest way I have found for PDFs is to use the fantastic, open-source project PDFBox. The download is good for all sorts of platforms, but you only need a few parts to use it with Asp.Net and C#.

*Now keep in mind the version numbers I show here may change, but the process should stay the same.

what you need

  • Just a few things, pull the following files into your bin:
    • FontBox-0.1.0-dev.dll
    • IKVM.GNU.Classpath.dll
    • IKVM.Runtime.dll
    • PDFBox-0.7.3.dll

  • Now just make sure you add a couple references in your project, it is a bit of a strange process, so follow it closely. First, add this reference:
    • IKVM.GNU.Classpath

  • Then build the project, as the next reference requires the previous to be built. So you then add this reference:
    • PDFBox-0.7.3

  • Then build again - once again, there may be a better way to do this, but this is what the documentation said and it worked, so I won't mess with it. Now all you need to do is make sure you add the following using statements to any code file that needs to use the parser:
    using org.pdfbox.pdmodel;
    using org.pdfbox.util;

all set, now use it

This quick snippet shows how to use the program to take in a pdf file and output it to a .txt file.  The inputs are simply the string 'pdf_in' which is the path to a PDF to parse, and the string 'txt_out' which is the path to the output text file. You can easily modify it to take in a Stream of some sort or something else like using a FileUpload or output some other way, but this should get the idea across.
public void parsePDF(string pdf_in, string txt_out)
{
    StreamWriter sw = new StreamWriter(txt_out, false);
    try
    {
        sw.WriteLine();
        sw.WriteLine(DateTime.Now.ToString());
        PDDocument doc = PDDocument.load(pdf_in);
        PDFTextStripper stripper = new PDFTextStripper();
        sw.Write(stripper.getText(doc));
    }
    catch (Exception ex) { Response.Write(ex.Message); }
    finally
    {
        sw.Close();
        sw.Dispose();
    }
}

And there you have it, your PDF is now a (most likely, ugly and difficult to parse) text file with your PDF data in it; now it's up to you to figure out how to use it. As you will see, PDFs can (not always) be very strange in how they come out as text, tables will often be in odd order and such and it is a new adventure each time to engineer an effective and acurate parsing scheme that is very case-specific. Normally I would offer a download, but these files are pretty big, so I will leave it to the guys at SourceForge.

Inserting New Items Into a Table : 'REAL' AJAX with Asp.Net Series

Add a new item into the table and database and have it immediately sortable, pageable, etc without any postback

This is a continuation of my 'REAL' AJAX with Asp.Net (not Asp.Net AJAX) series posts for those of us trying to stop relying on Asp.Net 'AJAX'.

This is probably the hardest part about a full CRUD (Create, Read, Update, Delete) system, but as we have already covered 'RUD', it's time to hit the 'C'. First of all, we have to break down what will be happening, both on the client-side, and the server side, then it will be easier to dissect.
  1. User pulls up a 'New Entry' dialogue (client)
  2. User enters information and it is validated (client*)
  3. Valid information is sent to the server (client)
  4. Server attempts to add in new item (server)
  5. Server spits back status update (server)
  6. status update is displayed (client)
  7. item is added into the visible table (client)
  8. input fields are cleared and the input dialogue is hidden (client)

The * above denotes that while in this example we are doing only client-side validation, it is in your best interest to add in some server side as well (keep in mind that this example will not work without javascript, so it is not robust and does not 'gracefully' degrade).

As you can see, most of the heavy lifting here is done on the client side, with the dedicated server doing just a couple things.

set up the entry dialogue

First I am adding a couple divs which will be clicked to open the dialogue:

insert.aspx

<div class="button triggerNew">New Entry</div>


In the CSS, you can see that these will render like buttons:

demo.css
.button 
{ 
    width: 100px; 
    font-weight: bold; 
    border: outset 2px blue; 
    padding: 1px; 
    text-align: center; 
    color: Blue; 
}
.button:hover 
{ 
    border-style: inset; 
    cursor: pointer; 
    padding: 2px 0 0 2px; 
}

To go with the whole ajax feel, I am setting up the entry in a modal popup; you probably noticed the class 'triggerNew' which isn't really a css class, but it will be used to by jQuery to hook into any elements that have that class and tie them to the modal. To do that first we must include the jquery.jqModal plugin (which IMO is the best modal plugin as it is minimalist and customizable as well as easy to use) in our masterpage.
demo.master

<script type="text/javascript" src="js/jquery.jqModal.js"></script>


As for the modal popup, here is the markup:

<div class="jqmWindow" id="new">
  <h3 class="modal_header">
    <div class="working">working...</div>
    <a href="#" class="x jqmClose">X</a>
    Add New Entry
  </h3>
  <div class="pad">
    <div class="left">
      <h4>
        <asp:RequiredFieldValidator ID="rfvFirst" ControlToValidate="txtFirst" CssClass="right"
          ErrorMessage="required" runat="server" ValidationGroup="new" />
        First Name
      </h4>
      <input type="text" ID="txtFirst" runat="server" />
    </div>
    <div class="left">
      <h4>
        <asp:RequiredFieldValidator ID="rfvLasst" ControlToValidate="txtLast" CssClass="right"
          ErrorMessage="required" runat="server" ValidationGroup="new" />
        Last Name
      </h4>
      <input type="text" ID="txtLast" runat="server" />
    </div>
    <div class="left">
      <h4>
        <asp:RegularExpressionValidator ID="regAge" ControlToValidate="txtAge" CssClass="right"
          ErrorMessage="1-3 digits" runat="server" Display="dynamic" ValidationGroup="new" />
        <asp:RequiredFieldValidator ID="rfvAge" ControlToValidate="txtAge" CssClass="right"
          ErrorMessage="required" runat="server" Display="dynamic" ValidationGroup="new" />
        Age
      </h4>
      <input type="text" ID="txtAge" runat="server" />
    </div>
    <div class="summary">
      <div id="submit" class="button">Submit</div>
    </div>
  </div>
</div>


Now there is a lot going on there. First of all, I used the Asp.Net validators as we are most familiar with them, and they play nicely with jQuery. Also, I am not using asp:TextBoxes at all, I am sticking with the basic input boxes. But, because I am using the Asp.Net Validators, I still need to include runat="server" with each input that I Validate; this also means that they will get weird Asp.Net IDs and not the exact ones I assign them. All I am doing here is making sure all the fields have values, and that age is a diget with 1-3 digits (yes, someone can be 999 in this system...).

In addition, the css class 'jqmClose' class is included in a link: this will automatically be assigned to close the modal window by jqModal.

I also put an additional 'working' element in there so the user can see that dialogue while the program is running its magic.

But as it stands now, we can't even see that popup, get the validators to fire or submit any sort of data. Here comes the jQuery.

work the jQuery magic

Here is the script, it is explained in the comments:
//assign all 'triggerNew' elements to open the modal dialogue
$('#new').jqm({ trigger: false }).jqmAddTrigger($('.triggerNew'));

//attach this event to the clicking of the 'submit' div
$("#submit").click(function() {
    //will not do anything if the validators don't check out
    //Page_ClientValidate('ValidationGroup') is an Asp.Net generated function
    if (!Page_ClientValidate('new')) { return false; }
    else {
        // get the values from the textboxes into an array
        // notice that this is using .ClientID to get the strange Asp.Net ID assigned to it
        var vals = [
            $("#<%= txtFirst.ClientID %>"),
            $("#<%= txtLast.ClientID %>"),
            $("#<%= txtAge.ClientID %>")
        ];
        // do the ajax post
        $.post(
            //function is at
            "ajax_functions/insert.aspx",
            //send the values frorm the vals object
            { first: vals[0].val(), last: vals[1].val(), age: vals[2].val() },
            function(data) {
                //output the return data
                $("#report").html(data);
                // the class 'success' was sent back if it worked...
                var success = (data.toString().substring(12, 19) == "success");
                //add the new data into the table so the user can see it if it was successful
                if (success) {
                    oTable.fnAddData([
                    vals[0].val(),
                    vals[1].val(),
                    vals[2].val(),
                    '&nbsp;'
                ]);
                    //clear the textboxes
                    $(vals).each(function() { $(this).val('') });
                }
                //hide the modal now that it's done
                $('#new').jqmHide();
            }
        );
    }
});

Note that this will not allowed items added since the last postback to be deleted/edited, that is why the last cell simpy gets an '&nbsp;'. Keep in mind that it is possible, just beyond the scope of this tutorial.

Also, in a ghetto form of error reporting, I passed the css class which will either be 'error' on error, or 'success' otherwise.  We can use that to decide whether or not to post the new data into the table and to clear the inputs.  This will catch any errors that we did not already account for.

Really all that remains is the server-side part, we need to make the page that is called: "ajax_functions/insert.aspx" to handle the inputs passed.

server-side functions

Notice this time that we used a $.post() method, which sends the stated values and returns something - in this case, we are having it return a chunk of html which will tell us what happened with the server, and it will be pushed into the 'report' div. Here is our server operations:

ajax_functions/insert.aspx.cs
using System;

public partial class ajax_functions_insert : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        demoDataContext db = new demoDataContext();
        try
        {
            // get all the new values
            string first = Request.Form[0];
            string last = Request.Form[1];
            int age = Convert.ToInt32(Request.Form[2]);

            //make a new person
            person p = new person()
            {
                first_name = first,
                last_name = last,
                age = age
            };

            //insert them into the db
            db.persons.InsertOnSubmit(p);
            db.SubmitChanges();

            //if it was all successful
            Response.Write("<div class='success'>" + first + " " + last + " [" + age + "] inserted</div>");
        }
        // this will catch all the errors and output an error message if caught
        catch (Exception ex) 
        {
            Response.Write("<div class='success'>Error: " + ex.Message +"</div>");
        }
    }
}

As usual, this is the easy part. Now you have a full working CRUD system, with some pretty good error catching as such with absolutely no postbacks! Ajax is fun and can be quite useful, I hope these tutorials helped make it a little more accessible to some Asp.Net devs, I know I learned a lot making them.

« Deleting

Inline editing with DB write : 'REAL' AJAX with Asp.Net Series

Writing to the database inline without a postback, simple and effective

This is a continuation of my 'REAL' AJAX with Asp.Net (not Asp.Net AJAX) series posts for those of us trying to stop relying on Asp.Net 'AJAX'.

This is the first example of ajax I am showing with a write to the DB, and this will show just how easy it really is. I am building on the last post (GridView Sorting/Paging : 'REAL' AJAX with Asp.Net Series) which will explain where/how the data and display is set up.

Also, you will notice that the CSS changed a bit, that is because this plugin has a hover property that resets the backgorund of the td, and that gave a funky interface; now this can be taken care of with some jQuery magic pretty easily, but that is outside the scope of what this is trying to show.

load all of your js

I am still using jQuery and the extension dataTables but for this lesson I am using an additional plugin: inPlace. So now our script loading looks like this (notice I moved the script refs into the masterpage in this lesson):

demo.master

<script type="text/javascript"
  src="js/jquery.js"></script>
<script type="text/javascript"
  src="js/jquery.dataTables.js"></script>
<script type="text/javascript"
  src="js/jquery.inplace.js"></script>


decide what you want to be editable

Now, since we are using our Repeater table from the last example, we just modify if ever-so-slightly to make it editable, to do that, we just add the class 'editable' (or whatever you want it to be) to each td that we want to be editable. In addition to adding a class, we are going to add meaningful ids to each of the cells, each ID will contain:
  • First 4 Letters - What field is being edited
  • Following digits - 'person_id' that is being edited

if you are unsure as to why we are doing this, keep reading, it will become clear.

inline_edit.aspx

<asp:Repeater ID="rpt" runat="server" DataSourceID="lds">
  <HeaderTemplate>
    <table id="sort_table">
      <thead>
        <tr><th>First</th><th>Last</th><th>Age</th></tr>
      </thead>
      <tbody>
  </HeaderTemplate>
  <ItemTemplate>
    <tr>
      <td id="frst<%#Eval("person_id")%>" class="editable">
        <%#Eval("first_name")%>
      
</td>
      <td id="last<%#Eval("person_id")%>" class="editable">
        <%#Eval("last_name")%>
      
</td>
      <td id="age_<%#Eval("person_id")%>" class="editable">
        <%#Eval("age")%>
      
</td>
    </tr>
  </ItemTemplate>
  <FooterTemplate>
    </tbody></table>
  </FooterTemplate>
</asp:Repeater>
<asp:LinqDataSource ID="lds" runat="server"
  ContextTypeName="demoDataContext" TableName="persons" />


make them editable

Now just add in a a little jQuery magic, and the fields will be editable:

inline_edit.aspx
$(function() {
    $("#sort_table").dataTable();
    $(".editable").editInPlace({
        url: "ajax_functions/update.aspx",
        params: "ajax=yes",
        value_required: true,
        default_text: "click to edit"
    });
});

You can look on the documentation for jquery.inplace.js for a further explanation, but what we declared there is that all items that have class 'editable' will now be editable; also, requests will get sent to 'ajax_functions/update.aspx' (which we haven't made yet) and we are requiring a value.

handling the ajax

Ok, so what is happening now, is if you click on a cell you will be able to edit it, and when you click off, or hit enter (you can edit these behaviors if you want), a POST will be sent to 'ajax_functions/update.aspx'. What is sent to the page is the following values:
  • update_value - the value you edited
  • element_id - the element id that was edited
  • original_html - value from before your edit

Now we only care about the first two in this example, but the third value could also be useful given the situation; possibly returning the old value if an error occurs? Also, it may be easier to see why we included both the field and the person_id in the ID of the <td>.

For handling, you could easily have seperate pages that only handle a certain element, i.e. ajax_functions/first_name.aspx for first_name, ajax_functions/last_name.aspx for last_name, etc. but I found that if you did it that way, you ended up writing the same code over and over - so what I did was combine it into a single update handling page. That is why I include 'what' is being updated in the id.

When you make your handling page, it is important that there is no html in it to start with - as the page text itself will be what is stuffed back into the area that you edited. This is the ENTIRE ajax_functions/update.aspx page:

ajax_functions/update.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="update.aspx.cs" Inherits=ajax_functions_update" %>


Anything we want to appear we will just dump out a Response.Write() for. Here is the handling code, I will explain afterwards:

ajax_functions/update.aspx.cs
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;

public partial class ajax_functions_update : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        demoDataContext db = new demoDataContext();
        // this is unneccessary, but more efficient since there will 
        // be more than 1 db call, this uses the same connection
        SqlConnection con = new SqlConnection(
            ConfigurationManager
            .ConnectionStrings["demoConnectionString"].ConnectionString);

        try
        {
            // get the new value
            string newValue = Request.Form[0]; 
            // figure out what is being edited (remember, its the first 4 chars in the id)
            string what = Request.Form[1].Substring(0, 4);
            // get the person_id of the changing value (the remaining digits)
            int person_id = Convert.ToInt32(
                Request.Form[1].Substring(4, Request.Form[1].Length - 4));

            con.Open();
            // get the record you want to deal with
            person p = db.persons.First(x => x.person_id == person_id);

            // change the value depending on 'what' was sent
            switch (what)
            {
                case "frst": p.first_name = newValue; break;
                case "last": p.last_name = newValue; break;
                case "age_":
                    try { p.age = Convert.ToInt32(newValue); }
                    catch { throw new Exception("not an int"); }
                    break;
                default: throw new Exception("nothing saved");
            }
            db.SubmitChanges();
            con.Close();
            Response.Write(newValue);
        }
        // this will catch all the errors and output them to the edited field
        catch (Exception ex) { Response.Write("Error: " + ex.Message); }
        finally { con.Close(); }
    }
}

First off, the code simply sets the DataContext and opens a SqlConnection (it is explained why in the code). Then, we pull from the POST values from the Request. We pull 3 things from this:
  • person_id - 'person_id' that is being edited
  • what - what field is being edited
  • newValue - the new value for the update

Now is the easy part, get the 'person' to edit, run a switch on 'what' and and update it to 'newValue'. I caught all errors and output them to Response.Write() as that is all the user will be able to see. Also, whatever is written is returned so it integrated seamlessly.  What the user will see is 'Saving...' (unless you change the text or substitute an image) and when it is complete, they will see the new value in place of the old value (which is now written to the DB) as that is what was put in the Response.Write() (if there are no errors, otherwise they'll see the error message).

There you have it! A 'real' ajax inline update with asp.net, and it was as painless as can be! There are all sorts of other things you can do like apply a js function after the update has completed, add in a nice loading graphic instead of the text, etc. - fun to play around with.

Keep in mind with this, if a user (hacker) realizes how it works, they would be able to fabricate a POST to send to your page and update it how they see fit; so be sure you test, protect against that. In one of my apps that uses this. I make sure that the record being edited belongs to the user currently logged in before they can update.

Next will be deleting a row in this table with ajax.



but...

If I have 3 'people', with last names, 'a', 'b', and 'c', and edit 'b' to now be 'z', it will not sort correctly; 'z' will not be the last one in the table, it will still be 'c'. That is because the table data that does the sorting is not held within the table itself, but in an array of 'nodes'.

I left that as a seperate download because this part dives a little deeper into the relation of the sort/edit. What needs to be done is we have to go into the array that holds the nodes, and change the related data along with the represented data in the table.

how do I do that?

This one might require reading a few times, so bear with me. First you need to set the global variables we will be using:
var oTable;
var aPos;
var editedCell;

Then, you will need to store your table in memory:
oTable = $("#sort_table").dataTable();

Now you need to intercept the click in any editable field, and get the position of it in memory; also, we will store the edited cell into a variable as well:
$(".editable").click(function() {
    aPos = oTable.fnGetPosition(this);
    editedCell = $(this);
})

What is now in aPos is a 2-element array [int row_index, int cell_index]; editedCell is the cell that you just clicked. At this point we have all the necessary values to plug the data back in. So next we tack the '.editable()' on to the end of the previous 'click()' call and add a 'success' parameter that will fire on success of the edit. Here is the completed js:

inline_edit.aspx
var oTable;
var aPos;
var editedCell;
$(function() {
    $(".editable").click(function() {
        aPos = oTable.fnGetPosition(this);
        editedCell = $(this);
    }).editInPlace({
        url: "ajax_functions/update.aspx",
        params: "ajax=yes",
        value_required: true,
        default_text: "click to edit",
        success: function() {
            var data = oTable.fnGetData(aPos[0]); 
            data[aPos[1]] = editedCell.html();
        }
    });
    oTable = $("#sort_table").dataTable();
});

Now, the table will re-sort correctly, it's a little extra work, but the added benefit is worth it IMO. Here is the download with the extended functionality (it also has the next demo in it):

Shout it

Making a registration system with Asp.Net and Linq-to-SQL (part 2)

Verifiying your users' email and activating thier accounts

In part 1 of this tutorial, we ended by emailing the user a verification link. In this part, we will us that to verify the user and activate their account.

markup

The markup is very basic, as almost all of your stuff will be done in the backend:

confirm.aspx
<h2>Confirmation</h2>
<asp:Panel ID="Report" runat="server" />

Handle your input

This is where all the heavy lifting is done.Your user has a link which passes the verification guid in the querysting, so our verification page must parse the querystring.There are a few things we will be doing here:
  • Make sure there is a querystring
  • Make sure the Guid is in Guid format
  • Confirm the user from the DB based on Guid

Which is all reletively simple:

confirm.aspx.cs
if (string.IsNullOrEmpty(Request.QueryString["guid"]))
  throw new Exception("invalid inputs", new Exception("please navigate here from the provided link"));

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["demoConnectionString"].ConnectionString);
try
{
  con.Open();
  dbDataContext db = new dbDataContext();
  string guid = Request.QueryString["guid"];
  Guid g = new Guid(guid);
  user u = Users.GetFromGuid(db, g);
  if (u.confirmed)
    Report.Success("account already confirmed", "apparently it was fun enough to do twice...");
  else
  {
    u.confirmed = true;
    db.SubmitChanges();
    Report.Success("account confirmed", "put some link here for the user to continue");
  }
}
catch (Exception ex)
{
  if (ex.Message.Contains("no elements"))
    ex = new Exception("confirmation number not found");   Report.Error(ex);
}
finally { con.Close(); }

Users.cs
public static user GetFromGuid(dbDataContext db, Guid guid)
{ return db.users.Single(u => u.guid_ == guid); }

Ok, now what was done there? First, we simply test to see if a proper querystrirng was passed, if not, it throws an error which is handled with my.Error() extension.Then it goes on to make a SqlConnection, which might seems strange as it is not necessary, *but* since we are possibly making two calls to the DB: getting the user and confirming the user (if necessary) - opening the connection will make sure it only opens once as opposed to twice if we weren't to open it; it is unnecessary, but it is ever-so-slightly more efficient.After that it goes to get the user object from the DB by the GUID, if it is found, it is then confirmed.If there are eny errors or it is not found, it is handled.

Now, since nothing is emailed or anything like that, we can assume (maybe not totally safely) that the user who confirms is the one who got the email. I suppose there could be a brute force GUID bombing of the site, but that would be incredibly inefficient, not to mention pointless as it would only activate accounts and not provide the hacker with anything.

And that is it. You now have a fully working registration system that requires email confirmation and it wasn't even that tough. Here is the code:


« Part 1» Part 2

Making a registration system with Asp.Net and Linq-to-SQL (part 1)

This registration has a slick interface and user verification

NOTE* I do not cover encryption in this tutorial, but I highly discourage storing plain-text passwords in your DB!

It is inevitable that every programmer will want to eventually make a site which requires registration. Then you need to deal with spam accounts and all that good stuff. This is part one of a series of tutorials, where I will show you how to set up a registration process that requires a valid email address. I will be building on the code provided here for the next tutorial and so on. In the future, I will also include a login process I developed that will lock out accounts after a certain amount of attempts, retrieve passwords, and all sorts of other goodies. But before users can log in, they have to register.

I will be using a lot of other people's stuff in this example, such as jQuery, jQuery extensions, Grid960 and so on as well as a lot of my own Extensions, etc. With that said, this is going to have a lot of 'extras' included such as Ajax functionality and some UI niceties to make it a quality interface - you can feel free to cut these parts out, but I feel it will be nice for those that want it. All of the code referenced is included.

Storage

The first step is setting up your user table in the database. The most important thing to think of here is: what am I going to need to collect? I am all about being as simple as possible, so I am going to require two things: email and password - that is it. This is what I came up with:
  • userid - the primary key integer
  • email - the user's email
  • password - user's password
  • guid - guid for verification
  • created - date created; both for record keeping and to see if it was not confirmed after a long time it can be removed
  • confirmed - whether or not it is confirmed
  • last try* - the last login
  • number of failed logins* - number of failures for lockout

The two starred items will not really be used in this too tutorial and are optional if you do not want to prevent unlimited login attempts; though they will be relevant in upcoming tutorials.

Here is the SQL to create my table for users:
CREATE TABLE dbo.users (
userid INT NOT NULL PRIMARY KEY IDENTITY,
email VARCHAR(100) NOT NULL UNIQUE,
password_ VARCHAR(30) NOT NULL,
guid_ UNIQUEIDENTIFIER NOT NULL UNIQUE,
created DATETIME NOT NULL,
confirmed BIT NOT NULL DEFAULT 0,
last_try DATETIME NOT NULL,
tries INT NOT NULL DEFAULT 0
);

Markup

Now that we have our table, go ahead and drag it into a dbml - for this example, I will use one named db.dbml. Now we have our access layer built, we can work on making a registration control; I will be making an ascx registration control, so it can be plugged in aywhere I want to use it. Since I am only collecting two bits of information, this will be a simple control. Here is the markup, I will explain it afterwards:

register.ascx
<asp:UpdatePanel ID="upContact" runat="server">
  <ContentTemplate>
    <div class="pad">
      <asp:Panel ID="Report runat="server" />
      <asp:Panel ID="pnlRegister" runat="server" DefaultButton="registerSubmit">
        <div class="pad_sides">
          <div>
            <h4>
              <asp:RequiredFieldValidator ID="rfvRegisterEmail" runat="server" CssClass="validate" ValidationGroup="register"
                ControlToValidate="registerEmail" ErrorMessage="required" Display="Dynamic" />
            <asp:RegularExpressionValidator ID="regRegisterEmail" runat="server" ControlToValidate="registerEmail"
                ErrorMessage="invalid email" CssClass="validate" Display="Dynamic" ValidationGroup="register"
                ValidationExpression="^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$"
            />
              email
            </h4>
            <asp:TextBox ID="registerEmail" runat="server" CssClass="inputBig full" />
          </div>
        <div>
          <h4>
            <asp:RequiredFieldValidator ID="rfvRegisterPassword" runat="server" CssClass="validate" ValidationGroup="register"
                ControlToValidate="registerPassword" ErrorMessage="required" Display="Dynamic" InitialValue="8 character minimum" />
            <asp:RegularExpressionValidator ID="regRegisterPassword" runat="server" CssClass="validate" ValidationGroup="register"
              ControlToValidate="registerPassword" ErrorMessage="must be at least 8 characters" Display="Dynamic"
              ValidationExpression="^.{8}.*$" />
            password
          </h4>
          <asp:TextBox ID="registerPassword" runat="server" CssClass="inputBig full wm watermark" Text="8 character minimum" />
        </div>
        <div class="summary field">
          <asp:LinkButton ID="registerSubmit" CssClass="button"
            Text="submit" runat="server" ValidationGroup="register" onclick="registerSubmit_Click" />
          </div>
        </div>
      </asp:Panel>
    </div>
  </ContentTemplate>
</asp:UpdatePanel>

Ok, there is a lot going on here, so I will go part by part.

First of all, you will notice that it is within a Asp.Net UpdatePanel which I have been trying to get away from for most things, but for such small controls I have found that is is the best way to go about it: easy and fast.

Next you will see that I have added a Panel with an ID of "Report" - I use this as a standard in most applications as to where to output my 'updates' to the user. This is explained here. The code for this is included in the Extensions.cs file.

Next there is a good amount of validation going on.
  • First I use RequiredFieldValidators for both fields
  • Then I added the RegularExpressionValidator for emails
  • Then I added the RegularExpressionValidator for password length
  • Finally you will notice that the password entry has a watermark which is called via jQuery in the MasterPage

You might notice that I am not using a password field or asking for password verification. This is something you might want to do, but for this example, security is not really a concern, simplicity is; so I figure if you can see your password, you wont screw it up. Also, since we will be adding a password retrieval function, this won't be a big deal.

Backend

That is the markup, but now we have to go to the code so it actually does something. Now what does this have to accomplish?
  • Check if the Email has already been registered
  • Create a new entry in the users table
  • Send an email with a verification link

Not too much going on here, here is the code for accomplishing that, followed by an explanation:

register.ascx.cs
using System;

public partial class controls_register : System.Web.UI.UserControl
{
  protected void Page_Load(object sender, EventArgs e)
  {
    if (this.Attributes["in_page"] != null)
    {
      Utils.DuplicateValidators(this);
    }
  }

  protected void registerSubmit_Click(object sender, EventArgs e)
  {
    try
    {
      dbDataContext db = new dbDataContext();
      if(Users.DuplicateEmail(db, registerEmail.Text))
        throw new Exception("email already registered");
      Guid g = Guid.NewGuid();
      user u = new user()
      {
        created = DateTime.Now,
        email = registerEmail.Text,
        guid_ = g,
        password_ = registerPassword.Text,
        last_try=DateTime.Now
      };
      db.users.InsertOnSubmit(u);
      db.SubmitChanges();
      Email email = new Email(registerEmail.Text, Settings.Get("gm"),
        "please verify your email address",
        "http://yoursite.com/confirm.aspx?guid=" + g.ToString()); //we will get to this in the next tutorial
      email.Send();
      Report.Success("account successfully created", "please check your email to verify your account");
      pnlRegister.Visible = false;
    }
      catch (Exception ex)
    {
      Report.Error(ex);
    }
  }
}

The first thing that happens here is the check for if the Attribute "in_page" is set. This is a bit of a sidebar as it just takes care of duplicate validators if there is more than one of these controls on the page, since I plan on showing how to use them both as a modal popup as well as a standalone page I had to add this check; that way, if you are filling out the popup instead of the form on the page it makes sure that it will not fire the validation for the form you are not using, all it does is change the validation group. The code is visible in the Utils class if you are curious about it. Don't really worry about this too much right now, as it will be covered in an upcoming tutorial.

Next it checks if the email is a duplicate. This calls the Users.cs class, we will get to that next; just remember for now it returns true if it is already in the system, false if not.

If it is new, a new user is then made and inserted into the DB via Linq-to-SQL.

An email is made and sent to the user with the link to the authorization page (which will be coevered in the next tutorial). This is sent using a simplified Email class. The authorization is the guid which was produced - I will cover the authorization page in the next part of the tutorial.

Then the user is notified of the status whether it is success or error using the panel reporting extensions.

This is all pretty simple, all that is left is to explain what is going on in the Users.cs class which is also simple:

Users.cs
using System.Linq;

public static class Users
{
  public static user GetFromEmail(dbDataContext db, string email)
  { return db.users.First(u => u.email.ToLower().Equals(email.ToLower())); }

  public static bool DuplicateEmail(dbDataContext db, string email)
  {
    try
    {
      user temp = GetFromEmail(db, email);
      span class="var">return true;
    }
    catch { return false; }
  }
}

As you can see, this is just two basic LINQ queries, the first one retrieving a user object based on an email string. And the duplicate check which tries to find a user with the given email, if it can, it will return true, meaning it is already in there, otherwise it spits out a false.

And that is all there is to it so far. It is not yet a working system as the user has not verified their identity, but we have accomplished the base of a registration system:
  • Collected the user's email address
  • Collected their password
  • Produced a unique Guid for verification
  • Sent them an email to verify their identity

Now we have it all built, we just need to display everything and call the necessary scripts. I am going to stick all of these pages within a masterpage which calls the scripts in the Page_Load:

demo.master.cs
protected void Page_Load(object sender, EventArgs e)
{
  Page.ClientScript.RegisterClientScriptInclude(typeof(demo), "jQuery", ResolveUrl("~/js/jquery.js"));
  //this will be used in the next tutorial
  Page.ClientScript.RegisterClientScriptInclude(typeof(demo), "jQuery", ResolveUrl("~/js/jqModal.js"));
}

Then just call the control (registered in the web.config file) and the js in the markup:

register.aspx
<h2>Registration</h2>
<cc:register id="reg" runat="server" />

and call the watermark frorm jQuery:

demo.master
<script type="text/javascript" language="javascript">
   $().ready(function() {
     swapValues = [];
     $(".wm").each(function(i) {
       swapValues[i] = $(this).val();
       $(this).focus(function() {
         if ($(this).val() == swapValues[i]) {
           $(this).val("").removeClass("watermark")
         }
       })
     })
   });
</script>

Notice that I am calling the watermark in the masterpage. This may seem strange, but this stops me from writing redundant code as this will take care of *all* watermarks that I will put into this project due to the versatiliy of jQuery.

All we have to do to complete this registration process is to verify the email which will be the next part to this tutorial. I am also going to show how to add this into a registration popup. The hard part is all finished.


Shout it » Part 2 » Part 3

Keeping all your settings in an XML file in a centralized location

Often times you want to centralize some key data, and avoid a DB call

Most of my applications use a settings.xml file where keep all of my settings. I then use a Settings.cs class to view/update those values. It is great for centralizing data while avoiding unnecessary trips to the DB; change once, it's changed everywhere.

One of the most common things I use in my settings class is for my email settings, just in case I want to change the account or smtp that I am using, here is a sample of what one would look like:
<?xml version="1.0" encoding="utf-8" ?>
<settings>
  <smtp>smtp.gmail.com</smtp>
  <ssl>True</ssl>
  <mail_account>demo@naspinski.net</mail_account>
  <mail_password>Iparty</mail_password>
</settings>

Pretty basic stuff there, now here is the Settings.cs class I use to access it using a little Linq-to-XML:
using System.Linq;
using System.Web;
using System.Xml.Linq;

public static class Settings
{
  public static string Get(string setting)
  { // gets the specified setting
    XElement x = XElement.Load(HttpContext.Current.Server.MapPath("~") + "\\App_Data\\settings.xml");
    return (from p in x.Descendants(setting) select p).First().Value;
  }

  public static void Update(string setting, string value)
  { // changes the specified setting
    string file_location = HttpContext.Current.Server.MapPath("~") + "\\App_Data\\settings.xml";
    XElement x = XElement.Load(file_location);
    XElement xe = (from p in x.Descendants(setting) select p).First();
    xe.Value = value;
    x.Save(file_location);
  }
}

As you can see, I keep my settings in the App_Data folder. Here you can see how I use it in some apps, for example, this is how I use it with my Email.cs class:
public Email(string to, string from, string subject, string body) : base(Settings.Get("smtp"), 587)
{
  this.Credentials = new System.Net.NetworkCredential(Settings.Get("mail_account"), Settings.Get("mail_password"));
  ...

I think you can figure out Settings.Update() as it is basically the same. You can use whatever method to encrypt your data whereever you keep it.


Method of Simplifying Reporting Errors/Success to Users

Since updating users to what is going on is so common, I use a simple group of extensions to simplify my life

There is most likely crap going on in your apps, so there will be success and errors. If the users don't see these things going on, they will assume nothing is. Therefore, it is in your best interest to keep them posted. Since you have to do this basically all the time, it makes sense to make it simple.

I output all of my stuff to an asp:panel control, so I decided just to extend it to handle these conditions.

using System;
using System.Web.UI;
using System.Web.UI.WebControls;

public static class Extensions
{
  public static void Error(this Panel pnl, Exception ex)
  { // reporting extension for Panel indicating error
    try { pnl.Report(false, "Error: " + ex.Message, ex.InnerException.Message); }
    catch { pnl.Report(false, "Error: " + ex.Message, null); }
  }

  public static void Success(this Panel pnl, string headline, string message)
  { // reporting extension for Panel indicating success
    pnl.Report(true, headline, message);
  }

  public static void Report(this Panel pnl, bool successful, string headline, string message)
  { //this is a helper used by the Success and Error extensions in extensions.cs - for ease of error/success reporting
    string msg = "<h5 class='" + (successful ? "success" : "error") + "'>" + headline + "</h5>";
    msg += !string.IsNullOrEmpty(message) ? "<div>" + message + "</div>" : "<br />";
    pnl.Controls.Add(new LiteralControl(msg));
    pnl.Visible = true;
  }
}

Nothing fancy here, Report just takes in a bool which switches the css class to 'success' if true, 'error' if false, and outputs the 'headline' in h5 tags and the 'message' in a smaller line underneath it. You can use this method by itself, but I also have the Error and Success classes to make it even easier for myself. Of course you have to set up your css to work with this, but it shouldn't be too hard. I will often use it like this:
try
{
  //do some crap here
  myReportPanel.Success("Success!", "whatever you did worked");
}
catch(Exception ex) { myReportPanel.Error(ex); }

And that is it, centralizing your error/success handling also makes it easier to change formatting and be consistent throughout your project. Not to mention it makes it extremely easy to use and implement.


An improved Email Class for .Net

Nothing too special, just combining a couple classes for a more logical approach to email

Most all of my websites/projects require some email to be sent. The System.Net.Mail class is great, but to me, sending an email is not a logical process. Also, the fact that you will likely only use one smtp server throughout a project seems to make the SmtpClient setup a bit repetetive to me. With my class you can setup and send an email as simply as this:
new Email("recipient@hotmail.com", "sender@gmail.com", "You stink", "here are the reasons why...").Send();

Now you wouldn't have to do it all in one line, but this demonstrates that the smtp is already taken care of in the background, this simply builds on the SmtpClient class. Here is the code:
using System;
using System.Net.Mail;

public class Email : SmtpClient
{
  public MailMessage Message { get; private set; }

  // keep in mind I don't recommend hard coding these values in, this is just for example

  public Email(string to, string from, string subject, string body) : base("smtp.gmail.com", 587)
  {
    // this next line is only necessary if you want to enable SSL
    this.Credentials = new System.Net.NetworkCredential("some_guy@gmail.com", "crap_nugget");
    this.Message = new MailMessage(from, to, subject, body);
  }

  public void Send()
  {
    try { this.Send(Message); }
    catch (Exception ex) { throw ex; }
  }
}

Notice that I do not recommend hard-coding your username/password and/or smtp server in the class -- but it will work. Also, this example is to use Gmail, but it will work just as well with another SMTP (I use this with a local Exchange server some times).

Just polishing up an already good tool and making it a bit better.