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);
}
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);
}