SQL Database Integration

Re: Query DB By Stored Procedure OnLoad and Set Field Value

RobSil1976 — Fri, 01 Mar 2013 16:59:59 GMT

Hilary,

That was what I needed! :) My code is below in case anyone else tries something similar. Basically I use the DBXL AD Service (as a Data Control in my form) to find the department of the current user, then I query my DB Stored Procedure to get a New Form ID. I "only" do this if the Form is "new".

   public void FormEvents_Loading(object sender, LoadingEventArgs e)
        {
            if (this.New)
            {
                string currentUser = System.Environment.UserName.ToString();

                string department = GetDepartmentOfCurrentUser(currentUser);
                string formNumber = GetFormNumberByUserAndDepartment(department, currentUser);


                XPathNavigator xn = this.MainDataSource.CreateNavigator();
                XPathNavigator xnFormNumber = xn.SelectSingleNode("/my:myFields/my:Form_Number", this.NamespaceManager);

                xnFormNumber.SetValue(formNumber);
            }
        }

        private string GetFormNumberByUserAndDepartment(string department, string currentUser)
        {
            //Set the Command for the Query Adapter of the Data Source. Incorporate the 
            //parameter values that you want to use.
            string query = "DECLARE	@return_value int, @FormNumber varchar(50) " +
                "EXECUTE [dbo].[FetchNextFormNumber] @UserName = N'" + currentUser + "', @Department = N'" + department + "', @FormNumber = @FormNumber OUTPUT SELECT @FormNumber AS formNumber";
                        
            AdoQueryConnection myAdoQueryConnection =
                    (AdoQueryConnection)(this.DataConnections["FormNumberLookup"]);
           
            string oldCmd = myAdoQueryConnection.Command;
            myAdoQueryConnection.Command = query;
            myAdoQueryConnection.Execute();
            myAdoQueryConnection.Command = oldCmd;

            XPathNavigator myNavigator =
              this.DataSources["FormNumberLookup"].CreateNavigator().SelectSingleNode("//dfs:myFields/dfs:dataFields/d:row/@formNumber", this.NamespaceManager);
            
            return myNavigator.Value;
        }

        private string GetDepartmentOfCurrentUser(string currentUser)
        {
            //Set the Command for the Query Adapter of the Data Source. Incorporate the 
            //parameter values that you want to use.
                        
            WebServiceConnection myWSConnection =
                    (WebServiceConnection)(this.DataConnections["GetMyInfo"]);

            myWSConnection.Execute();
            XPathNavigator myNavigator =
              this.DataSources["GetMyInfo"].CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/tns:GetMyInfoResponse/tns:GetMyInfoResult/tns:ADProp[tns:Key[text()='department']]/tns:Value", this.NamespaceManager);
              return myNavigator.Value;
        }
    }

Re: Query DB By Stored Procedure OnLoad and Set Field Value

Hilary Stoupa — Thu, 28 Feb 2013 19:51:26 GMT

Hi - the returned data should be in the MyNumberLookup data source. Try this link, see if gets you going in the right direction. :)

Re: Query DB By Stored Procedure OnLoad and Set Field Value

RobSil1976 — Thu, 28 Feb 2013 19:19:18 GMT

As a follow-up... I read this description of the classes:
http://msdn.microsoft.com/en-us/library/microsoft.office.infopath.dataconnection.execute(v=office.14).aspx

When you "Execute" are the results only bound to a field automatically, in that you can never iterate over them programatically? The complete and total lack of any code after an "Execute" seems to give me that impression... but not sure.

Thx,

Rob

Query DB By Stored Procedure OnLoad and Set Field Value

RobSil1976 — Thu, 28 Feb 2013 15:42:27 GMT

Greetings,

I have a stored procedure on my SQL Server 2008 that I have tested as working fine by SQL Queries etc.. It does some business logic but end result is it returns a varchar(8) as an OUTPUT parameter. I am attempting to call that SP from Infopath via the OnLoad method of the form (C# format) and then show the result in a simple test field. Just am stuck trying to find out how to read the result from the query and use it? I am familiar with ASP.NET coding so thinking maybe I have a conceptual issue as I am new to Infopath.

My OnLoad code is:

public void FormEvents_Loading(object sender, LoadingEventArgs e)
        {

            XPathNavigator xn = this.MainDataSource.CreateNavigator();
            XPathNavigator xnPONumber = xn.SelectSingleNode("/my:myFields/my:MyID_Number", this.NamespaceManager);
            string currentUser = System.Environment.UserName.ToString();

            //Load the appropriate Number from the DB based upon username of current user AND their department.
            //SP has business logic to find right PO Number.
            string department = "TestDept";

            
            //Set the Command for the Query Adapter of the Data Source. Incorporate the 
            //parameter values that you want to use. 
            string theNumber = "not set";
            string query = "DECLARE	@return_value int, @RetNumber varchar(50) " +
            "EXECUTE [dbo].[FetchNextNumber] @UserName = N'JohnDoe', @Department = N'TestDeptHardcoded', @TheNumber = @RetNumber OUTPUT SELECT @RetNumber AS theNumber";


            AdoQueryConnection myAdoQueryConnection = (AdoQueryConnection)(this.DataConnections["MyNumberLookup"]);
            string oldCmd = myAdoQueryConnection.Command;
            myAdoQueryConnection.Command = query;
            myAdoQueryConnection.Execute();
        }

After the "Execute();"  how do I read the results out so I can put them into my document?