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

  • 问题

  • 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));
    		}
    	}
    }
    
    2009年9月1日 8:13

答案

  •   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)); } } }

    2009年9月1日 10:48
  •  // 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
                }

    知识改变命运,奋斗成就人生!
    2009年9月2日 1:57
    版主

全部回复

  • 第一个问题
    CmdStr = @"Select * From [Base] Where InPutStatus=false" 有这样sql吗?
    第二个问题
    不建议在循环中用SqlTransaction 
    浪费大量没必要资源
    2009年9月1日 9:01
  • 第一个问题
    CmdStr = @"Select * From [Base] Where InPutStatus=false" 有这样sql吗?
    第二个问题
    不建议在循环中用SqlTransaction 
    浪费大量没必要资源

    SQL语句是正确的

    您说的第二个问题,要怎么解决呢?
    2009年9月1日 9:49
  •   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)); } } }

    2009年9月1日 10:48
  • while里有7W多条的记录.
    如果像你这样写的话合适吗?
    2009年9月2日 1:01
  •  // 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
                }

    知识改变命运,奋斗成就人生!
    2009年9月2日 1:57
    版主
  • while里有7W多条的记录.
    如果像你这样写的话合适吗?

    合适  超过5条锁就会改成锁表   比锁7w次强多了
    恭喜自己5星用户达成
    2009年9月2日 4:31