Database Samples (Best Practices)

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

Are there any sample projects or web-sites out there that discuss best
practices with regards to inserting, editing and deleting records in a
database with VB.Net. I know how I can do all this, I am just looking to see
some best practice solutions that are out there to get some better ideas on
how I want to be approaching this.

Most often it seems when I find something with regards to this topic, it's
quite limited. I'd like to see some examples of how these utilities are
implemented to give me some better ideas of how I want to approach this on
project that will contain many, many tables, thus I want to use as much code
re-use as possible.

Thanks, Greg
 
Greg,

As you see my answers, than you hope that you will understand that there are
no "best" samples.

You can to insert, update or delete use a Command.ExecuteNonQuerry direct
You can use a dataadapter
You can use a true DBDataAdapter (OracleDataAdapter, SqlDataAdapter,
OleDbDataadapter etc)
You can use different kind of concurrency solutions (Optimistic or
Pesimistic)
You can use different methods to do your concurrency checking inside the two
above
You can use transactions in your connection (this means that you close the
datatables for others however this should very careful be used)
Etc, etc

All those and for sure much more exist and are in circum situations "The
Best" to use.

Cor
 
Hello Greg,

From your post, my understanding on this issue is: you wonder the best
practice of data accessing behavior. If I'm off base, please feel free to
let me know.

As we know, there are a lot of practices/solutions to do the data access.
For instance,

(1) We can use ADO.NET
(http://msdn2.microsoft.com/en-us/library/h43ks021(VS.71).aspx) to hard
code Insert/Update/Select commands
(2) We can use LINQ
(http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx) if .net 3.5
is available to the development/deployment environment.
(3) Enterprise Library
(http://msdn2.microsoft.com/en-us/library/aa480453.aspx) is also a very
good library based on ADO.NET
(4) Subsonic (http://www.codeplex.com/subsonic) can automatically generate
the data access layer of a project according to the database schema, so
that programmers can focus on the business logic.
(5) or we can also build our own data access layer by applying DAO design
pattern. http://java.sun.com/blueprints/patterns/DAO.html
Etc.

All in all, there are a lot of good practices to do the data access. It is
really hard to tell which one is the best. It depends on the real situation
to choose the fittest solution. For instance, if the database has a lot of
tables, views, we may consider the solution (4) subsonic, which can
generate the data access codes automatically, and save a lot of time for
the developer. But if the data access involves a lot of cross-table
operations, I'd suggest (2) or (3) or (5).

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
(e-mail address removed) (Jialiang Ge [MSFT]) wrote in
(4) Subsonic (http://www.codeplex.com/subsonic) can automatically
generate the data access layer of a project according to the database
schema, so that programmers can focus on the business logic.
Click to expand...

I use LLBLGen Pro (http://www.llblgenpro.com) which is a similar prodct.
Works very well.

As for DB practices, you should use SQL parameters to avoid SQL injection
attacks:

http://msdn2.microsoft.com/en-us/library/ms998271.aspx

Or for general security guidance:

http://msdn2.microsoft.com/en-us/library/ms998408.aspx
 
I was just wondering if there were any sample projects out there that show
some standard approaches developers take to handle data access. Just to get
some ideas of my own together. I already have my DAL together, but I just
want to see other approaches out there to see if my approach is a good one.

Thanks.
 
Greg,

Be aware that there are many samples from people who have one time done it
and are so proud on their selves that they finaly succeeded that they want
to show it to the world as the best there is ever made.

One of the reasons that I so often write that there is no "Best", is that my
next solution is better than the one I made today while the one from todays
was better than that from yesterday.

Don't expect to get real good samples from Microsoft covering complete
solutions. Those guys who build our samples are basicly toolbuilders. In
their samples they show you what is possible, however that does not mean
that you always should use it. They are learning you to fish. They don't
give you the fish.

Just my opinion.

Cor
 
I agree with you that there are no best practices. And, yes, every time I do
another project, I get better each time. But, over the years I have learned
to improve my skills by looking at other developers examples. I rarely
implement anything other developers have done without examining it and
improving upon it myself. I've gone through books and gottne good ideas on
how i will be building my project, but if I am able to see how others are
doing it as well I will be able to develop an even better solution. I have no
doubt, of all the developers out there, some of them have come up with some
great ideas. Heck, I've worked with some great developers who have
contributed a great deal to the way I develop today.

Anyway, thanks for your opinion and I certainly agree with the fact that
there are no real "best" practices, although I am sure many would disagree
with that. I probably should have worded my question as follows.

Does anyone know of any good examples posted on the internet of Data Access
solutions using VB.Net 2005 and SQL Server 2005. I am looking for examples so
that I can expand my existing knowledge, and possibly, learn something new
that I have not come across yet. Or, if you know of any samples that you feel
are great examples, then I would be interested in seeing them.

Thanks.
 
Hello Greg,

Here are some good examples of building DAL for your reference.

Tutorial 1: Creating a Data Access Layer by Scott Mitchell:
http://msdn2.microsoft.com/en-us/library/aa581778.aspx

Persistence layer built by Enterprise Library:
http://www.codeplex.com/entlib/Wiki/View.aspx?title=Sample Files&referring
Title=Home

DLINQ walkthrough
http://download.microsoft.com/download/5/8/6/5868081c-68aa-40de-9a45-a3803d8
134b8/dlinq_designer.doc

A sample that uses XLINQ
http://billmccarthy.com/Projects/Snippet_Editor/

If you have any other concern or need anything else, please feel free to
let me know.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights
 
Hi Greg,

If you need further assistance, feel free to let me know. I will be more
than happy to be of assistance.

Have a great day!

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top