Beispiel #1
0
        /// <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());
        }
Beispiel #2
0
        /// <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));
        }
Beispiel #5
0
        /// <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));
        }
Beispiel #7
0
        /// <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());
        }
Beispiel #8
0
        /// <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());
        }
Beispiel #9
0
        /// <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());
        }
Beispiel #10
0
        /// <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));
        }