locked
No Of Records RRS feed

  • Question

  • User1979860870 posted

    Hi

     I have below code &i want to get no of records . If greater than 0 then code should be executed.

    Also to display Quantity using datareader

    string sql = @"SELECT T0.[Name] , 
                    T0.[DocNum], T0.[CardName] 'Customer Name', T0.[DocDate],
                    T1.[Description], Abs(T2.[Quantity]) as Quantity,
                    
                    FROM Tbl1  T0 INNER JOIN Tbl2 T1 ON T0.[Id] = T1.[Id]
    ";
                    string strHtml = "<table>";
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        dr = cmd.ExecuteReader();
    
                        
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            strHtml += "<tr>";
                            strHtml += "<td>" + dr.GetValue(0).ToString() + "</td>";
                            strHtml += "<td>" + dr.GetValue(1).ToString() + "</td>";
                            strHtml += "<td>" + dr.GetValue(2).ToString() + "</td>";
                            strHtml += "</tr>";
                        }
                        strHtml += "</table>";
                    }

    Thanks

    Wednesday, December 30, 2020 4:36 AM

Answers

All replies

  • User-1330468790 posted

    Hi jagjit saini, 

     

    SqlDataReader are commonly used to read one row at one time.

    I think the problem for your scenario is that you are not using dr.Read(), which advances the SqlDataReader to the next record and returns true if there are more rows, otherwise false.

    You could refer to this link: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.read?view=dotnet-plat-ext-5.0

      

    Regarding your codes, you might need to modify it as below.

     string sql = @"SELECT ANY COLUMNS THAT YOU WANT";
    
                string strHtml = "<table>";
                using (SqlConnection conn = new SqlConnection(constr))
                {
    
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        conn.Open();
                        SqlDataReader dr = cmd.ExecuteReader();
                        // while data reader has a next row, then add value, otherwise stop the loop
                        while (dr.Read())
                        {
    
                            strHtml += "<tr>";
                            strHtml += "<td>" + dr.GetValue(0).ToString() + "</td>";
                            strHtml += "<td>" + dr.GetValue(1).ToString() + "</td>";
                            strHtml += "<td>" + dr.GetValue(2).ToString() + "</td>";
                            strHtml += "</tr>";
    
                            
                        }
    
                        strHtml += "</table>";
                    }
                }

      

    There is another alternative that you could use SqlDataAdapter to fill a DataTable and then count the rows of the DataTable.

    Reference:  https://stackoverflow.com/a/18511793/12871232

     

    Hope helps.

    Best regards,

    Sean

    Wednesday, December 30, 2020 6:13 AM
  • User1979860870 posted

    Hi Sean

      I want if rows > 0 then code should gets execute

    Thanks

    Wednesday, December 30, 2020 6:24 AM
  • User-1330468790 posted

    Hi jagjit saini, 

     

    I am afraid that you can't get the number of rows without (1) looping through all of the rows AND (2) closing the DataReader.

    Is this what you need? (looping through all of rows twice in total)

      

    If you step into the SqlDataReader Class Document, you will find that there is no directly workaround to get and count all of rows if you use SqlDataReader. Hence I suggest you use SqlDataAdapter to

    1. Fill a DataTable object first
    2. And use this resulting DataTable object to do counting and traversing
    3. Populate the data to the table html element.

      

    Moreover, there are two incorrect places in your codes.

    1. You must call dr.Read() to read the next row. Otherwise, you will get below error when you attempt to read data 

    Exception Details: System.InvalidOperationException: Invalid attempt to read when no data is present.

    2. You cannot use dr.FieldCount to do the for-loop because the FieldCount returns the number of the column in a data row.

    For the second issue, please refer to below example.

    Id Name Value
    1 Name_1 Value_1
    2 Name_2 Value_2

    If you use dr.Read() to start reading the first row and use dr.FieldCount to loop through the data, the final table would be repeating the first row for three times (the table has three columns)

    1 Name_1 Value_1
    1 Name_1 Value_1
    1 Name_1 Value_1

     

    The reason why I suggest you that solution is that the codes are working for your final purpose:

    • Read through the result that is returned with the select sql query
    • Fill one row of the data to the html table and move to the next row 

    Have you tried and tested the codes to see if it is working as you expect?

     

    Best regards,

    Sean 

    Wednesday, December 30, 2020 9:38 AM
  • User753101303 posted

    Hi,

    You want really the count or just skip rendering the table? In this later case you can use SqlDataReader.HasRows Property (System.Data.SqlClient) | Microsoft Docs to know if you have at least oine row.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 30, 2020 9:43 AM