Txt文件导入 (2种方法)
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "文本文件|*.txt";
if (ofd.ShowDialog() == false)
{
return;
}
string[] lines = File.ReadLines(ofd.FileName, Encoding.Default).ToArray();
DateTime startTime = DateTime.Now;
DataTable table = new DataTable();
table.Columns.Add("startTelNum");
table.Columns.Add("city");
table.Columns.Add("telType");
table.Columns.Add("telNum");
for (int i = 0; i < lines.Length; i++)
{
string line = lines[i];
//string[] s = line.Split(new char[] { '#' });
string[] strs = line.Split('\t'); //vs里 '\t'为制表符
string startTelNum = strs[0];
string city = strs[1];
city = city.Trim('"'); //去掉两边的双引号:"北京市"
string telType = strs[2];
telType = telType.Trim('"');
string telNum = strs[3];
telNum = telNum.Trim('"');
DataRow row = table.NewRow(); //创建一个DataRow对象
row["startTelNum"] = startTelNum; //一定要在一开始创建table.Columns添加列
row["city"] = city;
row["telType"] = telType;
row["telNum"] = telNum;
table.Rows.Add(row); //NewRow只是创建,没有插入
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr))
{
//添加DAtaTAble中列名和数据库表汇中列名的映射
bulkCopy.DestinationTableName = "TelNums";
bulkCopy.ColumnMappings.Add("startTelNum", "startTelNum");
bulkCopy.ColumnMappings.Add("city", "telArea");
bulkCopy.ColumnMappings.Add("telType", "telType");
bulkCopy.ColumnMappings.Add("telNum", "telNum");
bulkCopy.WriteToServer(table);
bulkCopy.Close();
}
table.Clear();
table.Dispose();
TimeSpan ts = DateTime.Now - startTime;
MessageBox.Show(ts.ToString());
// using (SqlConnection conn = new SqlConnection(connStr))
// {
// conn.Open();
// for (int i = 0; i < lines.Length; i++)
// {
// string line = lines[i];
// string[] strs = line.Split('\t');//vs里 '\t'为制表符
// string startTelNum = strs[0];
// string city = strs[1];
// city = city.Trim('"');//去掉两边的双引号:"北京市"
// string telType = strs[2];
// telType = telType.Trim('"');
// //数据库连接池:ADO.Net会尽可能的复用连接池中的连接。
// using (SqlCommand cmd = conn.CreateCommand())
// {
// cmd.CommandText = @"insert into T_TelNum(StartTelNum,TelType,TelArea)
// values(@StartTelNum,@TelType,@TelArea)";
// cmd.Parameters.Add(new SqlParameter("@StartTelNum", startTelNum));
// cmd.Parameters.Add(new SqlParameter("@TelType", telType));
// cmd.Parameters.Add(new SqlParameter("@TelArea", city));
// cmd.ExecuteNonQuery();
// }
// TimeSpan ts = DateTime.Now - startTime;//DateTime得到TimeSpan类型
// //ts.TotalSeconds//时间段代码的总秒数
// double ellapsedSec = ts.TotalSeconds;//已经耗用的秒数
// //double totalSec = (ellapsedSec / (i + 1)) * lines.Length; //先乘后除更精确
// double totalSec = ellapsedSec * lines.Length / (i + 1);//估计需要的总秒数
// }
// }