/// <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(); }