[C#] ADO 데이터 관련 함수 라이브러리

DataRow 의 데이터를 Insert Sql  쿼리문(DataRow to Sql Query)으로 변환 (하단에 DataCnv, TypeCnv 메소드 사용)

/// <summary>
/// 데이터셋의 특정 레코드를 Insert SQL문장으로 생성
/// </summary>
/// <param name="arSqls">sql문이 담길 객체</param>
/// <param name="dt">데이터넷</param>
/// <param name="psDisplay">sql문 생성시 제외되어야할 필드명(화면 디스플레이용)</param>
public void InsertDataSetToSql(ref ArrayList arSqls, ref DataTable dt, params string[] psDisplay)
{

    for (int nIndex = 0; nIndex < dt.Rows.Count; nIndex++)
    {
        StringBuilder sQuery = new StringBuilder();
        StringBuilder sField = new StringBuilder();
        StringBuilder sData = new StringBuilder();

        string sColumnName;
        string sColumnType;
        string sColumnData;
        bool bIsNotColumn;

        foreach (DataColumn dc in dt.Columns)
        {
            sColumnName = dc.ColumnName;
            sColumnType = TypeCnv(dc.DataType.ToString());
            switch (sColumnType)
            {
                case "D":
                    if (dt.Rows[nIndex][sColumnName].ToString().Trim().Length == 0)
                        sColumnData = "";
                    else
                        sColumnData = string.Format("{0:yyyy/MM/dd HH:mm:ss}", (DateTime)dt.Rows[nIndex][sColumnName]);
                    break;
                case "B":
                    sColumnData = (bool)dt.Rows[nIndex][sColumnName] ? "1" : "0";
                    break;
                case "F":
                    byte[] bColumnData = (byte[])dt.Rows[nIndex][sColumnName];

                    sColumnData = DataCnv("S", Encoding.ASCII.GetString(bColumnData));

                    break;
                default:
                    sColumnData = dt.Rows[nIndex][sColumnName].ToString();
                    break;
            }

            bIsNotColumn = false;
            foreach (string sNotColumn in psDisplay)
            {
                if (sNotColumn.Equals(sColumnName))
                {
                    bIsNotColumn = true;
                    break;
                }
            }
            if (bIsNotColumn) continue;

            if (sField.Length != 0 && !CisString.Empty(sColumnData))
            {
                sField.Append(", ");
                sData.Append(", ");
            }

            if (!CisString.Empty(sColumnData))
            {
                sField.Append(sColumnName);
                if(sColumnType == "F")
                    sData.Append("CONVERT(VARBINARY(MAX)," + sColumnData + ")");
                else
                    sData.Append(DataCnv(sColumnType, sColumnData));
            }
        }

        sQuery.AppendFormat("Insert Into {0} ({1}) values({2})", dt.TableName, sField.ToString(), sData.ToString());
        arSqls.Add(sQuery.ToString());
    }

    return;
}

DataRow 의 데이터를 Update Sql 쿼리문(DataRow to Sql Query)으로 변환 (하단에 DataCnv, TypeCnv 메소드 사용)

/// <summary>
/// 데이터 셋의 특정 레코드를 Update SQL 문장으로 생성
/// </summary>
/// <param name="arSqls">sql문이 담길 객체</param>
/// <param name="dt">데이터넷</param>
/// <param name="psDisplay">sql문 생성시 제외되어야할 필드명(화면 디스플레이용)</param>
public void UpdateDataSetToSql(ref ArrayList arSqls, ref DataTable dt, params string[] psDisplay)
{
    string sColumnName;
    string sColumnType;
    string sColumnData;
    bool bIsNotUpdate = false;

    for (int nIndex = 0; nIndex < dt.Rows.Count; nIndex++)
    {
        StringBuilder sQuery = new StringBuilder();
        StringBuilder sSql = new StringBuilder();
        StringBuilder sWhere = new StringBuilder();
        bool bIsNotColumn;

        foreach (DataColumn dc in dt.PrimaryKey)
        {
            if (dc.AllowDBNull)
                continue;
            
            sColumnName = dc.ColumnName;
            sColumnType = TypeCnv(dc.DataType.ToString());
            switch (sColumnType)
            {
                case "D":
                    if (dt.Rows[nIndex][sColumnName, DataRowVersion.Original].ToString().Trim().Length == 0)
                        sColumnData = "";
                    else
                        sColumnData = string.Format("{0:yyyy/MM/dd HH:mm:ss}", (DateTime)dt.Rows[nIndex][sColumnName, DataRowVersion.Original]);
                    break;
                case "B":
                    sColumnData = (bool)dt.Rows[nIndex][sColumnName, DataRowVersion.Original] ? "1" : "0";
                    break;
                default:
                    sColumnData = dt.Rows[nIndex][sColumnName, DataRowVersion.Original].ToString();
                    break;
            }

            if (sWhere.Length == 0)
                sWhere.Append("WHERE ");
            else
                sWhere.Append("AND ");

            sWhere.AppendFormat(" {0} = {1} ", sColumnName, DataCnv(sColumnType, sColumnData));
        }

        foreach (DataColumn dc in dt.Columns)
        {
            sColumnName = dc.ColumnName;
            sColumnType = TypeCnv(dc.DataType.ToString());

            //이전 값이랑 변동이 없으면 쿼리에서 해당 필드는 제외
            if (dt.Rows[nIndex][sColumnName, DataRowVersion.Current].ToString() ==
                dt.Rows[nIndex][sColumnName, DataRowVersion.Original].ToString())
                continue;
            else
                bIsNotUpdate = true;

            switch (sColumnType)
            {
                case "D":
                    if (dt.Rows[nIndex][sColumnName].ToString().Trim().Length == 0)
                        sColumnData = "";
                    else
                        sColumnData = string.Format("{0:yyyy/MM/dd HH:mm:ss}", (DateTime)dt.Rows[nIndex][sColumnName]);
                    break;
                case "B":
                    sColumnData = (bool)dt.Rows[nIndex][sColumnName] ? "1" : "0";
                    break;
                case "F":
                     byte [] bColumnData = (byte[])dt.Rows[nIndex][sColumnName];

                     sColumnData = DataCnv("S", Encoding.ASCII.GetString(bColumnData));
 
                    break;
                default:
                    sColumnData = dt.Rows[nIndex][sColumnName].ToString();
                    break;
            }

            bIsNotColumn = false;
            foreach (string sNotColumn in psDisplay)
            {
                if (sNotColumn.Equals(sColumnName))
                {
                    bIsNotColumn = true;
                    break;
                }
            }
            if (bIsNotColumn) continue;

            if (sSql.Length != 0)
                sSql.Append(", ");

            if (CisString.Empty(sColumnData)) 
                sSql.AppendFormat(" {0} = null ", sColumnName); 
            else if(sColumnType == "F")
                sSql.AppendFormat(" {0} = CONVERT(VARBINARY(MAX), {1} , 0)", sColumnName,  sColumnData);
            else
                sSql.AppendFormat(" {0} = {1} ", sColumnName, DataCnv(sColumnType, sColumnData));
        }

        sQuery.AppendFormat("UPDATE {0} SET {1} {2} ", dt.TableName, sSql.ToString(), sWhere.ToString());
        if (bIsNotUpdate && !CisString.Empty(sSql.ToString()))
            arSqls.Add(sQuery.ToString());
    }
    return;
}




DataRow 의 데이터를 Delete Sql 쿼리문(DataRow to Sql Query)으로 변환 (하단에 DataCnv, TypeCnv 메소드 사용)

/// <summary>
/// 데이터 셋의 특정 레코드를 Delete SQL 문장으로 생성
/// </summary>
/// <param name="arSqls">sql문이 담길 객체</param>
/// <param name="dt">데이터넷</param>
public void DeleteDataSetToSql(ref ArrayList arSqls, ref DataTable dt)
{
    string sColumnName;
    string sColumnType;
    string sColumnData;

    for (int nIndex = 0; nIndex < dt.Rows.Count; nIndex++)
    {
        StringBuilder sQuery = new StringBuilder();
        StringBuilder sWhere = new StringBuilder();

        foreach (DataColumn dc in dt.PrimaryKey)
        {
            if (dc.AllowDBNull)
                continue;

            sColumnName = dc.ColumnName;
            sColumnType = TypeCnv(dc.DataType.ToString());
            sColumnData = dt.Rows[nIndex][sColumnName, DataRowVersion.Original].ToString();

            if (sWhere.Length == 0)
                sWhere.Append("WHERE ");
            else
                sWhere.Append("AND ");

            if (sColumnType.Equals("D"))
                sWhere.AppendFormat(" {0} = {1} ", sColumnName, DataCnv(sColumnType, CisString.cisDel_Char(DateTime.Parse(sColumnData).ToShortDateString(), "-", "/")));
            else
                sWhere.AppendFormat(" {0} = {1} ", sColumnName, DataCnv(sColumnType, sColumnData));
        }
        sQuery.AppendFormat("DELETE FROM {0} {1} ", dt.TableName, sWhere.ToString());
        arSqls.Add(sQuery.ToString());
    }
    return;
}

DataType에 따라 Sql 문의 컬럼부분을 만들기 위한 DataCnv 함수

      /// <summary>
      /// 내부 변수형에 따라 기본값, ' '등을 설정
      /// </summary>
      /// <param name="sType">내부 변수형</param>
      /// <param name="sDatum">변수값</param>
      /// <returns>변경된 변수값</returns>
      public string DataCnv(string sType, string sDatum)
      {
          string sReturnData = "";
          sType = sType.ToUpper();

 
          if (sType.Substring(0, 1).Equals("B"))
          {
              sReturnData = string.Format("CONVERT(BIT, {0})", sDatum);
          }
          if (sType.Substring(0, 1).Equals("I"))
          {
              if (sDatum == "")
                  sReturnData = "0";
              else
                  sReturnData = sDatum.Replace(",","");
          }

          if (sType.Substring(0, 1).Equals("F") || sType.Substring(0, 1).Equals("C"))
          {
              if (sDatum == "")
                  sReturnData = "0.0";
              else
                  sReturnData = sDatum.Replace(",", "");
          }

          if (sType.Substring(0, 1).Equals("S") || sType.Substring(0, 1).Equals("M"))
          {
              sDatum.TrimEnd();

              StringBuilder tempStr = new StringBuilder(sDatum);
              tempStr.Replace(@"'", @"''", 0, sDatum.Length);

              sReturnData = "'" + tempStr + @"'";
 
          }

          if (sType.Substring(0, 1).Equals("D"))
          {
              sDatum =  sDatum.Replace("/","").Replace("-","").Replace(":","").Replace(" ","");
              if (sDatum.Trim().Length < 8)
                  sReturnData = "Null";
              else
              {
                  if (sDatum.Trim().Length == 6)
                  {
                      if (sDatum.Substring(0, 1).Equals("9"))
                          sDatum = "19" + sDatum;
                      else
                          sDatum = "20" + sDatum;
                  }

                  sDatum = sDatum + "000000";

                  if (sDatum.Length >= 14)
                      sDatum = sDatum.Substring(0, 14);


                  int nDataCnt, nSpecCnt;
                  StringBuilder tempRet = new StringBuilder();

                  nSpecCnt = nDataCnt = 0;

                  string sSpec ="9999/99/99 99:99:99";

                  while (true)
                  {
                      if ((nSpecCnt >= sSpec.Length) || (nDataCnt >= sDatum.Length))
                          sDatum = tempRet.ToString();

                      if (sSpec.Substring(nSpecCnt, 1) == "9")
                      {
                          tempRet.Append(sDatum.Substring(nDataCnt, 1));
                          nSpecCnt++;
                          nDataCnt++;
                      }
                      else
                      {
                          tempRet.Append(sSpec.Substring(nSpecCnt, 1));
                          nSpecCnt++;
                      }
                  }

                  sReturnData = "'" + sDatum.Trim() + "'";
 
              }
          }
          return sReturnData + " ";
      }



DataType에 따라 Sql 문의 컬럼부분을 만들기 위한 TypeCnv 함수

/// <summary>
/// 닷넷의 변수형에 따라 내부 사용할 변수형으로 변경
/// ===============================
///   C#데이터형	DB형 
/// ===============================
/// System.String	varchar
///	System.Int32	int
///	System.Decimal	numeric
///	System.DateTime	DateTime
///	System.Double	float
/// ===============================
/// </summary>
/// <param name="sType">변수형</param>
/// <returns>내부 사용 변수형</returns>
/// <remark>
/// <list type="bullet">
///   <item><description>S : System.String</description></item>
///   <item><description>I : System.Int32, System.Int64, System.UInt32, System.UInt64, System.Single</description></item>
///   <item><description>C : System.Double, System.Float,System.Decimal</description></item>
///   <item><description>D : SystemDateTime</description></item>
///   <item><description>  : 그외(Tmp컬럼 등..)</description></item>
/// </list>
/// </remark>
public string TypeCnv(string sType)
{
    string sReturnData = "";

    if (sType.Equals("System.String"))
        sReturnData = "S";
    else if (sType.Equals("System.Int32") || sType.Equals("System.Int64")
            || sType.Equals("System.UInt32") || sType.Equals("System.UInt64")
            || sType.Equals("System.Single") || sType.Equals("System.Byte")
            || sType.Equals("System.Int16") || sType.ToLower().Equals("System.tinyint"))
        sReturnData = "I";
    else if (sType.Equals("System.Double") || sType.Equals("System.Float") || sType.Equals("System.Decimal"))
        sReturnData = "C";
    else if (sType.Equals("System.DateTime"))
        sReturnData = "D";
    else if (sType.Equals("System.Boolean"))
        sReturnData = "B";
    else if (sType.Equals("System.Byte[]"))
        sReturnData = "F";
    else
        sReturnData = " "; //임시 컬럼용

    return sReturnData;
}

DataReader -> DataTable(DataReader to DataTable) 변환 함수

/// <summary>
      /// DataReader에서 DataTable 추출
      /// </summary>
      /// <param name="reader">대상 DataReader</param>
      /// <returns>결과 DataTable</returns>
      public DataTable GetTablefromDataReader(SqlDataReader reader)
      {
          DataTable table = reader.GetSchemaTable();
          DataTable dt = new System.Data.DataTable();
          DataColumn dc;
          DataRow row;
          ArrayList aList = new ArrayList();

          for (int i = 0; i < table.Rows.Count; i++)
          {
              dc = new DataColumn();

              if (!dt.Columns.Contains(table.Rows[i]["ColumnName"].ToString()))
              {
                  dc.ColumnName = table.Rows[i]["ColumnName"].ToString();
                  dc.Unique = Convert.ToBoolean(table.Rows[i]["IsUnique"]);
                  dc.AllowDBNull = Convert.ToBoolean(table.Rows[i]["AllowDBNull"]);
                  dc.ReadOnly = Convert.ToBoolean(table.Rows[i]["IsReadOnly"]);
                  aList.Add(dc.ColumnName);
                  dt.Columns.Add(dc);
              }
          }

          while (reader.Read())
          {
              row = dt.NewRow();
              for (int i = 0; i < aList.Count; i++)
              {
                  row[((System.String)aList[i])] = reader[(System.String)aList[i]];
              }
              dt.Rows.Add(row);
          }

          return dt;
      }

DataTable Cell의 현재값과 이전값 동기화 함수

/// <summary>
/// DataTable안 현재/이전 값 동기화 
/// </summary>
/// <param name="dt">해당 데이터 테이블</param>
/// <param name="psDisplay">비교할 값</param>
public void GetChangedFieldRow(ref DataTable dt, params string[] psDisplay)
{
    string sBeforeValue = "";
    string sAfterValue = "";
    int nCol = dt.Columns.Count;
    
        foreach (DataRow dr in dt.Rows)
        {
            int nDiffCnt = 0;
            for (int i = 0; i < nCol; i++)
            {
                if (psDisplay.Contains(dt.Columns[i].Caption))
                    continue;

                sBeforeValue = dr[i, DataRowVersion.Original].ToString();
                sAfterValue = dr[i, DataRowVersion.Current].ToString();
                if (sBeforeValue != sAfterValue)
                    nDiffCnt++;
            }
            if (nDiffCnt == 0)
                dr.Delete();
        }

        dt.AcceptChanges();
}

댓글 남기기