//[DisableRequestSizeLimit] public async Task <IActionResult> FileInsert(IFormFile file, string uploadType) { #region testw if (file == null || file.Length == 0) { return(RedirectToAction("Index")); } //var list = await _importer.Upload<ExcelImport>(file); var table = await _importer.Upload(file); StringBuilder sb = new StringBuilder(); var name = file.Name; CRUDOption option = new CRUDOption() { TableName = "ExcelImport", BatchSize = 10000, BulkCopyTimeout = 600, NotifyAfter = 10000, ColumnMapping = TableMapDict.Mapping }; //_insertObject.RowsCopied += OnSqlRowsCopied; Stopwatch watch = new Stopwatch(); watch.Start(); _crudObject.Insert <ExcelImport>(table, option); watch.Stop(); string executedTime = watch.ElapsedMilliseconds.ToString(); return(Content("执行共:" + executedTime + "毫秒")); #endregion }
private void SetData(ExcelWorksheet worksheet) { CRUDOption option = new CRUDOption() { TableName = "ExcelImport", Where = " Id<1040000 " }; var list = _crudObject.Select <ExcelImport>(option); worksheet.Cells["A2"].LoadFromCollection(list, PrintHeaders: false); }
public string CRUDMethod(string option, Recipe aRecipe) { CRUDOption crud = CRUDOption.Create; string query; string message = "fail"; int recipeId = 0; int ingredientId = 0; switch (option) { case "create": crud = CRUDOption.Create; break; case "update": crud = CRUDOption.Update; break; case "delete": crud = CRUDOption.Delete; break; } switch (crud) { case CRUDOption.Create: query = "INSERT INTO Recipe VALUES (@RecipeName, @RecipePrep, @RecipeInst)"; using (connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(query, connection)) { connection.Open(); command.Parameters.AddWithValue("@RecipeName", aRecipe.RecipeName); command.Parameters.AddWithValue("@RecipePrep", aRecipe.PrepTime); command.Parameters.AddWithValue("@RecipeInst", aRecipe.Instructions); command.ExecuteScalar(); } query = "SELECT Id from Recipe WHERE Name = @RecipeName"; using (connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(query, connection)) using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { connection.Open(); command.Parameters.AddWithValue("@RecipeName", aRecipe.RecipeName); DataTable recipeInfo = new DataTable(); adapter.Fill(recipeInfo); foreach (DataRow row in recipeInfo.Rows) { recipeId = int.Parse(row["Id"].ToString()); } } foreach (string ingred in aRecipe.Ingredients) { query = "INSERT INTO Ingredient VALUES (@IngredientName) "; using (connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(query, connection)) { connection.Open(); command.Parameters.AddWithValue("@IngredientName", ingred); command.ExecuteScalar(); } query = "SELECT Id from Ingredient WHERE Name = @IngredientName"; using (connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(query, connection)) using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { connection.Open(); command.Parameters.AddWithValue("@IngredientName", ingred); DataTable recipeInfo = new DataTable(); adapter.Fill(recipeInfo); foreach (DataRow row in recipeInfo.Rows) { ingredientId = int.Parse(row["Id"].ToString()); } } query = "INSERT INTO RecipeIngredient VALUES (@RecipeId, @IngredientId)"; using (connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(query, connection)) { connection.Open(); command.Parameters.AddWithValue("@RecipeId", recipeId); command.Parameters.AddWithValue("@IngredientId", ingredientId); command.ExecuteScalar(); } } message = "recipe added!"; break; case CRUDOption.Update: query = "SELECT Id from Recipe WHERE Name = @RecipeName"; using (connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(query, connection)) using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { connection.Open(); command.Parameters.AddWithValue("@RecipeName", aRecipe.RecipeName); DataTable recipeInfo = new DataTable(); adapter.Fill(recipeInfo); foreach (DataRow row in recipeInfo.Rows) { recipeId = int.Parse(row["Id"].ToString()); } } query = "UPDATE Recipe SET PrepTime = @PrepTime, Instructions = @Instructions " + "WHERE Id = @Id"; using (connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(query, connection)) { connection.Open(); command.Parameters.AddWithValue("@Id", recipeId); command.Parameters.AddWithValue("@PrepTime", aRecipe.PrepTime); command.Parameters.AddWithValue("@Instructions", aRecipe.Instructions); command.ExecuteScalar(); } message = "recipe updated"; break; case CRUDOption.Delete: query = "SELECT Id from Recipe WHERE Name = @RecipeName"; using (connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(query, connection)) using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { connection.Open(); command.Parameters.AddWithValue("@RecipeName", aRecipe.RecipeName); DataTable recipeInfo = new DataTable(); adapter.Fill(recipeInfo); foreach (DataRow row in recipeInfo.Rows) { recipeId = int.Parse(row["Id"].ToString()); } } query = "DELETE FROM RecipeIngredient WHERE RecipeId = @RecipeId"; using (connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(query, connection)) { connection.Open(); command.Parameters.AddWithValue("@RecipeId", recipeId); command.ExecuteScalar(); } query = "DELETE FROM Recipe WHERE Id = @RecipeId"; using (connection = new SqlConnection(connectionString)) using (SqlCommand command = new SqlCommand(query, connection)) { connection.Open(); command.Parameters.AddWithValue("@RecipeId", recipeId); command.ExecuteScalar(); } message = "recipe deleted!"; break; } return(message); }