Solved

VB.NET update DataTable using ADO.NET

Posted on 2013-05-10
11
946 Views
Last Modified: 2013-05-12
Hi experts,

how is it possible to update one column of DataTable in memory with values from second DataTable using ADO.NET? Both data tables have one column identical (ID) and sources are different databases (servers)

I would like to update column Price from first DataTable to second DataTable. Both data tables are from different data sources, thats why I can't use SQL command. Or is it possible to use SQL for it?

I do not want to use LINQ in this case, although LINQ is newer technology.

I have Visual Basic 2010 and .NET Framework 4.0.

Thanks.
0
Comment
Question by:skdan
[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
  • 6
  • 5
11 Comments
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39157669
Hi,

First of all it is possible to use SQL if there is a linked server set up, or the dbs are on the same server but i don't think this is your case.

I am not in a pc to write code but it goes like this:
        For Each row As DataRow In dt1.rows
            dt2.Select("ID=" + row("ID"))
            dt2.rows(0)("Price") = row("Price")
        Next

Open in new window

I have assumed dt1 and dt2 are your tables.

Again i would prefer either a liked server or LINQ.

Giannis
0
 

Author Comment

by:skdan
ID: 39157717
Hi Giannis,

thank you for answer.

I know, these are questions of beginner, but I kindly ask you for answers.

1. I use 3 different data sources and I use separate connections to each one (MS Access file, DBase files, MySQL server). Is there some possibility to use SQL as if data were on one server?

2. Is it possible to use LINQ similar like SQL queries (across more connections to databases)? I mean not only equivalent of SELECT command, but also UPDATE, JOIN, INNER JOIN commands etc. I mean if LINQ can completely substitute SQL for these purposes.

Thanks.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39157746
Hi,

1.I know you could do it if you had an SQL Server running. You could create linked server to MySql, Access and DBase. So in my first post i though you actually had an SQL Server. I don't know if this is doable from Access, sorry.

2.LINQ is great utility for getting data so SELECT is preferable with it. For Joins i have written an article found here and i hope you find helpful as an entry point.

For Inserts and Updates LINQ is limmited to row by row process, as a datatable would, so you might find it useful. I like it being strongly typed. There is actually a way of updating one datatable object without iterating through its every row with LINQ and this is creating a third datatable with a SELECT and then loading the datatable you need to update with the one you just created. See the following example:

First of all create a vb.net console application
Add a reference to your project to System.Data.DataSetExtensions
Then paste the next code to your Sub Main()
        'DECLARE the two tables
        Dim dt1 As New DataTable
        dt1.Columns.Add("RANGE", GetType(String))
        dt1.Columns.Add("desc", GetType(String))
        dt1.Columns.Add("qty", GetType(Integer))

        Dim dt2 As New DataTable
        dt2.Columns.Add("RANGE", GetType(String))
        dt2.Columns.Add("desc", GetType(String))
        dt2.Columns.Add("Qty", GetType(Integer))


        'Load data to tables
        Dim dr As DataRow = dt1.NewRow
        dr("Range") = "N001"
        dr("desc") = "cv plug"
        dr("qty") = 5
        dt1.Rows.Add(dr)

        dr = dt1.NewRow
        dr("Range") = "N002"
        dr("desc") = "car plug"
        dr("qty") = 3
        dt1.Rows.Add(dr)

        dr = dt2.NewRow
        dr("Range") = "N001"
        dr("desc") = "cv plug"
        dr("qty") = 0
        dt2.Rows.Add(dr)

        dr = dt2.NewRow
        dr("Range") = "N002"
        dr("desc") = "car plug"
        dr("qty") = 0
        dt2.Rows.Add(dr)

    
        'Declare a clone of the tables
        Dim dt3 As New DataTable
        dt3.Columns.Add("RANGE", GetType(String))
        dt3.Columns.Add("desc", GetType(String))
        dt3.Columns.Add("Qty", GetType(Integer))

        'This is just for display to show you the values before the update
        For Each row In dt2.Rows
            Console.WriteLine(row("Range").ToString + " " + row("desc").ToString + " " + row("qty").ToString)
        Next

        'Here the update happens
        Dim query = From a In dt1.AsEnumerable _
                   Join b In dt2.AsEnumerable _
                   On a.Field(Of String)("Range") Equals b.Field(Of String)("Range")
                   Select dt3.LoadDataRow( _
                   { _
                    a.Field(Of String)("Range"), _
                    a.Field(Of String)("desc"), _
                    a.Field(Of Integer)("qty") _
                   }, True)

        'Copy the updated data to dt2
        dt2 = query.CopyToDataTable


        'This is just for display to show you the values after the update
        For Each row In dt2.Rows
            Console.WriteLine(row("Range").ToString + " " + row("desc").ToString + " " + row("qty").ToString)
        Next

        Console.ReadLine()

Open in new window



The above code is a sample i have used in another question here in EE. It demonstrates how you could update a table using LINQ. It will certainly need some modification to fit your case.

Giannis
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:skdan
ID: 39158135
Hi,

I will try your solution soon, and I accept what you say, but for me is still not clear, how it was solved in .NET earlier, because if I remember well, LINQ started in .NET 3.5.

I believe, that work with multiple data sources was possible in .NET 2.0 (Visual Studio 2005) with ADO.NET technology, but I don't know how programers solved this in that time. Do you have some informations about that?

Thanks.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39158143
Do you mean TableAdapters?
0
 

Author Comment

by:skdan
ID: 39158456
I do not mean nothing specific, I try only imagine, that there is year 2005, I have Visual Studio 2005 and .NET Framework 2.0 and I would like to use 3 different data sources on different servers (MS Access, dBase, MySQL), use SQL queries for update one data source from second data source.

How was it possible in that time? Which technology was necessary to use and how?

Thanks.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39158537
0
 

Author Comment

by:skdan
ID: 39159230
Hi,

thank you for link. Do you have some example of code in Visual Basic .NET, where I can solve my problem using this technology?

We can create this situation for demonstration (see attached files):

1. We have two MS Access database files.

2. The first database file has one table "Prod1" and 3 columns (ProductID, ProductName, ProductPrice)

3. The second database file has one table "Prod2" and 2 columns (ProductID, ProductPrice)

4. I would like to update column ProductPrice of table "Prod2" with values from column ProductPrice of table "Prod1".

When both tables are in one database file, I simply use SQL command (I copied it from MS Access):

UPDATE Prod1 INNER JOIN Prod2 ON Prod1.ProductID = Prod2.ProductID SET Prod2.ProductPrice = [Prod1]![ProductPrice];

Open in new window


But when they are in different database files, not linked, I don't know, how to do it.

Thanks.
Prod.zip
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 500 total points
ID: 39159360
Hi,

Create a project.
From the menu choose 'View'->'Server Explorer'
In the 'Server Explorer' window add the two connections to your Access DB files
Add a new item (Data->DataSet) in your project for Prod1
In the designer of the dataset drag and drop the first table from the Server Explorer
Add a new item (Data->DataSet) in your project for Prod2
In the designer of the dataset drag and drop the second table from the Server Explorer

Now you have two DataSets with one table each. Each DataSet has one TableAdapter.
The TableAdapters have two parts. The first(upper) part has the columns of the Adapter. The lower one has all the defined methods of the Adapter. By default there are going to be a Fill and GetData methods that you will normally use to load data to the Adapter.

Now we are going to add a method to Prod2TableAdapter for Update.

Right Click on the name of the lower section of Prod2TableAdapter and select 'Add Query'
Choose SQL Statements
Choose 'UPDATE'
Replace the generated query with:
UPDATE `Prod2` SET `ProductPrice` = ? WHERE (`ProductID` = ?)

Open in new window

Give a name for your method

Now you have created the method to call for update rows in Prod2.

Next step is to add the datasets in your form.

Open the designer of your form
From the toolbox open Data group and double click on DataSet
From the dropdown select the first DataSet
Repeat for Prod2DataSet
Add a button to your form
Add a listbox on your form

In the code of the form add the following:
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim Prod1 As New DataTable
        Dim Prod1TableAdapter As New Prod1DataSetTableAdapters.Prod1TableAdapter
        Dim Prod2 As New DataTable
        Dim Prod2TableAdapter As New Prod2DataSetTableAdapters.Prod2TableAdapter

        Prod1 = Prod1TableAdapter.GetData
        Prod2 = Prod2TableAdapter.GetData

        ListBox1.Items.Add("Prod1 Before Update")
        ListBox1.Items.Add("-------------------")

        For Each row As DataRow In Prod1.Rows
            ListBox1.Items.Add(row(0).ToString + " " + row(1).ToString + " " + row(2).ToString)
        Next


        ListBox1.Items.Add("                   ")


        ListBox1.Items.Add("Prod2 Before Update")
        ListBox1.Items.Add("-------------------")

        For Each row As DataRow In Prod2.Rows
            ListBox1.Items.Add(row(0).ToString + " " + row(1).ToString + " " + row(2).ToString)
        Next

        ListBox1.Items.Add("                   ")


        ListBox1.Items.Add("Prod2 After Update")
        ListBox1.Items.Add("-------------------")

        For Each row As DataRow In Prod1.Rows
            Prod2TableAdapter.UpdateQuery(row(2), row(0))
        Next

        Prod2 = Prod2TableAdapter.GetData

        For Each row As DataRow In Prod2.Rows
            ListBox1.Items.Add(row(0).ToString + " " + row(1).ToString + " " + row(2).ToString)
        Next


    End Sub

Open in new window


Time to test.....Run the project....Hit the button.....Check the data in the ListBox......


Hope it helps,

Giannis
0
 

Author Comment

by:skdan
ID: 39159448
Hi Giannis,

your tutorial is really excellent. It works great and it will be very helpful for my purposes.

Thank you very much :-)
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39159538
Glad i could help...
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

719 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