Code and Procedure for Insert Excel file in Database CREATE proc [Insert_Purchase]
@filepath varchar(200),
@sheet varchar(50)
as
Declare @enteredby varchar(50)=''
Declare @SheetName varchar (50),@path varchar(500),@Query varchar(Max)
Select @SheetName = @sheet,@path=@filepath
Select @path = 'Excel 12.0;IMEX=1;Database='+@path
truncate table XLImport_purchase
Set @Query = 'Insert into XLImport_purchase([PO No])
Select [PO No] FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'','''+@path+''', '+@SheetName+')'
Exec (@query)
if exists (select 1 from Purchase where Purchase.isActive='1' and [PO No] in
(select distinct [PO No] from XLImport_Purchase))
begin
delete from Purchase where [PO No] in (select distinct [PO No] from XLImport_purchase)
Set @Query = 'Insert into Purchase(EnteredBy,Isactive,EnteredOn,[Item Code],[Item Name],[Loc Name],[PO No],[Doc No],
[Issue Date],[Tax Rate1],[Tax Rate2],[Tax Rate3],AddTaxRate,Quantity,[Unit Cost],[Basic Amt (BF Tax)],CST,VAT,[ADD VAT],
[Total Amt (AF Tax)])
Select '''+@enteredby+''',1,getdate(),[Item Code],[Item Name],[Loc Name],[PO No],[Doc No],
[Issue Date],[Tax Rate1],[Tax Rate2],[Tax Rate3],AddTaxRate,Quantity,[Unit Cost],[Basic Amt (BF Tax)],CST,VAT,[ADD VAT],
[Total Amt (AF Tax)] FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'','''+@path+''', '+@SheetName+')'
Exec (@query)
select 1
end
else if exists (select 1 from Purchase where Purchase.isActive='2' and [PO No] in (select distinct [PO No] from XLImport_purchase))
begin
select 0
end
begin
Set @Query = 'Insert into Purchase(EnteredBy,Isactive,EnteredOn,[Item Code],[Item Name],[Loc Name],[PO No],[Doc No],
[Issue Date],[Tax Rate1],[Tax Rate2],[Tax Rate3],AddTaxRate,Quantity,[Unit Cost],[Basic Amt (BF Tax)],CST,VAT,[ADD VAT],
[Total Amt (AF Tax)])
Select '''+@enteredby+''',1,getdate(),[Item Code],[Item Name],[Loc Name],[PO No],[Doc No],
[Issue Date],[Tax Rate1],[Tax Rate2],[Tax Rate3],AddTaxRate,Quantity,[Unit Cost],[Basic Amt (BF Tax)],CST,VAT,[ADD VAT],
[Total Amt (AF Tax)] FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'','''+@path+''', '+@SheetName+')'
select 2
Exec (@query)
end
truncate table XLImport_purchase
delete from purchase where [Doc No] is null
update Purchase set AddTaxRate = 0 where AddTaxRate is null
.html code
<asp:Panel ID="pnladd" runat="server" Visible="false">
<table width="100%">
<tr>
<td width="50%" style="padding-left:320px">
<asp:Label ID="lbltype" runat="server" Text="Select File Type"></asp:Label>
</td>
<td style="padding-left:40px">
<asp:DropDownList ID="drpfiletype" runat="server" Width="30%">
<asp:ListItem Value="0" Text="--Select--"></asp:ListItem>
<asp:ListItem Value="1" Text="Sales"></asp:ListItem>
<asp:ListItem Value="2" Text="Sales Return"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td width="50%" style="padding-left:320px">
<asp:Label ID="lbl_upload" Text="Upload File" runat="server"></asp:Label>
</td>
<td style="padding-left:40px">
<asp:FileUpload ID="excel_fileupload" runat="server" />
</td>
</tr>
</table>
<center>
<asp:Button runat="server" Text="Upload" ID="btn_upload" OnClick="btn_upload_Click" />
</center>
</asp:Panel>
.cs code
public void ReadExcelData()
{
try
{
if (excel_fileupload.HasFile)
{
DataSet dtb = new DataSet();
string filename = excel_fileupload.FileName.ToString();
string newfile = DateTime.Now.Ticks + filename;
string path = HttpContext.Current.Server.MapPath("files/"+newfile);
excel_fileupload.SaveAs(path);
if (drpfiletype.SelectedItem.Text == "Sales")
{
cs.Path = path;
cs.Sheet = "Sheet1$";
int count = cs.Insert();
if (count == 0)
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "alert", "alert('The Record You Are Uploading IS Already Freezed');", true);
}
else if (count == 1)
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "alert", "alert('The Existing Data Is Successfully Updated');", true);
}
else if (count == 2)
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "alert", "alert('Record Saved Successfully');", true);
}
else
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "alert", "alert('An Error has Occurred');", true);
}
}
if (drpfiletype.SelectedItem.Text == "Sales Return")
{
cs.Path = path;
cs.Sheet = "Sheet1$";
int count = cs.Insert_Sales_Return();
if (count ==0)
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "alert", "alert('The Record You Are Uploading Is Already Freezed');", true);
}
else if (count ==1)
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "alert", "alert('The Existing Data Is Successfully Updated');", true);
}
else if (count == 2)
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "alert", "alert('Record Saved Successfully');", true);
}
else
{
ScriptManager.RegisterStartupScript(this, typeof(Page), "alert", "alert('An Error has Occurred');", true);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public int Insert()
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "insertsales";
cmd.CommandTimeout = 3600;
cmd.Parameters.AddWithValue("@filepath", _path);
cmd.Parameters.AddWithValue("@sheet", _sheet);
try
{
return Database.updateTable(cmd);
}
catch (Exception Ex)
{
throw Ex;
}
}
