protected void RepaceData(Dictionary <string, object> param)
{
var TableName = (string)param["TableName"];
var RowDataList = param["RowDataList"];
//メイン処理
_context.Database.OpenConnection();
var conn = _context.Database.GetDbConnection();
try
{
using (var command = conn.CreateCommand())
{
//テーブルバックアップ
command.CommandText = "CREATE TABLE " + TableName + "_" + DateTime.Now.ToString("yyMMddHHmm") + " AS SELECT * FROM " + TableName + ";";
command.ExecuteNonQuery();
command.CommandText = "delete from " + TableName;
command.ExecuteNonQuery();
//カラムリスト
//employeeテーブル情報取得
command.CommandText = "PRAGMA TABLE_INFO('" + TableName + "');";
var columnList = new Dictionary <string, VueMSFramework.ViewModels.Table.ColumnList>();
try
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
//name: カラム名, type: データ型
var cl = new VueMSFramework.ViewModels.Table.ColumnList
{
ColumnId = reader["cid"].ToString(),
ColumnName = reader["name"].ToString(),
DataType = reader["type"].ToString()
};
columnList.Add(cl.ColumnName, cl);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return;
}
foreach (var r in (List <object>)RowDataList)
{
string vals = "";
string keys = "";
foreach (KeyValuePair <string, object> kvp in (IDictionary <string, object>)r)
{
var col = kvp.Key;
var val = kvp.Value ?? "";
//型の判断
var c = columnList[col];
switch (c.DataType)
{
case "TEXT":
if (val.ToString().Length == 0)
{
//vals += ",null";
}
else
{
keys += "," + col;
vals += ",'" + val.ToString() + "'";
}
break;
case "INTEGER":
if (val.ToString().Length == 0)
{
// vals += ",null" ;
}
else
{
keys += "," + col;
vals += "," + int.Parse(val.ToString()).ToString();
}
break;
case "REAL":
if (val.ToString().Length == 0)
{
// vals += ",null";
}
else
{
keys += "," + col;
vals += "," + Double.Parse(val.ToString()).ToString();
}
break;
case "BOOL":
if (val.ToString().Length == 0)
{
// vals += ",null";
}
else
{
keys += "," + col;
vals += "," + Boolean.Parse(val.ToString()).ToString();
}
break;
default:
if (val.ToString().Length == 0)
{
// vals += ",null";
}
else
{
keys += "," + col;
vals += ",'" + val + "'";
}
break;
}
}
try
{
command.CommandText = "insert into " + TableName + "(" + keys.Substring(1) + ") values (" + vals.Substring(1) + ")";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
continue;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return;
}
/// <summary>
/// LoadExcel
/// </summary>
/// <param name="filefullName"></param>
/// <returns></returns>
protected Dictionary <string, object> LoadExcel(string filefullName)
{
//パラメータチェック
if (filefullName == null)
{
return(null);
}
//主処理
var currentPath = Directory.GetCurrentDirectory();
var tableName = "";
_context.Database.OpenConnection();
var conn = _context.Database.GetDbConnection();
//エクセルファイルを開く
IWorkbook book = null;
var dynamicObject = new ExpandoObject() as IDictionary <string, Object>;
try
{
//ブック読み込み
book = WorkbookFactory.Create(filefullName);
var sheet = book.GetSheetAt(0);
tableName = sheet.SheetName;
var columnList = new List <VueMSFramework.ViewModels.Table.ColumnList>();
//カラムリスト
using (var command = conn.CreateCommand())
{
//テーブル情報取得
command.CommandText = "PRAGMA TABLE_INFO('" + tableName + "');";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
//name: カラム名, type: データ型
var cl = new VueMSFramework.ViewModels.Table.ColumnList
{
ColumnId = reader["cid"].ToString(),
ColumnName = reader["name"].ToString(),
DataType = reader["type"].ToString()
};
columnList.Add(cl);
}
}
}
var i = 0;
var rowIndex = 0;
var headeInfo = new Dictionary <string, int>();
for (; ;)
{
try
{
//1行目はヘッダの情報
if (rowIndex == 0)
{
var colIndex = 0;
for (; ;)
{
if (ReadCell(sheet, colIndex, rowIndex).Equals(string.Empty))
{
break;
}
else
{ //カラム列と名のマッピングリスト
headeInfo.Add(ReadCell(sheet, colIndex, rowIndex), colIndex);
}
colIndex++;
}
}
else
{
//1列目が未設定の場合は、データの終了と判断する
if (ReadCell(sheet, 0, rowIndex).Equals(string.Empty))
{
break;
}
else
{
//2行目以降はデータ
var dynamicColumnObject = new ExpandoObject() as IDictionary <string, Object>;
foreach (var col in columnList)
{
if (headeInfo.ContainsKey(col.ColumnName))
{
var ret = ReadCell(sheet, headeInfo[col.ColumnName], rowIndex);
dynamicColumnObject.Add(col.ColumnName, ret);
}
else
{
dynamicColumnObject.Add(col.ColumnName, "");
}
}
dynamicObject.Add((i++).ToString(), dynamicColumnObject);
}
}
}
catch (Exception ex)
{
Console.WriteLine("エラー:" + ex.Message);
return(null);
}
rowIndex++;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
if (book != null)
{
book.Close();
book = null;
}
}
var obj = new Dictionary <string, object>
{
{ "TableName", tableName },
{ "RowDataList", dynamicObject.Values.ToList() }
};
return(obj);
}
