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