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