Convert Rank to percentage C#

Question

I am trying to display percentage based on the rank of each record returned in search. I want it to loop through each item but it only loops through the first item as many times as I have items. For instance if it found 4 results it would display the rank of the first one on all 4 results.

Any suggestions to get it to display each rank separately and convert it to percentage?

private void BindRpt()
{
    if (string.IsNullOrEmpty(txtSearch.Text)) return;
    SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString);
    cn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = cn;
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    cmd.CommandText = "select Distinct Rank, SUBSTRING(ColumnA, 1, 500) AS ColumnA, ColumnB, ColumnC, ColumnD, ColumnE from FREETEXTTABLE (TABLE , ColumnA, '" + Search.Text + "'  ) S, TABLE C WHERE c.ID = S.[KEY] order by Rank Desc";

    DataTable dt = new DataTable();
    adapter.SelectCommand = cmd;
    adapter.Fill(dt);

    PagedDataSource pgitems = new PagedDataSource();
    pgitems.DataSource = dt.DefaultView;
    pgitems.AllowPaging = true;

    pgitems.PageSize = 3;
    pgitems.CurrentPageIndex = PageNumber;
    if (pgitems.Count > 1)
    {                
        rptPaging.Visible = true;
        ArrayList pages = new ArrayList();
        for (int i = 0; i <= pgitems.PageCount - 1; i++)
        {
            pages.Add((i + 1).ToString());
        }
        rptPaging.DataSource = pages;
        rptPaging.DataBind();

        lblSentence.Visible = true;
        lblSearchWord.Visible = true;
        lblSearchWord.Text = txtSearch.Text;
    }
    else 
    {
        rptPaging.Visible = false;

        lblSentence.Visible = true;
        lblSentence.Text = "Results were found for";

        lblSearchWord.Visible = true;
        lblSearchWord.Text = txtSearch.Text;
    }
    rptResults.DataSource = pgitems;
    rptResults.DataBind();
    cn.Close();
}

protected void rptResults_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["HTAA"].ConnectionString);
    cn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = cn;
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    cmd.CommandText = "select Distinct Rank, SUBSTRING(ColumnA, 1, 500) AS ColumnA, ColumnB, ColumnC, ColumnD, ColumnE from FREETEXTTABLE (TABLE , ColumnA, '" + Search.Text + "'  ) S, TABLE C WHERE c.ID = S.[KEY] order by Rank Desc";


    int number = Page.Items.Count;
    SqlDataReader dr = cmd.ExecuteReader();
    if(dr.Read())
    {
        int firstrank = dr.GetInt32(0);
        while (dr.Read())
        {
            int rank = dr.GetInt32(0);
            int percentage = (rank / firstrank) * 100;
            Label lblpre = (Label)e.Item.FindControl("lblRank");
            lblpre.Text = percentage.ToString();
        }
    }
    dr.Close();
    cn.Close();
}

`int percentage = (rank / rank) * 100;` Will always result in a value of 100, no matter what `rank` is. — p e p, Aug 01 '14 at 18:13
Your code has a number of issues. In addition to the logic error pointed out above, and the looping issue pointed out in the answers, it's vulnerable to SQL injection. You're not properly wrapping things in `using` statements. You may want to do a bit more research on the basics. — David Crowell, Aug 01 '14 at 18:19
What do you expect the data to look like? If you have four records, they're ranked 1..4, right? What should your result look like? — tbddeveloper, Aug 01 '14 at 18:21
I changed the code, it seems like it is doing it but it displays 0 — Victor_Tlepshev, Aug 01 '14 at 18:22

tbddeveloper · Accepted Answer · 2014-08-01T19:36:35.547

1

After a chat, I have a better handle on things. A way to do this;

Create a private field on your code behind file.

private int topRanked = 0;

In your Bind method()

