[求助]这段代码为什么SqlHelper老报错

Question

using

 (OleDbConnection aconn = new

 OleDbConnection(AConnStr))
{
	string

 tmpperID = ""

;
	string

 CmdStr = ""

;
	BaseTb mBaseTb = new

 BaseTb();
	InfoTb mInfoTb = new

 InfoTb();
	EducationTb mEducationTb = new

 EducationTb();
	WorkTb mWorkTb = new

 WorkTb();
	CmdStr = @"Select * From [Base] Where InPutStatus=false"

;
	OleDbDataReader OleDBdr = AccessHelper.ExecuteReader(AConnStr, CmdStr);
	using

 (SqlConnection conn = new

 SqlConnection(SConnStr))
	{
		conn.Open();
		SqlTransaction transaction = conn.BeginTransaction();

		while

 (OleDBdr.Read())
		{
			mBaseTb.perID = OleDBdr["perID"

].ToString();
			mBaseTb.UserName = OleDBdr["UserName"

].ToString();
			CmdStr = @"Select * From [pH_Person_Base] Where perId=@perID"

;
			SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, CmdStr, new

 SqlParameter("@perID"

, mBaseTb.perID));
		}
	}
}

为什么我这句运行时,SqlHelper老报错!

但是换成下面这样可以运行一会,过一段时间后就会提示"系统资源不足,无法完成请求的服务。"

using
 (OleDbConnection aconn = new
 OleDbConnection(AConnStr))
{
	string
 tmpperID = ""
;
	string
 CmdStr = ""
;
	BaseTb mBaseTb = new
 BaseTb();
	InfoTb mInfoTb = new
 InfoTb();
	EducationTb mEducationTb = new
 EducationTb();
	WorkTb mWorkTb = new
 WorkTb();
	CmdStr = @"Select * From [Base] Where InPutStatus=false"
;
	OleDbDataReader OleDBdr = AccessHelper.ExecuteReader(AConnStr, CmdStr);

	while
 (OleDBdr.Read())
	{
		using
 (SqlConnection conn = new
 SqlConnection(SConnStr))
		{
			conn.Open();
			SqlTransaction transaction = conn.BeginTransaction();

			mBaseTb.perID = OleDBdr["perID"
].ToString();
			mBaseTb.UserName = OleDBdr["UserName"
].ToString();
			CmdStr = @"Select * From [pH_Person_Base] Where perId=@perID"
;
			SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, CmdStr, new
 SqlParameter("@perID"
, mBaseTb.perID));
		}
	}
}

Answer 1

第一个问题
CmdStr = @"Select * From [Base] Where InPutStatus=false" 有这样sql吗？
第二个问题
不建议在循环中用SqlTransaction 
浪费大量没必要资源

Answer 2

第一个问题
CmdStr = @"Select * From [Base] Where InPutStatus=false" 有这样sql吗？
第二个问题
不建议在循环中用SqlTransaction 
浪费大量没必要资源

SQL语句是正确的

您说的第二个问题,要怎么解决呢?

Answer 3

  using
(SqlConnection conn = new
SqlConnection(SConnStr))
{
   conn.Open();
   SqlTransaction transaction = conn.BeginTransaction();
while(OleDBdr.Read())
	{


  	mBaseTb.perID = OleDBdr["perID"
].ToString();
			mBaseTb.UserName = OleDBdr["UserName"
].ToString();
			CmdStr = @"Select * From [pH_Person_Base] Where perId=@perID"
;
			SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, CmdStr, new
 SqlParameter("@perID"
, mBaseTb.perID));
		}
	}
}

Answer 4

while里有7W多条的记录.
如果像你这样写的话合适吗?

Answer 5

 // 1 aconn 声明了未使用
            using (OleDbConnection aconn = new OleDbConnection(AConnStr))
            {
                string tmpperID = "";
                string CmdStr = "";
                BaseTb mBaseTb = new BaseTb();
                InfoTb mInfoTb = new InfoTb();
                EducationTb mEducationTb = new EducationTb();
                WorkTb mWorkTb = new WorkTb();
                CmdStr = @"Select * From [Base] Where InPutStatus=false";
                OleDbDataReader OleDBdr = AccessHelper.ExecuteReader(AConnStr, CmdStr); // 2 这里应改为（如果有这种重载的话） OleDbDataReader OleDBdr = AccessHelper.ExecuteReader(aconn, CmdStr);

                while (OleDBdr.Read())
                {
                    using (SqlConnection conn = new SqlConnection(SConnStr))
                    {
                        conn.Open();
                        // 3 Select 语句在大多数情况下都不需要使用事务
                        //SqlTransaction transaction = conn.BeginTransaction();

                        mBaseTb.perID = OleDBdr["perID"].ToString();
                        mBaseTb.UserName = OleDBdr["UserName"].ToString();
                        CmdStr = @"Select * From [pH_Person_Base] Where perId=@perID";
                        //SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, CmdStr, new SqlParameter("@perID", mBaseTb.perID));
                        // 4 这里应改为（如果有这种重载的话）
                        // 5 既然 Select  语句应该调用 ExecuteDataSet 、 ExecuteDataTable 等方法，并且声明变量接收返回值
                        DataSet retDS = SqlHelper.ExecuteDataSet(conn, CommandType.Text, CmdStr, new SqlParameter("@perID", mBaseTb.perID));
                    }
                }

                OleDBdr.Close(); // 需要显示的关闭 DataReader
            }

---

知识改变命运，奋斗成就人生!

Answer 6

while里有7W多条的记录.
如果像你这样写的话合适吗?

合适  超过5条锁就会改成锁表   比锁7w次强多了

---

恭喜自己5星用户达成