Read And Upload Excel Data To Oracle Table

Introduction

In this article, I have explained the uploading of an Excel file into a Database table. this will be read line by line and uploaded using the dataset.

ASPX PAGE

<asp:FileUpload ID="FileUpload1" runat="server" Width="303px" BackColor="white" CssClass="form-control" />
<asp:Button ID="btnUploadFile" runat="server" Text="Upload" OnClick="btnUploadFile_Click" CssClass="btn btn-success" />

Example. This code we will be writing on the button upload click event.

if (FileUpload1.HasFile)
{
    string fileExtension = Path.GetExtension(FileUpload1.FileName); // Checking file extension
    if (fileExtension.ToLower() != ".xls")
    {
        lblMsg.Text = "Only .xlsx files are allowed";
        lblMsg.ForeColor = System.Drawing.Color.Red;
    }
    else
    {
        int fileSize = FileUpload1.PostedFile.ContentLength;
        if (fileSize > 2097152) // Checking file length (customizable size limit)
        {
            lblMsg.Text = "Maximum size 2(MB) exceeded";
            lblMsg.ForeColor = System.Drawing.Color.Red;
        }
        else
        {
            FileUpload1.SaveAs(Server.MapPath("~/Upload_File/" + FileUpload1.FileName));
            System.Data.OleDb.OleDbConnection MyConnection; // OleDb data connection
            System.Data.DataSet DtSet;
            System.Data.OleDb.OleDbDataAdapter MyCommand;
            MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=' Path of file \\" + FileUpload1.FileName + "';Extended Properties=Excel 8.0;");
            MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); // File sheet name should be [Sheet1]
            MyCommand.TableMappings.Add("Table", "TestTable"); // Oracle table name
            DtSet = new System.Data.DataSet();
            MyCommand.Fill(DtSet); // Filling the dataset
            for (int i = 0; i < DtSet.Tables[0].Rows.Count; i++)
            {
                try
                {
                    strQuery = "INSERT INTO TestTable(col1,col2,col3,col4,col5,col6,col7) VALUES('" + DtSet.Tables[0].Rows[i][0].ToString() + "','" + DtSet.Tables[0].Rows[i][1].ToString() + "','" + DtSet.Tables[0].Rows[i][2].ToString() + "','" + DtSet.Tables[0].Rows[i][3].ToString() + "','" + DtSet.Tables[0].Rows[i][4].ToString() + "','" + txtRptDate.Text + "','" + Session["UserID"].ToString() + "')";
                    dbContext.ExecuteNonQuery(strQuery);
                }
                catch (Exception)
                {
                    this.ClientScript.RegisterStartupScript(this.GetType(), "SweetAlert", "swal('Error!', 'Failed', 'error');", true);
                }
            }
            string msg = "Uploaded Successfully...";
            this.ClientScript.RegisterStartupScript(this.GetType(), "SweetAlert", "swal('Success!', 'Uploaded Successfully', 'success');", true);
            MyConnection.Close();
        }
    }
}
else
{
    // Handle the case when no file is uploaded
}

Thanks for having me. please feel free to write back.