Hi,
Suppose you have data in your excel sheet which contains 1500 rows and you want to save this data into database.
In your design page put a file upload control and a button.
Then in your code behind page call this method in your button click event:
public void Insert(HttpPostedFileBase AmazonUpload)
{
if (AmazonUpload.FileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) || AmazonUpload.FileName.EndsWith(".xslx", StringComparison.OrdinalIgnoreCase))
{
var fileName = Path.GetFileName(AmazonUpload.FileName);
var path = Path.Combine(Server.MapPath("~/App_Data/"), fileName);
AmazonUpload.SaveAs(path);
var excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), fileName) + ";Extended Properties=Excel 12.0;");
OleDbConnection objOlecon = new OleDbConnection();
objOlecon.ConnectionString = excelConnectionString;
objOlecon.Open();
OleDbDataAdapter objOleDa = new OleDbDataAdapter("Select * from [Amazon-orders$]", objOlecon);
DataTable objdt = new DataTable();
objOleDa.Fill(objdt);
SqlConnection objsqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString);
objsqlCon.Open();
SqlCommand cmd = new SqlCommand("ImportOrders", objsqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@AmazonOrdersTemp", objdt);
cmd.ExecuteNonQuery();
objsqlCon.Close();
ViewData["message"] = "Records Imported Successfully";
}
}
ImportOrders Query is retrieving data from Datable and storing it into another table:
CREATE PROCEDURE ImportOrders
(
@AmazonOrdersTemp AmazonOrderType READONLY
)
AS
BEGIN
INSERT INTO AmazonOrders
SELECT *
FROM @AmazonOrdersTemp
END
Please Mark as Answer if satisfied.............
Thank You,
Kavya Shree M.
Kavya Shree Mandapalli
Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator