how to enumerate repeating data using Sql and asp.net

Question

hi, every one i am developing an application with asp.net with database SQL server, in the database I have repeating data like these :

2001 -->2001
-->2001
-->2001
2002 -->2002
2003 -->2003
-->2003
i want the view will be like these :
2001 --> 1
-->2
-->3
2002 -->1
2003 -->1
2003 -->2
and it's a treeview that's why i want it like these , please help and thank you so much

Answer 1

Hi @Seif Eddine Del Kemala ,
You can try to use the number of nodes to generate a tree without duplicates, pass the parent node as a parameter, you can refer to the example below.

<asp:TreeView runat="server" ID="tview">    
    </asp:TreeView>    

code behind

  if (!Page.IsPostBack)  
                {  
                    string connection = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;  
                    using (SqlConnection Conn = new SqlConnection(connection))  
                    {  
                        string State = "Select * from IN_State";  
                        string City = "Select * from IN_City";  
      
                        string Treeview = State + ";" + City;  
      
                        DataSet ds = new DataSet();  
                        SqlDataAdapter da = new SqlDataAdapter(Treeview, Conn);  
                        da.Fill(ds);  
                        ds.Tables[0].TableName = "IN_State";  
                        ds.Tables[1].TableName = "IN_City";          
                        DataRelation dr = new DataRelation("StateCity", ds.Tables["IN_State"].Columns["S_Id"], ds.Tables["IN_City"].Columns["S_Id"]);  
                        ds.Relations.Add(dr);          
                        foreach (DataRow drState in ds.Tables["IN_State"].Rows)  
                        {  
                            TreeNode NDState = new TreeNode();  
                            NDState.Text = drState["S_Name"].ToString();  
                            NDState.Value = drState["S_Id"].ToString();  
                            tview.Nodes.Add(NDState);                                 
                            int index = 0;  
                            foreach (DataRow drCity in drState.GetChildRows("StateCity"))  
                            {  
                                index++;  
                                TreeNode NDCity = new TreeNode();                                                                 
                                //If index is 0, dont change text.  
                                if (index != 0)  
                                {  
                                    NDCity.Text =    index.ToString()  ;  
                                    NDCity.Value = drCity["C_Id"].ToString();          
                                }  
                                else  
                                {  
                                    NDCity.Text = drCity["C_Name"].ToString();  
                                    NDCity.Value = drCity["C_Id"].ToString();  
                                }  
                                NDState.ChildNodes.Add(NDCity);  
                            }  
                        }  
                    }  
                }  

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.