Hi rani,
You need to convert the Generic List to DataTable. The use SqlBulkCopy to insert the record.
Check this example. Now please take its reference and correct your code.
Database
I have made use of the following table Customers with the schema as follows.
You can download the database table SQL by clicking the download link below.
Download SQL file
Model
public class CustomerModel
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string Country { get; set; }
}
Namespaces
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
Controller
public class HomeController : Controller
{
public IActionResult Index()
{
return View();
}
[HttpPost]
public IActionResult Save()
{
List<CustomerModel> customers = new List<CustomerModel>();
customers.Add(new CustomerModel { CustomerId = 1, Name = "John Hammond", Country = "United States" });
customers.Add(new CustomerModel { CustomerId = 2, Name = "Mudassar Khan", Country = "India" });
customers.Add(new CustomerModel { CustomerId = 3, Name = "Suzanne Mathews", Country = "France" });
customers.Add(new CustomerModel { CustomerId = 4, Name = "Robert Schidner", Country = "Russia" });
DataTable dt = ToDataTable(customers);
string conString = @"Data Source=.\\SQL2017;Initial Catalog=AjaxSamples;Integrated Security=true";
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.Customers";
sqlBulkCopy.ColumnMappings.Add("CustomerId", "CustomerId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
return View();
}
public DataTable ToDataTable<T>(List<T> items)
{
DataTable dataTable = new DataTable(typeof(T).Name);
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in Props)
{
dataTable.Columns.Add(prop.Name);
}
foreach (T item in items)
{
var values = new object[Props.Length];
for (int i = 0; i < Props.Length; i++)
{
values[i] = Props[i].GetValue(item, null);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
}
View
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<form asp-action="Save" asp-controller="Home" method="post">
<input type="submit" value="Save" />
</form>
</body>
</html>
Table containing the data from the Generic List
