以前做文件导入数据库数据量不大,没有想到这个以前我都是将整个文件上传到服务器然后循环读取,后来数据量大了几百万的数据量,按照现在的速度那简直了,所以搜索了百度读了几个贴,自己总结了下,现在发出我的方法,我是专门给自己用的所以有些问题,大家可以完善
1.C#的Dll生成(我用的是2010)
[ComVisible(true)]
public interface inputface
{
string DataInputFile(string Filename, string dataname, string tablename, string username, string password);
}
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class DataInput:inputface
{
DataSet dstiao = new DataSet();//定义一个全局
public string DataInputFile(string Filename, string dataname, string tablename, string username, string password)
{
string tag = "";
//Filename = @"D:\wite\yoroyal\tmfile\asd.txt";
int o = Filename.LastIndexOf(".");
string StrType = Filename.Substring(o);
if (StrType != ".txt")
{
//返回一个值判断文件错误
tag = "txt";
return tag;
} Hashtable FileArrlist = new Hashtable();//读取文本文件中的内容
Hashtable readDs = new Hashtable();//获取从数据库中读取的
DataTable dt;
//读取数据库中已经存在
if (Filename.Length > 0)
{
//readDs = ReadData("select distinct tm_dabiao from fw_tiaomasend where tm_tag='Y' ", "fw_tiaomasend", dataname, tablename, username, password);//读取数据库中所有的数据 ,用于判断是否重复
//读取传过来的文件内容
FileArrlist = ReadFile(Filename);
dt = new DataTable();//定义一个内存表,这个表用来记录没有重复时写入数据库
dt.Columns.Add("tm_partno", typeof(string));//表中的数据列名
dt.Columns.Add("tm_dabiao", typeof(string));
dt.Columns.Add("tm_xiaobiao", typeof(string));
//for (int i = 0; i < FileArrlist.Count; i++)//根据获取的文本文件中的内容来判断是否存在数据库中读取的数据ReadDs
//{
// //DataRow[] dv = readDs.Tables[0].Select("tm_dabiao='" + FileArrlist[i] + "'");//如果获取的数组集合中有文本文件的值
// if (readDs.ContainsValue(FileArrlist[i]))//判断是否有值
// {
// tag = "CF";//在调用时返回的值
// readDs.Clear();
// FileArrlist.Clear();
// dt.Clear();
// dstiao.Clear();//清空所有 // return tag;
// }
//}
for (int k = 0; k < dstiao.Tables["tiaomasend"].Rows.Count; k++)//如果不存在重复,就一次性导入数据库/dstiao.Tables["tiaomasend"].Rows.Count数据在ReadFile中获取
{
DataRow dr = dt.NewRow();
dr["tm_partno"] = dstiao.Tables["tiaomasend"].Rows[k]["tm_partno"].ToString().Trim();
dr["tm_dabiao"] = dstiao.Tables["tiaomasend"].Rows[k]["tm_dabiao"].ToString().Trim();
dr["tm_xiaobiao"] = dstiao.Tables["tiaomasend"].Rows[k]["tm_xiaobiao"].ToString().Trim();
dt.Rows.Add(dr);
} if (ExecuteInsertData(dt, dataname, tablename, username, password) == false) //代表成功添加
{
tag = "Ok,"+ dt.Rows.Count +"";
dt.Clear();
dstiao.Clear();
readDs.Clear();
FileArrlist.Clear();
return tag;
}
else
{
tag = "No";
dt.Clear();
dstiao.Clear();
readDs.Clear();
FileArrlist.Clear();
return tag;
}
}
return tag; }public SqlConnection Connection;
public SqlCommand Command;
public SqlDataAdapter Adapter;
public DataSet ds;
public SqlDataReader sqlread;
public Hashtable ReadData(string sql, string table, string dataname, string tablename, string username, string password)
{
Hashtable ds = new Hashtable();
int k = 0;
try
{
this.Connection = new SqlConnection(getConnectionText(dataname, tablename, username, password));
this.Connection.Open();
//this.Adapter = new SqlDataAdapter(sql, Connection);
//this.Adapter.Fill(ds, table);
Command = new SqlCommand(sql, Connection);
Command.CommandTimeout = 120;
sqlread = Command.ExecuteReader();
while (sqlread.Read())
{
ds.Add(k, sqlread.GetValue(0).ToString().Trim());
k++;
}
}
catch
{
throw;
//read = false;
}
finally
{
Connection.Close();
Connection.Dispose();
//Adapter.Dispose();
Command.Dispose();
sqlread.Dispose();
}
return ds;
}
public string getConnectionText(string dataname, string tablename, string username, string password)
{
string selectServer;
//getConnectionTextFile();
selectServer = @"server=" + dataname + ";database=" + tablename + ";uid=" + username + ";pwd=" + password + "";
return selectServer;
}
public Hashtable ReadFile(string filename)
{
Hashtable Readfile = new Hashtable(); DataTable dtiao = new DataTable("tiaomasend");//定义一个内存表
dstiao.Tables.Add(dtiao);//添加 到Ds中,这在全局已定义
dstiao.Tables["tiaomasend"].Columns.Add("tm_dabiao", typeof(string));
dstiao.Tables["tiaomasend"].Columns.Add("tm_xiaobiao", typeof(string));
dstiao.Tables["tiaomasend"].Columns.Add("tm_partno", typeof(string));
DataRow drtiao = dstiao.Tables["tiaomasend"].NewRow();//加一行
StreamReader sr;
string[] sArray;
string line = "";
int k = 0;
sr = new StreamReader(filename);
line = sr.ReadLine().Trim();
sArray = line.Split(',');
while (line.Length > 0)
{
//sArray = line.Split(',');
Readfile.Add(k, sArray[0].ToString());
drtiao["tm_dabiao"] = sArray[0].ToString();
for (int pa = 1; pa < sArray.Length; pa++)
{
drtiao["tm_xiaobiao"] = sArray[pa];
pa++;
if (pa < sArray.Length)
{
drtiao["tm_partno"] = sArray[pa];
dstiao.Tables["tiaomasend"].Rows.Add(drtiao);
drtiao = dstiao.Tables["tiaomasend"].NewRow();
drtiao["tm_dabiao"] = sArray[0].ToString();
}
} if (sr.EndOfStream == false)
{
line = sr.ReadLine().Trim();
sArray = line.Split(',');
k = k + 1;
}
else
{
line = "";
sr.Close();
}
}
sr.Close(); return Readfile;
}
public bool ExecuteInsertData(DataTable dt, string dataname, string tablename, string username, string password)
{
bool isok = false;
SqlBulkCopy sqlbc;
try
{
Connection = new SqlConnection(getConnectionText(dataname, tablename, username, password));
//this.Connection.Open();
using (sqlbc = new SqlBulkCopy(Connection))
{
Connection.Open();
sqlbc.BatchSize = 10000;
sqlbc.BulkCopyTimeout = 300;
sqlbc.DestinationTableName = "fw_tiaomasend";
sqlbc.ColumnMappings.Add("tm_partno", "tm_partno");
sqlbc.ColumnMappings.Add("tm_dabiao", "tm_dabiao");
sqlbc.ColumnMappings.Add("tm_xiaobiao", "tm_xiaobiao");
// sqlbc.ColumnMappings.Add("tm_qz", "tm_qz");
sqlbc.WriteToServer(dt); }
isok = false;
}
catch
{ isok = true; }
//sqlbc.Close();
finally
{
Connection.Close();
Connection.Dispose();
// sqlbc.Close();
}
return isok;
}
}
整个Dll的方法完成,生成后打开Visual Studio命令提示符下,生成一个密匙文件,这个可以在网上找。
输入tlbexp aspinput.dll /out:aspinput.tlb,输入regasm aspinput.dll /tlb: aspinput.tlb /codebase,将类型库导入到注册表。输入gacutil /i aspinput.dll,将此.net程序集添加到GAC全部注册完成,就可以调用了
2在自己和其他机器的区别
在自己的机器上,因为已经运行了命令,所以直接调用(注意文件的格式)
SET input = CreateObject("AspInput.DataInput") results=input.DataInputFile(Filename, Application("sql_localname"),Application("sql_databasename") ,Application("sql_username"), Application("sql_password"))
result=split(results,",")
这是Asp的调用,数据连接这个要给
如果是在其他机器上调用的话,那就要装个dotNetFx40 4.0版本的
然后在cmd 运行命令 不过要先找到regasm的目录
我的是在C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319 下的,
命令是C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\regasm aspinput.dll
注册之后重新启动下,就行了,
欢迎交流
1.C#的Dll生成(我用的是2010)
[ComVisible(true)]
public interface inputface
{
string DataInputFile(string Filename, string dataname, string tablename, string username, string password);
}
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class DataInput:inputface
{
DataSet dstiao = new DataSet();//定义一个全局
public string DataInputFile(string Filename, string dataname, string tablename, string username, string password)
{
string tag = "";
//Filename = @"D:\wite\yoroyal\tmfile\asd.txt";
int o = Filename.LastIndexOf(".");
string StrType = Filename.Substring(o);
if (StrType != ".txt")
{
//返回一个值判断文件错误
tag = "txt";
return tag;
} Hashtable FileArrlist = new Hashtable();//读取文本文件中的内容
Hashtable readDs = new Hashtable();//获取从数据库中读取的
DataTable dt;
//读取数据库中已经存在
if (Filename.Length > 0)
{
//readDs = ReadData("select distinct tm_dabiao from fw_tiaomasend where tm_tag='Y' ", "fw_tiaomasend", dataname, tablename, username, password);//读取数据库中所有的数据 ,用于判断是否重复
//读取传过来的文件内容
FileArrlist = ReadFile(Filename);
dt = new DataTable();//定义一个内存表,这个表用来记录没有重复时写入数据库
dt.Columns.Add("tm_partno", typeof(string));//表中的数据列名
dt.Columns.Add("tm_dabiao", typeof(string));
dt.Columns.Add("tm_xiaobiao", typeof(string));
//for (int i = 0; i < FileArrlist.Count; i++)//根据获取的文本文件中的内容来判断是否存在数据库中读取的数据ReadDs
//{
// //DataRow[] dv = readDs.Tables[0].Select("tm_dabiao='" + FileArrlist[i] + "'");//如果获取的数组集合中有文本文件的值
// if (readDs.ContainsValue(FileArrlist[i]))//判断是否有值
// {
// tag = "CF";//在调用时返回的值
// readDs.Clear();
// FileArrlist.Clear();
// dt.Clear();
// dstiao.Clear();//清空所有 // return tag;
// }
//}
for (int k = 0; k < dstiao.Tables["tiaomasend"].Rows.Count; k++)//如果不存在重复,就一次性导入数据库/dstiao.Tables["tiaomasend"].Rows.Count数据在ReadFile中获取
{
DataRow dr = dt.NewRow();
dr["tm_partno"] = dstiao.Tables["tiaomasend"].Rows[k]["tm_partno"].ToString().Trim();
dr["tm_dabiao"] = dstiao.Tables["tiaomasend"].Rows[k]["tm_dabiao"].ToString().Trim();
dr["tm_xiaobiao"] = dstiao.Tables["tiaomasend"].Rows[k]["tm_xiaobiao"].ToString().Trim();
dt.Rows.Add(dr);
} if (ExecuteInsertData(dt, dataname, tablename, username, password) == false) //代表成功添加
{
tag = "Ok,"+ dt.Rows.Count +"";
dt.Clear();
dstiao.Clear();
readDs.Clear();
FileArrlist.Clear();
return tag;
}
else
{
tag = "No";
dt.Clear();
dstiao.Clear();
readDs.Clear();
FileArrlist.Clear();
return tag;
}
}
return tag; }public SqlConnection Connection;
public SqlCommand Command;
public SqlDataAdapter Adapter;
public DataSet ds;
public SqlDataReader sqlread;
public Hashtable ReadData(string sql, string table, string dataname, string tablename, string username, string password)
{
Hashtable ds = new Hashtable();
int k = 0;
try
{
this.Connection = new SqlConnection(getConnectionText(dataname, tablename, username, password));
this.Connection.Open();
//this.Adapter = new SqlDataAdapter(sql, Connection);
//this.Adapter.Fill(ds, table);
Command = new SqlCommand(sql, Connection);
Command.CommandTimeout = 120;
sqlread = Command.ExecuteReader();
while (sqlread.Read())
{
ds.Add(k, sqlread.GetValue(0).ToString().Trim());
k++;
}
}
catch
{
throw;
//read = false;
}
finally
{
Connection.Close();
Connection.Dispose();
//Adapter.Dispose();
Command.Dispose();
sqlread.Dispose();
}
return ds;
}
public string getConnectionText(string dataname, string tablename, string username, string password)
{
string selectServer;
//getConnectionTextFile();
selectServer = @"server=" + dataname + ";database=" + tablename + ";uid=" + username + ";pwd=" + password + "";
return selectServer;
}
public Hashtable ReadFile(string filename)
{
Hashtable Readfile = new Hashtable(); DataTable dtiao = new DataTable("tiaomasend");//定义一个内存表
dstiao.Tables.Add(dtiao);//添加 到Ds中,这在全局已定义
dstiao.Tables["tiaomasend"].Columns.Add("tm_dabiao", typeof(string));
dstiao.Tables["tiaomasend"].Columns.Add("tm_xiaobiao", typeof(string));
dstiao.Tables["tiaomasend"].Columns.Add("tm_partno", typeof(string));
DataRow drtiao = dstiao.Tables["tiaomasend"].NewRow();//加一行
StreamReader sr;
string[] sArray;
string line = "";
int k = 0;
sr = new StreamReader(filename);
line = sr.ReadLine().Trim();
sArray = line.Split(',');
while (line.Length > 0)
{
//sArray = line.Split(',');
Readfile.Add(k, sArray[0].ToString());
drtiao["tm_dabiao"] = sArray[0].ToString();
for (int pa = 1; pa < sArray.Length; pa++)
{
drtiao["tm_xiaobiao"] = sArray[pa];
pa++;
if (pa < sArray.Length)
{
drtiao["tm_partno"] = sArray[pa];
dstiao.Tables["tiaomasend"].Rows.Add(drtiao);
drtiao = dstiao.Tables["tiaomasend"].NewRow();
drtiao["tm_dabiao"] = sArray[0].ToString();
}
} if (sr.EndOfStream == false)
{
line = sr.ReadLine().Trim();
sArray = line.Split(',');
k = k + 1;
}
else
{
line = "";
sr.Close();
}
}
sr.Close(); return Readfile;
}
public bool ExecuteInsertData(DataTable dt, string dataname, string tablename, string username, string password)
{
bool isok = false;
SqlBulkCopy sqlbc;
try
{
Connection = new SqlConnection(getConnectionText(dataname, tablename, username, password));
//this.Connection.Open();
using (sqlbc = new SqlBulkCopy(Connection))
{
Connection.Open();
sqlbc.BatchSize = 10000;
sqlbc.BulkCopyTimeout = 300;
sqlbc.DestinationTableName = "fw_tiaomasend";
sqlbc.ColumnMappings.Add("tm_partno", "tm_partno");
sqlbc.ColumnMappings.Add("tm_dabiao", "tm_dabiao");
sqlbc.ColumnMappings.Add("tm_xiaobiao", "tm_xiaobiao");
// sqlbc.ColumnMappings.Add("tm_qz", "tm_qz");
sqlbc.WriteToServer(dt); }
isok = false;
}
catch
{ isok = true; }
//sqlbc.Close();
finally
{
Connection.Close();
Connection.Dispose();
// sqlbc.Close();
}
return isok;
}
}
整个Dll的方法完成,生成后打开Visual Studio命令提示符下,生成一个密匙文件,这个可以在网上找。
输入tlbexp aspinput.dll /out:aspinput.tlb,输入regasm aspinput.dll /tlb: aspinput.tlb /codebase,将类型库导入到注册表。输入gacutil /i aspinput.dll,将此.net程序集添加到GAC全部注册完成,就可以调用了
2在自己和其他机器的区别
在自己的机器上,因为已经运行了命令,所以直接调用(注意文件的格式)
SET input = CreateObject("AspInput.DataInput") results=input.DataInputFile(Filename, Application("sql_localname"),Application("sql_databasename") ,Application("sql_username"), Application("sql_password"))
result=split(results,",")
这是Asp的调用,数据连接这个要给
如果是在其他机器上调用的话,那就要装个dotNetFx40 4.0版本的
然后在cmd 运行命令 不过要先找到regasm的目录
我的是在C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319 下的,
命令是C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\regasm aspinput.dll
注册之后重新启动下,就行了,
欢迎交流
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货