Пример #1
0
        /// <summary>
        ///  根据OQL查询数据获得DataReader。如果指定single=真,将执行优化的查询以获取单条记录
        /// </summary>
        /// <param name="oql">OQL表达式</param>
        /// <param name="db">当前数据库访问对象</param>
        /// <param name="factEntityType">实体类类型</param>
        /// <param name="single">是否只查询一条记录</param>
        /// <returns>DataReader</returns>
        public static IDataReader ExecuteDataReader(OQL oql, AdoHelper db, Type factEntityType, bool single)
        {
            string sql = "";
            Dictionary <string, object> Parameters = null;

            //处理用户查询映射的实体类
            if (oql.EntityMap == PWMIS.Common.EntityMapType.SqlMap)
            {
                if (CommonUtil.CacheEntityMapSql == null)
                {
                    CommonUtil.CacheEntityMapSql = new Dictionary <string, string>();
                }
                if (!CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table))
                {
                    string tempView = GetMapSql(factEntityType);
                    CommonUtil.CacheEntityMapSql.Add(oql.sql_table, tempView);
                }
                sql = oql.GetMapSQL(CommonUtil.CacheEntityMapSql[oql.sql_table]);


                //如果用户本身没有初始化参数对象,则这里声明一个 edit at 2012.11.16
                Parameters = oql.InitParameters ?? new Dictionary <string, object>();
                if (oql.Parameters != null && oql.Parameters.Count > 0)
                {
                    foreach (string name in oql.Parameters.Keys)
                    {
                        Parameters.Add(name, oql.Parameters[name]);
                    }
                }
            }
            else if (oql.EntityMap == PWMIS.Common.EntityMapType.StoredProcedure)
            {
                string script = "";
                if (CommonUtil.CacheEntityMapSql == null)
                {
                    CommonUtil.CacheEntityMapSql = new Dictionary <string, string>();
                }
                //获取SQL-MAP脚本
                if (CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table))
                {
                    script = CommonUtil.CacheEntityMapSql[oql.sql_table];
                }
                else
                {
                    script = GetMapSql(factEntityType);
                    CommonUtil.CacheEntityMapSql.Add(oql.sql_table, script);
                }
                //对SQL-MAP格式的参数进行解析
                SqlMap.SqlMapper mapper = new PWMIS.DataMap.SqlMap.SqlMapper();
                mapper.DataBase = db;
                //解析存储过程名称
                sql = mapper.FindWords(mapper.GetScriptInfo(script), 0, 255); //由于是存储过程,需要特殊处理,调用 FindWords方法
                //解析参数
                IDataParameter[] paras = mapper.GetParameters(script);
                if (oql.InitParameters != null && oql.InitParameters.Count > 0)
                {
                    try
                    {
                        foreach (IDataParameter para in paras)
                        {
                            string key = para.ParameterName.TrimStart(db.GetParameterChar.ToCharArray());
                            para.Value = oql.InitParameters[key];
                        }
                    }
                    catch (KeyNotFoundException exKey)
                    {
                        throw new KeyNotFoundException("'存储过程实体类'的初始化参数中没有找到指定的参数名,请检查参数定义和设置。", exKey);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                else
                {
                    if (paras.Length > 0)
                    {
                        throw new Exception("当前'存储过程实体类'需要提供初始化参数,请设置OQL对象的InitParameters属性");
                    }
                }

                return(db.ExecuteDataReader(sql, CommandType.StoredProcedure, paras));
            }
            else
            {
                //edit at 2013-2-25 处理非SQLSERVER的参数名前缀问题
                if (db is SqlServer)
                {
                    sql = oql.ToString();
                }
                else
                {
                    sql = oql.ToString().Replace("@", db.GetParameterChar);
                }
                Parameters = oql.Parameters;
            }


            //处理实体类分页 2010.6.20
            if (oql.PageEnable && !single)
            {
                switch (db.CurrentDBMSType)
                {
                case PWMIS.Common.DBMSType.Access:
                case PWMIS.Common.DBMSType.SqlServer:
                case PWMIS.Common.DBMSType.SqlServerCe:
                    //如果含有Order By 子句,则不能使用主键分页
                    if (oql.HaveJoinOpt || sql.IndexOf("order by", StringComparison.OrdinalIgnoreCase) > 0)
                    {
                        sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.SqlServer, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount);
                    }
                    else
                    {
                        if (oql.PageOrderDesc)
                        {
                            sql = PWMIS.Common.SQLPage.GetDescPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition);
                        }
                        else
                        {
                            sql = PWMIS.Common.SQLPage.GetAscPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition);
                        }
                    }
                    break;

                //case PWMIS.Common.DBMSType.Oracle:
                //    sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.Oracle, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount);
                //    break ;
                //case PWMIS.Common.DBMSType.MySql:
                //case PWMIS.Common.DBMSType.SQLite:
                //    sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.MySql, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount);
                //    break;
                //case PWMIS.Common.DBMSType.PostgreSQL:
                //    sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.PostgreSQL, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount);
                //    break;
                default:
                    sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(db.CurrentDBMSType, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount);
                    break;
                }
            }

            IDataReader reader = null;

            if (Parameters != null && Parameters.Count > 0)
            {
                int fieldCount         = Parameters.Count;
                IDataParameter[] paras = new IDataParameter[fieldCount];
                int index = 0;

                foreach (string name in Parameters.Keys)
                {
                    paras[index] = db.GetParameter(name, Parameters[name]);
                    //为字符串类型的参数指定长度 edit at 2012.4.23
                    if (paras[index].Value != null && paras[index].Value.GetType() == typeof(string))
                    {
                        string field = FindFieldNameInSql(sql, name, db.GetParameterChar);
                        ((IDbDataParameter)paras[index]).Size = EntityBase.GetStringFieldSize(oql.sql_table, field);
                    }
                    index++;
                }
                if (single)
                {
                    reader = db.ExecuteDataReaderWithSingleRow(sql, paras);
                }
                else
                {
                    reader = db.ExecuteDataReader(sql, CommandType.Text, paras);
                }
            }
            else
            {
                if (single)
                {
                    reader = db.ExecuteDataReaderWithSingleRow(sql);
                }
                else
                {
                    reader = db.ExecuteDataReader(sql);
                }
            }
            return(reader);
        }
