Solved

VB.NET application - import/export data between applications (using datasets)?

Posted on 2003-10-30
19
3,997 Views
Last Modified: 2008-09-12
Hi,

Help and advice needed on building a vb.net app to port datasets around between various different applications. I'm looking for ideas on what approach to use, ideally plus example code.

I think a sensible way to do this is to setup classes for each of the applications - each class can import from a file to a common format (eg dataset), and export from that common format out to a file. I can then easily move data between the formats.

File formats I'm using so far are Excel, Access, SPSS and .csv.

1. Is that a good way to do it? Any comments / better ideas ...

2. Seems straightforward to import data from Excel and Access to a dataset. But how would I go the other way, eg saving dataset/table to a new Access table / Excel worksheet? Ideally wouldn't have to go through all the fields / records manually ...

3. Anyone have any pointers on how to do this with SPSS (I guess that's a long shot ;-)

Cheers, Tom.
0
Comment
Question by:tmc_smith
  • 10
  • 4
  • 2
  • +2
19 Comments
 
LVL 10

Expert Comment

by:GoodJun
ID: 9652590
If the source and target data share the same schema, then you can use the dataset.merge method to transfer the data. (dsSource and dsTarget have similar schema)
Search MSDN for the dataset.merge method and its overloaded methods.
dasource.AcceptChangesDuringfill=False
daSource.Fill(dsSource)
daTarget.Fill(dsTarget)
dsTarget.merge(dsSource)
daTarget.Update()
0
 

Author Comment

by:tmc_smith
ID: 9652813
Hi GoodJun - thanks for the help.

Trying this out now, hadn't come across merge before, looks useful. Will get back to you in 5 ...

Will this work if the datatable I want to transfer from the source does not exist at all in the target?

Cheers, Tom.
0
 
LVL 4

Expert Comment

by:NetPointer
ID: 9652831
1. why dont u use xml?
2. send Create table query to create a table in access or u can use com interop with excel for this too..
3. http://www.spss.com/spssbi/spss/data_access.htm
dont know more...about spss
0
 

Author Comment

by:tmc_smith
ID: 9652949
Hi GoodJun,

the merge works fine for the dataset, but it fails on updating back to the target database. The failure ms is:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Update unable to find TableMapping['Table'] or DataTable 'Table'.


My code below:

Private Sub ExcelToAccess(ByVal strDirName As String, ByVal strExcelFile As String, ByVal strAccessFile As String)

        ' getting data from excel ...
        Dim tablename As String = "Districts"
        Dim strConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDirName & strExcelFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
        Dim strCommand As String = "SELECT * FROM [" & tablename & "$]"
        Dim ds_source As DataSet = New DataSet
        Dim da_source As OleDbDataAdapter = New OleDbDataAdapter(strCommand, strConnect)
        da_source.AcceptChangesDuringFill = False
        da_source.Fill(ds_source, tablename)

        ' putting data into Access ...
        Dim strConnectAccess As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDirName & strAccessFile
        Dim strCommandAccess As String = "SELECT * FROM [tbl]"
        Dim ds_target As DataSet = New DataSet
        Dim da_target As OleDbDataAdapter = New OleDbDataAdapter(strCommandAccess, strConnectAccess)
        da_target.Fill(ds_target, "tbl")

        ' check what we've loaded in the target dataset
        DataGrid1.DataSource = ds_target.Tables("tbl").DefaultView()
        MsgBox(strExcelFile)

        ' marge the table into the target dataset
        ds_target.Merge(ds_source.Tables(tablename))

        ' check what we've loaded
        DataGrid1.DataSource = ds_target.Tables(tablename).DefaultView()
        MsgBox(strExcelFile)

        ' update the Access database
        da_target.Update(ds_target)

    End Sub
0
 
LVL 10

Expert Comment

by:GoodJun
ID: 9653018
'The table name should be the same, I think. Try this to see if it works:
'Change these
da_source.Fill(ds_source, tablename)
da_target.Fill(ds_target, "tbl")
ds_target.Merge(ds_source.Tables(tablename))
DataGrid1.DataSource = ds_target.Tables(tablename).DefaultView()

'To
da_source.Fill(ds_source)
da_target.Fill(ds_target)
ds_target.Merge(ds_source)
DataGrid1.DataSource = ds_target.Tables(0).DefaultView()
0
 

Author Comment

by:tmc_smith
ID: 9653038
Hi NetPointer,

Is there any advantage to using xml over a dataset? Is it easier to export into Access and Excel?

I was hoping to avoid having to build-up "create table" sql for MS Access - ideally i'm looking for methods to export the dataset without looping through the fields and records.

Cheers, Tom.
0
 

Author Comment

by:tmc_smith
ID: 9653187
GoodJun

Not sure that works - the table of data I want to get from the source to the target does not exist yet in the target ("tbl" is just an empty table. I want to create a new table called 'tablename' in the target database).

[It gives different failure message tho (as it is trying to update the table rather than add a new one):
Additional information: Update requires a valid InsertCommand when passed DataRow collection with new rows.]

Am I trying to do something that doesn't easily work in .net? ie:

I have a datatable called 'tablename'. That datatable might have come from an xml file, an Excel spreadhsheet, an Access database or whatever.

I want to take that datatable and save/export it. For example as a new spreadsheet called 'datatable' in an Excel file, or as a new table called 'datatable' in an Access database. I could then have functions ExportToAccess(dataset/datatable, tablename) and ExportToExcel(dataset/datatable, tablename)

I assumed I could do this fairly easily, without looping through all fields and records. Eg. something like dataset.xmlwrite(). Maybe that's not the case?

Tom.
0
 
LVL 10

Expert Comment

by:GoodJun
ID: 9653365
The method is assume you have the table already. To create a table in database, from .net you can use the oledbcommand object and execute a sql statement to create the table. Merge method in not for create table but transfer data. You can create the table first, then use merge to transfer the data.
0
 

Author Comment

by:tmc_smith
ID: 9654154
Thanks GoodJun - seems a shame not to be able to add a dataset automatically, but there you go ...

Found and modified the code from http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_20751983.html

    Private Sub DataTableToAccess(ByRef dt As DataTable, ByVal strFileName As String, ByVal strTableName As String)

        Dim dc As DataColumn
        Dim strSql As String = ""

        ' get the list of columns and build the SQL ...
        For Each dc In dt.Columns
            strSql = strSql & """" & dc.ColumnName & """ " & dc.DataType.Name & ","
        Next
        strSql = "CREATE TABLE " & strTableName & " (" & strSql.Substring(0, (strSql.Length - 1)) & ");"
        MsgBox(strSql)

        ' Open Access and execute the query ...
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName
        Dim conn As OleDbConnection = New OleDbConnection(strConn)
        Dim cmd As OleDbCommand = New OleDbCommand(strSql, conn)

        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()

This works until I get a lot of columns, then fails with "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll" - not sure why, maybe limit to size of text in OleDbCommand?

Heading to bed (UK - been here 14 hours already today ...), will look at stuff tomorrow
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Expert Comment

by:etmendz
ID: 9655172
I think you are trying to achieve what is commonly coined as any-to-any transformation. It is a key concept in enabling application integration as that in the Microsoft Biztalk Server. But because Biztalk is expensive, you can play around with the concepts of Biztalk in order to create your own integration engine.

The core technology is XML. And in order to enable transformation from one format to another, you'll need to use XSL/XSLT.

INPUT
You can create/re-use "adapter classes" to convert the original data format into a DataSet. Remember that a DataSet is actually an XML document in memory. You can get the XML using the DataSet.GetXml().

PARAMETER
Using the generated XML text, you can now apply XSLT to it in order to generate another XML (or a text-based file format). It's as easy as calling XslTransform.Transform().

OUTPUT
If necessary, the next step is to create/re-use "adapter classes" that will parse the generated XML into a target data format.

Advantages:
- one time development of the engine;
- future projects that will use the engine may require only the creation of the XSLT file for the project;
- XSLT development is easy and may promote shorter project turn around time (TAT);
- the "adapter classes" are smaller and easier to develop;
- your rich set of XSLTs and adapter classes measures the flexibility and maturity of your product in the long run.

Have fun...
0
 

Author Comment

by:tmc_smith
ID: 9656830
Thanks Etmendz - reassuring I'm not trying a totally stupid strategy ...

I've setup the dataset structure to be created in Access, with the contents added in after. Works without error, the table is created, but not the table contents. Any ideas?

    Private Sub DataTableToAccess(ByRef dt As DataTable, ByVal strFileName As String)

        Dim dc As DataColumn
        Dim strSql As String = ""

        ' get the list of columns and build the SQL ...
        For Each dc In dt.Columns
            strSql = strSql & "[" & dc.ColumnName & "] " & dc.DataType.Name & ", "
            'exit For
        Next
        strSql = "CREATE TABLE " & dt.TableName & " (" & strSql.Substring(0, (strSql.Length - 2)) & ");"
        MsgBox(strSql)

        ' Open Access and execute the query ...
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName
        Dim conn As OleDbConnection = New OleDbConnection(strConn)
        Dim cmd As OleDbCommand = New OleDbCommand(strSql, conn)

        ' try the query ...
        Try
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
        Catch ex As Exception
            MsgBox("DataTableToAccess() " & ex.Message)
        End Try

        ' get the Access table, merge in the dataset, and update ...
        Dim strCommand As String = "SELECT * FROM [" & dt.TableName & "]"
        Dim ds As DataSet = New DataSet
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(strCommand, conn)
        Dim cb As New OleDbCommandBuilder(da)
        conn.Open()

        da.AcceptChangesDuringFill = False
        da.Fill(ds, dt.TableName)

        DataGrid1.DataSource = ds.Tables(dt.TableName).DefaultView()
        MsgBox("wait0")

        ' try merging the datatable in ...
        Try
            ds.Merge(dt)
            DataGrid1.DataSource = ds.Tables(dt.TableName).DefaultView()
        Catch ex As Exception
            MsgBox("DataTableToAccess() " & ex.Message)
        End Try
        MsgBox("wait")

        ' try updating the dataset back to the database ...
        Try
            da.Update(dt)
            ds.AcceptChanges()
        Catch ex As Exception
            MsgBox("DataTableToAccess() " & ex.Message)
        End Try
        conn.Close()
        DataGrid1.DataSource = ds.Tables(dt.TableName).DefaultView()
        MsgBox("wait2")

    End Sub
0
 
LVL 6

Accepted Solution

by:
etmendz earned 125 total points
ID: 9667967
You might want to read the documentation of MSDN about using DataSet.Merge(). One example shows the use of DataSet.GetChanges() when using DataSet.Merge(). Here's the sample code (ms-help://MS.MSDNQTR.2002APR.1033/cpguide/html/cpconmergingdatasetcontents.htm):

-----
The following code example takes an existing DataSet with updates and passes those updates to a DataAdapter to be processed at the data source. The results are then merged into the original DataSet. After rejecting changes that resulted in an error, the merged changes are committed with AcceptChanges.

  Dim custTable As DataTable = custDS.Tables("Customers")

  ' Make modifications to the Customers table.
  ' ... some codes here like in your code maybe ;-)

  ' Get changes to the DataSet.
  Dim updDS As DataSet = custDS.GetChanges()

  ' Add an event handler to handle the errors during Update.
  AddHandler custDA.RowUpdated, New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)

  nwindConn.Open()
  custDA.Update(updDS, "Customers")
  nwindConn.Close()

  ' Merge the updates.
  custDS.Merge(updDS, true, MissingSchemaAction.Add)

  ' Reject changes on rows with errors and clear the error.
  Dim errRows() As DataRow = custDS.Tables("Customers").GetErrors()
  Dim errRow As DataRow
  For Each errRow In errRows
    errRow.RejectChanges()
    errRow.RowError = Nothing
  Next

  ' Commit the changes.
  custDS.AcceptChanges()


