将数据从excelsheet文件导入mysql数据库C#

tan

我有以下几行代码:

     protected void btnUpload_Click(object sender, EventArgs e)
{

        MySqlTransaction transaction;

        string ex_id = "";



        string file_name = Path.GetFileName(FileUpload1.FileName);
        string Excel_path = Server.MapPath("~/Excel/" + file_name);
    DataTable dtExceldata = new DataTable();//just added
    FileUpload1.SaveAs(Excel_path);

        OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False");
    my_con.Open();
    OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", my_con);
        da.Fill(dtExceldata);
    if (dtExceldata.Rows.Count > 0)             
    {
        //foreach (DataRow row in dtExceldata.Rows)
            for (int i = 0; i <= dtExceldata.Rows.Count - 1; i++)

        {

                string ex_dir = dtExceldata.Rows[i]["website_a"].ToString();
                //string ex_dir = row["website_a"].ToString();


                string ex_email = dtExceldata.Rows[i]["email_id"].ToString();
                // string ex_email = row["email_id"].ToString();


                string ex_email1 = dtExceldata.Rows[i]["email_id2"].ToString();
                //string ex_email1 = row["email_id2"].ToString();


                string ex_email2 = dtExceldata.Rows[i]["email_id3"].ToString();
                //string ex_email2 = row["email_id3"].ToString();


                string ex_company = dtExceldata.Rows[i]["company"].ToString();
                //string ex_company = row["company"].ToString();

                string ex_contact = dtExceldata.Rows[i]["contact_name"].ToString();
                //string ex_contact = row["contact_name"].ToString();


                string ex_proposal = dtExceldata.Rows[i]["proposal_status"].ToString();
                // string ex_proposal = row["proposal_status"].ToString();


                string ex_reason = dtExceldata.Rows[i]["reason"].ToString();
                //string ex_reason = row["reason"].ToString();


        int chk = 0;
            int type = 0;
            int dup = 0;
            int dir = 0;

            if (ddlwebsites.SelectedIndex != 0)
            {
                dir = Convert.ToInt32(ddlwebsites.SelectedValue);
                if (dir == 8)
                {
                    type = 1;
                }

            }


            foreach (ListItem lstAssign in ddlevents.Items)
            {
                if (lstAssign.Selected == true)
                {
                    chk = 1;
                }
            }

            if (type == 1 && chk == 0)
            {
                evyerror.Text = "Please Select the Event!!";
                return;

            }

            else
            {
                string querycomp = "", compID = "";
                querycomp = "Select * from barter_company where website like '%' '" + ex_dir + "' '%'";
                string connStr = ConfigurationManager.ConnectionStrings["BarterConnectionString"].ToString();
                connect = new MySqlConnection(connStr);
                connect.Open();
                transaction = connect.BeginTransaction();
                try
                {
                    ClassDtBaseConnect clsDtResult = new ClassDtBaseConnect();
                    DataTable dt = clsDtResult.GetDataTable(querycomp);
                    if (dt.Rows.Count > 0)
                    {
                        compID = dt.Rows[0]["comp_id"].ToString();
                        ViewState["comp_id"] = compID;
                        if (type == 1)
                        {
                            dup = checkforDuplicates(Convert.ToInt32(compID));
                            if (dup == 1)
                            {
                                //Confirm_MP.Show();
                                // ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "confirmation();", true);
                            }

                        }
                        //return;
                    }
                    else
                    {

                        string queryStr = "insert into barter_company (comp_name,website) values(?comp,?website)";
                        MySqlCommand cmd = new MySqlCommand(queryStr, connect, transaction);
                        cmd.Parameters.AddWithValue("?comp", ex_company);
                        cmd.Parameters.AddWithValue("?website", ex_dir);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "Select LAST_INSERT_ID()";
                        compID = cmd.ExecuteScalar().ToString();
                        transaction.Commit();
                        connect.Close();

                    }
                }
                catch
                {
                    transaction.Rollback();
                }





                if (dup == 0)
                {
                    // create a connection string with your sql database 
                    string connStr1 = ConfigurationManager.ConnectionStrings["BarterConnectionString"].ToString();
                    connect = new MySqlConnection(connStr1);
                    connect.Open();
                    DateTime date = new DateTime();
                    date = DateTime.ParseExact(txtsentdate.Text, "MM/dd/yyyy", null);
                    string SentDateString = date.ToString("yyyy/MM/dd");
                    //DateTime date = new DateTime();
                    //if (!string.IsNullOrEmpty(ex_date))
                    // {
                    //DateTime date = new DateTime();//added by chetan
                    //ex_date = ex_date.Split(' ')[0];//added by chetan
                    //date = DateTime.ParseExact(ex_date, "MM/dd/yyyy", null);//added by chetan
                    //string SentDateString = date.ToString("yyyy/MM/dd");//added by chetan
                    //DateTime SentDate = Convert.ToDateTime(SentDateString).Date;//added by chetan
                    //// DateTime date = DateTime.Parse(ex_date);//added by chetan


                    // }
                    //date = DateTime.ParseExact(ex_date, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture);//added by chetan
                    //date = DateTime.ParseExact(ex_date, "MM/dd/yyyy", null);
                    // string SentDateString = date.ToString("yyyy/MM/dd");
                    transaction = connect.BeginTransaction();
                    try
                    {

                        string ex_uid = Session["session_barterUser_id"].ToString();


                        MySqlCommand cmd = new MySqlCommand("insert into barter_proposals(user_id, sent_date, website_a, email_id, email_id2, email_id3, company, contact_name, proposal_status, reason,type) values(?uid,?sentdate,?dir,?email,?email2,?email3,?comp,?cont_name,?pro_status,?reason,?type)", connect);

                        cmd.Parameters.AddWithValue("?uid", ex_uid);
                        cmd.Parameters.AddWithValue("?comp", compID);

                        cmd.Parameters.AddWithValue("?sentdate", SentDateString);
                        // command.Parameters.AddWithValue("?event", eventname);
                        cmd.Parameters.AddWithValue("?dir", dir);
                        // command.Parameters.AddWithValue("?bar_type", ddlbartertype.SelectedValue);
                        // command.Parameters.AddWithValue("?website_b", txtwebsite.Text);
                        //cmd.Parameters.AddWithValue("?comp", ex_company);
                        cmd.Parameters.AddWithValue("?cont_name", ex_contact);
                        cmd.Parameters.AddWithValue("?email", ex_email);
                        cmd.Parameters.AddWithValue("?email2", ex_email1);
                        cmd.Parameters.AddWithValue("?email3", ex_email2);
                        cmd.Parameters.AddWithValue("?pro_status", ex_proposal);
                        cmd.Parameters.AddWithValue("?reason", ex_reason);
                        cmd.Parameters.AddWithValue("?type", type);
                        // command.Parameters.AddWithValue("?type", type);

                        cmd.ExecuteNonQuery();
                        if (type == 1)
                        {

                            cmd.CommandText = "Select LAST_INSERT_ID()";
                            Int64 CurrentProId = Convert.ToInt64(cmd.ExecuteScalar());
                            int eventAssignID;
                            string QueryInqEventAssign = "insert into barter_propeventassign(prop_id,event_id) values(?pro_id,?event_id)";

                            foreach (ListItem lstAssign in ddlevents.Items)
                            {
                                if (lstAssign.Selected == true)
                                {
                                    cmd = new MySqlCommand(QueryInqEventAssign, connect, transaction);
                                    cmd.Parameters.AddWithValue("?pro_id", CurrentProId);
                                    eventAssignID = Convert.ToInt32(lstAssign.Value);
                                    cmd.Parameters.AddWithValue("?event_id", eventAssignID);
                                    cmd.ExecuteNonQuery();

                                }
                            }

                        }//end of if
                        transaction.Commit();
                        connect.Close();
                        Response.Write("<script type=\"text/javascript\">alert('Proposal Added Successfully!!!');</script>");
                    }//end of try
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Response.Write("<script>alert('There is an Error Ocurred:" + Server.HtmlEncode(ex.Message) + "')</script>");
                    }
                    finally
                    {
                        connect.Close();

                    }

                }//ifdupzero
            }//else
        }//for//foreach
      }//while //if  


   // dr.Close();//commented by chetan

    my_con.Close();
    if (System.IO.File.Exists(Excel_path))
    {
        System.IO.File.Delete(Excel_path);
    }





}

