About Me

Having 12 years experience in Microsoft technologies.Since more than 7 years working in SharePoint technologies. Expert in providing consultation for SharePoint projects. Hands on with development and administration.

Wednesday 19 November 2014

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



1 comment:

  1. Very helpful article ! I was always curious about all these complex algorithms that are being used in these ssl encryptions.

    ReplyDelete