コード例 #1
0
ファイル: ListUtility.cs プロジェクト: radtek/vnr
        public static ParamaterModle ParseParamOracle(ListQueryModel model, string storeName)
        {
            string strQuery = "begin " + storeName + "(";
            List <OracleParameter> lstParas = new List <OracleParameter>();
            OracleParameter        para     = null;

            if (model.AdvanceFilters != null)
            {
                var selectExport = model.AdvanceFilters.Where(w => w.Member == "IsExport").FirstOrDefault();
                if (selectExport != null)
                {
                    if (selectExport.Value2 != null)
                    {
                        bool isExport = (bool)selectExport.Value2;
                        if (isExport)
                        {
                            model.PageSize = int.MaxValue;
                        }
                    }
                }

                foreach (var item in model.AdvanceFilters)
                {
                    if (item.Member != null && (item.Member.ToLower() == "IsExport".ToLower() ||
                                                item.Member == "ValueFields" || item.Member == "LoginUserID"))
                    {
                        continue;
                    }

                    Type paramType = item.MemberType;

                    strQuery          += ":" + item.Member + ", ";
                    para               = new OracleParameter();
                    para.ParameterName = item.Member;


                    FilterAttribute filterAtt  = model.Filters.Where(m => m.Member == item.Member).FirstOrDefault();
                    object          paramValue = item.Value2;
                    if (paramValue == null && filterAtt != null && filterAtt.Value != null)
                    {
                        paramValue = filterAtt.Value;
                    }
                    para.Value = paramValue;
                    if (paramType == typeof(Int32?) || paramType == typeof(Int32))
                    {
                        para.DbType       = System.Data.DbType.Int32;
                        para.OracleDbType = OracleDbType.Number;
                    }
                    //Tìm kiếm dạng Unicode, có dấu thay cho ký tự N trong Sql
                    if ((item.MemberType == typeof(string) && item.Member != "OrgStructureID") && paramValue != null)
                    {
                        //System.Text.UnicodeEncoding unicode = new System.Text.UnicodeEncoding();
                        para.DbType       = System.Data.DbType.String;
                        para.OracleDbType = OracleDbType.NVarChar;
                        para.Value        = paramValue;
                    }
                    //else
                    //{
                    //    if (item.MemberType == typeof(Int32?))
                    //    {
                    //        para.DbType = System.Data.DbType.Int32;
                    //    }
                    //    else if (item.MemberType == typeof(Double?))
                    //    {
                    //        para.DbType = System.Data.DbType.Double;
                    //    }
                    //}

                    if (paramValue == null || paramValue.ToString() == Guid.Empty.ToString())
                    {
                        para.Value = DBNull.Value;
                    }
                    lstParas.Add(para);
                }


                strQuery          += ":PageIndex";
                para               = new OracleParameter();
                para.ParameterName = "PageIndex";
                para.Value         = model.PageIndex == 0 ? 1 : model.PageIndex;
                para.DbType        = System.Data.DbType.Int32;
                para.OracleDbType  = OracleDbType.Number;
                lstParas.Add(para);

                strQuery          += ",:PageSize";
                para               = new OracleParameter();
                para.ParameterName = "PageSize";
                para.Value         = model.PageSize == 0 ? 50 : model.PageSize;
                para.DbType        = System.Data.DbType.Int32;
                lstParas.Add(para);
                if (CheckStoreNamePermisson(storeName))
                {
                    strQuery          += ",:UserName";
                    para               = new OracleParameter();
                    para.ParameterName = "UserName";
                    para.Value         = model.UserLogin;
                    para.DbType        = System.Data.DbType.String;
                    para.OracleDbType  = OracleDbType.NVarChar;
                    lstParas.Add(para);
                }
            }
            para = new OracleParameter();
            para.ParameterName = "R_Output";
            para.OracleDbType  = OracleDbType.Cursor;
            para.Direction     = System.Data.ParameterDirection.Output;
            lstParas.Add(para);
            ParamaterModle param = new ParamaterModle();

            param.Params   = lstParas.ToArray();
            param.SqlQuery = strQuery + ",:R_Output); end;";
            return(param);
        }
