Writing a SQLdatasource in code behind in C# to use a code behind value

Question

I am trying to populate a data table using the SQL datasource based upon parametrized inputs .The tricky thing is that the value of the parametrized input is available only in the code behind so I am forced to write the SQL datasource in the code behind page

How do I go about it ( I am using C# and ASP.Net 4.0)

The current code in the asp.net page to create the sql datasource connection is :

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:LicensingConnectionString %>" 
        SelectCommand="SELECT * FROM [commissions] where username=@username "></asp:SqlDataSource>

The value I want to use in @username is retrieved in the code behind by this code

IPrincipal p = HttpContext.Current.User;
        // p.Identity.Name : this is what we will use to call the stored procedure to get the data and populate it
        string username = p.Identity.Name;

Thanks !

score 7 · Accepted Answer · answered May 18 '11 at 18:13

You need to handle the OnSelecting event of the data source, and in there you can set the parameter's value.

In your page:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:LicensingConnectionString %>" 
        SelectCommand="SELECT * FROM [commissions] where username=@username"
        OnSelecting="SqlDataSource1_Selecting" >
    <SelectParameters>
        <asp:Parameter Name="username" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

In your codebehind:

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
     e.Command.Parameters["@username"].Value = HttpContext.Current.User.Identity.Name;
}

Writing a SQLdatasource in code behind in C# to use a code behind value

1 Answers1

Linked