Code and Procedure for Insert Excel file in Database

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;

            }
        }