/// <summary>
/// DataPack�� �Ķ���ͷ� �Ƽ� Insert ���� ����� ����.
/// �⺻���� SqlServer �� ���.
/// </summary>
/// <param name="dataPack">�Ķ���� ������ �ִ� Pack</param>
/// <param name="dataTable">Insert�� DB ���̺� ��</param>
/// <param name="skipNullProperty">DataPack ������ Null �� ��� Skip</param>
/// <returns>SQL Insert��</returns>
public static string BuildInsertQuery(DataPack dataPack, string dataTable, bool skipNullProperty)
{
return BuildInsertQuery(dataPack, dataTable, skipNullProperty, TargetDb.SqlServer);
}
/// <summary>
/// DataPack�� �Ķ���ͷ� �Ƽ� Update ���� ����� ����.
/// </summary>
/// <param name="dataPack">�Ķ���� ������ �ִ� Pack</param>
/// <param name="dataTable">Update�� DB ���̺� ��</param>
/// <param name="whereCondition">Update�� ����� Where ������</param>
/// <param name="skipNullProperty">DataPack ������ Null �� ��� Skip</param>
/// <param name="targetDb">Sql, Oracle, OleDb �߿� ����</param>
/// <returns>SQL Update��</returns>
public static string BuildUpdateQuery(DataPack dataPack, string dataTable, string whereCondition, bool skipNullProperty, TargetDb targetDb)
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE ").Append(dataTable).Append(" SET\r\n");
ITypedList itl = dataPack as ITypedList;
foreach( PropertyDescriptor pd in itl.GetItemProperties(null) )
{
object pdValue = pd.GetValue( dataPack );
if ( skipNullProperty == true )
{
switch ( pd.PropertyType.Name )
{
case "DateTime":
DateTime dt = Convert.ToDateTime( pdValue );
if ( dt == DateTime.MinValue )
{
continue;
}
break;
default:
if ( pdValue.ToString() == string.Empty )
{
continue;
}
break;
}
}
switch (targetDb)
{
case TargetDb.SqlServer:
sb.AppendFormat("{0} = @{1}", pd.Name, pd.Name);
break;
case TargetDb.OleDb:
sb.AppendFormat("{0} = ?", pd.Name);
break;
case TargetDb.Oracle:
sb.AppendFormat("{0} = :{1}", pd.Name, pd.Name);
break;
}
sb.Append(", \r\n");
}
sb.Remove(sb.Length - 4, 2);
sb.Append("WHERE ").Append(whereCondition);
return sb.ToString();
}
/// <summary>
/// DataPack�� �Ķ���ͷ� �Ƽ� Insert ���� ����� ����.
/// �⺻���� SqlServer �� �������, DataPack ������ Null �� ��� Skip�ؼ� �����.
/// </summary>
/// <param name="dataPack">�Ķ���� ������ �ִ� Pack</param>
/// <param name="dataTable">Insert�� DB ���̺� ��</param>
/// <returns>SQL Insert��</returns>
public static string BuildInsertQuery(DataPack dataPack, string dataTable)
{
return BuildInsertQuery(dataPack, dataTable, true, TargetDb.SqlServer);
}
/// <summary>
/// DataPack�� �Ķ���ͷ� �Ƽ� Update ���� ����� ����.
/// DataPack ������ Null �� ��� Skip�ؼ� �����.
/// </summary>
/// <param name="dataPack">�Ķ���� ������ �ִ� Pack</param>
/// <param name="dataTable">Update�� DB ���̺� ��</param>
/// <param name="whereCondition">Update�� ����� Where ������</param>
/// <param name="targetDb">Sql, Oracle, OleDb �߿� ����</param>
/// <returns>SQL Update��</returns>
public static string BuildUpdateQuery(DataPack dataPack, string dataTable, string whereCondition, TargetDb targetDb)
{
return BuildUpdateQuery(dataPack, dataTable, whereCondition, true, targetDb);
}
/// <summary>
/// DataPack�� �Ķ���ͷ� �Ƽ� Update ���� ����� ����.
/// �⺻���� SqlServer �� �������,
/// </summary>
/// <param name="dataPack">�Ķ���� ������ �ִ� Pack</param>
/// <param name="dataTable">Update�� DB ���̺� ��</param>
/// <param name="whereCondition">Update�� ����� Where ������</param>
/// <param name="skipNullProperty">DataPack ������ Null �� ��� Skip</param>
/// <returns>SQL Update��</returns>
public static string BuildUpdateQuery(DataPack dataPack, string dataTable, string whereCondition, bool skipNullProperty)
{
return BuildUpdateQuery(dataPack, dataTable, whereCondition, skipNullProperty, TargetDb.SqlServer);
}
/// <summary>
/// 데이터팩에서 데이터 추출
/// </summary>
/// <param name="ps_source"></param>
/// <param name="po_dp"></param>
/// <returns></returns>
public static string ExtractDataInfo(string ps_source, DataPack po_dp)
{
string ls_return = ps_source + "<br><br>";
for (int i = 0; i < po_dp.Properties.Count; i++)
{
ls_return += "@" + po_dp.Properties[i].Name + "=" + po_dp[po_dp.Properties[i].Name].ToString() + "<br>";
}
return ls_return;
}
/// <summary>
/// DataTable ���� DataRow�� Array ���·� ��� �ִ�.
/// ���� DataPack[] ���·� ������.
/// </summary>
/// <param name="table">DataTable ��ü</param>
/// <returns>DataPack[] ��ü</returns>
public static DataPack[] FromDataTable( DataTable table )
{
if ( table == null )
{
throw new ArgumentNullException();
}
if ( table.Rows.Count < 1 )
{
return null;
}
DataPack [] dataPacks = new DataPack[ table.Rows.Count ];
for ( int i = 0; i < table.Rows.Count; i ++ )
{
dataPacks[ i ] = DataPack.FromDataRow( table.Rows[ i ] );
}
return dataPacks;
}
/// <summary>
/// DataPack(conditionPack)�� �Ķ���ͷ� �Ƽ� Select ���� ����� ����.
/// DataPack(conditionPack)�� ���� '%' �� �ִ� ��� Select ������ Like �˻�, �ܴ� '=' �˻��� �����Ѵ�.
/// DataPack(conditionPack) ������ Null �� ��� Skip
/// </summary>
/// <param name="selectQuery">Select ������</param>
/// <param name="conditionPack">������ ���� DataPack</param>
/// <param name="targetDb">Sql, Oracle, OleDb �߿� ����</param>
/// <returns>SQL Select��</returns>
public static string BuildSelectQuery(string selectQuery, DataPack conditionPack, TargetDb targetDb)
{
return BuildSelectQuery(selectQuery, conditionPack, true, targetDb);
}
/// <summary>
/// DataPack(conditionPack)�� �Ķ���ͷ� �Ƽ� Select ���� ����� ����.
/// DataPack(conditionPack)�� ���� '%' �� �ִ� ��� Select ������ Like �˻�, �ܴ� '=' �˻��� �����Ѵ�.
/// �⺻���� SqlServer �� �������
/// </summary>
/// <param name="selectQuery">Select ������</param>
/// <param name="conditionPack">������ ���� DataPack</param>
/// <param name="skipNullProperty"></param>
/// <returns>SQL Select��</returns>
public static string BuildSelectQuery(string selectQuery, DataPack conditionPack, bool skipNullProperty)
{
return BuildSelectQuery(selectQuery, conditionPack, skipNullProperty, TargetDb.SqlServer);
}
/// <summary>
/// 메일발송 - DataPack
/// </summary>
/// <param name="lo_dp"></param>
/// <returns></returns>
public string SendMail(DataPack lo_dp)
{
try
{
this.from = String.IsNullOrEmpty(lo_dp["From"].ToString()) ? "" : lo_dp["From"].ToString();
this.to = String.IsNullOrEmpty(lo_dp["To"].ToString()) ? "" : lo_dp["To"].ToString();
this.subject = String.IsNullOrEmpty(lo_dp["Subject"].ToString()) ? "NO TITLE" : lo_dp["Subject"].ToString();
this.body = String.IsNullOrEmpty(lo_dp["Body"].ToString()) ? "NO CONTENTS" : lo_dp["Body"].ToString();
this.isBodyHtml = String.IsNullOrEmpty(lo_dp["IsBodyHtml"].ToString()) ? false : Convert.ToBoolean(lo_dp["IsBodyHtml"].ToString());
MailMessage msg = new MailMessage();
msg.From = new MailAddress(from.Trim());
msg.To.Add(new MailAddress(to.Trim()));
msg.Subject = subject.Trim();
msg.Body = body.Trim();
msg.BodyEncoding = BodyEncoding;
msg.IsBodyHtml = isBodyHtml;
SmtpClient sc = new SmtpClient();
sc.Host = hostName;
sc.Send(msg);
//로깅
if (WebConfig.SMFramework["Mail.Logging"].ToUpper() == "TRUE")
{
_mailLog = new ErrorLog("Mail", "SMFramework", WebConfig.SMFramework["Mail.LogFile"]);
DataPack lo_dp_header = new DataPack();
string[] keys = msg.Headers.AllKeys;
foreach (string s in keys)
{
lo_dp_header.AddProperty(s, typeof(string), msg.Headers[s]);
}
lo_dp_header.AddProperty("host", typeof(string), hostName);
lo_dp_header.AddProperty("subject", typeof(string), subject);
lo_dp_header.AddProperty("isBodyHtml", typeof(string), isBodyHtml.ToString());
lo_dp_header.AddProperty("bodyEncoding", typeof(string), msg.BodyEncoding.BodyName);
string ls_msg = Utility.ConversionUtil.ExtractDataInfo("--- SendMail Result ---", lo_dp_header);
ls_msg = ls_msg.Replace("<br>", "\r\n");
_mailLog.WriteLogMessage(ls_msg, System.Diagnostics.EventLogEntryType.Information);
}
}
catch (Exception e)
{
_mailLog.WriteLogMessage(e.Message, System.Diagnostics.EventLogEntryType.Error);
return e.Message;
}
return "OK";
}
/// <summary>
/// DataPack(conditionPack)�� �Ķ���ͷ� �Ƽ� Select ���� ����� ����.
/// DataPack(conditionPack)�� ���� '%' �� �ִ� ��� Select ������ Like �˻�, �ܴ� '=' �˻��� �����Ѵ�.
/// �⺻���� SqlServer �� �������, DataPack(conditionPack) ������ Null �� ���� Skip
/// </summary>
/// <param name="selectQuery">Select ������</param>
/// <param name="conditionPack">������ ���� DataPack</param>
/// <returns>SQL Select��</returns>
public static string BuildSelectQuery(string selectQuery, DataPack conditionPack)
{
return BuildSelectQuery(selectQuery, conditionPack, true, TargetDb.SqlServer);
}
/// <summary>
/// 메일발송
/// </summary>
/// <param name="ps_from"></param>
/// <param name="ps_to"></param>
/// <param name="ps_subject"></param>
/// <param name="ps_body"></param>
/// <param name="pb_is_body_html"></param>
/// <returns></returns>
public string SendMail(string ps_from, string ps_to, string ps_subject, string ps_body, bool pb_is_body_html)
{
DataPack lo_dp = new DataPack();
lo_dp.AddProperty("From", typeof(string), ps_from.Trim());
lo_dp.AddProperty("To", typeof(string), ps_to.Trim());
lo_dp.AddProperty("Subject", typeof(string), ps_subject.Trim());
lo_dp.AddProperty("Body", typeof(string), ps_body.Trim());
lo_dp.AddProperty("IsBodyHtml", typeof(string), pb_is_body_html.ToString());
return SendMail(lo_dp);
}
/// <summary>
/// �̹� �����ϴ� DataPack ���뿡 �Ǵٸ� DataPack�� �߰��Ѵ�.
/// </summary>
/// <param name="dataPack">���Ӱ� �߰��� DataPack</param>
public void Merge(DataPack dataPack)
{
ITypedList itl = this as ITypedList;
foreach( PropertyDescriptor pd in itl.GetItemProperties(null) )
{
pd.SetValue(this,dataPack[pd.Name]);
}
}
/// <summary>
/// �̹� �����ϴ� DataPack���κ��� ���� �����ؼ� ���ο� DataPack�� �����Ѵ�.
/// </summary>
/// <returns>�������� ������ ���ο� DataPack ��ü</returns>
public DataPack Copy()
{
DataPack dataPack = new DataPack();
ITypedList itl = this as ITypedList;
foreach( PropertyDescriptor pd in itl.GetItemProperties(null) )
{
dataPack.AddProperty(pd.Name, pd.PropertyType, pd.GetValue(this));
}
return dataPack;
}
/// <summary>
/// DataPack�� �Ķ���ͷ� �Ƽ� Insert ���� ����� ����.
/// DataPack ������ Null �� ��� Skip�ؼ� �����.
/// </summary>
/// <param name="dataPack">�Ķ���� ������ �ִ� Pack</param>
/// <param name="dataTable">Insert�� DB ���̺� ��</param>
/// <param name="targetDb">Sql, Oracle, OleDb �߿� ����</param>
/// <returns>SQL Insert��</returns>
public static string BuildInsertQuery(DataPack dataPack, string dataTable, TargetDb targetDb)
{
return BuildInsertQuery(dataPack, dataTable, true, targetDb);
}
/// <summary>
/// DataPack�� �Ķ���ͷ� �Ƽ� Select ���� ����� ����.
/// DataPack(conditionPack)�� ���� '%' �� �ִ� ��� Select ������ Like �˻�, �ܴ� '=' �˻��� �����Ѵ�.
/// </summary>
/// <param name="selectQuery">Select ������</param>
/// <param name="conditionPack">������ ���� DataPack</param>
/// <param name="skipNullProperty">DataPack ������ Null �� ��� Skip</param>
/// <param name="targetDb">Sql, Oracle, OleDb �߿� ����</param>
/// <returns>SQL Select��</returns>
public static string BuildSelectQuery(string selectQuery, DataPack conditionPack, bool skipNullProperty, TargetDb targetDb)
{
StringBuilder sb = new StringBuilder();
sb.Append(selectQuery);
bool whereClauseAdded = false;
ITypedList itl = conditionPack as ITypedList;
foreach( PropertyDescriptor pd in itl.GetItemProperties(null) )
{
object pdValue = pd.GetValue(conditionPack);
if ( skipNullProperty == true )
{
switch ( pd.PropertyType.Name )
{
case "DateTime":
DateTime dt = Convert.ToDateTime( pdValue );
if ( dt == DateTime.MinValue )
{
continue;
}
break;
default:
if ( pdValue.ToString() == string.Empty )
{
continue;
}
break;
}
}
if (!whereClauseAdded)
{
sb.Append(" WHERE ");
whereClauseAdded = true;
}
string conditionOperator = (pdValue.ToString().IndexOf("%") < 0) ? " = " : " LIKE ";
switch (targetDb)
{
case TargetDb.SqlServer:
sb.AppendFormat("{0} {1} @{2} AND ", pd.Name, conditionOperator, pd.Name);
break;
case TargetDb.OleDb:
sb.AppendFormat("{0} {1} ? AND ", pd.Name, conditionOperator);
break;
case TargetDb.Oracle:
sb.AppendFormat("{0} {1} :{2} AND ", pd.Name, conditionOperator, pd.Name);
break;
}
}
if (whereClauseAdded)
{
sb.Remove(sb.Length - 5, 4);
}
return sb.ToString();
}
/// <summary>
/// DataPack�� �Ķ���ͷ� �Ƽ� Insert ���� ����� ����.
/// </summary>
/// <param name="dataPack">�Ķ���� ������ �ִ� Pack</param>
/// <param name="dataTable">Insert�� DB ���̺� ��</param>
/// <param name="skipNullProperty">DataPack ������ Null �� ��� Skip</param>
/// <param name="targetDb">Sql, Oracle, OleDb �߿� ����</param>
/// <returns>SQL Insert��</returns>
public static string BuildInsertQuery(DataPack dataPack, string dataTable, bool skipNullProperty, TargetDb targetDb)
{
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
sb1.Append("INSERT INTO ").Append(dataTable).Append(" (\r\n");
ITypedList itl = dataPack as ITypedList;
foreach( PropertyDescriptor pd in itl.GetItemProperties(null) )
{
object pdValue = pd.GetValue( dataPack );
if ( skipNullProperty == true )
{
switch ( pd.PropertyType.Name )
{
case "DateTime":
DateTime dt = Convert.ToDateTime( pdValue );
if ( dt == DateTime.MinValue )
{
continue;
}
break;
default:
if ( pdValue.ToString() == string.Empty )
{
continue;
}
break;
}
}
sb1.Append(pd.Name).Append(", \r\n");
switch (targetDb)
{
case TargetDb.SqlServer:
sb2.AppendFormat("@{0}", pd.Name);
break;
case TargetDb.OleDb:
sb2.Append("?");
break;
case TargetDb.Oracle:
sb2.AppendFormat(":{0}", pd.Name);
break;
}
sb2.Append(", \r\n");
}
sb1.Remove(sb1.Length - 4, 2);
sb1.Append(") VALUES (\r\n");
sb2.Remove(sb2.Length - 4, 2);
sb2.Append(")");
return sb1.ToString() + sb2.ToString();
}
/// <summary>
/// DataPack�� �Ķ���ͷ� �Ƽ� Update ���� ����� ����.
/// �⺻���� SqlServer �� �������, DataPack ������ Null �� ��� Skip�ؼ� �����.
/// </summary>
/// <param name="dataPack">�Ķ���� ������ �ִ� Pack</param>
/// <param name="dataTable">Update�� DB ���̺� ��</param>
/// <param name="whereCondition">Update�� ����� Where ������</param>
/// <returns>SQL Update��</returns>
public static string BuildUpdateQuery(DataPack dataPack, string dataTable, string whereCondition)
{
return BuildUpdateQuery(dataPack, dataTable, whereCondition, true, TargetDb.SqlServer);
}
/// <summary>
/// 데이터팩에서 데이터 추출(SP 실행가능 파라미터 형식)
/// </summary>
/// <param name="ps_sp"></param>
/// <param name="po_dp"></param>
/// <returns></returns>
public static string ExtractDataParam(string ps_sp, DataPack po_dp)
{
string ls_return = "exec " + ps_sp + "<br><br>";
for (int i = 0; i < po_dp.Properties.Count; i++)
{
string ls_var = "@" + po_dp.Properties[i].Name;
string ls_value = po_dp[po_dp.Properties[i].Name].ToString();
ls_return += ls_var + "='" + ls_value + "',<br>";
}
return ls_return.Substring(0, ls_return.Length - 5) + ";";
}
/// <summary>
/// DataRow �κ��� DataPack ������ ���Ӱ� �����Ѵ�.
/// </summary>
/// <param name="dataRow">DataRow ��ü</param>
/// <returns>DataPack ��ü</returns>
public static DataPack FromDataRow(DataRow dataRow)
{
DataPack dataPack = new DataPack();
foreach(DataColumn col in dataRow.Table.Columns )
{
string colName = col.ColumnName;
dataPack.AddProperty(col.ColumnName,col.DataType,dataRow[col]);
}
return dataPack;
}