/// <summary>
/// Imports data of DataTable to table storage
/// </summary>
/// <param name="dtSheetInfo"></param>
/// <param name="strSheetName"></param>
private void ImportDataToTable(System.Data.DataTable dtSheetInfo, string strSheetName)
{
var client = storageAccount.CreateCloudTableClient();
string strTableName = txt_TableName.Text;
if (!string.IsNullOrEmpty(strTableName))
{
Response.Write(new string(' ', 1024));
Response.Write(String.Format("<div>Uploading {0} rows for sheet {1}", dtSheetInfo.Rows.Count, strSheetName.Replace("$", "")));
Response.Flush();
CloudTable table = client.GetTableReference(strTableName);
table.CreateIfNotExists();
// Create a new partition key for this data instead of overwriting old data.
var partitionKey = strSheetName + DateTime.UtcNow.ToString("o");
var batch = new TableBatchOperation();
for (int j = 0; j < dtSheetInfo.Rows.Count; j++)
{
ExcelTableEntity entity = new ExcelTableEntity(partitionKey, (j + 2).ToString("D5"));
var hasContent = false;
for (int i = 0; i < dtSheetInfo.Columns.Count; i++)
{
string strCloName = dtSheetInfo.Columns[i].ColumnName;
if (!(dtSheetInfo.Rows[j][i] is DBNull) && (dtSheetInfo.Rows[j][i] != null))
{
hasContent = true;
string strValue = dtSheetInfo.Rows[j][i].ToString().Trim();
if (!CheckPropertyExist(strCloName, strValue, entity))
{
EntityProperty property = entity.ConvertToEntityProperty(strCloName, dtSheetInfo.Rows[j][i]);
if (!entity.properties.ContainsKey(strCloName))
{
entity.properties.Add(strCloName, property);
}
else
{
entity.properties[strCloName] = property;
}
}
}
}
if (hasContent)
{
batch.Add(TableOperation.InsertOrReplace(entity));
}
if (batch.Count >= 100)
{
table.ExecuteBatch(batch);
Response.Write(".");
Response.Flush();
batch.Clear();
}
}
if (batch.Count > 0)
{
table.ExecuteBatch(batch);
Response.Write(".");
Response.Flush();
}
var pointer = new ExcelTableEntity(strSheetName.Replace("$", ""), "Latest");
pointer.properties.Add("ID", new EntityProperty(partitionKey));
table.Execute(TableOperation.InsertOrReplace(pointer));
Response.Write(String.Format("\n PartitionKey: <code>{0}</code></div><hr/>", partitionKey));
Response.Flush();
}
}
/// <summary>
/// Imports data of DataTable to table storage
/// </summary>
/// <param name="dtSheetInfo"></param>
/// <param name="strSheetName"></param>
private void ImportDataToTable(System.Data.DataTable dtSheetInfo, string strSheetName)
{
var client = storageAccount.CreateCloudTableClient();
CloudTable table = client.GetTableReference(strPIITable);
Response.Write(new string(' ', 1024));
Response.Write(String.Format("<div>Deleting existing data"));
Response.Flush();
table.DeleteIfExists();
create:
try
{
Response.Write(".");
Response.Flush();
table.Create();
}
catch (StorageException ex) when(ex.RequestInformation.ExtendedErrorInformation.ErrorCode.Equals(TableErrorCodeStrings.TableBeingDeleted))
{
Thread.Sleep(1000);
goto create;
}
Response.Write(String.Format("</div><div>Uploading {0} rows for sheet {1}", dtSheetInfo.Rows.Count, strSheetName.Replace("$", "")));
Response.Flush();
// Create a new partition key for this data instead of overwriting old data.
var partitionKey = strSheetName;
var batch = new TableBatchOperation();
for (int j = 0; j < dtSheetInfo.Rows.Count; j++)
{
ExcelTableEntity entity = new ExcelTableEntity(partitionKey, (j + 2).ToString("D5"));
var hasContent = false;
for (int i = 0; i < dtSheetInfo.Columns.Count; i++)
{
string strCloName = dtSheetInfo.Columns[i].ColumnName;
if (!(dtSheetInfo.Rows[j][i] is DBNull) && (dtSheetInfo.Rows[j][i] != null))
{
hasContent = true;
string strValue = dtSheetInfo.Rows[j][i].ToString().Trim();
if (!CheckPropertyExist(strCloName, strValue, entity))
{
EntityProperty property = entity.ConvertToEntityProperty(strCloName, dtSheetInfo.Rows[j][i]);
if (!entity.properties.ContainsKey(strCloName))
{
entity.properties.Add(strCloName, property);
}
else
{
entity.properties[strCloName] = property;
}
}
}
}
if (hasContent)
{
batch.Add(TableOperation.InsertOrReplace(entity));
}
if (batch.Count >= 100)
{
table.ExecuteBatch(batch);
Response.Write(".");
Response.Flush();
batch.Clear();
}
}
if (batch.Count > 0)
{
table.ExecuteBatch(batch);
Response.Write(".");
Response.Flush();
}
Response.Write("</div><hr/>");
Response.Flush();
}