locked
How do I update a table in batches ? RRS feed

  • Question

  • I have an application that processes a large number of input files in a CSV format and then posts the data to a table on SQL Server Express.

    The data table can end up very large and I have no requirements to store all the data locally.
    I have included the table in my DataSet using visual studio express so I have access to the schema, but will not run Fill() on it.

    Ideally I would like to process a CSV file at a time.
    I can add records to my local (empty) data table and when I am happy, I can call tableAdapter.Update() or dataSet.DataTable.AcceptChanges() to generate lots of SQL 'INSERT' commands to update the physical database at the server end.

    I would then like to empty my local data table (so it doesn't get too big) and repeat the same process over again for each CSV file.

    How can I empty my local table without causing it to generate a load of SQL 'DELETE' commands?  I want to empty the table and fool ADO.NET into thinking that everything is synchronised, as if it has just done an update but actually hasn't.


    Regards

    Tuesday, July 25, 2006 11:43 AM

Answers

  • hi, if I correctly understood your scenario, you never sync the source with your local dataset/datatable.. you only feed your local data via CSV files.. so you can perhaps just skip the sync at all, and provide only INSERT INTO (even via a stored proc as well :D) using just a prepared Command, and then cycle on it filling it's value and executing it, similar to
    Dim conn As System.Data.SqlClient.SqlConnection = New SqlClient.SqlConnection("...")
    Dim cmd As New SqlClient.SqlCommand
    With cmd
     .CommandText = "[dbo].[usp_insert_proc]"
     .CommandType = CommandType.StoredProcedure
     .Connection = conn
     .Parameters.Add(New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, Nothing))

     Dim Par As SqlClient.SqlParameter
     Par = .Parameters.Add(New SqlClient.SqlParameter("@ParamName", SqlDbType.VarChar))
     Par.Direction = ParameterDirection.Input

     'add further

     .Prepare()
    End With


    Dim dt As System.Data.DataTable = New System.Data.DataTable or get only changes, but youd' only have added rows

    For curRow As Int32 = 1 To dt.Rows.Count
     With cmd
      With .Parameters.Item("@ParamName")
       .Size = 1
       .Value = "value"
      End With
     End With
     cmd.ExecuteNonQuery()

     dt.Rows(curRow).Delete()
    Next


    and here you can eventually destroy and recreate your local datatable, so it always is fresh and clean :D

    regards

    Tuesday, July 25, 2006 5:25 PM

All replies

  • hi,

    if I correclty understood that you "never" sync your data FROM the SQL Server instance, but you always fill your dataset via the CSV files, you only need to process providing INSERT INTO statements and clearing (dropping all rows from) your dataset..

    instead of simply using the .Update method, just create a prepared command (based on stored procedures as well, if possible ) and cycle among the dataset/datatale rows performing "single manual updates" repeatedly consuming the ADO.Net command and filling it's parameters..

     something like

    Dim conn As System.Data.SqlClient.SqlConnection = New SqlClient.SqlConnection("...")
    Dim cmd As New SqlClient.SqlCommand
    With cmd
     .CommandText = "[dbo].[usp_insert_proc]"
     .CommandType = CommandType.StoredProcedure
     .Connection = conn
     .Parameters.Add(New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, Nothing))

     Dim Par As SqlClient.SqlParameter
     Par = .Parameters.Add(New SqlClient.SqlParameter("@ParamName", SqlDbType.VarChar))
     Par.Direction = ParameterDirection.Input

     'add further

     .Prepare()
    End With


    Dim dt As System.Data.DataTable = New System.Data.DataTable or get only changes, but youd' only have added rows

    For curRow As Int32 = 1 To dt.Rows.Count
     With cmd
      With .Parameters.Item("@ParamName")
       .Size = 1
       .Value = "value"
      End With
     End With
     cmd.ExecuteNonQuery()

     dt.Rows(curRow).Delete()
    Next


    and here you can eventually destroy and recreate your local datatable

    regards

    Tuesday, July 25, 2006 5:21 PM
  • hi, if I correctly understood your scenario, you never sync the source with your local dataset/datatable.. you only feed your local data via CSV files.. so you can perhaps just skip the sync at all, and provide only INSERT INTO (even via a stored proc as well :D) using just a prepared Command, and then cycle on it filling it's value and executing it, similar to
    Dim conn As System.Data.SqlClient.SqlConnection = New SqlClient.SqlConnection("...")
    Dim cmd As New SqlClient.SqlCommand
    With cmd
     .CommandText = "[dbo].[usp_insert_proc]"
     .CommandType = CommandType.StoredProcedure
     .Connection = conn
     .Parameters.Add(New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", DataRowVersion.Current, Nothing))

     Dim Par As SqlClient.SqlParameter
     Par = .Parameters.Add(New SqlClient.SqlParameter("@ParamName", SqlDbType.VarChar))
     Par.Direction = ParameterDirection.Input

     'add further

     .Prepare()
    End With


    Dim dt As System.Data.DataTable = New System.Data.DataTable or get only changes, but youd' only have added rows

    For curRow As Int32 = 1 To dt.Rows.Count
     With cmd
      With .Parameters.Item("@ParamName")
       .Size = 1
       .Value = "value"
      End With
     End With
     cmd.ExecuteNonQuery()

     dt.Rows(curRow).Delete()
    Next


    and here you can eventually destroy and recreate your local datatable, so it always is fresh and clean :D

    regards

    Tuesday, July 25, 2006 5:25 PM