Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

DB Server Connection Management with ADO.NET Connected Data Access

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
codequest
Asked:
codequest
2 Solutions
 
Surendra NathTechnology LeadCommented:
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
 
codequestAuthor Commented:
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
 
Bob LearnedCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
apeterCommented:
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
 
codequestAuthor Commented:
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
 
codequestAuthor Commented:
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
 
codequestAuthor Commented:
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
 
apeterCommented:
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
 
codequestAuthor Commented:
I'll award points based on apeter's comment.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now