You might be making a lot of unneccessary trips to your DB with Linq-to-SQL

Linq-to-SQL has great use of relations, but lazy loading can cause a huge amount of trips to your DB - implementing eager loading is one answer

After my rant on L2E, Dan from Microsoft got back to me in the comments and pointed out a lot of what I wanted to know. One thing that he brought up was the use of eager loading which I hadn't looked in to; turns out it can be VERY important!

Let's say you are using a GridView and for each row, you are using a relation to the 'states' table to pull the name of a 'state' object:
<asp:BoundField DataField="item.state.state_name" HeaderText="State Name" SortExpression="item.state.state_name" />

Now this will work just fine, but each time you add a row, there is going to be another call to the DB to get the state name. Now if you only have a few rows, no big deal, but what if you are calling 100 records each with a a call to the state name and a country name? It is no longer one trip to get the items, it grows huge! Here is some ugly psuedo-code for understanding:
ONE_SQL_QUERY -> 100_ROWS_RETURNED (1 trip)
FOREACH(ROW row in 100_ROWS_RETURNED)
{
  QUERY_FOR_STATE_NAME (1 trip)
  QUERY_FOR_COUNTRY_NAME (1 trip)
}

That one query has now jumped to 201 database calls (1+100(1+1)) ... that is no small difference! Now, that first query, no matter how big and ugly it is is dwarfed by these petty little calls for one small string. Just imagine what happens if you are calling multiple and nested relations, that one call can easily jump to well over 1000 in no time.

So what to do?

While searching on Linq-to-Entities (yes, I still want to make the switch some time) I came across this: http://blogs.msdn.com/adonet/archive/2008/10/07/migrating-from-linq-to-sql-to-entity-framework-eager-loading.aspx which shows how you deal with this in both L2S and L2E (also answered my last question on my rant against L2E. This is actually a situation where (I think) L2E shines over L2S, but that's a whole other debate. Here is what you would need to do.
dbDataContext db = new dbDataContext();
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<item>(i => i.state);
dlo.LoadWith<item>(i => i.country);
db.LoadOptions = dlo;

