Anj Anj - 3 months ago 18
ASP.NET (C#) Question

How to set SelectCommand to change 'WHERE' to logged in user

I've been working on webforms and per user data on ASP.NET where if a user logs in, they get their data.

I have a table where it takes the sqldatasource from an asp markup code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:DefaultConnection %>"
ProviderName="<%$ ConnectionStrings:DefaultConnection.ProviderName %>"
SelectCommand="SELECT [first_name], [last_name], [weight_data] FROM [userData]
WHERE [email]='test@test.com';">
</asp:SqlDataSource>


I can get the currently logged in email string through
<%: Context.User.Identity.GetUserName() %>


How would I put that into the WHERE email statement so I can take currently logged in user's email and replace it with 'test@test.com' to match it on the SQL table to get the other data?

Thanks a lot!

Answer

Different ways like: You can use the built in OnSelecting parameter of asp:SqlDataSource

<asp:SqlDataSource ID="SqldsExample" runat="server"
    SelectCommand="SELECT [first_name], [last_name], [weight_data] FROM [userData] 
            WHERE [email]=@UserEmail"
    OnSelecting="SqldsExample_Selecting">
    <SelectParameters>
        <asp:Parameter Name="UserEmail" Type="String"/>
</SelectParameters>

In code-behind

protected void SqldsExample_Selecting(object sender, SqlDataSourceCommandEventArgs e)
{
         e.Command.Parameters["UserEmail"].Value = Context.User.Identity.GetUserName();
}

Another way:

SqlDataSource1.SelectParameters.Add("@UserEmail", Context.User.Identity.GetUserName());

Hope this helps!