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