Wednesday, June 5, 2013

SalesForce Using the Bulk API sample VB.Net

This is in continuation from my previous post where I had explained how to fetch data from SalesForce using the SOAP API. Now we are going to see how to fetch data using Bulk API.

Prerequisites:

User Name
Password
Security Token (needed for the Api calls as the password is considered as password+security token)
Client Secret (Required for REST Call)
Client Id (Required for REST Call)
Call Back (This is required if we are authenticating from the web, else if we have a console application this could be some dummy value)


Steps:
1.     Create a console application
2.     Create a text file called “Request.txt” with the following contents
select Id,CaseNumber   from Case

3.     In the main class (Program.vb) add the below methods
At a high level below is the process
·         You will need to create a Job in SalesForce. This will create an entry in the SalesForce Bulk Data load jobs. Once the job is created you get a Job Id.

You can see the entry in SalesForce


·          Add the batch – this gets the file with the select query and adds it as part of the input stream. Pass the job id obtained after adding the job
·         You need to post and get the Batch Id
·         Finally when the batch gets executed, you can retrieve the Results id.
·         Using the Job id, batch id and results id retrieve the results and output it to a file.

Note: I have not included the logic needed to wait / check if the batch has been run or not.

4.     Run the console application


Sample Code:

Add the below methods to a console application

