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; }