利用循环向数据库中插入数据，参数重复的问题
0
悬赏园豆：10
[已解决问题]
解决于 2014-08-21 11:32
源数据在txt文件里，想插入到数据库里，思路是打开数据库，读取文件，逐行插入，代码如下
using (SqlConnection conn=new SqlConnection("Data Source=.;Initial Catalog=wsydb;User Id=sa;PassWord=wsy231925"))//连接数据库
{
conn.Open();//打开连接
using (SqlCommand cmd = conn.CreateCommand())
{
IEnumerable<string> lines = File.ReadLines(@"D:\C#Program\TelArea\手机号段归属地数据库20120814.txt", Encoding.Default);//读取所有行
List<string> strs = lines.ToList();//讲结果转为List
//第一行列名，不要
for (int i = 1; i < lines.Count(); i++)
{
string[] str = strs[i].Split('\t');
string NumStart = str[0];
string Area = str[1].Trim('"');
string Type = str[2].Trim('"');
//以上是对数据的分割，提取
cmd.CommandText = "insert into T_TelArea (NumStart,Area,Type) values(@NumStart,@Area,@Type)";//SQL语句
SqlParameter[] param = { new SqlParameter("@NumStart", NumStart), new SqlParameter("@Area", Area), new SqlParameter("@Type", Type) };//上面的三个参数
cmd.Parameters.AddRange(param);//加入参数
cmd.ExecuteNonQuery();//执行SQL
}
}
}
由于插入的过程中数据库一直在连接，所以插入一条数据之后，会说NumStart参数已存在，第一次执行时加入到了cmd.Parameters。有一个方法：如果把数据库的连接放到for循环里，即每插入一条数据就进行一次数据库的连接与关闭，但频繁的连接关闭会带来效率的降低。
问题：大家有什么好办法可以只连接一次把所有数据都插入吗?
最佳答案
0
一个打开一个关闭！不要把开关写到循环，就这么简单！
/// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> public static bool ExecuteSqlTran(string connectionString, Hashtable SQLStringList, CommandType cmType) { SqlConnection conn = null; try { using (conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { //循环 foreach (string myDE in SQLStringList.Keys) { string cmdText = myDE; SqlParameter[] cmdParms = (SqlParameter[])SQLStringList[myDE]; //PrepareCommand(cmd, conn, trans, cmdText, cmdParms); PrepareCommand(cmd, trans.Connection, trans, cmType, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch (Exception e) { string ExcMSG = e.Message; trans.Rollback(); return false; } } return true; } } catch (Exception) { throw; } finally { if (conn != null) { conn.Close(); conn.Dispose(); } } }
不要用自己写的ADO，这是微软的标准类，我自己稍微改了下。
收获园豆：10
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } }
好意思拉下一个类
落下一个方法。。。
其他回答(5)
0
t生成一个dataset 一次性提交 使用它的一个方法
0
SqlBulkCopy
0
”insert......;insert....;“生成一个sql
插入后需要清理
cmd.Parameters.Clear();
说得对，清理下就行~
0
在语句：
cmd.Parameters.AddRange(param);//加入参数
之前，执行：
cmd.Parameters.Clear();
using (SqlConnection conn=new SqlConnection("Data Source=.;Initial Catalog=wsydb;User Id=sa;PassWord=wsy231925"))//连接数据库 { conn.Open();//打开连接 using (SqlCommand cmd = conn.CreateCommand()) { IEnumerable<string> lines = File.ReadLines(@"D:\C#Program\TelArea\手机号段归属地数据库20120814.txt", Encoding.Default);//读取所有行 List<string> strs = lines.ToList();//讲结果转为List //第一行列名，不要 cmd.CommandText = "insert into T_TelArea (NumStart,Area,Type) values(@NumStart,@Area,@Type)";//SQL语句 SqlParameter[] param = { new SqlParameter("@NumStart", ""), new SqlParameter("@Area", ""), new SqlParameter("@Type", "") };//上面的三个参数 cmd.Parameters.AddRange(param);//加入参数 for (int i = 1; i < lines.Count(); i++) { string[] str = strs[i].Split('\t'); string NumStart = str[0]; string Area = str[1].Trim('"'); string Type = str[2].Trim('"'); //以上是对数据的分割，提取 cmd.Parameters["@NumStart"] = NumStart; cmd.Parameters["@Area"] = Area; cmd.Parameters["@Type"] = Type; cmd.ExecuteNonQuery();//执行SQL } } }
这个是你的方案，还可以修正为以下方案：
0
又看了些资料，发现对于说参数存在的问题可以把SqlCommand的创建放到For循环里，可以只连一次数据库，但是效率还是没提高。效率的问题不在于对数据库的开关，而是还是逐条地插入数据。而且数据库连接有数据库连接池，并不是每次都创建一个连接。可以用SqlBulkCopy实现批量插入。先把数据存到一个DataTable里，然后WriteToServer(datatable);搞定，大约10秒钟，25万多条数据。
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;//获取连接的字符串
using (SqlConnection sqlConn = new SqlConnection(connStr))//创建一个数据库连接
{
sqlConn.Open();//打开链接
//读取文件部分
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "文本文件|*.txt";//过滤器
ofd.InitialDirectory = @"D:\C#Program\TelAreaSqlBulkCopy";//创建初始目录
if (ofd.ShowDialog() != true)
{
return;//如果，没选则的话
}
string fileName = ofd.FileName;//获得文件名
IEnumerable<string> lines = File.ReadLines(fileName, Encoding.Default);//读取全部行，是 IEnumerable<string>
List<string> strs = lines.ToList();//转一下类型
DataTable datatable = new DataTable();//创建一个表，等会把读的数据存进去，有三列入下
datatable.Columns.Add("NumStart");
datatable.Columns.Add("Area");
datatable.Columns.Add("NumType");
DateTime datatime1 = DateTime.Now;
//利用for 把数据插入Datatable
for (int i = 1; i < strs.Count; i++)//第一行是列名，不要，所以i=1
{
string[] str = strs[i].Split('\t');//分割字符串
string NumStart = str[0];
string Area = str[1].Trim('"');
string NumType = str[2].Trim('"');
//把数据加入dataTable
DataRow newRow = datatable.NewRow();
newRow["NumStart"] = NumStart;
newRow["Area"] = Area;
newRow["NumType"] = NumType;
datatable.Rows.Add(newRow);
}
DateTime datatime2 = DateTime.Now;
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConn))//利用连接创建一个SqlBulkCopy
{
sqlBulkCopy.DestinationTableName = "T_TelAreaFull";
sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("NumStart", "NumStart"));
sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Area", "Area"));
sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("NumType", "NumType"));
sqlBulkCopy.WriteToServer(datatable);
}
DateTime datatime3 = DateTime.Now;
TimeSpan timespan1 = datatime2 - datatime1;
TimeSpan timespan2 = datatime3 - datatime2;
timespan1.TotalSeconds.ToString();
timespan2.TotalSeconds.ToString();
MessageBox.Show("第一阶段时间" + timespan1.TotalMinutes.ToString() + "第二阶段时间" + timespan2.TotalSeconds.ToString());
}