コード例 #2
0
ファイル: ListUtility.cs プロジェクト: radtek/vnr
        public static ParamaterModle ParseParamSQL(ListQueryModel model, string storeName)
        {
            string strQuery = storeName + " ";
            List <SqlParameter> lstParas = new List <SqlParameter>();
            SqlParameter        para     = null;
            var selectExport             = model.AdvanceFilters.Where(w => w.Member != null && w.Member.ToLower() == "IsExport".ToLower()).FirstOrDefault();

            if (selectExport != null)
            {
                if (selectExport.Value2 != null)
                {
                    bool isExport = (bool)selectExport.Value2;
                    if (isExport)
                    {
                        model.PageSize = 100000;
                    }
                }
            }

            foreach (var item in model.AdvanceFilters)
            {
                if (item.Member != null && (item.Member.ToLower() == "IsExport".ToLower() ||
                                            item.Member == "ValueFields" || item.Member == "LoginUserID"))
                {
                    continue;
                }

                Type paramType = item.MemberType;

                strQuery          += "@" + item.Member + ", ";
                para               = new SqlParameter();
                para.ParameterName = item.Member;
                FilterAttribute filterAtt  = model.Filters.Where(m => m.Member == item.Member).FirstOrDefault();
                object          paramValue = item.Value2;
                if (paramValue == null && filterAtt != null && filterAtt.Value != null)
                {
                    paramValue = filterAtt.Value;
                }
                paramValue = para.Value = item.Value2;
                if (paramType == typeof(Int32?) || paramType == typeof(Int32))
                {
                    para.SqlDbType = System.Data.SqlDbType.Int;
                }
                //Tìm kiếm dạng Unicode, có dấu thay cho ký tự N trong Sql
                System.Text.RegularExpressions.Regex isGuid = new System.Text.RegularExpressions.Regex(@"^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$", System.Text.RegularExpressions.RegexOptions.Compiled);
                bool _isMatch = isGuid.IsMatch(item.Value2 == null ? string.Empty : item.Value2.ToString());
                if ((item.MemberType == typeof(string) && !_isMatch && item.Member != "OrgStructureID") && paramValue != null)
                {
                    System.Text.UnicodeEncoding unicode = new System.Text.UnicodeEncoding();
                    para.Value = unicode.GetBytes(item.Value2.ToString());
                }

                else
                if (item.MemberType == typeof(Int32?))
                {
                    para.DbType = System.Data.DbType.Int32;
                }
                else if (item.MemberType == typeof(Double?))
                {
                    para.DbType = System.Data.DbType.Double;
                }
                else if (item.MemberType == typeof(DateTime?) || item.MemberType == typeof(DateTime))
                {
                    para.DbType = System.Data.DbType.DateTime;
                }

                if (paramValue == null)
                {
                    para.Value = DBNull.Value;
                }
                lstParas.Add(para);
            }
            strQuery          += "@PageIndex";
            para               = new SqlParameter();
            para.ParameterName = "PageIndex";
            para.Value         = model.PageIndex == 0 ? 1 : model.PageIndex;
            para.DbType        = System.Data.DbType.Int32;
            lstParas.Add(para);
            if (model.PageSize > 0)
            {
                strQuery          += ",@PageSize";
                para               = new SqlParameter();
                para.ParameterName = "PageSize";
                para.Value         = model.PageSize;
                para.DbType        = System.Data.DbType.Int32;
                lstParas.Add(para);
            }
            else
            {
                //if (strQuery.LastIndexOf(',') > 0)
                //{
                //    strQuery = strQuery.Substring(0, strQuery.Length - 1);
                //}
            }


            ParamaterModle param = new ParamaterModle();

            param.Params   = lstParas.ToArray();
            param.SqlQuery = strQuery;
            return(param);
        }