Multiple/Duplicate SQL Server Worker Processes

I don't even know where to begin looking... I have a page that loads multiple web user controls...
I know I use one connection object class that is used in all my objects when executing the query (calling Stored Procedures).
The problem is when the first page is rendered and each user control queries the database (SQL Server),
it eventually slows down. In my controls, I use a lot of repeaters and internal queries per each repeater item.
So I know it hits the database quite often.
Problem is when I look in SQL Server Enterprise Manager Process Info, I have multiple worker processes sleeping.
My first thought is ASP.net is creating a new session connection (process) to the SQL Server? Why? How?
What do I do to check either my code is creating the connection object properly. Thanks!
Larry
I have discovered, when I created my connection object for ExecuteNonQuery(), I forgot to Close() the connection.
Since I didn't close the connection object when I was finished... anda few new instances of the connection object was created, it created anew connection object.
In other functions, I was using the DataAdapter which opened and closedthe connection for you, so I took that feature for granted andcompletely forgot to close the connection object when doing aExecuteNonQuery().
I get a doht for the day...
Thanks for taking your time in reading...

multiple where statement in a SP

Hello, a small question. I have a search page on the site where the user can search for other users, they can fill in age, gender etc

The question is, how should i extract all that information from the database, i am using SP in MS SQL 2005, C#.

For each of the options is there a possibility to press "not specified", how do i build up a query in the sql server?

Should i just

IF (@.Age1 <> '')
BEGIN
SET @.SearchString = @.SearchString + 'AND (profile_publicinfo.age = ' + @.Age1 + ')'
END

and then continue like this?

so it would look something like this:

SELECT username, gender, signupdate .... FROM profile_publicinfo FULL OUTER JOIN ..... WHERE (@.SearchString)

any ideas?

I highly recomomend Erland Sommarskog's article:Dynamic Search Conditions in T-SQL.|||Thanks, Erland Sommarskog's article solved my problems.sql

Multiple tables used in select statement makes my Update statement not work?

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown.

I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:

----------------------------------------

SELECT:

SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName

FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID)

WHERE (People.PersonID = ?)

UPDATE:

UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ?

----------------------------------------

The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed.

If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.

Thank you in advance for any time, help, and/or advice you can give.

Brian

I just wanted to add that I am working with Visual Web Developer 2005 Express. If any one knows how I can get around this issue please let me know, or even if you might know of a place that could possibly have a document that approaches this I have no problem doing the research. I have spent hours searching forums and doing google and microsoft searches for tutorials and have had no luck. I definately havn't come across anything say this is not possible, I just need to know what I am missing.

Thanks for your time in advance,

Brian

|||

You shouldn't really be using ?'s for parameters unless you are using odbc or something. That's not standard practice for sql server, but it is the only way to do it for databases that don't support named parameters.

That aside the problem is the foreign key fields. You don't want to update the base table with the text representations of the foreign keys, you need to update the base table with the foreign key itself. After an edit, you don't have those values anywhere, all you have is the text version. There are many approaches to solving your problem. Looking the values up in the sqldatasource_updating event, and populating the parameters with the looked up id's. You can even come up with some sql to do the lookup for you as well although it does get a bit messy. The alternative that I use is a custom column type specifically for this purpose that pulls the foreign key values in from another sqldatasource and when in "edit" mode, it presents them to the user as a dropdownlist. Unfortunately I can't share that code with you, but there are probably other examples of it on the web, I'd google for "dropdownfield" or "asp.net dropdownfield".

|||

Motley:

You shouldn't really be using ?'s for parameters unless you are using odbc or something. That's not standard practice for sql server, but it is the only way to do it for databases that don't support named parameters.

