KoffeeKoder
Skip Navigation Links
Home
About Me


Categories
C# (11)
ASP.NET (34)
JavaScript (6)
CSS (1)
XSLT (1)
Unit Testing (22)
Architecture (23)
Ajax (8)
LINQ to SQL (2)
ASP.NET MVC (5)
Life (18)
Book Reviews (2)
WPF (13)
Projects (2)

  • Dynamically Assign Parameters to SQLDataSource
    published on 6/11/2008 2:44:59 PM
  • SqlDataSource allows you to Select/Insert/Update/Delete the data from the database without even writing a single line of code. But sometimes you need to dynamically assign a parameter. First let's take a look at the simple update which does not dynamically assign the parameters.
    In the example below I am using a simple GridView control and updating the UserName, FirstName and LastName. The Bind("UserName") will get the username from the database and display it on the screen and it will also send the username back to the database when you update.

    <asp:GridView AutoGenerateEditButton="true" AutoGenerateColumns="false" DataKeyNames="UserID" DataSourceID="SqlDataSource1" ID="gvUsers" runat="server" OnRowUpdating="gvUsers_RowUpdating">
    <Columns>
    <asp:TemplateField HeaderText="UserName">
    <ItemTemplate>
    <asp:Label ID="lblUserName" runat="server" Text='<%# Eval("UserName") %>' />
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtUserName" Enabled="false" runat="server" Text = '<%# Bind("UserName") %>' />
    </EditItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="FirstName" HeaderText="First Name" />
    <asp:BoundField DataField="LastName" HeaderText="Last Name" />
    </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString= "<%$ ConnectionStrings:ConnectionString %>"
    SelectCommand="SELECT * FROM Users" UpdateCommand="UPDATE Users SET UserName = @UserName, FirstName = @FirstName, LastName = @LastName WHERE UserID = @UserID" OnUpdating="SqlDataSource1_Updating">
    <UpdateParameters>
    <asp:Parameter Name="FirstName" Type="String" />
    <asp:Parameter Name="LastName" Type="String" />
    <asp:Parameter Name="UserName" Type="String" />
    </UpdateParameters>
    </asp:SqlDataSource>

    Now, consider a situation where you don't want to display the username from the database but you want to display the current user in the txtUserName TextBox. For this you can easily create a protected method and use databind to display the username in the txtUserName TextBox.

    protected string GetUserName()
    {
    // This can also be User.Identity.Name
    return "AzamSharp";
    }

    And in the GridView you can use something like this:

    <EditItemTemplate>
    <asp:TextBox ID="txtUserName" Enabled="false" runat="server" Text = '<%# GetUserName() %>' />
    </EditItemTemplate>

    But, now the question is that how will you update the username or send the username to the database. You cannot use ControlParameter since the name of the TextBox for each GridView row is different. You can however add assign the parameter in the SqlDataSource Updating event.

    protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
    {
    // Attach another parameter
    e.Command.Parameters["@UserName"].Value = GetUserName();
    }

    This will insert the username "AzamSharp" in the database. Offcourse you can use User.Identity.Name to get the current logged user.