Sorting in InfoPath Repeating table through C-# code

There are few ways we can sort the data in InfoPath repeating table. However, there is no out of the box sorting mechanism in InfoPath. (I think it is doable but very tedious). Here is very simple code, we can sort the data with multiple fields (columns).


Generally, explicit sorting is not required if you bind data from Database, as we can include our sorting login in SQL query. What, if you are getting data from different sources. Here is the code.
I am assuming that, on button click we will sort the data in Repeating table.

public void btnSortData_Clicked(object sender, ClickedEventArgs e)

{

           // Replace with your respective xpath and field names

            String xpathRow =           "/my:myFields/my:ManageProjectViewWrapper/my:SubProjectsSum/my:SUBPROJECTS";

             // you can add as many fields as you can 

          String[] xpathField = { "my:subprojecTitle", “my:subprojecID” };

 

            XmlSortOrder[] order = { XmlSortOrder.Ascending,

            XmlSortOrder.Ascending };

 

            XmlDataType[] type = { XmlDataType.Text, XmlDataType.Text }; 

            SortData(xpathRow, xpathField, order, type);

}

public bool SortData(String row, String[] field, XmlSortOrder[] order, XmlDataType[] type)

        {

            bool returnVal = true;

            try

            {// you should have more than 2 rows for sorting 

                XPathNavigator myNamespace =

                MainDataSource.CreateNavigator(); 

                XPathExpression myRow = myNamespace.Compile(row); 

                myRow.SetContext(NamespaceManager); 

            XPathNodeIterator myRowIterator = myNamespace.Select(myRow); 

                if (myRowIterator.Count > 2)

                {// Loop through parameters to add sorting. 

                    for (int i = 0; i < field.Length; i++)

                    {// Set the various sort parameters. 

                        XmlSortOrder myOrder = order[i]; 

                        XmlDataType myDataType = type[i]; 

                 XPathExpression myField = myNamespace.Compile(field[i]); 

                        myField.SetContext(NamespaceManager); 

                        // Add sort parameter to compiled row XPath. 

        myRow.AddSort(myField, myOrder, XmlCaseOrder.None, "",myDataType);

                    }

                } 

                // Select table and sorted node set.

                XPathNavigator myTable = myNamespace.SelectSingleNode(row,

                NamespaceManager); 

                myTable.MoveToParent(); 

                myRowIterator = myNamespace.Select(myRow); 

                // Delete unsorted rows. 

         XPathNavigator xnRangeFirst = myNamespace.SelectSingleNode(row +

                "[1]", NamespaceManager); 

           XPathNavigator xnRangeLast = myNamespace.SelectSingleNode(row +  

                "[last()]", NamespaceManager); 

                xnRangeFirst.DeleteRange(xnRangeLast);

                // Append sorted rows. 

                while (myRowIterator.MoveNext())

                {myTable.AppendChild(myRowIterator.Current);

                }

            }// end of try 

            catch

            {

             returnVal = false;

            }           

            return returnVal;

        }

Happy programming....
Cheers!
Vamsi

Bind InfoPath repeating table with data from SQL Server Database using C-# Code

I am assuming that following are already placed in your InfoPath form
1. Form template with Repeating table
2. Table from where to get data in SQL Server
3. Connection string
Assuming that, on button click I am filling data to Repeating table
public void CTRL122_5_Clicked(object sender, ClickedEventArgs e)
{
   RefreshData();
}

public void RefreshData()
{
 

// clear previous entires in repeating table

XPathNavigator rTable = MainDataSource.CreateNavigator();

// Replace with your XPath

XPathNodeIterator tableRows = rTable.Select("/my:myFields/my:ManageProjectViewWrapper/my:SubProjectsSum/my:SUBPROJECTS", NamespaceManager);

            if (tableRows.Count > 0)

            {

              for (int i = tableRows.Count; i > 0; i--)

                {

                    XPathNavigator reTable =

                    MainDataSource.CreateNavigator();

        // Replace with your XPath            

XPathNavigator reTableRows = reTable.SelectSingleNode("/my:myFields/my:ManageProjectViewWrapper/my:SubProjectsSum/my:SUBPROJECTS[" + i + "]", NamespaceManager);

                    reTableRows.DeleteSelf();

                }

            }

            //get sub projects from database

            string Dbconnection = GetDBConnection();

            SqlConnection sqlConn = new SqlConnection(Dbconnection);

            //query sql

            sqlConn.Open();

            SqlDataReader reader;

            SqlCommand sqlComm = new SqlCommand();           

            // Replace with your query           

            sqlComm.CommandText = " SELECT [SubProjectTitle], [DesignManager],  

            [SubProjID] FROM [DesignTrackerReporting].[dbo].[SubProject] ORDER BY

            [SubProjectTitle] ASC ";

            sqlComm.CommandType = CommandType.Text;

            sqlComm.Connection = sqlConn;

            reader = sqlComm.ExecuteReader();

            reader.Read();

            //create navigator for repeating table

            string myNamespace = NamespaceManager.LookupNamespace("my");

            while (reader.Read())

            {

             // Replace with your XPath               

             using (XmlWriter writer = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:ManageProjectViewWrapper/my:SubProjectsSum", NamespaceManager).AppendChild())

                {

                    // Replace with your fields and Xpath        

                 writer.WriteStartElement("SUBPROJECTS", myNamespace);

                    writer.WriteElementString("subprojecTitle", myNamespace, reader

                    ["SubProjectTitle"].ToString());

                    writer.WriteElementString("designManager", myNamespace, reader

                    ["DesignManager"].ToString());

                    writer.WriteElementString("SubProjID", myNamespace, reader

                    ["SubProjID"].ToString());

                    writer.WriteEndElement();

                    writer.Close();

                }// end of using writer

            }//end of while

       

}// end of method

// Get DB connection method, replace with your connection string

private string GetDBConnection()

        {

        string value = " user id=domain\\user; password=*****;server=Server1; Trusted_Connection=yes; database=TemDb; connection timeout=30;";

            return value;

        }

Happy programming....

Cheers!

Vamsi

Delete/Remove single row from InfoPath Repeating Table

Here is simple code snippet to get current row context in InfoPath repeating table
There are two ways
1. Assuming that, the delete or any other button is also part of the same row
     Place below code in button click event in the code (Visual Studio). This is straight forward in case
     of deleting current row in the repeating table
   public void DeleteRow_Clicked(object sender, ClickedEventArgs e)
   {

      // Write your code here.
      e.Source.DeleteSelf();
   }
2. Assuming that the button is out side of the repeating table. This is also very much easy
     public void Button1_Clicked(object sender, ClickedEventArgs e)
   {
      XPathNavigator nav = e.Source.CreateNavigator();
      string ProjectId = nav.SelectSingleNode("my:ProjectID",  
                         NamespaceManager).Value;
      string ProjectTitle = nav.SelectSingleNode("my:ProjectTitle",
                             NamespaceManager).Value;   }    
    
     Note: Replace ProjectID & ProjectTitle with your respective fields.


Cheers!
Vamsi