Solved

Example of Updating a Database using a DataTable

Posted on 2011-09-12
16
227 Views
Last Modified: 2012-05-12
I was recently advised that I should NOT use DataSet and TableAdaptors to preform Updates and Inserts to a individual table within my database.  They strongly suggested I use a DataTable to perform these sort of updates.

I've hunted Google for a good example of how one does this but, believe it or not, I'm not finding anything worth following.

I'm hoping one of you here on the Exchange could provide me with a very simple example of your UPDATE DAL and you BLL function that would call the Update.  

The simipler the better.  I'm really trying to wrap my head around using DataTables for Updates rather than DataSet.   I really want to do it the right way.

Thanks in advance for your help.
0
Comment
Question by:cdemott33
  • 6
  • 5
  • 2
  • +2
16 Comments
 
LVL 40
ID: 36524364
There are as many variations as there are programmers and situations. That is why working with DataTables gives you a lot of control over the automatically generated classes. It is harder to learn at the beginning, but the extra control and the fact that you will be using them for years instead of having to master a new technology everytime they have a new version of Visual Studio will have you gain a lot of time in the medium to long run.

At the simplest, here is how it works.
Dim cmbCommandes As New OleDbCommandBuilder()
Dim dadPhotos As New OleDbDataAdapter()
Dim dtbPhotos As New DataTable

'Fill the table
dadPhotos.SelectCommand = New OleDbCommand("SELECT * FROM tbPhotos", New OleDbConnection("Your ConnectionString"))
'The command builder will automatically generate the necessary update commands
'*** If it is able to do so, which is usually the case with standard tables that have a primary key ***
cmbCommandes = New OleDbCommandBuilder(dadPhotos)
dadPhotos.Fill(dtbPhotos)

'Work with the DataTable, something like
Dim id As Integer
id = CInt(dtbPhotos.Rows(2).Item("PhotoID"))
dtbPhotos.Rows(4).Item("Photographer") = "Ansel Adams"

'Update the result of your work
'You can do this after each line if you want, but can wait and do it in batch later
dadPhotos.Update(dtbPhotos)

Open in new window

My sample uses the OleDB classes that are found in the System.Data.OleDB namespace. They have the advantage of working with almost any database. For performance and in order to be able to use the specifity of a given database, you might want to use classes conceived for the database in use. They all have the same base, so you all use them the same way. Here is an equivalent of the previous for SQL Server, using classes in the System.Data.SqlClient namespace:
Dim cmbCommandes As New SqlDbCommandBuilder()
Dim dadPhotos As New SqlDataAdapter()
Dim dtbPhotos As New DataTable

'Fill the table
dadPhotos.SelectCommand = New SqlCommand("SELECT * FROM tbPhotos", New SqlConnection("Your ConnectionString"))
'The command builder will automatically generate the necessary update commands
'*** If it is able to do so, which is usually the case with standard tables that have a primary key ***
cmbCommandes = New SqlCommandBuilder(dadPhotos)
dadPhotos.Fill(dtbPhotos)

'Work with the DataTable, something like
Dim id As Integer
id = CInt(dtbPhotos.Rows(2).Item("PhotoID"))
dtbPhotos.Rows(4).Item("Photographer") = "Ansel Adams"

'Update the result of your work
'You can do this after each line if you want, but can wait and do it in batch later
dadPhotos.Update(dtbPhotos)

Open in new window

Notice that the DataTable itself is independant of the library used to get and update the data. Once the data is in the DataTable, you use it with exactly the same syntax no matter if you went through OleDB or directly to the server through the SqlClient namespace.

0
 
LVL 41

Expert Comment

by:graye
ID: 36524448
Let's start off with that premise...  I totally disagree with the idea that you should not use TableAdapters to perform the updates.

Are you sure you heard/understood the advice?   Could you elaborate?

