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;
}
}