SqlDataReader reading only needed records

[Roy Gourgi]

Hi,

I have a table with 10,000 records but I only want to read 250 at a time. I know the row number of the 250 records that I have to read and so I want to avoid reading all 100,000 records each time. For example, if I want to read rows 60,001 to 60,250 how would I do that? Is there any way of doing it without reading the previous 60,000 rows?

Below is my code:

public class cRead_CoveringElementType_Variation_IntoArray
{

    public static void mRead_CoveringElementType_Varation_IntoArray(int liFromBlock, int liToBlock)
    {

        int liRowNo = 0;
        int liMiscLp = 0;

        string strCommSelect = "";

        strCommSelect = "SELECT * FROM tbl_COVERING_and_KType";

        string strConnection = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\CombinatoRoyX\DataBase\dbCoveringType_Sorted.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"; // User Id=sa;Password=Elena_501_Pogutz; This is the PW
        SqlConnection oConnection = new SqlConnection(strConnection);
        SqlCommand oCommand = new SqlCommand(strCommSelect, oConnection);

        oCommand.Connection.Open();

        SqlDataReader mySqlDataReader = oCommand.ExecuteReader();


        int liRowToRead = 0;


        while (mySqlDataReader.Read())
        {
            
            liRowNo++;
            
            if (liRowNo >= liFromBlock && liRowNo <= liToBlock)
            {

                liRowToRead++;


                for (liMiscLp = 1; liMiscLp <= 2 * aGV.K; liMiscLp++) aGV.Cov_Var[liRowToRead, liMiscLp] = mySqlDataReader.GetInt32(liMiscLp);

            }
        
        }


        mySqlDataReader.Close();
        oConnection.Close();





    }

}

[sknake]

Please make sure to use matching code tags when posting code:

[code=C#] ...code...

[/code]

You should modify your query to only select the desired records. MSSQL has a hard limit of 2100 parameters. As far as selecting rows #N1-N2 you have a few options.

#1 - Parameters:

Select * From dbo.[Table] Where [Table].[RowNumber] In (@P1, @P2, @P3)

DataTable GetRows()
    {
      using (SqlConnection conn = new SqlConnection("conn string"))
      {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
          cmd.CommandText = @"Select * From dbo.[Table] Where [Table].[RowNumber] In (@P1, @P2, @P3)";
          cmd.Parameters.Add("@P1", SqlDbType.Int).Value = 1;
          cmd.Parameters.Add("@P2", SqlDbType.Int).Value = 2;
          cmd.Parameters.Add("@P3", SqlDbType.Int).Value = 3;
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            DataTable dt = new DataTable();
            dt.Load(dr);
            return dt;
          }
        }
      }
    }

#2 - No Parameters:

Select * From dbo.[Table] Where [Table].[RowNumber] In (1, 2, 3)

There are a few TSQL tricks to assign row numbers if your schema doesn't support selecting forth the desired rows.

Please mark this thread as solved if your question has been answered and good luck!