Multiple SqlDataSource on a page

Hi,

I placed several SqlDataSource objects on my page which work with the same database (same connection string).

As I know, connection operation to database is costly in the performance prespective.

Do these SqlDataSource controls work with the same connection object or each of them create his own connecton object?

If each create it's own, then can they be changed to work with one connection object?

Thanks

As long as the connections strings are exactly identical, then the connection will get reused automatically as connections are automatically pooled.

Read tip #3:http://msdn.microsoft.com/msdnmag/issues/05/01/ASPNETPerformance/#S4

|||

Thanks,

Yet, another question:

If a SqlDataSource is not connected to a data UI control than does it still go retrieving the data or does it wait to be useed?

|||

It waits for the data to be requestsed such as by calling its Select() method

Multiple SqlDataSource Controls on a Page

Thanks for your help.

I'm just getting into ASP.NET 2.0 and started using the SqlDataSource control to declaritively connect to a database. I was wondering if I have two SqlDataSource controls on one page with two select commands querying the same database, will the that create to seperate instances of opening and closing the connection to the database, or will it open the database once, execute both queries, and then close the connection. It seems that if it creates two separate connections, it would still be more efficient to to connect to the database programmatically using ADO.NET. You would only need to make one trip to the database, pull down all of your data, and then close the connection.

Adrian

I believe it will open two connections. Which is what you would probably want anyhow. That would allow SQL Server to prioritize and minimize response time.

That said, most of the time you can write code to do things more efficiently than depending on a generic library. It's a trade off, and often the code you may generate may not actually be more efficient unless you are also able to spend the time to implement the more advanced features of the library, in this case, the controls ability to selectively databind and cache data.

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>