[C#] 오류: command 와 연결된 datareader 가 이미 열려 있습니다 먼저...

반응형

C#에서 2개의 쓰레드 함수의 while(1){...} 에서

MSSql 과 연결하여 데이터를 가져올 때

'command 와 연결된 datareader 가 이미 열려 있습니다 먼저 닫아야 합니다' 라는

오류 메세지가 발생하는 원인은

1. 전역변수로 선언된 SqlConnection 변수를 두개의 쓰레드에서 동시에 사용하기 때문에 발생하는 것으로 판단됨
2. SqlDataReader와 SqlCommand 변수를 사용하고 닫지 않았기 때문

해결방법은

1. 각 쓰레드 함수에서 루프밖에 SqlConnection 변수를 선언 및 연결
2. SqlDataReader와 SqlCommand 변수를 사용한 후 Close() 및 Dispose() 함

 예제)

/// <summary>
/// 트렌드 차트의 그래프를 갱신하는 함수
/// </summary>
/// <param name="values">트렌드 차트의 데이터</param>
delegate void DRefreshTrendChart(List<ValueXY> values);
private void RefreshTrendChart(List<ValueXY> values)
{
    try
    {
 var series = CHART_TAG.Series[0];
 series.Points.Clear();

 /// 트렌드차트의 최대 데이터 갯수를 초과하면
 int cnt = m_Values.Count + values.Count - m_xMax;
 if (cnt > 0)
 {
     m_Values.RemoveRange(0, cnt);

     //int i = cnt;
     //while (i-- > 0)
     //{
     //    series.Points.RemoveAt(series.Points.Count-1);
     //}
 }
 m_Values.AddRange(values);

 foreach (ValueXY xy in m_Values)
 {
     var xDate = xy.X;
     var yValue = xy.Y;
     series.Points.AddXY(xDate, yValue);
     //series.Points[cnt].AxisLabel = xDate;
 }
    }
    catch (System.Exception ex)
    {
 GenTools.GenTools.logWrite("Monitoring=>RefreshTrendChart", ex.Message);
    }
}

