- Posted by matt on June 3, 2008
I recently had a need to track referral codes for ad campaigns as they came in through Google ad clicks. This same module can easily be used to handle affiliate codes as they come in. This module will be executed at each request, will check for a query variable, and store the value in a cookie. You can then retrieve the cookie where ever you need it in you app.
The HTTP Module
/// <summary>
/// Adds a cookie to the clients machine to track what ref code they came in through
/// </summary>
public class RefCodeExtraction : IHttpModule
{
public void Dispose()
{
}
public void Init(HttpApplication context)
{
context.BeginRequest += new EventHandler(context_BeginRequest);
}
void context_BeginRequest(object sender, EventArgs e)
{
HttpContext context = (sender as HttpApplication).Context;
//replace "q" with your variable name
if (!String.IsNullOrEmpty(context.Request.QueryString["q"]))
SaveRefCodeToCookie(context.Request.QueryString["q"], context);
else//ADD A DEFAULT CODE
SaveRefCodeToCookie("ORGANIC", context);
}
private void SaveRefCodeToCookie(string refCode, HttpContext context)
{
try
{
context.Response.Cookies.Remove("RefCookieName");
HttpCookie c = new HttpCookie("RefCookieName");
c.Value = refCode.EndsWith("/") ? refCode.TrimEnd('/'):refCode;
c.Expires = DateTime.Now.AddDays(30);
context.Response.Cookies.Add(c);
}
catch {}
}
}Activate the Module
To activate this module, place the class in your App_Code directory and add the class name to the web.config file under the HttpModules section. For example:
<httpModules>
<add name="RefCodeExtraction" type="RefCodeExtraction"/>
<add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
</httpModules>It will now be executed at the beginning of each request.
Retrieve the Ref Code from the Cookie
To retrieve the code later on, just call:
string refCode = HttpContext.Current.Request.Cookies["RefCookieName"].Value;
Conclusion
How you use the referral code or ad code is up to you. In my case, the ad campaign code is attached to a google ad using the variable "q" followed by the campaign code. This is set in the ad management system and allows for our web site to track and persist which ad codes are generating conversions. This could be adapted to pay out affiliates for referrals or whatever else you might need.
The code is below.
RefCodeExtraction.cs (1.24 kb)
- Posted by matt on May 28, 2008
A very common requirement for web applications is that they be configurable in an easy way. A lot of times it doesn't make sense to use a database to store some simple settings for your web site, especially if the application doesn't require one in the first place. There are a ton of ways to accomplish this task, but it seems to me that XML is perfectly suited for this problem. I don't mean to presume that it is better than other solutions but in case it fits the bill for your project, here is once way it can be used.
In this post I'll demonstrate one simple way to use XML to retrieve, edit, and save settings for your ASP.NET application.
The Scenario
You're building an ASP.NET application in C# that needs to have some user configurable settings. You don't want to use a database to store these settings and you want the ability to edit and save the settings through a web page. The edit page should be created dynamically from the settings contained in the XML file so that the page will not have to be updated if a setting is added.
Limitations of this example
To begin, this is not a "killer" solution to settings management. You can find a much more capable approach to XML settings by viewing the source of BlogEngine.NET and its setting management system.
This example will not allow for a user to add settings on their own that will be automatically recognized by the code. If a new setting is to be added, it will need to have a property set up and defined in the Settings class as well as be added to the Settings.xml file.
This example will not cover the vast possibilities for sub-settings you can obtain using element attributes and many other XML features.
Mostly, this is just a demonstration of how to accomplish the simple goal of storing and retrieving settings in XML.
Step 1: Setup
To begin, I would create an XML file in the App_Data directory of your application. For this example, I will call the file Settings.xml. If you're using Visual Studio just right click the App_Data folder and Add a new item. The file will be pre-filled with the standard xml encoding:
<?xml version="1.0" encoding="utf-8" ?>
You should then add a root element to the XML file. I chose the root element to be <settings></settings>. Within those tags, all of your settings will be listed out as child elements. So far the file looks like this:
<?xml version="1.0" encoding="utf-8" ?>
<settings>
</settings>
Save the file to the App_Data folder.
Step 2: Create the Settings.cs class
Next, create a Settings class in your App_Code folder (or name it whatever you like). This class will be responsible for retrieving and updating the data in the Settings.xml file. Set the class to static so that it is only loaded once for the application.
Step 3: Define the setting elements and properties
You should now begin entering the settings you'll be using for the application. In the Settings.xml file, create an element for each of the settings you will need for your program and pre-populate them with defaults if you like.
Tip: If your setting contains any markup or unsafe characters, surround the settings in the <![CDATA[ your_setting_value_here ]]> tag to avoid xml errors.
I've added some example setting elements to my Settings.xml file below:
<?xml version="1.0" encoding="utf-8"?>
<settings>
<MaxImageUploadSizeKB>1000</MaxImageUploadSizeKB>
<ThumbnailWidth>100</ThumbnailWidth>
<ThumbnailHeight>90</ThumbnailHeight>
<UploadedImageDirectory>~/images/postImages/</UploadedImageDirectory>
<SessionExpiresMinutes>20</SessionExpiresMinutes>
</settings>Next, for each of the settings you added to the Settings.xml file, create a property for it in the Settings.cs class. I'm assuming >= .NET 2.0 for the code here.
public static class Settings
{
/// <summary>
/// The maximum file size in KB for uploaded images. [Default: 1000KB]
/// </summary>
public static int MaxImageUploadSizeKB { get; set; }
/// <summary>
/// The whole number integer width of the image thumbnails [Default:100]
/// </summary>
public static int ThumbnailWidth { get; set; }
/// <summary>
/// The whole number integer height of the image thumbnails [Default:90]
/// </summary>
public static int ThumbnailHeight { get; set; }
/// <summary>
/// The directory to upload and read post images from/to [Default: ~/images/postImages/]
/// </summary>
public static string PostImageDirectory { get; set; }
/// <summary>
/// The number of minutes before a checkout session expires. [Default: 20]
/// </summary>
public static int SessionExpiresMinutes { get; set; }
}Step 4: Read the XML file into the properties
I'm sure I will catch some heat for using a DataSet to read the XML file as it seems to go against the whole point of using an XML file in the first place, but I like the simplicity and readability of the code it produces...Plus it's dead easy. The following function will assign the properties in the Settings class to the values in the XML file:
/// <summary>
/// Load the settings from Settings.xml
/// </summary>
public static void LoadSettings()
{
DataSet ds = new DataSet();
ds.ReadXml(HttpContext.Current.Server.MapPath("~/App_Data/Settings.xml"));
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
MaxImageUploadSizeKB = Int32.Parse(dr["MaxImageUploadSizeKB"].ToString() ?? "1000");
ThumbnailWidth = Int32.Parse(dr["ThumbnailWidth"].ToString() ?? "100");
ThumbnailHeight = Int32.Parse(dr["ThumbnailHeight"].ToString() ?? "90");
PostImageDirectory = dr["UploadedImageDirectory"].ToString() ?? "~/images/postImages/";
SessionExpiresMinutes = Int32.Parse(dr["SessionExpiresMinutes"].ToString() ?? "20");
}
}Note that the ?? operator will use the value following the ?? if the expression before it is null. This allows you to hard code defaults if you desire instead of having to do so in the XML file.
Now that you have the properties filled with data from the XML file, you can use them in your application just by referencing Settings.ThumbnailWidth for example.
Step 5: Viewing / Editing the Settings file in a web page
To display the current settings in a way that will allow them to be editable, but also allow the page to work regardless of changes to the available settings, here is something you could do.
- Create a normal .aspx page for the purpose of editing the settings.
- Add a table to the page and set runat="server" and give it an ID.
- On the Page_load event, call a function to dynamically create the setting label along with a text box for changing the value.
- Provide a Save button that will write the changes back to the XML file.
The following function will accomplish the loading task (there are some css styles in this function that are not included here):
private void LoadSettings()
{
XmlDocument xDoc = new XmlDocument();
try
{
xDoc.Load(Server.MapPath("~/App_Data/Settings.xml"));
}
catch { xDoc.LoadXml("<Settings></Settings>"); }
int alt = 0;
XmlNodeReader nodeReader = new XmlNodeReader(xDoc);
while (nodeReader.Read())
{
if (nodeReader.NodeType == XmlNodeType.Element && nodeReader.Name != "settings")
{
HtmlTableRow tr = new HtmlTableRow();
HtmlTableCell c1 = new HtmlTableCell();
HtmlTableCell c2 = new HtmlTableCell();
TextBox tb = new TextBox();
c1.InnerHtml = "<strong>" + nodeReader.Name + "</strong>";
if (alt % 2 == 0)
{
c1.Attributes.Add("class", "postDetailsLabel");
c2.Attributes.Add("class", "postDetailsReview");
}
else
{
c1.Attributes.Add("class", "postDetailsLabel");
c2.Attributes.Add("class", "postDetailsReview");
}
c1.Attributes.Add("style", "padding-right:20px;");
tb.ID = nodeReader.Name;
tb.Text = nodeReader.ReadString();
tb.Width = 400;
c2.Controls.Add(tb);
tr.Controls.Add(c1);
tr.Controls.Add(c2);
settingsTable.Rows.Add(tr);
alt++;
}
}
nodeReader.Close();
}You could use the meat of that function in a number of ways to produce the html markup that fits your style. This function will produce a table with a row for each setting, a column for the setting label, and a column with a text box holding the current setting value (editable).
Step 6: Saving the setting to the XML file
Finally, the edit page should be able to save the settings back to the XML file.
private void UpdateSettings()
{
string filename = "~/App_Data/Settings.xml";
XmlWriterSettings writerSettings = new XmlWriterSettings(); ;
writerSettings.Indent = true;
//------------------------------------------------------------
// Create XML writer against file path
//------------------------------------------------------------
using (XmlWriter writer = XmlWriter.Create(Server.MapPath(filename), writerSettings))
{
writer.WriteStartElement("settings");
foreach (HtmlTableRow row in settingsTable.Rows)
{
foreach (HtmlTableCell cell in row.Cells)
{
foreach (Control ctrl in cell.Controls)
{
if (ctrl is TextBox)
{
TextBox tb = ctrl as TextBox;
writer.WriteElementString(tb.ID, tb.Text.Trim());
}
}
}
}
writer.WriteEndElement();
}
Settings.LoadSettings();
}This function iterates through each table row which should represent a setting per row and gets the value from the text box control. The value is then written to the XML file as an element value using the text box id as the element name. Finally, the LoadSettings() function is called to reload the app settings with the new values.
Conclusion
You can use this technique to store all kinds of data in XML files. I'm sure many people will disagree with this method of settings management but the concepts presented here can be used for many different types of data. The core functions in this post will probably make it back into my helper class project in some form or another.
- Posted by Matt on May 14, 2008
I realized a while ago that I was breaking the "DRY" (Don't repeat yourself) rule pretty severely when looking at my various projects as a whole. I generally focus on not repeating code within an application but I have not been careful enough with this rule when starting new projects. Many times when I need a particular function that I have written before I will just go hunt it down and copy the code into my new project. It has recently become a problem because I now have the same function littered throughout my different programs with various modifications made to each!
It's time for me to build a utility library that will become the main source for commonly used functions in my applications. The main need that I saw for this library was the simple but tedious task of creating an email message and sending it along via a web contact form. Just about every .NET web site I've ever built has needed this functionality and each time I created a new MailMessage and Email class to handle the job. This was the first thing I added to my new Utility Library.
The purpose of this post is to try and get some feedback on which functions you would include in such a library and which you would leave out. The last thing I want to do is create some bloated set of code filled with methods that are rarely used. If anyone is interested in seeing how I am going about creating this library (or would like the actual library) just let me know in the comments and I'll do my best to help out.
Here is the initial structure and the classes I'm starting with:
Each class contains a few functions that I find myself using frequently. Here is the class diagram for the few classes I have so far:
Again, if anyone wants the actual code or libraries let me know.
Help!
If you were building a utility library, what would you include or exclude? Anyone? Bueller...?
EDIT
I've added the source code for the project to the post for anyone interested. Since the post I've added a couple more functions to the library.
SevenLabs.Utilities.zip (35.55 kb)
- Posted by matt on May 9, 2008
The built in function for creating thumbnails in ASP.NET is extremely convenient and very simple to implement.
int width = 190;
int height = 190;
Bitmap source = new Bitmap("c:\someimage.gif");
System.Drawing.Image thumb = source.GetThumbnailImage(width,height,null,IntPtr.Zero); 
(31.7k)
The trouble is that it produces relatively poor quality results and excessively large file sizes. The thumbnails tend to look very muddy when using this route, but many times it's good enough for whatever your needs may be.
An Alternative
The alternative that I use regularly involves redrawing the image using the System.Drawing.Graphics library. It is very simple to implement but produces superior results if for no other reason than its file size. The following is the standard function I use for creating thumbnails.
public static Bitmap CreateThumbnail(Bitmap source, int thumbWi, int thumbHi, bool maintainAspect)
{
// return the source image if it's smaller than the designated thumbnail
if (source.Width < thumbWi && source.Height < thumbHi) return source;
System.Drawing.Bitmap ret = null;
try
{
int wi, hi;
wi = thumbWi;
hi = thumbHi;
if (maintainAspect)
{
// maintain the aspect ratio despite the thumbnail size parameters
if (source.Width > source.Height)
{
wi = thumbWi;
hi = (int)(source.Height * ((decimal)thumbWi / source.Width));
}
else
{
hi = thumbHi;
wi = (int)(source.Width * ((decimal)thumbHi / source.Height));
}
}
// original code that creates lousy thumbnails
// System.Drawing.Image ret = source.GetThumbnailImage(wi,hi,null,IntPtr.Zero);
ret = new Bitmap(wi, hi);
using (Graphics g = Graphics.FromImage(ret))
{
g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;
g.FillRectangle(Brushes.White, 0, 0, wi, hi);
g.DrawImage(source, 0, 0, wi, hi);
}
}
catch
{
ret = null;
}
return ret;
}
(10.5k)
This function is handy because it's parameters include a flag for maintaining the aspect ratio of the image along with the thumbnail size you would like. The thumbnail magic happens in this portion of the code:
using (Graphics g = Graphics.FromImage(ret))
{
g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;
g.FillRectangle(Brushes.White, 0, 0, wi, hi);
g.DrawImage(source, 0, 0, wi, hi);
}This method is slightly slower but the results are hard to ignore as illustrated by the comparison below:
(31.7k) (10.5k)
Pretty nice improvement in both file size and quality I would say, but....
We can do even better
Now we can add into the mix some JPEG compression and really optimize the results. I won't pretend to fully understand how the JPEG compression code below works, but it sure does the trick.
//Configure JPEG Compression Engine
System.Drawing.Imaging.EncoderParameters encoderParams = new System.Drawing.Imaging.EncoderParameters();
long[] quality = new long[1];
quality[0] = 75;
System.Drawing.Imaging.EncoderParameter encoderParam = new System.Drawing.Imaging.EncoderParameter(System.Drawing.Imaging.Encoder.Quality, quality);
encoderParams.Param[0] = encoderParam;
System.Drawing.Imaging.ImageCodecInfo[] arrayICI = System.Drawing.Imaging.ImageCodecInfo.GetImageEncoders();
System.Drawing.Imaging.ImageCodecInfo jpegICI = null;
for (int x = 0; x < arrayICI.Length; x++)
{
if (arrayICI[x].FormatDescription.Equals("JPEG"))
{
jpegICI = arrayICI[x];
break;
}
}This code will set up the encoderParameters needed for saving the new compressed thumbnail. The quality[0] value is where you set the compression level. I've had success going as low as a value of 40 for some applications, but when quality is a major requirement I find 75 to do very well. To use this engine you would execute the JPEG Compression code before you save your thumbnail, then use its encoderParamaters as a parameter when saving. For example:
System.Drawing.Image myThumbnail = CreateThumbnail(myBitmap,Width,Height,false);
//Configure JPEG Compression Engine
System.Drawing.Imaging.EncoderParameters encoderParams = new System.Drawing.Imaging.EncoderParameters();
long[] quality = new long[1];
quality[0] = 75;
System.Drawing.Imaging.EncoderParameter encoderParam = new System.Drawing.Imaging.EncoderParameter(System.Drawing.Imaging.Encoder.Quality, quality);
encoderParams.Param[0] = encoderParam;
System.Drawing.Imaging.ImageCodecInfo[] arrayICI = System.Drawing.Imaging.ImageCodecInfo.GetImageEncoders();
System.Drawing.Imaging.ImageCodecInfo jpegICI = null;
for (int x = 0; x < arrayICI.Length; x++)
{
if (arrayICI[x].FormatDescription.Equals("JPEG"))
{
jpegICI = arrayICI[x];
break;
}
}
myThumbnail.Save(Path.Combine(SavePathThumb, fileName), jpegICI, encoderParams);
myThumbnail.Dispose();
(2.39k)
Which still looks pretty darn good for 2.39k.
Conclusion and final comparison
Here is the final comparison between the 3 thumbnails going from largest file size to smallest:
Largest = 31.7k
Uncompressed redraw = 10.5k (67% smaller)
Compressed redraw = 2.39k (92% smaller)
It's hard to ignore those results. The source code for the thumbnail function and the JPEG compression engine are below.
ThumbnailGenerator.cs (1.97 kb)
JPEGCompressionConfig.cs (969.00 bytes)
- Posted by matt on April 16, 2008
The .NET framework (starting with v2.0) has some great built in role and member based authentication that is easy to tap with a little setup. Recently I was setting up some XML based user authentication for members only (no roles) and was having trouble finding a relevant guide as to how you would go about setting this up. I decided to turn to the BlogEngine.Net source for some guidance and was able to get a complete solution online without too many hang ups.
What is the goal?
The goal here is to set up basic user authentication using an XML file for user data storage and the built in .NET Login controls. The solution should tie in with the .NET MembershipProvider to take advantage of all the work that has already been done in the framework.
Steps to complete
- Create an XMLMembershipProvider (or download this one)
- Create a Users.xml file in the App_Data folder
- Configure the web.config to use the provider
- Build the Login page
Step 1. Create an XMLMembershipProvider
The first thing you need is a class that inherits from the .NET MembershipProvider which will let the program know how to interface with your data. This is probably the most complicated part, but fortunately the BlogEngine.NET code saves us a lot of time since the provider has already been written for that system. It does not make use of all of the available MembershipProvider features but is sufficient for basic authentication. You can write your own solution if you prefer but this code will help get you going, just add it to your App_Code folder.
The source code can be downloaded here: XmlMembershipProvider.cs
Step 2. Create a Users.xml file
The Users.xml file will store all of the account data for the users. Create a file under the App_Data folder and name it Users.xml.
The file should be in the following format:
<?xml version="1.0" encoding="utf-8" ?>
<Users>
<User>
<UserName>user1</UserName>
<Password>password</Password>
<Email>user1@demo.com</Email>
<LastLoginTime>2007-12-05 20:46:40</LastLoginTime>
</User>
<User>
<UserName>user2</UserName>
<Password>password</Password>
<Email>user2@demo.com</Email>
<LastLoginTime>2007-12-05 20:46:40</LastLoginTime>
</User>
</Users>Step 3. Configure the web.config file to use the provider
The web.config file needs to be set up to know how to access your custom membership provider from step 1.
First set the authentication mode to "Forms" and configure the form values. You should set the name= attribute to the cookie name you want to use for the authentication. You should set the loginURL= attribute to the virtual path to your login page.
Next add the membership section and set the defaultProvider= attribute to the name of your provider class. Add a provider to the section and set the type= attribute to the namespace or class name of your xml provider. Finally, specify the xmlFileName= attribute by setting it to the virtual path of the Users.xml file
<system.web>
<!-- Authentication -->
<authentication mode="Forms">
<forms timeout="129600" name=".XMLAUTH" protection="All" slidingExpiration="true" loginUrl="~/Login/Default.aspx" cookieless="UseCookies"/>
</authentication>
<membership defaultProvider="XmlMembershipProvider">
<providers>
<clear/>
<add name="XmlMembershipProvider" type="XmlMembershipProvider" description="XML membership provider" xmlFileName="~/App_Data/Users.xml"/>
</providers>
</membership>
</system.web>Step 4. Build the Login Page
Now that your application is wired up to use your new provider and xml data file, you need to set up a page for the user to authenticate from. This is normally done at ~/Login.aspx or at ~/Login/Default.aspx. I prefer the latter as it's easier for people to remember a path without an extension.
Create your page and drag an <asp:Login /> control from your toolbar onto the page. At minimum you need to specify a DestinationPageUrl= for your user to be redirected to upon successful sign in, and an OnAuthenticate method. To assign the OnAuthenticate method, double click on the control in the design view to be taken to the method in the code view.
On the page, include the System.Web.Security library to easily access the Membership class. You can now use the following simple method to authenticate a user: (this is in the code behind of the login page)
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
public partial class Login_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
if (Membership.ValidateUser(Login1.UserName, Login1.Password))
FormsAuthentication.RedirectFromLoginPage(Login1.UserName, true);
}
}The Membership.ValidateUser() function will access your XmlMembershipProvider which will check the supplied credentials against the xml file. If the username and password matches it will return true. If they provided the proper credentials, you can use the FormsAuthentication.RedirectFromLoginPage() function to add a cookie to the browser indicating the person has been authenticated. The second parameter (true) indicates that you do want the cookie to be placed on the browser so that the authentication carries throughout the site.
How to determine authentication on the site
Now that you have the user authentication taken care of, it's a simple task to determine if a user is logged in on the rest of your sites pages. To find out, you can simply call the following function:
if (Page.User.Identity.IsAuthenticated)
{
//User is logged in
}
else
{
//User is not logged in
}You can also make use of the other built in .NET login controls like <asp:LoginStatus />, <asp:CreateUserWizard />, <asp:PasswordRecovery /> etc.
- Posted by Matt on April 15, 2008
Optimizing a database can be one of the most difficult things to do. There are so many factors that come in to play that it's hard to gauge your results and identify the actual bottleneck. From network and hardware issues to poorly configured server settings, things can get frustrating really fast. There are far too many variables to consider as it is without having to worry about poorly written SQL statements.
Fortunately, inefficient SQL is the easiest performance problem to solve and typically produces the most dramatic results in an application that rely's heavily on a database. There are plenty of ways to write bad SQL but today I want to focus on an issue I come across regularly with colleagues, even some of the more experienced ones.
The dreaded subquery. Boy can they be misused and expensive. Don't get me wrong, a subquery is an extremely powerful feature of modern databases but more often than not (in my experience) it is used improperly. There are some situations when a subquery is the only answer to the problem and when that is the case I'm real pumped that it exists.
What's a subquery?
A subquery is a select statement within another select statement. For example:
Select * from orders where orders.customerID IN (Select ID from customers where customers.state = 'NY')
The statement inside the ( ) will execute separately from the main query and it's results will be held in memory to be used for the original query. This query will give you all orders whose customers state is NY.
What's bad about subqueries?
Not a lot on their own, but if you're not careful they can cause serious performance problems. Depending on your goal you can quickly get into several nested sub query statements, each of which rely on the furthest inner query to complete first. If any of the queries are poorly written, say missing a limiting WHERE condition, the entire chain collapses. Subqueries do not take advantage of the database engine properly either and typically produce much slower results.
Have no fear, the GROUP BY clause is here!
In the majority of the situations that I have seen, a sub query could be replaced with a JOIN statement or with a GROUP BY clause. I don't want to get into a long discussion about how JOIN operations work but I thought it would be helpful to show an example GROUP BY statement. Most times when a sub query is used it's only because the writer is unaware of the group by clause or how to use it.
Suppose you have a database table holding a bunch of orders and you have a STATE column and an ORDER_AMOUNT column.
You want to know the total order amount per state in 2007.
This could be accomplished using a subquery in the following way:
select STATE as oState, (SELECT SUM(ORDER_AMOUNT) from OrderData
where RECEIVED_DATE between '20070101' and '20080101' and STATE = oState) as Dollars
from OrderData where RECEIVED_DATE between '20070101' and '20080101' order by oState asc
That code will work, but the query will be inefficient. Running this query on an actual set of data (not the identical query) with around 145,000 rows took 13.09 seconds in MySql according to the query browser.
This query can be re-written using the GROUP BY clause which will take advantage of the database engine to produce the same results. The same query can be written as follows:
SELECT STATE, SUM(ORDER_AMOUNT) as DOLLARS FROM OrderData
where RECEIVED_DATE between '20070101' and '20080101' GROUP BY STATE HAVING SUM(ORDER_AMOUNT) > 0;
In comparison, this query which produced the same result against the same set of data took .07 seconds!
That is 187 times faster. More than a little bit.
Obviously this is a drastic result, and honestly I didn't plan that or expect it but it sure illustrates my point. It's less than scientific since you don't know my environment, the actual query, or the actual data but I did try my best to make it legit. Making that change in a production environment could save an immense amount of processing time.
What's the point?
Nothing really, I just realized that a lot of people are unaware of the group by clause and all they need is to see it once to change their ways. So if you know a database beginner or a database "expert" that could use a wake up call, pass them this article and help them out.
- Posted by matt on April 8, 2008
I've been using the excellent .NET blogging platform BlogEngine.NET for a while now and really enjoy coding against it even though code highliting doesn't seem to work. The extensibility of the code is awesome and allows you to morph the program into whatever you like. I was recently converting the program into a CMS system for a project that required a simple management tool and realized that the Page listing was lacking a bit in its display. Granted, this tool was not meant to be a CMS system but if your blog has many pages this might be useful to you as well. The system has built in Parent -> Child paging but displays all of the pages straight out in a list. I wanted to see the list with the Parent pages in bold and the child pages indented beneith its parent. To accomplish this I added 2 properties to the Page.cs object in the Blogengin.core, then I added a function to the Pages.cs code behind in /Admin/Pages/Pages.aspx.cs and modified the BindPageList() function to call it.
Note: I recompiled the program as a .NET 3.5 project to make use of Lambda expressions in the BindChildPageList() function. Change it to use delegates in the for loop if you're using .NET 2.0
First I added two properties to the Page object to make things easier later on: [BlogEngine.Core/Page.cs]
///
/// Does this post have a parent page?
///
public bool HasParentPage
{
get { return this.Parent != Guid.Empty; }
}
///
/// Does this post have child pages
///
public bool HasChildPages
{
get
{
foreach (Page p in Page._Pages)
{
if (p.Parent == this.Id)
return true;
}
return false;
}
}Then I modified the BindPageList() function [BlogEngine.Web/Admin/Pages/Pages.aspx.cs]
private void BindPageList() {
foreach (Page page in BlogEngine.Core.Page.Pages) {
if (!page.HasParentPage)//MOD: Only add the page to the list if it's a root page (children are added below)
{//MOD
HtmlGenericControl li = new HtmlGenericControl("li");
HtmlAnchor a = new HtmlAnchor();
a.HRef = "?id=" + page.Id.ToString();
a.InnerHtml = page.Title;
System.Web.UI.LiteralControl text = new System.Web.UI.LiteralControl
(" (" + page.DateCreated.ToString("yyyy-dd-MM HH:mm") + ")");
li.Controls.Add(a);
li.Controls.Add(text);
if (page.HasChildPages)//MOD: If the page has children, add them recursively
{//MOD
//find children
li.Controls.Add(BuildChildPageList(page));//MOD
}//MOD
ulPages.Controls.Add(li);
}//MOD
}
divPages.Visible = true;
aPages.InnerHtml = BlogEngine.Core.Page.Pages.Count + " " + Resources.labels.pages;
}
And finally I added the BuildChildPageList() function [BlogEngine.Web/Admin/Pages/Pages.aspx.cs]
private HtmlGenericControl BuildChildPageList(BlogEngine.Core.Page page)
{
HtmlGenericControl ul = new HtmlGenericControl("ul");
foreach (Page cPage in BlogEngine.Core.Page.Pages.FindAll(p => (p.Parent == page.Id)))
{
HtmlGenericControl cLi = new HtmlGenericControl("li");
cLi.Attributes.CssStyle.Add("font-weight", "normal");
HtmlAnchor cA = new HtmlAnchor();
cA.HRef = "?id=" + cPage.Id.ToString();
cA.InnerHtml = cPage.Title;
System.Web.UI.LiteralControl cText = new System.Web.UI.LiteralControl
(" (" + cPage.DateCreated.ToString("yyyy-dd-MM HH:mm") + ")");
cLi.Controls.Add(cA);
cLi.Controls.Add(cText);
if (cPage.HasChildPages)
{
cLi.Attributes.CssStyle.Remove("font-weight");
cLi.Attributes.CssStyle.Add("font-weight", "bold");
cLi.Controls.Add(BuildChildPageList(cPage));
}
ul.Controls.Add(cLi);
}
return ul;
}
The result is the following:
- Posted by Matt on April 2, 2008
I know there are other C# Google Chart solutions out there but none of them seemed to accomplish what I needed for my particular situation. They all seem to be geared towards generating simple graphs from a small static data set.
I wanted something that would make it easy to generate graphs from large datasets in a variety of ways while keeping things clean when the data became questionable. My focus was aimed at financial analysis over a specified period of time, generally dealing with revenue numbers. For this reason, my example will demonstrate how to use the API to create a graph showing spend vs. revenue over time.
I was able to create a GoogleChart class that basically aids in generating the Google URL syntax and combine that with a GraphHelper class that scrubs up the data for use with the charts. The GoogleChart class will simply return a string with the url to the google chart you want generated.
The source code for the API and Helper class is provided below, and I will simply show how to code against them.
1: GoogleChart chartRevenue = new GoogleChart();
2: chartRevenue.GraphWidth = width;
3: chartRevenue.GraphHeight = height;
4: chartRevenue.ChartTitle = "Revenue vs Spend";
5: chartRevenue.ChartType = ChartTypes.LineChart;
6: //The Legend is stored in a List and expects a set of data points per legend.
7: chartRevenue.ChartLegend.Add("Revenue Dollars");
8: chartRevenue.ChartLegend.Add("Spend Dollars");
9: //The Colors are stored in a List and expect a set of data points per color
10: chartRevenue.DataSetColors.Add("#00ff00");
11: chartRevenue.DataSetColors.Add("#ff6614");
12: chartRevenue.ShowGridLines = true;
13:
14: //fake query
15: string query1 = "select revenue from data where date between now and later";
16: string query2 = "select spend from data where date between now and later";
17:
18: DataSet dsRev = DataAccess.ExecuteQueryGetDataSet(query1);
19: DataSet dsSpend = DataAccess.ExecuteQueryGetDataSet(query2);
20:
21: int MaxRev = 0;
22: int MaxSpend = 0;
23:
24: //The MaxValue is needed to scale the results to the graph.
25: //The second parameter is the column to use for the dollar value.
26: try { MaxRev = GraphHelper.GetMaxValueFromDataSet(dsRev, 2); }
27: catch { MaxRev = 0; }
28:
29: try { MaxSpend = GraphHelper.GetMaxValueFromDataSet(dsSpend, 0); }
30: catch { MaxSpend = 0; }
31:
32: if (dsRev != null && dsRev.Tables[0].Rows.Count > 0)
33: {
34: //Set the MaxValue for the chart to get the proper scale.
35: chartRevenue.MaxValue = MaxRev > MaxSpend ? MaxRev : MaxSpend;
36: //The GraphHelper will grab out a range of X Axis Labels
37: //using your max value as the top most value.
38: //The additional x axis values are generated by dividing out the
39: //max value to get intermediate values.
40: foreach (string s in GraphHelper.GetAxisLabelsFromDataSet(dsRev, 1))
41: {
42: chartRevenue.XAxisLabels.Add(s);//Add the labels to the chart
43: }
44: //Add the revenue data points to the collection using the
45: //Graph Helper, the second parameter is the column holding the data values.
46: chartRevenue.DataPoints.Add(GraphHelper.GetDataValuesFromDataSet(dsRev, 2));
47: }
48: else
49: {
50: //No Data, return no data image
51: return "~/Images/Layout/Tracker_LCD_No_Data.jpg";
52: }
53:
54: //Add the Spend data points to the chart
55: if (dsSpend != null && dsSpend.Tables[0].Rows.Count > 0)
56: chartRevenue.DataPoints.Add(GraphHelper.GetDataValuesFromDataSet(dsSpend, 0));
57:
58: //Tell the graph to build with the supplied data and
59: //return a URL to the resulting chart image.
60: return chartRevenue.GetGraphImage();
The resulting graph looks like the following:
The Source code is below. I'm sure this solution is not for everyone, but people with large data sets might find it useful.
Source: GoogleChart.zip (3.70 kb)
