Get SqlDataSource To Retrieve DefaultValue of Current User
(Use ExpressionBuilder, Not DataBinding in ASP.NET 2.0)
Abstract
This article shows how to use Expression Builders in ASP.NET 2.0 to retrieve the current logged in user. DataBinding will not work so Expression Builders is the ticket. A small source file is created, the references to web.config are shown and a simple example is built.
The Problem
I recently was looking at unanswered posts in the asp.net forum, specifically this one: http://forums.asp.net/thread/1402259.aspx. I thought I understood how databinding and expressions worked, but just wanted to check myself. So, I made a simple example web page just like the post shows. (see below)
<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head id=”Head1″ runat=”server”>
<title>Untitled Page</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” DataSourceID=”SqlDataSource1″>
<Columns>
<asp:BoundField DataField=”Username” HeaderText=”Username” SortExpression=”Username” />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:CodeCampSV06 %>“
SelectCommand=”SELECT [Username], [Email] FROM [Attendees] WHERE ([Username] = @Username)”>
<SelectParameters>
<asp:Parameter Name=”Username” Type=”String” DefaultValue=”<% User.Identity.Name %>“ />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
Then, I ran it through Lutz Roeder’s .NET Reflector and got the following from the code.
private Parameter __BuildControl__control6()
{
Parameter parameter1 = new Parameter();
parameter1.Name = “Username”;
parameter1.Type = TypeCode.String;
parameter1.DefaultValue = “<% User.Identity.Name %>”;
return parameter1;
}
It’s pretty obvious that unless there is a username of the literal string "<% User.Identity.Name %>" no records will be find. This simply means that the build provider asp.net uses is not generating any code for us with sqldatasource that will help with the DefaultValue of parameters in a SqlDataSource.
The Solution
Expression Builder to the rescue!
To solve the problem, we need to build a simple expression builder. Without going into all the details of what an expression builder is, all you need to do is put the following code in your app_code directory and call it something like ExpressionBuilderIdentity.cs.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Compilation;
using System.CodeDom;
public class ExpressionBuilderIdentity : ExpressionBuilder
{
public override System.CodeDom.CodeExpression GetCodeExpression(
BoundPropertyEntry entry, object parsedData, ExpressionBuilderContext context)
{
CodeTypeReferenceExpression targetClass =
new CodeTypeReferenceExpression(typeof(ExpressionBuilderIdentity));
string targetMethod = “GetIdentity”;
CodeExpression methodParameter =
new CodePrimitiveExpression(entry.Expression.Trim());
return new CodeMethodInvokeExpression(
targetClass, targetMethod, methodParameter);
}
public static object GetIdentity(string param)
{
string returnString = string.Empty;
if (param.ToLower().Equals(“name”))
{
returnString = HttpContext.Current.User.Identity.Name;
}
return returnString;
}
}
Then, you need to add a reference to it in your web.config as follows:
<compilation debug=“true“>
<expressionBuilders>
<add expressionPrefix=“UserIdentity“ type=“ExpressionBuilderIdentity, __code“/>
</expressionBuilders>
…
Then, specify the SqlDataSource as follows:
<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:CodeCampSV06 %>“ SelectCommand=”SELECT [Username], [Email] FROM [Attendees] WHERE ([Username] = @Username)”>
<SelectParameters>
<asp:Parameter Name=”Username” Type=”String”
DefaultValue=”<%$ UserIdentity:name %>“ />
</SelectParameters>
</asp:SqlDataSource>
And, Presto, it works!
Summary
Anytime you are using one of the new datasourceID controls, you need to use expressions rather than databinding if you want to reference outside variables. Creating the code is fairly straight forward and lends itself to lots of cool possibilities.











