让IBatisNet支持DataTable,和分页(support DataMapper-1.5.1)

在我们的实际框架中更多的地方需要得到SQL语句。复杂的SQL主要是查询.本文介绍了怎么让IBatisNet支持DataTable。

IBatisNet之获取和操作SQL语句  一文中的方法不支持最新版本的IBatisNet。根据IBatisNet新版本改为：

GetSql/GetDataTable#region GetSql/GetDataTable

        /**//// <summary>
        /// 得到参数化后的SQL
        /// </summary>
        public static string GetSql(string tag, object paramObject)
        {
            IStatement statement = Mapper.Instance().GetMappedStatement(tag).Statement;

            IMappedStatement mapStatement = Mapper.Instance().GetMappedStatement(tag);

            IDalSession session = new SqlMapSession(Mapper.Instance());

            RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);

            return request.PreparedStatement.PreparedSql;

        }

        protected static IDbCommand GetDbCommand(string tag, object paramObject)
        {
            IStatement statement = Mapper.Instance().GetMappedStatement(tag).Statement;

            IMappedStatement mapStatement = Mapper.Instance().GetMappedStatement(tag);

            IDalSession session = new SqlMapSession(Mapper.Instance());

            RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);

            mapStatement.PreparedCommand.Create(request, session, statement, paramObject);

            return request.IDbCommand;

        }

        /**//// <summary>
        /// 通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数)
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>得到的DataTable</returns>
        public static DataTable GetDataTable(string tag, object paramObject)
        {
            DataSet ds = new DataSet();
            bool isSessionLocal = false;
            IDalSession session = Mapper.Instance().LocalSession;
            if (session == null )
            {
                session = new SqlMapSession(Mapper.Instance());
                session.OpenConnection();
                isSessionLocal = true;
            }
            try
            {
                IDbCommand cmd = GetDbCommand(tag, paramObject);
                cmd.Connection = session.Connection;
                IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
                adapter.Fill(ds);
            }
            finally
            {
                if (isSessionLocal) 
                {
                    session.CloseConnection();
                }
            }
            return ds.Tables[0];

        }

        /**//// <summary>
        /// 用于分页控件使用
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <param name="PageSize">每页显示数目</param>
        /// <param name="curPage">当前页</param>
        /// <param name="recCount">记录总数</param>
        /// <returns>得到的DataTable</returns>
        public static DataTable GetDataTable(string tag, object paramObject, int PageSize, int curPage, out int recCount)
        {
            IDataReader dr = null;
            bool isSessionLocal = false;
            string sql = GetSql(tag, paramObject);
            string strCount = "select count(*) " + sql.Substring(sql.ToLower().IndexOf("from"));

            IDalSession session = Mapper.Instance().LocalSession;
            DataTable dt = new DataTable();
            if (session == null)
            {
                session = new SqlMapSession(Mapper.Instance());
                session.OpenConnection();
                isSessionLocal = true;
            }
            try
            {
                IDbCommand cmdCount = GetDbCommand(tag, paramObject);
                cmdCount.Connection = session.Connection;
                cmdCount.CommandText = strCount;
                object count = cmdCount.ExecuteScalar();
                recCount = Convert.ToInt32(count);

                IDbCommand cmd = GetDbCommand(tag, paramObject);
                cmd.Connection = session.Connection;
                dr = cmd.ExecuteReader();

                dt = Paging(dr, PageSize, curPage);
            }
            finally
            {
                if (isSessionLocal) 
                {
                    session.CloseConnection();
                }
            }
            return dt;
        }


        /**//// <summary>
        /// 取回合适数量的数据
        /// </summary>
        /// <param name="dataReader"></param>
        /// <param name="PageSize"></param>
        /// <param name="curPage"></param>
        /// <returns></returns>
        protected static DataTable Paging(IDataReader dataReader, int PageSize, int curPage)
        {
            DataTable dt;
            dt = new DataTable();
            int colCount = dataReader.FieldCount;
            for (int i = 0; i < colCount; i++)
            {
                dt.Columns.Add(new DataColumn(dataReader.GetName(i), dataReader.GetFieldType(i)));
            }

            // 读取数据。将DataReader中的数据读取到DataTable中

            object[] vald = new object[colCount];
            int iCount = 0; // 临时记录变量
            while (dataReader.Read())
            {
                // 当前记录在当前页记录范围内

                if (iCount >= PageSize * (curPage - 1) && iCount < PageSize * curPage)
                {
                    for (int i = 0; i < colCount; i++)
                        vald[i] = dataReader.GetValue(i);

                    dt.Rows.Add(vald);
                }
                else if (iCount > PageSize * curPage)
                {
                    break;
                }
                iCount++; // 临时记录变量递增
            }

            if (!dataReader.IsClosed)
            {
                dataReader.Close();
                dataReader.Dispose();
            }
            return dt;
        }

        #endregion