?
Solved

DB Server Connection Management with ADO.NET Connected Data Access

Posted on 2013-12-27
11
Medium Priority
?
292 Views
Last Modified: 2014-02-11
I am rewriting a 2006-era ASP.net application.  To maintain high platform migrate-ability, one option that I am looking at is ADO.NET connected data access  as described here:

http://msdn.microsoft.com/en-us/data/ff707264.aspx#_Toc261428884

Given that starting assumption, I am looking for additional information on how to manage server resources and connections in this approach.

Any guidance on this would be appreciated.

Thanks!
0
Comment
Question by:codequest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 600 total points
ID: 39743187
A few inputs here...

1) if you re-writing your application, then please consider the below
          a) put all the database retrival stuff into a WCF and let it give the results, instead of actual ADO.NET
          b) Ensure that the access is done through stored procedures or functions but not by direct select statemets.

By doing so, you will website will have an API for further improvement.

And also I would suggest you to take a look at the entity framework, which has more capability than just ADO.NET.

Hope this helps.
0
 
LVL 2

Author Comment

by:codequest
ID: 39752398
Thanks for the input.  Those are worth consideration.  Right now though, I'm looking for something more specifically related to my question, with the assumptions given.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39756079
What version of Visual Studio .NET do you have?
What type of web site are you planning (MVC, Web Forms, OWIN/Katana, ...)?
Do you know about Entity Framework?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 25

Accepted Solution

by:
apeter earned 1400 total points
ID: 39756684
Hi,the article talks about two options, linq to SQL and entity framework. Which option you have opted for?

To manage resources in both above options, invoke dispose method on datacontext object to release the resources.
0
 
LVL 2

Author Comment

by:codequest
ID: 39756726
Thanks for the inputs.   I'm using VS2013, MVC, and I know about EF.

The option I need to work out is "ADO.NET connected data access", the first option in the article.

The reason I'm going to work through this option first is that I want to maintain the lowest level of abstraction for both performance and migrate-ability.

While I appreciate the tip on the "dispose" method, I need to look at some contextual material on this to understand things like how many connections I'm going to have open, the cost of leaving connections open, the cost of opening and closing connections, etc.

I appreciate that EF resolves many of these questions, however, that's a different path that I won't pursue until I've more completely understood what it takes to manage ADO.NET connected data access.
0
 
LVL 2

Author Comment

by:codequest
ID: 39811181
http://forums.asp.net/t/932515.aspx

Apparently best practice for ASP net SQL connections is to open and close them frequently, and the rule is open late close early.

That's kind of what I was looking for; in essence you don't manage connections you just open them use them and close them.

Unless someone disagrees I'm going to go with that answer thanks.
0
 
LVL 2

Author Comment

by:codequest
ID: 39851406
I've requested that this question be closed as follows:

Accepted answer: 0 points for codequest's comment #a39811181

for the following reason:

most accurate
0
 
LVL 25

Expert Comment

by:apeter
ID: 39850260
I guess that is what I have also stating.

"To manage resources in both above options, invoke dispose method on datacontext object to release the resources."
0
 
LVL 2

Author Comment

by:codequest
ID: 39851408
I'll award points based on apeter's comment.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question