• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Example of Updating a Database using a DataTable

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
cdemott33
Asked:
cdemott33
  • 6
  • 5
  • 2
  • +2
1 Solution
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
grayeCommented:
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
 
Gustav BrockCIOCommented:
> 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
cdemott33Author Commented:
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
 
Gustav BrockCIOCommented:
> .. "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
 
Jacques Bourgeois (James Burger)PresidentCommented:
What do you mean by DAL and BLL. I know a lot of acronyms, but not those.
0
 
Gustav BrockCIOCommented:
> What do you mean by DAL and BLL.

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

/gustav
0
 
grayeCommented:
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
 
Gustav BrockCIOCommented:
True.
In .Net there's never one single method that is "right" for everything. That's what creates the challenge and fun.

/gustav
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
cdemott33Author Commented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
Gustav BrockCIOCommented:
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
 
vastoCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now