none
随机取数据 RRS feed

  • 问题

  • select * from table order by newid()

    /// <summary>
            /// 随机从数据库中取N条记录
            /// </summary>
            /// <param name="commandText"></param>
            /// <param name="nTop">返回的记录数</param>
            /// <param name="nRecordCount">满足条件的总记录数</param>
            /// <returns></returns>
            public static DataSet ExecuteDataSetRandom(string commandText, int nTop, int nRecordCount,params DbParameter[]parm)
            {
                return ExecuteDataSetRandom(CommandType.Text, commandText, nTop, nRecordCount,parm);
            }
            public static DataSet ExecuteDataSetRandom(CommandType commandType, string commandText,int nTop,int nRecordCount, params DbParameter[] commandParameters)
            {
             
                if (ConnectionString == null || ConnectionString.Length == 0) throw new ArgumentNullException("ConnectionString");

                // 创建并打开数据库连接对象,操作完成释放对象.

                //using (DbConnection connection = (DbConnection)new System.Data.SqlClient.SqlConnection(ConnectionString))
                using (DbConnection connection = Factory.CreateConnection())
                {
                    connection.ConnectionString = ConnectionString;
                    connection.Open();

                    // 调用指定数据库连接字符串重载方法.
                    //return ExecuteDataset(connection, commandType, commandText, commandParameters);
                   // 预处理
                    DbCommand cmd = Factory.CreateCommand();
                    bool mustCloseConnection = false;
                    PrepareCommand(cmd, connection, (DbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

                    // 创建DbDataAdapter和DataSet.
                    using (DbDataAdapter da = Factory.CreateDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        Random rdm = new Random();
                        int nStart = 0;
                        if(nRecordCount>nTop)
                            nStart=rdm.Next(nRecordCount - nTop);
                        if (nStart < 0)
                            nStart = 0;
                        // 填充DataSet.
                        da.Fill(ds,nStart,nTop,"tab0");

                        m_querycount++;

                        cmd.Parameters.Clear();


                        if (mustCloseConnection)
                            connection.Close();

                        return ds;
                    }
                }

            }这两种方法哪儿个好?哪个快?


    2009年7月20日 3:05

答案

  • 这两种方法,实际并不完全一样,不同之处是,使用方法 2 随机取数据中连续的一部分,如果你的主键是自增列,你可以看到取出来的记录是连续的. 在同样的条件下Order By NewId() 不管是从功能上还是性能上都是个比较好的方法
    知识改变命运,奋斗成就人生!
    2009年7月20日 3:49
    版主