天生舞男

我喜欢谦虚的学习各种...,希望自己能坚持一辈子,因为即使一张卫生巾也是有它的作用.
posts - 76, comments - 39, trackbacks - 1, articles - 4
  博客园 :: 首页 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理

公告

MS bug "The connection pool" in Oracle 10g and the data sort according to specified filed on DataGrid control.

Posted on 2006-03-15 15:23 天生舞男 阅读(...) 评论(...) 编辑 收藏

1.Issue:
    The program prepares three SQL sentences to execute, the operation is like:
    1. It is correct to executes the first SQL sentence. 
    2. before executes the second SQL sentence, we pull out the network line, and executes the second SQL sentence.
    3. before executes the third SQL sentence, plug the network line, and executes the third SQL sentence.
    result:   
    The third SQL sentence can't execute correctly
2.Causation:
    The issue occurs by connection pool, the defualt value of "Connection Lifetime" is max timeout, so when we pull out the network line, and then plug it, the next OracleConnection object will using the previous OracleConnection object from connection pool, and the previous OracleConnection object is invalid, so the program leads to the issue.

3.How to resolve:
   The progrom had added the connection string with ";Pooling='true';Connection Lifetime=10".
   If over 10 seconds, the progrom will create a new OraleConnection object, but it is not the best solution for the 
   performance is inefficient, but if we setup the .Net Framework 1.1 SP1 cann't resolve the issue.

private void OpenConnection()
{
    oconDBCon = new OracleConnection(strDBConStr);
    oconDBCon.Open();
}

private void CloseConnection()
{
    if (null != oconDBCon)
    {
        if (ConnectionState.Open == oconDBCon.State)
        {
             oconDBCon.Close();
        }
        oconDBCon = null;
    }
}

public DataTable DPExecuteDataTable(string strSql)
{
    DataTable dtRet = new DataTable();
    OracleCommand ocomSql = null;
    OracleDataAdapter odaSql = new OracleDataAdapter();

    try
    {
        OpenConnection();
        ocomSql = oconDBCon.CreateCommand();
         ocomSql.CommandText = strSql;
         odaSql.SelectCommand = ocomSql;
         odaSql.Fill(dtRet);
    }
    catch (OracleException oex)
    {  
                CDPEventLog.ThrowException("MSGID_ERROR_DPEXECUTEDATATABLE_ORACLE_EXCEPTION",     strSql, oex);
     }
    finally
    {
         CloseConnection();
    }
    return dtRet;
}

The second program is for the data sort according to specified filed on DataGrid control when customming the fields
private void Page_Load(object sender, System.EventArgs e)
  {
   if(!Page.IsPostBack)
   {
    ViewState["strSortExpression"] = "";
   }
}
private void dgSort_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
  { 
   string strSortExpression = e.SortExpression;
   if (ViewState["strSortExpression"].ToString() == strSortExpression)
   {
    strSortExpression = strSortExpression.Replace("DESC", "ASC");
   }
   ViewState["strSortExpression"] = strSortExpression;
   dasEquipmentList.Tables[0].DefaultView.Sort = ViewState["strSortExpression"].ToString();
   dgSort.DataSource = dasEquipmentList.Tables[0].DefaultView;
   dgSort.DataBind();
  }