Link to home
Start Free TrialLog in
Avatar of skdan
skdanFlag for Slovakia

asked on

VB.NET update DataTable using ADO.NET

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.
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

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
Avatar of skdan

ASKER

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.
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
Avatar of skdan

ASKER

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.
Do you mean TableAdapters?
Avatar of skdan

ASKER

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.
Avatar of skdan

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of skdan

ASKER

Hi Giannis,

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

Thank you very much :-)
Glad i could help...