excel批量导入sql,应用事务的问题
string FileSource = File1.Value;
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + @FileSource + ";Extended Properties=Excel 8.0";
string StyleSheet = "sheet1";
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open(); //打开数据链接,得到一个数据集
DataSet myDataSet = new DataSet(); //得到自己的DataSet对象
string StrSql = "select * from [" + StyleSheet + "$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn); //创建一个 DataSet对象
SqlTransaction myTrans;
SqlConnection conn = new SqlConnection(Configuration.DbConnectionString);
conn.Open();
myTrans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
SqlCommand comm = conn.CreateCommand();
comm.Connection = conn;
comm.Transaction = myTrans;
try
{
try
{
myCommand.Fill(myDataSet, "[" + StyleSheet + "$]");
myCommand.Dispose();
}
catch (Exception e)
{
//MessageBox.Show(e.Message, "错误");
}
DataTable DT = myDataSet.Tables["[" + StyleSheet + "$]"];
myConn.Close();
if (getData(DT.Rows[0][5].ToString()) == 0)
{
string sql = "";
//myConn.Close();
for (int j = 3; j < DT.Rows.Count; j++)
{
//Console.Write(DT.Rows[j][0].ToString());
if (DT.Rows[j][0].ToString() != "")
{
sql = "insert into icbc_deposit(NO,company,sdye,sdrjye,address,area,createdate) values('" + DT.Rows[j][0].ToString() + "','" + DT.Rows[j][1].ToString() + "'";
sql = sql + ",'" + DT.Rows[j][3].ToString() + "','" + DT.Rows[j][4].ToString() + "','" + DT.Rows[j][5].ToString() + "','" + DT.Rows[j][6].ToString() + "','" + DT.Rows[0][5].ToString() + "')";
comm.CommandText = sql;
comm.ExecuteNonQuery();
}
}
DateTime begindate = Convert.ToDateTime(DT.Rows[0][5]);
DateTime redate = begindate.AddDays(-1);
comm.CommandText = "insert into icbc_managerdate select NO,company,address,'未分配','" + begindate.ToString() + "',null,1 from icbc_deposit where createdate='" + begindate.ToString() + "' and no not in (select no from icbc_deposit where createdate='" + redate.ToString()+ "')";
comm.ExecuteNonQuery();
comm.CommandText = "update icbc_managerdate set flag=0,enddate='"+begindate.ToString()+"' where no in (select no from icbc_deposit where createdate='" + redate.ToString() + "' and no not in (select no from icbc_deposit where createdate='" + begindate.ToString() + "'))";
comm.ExecuteNonQuery();
myTrans.Commit();
comm.Dispose();
conn.Dispose();
conn.Close();
}
else {
//Response.Write("<script></s");pag
Page.RegisterStartupScript("onclick", "<script>alert('数据重复导入!');</script>");
}
}
catch (IndexOutOfRangeException e)
{
//MessageBox.Show(e.Message,"错误");
//throw;
try
{
myTrans.Rollback();
}
catch (OleDbException ex)
{
if (myTrans.Connection != null)
{
//Console.WriteLine("An exception of type " + ex.GetType() + " was encountered while attempting to roll back the transaction.");
Page.RegisterStartupScript("onclick", "<script>alert('数据导入出错!');</script>");
}
}
}
这段代码,在第一次导入没问题,第二次导入就出现很慢的情况,感觉有数据库资源没有释放,但是我没有发现问题,请大家帮我看看 --------------------编程问答-------------------- 没有看出什么问题 先友情up
ps:建议下次再发类似问题的时候,代码最好是加上代码对应的格式。 --------------------编程问答--------------------
--------------------编程问答-------------------- 在
string FileSource = File1.Value;
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + @FileSource + ";Extended Properties=Excel 8.0";
string StyleSheet = "sheet1";
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open(); //打开数据链接,得到一个数据集
DataSet myDataSet = new DataSet(); //得到自己的DataSet对象
string StrSql = "select * from [" + StyleSheet + "$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn); //创建一个 DataSet对象
SqlTransaction myTrans;
SqlConnection conn = new SqlConnection(Configuration.DbConnectionString);
conn.Open();
myTrans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
SqlCommand comm = conn.CreateCommand();
comm.Connection = conn;
comm.Transaction = myTrans;
try
{
try
{
myCommand.Fill(myDataSet, "[" + StyleSheet + "$]");
myCommand.Dispose();
}
catch (Exception e)
{
//MessageBox.Show(e.Message, "错误");
}
DataTable DT = myDataSet.Tables["[" + StyleSheet + "$]"];
myConn.Close();
if (getData(DT.Rows[0][5].ToString()) == 0)
{
string sql = "";
//myConn.Close();
for (int j = 3; j < DT.Rows.Count; j++)
{
//Console.Write(DT.Rows[j][0].ToString());
if (DT.Rows[j][0].ToString() != "")
{
sql = "insert into icbc_deposit(NO,company,sdye,sdrjye,address,area,createdate) values('" + DT.Rows[j][0].ToString() + "','" + DT.Rows[j][1].ToString() + "'";
sql = sql + ",'" + DT.Rows[j][3].ToString() + "','" + DT.Rows[j][4].ToString() + "','" + DT.Rows[j][5].ToString() + "','" + DT.Rows[j][6].ToString() + "','" + DT.Rows[0][5].ToString() + "')";
comm.CommandText = sql;
comm.ExecuteNonQuery();
}
}
DateTime begindate = Convert.ToDateTime(DT.Rows[0][5]);
DateTime redate = begindate.AddDays(-1);
comm.CommandText = "insert into icbc_managerdate select NO,company,address,'未分配','" + begindate.ToString() + "',null,1 from icbc_deposit where createdate='" + begindate.ToString() + "' and no not in (select no from icbc_deposit where createdate='" + redate.ToString()+ "')";
comm.ExecuteNonQuery();
comm.CommandText = "update icbc_managerdate set flag=0,enddate='"+begindate.ToString()+"' where no in (select no from icbc_deposit where createdate='" + redate.ToString() + "' and no not in (select no from icbc_deposit where createdate='" + begindate.ToString() + "'))";
comm.ExecuteNonQuery();
myTrans.Commit();
comm.Dispose();
conn.Dispose();
conn.Close();
}
else {
//Response.Write(" <script> </s");pag
Page.RegisterStartupScript("onclick", " <script>alert('数据重复导入!'); </script>");
}
}
catch (IndexOutOfRangeException e)
{
//MessageBox.Show(e.Message,"错误");
//throw;
try
{
myTrans.Rollback();
}
catch (OleDbException ex)
{
if (myTrans.Connection != null)
{
//Console.WriteLine("An exception of type " + ex.GetType() + " was encountered while attempting to roll back the transaction.");
Page.RegisterStartupScript("onclick", " <script>alert('数据导入出错!'); </script>");
}
}
}
catch (IndexOutOfRangeException e)
{
//MessageBox.Show(e.Message,"错误");
//throw;
try
{
myTrans.Rollback();
}
catch (OleDbException ex)
{
if (myTrans.Connection != null)
{
Page.RegisterStartupScript("onclick", " <script>alert('数据导入出错!'); </script>");
}
}
}
里加上 释放链接等资源的语句
下面 加个catch Exception
里加上 释放链接等资源的语句 --------------------编程问答-------------------- 连接excel后,关闭连接,通过GC释放资源 --------------------编程问答-------------------- 谁能给个详细点的解决办法啊?帮忙把代码贴上来啊,谢谢啦 --------------------编程问答-------------------- ''''''''''''''''''' --------------------编程问答-------------------- 学习下。!
补充:.NET技术 , ASP.NET