The question marks were put in place by the software. I was trying to get the UPDATE to work on my own and i was setting the fields I wanted to be updated equal to @.fieldName. I could not get this to update so I used the "Advanced" feature in the quesry wizard (I guess it's called) and selected the button to auto-generate the SQL statement and this worked. The UPDATE statement it generated is the one I pasted above. My database is an access database for the time being, but I am using the SqlDataSource method to connect to my database, must be thats why it used the question marks?

Motley:

That aside the problem is the foreign key fields. You don't want to update the base table with the text representations of the foreign keys, you need to update the base table with the foreign key itself. After an edit, you don't have those values anywhere, all you have is the text version. There are many approaches to solving your problem. Looking the values up in the sqldatasource_updating event, and populating the parameters with the looked up id's. You can even come up with some sql to do the lookup for you as well although it does get a bit messy. The alternative that I use is a custom column type specifically for this purpose that pulls the foreign key values in from another sqldatasource and when in "edit" mode, it presents them to the user as a dropdownlist. Unfortunately I can't share that code with you, but there are probably other examples of it on the web, I'd google for "dropdownfield" or "asp.net dropdownfield".

The way I have it setup is so that the field contains a INT value, but the value it shows to the user is a text value. The drop down list you are suggesting is exactly what I am using. When the user is in Read Only mode, everything is shown as a label. When they enter Edit mode, all the fields with foreign keys turn into drop down lists. The drop down list shows a string value, but when the choice is made, its value is and integer. I believe this is working right because the technique I use in order to select a user to edit is to select a name from a drop down list that is external from my gridview (same thing with my detailview). The value of the drop down list is numerical based on the primary key of the PersonID, but the value shown in the drop down list is the name that corrisponds to the given PersonID. This works great and only shows me the record of the person I want to see. Because this worked, I just assumed that useing the same technique in the Edit view would be working the same way. I think this is what you mentioned in what you mentioned above. I will paste all the acctual code from the page I am working on that uses DetailsView.

<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
DeleteCommand="DELETE FROM [People] WHERE [PersonID] = ?" InsertCommand="INSERT INTO [People] ([FirstName], [LastName], [FullName], [PropertyID], [InviteTypeID], [RSVP], [Wheelchair], [MealID], [TransportationID]) VALUES (?, ?, ?, ?, ?, ?, ?,?,?)"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT People.*, Property.[House/Day Hab], InviteType.InviteTypeName, Meals.MealName, Transportation.TransportationName FROM Transportation INNER JOIN (Meals INNER JOIN (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) ON Meals.MealID=People.MealID) ON Transportation.TransportationID=People.TransportationID WHERE (PersonID = ?)"
UpdateCommand="UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ?, [MealID] = ?, [TransportationID] = ?, WHERE [PersonID] = ?">
<DeleteParameters>
<asp:Parameter Name="PersonID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FullName" Type="String" />
<asp:Parameter Name="LocationID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="PersonID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="FullNameDDL" Name="People.PersonID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<InsertParameters>

<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />

<asp:Parameter Name="PropertyID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="MealID" Type="Int32" />
<asp:Parameter Name="TransportationID" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
<asp:DropDownList ID="FullNameDDL" runat="server" DataSourceID="GetNames" DataTextField="FullName"
DataValueField="PersonID" Style='left: 5px; position: relative; top: 190px' AutoPostBack='True'>
</asp:DropDownList><asp:AccessDataSource ID="GetNames" runat="server" DataFile="H:\Visual Studio 2005\WebSites\test\App_Data\DinnerGuest.mdb"
SelectCommand="SELECT [PersonID], [FullName] FROM [People]"></asp:AccessDataSource>

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="SqlDataSource1"
Height="50px" Style='left: 201px; position: relative; top: 152px' Width='125px'>
<RowStyle Wrap="False" />
<Fields>
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" ConvertEmptyStringToNull="False" NullDisplayText="<NULL>" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" ConvertEmptyStringToNull="False" />
<asp:TemplateField HeaderText="PropertyID" SortExpression="PropertyID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="GetProperty" DataTextField="column1"
DataValueField="PropertyID" SelectedValue='<%# Bind("PropertyID") %>' Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetProperty" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [PropertyID], [House/Day Hab] AS column1 FROM [Property]">
</asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="GetProperty" DataTextField="column1"
DataValueField="PropertyID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetProperty" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [PropertyID], [House/Day Hab] AS column1 FROM [Property]">
</asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("[House/Day Hab]") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="InviteTypeID" SortExpression="InviteTypeID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="GetInviteName"
DataTextField="InviteTypeName" DataValueField="InviteTypeID" SelectedValue='<%# Bind("InviteTypeID") %>'
Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetInviteName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [InviteType]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList4" runat="server" DataSourceID="GetInviteName"
DataTextField="InviteTypeName" DataValueField="InviteTypeID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetInviteName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [InviteType]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("InviteTypeName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CheckBoxField DataField="RSVP" HeaderText="RSVP" SortExpression="RSVP" />
<asp:CheckBoxField DataField="Wheelchair" HeaderText="Wheelchair" SortExpression="Wheelchair" />
<asp:TemplateField HeaderText="MealID" SortExpression="MealID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList5" runat="server" DataSourceID="GetMealName" DataTextField="MealName"
DataValueField="MealID" SelectedValue='<%# Bind("MealID") %>' Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetMealName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [MealID], [MealName] FROM [Meals]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList6" runat="server" DataSourceID="GetMealName" DataTextField="MealName"
DataValueField="MealID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetMealName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [MealID], [MealName] FROM [Meals]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("MealName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="TransportationID" SortExpression="TransportationID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList7" runat="server" DataSourceID="GetTransportationType"
DataTextField="TransportationName" DataValueField="TransportationID" SelectedValue='<%# Bind("TransportationID") %>'
Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetTransportationType" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [Transportation]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList8" runat="server" DataSourceID="GetTransportationType"
DataTextField="TransportationName" DataValueField="TransportationID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetTransportationType" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [Transportation]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("TransportationName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
</div>
</form>

|||

Yes, using access would require using ?'s since it doesn't support named parameters.

The DropDownField control I mentioned makes things a bit easier for you because you don't have to create templated fields (They become DropDownFields), your main select is kept simple if you want (No joins needed, it can pull the text values for you if you want).

But since you've already gone through the trouble, it appears the only problem I can find is that your update parameters do not match your update command.

UpdateCommand="UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ?, [MealID] = ?, [TransportationID] = ?, WHERE [PersonID] = ?"

UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FullName" Type="String" />
<asp:Parameter Name="LocationID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="PersonID" Type="Int32" />
</UpdateParameters>

LocationID should be PropertyID. MealID needs to be added. TransportationID needs to be added. PersonID should either be a controlparameter -- pulling it's value from the person dropdown, or you should add it to the detailview's datakeynames property. All the parameters need to be in the exact order they are mentioned in the update command.

|||

Thank you so much for all your help. I was definately only relying on Visual Web Developer to get this going which seems to be my down fall. As I made changes to my data source and changed my queries, I guess the code that is automatically developed doesn't follow along with the changes I make very well. Thanks again for taking the time to look through my code, I will definately make sure I am checking it more, instead of just relying on the software. I guess if the software could do everything I wanted without me seeing the code then I probably would have a much more difficult time finding work haha. Live and learn I guess.

Thanks again,

Brian

multiple tables

hi,

I have 3 tables: Unix, Windows & Sybase

there are 3 dropdownlists in .asp page,

dropdown1=<displays the lists of table> (Unix, Windows & Sybase) variable as @.table

dropdown2=<displays the list of Columns from the dropdown1 selected table> variable as @.server

dropdown3=<displays the list of Row Header from the dropdown1 selected table> variable as @.user

how shal i define the select statement for this:

it should be something like,

Select * from @.table WHERE Server= @.server AND Row-Header = @.user

I appreciate if you could kindly help me in getting the correct syntax, thanx in advance.

Hi,

the tablename cannot be defined at runtime without using dynamic Sql, compose your SQLString first (e.g. in a stored procedure) then execute it via sp_executesql:

DECLARE @.SQLString VARCHAR(4000)

SET @.SQLString ='Select * from ' + @.table + ' WHERE Server= ''' + @.server + ''' AND Row-Header = ''' + @.user + ''''

But keep in mind that dynamic sql can be dangerous, read the aarticle of Erland first: http://www.sommarskog.se/dynamic_sql.html

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

|||

hi,

Thanks a million Jens,

I have done the same way as you explained ( First line: DECLARE @.SQLString VARCHAR(4000) Second line: SET @.SQLString ='Select * from ' + @.table + ' WHERE Server= ''' + @.server + ''' AND Row-Header = ''' + @.user + '''').executed the .asp page: it shows only the dropdown & no table. Appreciate your help, many thanks again for this:

-

execution script part:
-
<script runat="server">
Sub OSClick1(ByVal sender As Object, ByVal e As EventArgs)
'EXEC "@.SQLString"'
End Sub
</script>
-
processing script part:
-
<asp:Button ID="btnOSClick1" runat="server" Text="Get" Width="90px" OnClick="OSClick1" /><br />
<br />
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource4">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:Unix.Primary %>"
SelectCommand="CREATE PROCEDURE general_select @.table nvarchar(127), @.server nvarchar(127), @.user nvarchar(127),&#13;&#10;DECLARE @.SQLString VARCHAR(4000)&#13;&#10;SET @.SQLString ='Select * from ' + @.table + ' WHERE Server= ''' + @.server + ''' AND Row-Header = ''' + @.user + ''''&#13;&#10;'">
<SelectParameters>
<asp:ControlParameter ControlID="txtOS" Name="table" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="txtSrvUnix" Name="server" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="txtUnixDes" Name="user" PropertyName="SelectedValue" />
<asp:Parameter Name="SQLString" />
</SelectParameters>
</asp:SqlDataSource>
-

|||Hi,

I am not very familiar with those new SQLDataSource mechanism, but you do not need to create the procedure as a Select Command. Create the procedure before and do just execute the procedure as the Select Command. In plain C# code you will have to assign the Commandtype = StoredProcedure first to the Select command (You will probably have to do this here also) and then just write something like SelectCommand="general_Select"

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

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 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.