Hi, I am working on a POST() method to be run asynchronously. The below code runs fine in a sequential manner.
public class DataController : ApiController
{
[HttpPost]
[Route("data/import", Name = "DataImport")]
public async Task<IHttpActionResult> Post()
{
try
{
string content = await Request.Content.ReadAsStringAsync();
string[] lines = content.Split(Environment.NewLine.ToCharArray());
await ImportData(lines);
return Ok("Success");
}
catch (Exception ex)
{
return BadRequest(ex.Message);
}
}
private async Task ImportData(IEnumerable<string> lines)
{
string connectionString = ConfigurationManager.ConnectionStrings["SQLDBCONN"].ConnectionString;
const string query = "INSERT INTO [dbo].[testImport] ([ID], [Type], [Status], [Description]) VALUES (@ID, @Type, @Status, @Description)";
using (SqlConnection sqldbConnection = new SqlConnection(connectionString))
{
await sqldbConnection.OpenAsync();
using (SqlTransaction transaction = sqldbConnection.BeginTransaction())
using (SqlCommand cmd = new SqlCommand(query, sqldbConnection, transaction))
{
var pID = cmd.Parameters.Add("@ID", SqlDbType.Int);
var pType = cmd.Parameters.Add("@Type", SqlDbType.VarChar);
var pStatus = cmd.Parameters.Add("@Status", SqlDbType.VarChar);
var pDescription = cmd.Parameters.Add("@Description", SqlDbType.VarChar);
foreach (string line in lines)
{
string[] parts = line.Split(',');
if (parts.Length < 4) throw new InvalidOperationException($"Invalid line: '{line}'");
pID.Value = parts[0];
pType.Value = parts[1];
pStatus.Value = parts[2];
pDescription.Value = parts[3];
await cmd.ExecuteNonQueryAsync();
}
transaction.Commit();
}
}
}
}
Could you please guide me on how to re-write the POST method and SqlBulkCopy run asynchronously.
Thank you in advance.
SQLEnthusiast