[RESOLVED]Batch Table Insert

[SeaCapnJ]

I need to do batch inserts with my datatable. Is there a simple way to do this? I have filled my datatable with new rows, but How can I update the Oracle database? The Dataadapter properiy "update" only seems to do one line at a time. What is the best way to do this? Any links or resources would be much appreciated.

Thanks

[CyberHawke]

Have you considered using the UpdateBatch method?

[CyberHawke]

Sorry for that, there is no UpdateBatch method.
One of the problems with boucing between platforms is forgetting which methods apply to which platform.
But in your post you stated that the update method only updates one row at a time. This is simply not true. The update method will update all changes made to a dataset or datatable.

[SeaCapnJ]

I was searching for the batchupdate, thinking how could I have missed that! Thanks for such a quick reply and the correction.

In regards the .update only updating one record at a time, let me be more specific.

.update is not neccessarily the problem. The Data adapter takes a parameter of command. When I insert one line at a time I give the Command connection string the equivalent sql string ('insert into ....).

However, Now that I need to insert more than one row, I am not sure how to have a dynamic command connection string. If that is even what I am supposed to do.

I am just trying to figure out what the right direction to go is.

Thanks!

[CyberHawke]

I hope I cleaned this up completely, it is from a C# project I am working on.

'Declarations Section
Private m_daJobsInfo As SqlDataAdapter = New SqlDataAdapter
Private m_cmdSelectJobsInfo As SqlCommand = New SqlCommand
Private m_cmdInsertNewJob As SqlCommand = New SqlCommand
Private m_cmdUpdateJobStatus As SqlCommand = New SqlCommand
Private m_cmdDeleteJob As SqlCommand = New SqlCommand
Private m_dtJobs As DataTable = New DataTable
 
Private Sub Form_Load()
    Me.m_cmdSelectJobsInfo.CommandText = "SELECT Job_ID, Job_Type_ID, LocationCode, CountryName, Status, Server, EnteredBy, EnteredDate, FirstRunDate, Notes FROM FOR_Jobs_List WHERE MONTH(EnteredDate) = MONTH(GETDATE()) AND DAY(EnteredDate) = DAY(GETDATE()) AND YEAR(EnteredDate) = YEAR(GETDATE()) AND Status = 'Not Started' ORDER BY Job_ID"
    Me.m_cmdSelectJobsInfo.Connection = new SqlConnection(string.Format(m_connectInfo,m_serverName,m_jobsDB))
    Me.m_cmdInsertNewJob.CommandText = "INSERT INTO FOR_Jobs_List (Job_ID, Job_Type_ID, LocationCode, CountryName, Status, Server, EnteredBy, EnteredDate, FirstRunDate, Notes) VALUES(@Job_Type_ID,@LocationCode,@CountryName,@Status,@Server,@EnteredBy,@EnteredDate,@FirstRunDate,@Notes)"
    Me.m_cmdInsertNewJob.Parameters.Add(new SqlParameter("@Job_Type_ID", SqlDbType.Int, 4, "Job_Type_ID"))
    Me.m_cmdInsertNewJob.Parameters.Add(new SqlParameter("@LocationCode", SqlDbType.VarChar, 3, "LocationCode"))
    Me.m_cmdInsertNewJob.Parameters.Add(new SqlParameter("@CountryName", SqlDbType.VarChar, 50, "CountryName"))
    Me.m_cmdInsertNewJob.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar, 100, "Status"))
    Me.m_cmdInsertNewJob.Parameters.Add(new SqlParameter("@Server", SqlDbType.VarChar, 30, "Server"))
    Me.m_cmdInsertNewJob.Parameters.Add(new SqlParameter("@EnteredBy", SqlDbType.VarChar, 50, "EnteredBy"))
    Me.m_cmdInsertNewJob.Parameters.Add(new SqlParameter("@EnteredDate", SqlDbType.DateTime, 8, "EnteredDate"))
    Me.m_cmdInsertNewJob.Parameters.Add(new SqlParameter("@FirstRunDate", SqlDbType.DateTime, 8, "FirstRunDate"))
    Me.m_cmdInsertNewJob.Parameters.Add(new SqlParameter("@Notes", SqlDbType.VarChar, 200, "Notes"))
    Me.m_cmdInsertNewJob.Parameters.Add(new SqlParameter("@Job_ID", SqlDbType.Int, 4).Direction=ParameterDirection.ReturnValue)
    Me.m_cmdInsertNewJob.Connection = new SqlConnection(string.Format(m_connectInfo,m_serverName,m_jobsDB))
    Me.m_cmdUpdateJobStatus.CommandText = "UPDATE FOR_Jobs_List SET Status = @Status WHERE (Job_ID = @Original_Job_ID)SELECT Job_ID, Job_Type_ID, LocationCode, CountryName, Status, Server, EnteredBy, EnteredDate, FirstRunDate, Notes FROM FOR_Jobs_List WHERE (Job_ID = @Job_ID)"
    Me.m_cmdUpdateJobStatus.Parameters.Add(new SqlParameter("@Original_Job_ID", System.Data.SqlDbType.Int, 4, ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Job_ID", System.Data.DataRowVersion.Original, null))
    Me.m_cmdUpdateJobStatus.Parameters.Add(new SqlParameter("@Job_ID", SqlDbType.Int, 4, "Job_Type_ID"))
    Me.m_cmdUpdateJobStatus.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar, 100, "Status"))
    Me.m_cmdUpdateJobStatus.Connection = new SqlConnection(string.Format(m_connectInfo,m_serverName,m_jobsDB))
    Me.m_cmdDeleteJob.CommandText = "DELETE FOR_Jobs_List WHERE (Job_ID = @Original_Job_ID)SELECT Job_ID, Job_Type_ID, LocationCode, CountryName, Status, Server, EnteredBy, EnteredDate, FirstRunDate, Notes FROM FOR_Jobs_List"
    Me.m_cmdDeleteJob.Parameters.Add(new SqlParameter("@Original_Job_ID", System.Data.SqlDbType.Int, 4, ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Job_ID", System.Data.DataRowVersion.Original, null))
    Me.m_cmdDeleteJob.Connection = new SqlConnection(string.Format(m_connectInfo,m_serverName,m_jobsDB))
    Me.m_daJobsInfo.SelectCommand = Me.m_cmdSelectJobsInfo
    Me.m_daJobsInfo.InsertCommand = Me.m_cmdInsertNewJob
    Me.m_daJobsInfo.UpdateCommand = Me.m_cmdUpdateJobStatus
    Me.m_daJobsInfo.DeleteCommand = Me.m_cmdDeleteJob
End Sub
 
Public Sub CreateMulitplePostJob(_user As String,_jobTypeID As Int32)
    Dim _cnTargetServer As SqlConnection = new SqlConnection(string.Format(m_connectInfo,m_serverName,m_dataDB))
    Dim _cnTargetServer.Open
    Dim _cmdActivePosts As SqlCommand = new SqlCommand("spSelActivePosts",_cnTargetServer)
    Dim _drActivePosts As SqlDataReader
    Try
        m_daJobsInfo.Fill(m_dtJobs)
        Dim _JobTypes As DataRow() = m_dtJobTypes.Select("Job_Type_ID = " + _jobTypeID)
        Dim _description As String = _JobTypes(0)("Description").ToString
        _drActivePosts = _cmdActivePosts.ExecuteReader
        If _drActivePosts.HasRows = True Then
            Do While _drActivePosts.Read
                Dim _loc As String = _drActivePosts["Location"].ToString
                Dim _country As String = _drActivePosts["Country"].ToString
                Dim _newJob As DataRow = m_dtJobs.NewRow
                _newJob["Job_Type_ID"] = _jobTypeID
                _newJob["LocationCode"] = _loc
                _newJob["CountryName"] = _country
                _newJob["Status"] = "Not Started"
                _newJob["Server"] = m_serverName
                _newJob["EnteredBy"] = _user
                _newJob["EnteredDate"] = DateTime.Now
                _newJob["Notes"] = "Testing"
                m_dtJobs.Rows.Add(_newJob)
            Loop
            m_daJobsInfo.Update(m_dtJobs)
            m_dtJobs.Clear
            dim _daJobs As SqlDataAdapter = New SqlDataAdapter("spselJobs",string.Format(m_connectInfo,m_serverName,m_jobsDB))
            _daJobs.Fill(m_dtJobs)
        End If
    Catch(Exception ex)
        MessageBox.Show(ex.ToString)
    End Try
    _cnTargetServer.Close
End Sub

[CyberHawke]

The above code works in my C# project and definitely allows you to Insert, Update, and Delete several records in your DataTable before calling the DataAdapter.Update method.

If you have problems with this let me know, I'll see what I can do to get it working

[SeaCapnJ]

With your help I was able to get the bulk insert working. Unfortunately UpdateCommand is giving me a problem.
The error message complains of an invalid number.

Any thoughts are greatly appreciated.

Dim da As New Oracle.DataAccess.Client.OracleDataAdapter
 
        da.InsertCommand = New Oracle.DataAccess.Client.OracleCommand _
            ("INSERT INTO getap_input_values" _
            & "(ID,CID,BID,UDID,PSID,GSID,USID,DIMENSION1,DIMENSION2,DIMENSION3,DIMENSION4,DATAVALUE,NAME,REASON)VALUES" _
            & "(GETAP_INPUT_VALUES_SEQ.nextval,:iCID,:iBID,:iUDID,:iPSID,:iGSID,:iUSID" _
            & ",:iDIMENSION1,:iDIMENSION2,:iDIMENSION3,:iDIMENSION4,:iDATAVALUE,:iNAME,:iREASON)", epromdevConnection)
 
        da.InsertCommand.Parameters.Add("iCID", Oracle.DataAccess.Client.OracleDbType.Int16, 4, "CID")
        da.InsertCommand.Parameters.Add("iBID", Oracle.DataAccess.Client.OracleDbType.Int16, 0, "BID")
        da.InsertCommand.Parameters.Add("iUDID", Oracle.DataAccess.Client.OracleDbType.Int16, 0, "UDID")
        da.InsertCommand.Parameters.Add("iPSID", Oracle.DataAccess.Client.OracleDbType.Int16, 0, "PSID")
        da.InsertCommand.Parameters.Add("iGSID", Oracle.DataAccess.Client.OracleDbType.Int16, 0, "GSID")
        da.InsertCommand.Parameters.Add("iUSID", Oracle.DataAccess.Client.OracleDbType.Int16, 0, "USID")
        da.InsertCommand.Parameters.Add("iDIMENSION1", Oracle.DataAccess.Client.OracleDbType.Int16, 0, "DIMENSION1")
        da.InsertCommand.Parameters.Add("iDIMENSION2", Oracle.DataAccess.Client.OracleDbType.Int16, 0, "DIMENSION2")
        da.InsertCommand.Parameters.Add("iDIMENSION3", Oracle.DataAccess.Client.OracleDbType.Int16, 0, "DIMENSION3")
        da.InsertCommand.Parameters.Add("iDIMENSION4", Oracle.DataAccess.Client.OracleDbType.Int16, 0, "DIMENSION4")
        da.InsertCommand.Parameters.Add("iDATAVALUE", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200, "DATAVALUE")
        da.InsertCommand.Parameters.Add("iNAME", Oracle.DataAccess.Client.OracleDbType.Varchar2, 50, "NAME")
        da.InsertCommand.Parameters.Add("iREASON", Oracle.DataAccess.Client.OracleDbType.Varchar2, 500, "REASON")
 
 
        da.UpdateCommand = New Oracle.DataAccess.Client.OracleCommand _
            ("UPDATE getap_input_values SET DATAVALUE = :iDATAVALUE WHERE CID = :iCID", epromdevConnection)
        
 
     da.UpdateCommand.Parameters.Add("iCID", Oracle.DataAccess.Client.OracleDbType.Int16, 0, "CID")
     da.UpdateCommand.Parameters.Add("iDATAVALUE", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200, "DATAVALUE")
 
 
        Try
            da.Update(tblIptVal)
        Catch e As Exception
            MsgBox(e.ToString)
            MsgBox(e)
        End Try

[SeaCapnJ]

Ok, to add more to the weirdness. I can have one variable (:iCID or :iDATAVALUE) in the command and it will work. For reason setting the variable and using a variable past where do not work.

[CyberHawke]

Ahhh.. I see your problem . . . Oracle, now you should know Microsoft doesn't play well with others
(Bill please don't take my cert away)

But seriously, the problem is you are not referring to your orginal row identity when updating your dataset.
You see what actually happens when you perform an update is, the datatable creates a new row that references your original row, the new row contains the changes, the original row is marked for update. This is how rollbacks are supported in the event you don't want to commit your changes to your datasource.

If you peruse my code a little more closely, you will see that part of the code in the parameter for the rowid makes a reference to the original rowid.

I don't personally know the datatypes and references for the Oracle client, but I'm sure they are not much different from the one for the SqlClient.

Let me know if this doesn't work, I do have a few Oracle servers laying around the office I can play with

[SeaCapnJ]

Order matters.

I simply had to declare the DATAVALUE parameter before any of the other parameters, and BOOM succesful! I will keep an eye out for that row version stuff. Thanks so much Hawke!