/// <summary>
/// 產生DTO所有欄位的SQL字串，用於OUTPUT INSERT的語法
/// </summary>
/// <param name="dtoBase">要使用INSERT語法的DTO物件</param>
/// <param name="strOutputField">要OUTPUT的欄位</param>
/// <returns>INSERT欄位的SQL字串</returns>
protected string CreateInsertPropertySQL(DataTransferObject.DataTransferObjectBase dtoBase, string strOutputField)
{
string _strSQL = "";
string _strFrontSQL = "";
string _strBehindSQL = "";
foreach (PropertyInfo _property in dtoBase.GetUpdatePropertyList())
{
if (_property.Name != "RowStatus")
{
if (_strFrontSQL == "")
{
_strFrontSQL = " [" + _property.Name + "]";
}
else if (_strFrontSQL != "")
{
_strFrontSQL = _strFrontSQL + " , [" + _property.Name + "]";
}
if (_strBehindSQL == "")
{
_strBehindSQL = " @" + _property.Name;
}
else if (_strBehindSQL != "")
{
_strBehindSQL = _strBehindSQL + ", @" + _property.Name;
}
}
}
_strSQL = " ( " + _strFrontSQL + " ) OUTPUT INSERTED." + strOutputField + " VALUES ( " + _strBehindSQL + " ) ";
return(_strSQL);
}
/// <summary>
/// 在SqlCommand加入UPDATE用的參數
/// </summary>
/// <param name="dtoBase">DTO物件</param>
/// <param name="sqlCmd">要加入參數的SqlCommand</param>
protected void FillSqlCommmandParameterForUpdate(DataTransferObject.DataTransferObjectBase dtoBase, ref SqlCommand sqlCmd)
{
foreach (PropertyInfo _property in dtoBase.GetUpdatePropertyList())
{
if (_property.Name != "RowStatus")
{
sqlCmd.Parameters.AddWithValue("@" + _property.Name + FOR_UPDATE_PARAMETER_TRAILER, _property.GetValue(dtoBase, null));
}
}
foreach (PropertyInfo _property in dtoBase.GetAccumulatePropertyList())
{
if (_property.Name != "RowStatus")
{
sqlCmd.Parameters.AddWithValue("@" + _property.Name + FOR_UPDATE_PARAMETER_TRAILER, _property.GetValue(dtoBase, null));
}
}
}
/// <summary>
/// 產生DTO更新欄位的SQL字串，用於UPDATE的語法
/// </summary>
/// <param name="dtoBase">要使用UPDATE語法的DTO物件</param>
/// <returns>UPDATE欄位的SQL字串</returns>
protected string CreateUpdatePropertySQL(DataTransferObject.DataTransferObjectBase dtoBase)
{
string _strSQL = "";
foreach (PropertyInfo _property in dtoBase.GetUpdatePropertyList())
{
if (_property.Name != "RowStatus")
{
if (_strSQL != "")
{
_strSQL = _strSQL + " , ";
}
_strSQL = _strSQL + _property.Name + "=" + "@" + _property.Name + FOR_UPDATE_PARAMETER_TRAILER;
}
}
foreach (PropertyInfo _property in dtoBase.GetAccumulatePropertyList())
{
if (_property.Name != "RowStatus")
{
if (_strSQL != "")
{
_strSQL = _strSQL + " , ";
}
switch (_property.PropertyType.Name)
{
case "Int32":
case "Double":
case "Decimal":
case "String":
_strSQL = _strSQL + _property.Name + "=" + _property.Name + "+" + "@" + _property.Name + FOR_UPDATE_PARAMETER_TRAILER;
break;
default:
_strSQL = _strSQL + _property.Name + "=" + "@" + _property.Name + FOR_UPDATE_PARAMETER_TRAILER;
break;
}
}
}
return(_strSQL);
}
/// <summary>
/// 產生DTO所有欄位的SQL字串，用於SELECT的語法
/// </summary>
/// <param name="dtoBase">要使用SELECT語法的DTO</param>
/// <param name="strTableSymbol">別名</param>
/// <returns>SELECT欄位的SQL字串</returns>
protected string CreateSelectPropertySQL(DataTransferObject.DataTransferObjectBase dtoBase, string strTableSymbol)
{
string _strSQL = String.Empty;
PropertyInfo[] _properties = dtoBase.GetType().GetProperties();
foreach (PropertyInfo _property in _properties)
{
if (_property.Name != "RowStatus")
{
if (String.IsNullOrWhiteSpace(_strSQL) == false)
{
_strSQL = _strSQL + " , ";
}
_strSQL = _strSQL + (strTableSymbol == String.Empty ? String.Empty : strTableSymbol + ".") + "[" + _property.Name + "]";
}
}
return(_strSQL);
}
/// <summary>
/// 產生DTO所有欄位的SQL字串，用於SELECT的語法
/// </summary>
/// <param name="dtoBase">要使用SELECT語法的DTO</param>
/// <returns>SELECT欄位的SQL字串</returns>
protected string CreateSelectPropertySQL(DataTransferObject.DataTransferObjectBase dtoBase)
{
return(this.CreateSelectPropertySQL(dtoBase, String.Empty));
}
