Communication between InfoPath and databases

Communication between InfoPath and databases

1. Create a form template in vs to save the data to the database.

Create an infopath form template:

Open vs2008, create a Project, select Visual C # à Office-à 2007 -- à InfoPathFormTemplate in the Project Types area, and enter the Name of the infopath form template in Name, such as PurcharForm. For example:

Start designing the form:

1. Click the control in "design task" on the right side and use the control to design the form;

2. add a data source, click the data source in "design task" on the right, select "manage data connections", and click "add". In the new data source, select "receive data only" and click "Next, select "Database", click "Next", select "select database", and click "create data source". Then, click "Next.

3. Bind the data source of the duplicate table and select the fields to display.

4. Click the submit button to create a rule, select "Submit using data connection", and then select "Submit to host environment ".

5. After the form is designed, start "Check the design scheme" and select "design a form template that can be opened in a browser or InfoPath" for compatibility. Select "full trust" for the security level ".

6. Save the template.

7. Create an information initialization list "WFMetaData". The value of "PurcharIDFormat0" in the "Confirm application number rules" column is "P-, yyyyMMddHHmmA ". Use this rule to create a scheduler ticket number.

8. Insert ----- à Loading event: first create a data source and read the initialization information. The Code is as follows:

Public void FormEvents_Loading (object sender, LoadingEventArgs e)