/// <summary>
/// 트렌드 차트 갱신하는 쓰레드 함수
/// </summary>
public void RefreshChartThread()
{
    DateTime dt = m_curTime;

    string lastDate = "";
    double prevMin = -1.0, prevSec = -5.0;
    DateTime oldTime = (dt < DateTime.Now) ? dt : DateTime.Now;
    oldTime.AddMinutes(prevMin);
    oldTime.AddSeconds(prevSec);

    string dbConnStr = "";
    SqlConnection dbConn = new SqlConnection();
    ConnectDB(ref dbConn, m_dbAddr, m_dbName, m_dbUser, m_dbPasswd, ref dbConnStr);

    SqlDataReader ds = null;
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = dbConn;
    cmd.CommandType = CommandType.Text;

    while (m_bRunningRefreshChart)
    {
 try
 {
     if (dbConn.State.Equals("Closed"))
     {
  if (ConnectDB(ref dbConn, m_dbAddr, m_dbName, m_dbUser, m_dbPasswd, ref dbConnStr) == false)
  {
      //System.Threading.Thread.Sleep(1);
      continue;
  }
     }

     if (m_curTag == null)
     {
  dt = DateTime.Now;
  dt.AddSeconds(prevSec);
  //System.Threading.Thread.Sleep(1);
  continue;
     }

     if (Math.Abs(dt.Second - oldTime.Second) < (int)INTERVAL_X)
     {
  dt = DateTime.Now;
  dt.AddSeconds(prevSec);
  //System.Threading.Thread.Sleep(1);
  continue;
     }

     List<ValueXY> values = new List<ValueXY>();

     cmd.CommandText = string.Format("select top {0} TAG_DATE,TAG_ID,TAG_VALUE ", m_xMax);
     cmd.CommandText += "from EB_ECM_TAGVALUE ";
     cmd.CommandText += string.Format("where TAG_DATE>='{0:0000}-{1:00}-{2:00} {3:00}:{4:00}:{5:00}' and TAG_ID={6} "
  , dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second, m_curTag.id);
     cmd.CommandText += "order by TAG_DATE desc";
     ds = cmd.ExecuteReader();
    
     if (ds.HasRows)
     {
  string tagDate = "";
  double currentX = 0;
  int idx = 0;

  while (ds.Read())
  {
      tagDate = Convert.ToDateTime(ds[0].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
      currentX = double.Parse(ds[2].ToString());

      //values.Insert(0, new ValueXY(ds[1].ToString(), currentX));
      values.Add(new ValueXY(tagDate, currentX));

      if (idx++ == 0)
      {
   lastDate = tagDate;
      }
  }
     }

     if (ds != null)
     {
  ds.Close();
  ds.Dispose();
     }

     if (CHART_TAG.InvokeRequired)
     {
  DRefreshTrendChart call = new DRefreshTrendChart(RefreshTrendChart);
  this.Invoke(call, values);
     }
     else
  RefreshTrendChart(values);

     string lastDateTime = lastDate;
     if (lastDateTime.Length > 1)
     {
  dt = DateTime.Parse(lastDateTime);
  oldTime = dt;
     }
 }
 catch (System.Exception ex)
 {
     GenTools.GenTools.logWrite("Monitoring=>RefreshChartThread", ex.Message);
 }
    }

    m_bRunningRefreshChart = false;
    GenTools.GenTools.SQL_DISPOSE(ref ds, ref cmd, "Monitoring=>RefreshChartThread");
    if (dbConn != null)
 dbConn.Close();
}

delegate void DRefreshFace(List<AlarmHist> alarmList);
private void RefreshFace(List<AlarmHist> alarmList)
{
    try
    {
 foreach (ListViewItem item in LISTVIEW_TAGLIST.Items)
 {
     TagInfo tag = item.Tag as TagInfo;

     int lvl = 0;

     foreach (AlarmHist alarm in alarmList)
     {
  if (tag.name.ToUpper() == alarm.cause.ToUpper())
  {
      if (lvl < alarm.level)
   lvl = alarm.level;
  }
     }

     switch (lvl)
     {
  case 1:
  case 2:
  case 3:
      {
   item.ImageIndex = lvl;
      }
      break;
  case 0:
      {
   item.ImageIndex = 0;
      }
      break;
     }
 }
    }
    catch (System.Exception ex)
    {
 GenTools.GenTools.logWrite("Monitoring=>RefreshFace", ex.Message);
    }
}

/// <summary>
/// Face 차트 갱신하는 쓰레드 함수
/// </summary>
public void RefreshFaceThread()
{
    DateTime dt = DateTime.Now.ToLocalTime();

    double prevMin = -1.0;
    DateTime oldTime = dt;
    oldTime.AddMinutes(prevMin);

    string dbConnStr = "";
    SqlConnection dbConn = new SqlConnection();
    ConnectDB(ref dbConn, m_dbAddr, m_dbName, m_dbUser, m_dbPasswd, ref dbConnStr);

    SqlDataReader ds = null;
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = dbConn;
    cmd.CommandType = CommandType.Text;

    while (m_bRunningRefreshFace)
    {
 try
 {
     if (dbConn.State.Equals("Closed"))
     {
  if (ConnectDB(ref dbConn, m_dbAddr, m_dbName, m_dbUser, m_dbPasswd, ref dbConnStr) == false)
  {
      //System.Threading.Thread.Sleep(1);
      continue;
  }
     }

     TimeSpan ts = dt.Subtract(oldTime);
     if (ts.TotalSeconds < (int)60)
     {
  dt = DateTime.Now.ToLocalTime();
  System.Threading.Thread.Sleep(1);
  continue;
     }

     string sDate = string.Format("{0:0000}-{1:00}-{2:00} {3:00}:{4:00}:00.000"
  , dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute);
     string eDate = string.Format("{0:0000}-{1:00}-{2:00} {3:00}:{4:00}:59.999"
  , dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute);

     List<AlarmHist> alarmList = new List<AlarmHist>();

     cmd.CommandText = "select distinct ALARM_POS,ALARM_ID,ALARM_LEVEL,ALARM_CAUSE,ALARM_CONTS ";
     cmd.CommandText += "from EB_ECM_ALARMHIST ";
     cmd.CommandText += string.Format("where ALARM_DATE>='{0}' and ALARM_DATE<='{1}' ", sDate, eDate);
     cmd.CommandText += "order by ALARM_ID";
     ds = cmd.ExecuteReader();

     if (ds.HasRows)
     {
  while (ds.Read())
  {
      AlarmHist alarm = new AlarmHist();
      alarm.alarmDate = sDate;

      if (ds.IsDBNull(0) == false)
   alarm.id = Int64.Parse(ds[0].ToString());
      if (ds.IsDBNull(1) == false)
   alarm.pos = Int64.Parse(ds[1].ToString());
      if (ds.IsDBNull(2) == false)
   alarm.level = int.Parse(ds[2].ToString());
      if (ds.IsDBNull(3) == false)
   alarm.cause = ds[3].ToString();
      if (ds.IsDBNull(4) == false)
   alarm.conts = ds[4].ToString();

      alarmList.Add(alarm);
  }
     }
     if (ds != null)
     {
  ds.Close();
  ds.Dispose();
     }
     
     if (LISTVIEW_TAGLIST.InvokeRequired)
     {
  DRefreshFace call = new DRefreshFace(RefreshFace);
  this.Invoke(call, alarmList);
     }
     else
  RefreshFace(alarmList);

     oldTime = dt;
 }
 catch (System.Exception ex)
 {
     GenTools.GenTools.logWrite("Monitoring=>RefreshFaceThread", ex.Message);
 }
    }

    m_bRunningRefreshFace = false;
    GenTools.GenTools.SQL_DISPOSE(ref ds, ref cmd, "Monitoring=>RefreshFaceThread");

    if (dbConn != null)
 dbConn.Close();
}

출처: https://devdb.tistory.com/12 [DB 관련:티스토리]