取消的Txt文件導入SQL

来源：互联网发布：php视频教程百度云编辑：程序博客网时间：2024/05/02 13:37

        protected void Menu_Import_Click(object sender, EventArgs e)
        {
            try
            {
                Message.Text = "";
                if (FileUpload1.PostedFile.InputStream.Length < 1)
                {
                    Message.Text = "請選擇文件!";
                    return;
                }
                string FileName = FileUpload1.FileName;
                if (FileName.ToLower().IndexOf(".txt") == -1)
                {
                    Message.Text = "請選擇文本文件!";
                    return;
                }
                int FileLen = FileUpload1.PostedFile.ContentLength;
                byte[] input = new byte[FileLen];
                System.IO.Stream UpLoadStream = FileUpload1.PostedFile.InputStream;
                UpLoadStream.Read(input, 0, FileLen);
                UpLoadStream.Position = 0;
                System.IO.StreamReader sr = new System.IO.StreamReader(UpLoadStream, System.Text.Encoding.Default);
                String content = sr.ReadToEnd();
                sr.Close();
                UpLoadStream.Close();
                UpLoadStream = null;
                sr = null;

                String sql = "Insert INTO Meal_Details(Meal_ID,Reader_No,Reader_Date,Employee_No,Name,CDG_Name,Meal_Type,Meal_Cost) Values(@Meal_ID,@Reader_No,@Reader_Date,@Employee_No,@Name,@CDG_Name,@Meal_Type,@Meal_Cost)";


                String[] d = System.Text.RegularExpressions.Regex.Split(content, "\r\n");
                DateTime t1 = DateTime.Now;
                using (SqlConnection cn = new SqlConnection())
                        {
                            cn.ConnectionString = ConfigurationManager.ConnectionStrings["ProvisionConnectionString"].ConnectionString;
                            cn.Open();
                            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, cn);

                            Parallel.For(0, d.Length - 1, (i) =>
                            {
                                if (d[i].Trim() != "")
                                {
                                    cmd.Parameters.Clear();
                                    cmd.Parameters.AddWithValue("@Meal_ID", Request.QueryString["Meal_ID"]);
                                    cmd.Parameters.AddWithValue("@Reader_No", d[i].Substring(0, 2));
                                    cmd.Parameters.AddWithValue("@Reader_Date", DateTime.ParseExact(d[i].Substring(2, 12) + "00", "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture));
                                    cmd.Parameters.AddWithValue("@Employee_No", d[i].Substring(15, 5));
                                    int t = Convert.ToInt16(d[i].Substring(10, 2));
                                    if (t > 6 && t <= 10)
                                    {
                                        cmd.Parameters.AddWithValue("@Meal_Type", "早餐");
                                    }
                                    else if (t > 10 && t <= 16)
                                    {
                                        cmd.Parameters.AddWithValue("@Meal_Type", "午餐");
                                    }
                                    else
                                    {
                                        cmd.Parameters.AddWithValue("@Meal_Type", "晚餐");
                                    }
                                    cmd.Parameters.AddWithValue("@Meal_Cost", d[i].Substring(d[i].Length - 2, 2));
                                    using (SqlConnection cn1 = new SqlConnection())
                                    {
                                        cn1.ConnectionString = ConfigurationManager.ConnectionStrings["EmployeeConnectionString"].ConnectionString;
                                        string sqlStr = "SELECT Name,CDG_Name FROM T_Employee Where Employee_No = " + d[i].Substring(15, 5);
                                        SqlDataAdapter sda = new SqlDataAdapter(sqlStr, cn1);
                                        DataSet ds = new DataSet();
                                        sda.Fill(ds);
                                        //if (ds.Tables[0].Rows.Count == 0)
                                        //{
                                        //    cmd.Parameters.AddWithValue("@Name", "無此人資料");
                                        //    cmd.Parameters.AddWithValue("@CDG_Name", DBNull.Value);
                                        //}
                                        //else
                                        //{
                                        //    cmd.Parameters.AddWithValue("@Name", ds.Tables[0].Rows[0]["Name"].ToString());
                                        //    cmd.Parameters.AddWithValue("@CDG_Name", ds.Tables[0].Rows[0]["CDG_Name"].ToString());
                                        //}
                                        cmd.Parameters.AddWithValue("@Name", ds.Tables[0].Rows[0]["Name"].ToString());
                                        cmd.Parameters.AddWithValue("@CDG_Name", ds.Tables[0].Rows[0]["CDG_Name"].ToString());
                                    }
                                    cmd.ExecuteNonQuery();
                                    //cmd.Parameters.Clear();
                                }
                            });
                            cn.Close();
                };
                System.TimeSpan ts = DateTime.Now - t1;
                Label1.Text += "\r\n 多線程結束時間: " + ts.Seconds;
                //for (int i = 0; i < d.Length; i++)
                //{

                //}
                Core_Refresh();
                //FileUpload1.Visible = false;
                //Menu_Import.Visible = false;
            }
            catch (Exception ex)
            {
                Message.Text = ex.Message;
            }
        }

        protected void Menu_Import0_Click(object sender, EventArgs e)
        {
            try
            {
                Message.Text = "";
                if (FileUpload1.PostedFile.InputStream.Length < 1)
                {
                    Message.Text = "請選擇文件!";
                    return;
                }
                string FileName = FileUpload1.FileName;
                if (FileName.ToLower().IndexOf(".txt") == -1)
                {
                    Message.Text = "請選擇文本文件!";
                    return;
                }
                int FileLen = FileUpload1.PostedFile.ContentLength;
                byte[] input = new byte[FileLen];
                System.IO.Stream UpLoadStream = FileUpload1.PostedFile.InputStream;
                UpLoadStream.Read(input, 0, FileLen);
                UpLoadStream.Position = 0;
                System.IO.StreamReader sr = new System.IO.StreamReader(UpLoadStream, System.Text.Encoding.Default);
                String content = sr.ReadToEnd();
                sr.Close();
                UpLoadStream.Close();
                UpLoadStream = null;
                sr = null;

                String sql = "Insert INTO Meal_Details(Meal_ID,Reader_No,Reader_Date,Employee_No,Name,CDG_Name,Meal_Type,Meal_Cost) Values(@Meal_ID,@Reader_No,@Reader_Date,@Employee_No,@Name,@CDG_Name,@Meal_Type,@Meal_Cost)";
                System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ProvisionConnectionString"].ConnectionString);
                cn.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, cn);
                String[] d = System.Text.RegularExpressions.Regex.Split(content, "\r\n");
                DateTime t1 = DateTime.Now;
                for (int i = 0; i < d.Length; i++)
                {
                    cmd.Parameters.Clear();
                    if (d[i].Trim() != "")
                    {
                        cmd.Parameters.AddWithValue("@Meal_ID", Request.QueryString["Meal_ID"]);
                        cmd.Parameters.AddWithValue("@Reader_No", d[i].Substring(0, 2));
                        cmd.Parameters.AddWithValue("@Reader_Date", DateTime.ParseExact(d[i].Substring(2, 12) + "00", "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture));
                        cmd.Parameters.AddWithValue("@Employee_No", d[i].Substring(15, 5));
                        int t = Convert.ToInt16(d[i].Substring(10, 2));
                        if (t > 6 && t <= 10)
                        {
                            cmd.Parameters.AddWithValue("@Meal_Type", "早餐");
                        }
                        else if (t > 10 && t <= 16)
                        {
                            cmd.Parameters.AddWithValue("@Meal_Type", "午餐");
                        }
                        else
                        {
                            cmd.Parameters.AddWithValue("@Meal_Type", "晚餐");
                        }
                        cmd.Parameters.AddWithValue("@Meal_Cost", d[i].Substring(d[i].Length - 2, 2));
                        using (SqlConnection cn1 = new SqlConnection())
                        {
                            cn1.ConnectionString = ConfigurationManager.ConnectionStrings["EmployeeConnectionString"].ConnectionString;
                            string sqlStr = "SELECT Name,CDG_Name FROM T_Employee Where Employee_No = " + d[i].Substring(15, 5);
                            SqlDataAdapter sda = new SqlDataAdapter(sqlStr, cn1);
                            DataSet ds = new DataSet();
                            sda.Fill(ds);
                            if (ds.Tables[0].Rows.Count == 0)
                            {
                                cmd.Parameters.AddWithValue("@Name", "無此人資料");
                                cmd.Parameters.AddWithValue("@CDG_Name", DBNull.Value);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("@Name", ds.Tables[0].Rows[0]["Name"].ToString());
                                cmd.Parameters.AddWithValue("@CDG_Name", ds.Tables[0].Rows[0]["CDG_Name"].ToString());
                            }
                        }
                        cmd.ExecuteNonQuery();
                    }
                }
                System.TimeSpan ts = DateTime.Now - t1;
                Label1.Text += "\r\n 單線程結束時間: " + ts.Seconds;
                Core_Refresh();
                //FileUpload1.Visible = false;
                //Menu_Import.Visible = false;
            }
            catch (Exception ex)
            {
                Message.Text = ex.Message;
            }
        }

0 0