{

Try

{

// Obtain the object for the submitted connection. FileSubmitConnection corresponds to the connection class submitted to the SharePoint document library.

FileSubmitConnection osssubmit = (FileSubmitConnection) this. DataConnections ["OSSSubmit"];

XPathNavigator fieldFillAuthor = this. MainDataSource. CreateNavigator (). SelectSingleNode ("/my: myFields/my: Tabulation", NamespaceManager );

XPathNavigator fieldFillID = this. MainDataSource. CreateNavigator (). SelectSingleNode ("/my: myFields/my: scheduler Ticket No.", NamespaceManager );

// You can obtain the website set of the Document Library Based on the FolderUrl attribute of the submitted connection (in some cases, the workflow website is not on the Root site, so it is not suitable here)

SPSite mysite = new SPSite (osssubmit. FolderUrl );

String PurcharWebName = ConfigurationManager. deleetask[ "PurcharWebName"];

// Based on the FolderUrl attribute of the submitted connection, you can obtain the website of the Document Library (directly obtain the current website)

Using (SPWeb myweb = mysite. OpenWeb (PurcharWebName) // actually needs to be changed to OpenWeb ("/wf /")

{

If (fieldFillAuthor! = Null) // automatically assigns a value to the, in the format of "Chinese display name (domain account )"

{

FieldFillAuthor. SetValue (myweb. CurrentUser. Name + "(" + myweb. CurrentUser. LoginName + ")");

}

SPList idList = myweb. Lists ["WFMetaData"];

String idFormat = "";

String idFormat2 = "";

Foreach (SPListItem item in idList. Items)

{

If (item. Title = "rule for confirming application number ")

{

IdFormat = item ["PurcharIDFormat0"]. ToString (). Split (new char [] {','}) [0];

IdFormat2 = item ["PurcharIDFormat0"]. ToString (). Split (new char [] {','}) [1];

IdFormat = idFormat + DateTime. Now. ToString (idFormat2 );

Break;

}

}

If (fieldFillID! = Null) // rule for confirming the application number

{

FieldFillID. SetValue (idFormat );

}

}

}

Catch

{}

Complete the information and serialize the information data to the database. The FormSave method is created to serialize the primary data of the application form to the database in the serialization path database and the PurcharToDB method. The Code is as follows:

/// <Summary>

/// Serialize the duplicate table data to the database

/// </Summary>

Private void FormSave ()

{

// Write code here.

String strResult = "";

Int currentPid =-1;

Try

{

// Obtain the object for the submitted connection. FileSubmitConnection corresponds to the connection class submitted to the SharePoint document library.

FileSubmitConnection osssubmit = (FileSubmitConnection) this. DataConnections ["OSSSubmit"];

// You can obtain the website set of the Document Library Based on the FolderUrl attribute of the submitted connection (in some cases, the workflow website is not on the Root site, so it is not suitable here)

SPSite mysite = new SPSite (osssubmit. FolderUrl );

// Read the list records

String PurcharListName = ConfigurationManager. deleetask[ "MEPPurcharListName"];

String PurcharWebName = ConfigurationManager. deleetask[ "PurcharWebName"];

// Scheduler Ticket No.

String PurcharID = MainDataSource. CreateNavigator (). SelectSingleNode ("/my: myFields/my: scheduler Ticket No.", this. NamespaceManager). Value;

Using (SPWeb myweb = mysite. OpenWeb (PurcharWebName) // actually needs to be changed to OpenWeb ("/wf /")

{

// Store data to the database

CurrentPid = PurcharToDB (myweb. CurrentUser. LoginName, myweb. CurrentUser. Name );

// Traverse each item in the repeated table group5

XPathNavigator NodeIter = this. MainDataSource. CreateNavigator (). SelectSingleNode ("/my: myFields/my: group5", this. NamespaceManager );

// Define the operation string

String strBatch = "";

SPList list = myweb. Lists [PurcharListName];

Foreach (XPathNavigator myf in NodeIter. SelectChildren (XPathNodeType. Element ))

{

// For each row in the duplicate table, go to the database for search. If it is found, use Update. If it is not found, use New

PurcharData purData = new PurcharData ();

PurData. ID =-1;

PurData. PID = currentPid;

PurData. Number = myf. SelectSingleNode ("my: No.", NamespaceManager). ValueAsInt;

PurData. SBName = myf. SelectSingleNode ("my: Material name", NamespaceManager). Value;

PurData. SBType = myf. SelectSingleNode ("my: specification model", NamespaceManager). Value;

PurData. Shuliang = (float) myf. SelectSingleNode ("my: quantity", NamespaceManager). ValueAsDouble;

PurData. Address = myf. SelectSingleNode ("my: Production Plant", NamespaceManager). Value;

PurData. GetTime = myf. SelectSingleNode ("my: arrival time", NamespaceManager). ValueAsDateTime;

PurData. UnitPrice = (float) myf. SelectSingleNode ("my: budget unit price", NamespaceManager). ValueAsDouble;

PurData. Totals = (float) myf. SelectSingleNode ("my: Total Budget", NamespaceManager). ValueAsDouble;

PurData. Notes = myf. SelectSingleNode ("my: Remarks", NamespaceManager). Value;

PurData. Results = myf. SelectSingleNode ("my: Execution record", NamespaceManager). Value;

PurData. Created = DateTime. Now;

PurData. AuthorId = myweb. CurrentUser. LoginName;

PurData. AuthorName = myweb. CurrentUser. Name;

PurData. JHDID = PurcharID;

Wf_PurcharData wfPurData = new wf_PurcharData ();

If (wf_PurcharData.Exists (PurcharID, purData. Number, purData. SBName, purData. SBType ))

{

// Update if it is found

WfPurData. Update (purData );

}

Else

{

// Add if not found

WfPurData. Add (purData );

}

//// Check the following: whether the record is deleted in this form editing.

// If (! StillExist)

// {// If the old item has been deleted during the new editing process, execute the delete Method

// StrBatch + = "<Method ID = '" + MethodId. toString () + "'cmd = 'delete'> <Field Name = 'id'>" + item. ID. toString () + "</Field> </Method> ";

//}

}

} // Using ended

}

Catch {}

}

/// <Summary>

/// Serialize the master data of the application form to the database

/// </Summary>

/// <Param name = "userID"> domain account </param>

/// <Param name = "userName"> display name </param>

/// <Returns> </returns>

Private int PurcharToDB (string userID, string userName)

{

Try

{

XPathNavigator xpath = this. MainDataSource. CreateNavigator ();

If (xpath! = Null)

{

XPathNavigator fieldJHDID = xpath. SelectSingleNode ("/my: myFields/my: scheduler Ticket No.", NamespaceManager );

XPathNavigator fieldZK = xpath. SelectSingleNode ("/my: myFields/my: ", NamespaceManager );

XPathNavigator fieldGC = xpath. SelectSingleNode ("/my: myFields/my: Domestic", NamespaceManager );

XPathNavigator fieldSBXC = xpath. SelectSingleNode ("/my: myFields/my: onsite device purchase", NamespaceManager );

XPathNavigator fieldWZGN = xpath. SelectSingleNode ("/my: myFields/my: materials purchased domestically", NamespaceManager );

XPathNavigator fieldWZXC = xpath. SelectSingleNode ("/my: myFields/my: onsite Material Purchase", NamespaceManager );

XPathNavigator fieldCountry = xpath. SelectSingleNode ("/my: myFields/my: country of the project", NamespaceManager );

XPathNavigator fieldProjectName = xpath. SelectSingleNode ("/my: myFields/my: Project name", NamespaceManager );

XPathNavigator fieldBeginTime = xpath. SelectSingleNode ("/my: myFields/my: Commencement Date", NamespaceManager );

XPathNavigator fieldFillTime = xpath. SelectSingleNode ("/my: myFields/my: ", NamespaceManager );

XPathNavigator fieldNumPersonId = xpath. SelectSingleNode ("/my: myFields/my: contact/my: Person/my: AccountId", NamespaceManager );

XPathNavigator fieldNumPersonName = xpath. SelectSingleNode ("/my: myFields/my: contact/my: Person/my: DisplayName", NamespaceManager );

XPathNavigator fieldFirstPersonId = xpath. SelectSingleNode ("/my: myFields/my: contacter/my: Person/my: AccountId", NamespaceManager );

XPathNavigator fieldFirstPersonName = xpath. SelectSingleNode ("/my: myFields/my: contacter/my: Person/my: DisplayName", NamespaceManager );

XPathNavigator fieldInitComment = xpath. SelectSingleNode ("/my: myFields/my: comment", NamespaceManager );

XPathNavigator fieldTotalsMoney = xpath. SelectSingleNode ("/my: myFields/my: total", NamespaceManager );

Purchar purchar = new Purchar ();

Purchar. PID =-1;

Purchar. JHDID = fieldJHDID. Value;

Purchar. ZK = fieldZK. ValueAsBoolean;

Purchar. GC = fieldGC. ValueAsBoolean;

Purchar. SBXC = fieldSBXC. ValueAsBoolean;

Purchar. WZGN = fieldWZGN. ValueAsBoolean;

Purchar. WZXC = fieldWZXC. ValueAsBoolean;

Purchar. Country = fieldCountry. Value;

Purchar. ProjectName = fieldProjectName. Value;

Purchar. BeginTime = fieldBeginTime. ValueAsDateTime;

Purchar. FillTime = fieldFillTime. ValueAsDateTime;

Purchar. FillPersonId = userID;

Purchar. FillPersonName = userName;

Purchar. NumPersonId = fieldNumPersonId. Value;

Purchar. NumPersonName = fieldNumPersonName. Value;

Purchar. FirstPersonId = fieldFirstPersonId. Value;

Purchar. FirstPersonName = fieldFirstPersonName. Value;

Purchar. InitComment = fieldInitComment. Value;

Purchar. TotalsMoney = (float) fieldTotalsMoney. ValueAsDouble; // Double Type

Wf_Purchar wfPurchar = new wf_Purchar ();

Return wfPurchar. Add (purchar );

}

Else

{

Return-1;

}

}

Catch (Exception)

{

Return-1;

}

}

9. In the submit button event, call the FormSave () method and save it to the database.

10. In the last step, publish it to the SharePoint Server of InfoPath Form Services.

11. upload to the management center, and choose Application Management> InfoPath Form Services> Configure InfoPath Form Services> select embedded SQL authentication and Data Source Authentication (user Form template ),

Application Management-à InfoPath Form Services --- à configure InfoPath Form Services ---- à upload Form templates and activate them to the website set,

12. Create a new form library ---> set form library -----> advanced settings --- set content type to allow management of content type ----> enable the browser to display documents as pages.

Content type ----> Add from existing website content type ------> Add your uploaded form template

2. Create a form template in the office to read the values in the database.

1. Office --- à Microsoft office InfoPath 2007 ----- à design form template --------- à blank ---------- à OK

2. Start designing the form:

L click the control in "design task" on the right side and use the control to design the form;

L add a data source, click the data source in "design task" on the right, select "manage data connections", click the Add button, select "receive data only" in the new data source, and click Next, select "Database", click "Next", select "select database", and click "create data source". Then, click "Next.

L bind the data source of the duplicate table and select the fields to display.

L click the submit button to create a rule, select "Submit using data connection", and then select "Submit to host environment ".

L after the form is designed, start "Check the design scheme" and select "design a form template that can be opened in a browser or InfoPath" for compatibility. Select "full trust" for the security level ".

L save the template.

L tool ------ à programming ----- à Loading event, add the Code as follows:

Try

{

// Write code here.

// Data source Association

AdoQueryConnection myAdoQueryConnection =

(AdoQueryConnection) (this. DataConnections ["wf_PurcharData"]);

// Obtain the query field

XPathNavigator wfNum =

CreateNavigator (). SelectSingleNode ("/my: myFields/my: wfNum ",

NamespaceManager );

// Field Value

String wfNumID = wfNum. InnerXml;

// Use the keyword field for database query

String tmpConn = myAdoQueryConnection. Command;

MyAdoQueryConnection. Command = tmpConn + "" + "where" "JHDID" "= '" + wfNumID + "'";

MyAdoQueryConnection. Execute ();

}

Catch {}

L design the form as follows:

L preview the form.

L publish to the specified location of the network.

Web. config
Add the following content to the appsonstrings node:

<Deleetask>
<Add key = "MEPPurcharListName" value = "material procurement application form"/>
<Add key = "PurcharWebName" value = "/wf"/>
<Add key = "gsegc_ConnectionString" value = "Password = sa; User ID = sa; Data

Source = litware; Initial Catalog = AD; "/>
<Add key = "PowerUser" value = "administrator"/>
<Add key = "PowerPwd" value = "sa"/>
<Add key = "PowerDomain" value = "litware"/>

</AppSettings>

<ConnectionStrings>
<Add name = "GSEGC_ConnectionString" connectionString = "Password = sa; User

ID = sa; Data Source = litware; Initial Catalog = AD; "providerName =" System. Data. SqlClient "/>
<Add name = "GSEGC_LogConnectionString" connectionString = "Password = sa; User

ID = sa; Data Source = litware; Initial Catalog = AD; "providerName =" System. Data. SqlClient "/>
</ConnectionStrings>