save data from excel to database [Resolved]

Hi,

Step 1:
Intially, in the Controller, write an ActionResult method as following:

public ActionResult ExcelData()
{
return View();
}

Step 2:
Create a view for the action method.In the view write the following code:
@using (Html.BeginForm("AmazonOrders", "Common", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<table width="50%">
<tr>
<td >
<div style="font-weight:bold; color:Red; background-color:Lime">
@ViewData["message"]
</div>
</td>
</tr>
<tr>
<td>
<input type="file" name="AmazonUpload" id="amazon" />
</td>
</tr>
<tr>
<td>
<input type="submit" value="Import" name="save"/> </td>
</tr>
</table>
}

Step 3:
Write the HttpPost method for the ExcelData method as follows:
[HttpPost]
public ActionResult ExcelData(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); //HereAmazon-orders is the name of the excel sheet.
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";
objOlecon.Close();
}
return View();
}

Mark as answer if satisfied............


Thanks,
Shree M.

Kavya Shree Mandapalli

Mandapallishree, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Im sorry, i forgot to send the procedure.

Here is the proc for saving the data from the datatable into database:

CREATE PROCEDURE ImportOrders
(
@AmazonOrdersTemp AmazonOrderType READONLY
)
AS
BEGIN

INSERT INTO AmazonOrders
SELECT *
FROM @AmazonOrdersTemp

END

Mark as answer if satisfied.........

Regards,
Shree M.

Kavya Shree Mandapalli

Mandapallishree, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

you have written a stored procedure "ImportOrders", Can you send me the stored procedure ?


Thanks,
Bhavya.

Mandapallishree, if this helps please login to Mark As Answer. | Alert Moderator