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 !

patmortech · Accepted Answer · 2011-05-18 18:13:04Z

7

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;
}

answered May 18, 2011 at 18:13

patmortech

10.2k6 gold badges41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related