Solved

VB.NET update DataTable using ADO.NET

Posted on 2013-05-10
11
820 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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

14 Experts available now in Live!

Get 1:1 Help Now