Private Sub QueryUsingBulkAPI()

        Dim jobId As String = String.Empty
        Dim resultId As String = String.Empty
        Dim batchId As String = String.Empty


        'Used to create the Job in SalesForce--
        'the Job can be query, insert, delete etc...This is the operation parameter
        'eg for querying Case
        CreateJob(loginRes.sessionId, "query", "Case", jobId)
        'e.g for inserting contact
        'Dim jobId As String = CreateJob(loginRes.sessionId, "insert", "Contact")

        Dim inputFileData() As Byte
        If (jobId.Length > 0) Then

            'data loading using a sampe file
            ' Open a file that is to be loaded into a byte array
            Dim oFile As System.IO.FileInfo
            'oFile = New System.IO.FileInfo("data.csv")
            oFile = New System.IO.FileInfo("request.txt")

            Dim oFileStream As System.IO.FileStream = oFile.OpenRead()
            Dim lBytes As Long = oFileStream.Length

            If (lBytes > 0) Then
                Dim fileData(lBytes - 1) As Byte
                ' Read the file into a byte array
                oFileStream.Read(fileData, 0, lBytes)
                oFileStream.Close()

                'Get the file where the Query is present
                inputFileData = fileData
            End If

            'Adds the batch to SalesForce
            AddBatch(loginRes.sessionId, inputFileData, jobId, batchId, resultId)

            'Check Status and Get BatchId and ResultsId
            GetStatusAndRetrieveBatchIdAndResultsId(loginRes.sessionId, jobId, batchId, resultId)

            'Get Results Id
            RetrieveResults(loginRes.sessionId, jobId, batchId, resultId)

        End If

    End Sub

    ''' <summary>
    ''' Creates a job in Salesforce
    ''' </summary>
    ''' <param name="sfSessionId"></param>
    ''' <param name="sfOperation"></param>
    ''' <param name="sfObjectName"></param>
    ''' <param name="jobId"></param>
    ''' <remarks></remarks>
    Public Sub CreateJob(ByVal sfSessionId As String,
                         ByVal sfOperation As String,
                         ByVal sfObjectName As String,
                         ByRef jobId As String)

        Dim str As String = ""
        Dim reqURL As String = ""
        Dim bytes() As Byte
        Dim reqDoc As XmlDocument
        Dim responseXmlDocument As XmlDocument = New XmlDocument
        '''Make changes to ContentType field if you want to
        '''post it as CSV or XML
        str = "" + "<?xml version=""1.0"" encoding=""UTF-8""?>" &
        "<jobInfo xmlns=""http://www.force.com/2009/06/asyncapi/dataload"">" + "    <operation></operation>" &
        "    <object></object>" &
        "    <contentType>CSV</contentType>" &
        "</jobInfo>"
        'Eg for XML content type
        '"    <contentType>XML</contentType>" &

        reqURL = "https://na1.salesforce.com/services/async/23.0/job"
        reqDoc = New XmlDocument
        reqDoc.LoadXml(str)
        ' added XML modifications
        reqDoc.GetElementsByTagName("operation")(0).InnerText = sfOperation
        reqDoc.GetElementsByTagName("object")(0).InnerText = sfObjectName
        bytes = System.Text.Encoding.ASCII.GetBytes(reqDoc.InnerXml)
        'bytes = System.Text.Encoding.UTF8.GetBytes(reqDoc.InnerXml)
        Using responseStream As Stream = Post(bytes, reqURL, sfSessionId, "POST", "text/csv; charset=UTF-8")
            responseXmlDocument.Load(responseStream)
            'Get jobId
            jobId = IIf((Not (responseXmlDocument) Is Nothing), responseXmlDocument.GetElementsByTagName("id").Item(0).InnerText, "")
        End Using

    End Sub

    ''' <summary>
    ''' Adds the Batch to SalesForce
    ''' </summary>
    ''' <param name="sfSessionId"></param>
    ''' <param name="fileBytes"></param>
    ''' <param name="sfJobId"></param>
    ''' <param name="sfBatchId"></param>
    ''' <param name="sfResultId"></param>
    ''' <remarks></remarks>
    Public Sub AddBatch(ByVal sfSessionId As String,
                        ByVal fileBytes() As Byte,
                        ByVal sfJobId As String,
                        Optional ByVal sfBatchId As String = Nothing,
                        Optional ByVal sfResultId As String = Nothing)

        Dim requestURI As String = ("https://login.salesforce.com/services/async/23.0/job/" _
                    + (sfJobId + "/batch"))
        Post(fileBytes, requestURI, sfSessionId, "POST", "text/csv; charset=UTF-8")

    End Sub

    ''' <summary>
    ''' Once the batch is added get the BatchId and then
    ''' once the processing is done , get the Results ID
    ''' </summary>
    ''' <param name="sfSessionId"></param>
    ''' <param name="sfJobId"></param>
    ''' <param name="batchId"></param>
    ''' <param name="resultId"></param>
    ''' <remarks></remarks>
    Public Sub GetStatusAndRetrieveBatchIdAndResultsId(ByVal sfSessionId As String,
                                                       ByVal sfJobId As String,
                                                       ByRef batchId As String,
                                                       ByRef resultId As String)


        Dim responseXmlDocument As XmlDocument = New XmlDocument
        Dim reqURL As String = ("https://na1.salesforce.com/services/async/23.0/job/" _
                   + sfJobId + "/batch")
        'Get BatchId
        Using responseStream As IO.Stream = Post(Nothing, reqURL, sfSessionId, "GET", "text/csv; charset=UTF-8")
            responseXmlDocument.Load(responseStream)
            batchId = IIf((Not (responseStream) Is Nothing), responseXmlDocument.GetElementsByTagName("id").Item(0).InnerText, "")
        End Using

        'Get ResultId
        reqURL = ("https://na1.salesforce.com/services/async/23.0/job/" _
                   + (sfJobId + "/batch/" + batchId + "/result"))
        Using responseStream As IO.Stream = Post(Nothing, reqURL, sfSessionId, "GET", "text/csv; charset=UTF-8")
            responseXmlDocument.Load(responseStream)
            resultId = IIf((Not (responseStream) Is Nothing), responseXmlDocument.GetElementsByTagName("result").Item(0).InnerText, "")
        End Using

    End Sub

    ''' <summary>
    ''' Post the rest call with batch id and results id to get the output
    ''' </summary>
    ''' <param name="sfSessionId"></param>
    ''' <param name="sfJobId"></param>
    ''' <param name="sfBatchId"></param>
    ''' <param name="sfResultId"></param>
    ''' <remarks></remarks>
    Public Sub RetrieveResults(ByVal sfSessionId As String,
                               ByVal sfJobId As String,
                               ByVal sfBatchId As String,
                               ByVal sfResultId As String)

        Dim reqURL As String = ("https://na1.salesforce.com/services/async/23.0/job/" _
                    + (sfJobId + "/batch/" + sfBatchId + "/result/" + sfResultId))
        Dim localFile As String = "output.csv"
        'Create the output file
        Using responseStream As IO.Stream = Post(Nothing, reqURL, sfSessionId, "GET", "text/csv; charset=UTF-8")
            Using fsOutputFile As New IO.FileStream(localFile, FileMode.Create, FileAccess.Write)
                Dim buffer(2047) As Byte
                Dim read As Integer
                Do
                    read = responseStream.Read(buffer, 0, buffer.Length)
                    fsOutputFile.Write(buffer, 0, read)
                Loop Until read = 0
                responseStream.Close()
                fsOutputFile.Flush()
                fsOutputFile.Close()
            End Using
            responseStream.Close()
        End Using

    End Sub

    ''' <summary>
    ''' Function to POST the HTTP rest request
    ''' </summary>
    ''' <param name="bytes"></param>
    ''' <param name="reqURL"></param>
    ''' <param name="sfSessionId"></param>
    ''' <param name="method"></param>
    ''' <param name="contentType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Post(ByVal bytes() As Byte, ByVal reqURL As String, ByVal sfSessionId As String, ByVal method As String, ByVal contentType As String) As Stream

        'Create the request object
        Dim requestHttp As WebRequest = WebRequest.Create(reqURL)
        'Assign the type of request POST,GET..
        requestHttp.Method = method
        'Assign Content Type
        requestHttp.ContentType = contentType '"text/csv; charset=UTF-8" or "application/xml; charset=UTF-8"
        'Assign the session id to the header
        requestHttp.Headers.Add(("X-SFDC-Session: " + sfSessionId))

        'Assign byte length
        If (bytes IsNot Nothing) Then
            requestHttp.ContentLength = bytes.Length

            Dim strmHttpContent As System.IO.Stream = requestHttp.GetRequestStream
            strmHttpContent.Write(bytes, 0, bytes.Length)
            strmHttpContent.Close()

        End If

        'Get the response object
        Dim responseHttpRequest As WebResponse = requestHttp.GetResponse
        'Return response Stream
        Return responseHttpRequest.GetResponseStream


    End Function