none
Transferring data from Mimer to SQL server by way of datasets RRS feed

  • Question

  • Hi Friends,

     

    I have a Mimer database application which uses binary data(basically DICOM files) and hence the size of the database grows very large in days. Typically after 3 months of use, it grows to 60-65Gb.

     

    What i want to do is this:  When some records have become obsolete, i want these records to the SQL server for later reference.

     

    I am using ODBC for Mimer database and SQLdata for SQL Server.

     

    I am able to fetch the relevant records from the Mimer table.

     

    Dim cn As New Odbc.OdbcConnection

    Dim str As String = TextBox1.Text

    Dim txtstr As String = "select * from Patient where suid= '" & str & "' "

    cn.ConnectionString = "DSN=OTP_Database;uid=bom;pwd=TRIDENT#bom"

    Dim da1 As New Odbc.OdbcDataAdapter(txtstr, cn)

    cn.Open()

    da1.Fill(Ds1, "Patient")

    DataGrid1.DataSource = Ds1

    DataGrid1.DataMember = "Patient"

    cn.Close()

     

    Now i see the records in the grid.

     

    I am doing the 2nd step as follows:

     

    Has a sqlconnection(cnn), sqldataadapter(da) and a dataset(ds) for the SQL database. I use the following code to try to merge the data from the Mimer dataset to the SQL dataset.

     

    ds1.merge(ds)

    da.update(ds)

     

    I have bound the SQL Server dataset (ds) to a 2nd datagrid just to see if the records are merged. I can see the new records in the 2nd datagrid. The update doesnt take place, no new records are added to the sql server table. There are no errors reported.

     

    I have found that this is not possible in .Net 1.1 and less. How can i achive this w/o moving to .Net 2.

     

    Is there an alternate way to achive this.

     

    Thanks for all the answers.

     

    kesk

     

     

     

     

    Wednesday, June 13, 2007 10:40 AM

Answers

  • Well,

     

    I found a way to overcome this by the following method myself:

     

    dsDest.Merge(dsSource)

     

    dim dt as DataTable = dsDest.Table(0)

    Dim cnt As Integer = dsDest.Tables(0).Rows.Count
                
      Dim i As Integer

         For i = 0 To cnt - 1
             dsDest.Tables(0).Rows(i).SetAdded()
           Next i

     

    Me.BindingContext(dsDest, "MyTable").EndCurrentEdit()
                daDest.Update(dsDest)

     

     

    The trick is to set the Rows.SetAdded method on the destination dataset. This way the dataset thinks that new rows are added and accepts that HasChanges=True and now we are free to Update the dataadapter.

     

    I would still wait for some other better way of doing this.

     

    kesk

    Wednesday, June 13, 2007 4:26 PM

All replies

  • I failed to mention the following details:

     

    1. Both the source data and the destination data has the same data types.

    2. The destination table initially will not contain any data as its empty.

    3. Before merging the datasets, i use the fill method to fill the data from the destination table (It doesnt bring any data as its empty initially).

     

    kesk

    Wednesday, June 13, 2007 11:01 AM
  • Well,

     

    I found a way to overcome this by the following method myself:

     

    dsDest.Merge(dsSource)

     

    dim dt as DataTable = dsDest.Table(0)

    Dim cnt As Integer = dsDest.Tables(0).Rows.Count
                
      Dim i As Integer

         For i = 0 To cnt - 1
             dsDest.Tables(0).Rows(i).SetAdded()
           Next i

     

    Me.BindingContext(dsDest, "MyTable").EndCurrentEdit()
                daDest.Update(dsDest)

     

     

    The trick is to set the Rows.SetAdded method on the destination dataset. This way the dataset thinks that new rows are added and accepts that HasChanges=True and now we are free to Update the dataadapter.

     

    I would still wait for some other better way of doing this.

     

    kesk

    Wednesday, June 13, 2007 4:26 PM