How to use a custom Parameter with SqlDataSource

Question

I want to add a GridView control to one of my views that is backed by a SqlDataSource. The SelectCommand query is parametrized on the aspnet_Users.UserId GUID for the currently-logged-in user, so I need a way to pass the user ID as a parameter.

I read How to utilize ASP.NET current user name in SqlParameter without code-behind and decided to create a custom Parameter named UserIdParameter:

namespace MyApp.Web
{
    public class UserIdParameter : Parameter
    {
        public UserIdParameter(string name)
            : base(name)
        {
        }

        protected UserIdParameter(UserIdParameter parameter)
            : base(parameter)
        {
        }

        protected override Parameter Clone()
        {
            return new UserIdParameter(this);
        }

        protected override object Evaluate(HttpContext context, Control control)
        {
            return Membership.GetUser().ProviderUserKey;
        }
    }
}

In the ASPX view I then added:

<%@ Register TagPrefix="my" Namespace="MyApp.Web" %>

in a line after the <%@ Page ... %> line as well as:

    <SelectParameters>
        <my:UserIdParameter Name="UserId" />
    </SelectParameters>

within the asp:SqlDataSource element.

Unfortunately I get a Parser Error ("An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.") with the following highlighted in red:

        <my:UserIdParameter Name="UserId" />

Visual Web Developer 2010 Express is also informing me that "Element 'UserIdParameter' is not a known element. This can occur if there is a compilation error in the Web site, or the web.config file is missing."

Do I need to modify Web.config in some way? If not, what do I need to do to be able to use my custom UserIdParameter parameter?

score 2 · Accepted Answer · answered Jan 18 '11 at 18:49

I figured out the problem. The custom UserIdParameter class needed to be in a separate assembly and it needed a no-arguments constructor.

The steps that worked for me were:

Create a new ASP.NET Server Control project and add it to the current solution. (I named this new project MyApp.Web.UI.WebControls).

Add the following class:

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

namespace MyApp.Web.UI.WebControls
{
    public class UserIdParameter : Parameter
    {
        public UserIdParameter()
        {
        }

        public UserIdParameter(UserIdParameter userIdParameter)
            : base(userIdParameter)
        {
        }

        protected override Parameter Clone()
        {
            return new UserIdParameter(this);
        }

        protected override object Evaluate(HttpContext context, Control control)
        {
            return Membership.GetUser().ProviderUserKey;
        }
    }
}

Build the MyApp.Web.UI.WebControls assembly. Reference it from the ASP.NET web project (named MyApp.Web in my case).

Add to Web.config:

<pages>
      <controls>
        <add tagPrefix="my" assembly="MyApp.Web.UI.WebControls" namespace="MyApp.Web.UI.WebControls"/>
      </controls>
      <!-- ... -->
</pages>

This registers the controls library site-wide. (Note that the pages element is within the system.web element.)

Use the following in the SelectParameters element of the SqlDataSource:
```
<my:UserIdParameter Name="UserGuid" DbType="Guid" />
```
(No need for <%@ Register ... %>)

Could use explain `public UserIdParameter(UserIdParameter userIdParameter) : base(userIdParameter)` — irfandar, Aug 08 '13 at 21:38
@irfandar: That is a [base constructor call](http://stackoverflow.com/a/12052/196844) for calling the [Parameter(Parameter) copy constructor](http://msdn.microsoft.com/en-us/library/8fffwa5h.aspx). — Daniel Trebbien, Aug 09 '13 at 11:16

score 0 · Answer 2 · answered May 04 '12 at 13:31

0

A more simpler way is to use "Selecting" event. here you can add your custom parameter.

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selecting.aspx

answered May 04 '12 at 13:31

avani gadhai

460
2
12

score 0 · Answer 3 · answered Jan 17 '11 at 18:17

0

Why did you don't use SQLDataSource with the parameters in your query ? after that i'm pretty sure that you can tell the SQLDataSource that the parameter @UserId is equal to an session variable. all that without one code line. all in the graphical interface.

answered Jan 17 '11 at 18:17

Jean-Christophe Fortin

756
6
17

As explained at [Accessing and Updating Data in ASP.NET: Creating Custom Parameter Controls](http://www.4guysfromrolla.com/articles/110106-1.aspx) in the "Determining the Currently Logged On User's UserId" section, "there is no built-in `Parameter` control that returns the currently logged in user's UserId value". – Daniel Trebbien Jan 18 '11 at 18:08
try something like this "SELECT * FROM TEST_TALBE WHERE TEST_FIELD=@TEST". the datasource component will show you a window which you'll be able to tel the datasource where it should take the value for the parameters. – Jean-Christophe Fortin Jan 18 '11 at 21:56
SelectCommand="SELECT * FROM TEST_TABLE WHERE TEST_FIELD= @TEST"> – Jean-Christophe Fortin Jan 18 '11 at 21:57
Christophe: I don't have the GUI for configuring this. I am using Visual Web Developer 2010 Express. – Daniel Trebbien Jan 19 '11 at 01:33

How to use a custom Parameter with SqlDataSource

3 Answers3