/// <summary>
/// Checks the property is exist or not in ExcelTableEntity
/// </summary>
/// <param name="strProperName"></param>
/// <param name="strValue"></param>
/// <param name="entity"></param>
/// <returns></returns>
private bool CheckPropertyExist(string strProperName, string strValue, ExcelTableEntity entity)
{
bool bln_Result = false;
Type entityType = typeof(ExcelTableEntity);
PropertyInfo[] ProList = entityType.GetProperties();
for (int i = 0; i < ProList.Length; i++)
{
if (ProList[i].Name == strProperName)
{
if (ProList[i].PropertyType.Name == "DateTimeOffset")
{
DateTime dtime = Convert.ToDateTime(strValue);
dtime = DateTime.SpecifyKind(dtime, DateTimeKind.Utc);
DateTimeOffset utcTime2 = dtime;
ProList[i].SetValue(entity, utcTime2);
}
else
{
ProList[i].SetValue(entity, strValue);
}
bln_Result = true;
break;
}
}
return(bln_Result);
}
/// <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();
}