TableAdapters are use a "grown up" version of the DataAdapter class (as shown in JamesBurger's comment above).  TableAdapters are just the strongly-typed versions of DataAdapter, so you'd actually be using the DataAdapter class behind the scences, if you used TableAdapters!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36524561
> TableAdapters are just the strongly-typed versions of DataAdapter ..

And that's the whole point: Strongly-typed!
Browse to the bottom here:

http://www.asp.net/learn/data-access/

This will learn you to stay off the low level stuff with SqlCommand etc. which in my opinion is waste of time.
This site alone will keep you busy for a long time. I have never bought a book - there is so much free material to learn from including videos.

/gustav
0
 
LVL 40
ID: 36525366
TableAdapter are easier to use and are strongly typed, I concede that.

But search for TableAdapter on MSDN, the Microsoft site for developers (http://social.msdn.microsoft.com/Search/en-us). You will see that they stopped documenting TableAdapters with Visual Studio 2005, 7 years ago. That is a very long time in this field that is ours. They do not care anymore that that TableAdapters have been first replaced by LINQ, which as itself been superseeded by Data Entities.

An no matter which of these technologies you use, you need to learn the basics anyway when these let you down. So better learn and master the basics than never getting to master anything because it is replaced every 2 or 4 years.
0
 

Author Comment

by:cdemott33
ID: 36525390
Hi graye - I was actually following JamesBurger's advise that he provided here:

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_27300685.html

James suggested going the route of using a DataTable vs the DataSet/TableAdaptor route found in this article:

http://msdn.microsoft.com/en-us/library/aa581778.aspx

...and he back it up with some pretty interesting facts.

cactus - Are you promoting the use of TableAdaptors in my case?  Please explain.  I'm not sure what you meant by "low level stuff".

James.  Thank you for your examples.  I'm trying to figure out how to break your code into a DAL class and a BLL class.  Any suggestions?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36525498
> .. "low level stuff"

That is the bottom level where you write embedded SQL for anything - as in classic ASP.

DataTables should be the minimum level to use as they are strongly typed. They work fine "as is" (also in VS2010) and are quite easy to use.

But, of course, if you wish to go more advanced, go for the Entity Framework and LINQ which are very very powerful and flexible - but takes a little more - I wouldn't say "to master" because that's a full-time profession. Challenge and fun are two other keywords.

/gustav
0
 
LVL 40
ID: 36525513
What do you mean by DAL and BLL. I know a lot of acronyms, but not those.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36525538
> What do you mean by DAL and BLL.

Top figure (Introduction) at the link I provided above.

/gustav
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 41

Expert Comment

by:graye
ID: 36525634
I hate to contribute to an online discussion between experts, but...

TableAdapters are NOT being replaced by LINQ and Entity Framework.   They are not event competing technologies!  

But, don't listen to me (or the others)... I'd highly recommend that you do your own research and form your own opinions (rather than rely on our opinions!)
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36525688
True.
In .Net there's never one single method that is "right" for everything. That's what creates the challenge and fun.

/gustav
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 36525750
Landline phones were not replaced by mobile phones, but...

Video stores have not been replaced by Netflix and a few others... they are just dying.

MS-DOS was not replaced by Windows... you can still use it. Same for .bat files. Same for <you name it>.

TableAdapters are not being replaced by LINQ and Entity Framework... they are just frozen to what they were in 2005 because Microsoft is now investing all its energies in LINQ and Entity Framework. As far as I am concerned, that means replaced.

In 40 years in this field, I have see them pass, those wonderful tools that do everything for you. And I can tell you that hanging to older technologies that have stopped to evolve is never a good decision in the medium to long run. I know of a few places where they are still running on Windows 3.1, because they did not make the switch to VB4 when we made the switch to 32-bits. When they finally decided to move on, there were no replacements for their old .vbx controls and it was too costly to rewrite the VB3 applications. I could go on like that for hours, but I have only a few minutes.

I will repeat myself. If you are an amateur, go for it, you are there to have fun, cactus_data put it very nicely with "Challenge and fun are two other keywords". But if you are a pro, work like a pro. A good mechanic or a good woodworker knows it very well. Multi-purpose tools do not work well. It's better to have 15 different good screwdrivers than have a cheap one that tries to do everything.

0
 
LVL 40
ID: 36525760
Correction to the previous one.

"Microsoft is now investing all its energies in LINQ and Entity Framework" should read "Microsoft is now investing all its energies in Entity Framework", without reference to LINQ.
0
 

Author Comment

by:cdemott33
ID: 36525766
Hi James - Here's what I'm refering too...

DAL = Data Access Layer
BLL = Business Logic Layer

Maybe these terms are no longer used?
0
 
LVL 40
ID: 36526173
Yes, these terms are still used, but for some reason, I never encountered the acronym. Might be my French environment.

We might be speaking of something entirely different there. No TablesAdapters, no DataTable, but classes and collections that you design yourself.

In fact, the aim of all those easy to use tools that we have been discussing is to do that for you. They do provide the DAL.

A discussion about those concepts might be quite involved and I do not have a lot of time right now.

Are you at ease with the design and implementation of classes and collections?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36527481
The turorials at the site I linked to demonstrate how DAL and BLL are build on top of DataTables - if you need them and prefer that route.

It is perfectly OK to settle with DataTables proven as they are. I've used them a lot and still do.

/gustav
0
 
LVL 18

Expert Comment

by:vasto
ID: 36535895
cdemott33, Tableadapters are easy to implement and they are perfectly acceptable for small projects. If you need to update multiple tables in the database TableAdapter will be able to serve you , because you can connect it to a stored procedures and to handle the updates as you want. However if you have a grid and you change 200 rows on this grid and you try to save the changes with TableAdapter it will result in 200 calls to the database which will be pretty expensive and slow update. What is your goal : to update multiple tables in the database or to reduce the number of the calls to the database ? It seems to me that the person who made the advice was pointing you to a way to reduce the calls , but the whole discussion here went to a direction how to use TableAdapters.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now