private void CellListInsert(ExcelRead excelRead)
{
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(ConnectionManager.Connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.CheckConstraints, null); // TableLock because of Azure.
sqlBulkCopy.DestinationTableName = string.Format("{0}Excel", ConnectionManager.Prefix);
//
DataTable dataTable = new DataTable();
dataTable.Columns.Add("Id", typeof(int));
dataTable.Columns.Add("FieldNameId", typeof(int));
dataTable.Columns.Add("SheetNameId", typeof(int));
dataTable.Columns.Add("RowName", typeof(int));
dataTable.Columns.Add("ColumnNameId", typeof(int));
dataTable.Columns.Add("ValueNumber", typeof(double));
dataTable.Columns.Add("ValueText", typeof(string));
//
int count = 0;
foreach (string fileName in excelRead.CellList.Keys)
{
int fileNameId = Name.Id(fileName);
foreach (string sheetName in excelRead.CellList[fileName].Keys)
{
int sheetNameId = Name.Id(sheetName);
foreach (string rowName in excelRead.CellList[fileName][sheetName].Keys)
{
foreach (string columnName in excelRead.CellList[fileName][sheetName][rowName].Keys)
{
ExcelRead.ExcelCell cell = excelRead.CellList[fileName][sheetName][rowName][columnName];
int columnNameId = Name.Id(columnName);
//
string valueText = cell.ValueText;
if (valueText != null && valueText.Length > ConnectionManager.ExcelVaueTextLengthMax)
{
// log("Warning! Cell truncate.");
valueText = valueText.Substring(0, ConnectionManager.ExcelVaueTextLengthMax);
}
dataTable.Rows.Add(0, fileNameId, sheetNameId, int.Parse(rowName), columnNameId, cell.ValueNumber, valueText);
// Flush
count += 1;
if (count == 100000)
{
count = 0;
sqlBulkCopy.WriteToServer(dataTable);
dataTable.Rows.Clear();
}
}
}
}
}
//
sqlBulkCopy.WriteToServer(dataTable);
}
/// <summary>
/// Add name from ExcelRead.
/// </summary>
private void NameListAdd(ExcelRead excelRead)
{
foreach (string fileName in excelRead.CellList.Keys)
{
Name.Add(fileName);
foreach (string sheetName in excelRead.CellList[fileName].Keys)
{
Name.Add(sheetName);
foreach (string rowName in excelRead.CellList[fileName][sheetName].Keys)
{
foreach (string columnName in excelRead.CellList[fileName][sheetName][rowName].Keys)
{
Name.Add(columnName);
}
}
}
}
}
public static void Run()
{
ConnectionManager.OnLog("Begin");
ConnectionManager.OnLog("SqlCreate");
SqlDrop();
SqlCreate();
//
// Get Local FileNameList.
List <string> fileNameList = new List <string>();
foreach (string fileName in Util.FileNameList())
{
if (fileName.EndsWith(".xlsx"))
{
fileNameList.Add(fileName);
}
}
// Copy local Excel files into database.
Excel.SqlInsertBegin();
try
{
foreach (string fileName in fileNameList)
{
ExcelRead excelRead = new ExcelRead();
ConnectionManager.OnLog(string.Format("Load local. ({0})", System.IO.Path.GetFileName(fileName)));
excelRead.Load(fileName);
ConnectionManager.OnLog(string.Format("Copy local to database. ({0})", System.IO.Path.GetFileName(fileName)));
Excel excel = new Excel();
excel.Run(excelRead);
}
}
finally
{
Excel.SqlInsertEnd();
}
ConnectionManager.OnLog("End");
}
/// <summary>
/// Copy data from ExcelRead object to database.
/// </summary>
public void Run(ExcelRead excelRead)
{
NameListAdd(excelRead); // Add name from ExcelRead.
Name.Insert(); // Insert name into database.
CellListInsert(excelRead); // Insert cells into database.
}