private void Bind() 
 { 
 ... 
 DataTable dt = new DataTable(); 
 adapter.SelectCommand = cmd; 
 adapter.Fill(dt); 

 topRanked = (int)dt.Rows[0]["Rank"];

Now, make your OnItemDataBound method;

protected void OnItemDataBound(object sender, RepeaterItemEventArgs e) 
 { 
 var dataItem = e.Item.DataItem as DataRowView; 
 int rank = (int) dataItem["Rank"]; 

 var percentage = ((double)topRanked / rank) * 100; 

 Label label = (Label)e.Item.FindControl("labelRank"); 

 label.Text = percentage.ToString(); 
 }

as mentioned. I don't believe it's the best answer, but it is an answer. I'm sure a stored procedure, or even a better sql method could probably calculate this and not leave you making calculations in code.

edited Aug 01 '14 at 19:36

answered Aug 01 '14 at 18:09

tbddeveloper

2,407
1
23
39

You're going to have to loop through the entire set anyway, but to calculate the rank, I'm thinking. – tbddeveloper Aug 01 '14 at 18:20
I'm trying, but again though, what are you expecting the data to look like? I'm certain that the label code in that loop is absolutely incorrect. – tbddeveloper Aug 01 '14 at 18:27
The data should like this 100, 95, 92 depends on the rank number – Victor_Tlepshev Aug 01 '14 at 18:30
it looks like the it does not save the value of (x/y)*100. For some reason itt always either 100 or 0 – Victor_Tlepshev Aug 01 '14 at 18:39
1

http://stackoverflow.com/questions/2602025/why-i-cannot-the-get-percentage-by-using-int – tbddeveloper Aug 01 '14 at 18:43
This works as far as the math but it sets all 4 items to show 74.223 – Victor_Tlepshev Aug 01 '14 at 18:48
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/58515/discussion-between-hammerstein-and-vt146911). – tbddeveloper Aug 01 '14 at 18:53

score 0 · Answer 2 · answered Aug 01 '14 at 18:09

0

Can you try with while(dr.Read()) instead of "if"?

answered Aug 01 '14 at 18:09

ssug89

249
2
8

score 0 · Answer 3 · answered Aug 01 '14 at 18:11

0

You will want to loop over the result set

while (dr.Read())
{
   int rank = dr.GetInt32(0);
   int percentage = (rank / rank) * 100;
   Label lblpre = (Label)e.Item.FindControl("lblRank");
   lblpre.Text = rank.ToString();

}

answered Aug 01 '14 at 18:11

vesuvious

2,753
4
26
39

`rank / rank` will always be `1`. – David Crowell Aug 01 '14 at 18:12
I didn't edit that part of his code, just copied it from the original. The point was to show how to iterate the result set. – vesuvious Aug 01 '14 at 18:14
hmm.. I see what you saying so how should I do it? – Victor_Tlepshev Aug 01 '14 at 18:15
I need somehow to set the first result to an int and use it when dividing but how to do it? – Victor_Tlepshev Aug 01 '14 at 18:17
To display each rank separately you will have to have multiple labels or concatenate the different ranks as a string. – vesuvious Aug 01 '14 at 18:17
I believe dr.GetInt32(0) will retrieve the value of the first column of your SQL result set as an integer, I am not sure how your dataset looks maybe having that info will help. – vesuvious Aug 01 '14 at 18:21
I am using a repeater control, I did not mention that. – Victor_Tlepshev Aug 01 '14 at 18:25

score 0 · Answer 4 · answered Aug 01 '14 at 18:12

"but it only loops through the first item" - because you have a for and checks for i <= 0

for (int i = 0; i <= 0; i++) 
{
   ....
}

You just don't need this for statement but rather use

if (dr != null)
   using (dr)
   {
         while (dr.Read())
         {
          ..
         }
   }

It's always better to use using when dealing with db connection objects so the resources used by these objects are properly disposed after it's been used.

I deleted the for loop – Victor_Tlepshev Aug 01 '14 at 18:14 — Victor_Tlepshev, Aug 01 '14 at 18:14

Convert Rank to percentage C#

4 Answers4