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