假设excelsheet文件中有2个条目。尝试导入时,它会将那2行条目插入数据库表中。但是问题是,读取器没有停止执行。读取2行条目后,它正在读取第三行是空白。我使用了dr.close,它继续读取空白的行条目。

莫里斯

您可以检查行的第一个单元格是否为空(dr [0] .ToString()!= String.Empty),而不是检查是否还有行(dr.Read())作为循环条件。

string file_name = Path.GetFileName(FileUpload1.FileName);
            string Excel_path = Server.MapPath("~/Excel/" + file_name);
            FileUpload1.SaveAs(Excel_path);

            OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False");

            my_con.Open();         
            OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", my_con);          
            OleDbDataReader dr = command.ExecuteReader();
            dr.Read();            
            while (dr[0].ToString() != String.Empty)
            {
            ex_id = dr[0].ToString();
            string ex_uid = dr[1].ToString();
            //get second row data and assign it ex_name variable
            string ex_date = dr[2].ToString();
            //get thirdt row data and assign it ex_name variable

            string ex_dir = dr[3].ToString();

            //get first row data and assign it ex_location variable
            string ex_email = dr[4].ToString();
            string ex_email1 = dr[5].ToString();
            string ex_email2 = dr[6].ToString();
            //string ex_company = dr[7].ToString();
            string ex_company = dr[7].ToString();
            string ex_contact = dr[8].ToString();
            string ex_proposal = dr[9].ToString();
            string ex_reason = dr[10].ToString();
               ...............
               //Insert operation
               ...............
            dr.Read();
            }
            dr.close();
            my_con.close();

当然前提是,如果该行的其余部分不为空白,则始终填充第一列。如果不是这种情况,您还可以检查循环条件中的其他列。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章