September 20th, 2006 at 12:02 am
hey Peter,
check out this good article :
http://weblogs.asp.net/infinitiesloop/archive/2006/08/09/The-CodeExpressionBuilder.aspx
using the CodeExpressionBuilder, you could use :
DefaultValue=”“
or maybe better :
DefaultValue=”“
the nice thing about the CodeExpressionBuilder is that you can use it for anything really:
September 20th, 2006 at 12:04 am
hey Peter,
check out this good article :
http://weblogs.asp.net/infinitiesloop/archive/2006/08/09/The-CodeExpressionBuilder.aspx
using the CodeExpressionBuilder, you could use :
DefaultValue=”<%$ Code:HttpContext.Current.User.Identity.Name%>“
or maybe better :
DefaultValue=”<%$ Code:CurrentUserName %>“
the nice thing about the CodeExpressionBuilder is that you can use it for anything really:
<%$ Code: DateTime.Now.AddDays(1) %>
<%$ Code: “Hello World, ” + CurrentUserName %>
<%$ Code: CurrentUserName.ToUpper() %>
<%$ Code: “Page compiled as: ” + this.GetType().AssemblyQualifiedName %>
February 14th, 2007 at 12:29 am
You’re post is very interesting and helpful. I am still a beginner in asp.net. Would you post the vb version of declaring the ExpressionBuilderIdentity class?
Thanks
July 2nd, 2007 at 5:03 am
Mentioned below is a PHP function , which can be used to navigate pages generated from the select query . ie. in the form of >
0)
$totalpages+=1;
echo “( Page $page of $totalpages ) “;
if($page>1 ){
echo “ <<Previous “;
}
$next=$page+1;
if($next Next >> “;
}
}
}
// Assuming you have Opened connection to mysql database.
$sql=” Select * from Products “;
// Show the pager link in form of Previous||Next
PagerNextPrevious($sql,20,’text’);
$rs=mysql_query($sql);
if(mysql_num_rows($rs)>0){
while($row=mysql_fetch_array($rs)){
echo $row["ProductName"].”";
}
}
?>
http://www.hanusoftware.com
September 18th, 2007 at 1:59 am
hey Peter, great post! ( I found it a year later, exactly
)
Your solution works great, Im using it now, but I’m not very sure if I understand well. Could you explain a little bit more the Expression Builder class? Thank you very much!
January 5th, 2008 at 5:06 pm
I’m working with this same problem and i can’t to think there has to be an easier way. I was thinking a lot like that other kid to try and use either the cookie , control , Query String , session, … and do something like when it asked for the control parameter or somewhere. Because all i really want to do is filter a drop down box based on what their userID is.
February 14th, 2008 at 12:36 am
Hi Peter .. Indeed a great post dude.. It really helped me as am just a beginner ..Thanks a lot
March 21st, 2008 at 2:20 pm
More than a year later, but here it is the VB version of the code above:
Imports Microsoft.VisualBasic
Imports System.Web.Compilation
Imports System.CodeDom
Public Class ExpressionBuilderIdentity
Inherits ExpressionBuilder
Public Overrides Function GetCodeExpression(ByVal entry As System.Web.UI.BoundPropertyEntry, ByVal parsedData As Object, ByVal context As System.Web.Compilation.ExpressionBuilderContext) As System.CodeDom.CodeExpression
Dim targetClass As New CodeTypeReferenceExpression(GetType(ExpressionBuilderIdentity))
Dim targetMethod As String = “GetIdentity”
Dim methodParameter As CodeExpression = New CodePrimitiveExpression(entry.Expression.Trim())
Return New CodeMethodInvokeExpression(targetClass, targetMethod, methodParameter)
End Function
Public Shared Function GetIdentity(ByVal param As String) As Object
Dim returnString As String = String.Empty
If param.ToLower().Equals(“name”) Then
returnString = HttpContext.Current.User.Identity.Name
End If
Return returnString
End Function
End Class
April 1st, 2008 at 8:11 am
This is just great, thanks so much – why isn’t this simpler by default? The likelihood of wanting to limit data by such a parameter is HUGE! Anyway, you’ve provided a fantastic solution, thanks again!
July 10th, 2008 at 8:07 pm
Your solution is probably the best one, but in this particular situation I use a label with visible=”false” and populate it with the Identity.Name on page load. Works fine, but I’m a newb, so this approach might suck for reasons unknown to me
July 16th, 2008 at 5:31 am
Thanks for detailed explanation…. nice work done
November 10th, 2008 at 10:40 am
Thanks, but I am receiving an error on your Web.Config code <expressionPrefix=
It doesn’t like the ” before UserIdentity.
Any ideas what might be causing this error?
March 11th, 2009 at 5:56 am
This is a great post for .NET developers. What all things can be done in .NET blog engine?
May 8th, 2009 at 8:19 am
Thank you very much! Your code works and it’s great!
May the God bless you because you are kindly charing your knowledge with the others.
August 19th, 2009 at 7:07 am
Thank You, it’s work fine in VS but when I transferred it to real application, it’s cannot load the “_code” in web.config. Any ideas what to do next? I’m using it in the sharepoint standalone application in profile update.
November 4th, 2009 at 11:42 am
It worked like a charm….Thanks a lot for sharing!!!! You are the MAN!!
January 17th, 2010 at 2:08 pm
I used it today and it worked well. Only thing was the quotation marks that got changed when I copied code with Chrome.
I agree that it open ups lots of cool avaibilites. Liked to hear more comments on any drawbacks or reason for when not to use this. Thanks anyway for your code.