Import Data in Database From Excel Using asp.net c#



.aspx file

  <div>
        <table>
            <tr>
                <td>
                    <span style="color: Red">*</span>Attach Excel file
                </td>
                <td>
                    <asp:FileUpload ID="fuexcelupload" runat="server" />
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Button ID="btnSend" runat="server" Text="Export" OnClick="btnSend_Click" />
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                 
                </td>
            </tr>
        </table>
        <asp:GridView ID="GridView1" runat="server">

        </asp:GridView>
    </div>







.cs File
 

 protected void btnSend_Click(object sender, EventArgs e)
        {
          
            string filename = System.IO.Path.GetFileName(fuexcelupload.FileName);
            if (filename == "" || filename == null)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Please Select Excel Sheet First');", true);
                return;
            }

            string[] file = filename.Split('.');
            string exten = file[1].ToString();

            if (exten == "xls" || exten == "xlt" || exten == "xlm" || exten == "xlsx" || exten == "xlsm" || exten == "xltx" || exten == "xltm" || exten == "xlsb" || exten == "xla" || exten == "xlam" || exten == "xll" || exten == "xlw")
            {
                try
                {
                    SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
                    con.Open();

                    string connString = "";
                    string strFileType = Path.GetExtension(fuexcelupload.FileName).ToLower();
                    string path = fuexcelupload.PostedFile.FileName;
                    fuexcelupload.SaveAs(Server.MapPath("~/test/" + path + ""));
                    string path1 = "~/test/'" + path + "'";
                    //Connection String to Excel Workbook
                    if (strFileType.Trim() == ".xls")
                    {

                        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/test/" + path + "") + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        //connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/test/" + path + "") + ";Extended Properties=Excel 12.0";
                    }
                    else if (strFileType.Trim() == ".xlsx")
                    {
                        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/test/" + path + "") + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        //connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/test/" + path + "") + ";Extended Properties=Excel 12.0";
                    }
                    string query = "SELECT * FROM [Sheet1$]";
                    OleDbConnection conn = new OleDbConnection(connString);
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    OleDbCommand cmd = new OleDbCommand(query, conn);
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        if (ds.Tables[0].Rows[i]["DoctorID"].ToString() != "")
                        {
                            string UpdateQury = "";
                            UpdateQury = "Update DoctorMaster set FirstName = '" + ds.Tables[0].Rows[i]["FirstName"].ToString().Trim() +
                                "', MiddleName = '" + ds.Tables[0].Rows[i]["MiddleName"].ToString() +
                                "', LastName = '" + ds.Tables[0].Rows[i]["LastName"].ToString() +
                                "', MobileNo = '" + ds.Tables[0].Rows[i]["MobileNo"].ToString() +
                                "', YearsOfExperience = '" + ds.Tables[0].Rows[i]["YearsOfExperience"].ToString() +
                                "', Languages = '" + ds.Tables[0].Rows[i]["Languages"].ToString() +
                                "', Gendor = '" + ds.Tables[0].Rows[i]["Gendor"].ToString() +
                                "', ProfessionalStatement = '" + ds.Tables[0].Rows[i]["ProfessionalStatement"].ToString() +
                                "', UserName = '" + ds.Tables[0].Rows[i]["UserName"].ToString() +
                                "', Password = '" + ds.Tables[0].Rows[i]["Password"].ToString() +
                                "', Status = '" + ds.Tables[0].Rows[i]["Status"].ToString() +
                                "' Where DoctorID = '" + ds.Tables[0].Rows[i]["DoctorID"].ToString() + "'";
                            SqlCommand cmd1 = new SqlCommand(UpdateQury, con);
                            cmd1.ExecuteScalar();
                            cmd1.Dispose();
                        }
                        else
                        {

                            /*  string Sql = "select * from DoctorMaster where FirstName = '" + ds.Tables[0].Rows[i]["AreaName"].ToString().Trim() +
                                  "' and CityId = " + ddlcity.SelectedValue;
                              SqlDataAdapter Sda = new SqlDataAdapter(Sql, con);
                              DataTable Sdt = new DataTable();
                              Sda.Fill(Sdt);*/
                            /*   if (Sdt.Rows.Count > 0)
                               {

                               }
                               else
                               {
                             * */
                            string qry1 = "";

                            qry1 = "Insert into DoctorMaster values('" + ds.Tables[0].Rows[i]["FirstName"].ToString().Trim() + "'," +

                            "'" + ds.Tables[0].Rows[i]["MiddleName"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["LastName"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["MobileNo"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["PhoneNo"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["Gendor"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["YearsOfExperience"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["Languages"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["ProfessionalStatement"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["PhotoImg"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["Status"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["UserName"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["Password"].ToString() +

                            "','" + ds.Tables[0].Rows[i]["CreatedDate"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["MembershipStartDate"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["MembershipExpireDate"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["MemberID"].ToString() +

                            "','" + ds.Tables[0].Rows[i]["GraduateRegNo"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["PostGraduateRegNo"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["IndiactiveFees"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["IsDeleted"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["Noofviews"].ToString() +
                            "','" + ds.Tables[0].Rows[i]["AvaibilityofHousecall"].ToString() +"')";

                            SqlCommand cmd1 = new SqlCommand(qry1, con);
                            cmd1.ExecuteNonQuery();
                            cmd1.Dispose();

                        }
                    }
                    da.Dispose();
                    conn.Close();
                    conn.Dispose();

                    ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", "alert('Your Data has been successfully inserted...!!!');", true);
                }
                catch (Exception ex)
                {

                    Response.Write(ex.Message.ToString());
                }
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Select Only Excel Sheet');", true);
                return;
            }

        }

 








Comments

Post a Comment

Popular posts from this blog

Stack vs Heap So far we have seen how to declare basic type variables such as int , double , etc, and complex types such as arrays and structs. The way we have been declaring them so far, with a syntax that is like other languages such as MATLAB, Python, etc, puts these variables on the stack in C. The Stack What is the stack? It's a special region of your computer's memory that stores temporary variables created by each function (including the main() function). The stack is a "FILO" (first in, last out) data structure, that is managed and optimized by the CPU quite closely. Every time a function declares a new variable, it is "pushed" onto the stack. Then every time a function exits, all of the variables pushed onto the stack by that function, are freed (that is to say, they are deleted). Once a stack variable is freed, that region of memory becomes available for other stack variables. The advantage of using the stack to store variable...
Read more

Validate Mobile Number with 10 Digits in ASP.Net

Here is the code for validating textbox for 10 digit mobile number. .ASPX File: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br\>     <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"        ControlToValidate="TextBox1" ErrorMessage="RegularExpressionValidator"      ValidationExpression="[0-9]{10}"></asp:RegularExpressionValidator>
Read more
What is  Abstraction? Abstraction is another good feature of OOPS. Abstraction means to show only the necessary details to the client of the object . Do you know the inner details of the Monitor of your PC? What happen when you switch ON Monitor? Does this matter to you what is happening inside the Monitor? No Right, Important thing for you is weather Monitor is ON or NOT. When you change the gear of your vehicle are you really concern about the inner details of your vehicle engine? No but what matter to you is that Gear must get changed that’s it!! This is abstraction; show only the details which matter to the user.  Let’s say you have a method "CalculateSalary" in your Employee class, which takes EmployeeId as parameter and returns the salary of the employee for the current month as an integer value. Now if someone wants to use that method. He does not need to care about how Employee object calculates the salary? An only thing he needs to be concern is name...
Read more