The CData ODBC Driver for Microsoft Dynamics 365 offers bulk load support in Microsoft Dynamics 365 through the ODBC Batch API. The driver can execute related SQL data manipulation statements simultaneously by translating them into a single bulk API request. In your application's code, the driver executes statements based on an array of inputs and a parameterized query.

Batch Update Procedure

Complete the following steps to execute a batch update:

1. Define arrays of column values for each parameter in the statement.
2. Set the SQL_ATTR_PARAMSET_SIZE statement attribute.
3. Bind each array to each parameter.
4. Execute the parameterized statement.

Bulk Insert

The following code shows how to execute a bulk insert with SQLSetStmtAttr, SQLBindParameter, and SQLExecDirect:

SQLHSTMT pHstmt = NULL;
const int MAX_INSERT_COUNT = 2;
const int MAX_BUFFER_SIZE = 100;
char GoalHeadingIdArray[MAX_INSERT_COUNT][MAX_BUFFER_SIZE] = { 0 };
SQLLEN cbGoalHeadingIdArray[MAX_INSERT_COUNT] = { 0 };
...
retcode = SQLSetStmtAttr(pHstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)MAX_INSERT_COUNT, 0);
char *GoalHeadingId1 = "Jon Doe", *GoalHeadingId2 = "John";
cbGoalHeadingIdArray[0] = strlen(GoalHeadingId1);
cbGoalHeadingIdArray[1] = strlen(GoalHeadingId2);
memcpy(GoalHeadingIdArray[0], GoalHeadingId1, strlen(GoalHeadingId1));
memcpy(GoalHeadingIdArray[1], GoalHeadingId2, strlen(GoalHeadingId2));
retcode = SQLBindParameter(pHstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, GoalHeadingIdArray, MAX_BUFFER_SIZE, cbGoalHeadingIdArray);

retcode = SQLExecDirect(pHstmt, (SQLCHAR*) "INSERT INTO GoalHeadings (GoalHeadingId) VALUES (?)", SQL_NTS); 

To retrieve the new records' Ids, query the LastResultInfo table:

SQLExecDirect(hstmt, (SQLTCHAR*)"SELECT * FROM LastResultInfo#TEMP", SQL_NTS);  
SQLTCHAR sGoalHeadingId[20] = {0};
SQLLEN cbGoalHeadingId = 0;
SQLBindCol(hstmt, 1, SQL_C_CHAR, sGoalHeadingId, 20, &cbGoalHeadingId);
SQLRETURN retcode = SQLFetch(hstmt);   

Bulk Update

An array of the primary keys of the records to update must be specified. The following code shows how to execute a bulk update with SQLSetStmtAttr, SQLBindParameter, and SQLExecDirect:

SQLHSTMT pHstmt = NULL;
const int MAX_UPDATE_COUNT = 2;
const int MAX_BUFFER_SIZE = 100;
char GoalHeadingIdArray[MAX_UPDATE_COUNT][MAX_BUFFER_SIZE] = { 0 };
char GoalHeadingIdArray[MAX_UPDATE_COUNT][MAX_BUFFER_SIZE] = { 0 };
SQLLEN cbGoalHeadingIdArray[MAX_UPDATE_COUNT] = { 0 };
SQLLEN cbGoalHeadingIdArray[MAX_UPDATE_COUNT] = { 0 };
...
retcode = SQLSetStmtAttr(pHstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)MAX_UPDATE_COUNT, 0);
char *GoalHeadingId1 = "Jon Doe", *GoalHeadingId2 = "John";
char *GoalHeadingId1 = "GoalHeadingId1", *GoalHeadingId2 = "GoalHeadingId2";
cbGoalHeadingIdArray[0] = strlen(GoalHeadingId1);
cbGoalHeadingIdArray[1] = strlen(GoalHeadingId2);
cbGoalHeadingIdArray[0] = strlen(GoalHeadingId1);
cbGoalHeadingIdArray[1] = strlen(GoalHeadingId2);
memcpy(GoalHeadingIdArray[0], GoalHeadingId1, strlen(GoalHeadingId1));
memcpy(GoalHeadingIdArray[1], GoalHeadingId2, strlen(GoalHeadingId2));
memcpy(GoalHeadingIdArray[0], GoalHeadingId1, strlen(GoalHeadingId1));
memcpy(GoalHeadingIdArray[1], GoalHeadingId2, strlen(GoalHeadingId2));
retcode = SQLBindParameter(pHstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, GoalHeadingIdArray, MAX_BUFFER_SIZE, cbGoalHeadingIdArray);
retcode = SQLBindParameter(pHstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, GoalHeadingIdArray, MAX_BUFFER_SIZE, cbGoalHeadingIdArray);

retcode = SQLExecDirect(pHstmt, (SQLCHAR*) "UPDATE GoalHeadings SET GoalHeadingId = ? WHERE GoalHeadingId = ?", SQL_NTS); 

Bulk Delete

An array of the primary keys of the records to delete must be specified. The following code shows how to execute a bulk delete with SQLSetStmtAttr, SQLBindParameter, and SQLExecDirect:

 
SQLHSTMT pHstmt = NULL;
const int MAX_DELETE_COUNT = 2;
const int MAX_BUFFER_SIZE = 100;
char GoalHeadingIdArray[MAX_DELETE_COUNT][MAX_BUFFER_SIZE] = { 0 };
SQLLEN cbGoalHeadingIdArray[MAX_DELETE_COUNT] = { 0 };
...
retcode = SQLSetStmtAttr(pHstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)MAX_DELETE_COUNT, 0);
char *GoalHeadingId1 = "GoalHeadingId1", *GoalHeadingId2 = "GoalHeadingId2";
cbGoalHeadingIdArray[0] = strlen(GoalHeadingId1);
cbGoalHeadingIdArray[1] = strlen(GoalHeadingId2);
memcpy(GoalHeadingIdArray[0], GoalHeadingId1, strlen(GoalHeadingId1));
memcpy(GoalHeadingIdArray[1], GoalHeadingId2, strlen(GoalHeadingId2));
retcode = SQLBindParameter(pHstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, GoalHeadingIdArray, MAX_BUFFER_SIZE, cbGoalHeadingIdArray);

retcode = SQLExecDirect(pHstmt, (SQLCHAR*) "DELETE FROM GoalHeadings WHERE GoalHeadingId = ?", SQL_NTS);