/// <summary>
/// 由vo中取得更新字串
/// </summary>
/// <param name="myVO"></param>
/// <returns>string</returns>
public static string GetUpdateString(BaseVO ObjVO)
{
Type objType = ObjVO.GetType();
string QueryString = string.Empty;
List <string> UpColNames = ObjVO.GetPropertyNames;
foreach (string ColName in UpColNames)
{
QueryString = QueryString + " , \"" + ColName + "\" =: " + ColName;
}
if (!string.IsNullOrEmpty(ObjVO.UpdateString.Trim()))
{
string tmpColName = ObjVO.UpdateString.Trim();
QueryString = QueryString + ", " + tmpColName;
}
if (QueryString.Length != 0)
{
QueryString = "SET " + QueryString.Substring(2);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat(" Update {0} ", objType.InvokeMember("TargetTable", BindingFlags.GetProperty, null, ObjVO, new object[] { }).ToString());
sql.Append(QueryString);
return(sql.ToString());
}
/// <summary>
/// 傳入VO，自動產生where條件
/// </summary>
/// <param name="ObjVO"></param>
/// <returns>string</returns>
public static string GetQueryString(BaseVO ObjVO)
{
Type objType = ObjVO.GetType();
StringBuilder QueryString = new StringBuilder(" WHERE 1 = 1");
List <string> ColNames = ObjVO.GetPropertyNames;
foreach (string ColName in ColNames)
{
QueryString.Append(" AND \"" + ColName + "\" =: " + ColName);
}
if (!string.IsNullOrEmpty(ObjVO.ConditionString.Trim()))
{
QueryString.Append(" " + ObjVO.ConditionString.Trim() + " ");
}
if (!string.IsNullOrEmpty(ObjVO.OrderBy.Trim()))
{
QueryString.Append(" " + ObjVO.OrderBy.Trim() + " ");
}
return(QueryString.ToString());
}
/// <summary>
/// 使用VO內資料及WVO條件進行更新
/// </summary>
/// <param name="vo"></param>
/// <param name="wvo"></param>
/// <returns></returns>
public int Update(BaseVO vo, BaseVO wvo)
{
TaSqlCommand objCommand = new TaSqlCommand();
SqlHelper.SetParametersByVO(vo, wvo, objCommand.DbCmd);
return(this.Execute(SqlHelper.GetUpdateString(vo, wvo), objCommand));
}
/// <summary>
/// 使用VO取出DataTable
/// </summary>
/// <param name="wvo"></param>
/// <returns></returns>
public DataTable QueryForDataTable(BaseVO wvo)
{
TaSqlCommand objCommand = new TaSqlCommand();
SqlHelper.SetParametersByVO(wvo, objCommand.DbCmd);
return(this.QueryForDataTable(SqlHelper.GetSelectString(wvo), objCommand));
}
/// <summary>
/// 傳入VO與OracleCommand，自動產生OracleCommand的Parameter參數
/// </summary>
/// <param name="ObjVO"></param>
/// <param name="objCommand"></param>
public static void SetParametersByVO(BaseVO ObjVO, SqlCommand objCommand)
{
Type objType = ObjVO.GetType();
List <string> ColNames = ObjVO.GetPropertyNames;
foreach (string ColName in ColNames)
{
objCommand.Parameters.Add(":" + ColName, objType.InvokeMember("GetPropertyValue", BindingFlags.InvokeMethod, null, ObjVO, new object[] { ColName }) ?? DBNull.Value);
}
}
/// <summary>
/// 使用VO內資料及指定欄位條件進行更新
/// </summary>
/// <param name="vo"></param>
/// <param name="pkField"></param>
/// <param name="pkVal"></param>
/// <returns></returns>
public int Update(BaseVO vo, string pkField, object pkVal)
{
string sql = SqlHelper.GetUpdateString(vo) + " where " + pkField + " = :F_" + pkField;
TaSqlCommand objCommand = new TaSqlCommand();
SqlHelper.SetParametersByVO(vo, objCommand.DbCmd);
objCommand.DbCmd.Parameters.AddWithValue(":F_" + pkField, pkVal);
return(this.Execute(sql, objCommand));
}
/// <summary>
/// 由vo中取得查詢字串
/// </summary>
/// <param name="ObjVO"></param>
/// <returns></returns>
public static string GetSelectString(BaseVO ObjVO)
{
Type objType = ObjVO.GetType();
StringBuilder QueryString = new StringBuilder();
QueryString.AppendFormat("SELECT * FROM {0} ", objType.InvokeMember("TargetTable", BindingFlags.GetProperty, null, ObjVO, new object[] { }).ToString());
QueryString.Append(GetQueryString(ObjVO));
return(QueryString.ToString());
}
/// <summary>
/// 由vo中取得更新字串
/// </summary>
/// <param name="ObjVO"></param>
/// <param name="ObjVObyWhere"></param>
/// <returns></returns>
public static string GetUpdateString(BaseVO ObjVO, BaseVO ObjVObyWhere)
{
//更新欄位
string QueryString = string.Empty;
Type objType = ObjVO.GetType();
List <string> UpColNames = ObjVO.GetPropertyNames;
foreach (string ColName in UpColNames)
{
QueryString += " , \"" + ColName + "\" =: " + ColName;
}
if (!string.IsNullOrEmpty(ObjVO.UpdateString))
{
string tmpColName = ObjVO.UpdateString.Trim();
QueryString += ", " + tmpColName;
}
if (QueryString.Length != 0)
{
QueryString = "SET " + QueryString.Substring(2);
}
StringBuilder sql = new StringBuilder();
sql.AppendFormat(" UPDATE {0} ", objType.InvokeMember("TargetTable", BindingFlags.GetProperty, null, ObjVO, new object[] { }).ToString());
//條件式
QueryString += " WHERE 1 = 1";
objType = ObjVObyWhere.GetType();
List <string> ColNames = ObjVObyWhere.GetPropertyNames;
foreach (string ColName in ColNames)
{
QueryString += " AND \"" + ColName + "\"= :F#_" + ColName;
}
if (!string.IsNullOrEmpty(ObjVObyWhere.ConditionString))
{
QueryString += " " + ObjVObyWhere.ConditionString.Trim() + " ";
}
//排序
if (!string.IsNullOrEmpty(ObjVObyWhere.OrderBy.Trim()))
{
QueryString += " " + ObjVObyWhere.OrderBy.Trim() + " ";
}
sql.Append(QueryString);
return(sql.ToString());
}
/// <summary>
/// 由vo中取得insert字串
/// </summary>
/// <param name="ObjVO"></param>
/// <returns>string</returns>
public static string GetInsertString(BaseVO ObjVO)
{
Type objType = ObjVO.GetType();
StringBuilder SqlColDesc = new StringBuilder();
StringBuilder SqlValuelDesc = new StringBuilder();
StringBuilder SqlDesc = new StringBuilder();
List <string> ColNames = ObjVO.GetPropertyNames;
foreach (string ColName in ColNames)
{
SqlColDesc.AppendFormat(",\"{0}\"", ColName);
SqlValuelDesc.AppendFormat(",:{0}", ColName);
}
SqlDesc.AppendFormat("INSERT INTO {0} (", objType.InvokeMember("TargetTable", BindingFlags.GetProperty, null, ObjVO, new object[] { }).ToString());
SqlDesc.AppendFormat("{0}) VALUES ({1})", SqlColDesc.ToString().Substring(1), SqlValuelDesc.ToString().Substring(1));
return(SqlDesc.ToString());
}
/// <summary>
/// 使用vo取出DataTable
/// 取出DataTable轉成List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="wvo"></param>
/// <returns></returns>
public List <T> QueryForList <T>(BaseVO wvo) where T : BaseVO, new()
{
DataTable dt = QueryForDataTable(wvo);
return(DataTableToList <T>(dt));
}