Private Shared Sub OnRowUpdated(sender As Object, args As SqlRowUpdatedEventArgs)
  If args.Status = UpdateStatus.ErrorsOccurred
    args.Row.RowError = args.Errors.Message
    args.Status = UpdateStatus.SkipCurrentRow
  End If
End Sub


Have fun...
0
 
LVL 10

Assisted Solution

by:GoodJun
GoodJun earned 125 total points
ID: 9670562
Hi. Just have a chance to follow this thread. You almost got there.
The reason why the contents are not copied over is the rowstate of the merged data is not shown as Added (you can check the .rowstate property of the row in the merged dataset).
This line of code:da.AcceptChangesDuringFill = False is for the source dataadpapter, its purpose is mark everyrow's rowstate as Added, it is not for the target dataset.
0
 

Author Comment

by:tmc_smith
ID: 9746487
GoodJun and etmendz - thanks for the help. I've been away for a few days, but now back on the case. Will try your suggestions and get back to you today.

Cheers, Tom.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 10232264
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: etmendz {http:#9667967} & GoodJun {http:#9670562}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

TheLearnedOne
EE Cleanup Volunteer
0
 

Author Comment

by:tmc_smith
ID: 10279543
Sorry for not clearing this up - I also have some additional comments to add in, so please don't close it yet ...

Will close up and award points today.
0
 

Author Comment

by:tmc_smith
ID: 10281830
Some information about getting data out of SPSS data files in case anyone gets this far! There are several ways of getting data out of SPSS format:

--------------
Automation:

Although SPSS exposes methods for opening and analysing files, the only method to get the data into a VB object - GetTextData() - is fairly useless on SPSS versions 10 and above due to the way the data files are accessed in memory. GetTextData() needs enormous memory with any reasonable sized SPSS data file (my 1Gb RAM system runs out memory on files of over 5Mb), and takes forever (30 mins on a 2Mb file ...). SPSS are aware there is a problem on this, but didn?t give any plans to fix it in future releases.

--------------
Binary file access:

SPSS do provide a dll enabling you to open SPSS data files directly, along with functions to get at the data, but I didn?t get this to work with VB.NET. If anyone has any joy with this I'd be interested.

--------------
ODBC:

You can extract data from SPSS files using ODBC. There's the usual limit of 255 variables thru ODBC, but this was good enough for what I needed.

1. Install the SPSS ODBC driver from SPSS CD (under the "Install SPSS Data Access Pack" heading)
2. Setup an ODBC data source (under control panel [ ->administrative tools] ->Data Sources (ODBC) ) pointing to the directory your SPSS files are in
3. Usual ADO.NET commands to get dsn data source, specifying the table name as the SPSS file name in the directory your DSN points to (thus each SPSS .sav file is seen as a different table)

Eg, to get data out of file called 'MyData.sav', in a directory pointed to by my system DSN called 'MyDSN':

Dim strQuery As String = "select * from MyData"
Dim strConn As String = "DSN=MyDSN;"

Dim ds As New DataSet
Dim conn As OdbcConnection = New OdbcConnection(strConn)
Dim da As OdbcDataAdapter = New OdbcDataAdapter(strQuery, conn)

da.Fill(ds)
da = Nothing
conn.Close()

Hope that helps someone struggling with SPSS data access!
0
 

Author Comment

by:tmc_smith
ID: 10289088
Thanks for awarding points, and thanks for all the help. I'm putting in my working code for reference.

There's a simpler method than using merge(). You can straightforwardly use da_target.Update(ds_source), but you must call da_source.AcceptChangesDuringFill = False when filling the source dataset (GoodJun explained this bit above, at the time I didn't understand why) . Otherwise, da_target.Update() will not see any data in the source that it regards as having 'changed'.

    ''''''''''''''''''''''''''''''''''''''''''''''
    '' DataTableToAccess()
    '' Given datatable, creates and updates into Access database.
    '' Args: datatable dt; string Access-filename
    '' Returns: -
    ''''''''''''''''''''''''''''''''''''''''''''''
    Public Sub DataTableToAccess(ByRef dt As DataTable, ByVal strFileName As String)

        '' call to create the Access table
        CreateAccessTable(dt, strFileName)

        '' call to update Access table
        UpdateAccessTable(dt, strFileName)

    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''
    '' UpdateAccessTable()
    '' Given datatable, updates into Access database.
    '' Args: datatable dt; string Access-filename
    '' Returns: -
    ''''''''''''''''''''''''''''''''''''''''''''''
    Sub UpdateAccessTable(ByRef dt As DataTable, ByVal strFileName As String)

        ' get data-adaptor for the Access table
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName
        Dim conn As OleDbConnection = New OleDbConnection(strConn)
        Dim strCommand As String = "SELECT * FROM [" & dt.TableName & "]"
        Dim ds_target As DataSet = New DataSet
        Dim da_target As OleDbDataAdapter = New OleDbDataAdapter(strCommand, conn)

        ' create the insert command for the data-adaptor
        Dim cb As New OleDbCommandBuilder(da_target)
        da_target.InsertCommand = cb.GetInsertCommand

        ' update the datatable into the Access database (may fail if table in database is not identical)
        da_target.Update(dt)

    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''
    '' CreateAccessTable()
    '' Given datatable, creates table in Access database.
    '' Args: datatable dt; string Access-filename
    '' Returns: -
    ''''''''''''''''''''''''''''''''''''''''''''''
    Sub CreateAccessTable(ByRef dt As DataTable, ByVal strFileName As String)

        Dim dc As DataColumn
        Dim strSql As String = ""
        Dim strDataType As String

        ' check tablename is not empty (defaults to "Table" which fails in CREATE TABLE ...)
        If (dt.TableName = "Table") Then dt.TableName = "NewTable"

        ' get the list of columns and build the SQL ...
        For Each dc In dt.Columns
            strSql = strSql & "[" & dc.ColumnName & "] " & dc.DataType.Name & " not null, "
        Next
        strSql = "CREATE TABLE " & dt.TableName & " (" & strSql.Substring(0, (strSql.Length - 2)) & ");"

        ' Open Access and execute the query ...
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName
        Dim conn As OleDbConnection = New OleDbConnection(strConn)
        Dim cmd As OleDbCommand = New OleDbCommand(strSql, conn)

        ' try the query, if fails likely to already exist ...
        Try
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
        Catch ex As Exception
            '  MsgBox("DataTableToAccess() " & ex.Message)
        End Try

    End Sub
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
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…

707 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

17 Experts available now in Live!

Get 1:1 Help Now