Multiple SqlDataSources on a Page

I was wondering, if you have multiple SqlDataSources on a single aspx page, does the framework need to open and close a SEPERATE connection for each SqlDataSource. This would make it much less efficient than coding with the ADO.NET objects and just opn and close the connection once irrespective how many quries that page needs.

Your comments would be appreciated.

Yes, each SqlDataSource is unaware of other data sources on the page. Each SQL Data Source can have it own connection string. Thus, sharing a connection between two might not always be possible if they use different connections.

You are correct, the SqlDataSource isnot meant for performance (but it isn't terrible, either). It is a WYSIWYG control meant for developers that are more comfortable designing their application in the IDE designer rather than by code or for simple pages that don't need the overkill of DataSet, Table Adapters, etc.

|||Many Thanks vcsjones.|||Just another question. Is it possible to tell all sqldatasources to use 1 connection object if the connections are all the same?|||

No, not that I am aware of.

|||Thanks again.

multiple sql staements in SqlDataSources update command

I have a gridview with a sqlDataSource with the SelectCommand as
"SELECT Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"

what kinda 'UpdateCommand' do I set so that ItemForSale is also updated from the grid? I tried two update statements seperated with a semicolon but that wouldn't work, any suggestions...

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"
SelectCommand= "SELECT Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"
UpdateCommand="UPDATE [ItemForSale] SET [Quantity] = @.myQuantity FROM FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"
WHERE ItemForSale.ID = @.ID" >
<UpdateParameters>
<asp:Parameter Name="myQuantity" Type="Int32" />
<asp:Parameter Name="ID" Type="Int32" />
</UpdateParameters>

</asp:SqlDataSource>

Make sure the ID column in your ItemForSale is a primary key of the table.

|||I need to update the movie table AND the ItemForSale table, the example above would only update the ItemForSale table.|||

The ID column should include in your select statement.

Here is a working sample for updating one column from each table using two UPDATE statements.

<%

@.PageLanguage="VB"AutoEventWireup="false"CodeFile="test2.aspx.vb"Inherits="test2" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><asp:GridViewID="GridView1"runat="server"DataSourceID="SqlDataSource1"DataKeyNames="ID"><Columns><asp:CommandFieldShowEditButton="True"/></Columns></asp:GridView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:mytestConnectionString %>"SelectCommand="SELECT Movie.ID, Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"UpdateCommand="UPDATE [Movie] SET [Title] = @.Title FROM Movie INNER JOIN ItemForSale ON ItemForSale.ID = Movie.ID WHERE Movie.ID = @.ID;UPDATE [ItemForSale] SET [Quantity] = @.Quantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID

WHERE ItemForSale.ID = @.ID">

<UpdateParameters><asp:ParameterName="ID"Type="Int32"/><asp:ParameterName="Quantity"Type="Int32"/><asp:ParameterName="Title"Type="String"/></UpdateParameters></asp:SqlDataSource></div></form>

</

body>

</

html>