Hi Ruben12345,
Check this example. Now please take its reference and implemented in your code.
SQL
CREATE PROCEDURE [dbo].[Customers_InsertUpdate]
@CustomerId INT = NULL
,@Name VARCHAR(100) = NULL
,@Country VARCHAR(100) = NULL
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT CustomerId FROM Customers WHERE CustomerId = @CustomerId)
BEGIN
UPDATE Customers
SET Name = @Name, Country = @Country
WHERE CustomerId = @CustomerId
END
ELSE
BEGIN
INSERT INTO Customers(Name, Country) VALUES (@Name, @Country)
END
END
Namespaces
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
Controller
public class HomeController : Controller
{
// GET: /Home/
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult Import()
{
string filePath = Server.MapPath("~/Files/Customers.xlsx");
string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
DataTable dt = new DataTable();
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
connExcel.Close();
}
}
}
foreach (DataRow row in dt.Rows)
{
int id = Convert.ToInt32(row["CustomerId"]);
string name = Convert.ToString(row["Name"]);
string country = Convert.ToString(row["Country"]);
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("Customers_InsertUpdate"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CustomerId", id);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Country", country);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
return View("index");
}
}
View
<div>
<% using (Html.BeginForm("Import", "Home", FormMethod.Post))
{ %>
<input type="submit" value="Save" id="btnSave" />
<% } %>
</div>
If you don't want to use loop the you can use SqlBulkCopy class to do so.
For this refer below article.