public async Task UpsertDocumentAsync <T>(string dbName, string collectionName, string id, T data)
{
var doc = new DocumentDBEntry <T>(id, data);
var collectionUri = UriFactory.CreateDocumentCollectionUri(dbName, collectionName);
await Client.UpsertDocumentAsync(collectionUri, doc);
}
// Step (4-5)
// Converts documentDB (JSON) string to SQL table
// @return: Table name
private string CreateTempSQLTableAndInsertJSON(List<SQLSchemaData> schemaList, string docDBJsonResults)
{
StringBuilder sbCreate = new StringBuilder("CREATE TABLE " + TempTableName + " (");
StringBuilder sbInsert = new StringBuilder("INSERT INTO " + TempTableName + " (");
List<DocumentDBEntry> documentList = new List<DocumentDBEntry>();
string[] rawDocumentList = Regex.Split(docDBJsonResults, "(?={)");
string returnValue = "";
DataTable dataTable = new DataTable();
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(SqlConnectionObject,
SqlBulkCopyOptions.TableLock |
SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.UseInternalTransaction,
null);
sqlBulkCopy.DestinationTableName = TempTableName;
// (3.5)
// Construct the string required to create the SQL table
//foreach (SQLSchemaData schemaElement in schemaList)
for (int i = 0; i < schemaList.Count; ++i)
{
sbCreate.Append("[").Append(schemaList[i].ColumnName).Append("]");
switch(schemaList[i].ColumnType)
{
case (SQLTypes.E_bigint):
sbCreate.Append("[bigint]");
dataTable.Columns.Add(schemaList[i].ColumnName, typeof(Int64));
break;
case (SQLTypes.E_nvarchar):
sbCreate.Append("[nvarchar](512) NOT NULL");
dataTable.Columns.Add(schemaList[i].ColumnName, typeof(string));
break;
case (SQLTypes.E_datetime):
sbCreate.Append("[datetime] NULL");
dataTable.Columns.Add(schemaList[i].ColumnName, typeof(string));//typeof(DateTime));
break;
default:
break;
}
sbInsert.Append(schemaList[i].ColumnName);
if (i + 1 != schemaList.Count) // Add comma
{
sbCreate.Append(", ");
sbInsert.Append(", ");
}
else // Close the statement
{
sbCreate.Append(")");
sbInsert.Append(") VALUES ");
}
}
// Create Temporary table
SqlConnectionObject.Open();
returnValue = ExecuteSQLCommand(sbCreate.ToString());
// (4.5)
// Convert docDBJsonResults to a structure we can easily parse
// Ignore the first entry which is empty
for (int i = 1; i < rawDocumentList.Length; ++i)
{
DocumentDBEntry document = new DocumentDBEntry(BuildSchemaList(rawDocumentList[i]));
documentList.Add(document);
}
// (4.75)
// Construct SQL string needed to insert data!
// Need to use BulkCopy here otherwise we will run into:
//
// Error: The number of row value expressions in the INSERT
// statement exceeds the maximum allowed number of 1000 row values.
foreach (var document in documentList)
{
int i = 0;
DataRow row = dataTable.NewRow();
foreach (var schema in document.SchemaList)
{
row[i++] = schema.ColumnValue;
}
dataTable.Rows.Add(row);
}
// (5)
// Take results and insert into SQL table
sqlBulkCopy.WriteToServer(dataTable);
return returnValue;
}