Пример #2
0
        internal static SqlInfo GetSqlInfoFromOQL(OQL oql, AdoHelper db, Type factEntityType, bool single)
        {
            string sql = "";
            Dictionary <string, TableNameField> Parameters = null;

            if (oql.EntityMap == PWMIS.Common.EntityMapType.Table ||
                oql.EntityMap == PWMIS.Common.EntityMapType.View)
            {
                #region OQL2SqlInfo
                if (oql.PageEnable && (!single || oql.PageWithAllRecordCount <= 0))
                {
                    sql = GetOQLPageSql(oql, db);
                }
                else
                {
                    sql = oql.ToString();
                }

                SqlInfo result = new SqlInfo(sql, oql.Parameters);
                result.CommandType = CommandType.Text;
                result.TableName   = oql.GetEntityTableName();
                return(result);

                #endregion
            }
            else if (oql.EntityMap == PWMIS.Common.EntityMapType.SqlMap)
            {
                #region SQLMAP
                //处理用户查询映射的实体类
                if (CommonUtil.CacheEntityMapSql == null)
                {
                    CommonUtil.CacheEntityMapSql = new Dictionary <string, string>();
                }
                if (!CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table))
                {
                    string tempView = GetMapSql(factEntityType);
                    CommonUtil.CacheEntityMapSql.Add(oql.sql_table, tempView);
                }
                sql = oql.GetMapSQL(CommonUtil.CacheEntityMapSql[oql.sql_table]);


                //如果用户本身没有初始化参数对象,则这里声明一个 edit at 2012.11.16
                Parameters = new Dictionary <string, TableNameField>();
                if (oql.InitParameters != null)
                {
                    foreach (string name in oql.InitParameters.Keys)
                    {
                        TableNameField tnf = new TableNameField();
                        tnf.FieldValue = oql.InitParameters[name];
                        Parameters.Add(name, tnf);
                    }
                }

                if (oql.Parameters != null && oql.Parameters.Count > 0)
                {
                    foreach (string name in oql.Parameters.Keys)
                    {
                        Parameters.Add(name, oql.Parameters[name]);
                    }
                }

                //这里可能需要特别处理分页 --修改 @芜湖-大枕头 2016.12.15
                if (oql.PageEnable && (!single || oql.PageWithAllRecordCount <= 0))
                {
                    sql = GetOQLPageSql(oql, db);
                }
                else
                {
                    sql = oql.ToString();
                }
                SqlInfo si = new SqlInfo(sql, Parameters);
                si.CommandType = CommandType.Text;
                return(si);

                #endregion
            }
            else
            {
                #region StoredProcedure
                string script = "";
                if (CommonUtil.CacheEntityMapSql == null)
                {
                    CommonUtil.CacheEntityMapSql = new Dictionary <string, string>();
                }
                //获取SQL-MAP脚本
                if (CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table))
                {
                    script = CommonUtil.CacheEntityMapSql[oql.sql_table];
                }
                else
                {
                    script = GetMapSql(factEntityType);
                    CommonUtil.CacheEntityMapSql.Add(oql.sql_table, script);
                }
                //对SQL-MAP格式的参数进行解析
                SqlMap.SqlMapper mapper = new PWMIS.DataMap.SqlMap.SqlMapper();
                mapper.DataBase = db;
                //解析存储过程名称
                sql = mapper.FindWords(mapper.GetScriptInfo(script), 0, 255); //由于是存储过程,需要特殊处理,调用 FindWords方法
                //解析参数
                IDataParameter[] paras = mapper.GetParameters(script);
                if (oql.InitParameters != null && oql.InitParameters.Count > 0)
                {
                    Parameters = new Dictionary <string, TableNameField>();
                    try
                    {
                        foreach (IDataParameter para in paras)
                        {
                            string key = para.ParameterName.TrimStart(db.GetParameterChar.ToCharArray());
                            para.Value = oql.InitParameters[key];
                            Parameters.Add(key, new TableNameField()
                            {
                                FieldValue = para
                            });
                        }
                    }
                    catch (KeyNotFoundException exKey)
                    {
                        throw new KeyNotFoundException("'存储过程实体类'的初始化参数中没有找到指定的参数名,请检查参数定义和设置。", exKey);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                else
                {
                    if (paras.Length > 0)
                    {
                        throw new Exception("当前'存储过程实体类'需要提供初始化参数,请设置OQL对象的InitParameters属性");
                    }
                }

                SqlInfo si = new SqlInfo(sql, Parameters);
                si.CommandType = CommandType.StoredProcedure;
                return(si);

                #endregion
            }
        }