What this is doing is telling your DataContext to load those other elements with your every 'item' you load (don't forget to include using System.Data.Linq;). This will obviously make for a larger query to begin with, but it will cut out those multiple return trips that are the real problem!

On an somewhat related note... I am going to force myself to use L2E on my next project.

Using jquery.autosuggest.js with Asp.Net

Simple Auto-Suggest with Asp.Net and jQuery

After getting fed up with the Ajax Control Toolkit's AutoCompleteExtender and it's inability to deal with strings that are numbers, I decided to look to my new friend jQuery. Apparently they decided for you if you are feeding it an array of number strings (i.e. { "001","002", etc.}) that the auto-suggest will strip all leading zeroes... BUT I NEEDED THOSE ZEROES!

The jQuery solution is pretty simple, much easier than I thought thanks to this awesome plugin: jquery.autocomplete.js. All you really need to do is make a simple aspx page that spits out the data you want based on a QueryString, use a little jquery to tie it up with a TextBox and you are all set. Here is how its done:


Make your data retrieval page

This plugin consumes it's data in one form: text, with one item per line. This makes it very easy to use and very versatile. You could simply point it at a static .txt file, feel it a javascript variable, or, what we are going to do, use Asp.Net to get you some filtered data.

This tutorial is going to use two examples, one pulling from a list of numbers 1-1000, and another pulling form an xml file.

First, the code-behind for number.aspx:
protected void Page_Load(object sender, EventArgs e)
{
  if (!string.IsNullOrEmpty(Request.QueryString["q"]))
  {
    int q = 0;
    if (Int32.TryParse(Request.QueryString["q"], out q))
    {
      for (int i = q; i < 1001; i++)
        if (i.ToString().Contains(q.ToString())) Response.Write(i + Environment.NewLine);
    }
    else
      Response.Write("Not a number fool!");
  }
}

Doesn't get much easier than that... simply writes out any numbers that contain the number (not mathematically, though we could do that) passed in the 'q' QueryString, so if you were to pass the url: number.aspx?q=100 you will get the results:
100
1000

simple.

Now we do the same thing, but dig into an xml file with Linq:
protected void Page_Load(object sender, EventArgs e)
{
  string q = Request.QueryString["q"] ?? string.Empty;
  IEnumerable<string> things = from p in XDocumentLoad(path).Descendants("thing")
        where p.Value.Contains(q) select p.Value;
  foreach(string s in things) Response.Write(s + Environment.NewLine);
}

That was actually the hardest part. Notice that on the Response.Write() I used Environment.NewLine - that will make a newline in the proper format for the jquery to digest, a <br /> or a \n will not work with this plugin. What was made is a psuedo-web-service (it's not really becuase it does not output xml).

Tie it to some TextBoxes

Now just make a couple TextBoxes that will use them:
<asp:textbox ID="txtNumbers" runat="server" />
<asp:textbox ID="txtThings" runat="server" />

Now use jQuery to attach the pages we made to the proper TextBox:

$().ready(function() {
  $("#txtNumbers").autocomplete("number.aspx");
  $("#txtThings").autocomplete("thingXml.aspx");
});

And that's it, you have working auto-suggesters; pretty simple. One thing to notice that can give you some headaches in Asp.Net is that I used the IDs for selection in the code, which is fine if you are not using User Controls or Masterpages, but if you are you get those funky '...ctl100_...' ids that are made at runtime, so you might want to select them with a different method, like making a dummy class and using that as a selector in jQuery

That's about it - but don't forget to style your auto-suggest box or it will just be transparent; there is some basic css showing the classes produced by the jquery in the example. This was just enough to get you started; there is a bunch of documentation and other variables, settings, formatting tricks that you can use (this is a really powerful plugin) - you can read up on them here: http://docs.jquery.com/Plugins/Autocomplete.

IMPORTANT: Remember to delete *everything* on your handling .aspx page other than the '@' declaration so you don't have any extra html in there (I think I left it in there on the download...).


Make sure you have consistent page titles on your web site

A simple way to catch inconsistency in your titles

Page Titles are often overlookd aspects of web design and can be a simple way to add a little value and professionalism to your site. Users can use your page title to quickly scan bookmarks or on tabs in their browser. Therefore, I feel it is good practice to have a consistent page title naming scheme across your site.

Often I seem to screw up making titles for my pages or forget to add one altogether, or even worse, have the dreaded 'Untitled Page' that was ever=resent before VS2008 SP1. I use MasterPages so there is a centralized structure to build on, and I used this to come up with a way to provide a consistent page title:
string TITLE_ROOT = "My Site";
protected void Page_Load(object sender, EventArgs e)
{
  this.Page.Title = (string.IsNullOrEmpty(this.Page.Title) || this.Page.Title.Equals("Untitled Page"))
    ? TITLE_ROOT : TITLE_ROOT + " :: " + this.Page.Title;


This way, any page that uses this MasterPage will have a nicely formatted title. If you forgot to set a title it would simply be:
My Site

But, say you titled the page "Welcome New Users!", it would then look like this:
My Site :: Welcome New Users!

An easy way to catch myself from making mistakes as well as making it much more consistent.

C-Sharpener.com - Another Project

Learn to Program Asp.Net/C# is Just Days - Guaranteed!

If you have been here before you have likely noticed the ad banner over to the right of the page. That is one of my newer projects that you can check out at C-Sharpener.com.

It is a video series provided with code and throrough explanations of how to get started in the web application and web programming world. The videos cover everything from setting up your environment (with all free tools) and arrays to things such as AJAX and Linq-to-SQL. If it doesn't work, I give you your money back - it's that simple.



Slick-Ticket Trouble Ticketing/Help Desk System

My first Open-Source Project!

This is a simple, to-the-point system. It was born out of loathing of the system that I was forced to use. It was tested in a live environment with hundreds of users and ultimately de-throned our expensive and bloated ticketing software. I really enjoyed developing this and my users and I really enjoy using it, I hope some other people out there like it.
  • Full Integrated with Active Directory means not another layer of permissions to add
  • Intuitive interface allows users to jump right in
  • Integrated help/faq system for administrators to inform users

Details

  • Utilizes .Net 3.5 (C#)
  • Asp.Net architecture built with Linq-to-SQL
  • Utilizes Asp.Net AJAX and the AJAX Control Toolkit
  • Completely customizable colors/themes
  • Installation program included, just load it on your machine and follow the directions

Requirements

  • SQL 2005 Database (SQLExpress works fine)
  • .Net 3.5
  • Active Directory

It is available for download at CodePlex. I also have a site up at http://slick-ticket.com with a demo site soon on the horizon.

Screenshots (Click for full-size images)

Settings interface for the administrator, including the theme customizer Adding a new ticket User profile, most data is pulled directly frorm AD (though you can change it)
*Also notice the different style with the sidebar on the opposite side
Integrated FAQ/Help section for information sharing Easy interface to view the tickets you are interested in View/comment a ticket in progress
*This is Licensed under the GNU General Public License version 2 (GPLv2)

Shout it kick it on DotNetKicks.com

Improved SaveAs() Method on the FileUpload Control

This extension handles things such as duplicate filenames and on-the-fly directory creation

Previously I showed how to save multiple files of the same name which I have used many times, so much in fact that I figured I would make a universal drop-in extension for it.

This new extension improves on the SaveAs() method in a few ways:
  • If a directory doesn't yet exist, it will be made
  • If a file already exists with the same file name, an incremented tag will be added: abc.pdf, abc[1].pdf, abc[2].pdf, etc.
  • It returns the name the file was saved with

There are two ways to call it:
  • FileUploadControl.SaveAsNoOverwrite() :: will save to the current directory with the 'FileName' property of the 'FileUpload'
  • FileUploadControl.SaveAsNoOverwrite(path) :: where 'path' is the path to save to

Here is the code:
public static string SaveAsNoOverwrite(this FileUpload up, string saveas)
{
  string[] split = saveas.Split(new char[] { '\\', '/' }, StringSplitOptions.RemoveEmptyEntries);

  string directory = string.Empty;
  for (int i = 0; i < split.Length - 1; i++) directory += split[i] + "\\";

  string filename = split[split.Length - 1];

  //saves to current directory if only filename is specified
  if (string.IsNullOrEmpty(directory)) directory = HttpContext.Current.Server.MapPath(".") + "\\";
  //creates directory if it does not exist
  else if (!Directory.Exists(directory)) Directory.CreateDirectory(directory);

  string[] fileNameSplit = filename.Split(new char[] { '.' });
  string ext = "." + fileNameSplit[fileNameSplit.Count() - 1];
  string prefix = filename.Substring(0, filename.Length - ext.Length);

  int count = 1;
  // if the files already exists, this will append a [x] where x is 0+(number of files existing with the same name)
  while (File.Exists(directory + filename))
  {
    filename = prefix + "[" + count.ToString() + "]" + ext;
    count++;
  }
  up.SaveAs(directory + filename);
  return filename;
}

public static string SaveAsNoOverwrite(this FileUpload up)
{
  return up.SaveAsNoOverwrite(up.FileName);
}

Getting started with Linq-To-Entities tutorial

The transition from Linq-to-SQL to .Net's Entities framework is incredibly simple

In my humble opinion, Linq is easily the greatest thing .Net has come out with in the past few years, and along with it, Linq-to-SQL (L2S) was a godsend. Being quick to hop on the L2S bandwagon, I quickly became a huge fan of the framework, it is amazing easy and useful. That being as it is, I was quite disappointed when I heard that Linq-to-SQL was no longer going to be advanced at all. Now, it is surely not dead, it is still very usable and effective at what it does, but I like to stay with frameworks that will be actively advanced and fbug-fixed. So I decided to make the jump to Linq-to-Entities (L2E), and it was suprisingly simple.

This guide should be a good starting point for anyone whether or not they are familiar with L2S, and a quick 'jumping guide' to L2S developers. Here's how to get started:

Make your ADO.NET Entity Data Model (.edmx file)

This is comparable to the .dbml file that you made with L2S.
  • Right click on your App_Code folder and select New Item
  • Select ADO.NET Entity Data Model and name it (I left the default Model.edmx for the example)
  • Choose Generate from database and click Next
  • Choose your database from the dropdown and choose the name to save the ConnectionString as and click Next
  • Choose all the things you want included, I just chose tables, but you may want to include views and SPs if you have them
  • *Be sure to remember the Model Namespace you choose for your .edmx (I will use DatabaseModel for the example) - click Finish

Now it will take a moment to run through and produce your .edmx; when it is done you will see a nice little representation of your tables reminiscent of the .dbml display in L2S:

Access your .edmx

Now we simply access the .edmx much like we did with L2S. You must remember to add Using DatabaseModel (or whatever your namespace was) to the code where you are using it. Now we will make a DatabaseModel object and use it to add a 'product' to the database. First, you need to make your Entity Access Object:
DatabaseEntities db = new DatabaseEntities();

Then you can use it to enter an object:
products p = new products();
p.product_name = "Word Processing Software";
p.price = 99;
p.stock = 100;
db.AddToproducts(p);
db.SaveChanges();

Once again, if you are familiar with L2S, this is almost the same exact thing! If you are new, this is very straight-forward and should be easy to understand:
  • Make an object of a type inside your database
  • Fill the object up with data
  • Put it into the database
  • Save the changes

Now L2E one-ups L2S and makes data entry even easier, this will accomplish the same as above:
db.AddToproducts(products.Createproducts(0, "Accounting Software", 300, 125));
db.SaveChanges();

Notice that the 'product_id' is entered as '0', that is because it is auto-incrementing, so it doesn't matter what I put there, it will be ignored. Now I don't know about you, but that little bit there will save me hundreds of lines of code! I am starting to like L2E already!

Display your data

Now that we have put a few objects in to the database, we can go and check it out. Later we will dig in with code, but first we will use a LinqDatasource/GridView:
  • Drag a LinqDataSource (LDS) on to the page and click Configure Data Source from the little square on the upper right of the LDS
  • Choose your Object Model and click Next
  • Choose your table and click Finished
  • Drag a GridView (GV) on to your page
  • In the GV option box, choose your datasource from the dropdown

now just view your page:

Modify Data

Now that we have seen how to put in data, we will modify some; once again, L2E makes it trivially simple:
products edit = (from p in db.products where p.product_id == 2 select p).First();
edit.product_name = "Account Software v2";
db.SaveChanges();

The Linq statement is EXACTLY like it would be in L2S, and just like in L2S, you can shorten up this with some Lambda integration; this will accomplish the same thing:
db.products.First(p => p.product_id == 2).product_name = "Accounting Software v2.1";
db.SaveChanges();

Deleting Items
Now that we have seen Insert and Update, the next logical step is Delete, and it is just as easy. Let us delete the 'Word Processing Software' frorm the table:
products del = (from p in db.products where p.product_id == 1 select p).First();
db.DeleteObject(del);
db.SaveChanges();

And the same exact thing shorthand with Lambdas:
db.DeleteObject(db.products.First(p => p.product_id == 1));
db.SaveChanges();

Once again, I have to say I like the approach that L2E takes of that of L2S, as it is not necessary to specify which table to delete from, as an object can only be deleted frorm the table that it is in.

Using your database relations

As in L2S, L2E takes great advantage of well designed databases and relations. If you noticed up above, the two tables in my database have a 1 to many relation frorm product to reviews. Each element in the 'reviews' table is required to relate to an element in the 'products' table (column 'product_id'). Let's fill the DB with a few more products and some reviews; a lot is going to go on here:
products p1 = db.products.First(p => p.product_id == 2);
products p2 = products.Createproducts(0, "Strategy Game", 49, 99);
products p3 = products.Createproducts(0, "Sports Game", 39, 99);
db.AddToproducts(p2);
db.AddToproducts(p3);

reviews r1_1 = reviews.Createreviews(0, "Much Improved", "this is a much better version", "Bill Brasky");
r1_1.productsReference.Value = p1;
reviews r2_1 = reviews.Createreviews(0, "Terrible", "worthless", "Dirk Digler");
r2_1.productsReference.Value = p2;
reviews r3_1 = reviews.Createreviews(0, "Great Game", "very tough AI", "Wonderboy");
r3_1.productsReference.Value = p3;
reviews r3_2 = reviews.Createreviews(0, "Very Fun", "the Bears rule", "Mike Ditka");
r3_2.productsReference.Value = p3;

db.AddToreviews(r1_1);
db.AddToreviews(r2_1);
db.AddToreviews(r3_1);
db.AddToreviews(r3_2);

db.SaveChanges();

Now to explain what just happened:
  • The first line gets an object of type products where product_id == 2 (this is 'Accounting Software v2.1')
  • The next two lines create new products
  • The next two submit those into the database

Now we have a total of 3 objects in the 'products' table and none in the 'review' table, that is where the next 8 lines come in. If you break up those 8 lines into pairs of two, you can see they are all the same thing really:
  • Make a new object of type reviews
  • Assign its foreign key reference to a products object

Since the database requires a relation between these two tables, you must be sure to set the reference. The last lines just submit everything and commit them to the database.

Now that that is in there, you can use the real power of relations in L2E. Once again, it is almost the same as L2S:
foreach (products p in (from _p in db.products select _p))
{
  Response.Write("<h3>" + p.product_name + " - $" + p.price + "</h3>");
  Response.Write("<b>Reviews:</b><div style='border:1px solid navy;padding:5px;'>");
  p.reviews.Load(); // this loads all the reviews that relate to the product p
  foreach (reviews r in p.reviews)
    Response.Write("<b>" + r.title + " - " + r.reviewer + "</b><br />" + r.review + "<br />");
  Response.Write("</div><br />");
}

And this is what you get:
Yes, I know I used the ugly 'Response.Write', and the output is hideous... but this is just a demo people! As you can see, it is incredibly easy to iterate through all of the reviews of a products, this big difference being that you need to call the .Load() method or they will not be populated. Now this is both a blessing and a curse; this is very efficient as it only loads the related objects if need be, but... it is soemthing that is easy to forget and may leave you scratching your head. I think I like it.

Now relations also work the other way as well. Say you have a reviews object, and you want to know the price of the products it is related to. No need to actually look up the products object, you just call the relation:
reviews r = db.reviews.First(_r => _r.review_id == 3);
r.productsReference.Load();
Response.Write("Price: $" + r.productsReference.Value.price.ToString());

Once again, notice that you have to Load() the reference, or you will get nothing.

Now that should get you started. Like I said, if you are familiar with L2S, this transition should be no problem, and if you are new to this whole Linq arena, this should be simple to pick up. This new ADO is getting more and more impressive the more MS works on it. I can't even imagine goging back to the old methods...

Simplify setting your DropDownLists to a specific value

An extension to quickly and easily set your DropDowns while avoiding errors

Sometimes you want to set your DropDownList to a value that may or may *not* be in the DropDownList, but setting that can be a bit tricky as it will often error out. This extension will avoid that and give you a simple way to set your DropDownLists wihtout all the extra code:
public static void Set(this DropDownList ddl, string findByVal)
{ // attempts to set a DDL to the 'findByVal'
  try { ddl.SelectedIndex = ddl.Items.IndexOf(ddl.Items.FindByValue(findByVal)); }
  catch { };
}

Now just call it from your DropDownList:
SomeDropDownList.Set("Some String Value");

And it will attempt to set your DropDownList to that value, but *not* error out if it doesn't exist.

Simple Dynamic Sorting Headers for GridView using indication arrows

A simple centralized way to make a nice flipping indicator (arrow) of which way you are sorting on a GridView

When sorting with gridviews, it is nice to have an indicator of which direction you are sorting on which field like the use at Yahoo! Autos. To copy this idea, I simply made 3 Css classes 'sort', which is a base class with no backgorund image, 'up' which has an up arrow, and 'down' which has a down arrow in it. I made sure to put the backgorund image in just once, and push the text away so it can show through. Also, since these will be applied to the Header element of the GridView, you have to make sure you declase '.class a':
.up a, .down a, .sort a { display:block; padding:0 4px 0 15px; }
.up a, .down a { color:#8F5F00; }
.sort a:hover { background:#ffcc66; }
.up a { background:url(../images/up.gif) left no-repeat; }
.up a:hover { background:url(../images/up.gif) left no-repeat #ffcc66; }
.down a { background:url(../images/down.gif) left no-repeat }
.down a:hover { background:url(../images/down.gif) left no-repeat #ffcc66; }

Now, I made a function in a helper Class that will take in the same arguments that a GridView Sorting event throws, that way the transition will be easy.
// This is used to flip the sorting arrow up/down
// Base Css class is 'sort', the Ascending Css Class is 'up' and Descending is 'down'
public static void GVSort(object sender, GridViewSortEventArgs e)
{ // call on sort and sets the sorted field to the proper Css Class, while setting all others to the base class
  string BASE = "sort";
  string UP = "up";
  string DOWN = "down";
  GridView g = (GridView)sender;
  for (int i = 0; i < g.Columns.Count; i++)
  {
    var c = g.Columns[i];
    c.HeaderStyle.CssClass = c.HeaderStyle.CssClass.Replace(UP, BASE).Replace(DOWN, BASE);
    if (c.SortExpression.Equals(e.SortExpression))
    {
      c.HeaderStyle.CssClass =
        e.SortDirection.Equals(System.Web.UI.WebControls.SortDirection.Ascending) ?
          c.HeaderStyle.CssClass.Replace(BASE, UP).Replace(DOWN, UP) :
          c.HeaderStyle.CssClass.Replace(BASE, DOWN).Replace(UP, DOWN);
    }
  }
}

Now say I had this in a class utils.cs I would just call it one the GridView Sorting event:
protected void gv_Sorting(object sender, GridViewSortEventArgs e)
{
  sao.GVSort(sender, e);
}

And there you have it! You can call that from every Gridview in your application that you want to look similar - code only once!

Convert DateTime to Military Time

Good ol' military time


To continue on my posts about extend existing classes (I have been doing these a lot at my new job), here is a simple extension to make DateTime.ToMilitaryString() to output a military time such as: 21 DEC 08 : 16:22:34 - it's very simple actually:
public static string ToMilitaryString(this DateTime dt)
{
  string time = dt.ToString("dd MMM yy : hh:mm:ss");
  return time.Substring(0,12) + dt.Hour + time.Substring(14,6);
}