How to use User.Identity.Name as a parameter for SqlDataSource in ASP.NET?

Question

For SqlDataSource I can configure the external source for the incoming paramater. For example it might be a QueryString, Session, Profile and so on. However I do not have an option to use User as a source.

I know that I could provide value for the parameter in Inserting,Selecting,Updating,Deleting events. But I do not think that this is an ellegant solution because I have some parameteres already definied in aspx file. I do not want to have parameters defined in two separate places. It makes mess.

So can I somehow define this parameter in .aspx file?

    <SelectParameters>
        <asp:QueryStringParameter DefaultValue="-1" Name="ID" 
            QueryStringField="ID" />
        //User.Identity.Name goes here as a value for another parameter  
    </SelectParameters> 

Solution 1

Declare it in your .aspx and fill it in your codebehind:

aspx

<asp:Parameter Name="username" Type="String" DefaultValue="Anonymous" />

codebehind

protected void Page_Init(object sender, EventArgs e) {
    DataSource.SelectParameters["username"].DefaultValue = User.Identity.Name;
}

Solution 2

You can also accomplish this by creating a hidden textbox on the page, apply the User.Identity.Name to the value and then use the formcontrol parameter in the SQL data source. The advantage here is that you can reuse the code in the select, insert, delete, and update parameters without extra code.

So in aspx we have (noneDisplay is a css class to hide it):

<asp:TextBox runat="server" ID="txtWebAuthUser" CssClass="noneDisplay"></asp:TextBox>

and in the Update parameter of the sql datasource update section:

<asp:ControlParameter Name="CUrrentUser"  ControlID="txtWebAuthUser" Type="String" PropertyName="Text" />

which gets interpreted in the update something like this:

UpdateCommand="UPDATE [Checks] SET [ScholarshipName] = @ScholarshipName, [Amount] = @Amount,LastModifiedBy=@CUrrentUser,
         [LastModified] = getdate() WHERE [CheckId] = @CheckId"

and then in the .cs file form load we have:

this.txtWebAuthUser.Text = User.Identity.Name; 

This technique has worked well in many places in all of our applications.

Solution 3

in asp page put a blank datasource with a connectionstring

e.g.

<asp:SqlDataSource ID="SqlDataSourceDeviceID" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>">
<asp:DropDownList ID="DropDownListDeviceID" runat="server" DataSourceID="SqlDataSourceDeviceID" DataTextField="DevLoc" DataValueField="DeviceId"></asp:DropDownList>

in code behind on pageload

  protected void Page_Load(object sender, EventArgs e)
    {
if (!IsPostBack) {
   String myQuery =String.Format("SELECT DeviceID,DevLoc  FROM  ... where UserName='{0}')",User.Identity.Name);
   SqlDataSourceDeviceID.SelectCommand = myQuery;
   SqlDataSourceDeviceID.DataBind();
   DropDownListDeviceID.DataBind();
}

Comments

[Wodzu]

Thanks Jan, but this is what I would like to avoid. In yuour example I need to fill value of parameter in codebehind. So I have to handle parameters in two separate places: in aspx file and in cs file. Can't I declare the parameter in aspx and bind value for it in aspx too?

[Jan Jongboom]

You can give it a shot by using <asp:Parameter Name="username" Type="String" DefaultValue="<%#User.Identity.Name%>" />

[Wodzu]

<%#User.Identity.Name%> unfortunately that doesn't work since Parameter doesn't support DataBinding...

[Djordje Miljkovic]

Thanks Jan, succeeded in codebehind. It is not necessary to declare a Default Value in .aspx file. Can only add to the codefehind file. protected void Page_Init(object sender, EventArgs e) { SqlDataSource1.InsertParameters["UserName"].DefaultValue = User.Identity.Name; }

[Barry]

fyi. If you have asp:Button's that postBack to functions that perform db ops or in any way use the datasource... You need to have this same code at the top of that button callback too. (aka prior to databinding or pre-databind()) This is why I hate aspx forms. Ridiculous, tedious, over the top, undocumented, frustratingly difficult to figure out and deal with, and completely retarded. My advice: switch to razor, asap.

[Jan Jongboom]

Does this not leave a gaping security hole, where you can impersonate other users by changing the variable in the hidden text box?

[done_merson]

Set it on page load, so any change by the user will not be seen.