Пример #3
0
        internal static SqlInfo GetSqlInfoFromOQL(OQL oql, AdoHelper db, Type factEntityType, bool single)
        {
            string sql = "";
            Dictionary <string, TableNameField> Parameters = null;

            //处理用户查询映射的实体类
            if (oql.EntityMap == PWMIS.Common.EntityMapType.SqlMap)
            {
                if (CommonUtil.CacheEntityMapSql == null)
                {
                    CommonUtil.CacheEntityMapSql = new Dictionary <string, string>();
                }
                if (!CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table))
                {
                    string tempView = GetMapSql(factEntityType);
                    CommonUtil.CacheEntityMapSql.Add(oql.sql_table, tempView);
                }
                sql = oql.GetMapSQL(CommonUtil.CacheEntityMapSql[oql.sql_table]);


                //如果用户本身没有初始化参数对象,则这里声明一个 edit at 2012.11.16
                Parameters = new Dictionary <string, TableNameField>();
                if (oql.InitParameters != null)
                {
                    foreach (string name in oql.InitParameters.Keys)
                    {
                        TableNameField tnf = new TableNameField();
                        tnf.FieldValue = oql.InitParameters[name];
                        Parameters.Add(name, tnf);
                    }
                }

                if (oql.Parameters != null && oql.Parameters.Count > 0)
                {
                    foreach (string name in oql.Parameters.Keys)
                    {
                        Parameters.Add(name, oql.Parameters[name]);
                    }
                }
            }
            else if (oql.EntityMap == PWMIS.Common.EntityMapType.StoredProcedure)
            {
                string script = "";
                if (CommonUtil.CacheEntityMapSql == null)
                {
                    CommonUtil.CacheEntityMapSql = new Dictionary <string, string>();
                }
                //获取SQL-MAP脚本
                if (CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table))
                {
                    script = CommonUtil.CacheEntityMapSql[oql.sql_table];
                }
                else
                {
                    script = GetMapSql(factEntityType);
                    CommonUtil.CacheEntityMapSql.Add(oql.sql_table, script);
                }
                //对SQL-MAP格式的参数进行解析
                SqlMap.SqlMapper mapper = new PWMIS.DataMap.SqlMap.SqlMapper();
                mapper.DataBase = db;
                //解析存储过程名称
                sql = mapper.FindWords(mapper.GetScriptInfo(script), 0, 255); //由于是存储过程,需要特殊处理,调用 FindWords方法
                //解析参数
                IDataParameter[] paras = mapper.GetParameters(script);
                if (oql.InitParameters != null && oql.InitParameters.Count > 0)
                {
                    Parameters = new Dictionary <string, TableNameField>();
                    try
                    {
                        foreach (IDataParameter para in paras)
                        {
                            string key = para.ParameterName.TrimStart(db.GetParameterChar.ToCharArray());
                            para.Value = oql.InitParameters[key];
                            Parameters.Add(key, new TableNameField()
                            {
                                FieldValue = para
                            });
                        }
                    }
                    catch (KeyNotFoundException exKey)
                    {
                        throw new KeyNotFoundException("'存储过程实体类'的初始化参数中没有找到指定的参数名,请检查参数定义和设置。", exKey);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                else
                {
                    if (paras.Length > 0)
                    {
                        throw new Exception("当前'存储过程实体类'需要提供初始化参数,请设置OQL对象的InitParameters属性");
                    }
                }

                SqlInfo si = new SqlInfo(sql, Parameters);
                si.CommandType = CommandType.StoredProcedure;
                //return db.ExecuteDataReader(sql, CommandType.StoredProcedure, paras);
                return(si);
            }
            else
            {
                sql        = oql.ToString();
                Parameters = oql.Parameters;
            }


            if (oql.PageEnable && (!single || oql.PageWithAllRecordCount <= 0))
            {
                switch (db.CurrentDBMSType)
                {
                case PWMIS.Common.DBMSType.Access:
                case PWMIS.Common.DBMSType.SqlServer:
                case PWMIS.Common.DBMSType.SqlServerCe:
                    //如果含有Order By 子句,则不能使用主键分页
                    if (oql.haveJoinOpt || sql.IndexOf("order by", StringComparison.OrdinalIgnoreCase) > 0)
                    {
                        sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.SqlServer, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount);
                    }
                    else
                    {
                        //如果是字符串类型的主键,下面的分页可能不准确
                        if (oql.PageOrderDesc)
                        {
                            sql = PWMIS.Common.SQLPage.GetDescPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition);
                        }
                        else
                        {
                            sql = PWMIS.Common.SQLPage.GetAscPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition);
                        }
                    }
                    break;

                default:
                    sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(db.CurrentDBMSType, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount);
                    break;
                }
            }
            SqlInfo result = new SqlInfo(sql, Parameters);

            result.CommandType = CommandType.Text;
            result.TableName   = oql.sql_table;
            return(result);
        }