运行工具 vs2008+sql2005 ,导入150万条数据 第一次运行需要3-4分钟 ,第二次运行需要44秒哦
表结构 》 文件
前台————————————————————————————————————————————
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Button" οnclick="Button1_Click" />
</div>
</form>
</body>
</html>
后台————————————————————————————————————————————
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;
using System.Collections;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
//读出文本的内容
private string TxtFileToStr()
{
string path = FileUpload1.PostedFile.FileName.ToString().Trim(); //得到文本路径
string kk = Server.MapPath("2_201005141.TXT").ToString();
FileInfo file = new FileInfo(path);
FileStream fs = file.OpenRead();
StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
sr.BaseStream.Seek(0, SeekOrigin.Begin);
string str = sr.ReadToEnd();
fs.Close();
sr.Close();
return str;
}
//事务处理插入数据库中
private void Exesql()
{
//调用方法TxtFileToStr()
string str = TxtFileToStr();
string[] sql = str.Split('|'); //得到string 数组
int num = sql.Length - 1;
/*构建DataTable 开始 */
DataTable dt = new DataTable();
dt.Columns.Add("name",typeof(string));
dt.Columns.Add("num", typeof(string));
dt.Columns.Add("telephone", typeof(string));
dt.Columns.Add("num2", typeof(string));
dt.Columns.Add("telephone2", typeof(string));
DataRow dr = null;
SqlConnection conn = null;
int number = 0; //标识哪行有问题
int col = 0;//标识那列有问题
int count=num/5;
ArrayList list = new ArrayList();
try
{
for (int i = 0; i < num; i++)
{
list.Add(sql[i].ToString().Trim());
if ((i + 1) % 5 == 0)// 表中有几列 就除余几 (本表为5列)
{
number++;
//实例化 行
dr = dt.NewRow();
col = 1;
dr["name"] = list[0].ToString().Trim();
col = 2;
dr["num"] = list[1].ToString().Trim();
col = 3;
dr["telephone"] = list[2].ToString().Trim();
col = 4;
dr["num2"] = list[3].ToString().Trim();
col = 5;
dr["telephone2"] = list[4].ToString().Trim();
dt.Rows.Add(dr);
list.Clear(); //清空 ArrayList对象
}
}
string conString = ConfigurationManager.AppSettings["ConnectionString"].ToString();
//声明数据库连接
conn = new SqlConnection(conString);
conn.Open();
//声明SqlBulkCopy ,using释放非托管资源
using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
{
//一次批量的插入的数据量
sqlBC.BatchSize = 2000;
//超时之前操作完成所允许的秒数,如果超时则事务不会提交 ,数据将回滚,所有已复制的行都会从目标表中移除
sqlBC.BulkCopyTimeout = 60;
//设置要批量写入的表
sqlBC.DestinationTableName = "test";
//自定义的datatable和数据库的字段进行对应
sqlBC.ColumnMappings.Add("name", "a");
sqlBC.ColumnMappings.Add("num", "b");
sqlBC.ColumnMappings.Add("telephone", "c");
sqlBC.ColumnMappings.Add("num2", "d");
sqlBC.ColumnMappings.Add("telephone2", "e");
//批量写入
sqlBC.WriteToServer(dt);
}
ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('导入成功!')</script>");
}
catch
{
string s = "第" + number + "行,第" + col + "列有问题;请检查一下txt文件!";
ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('" + s + "')</script>");
}
finally
{
dt.Rows.Clear();
conn.Close();
GC.Collect();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
//获取当前时间的刻度数
TimeSpan ts1 = new TimeSpan(DateTime.Now.Ticks);
//你的代码或者其他操作
Exesql();
TimeSpan ts2 = new TimeSpan(DateTime.Now.Ticks);
TimeSpan ts = ts2.Subtract(ts1).Duration();
//时间差
string spanTime =ts.Hours.ToString()+"时"+ts.Minutes.ToString() + "分" + ts.Seconds.ToString() + "秒"+ts.Milliseconds+"毫秒";
Response.Write(spanTime);
}
}