DataConnections in InfoPath 2007- Part I

What is InfoPath?

Infopath is a very powerful Form Designer. One of the major advantages of InfoPath is, it is wholely XML-based and the form design is based on XSD. Infopath 2007 comes with MS Office 2007.

What is Form Template and Form Data?

Form Template is nothing but the Form Design. Form Data is what we store through the Form.

Data Connections

It is the connection to Data Source. They allow to do read, write operations over the Data Source. There are many types of connections in InfoPath. They are :

1. SQL Server

2. Web Service

3. XSD

4. XML

5. Data Connection Library (DCL)

6. E- mail

7. SharePoint Document Library

The objective of this topic is to explain “How to work with DataConnections in InfoPath 2007”. So, We are not going to discuss the basics of InfoPath in detail.

We are going to see here a sample in a step by step manner , which shows you the basic of Data Connection in InfoPath. Here InfoPath is the front-end and MS Sql Server 2005 is the back-end.

A sample displaying an Employee Information from the back-end

Let us see the sample in a step by step manner with back-end and fron-end flow.

Back-end (SQL Server):

We are creating a PayRoll Database for this sample. You can either create this database or use your existing database. But you need to create the following Employee table.

The code to create employee table:

CREATE TABLE [dbo].[Employee](

[EmpId] [bigint] IDENTITY(1,1) NOT NULL,

[EmpName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[DOJ] [datetime] NOT NULL,

[Designation] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Department] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Salary] [money] NOT NULL,

CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED

(

[EmpId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Code to insert data into the table:

INSERT INTO [Employee]([EmpName],[DOJ],[Designation],[Department],[Salary])

VALUES ("Bill Gates","01/01/1983","CEO","Computers","12222222.0000")

Simillarly insert few records.

Front-End (InfoPath):

Step 1 : Start InfoPath

Click Start > All Programs > MS Office > MS InfoPath 2007 as show in Fig. 1

Step 2: Decide Way of Designing

You will be shown a ‘Getting Started’ Window as shown in Fig. 2.

Click on Design a Form Template.

Step 3: Select Template

Select Database , Check whether “Enable browser-compatible features only” is checked. If not then check it. Form Template Option button will also bet selected. Then Click Ok.

Step 4: Select Database

Data Connection Wizard will be shown .

Click on Select Database.

Double click on +NewSQLServerConnection

Enter the server name.

Select “Use Windows Authentication”

Click Next.

Choose PayRoll database from the combo box.

Select Employee table , then Click Next.

Click on Finish.

Select desired fields there in this screen.

Click Next

Click Finish in the next Screen.

Step 5: Design Form

Expand the queryFields in the DataSource Window.

Then Drag EmpId into the form and drop into “Drag query fields here” area.

Simillarly expand dataFields and drag remaining fields into “Drag data fields here” area.

Then the resultant form will look like in the Fig.10

Now the Form creation and data connection is over. That’s all the database form generation.

The final step is to test the Form we created above.

Step 6: Running and Testing the Form

Click Preview in the toolbar.

Enter 1 in the EmpId field.

Click Run Query.

A security screen will be shown, click Yes in it. After that the form will fetch the corresponding values for the empId “1”, as shown in the following diagram. You can test with your desired values and it will display the result.

Click Close Preview to exit.

Have fun creating forms.

Purpose and Usage of Partial Class

Purpose and Usage of Partial Class

What is a Partial Class (or struct or interface)?
A partial class is a feature in which you split a class definition into two or more files. All the parts are joined together when the application is compiled.

Situations on which it can be used:
When multiple programmers need to work on a same class then Partial class is an obvious choice.

Conditions:
All files should use Partial keyword
All files should be available at compile time
All files should have same scope specifies.

On which it cannot be used:
It can’t be applied on delegate or enumeration.

An Example:
Let us consider an example where we have 3 .cs files one for educational marks, one for sports marks and one for Cumulative marks and average of a student. A single class name Marks is split across these three .cs files respectively.

1. Create a Visual Studio Windows Application Project.

2. Then add 3 .cs files with the following names respectively EducationMarks.cs, SportsMarks.cs, and Cumulative.cs.

The three .cs files used in the example are:
EducationMarks.cs
SportsMarks.cs
Cumulative.cs

The Marks class is split into the three .cs files. The three .cs files are given below with explanations.

EducationMarks.cs
This class has an integer variable eduMarks in it. There is one method to set value to the eduMarks called setEducationMark and one method to get value from the variable called getEducationMark.

using System;
using System.Collections.Generic;
using System.Text;

namespace PartialSamp
{
partial class Marks
{
int eduMarks;

public void setEducationMark(int em)
{
eduMarks = em;
}

public int getEducationMark()
{
return eduMarks;
}
}

}

SportsMarks.cs
This class has an integer variable sportMarks in it. There is one method to set value to the sportMarks called setSportsMark and one method to get value from the variable called getSportsMark.

using System;
using System.Collections.Generic;
using System.Text;

namespace PartialSamp
{
partial class Marks
{
int sportMarks;

public void setSportsMark(int spm)
{
sportMarks=spm;
}

public int getSportsMark()
{
return sportMarks;
}

}
}

Cumulative.cs
This class is the one which sums the sports and educational marks to get the total and then finds the average out of it.

using System;
using System.Collections.Generic;
using System.Text;

namespace PartialSamp
{
partial class Marks
{
int totMarks;
float avg;

public int getTotMarks()
{
totMarks = sportMarks + eduMarks;
return totMarks;
}

public float getavgMarks()
{
return totMarks/2;
}
}
}

Note:

You can even find the intellisense of the methods of prior .cs files in the current files. For example after typing the EducationMarks class, you will get the methods in it being populated in intellisense in the SportsMarks and Cumulative classes.

Visual Design:
Create 4 text boxes and 4 labels and a button. Name them as shown in the Figure 1. Then type the following code in the button1_click,

private void button1_Click(object sender, EventArgs e)
{
Marks m = new Marks();
m.setEducationMark(int.Parse ( txtEM.Text ) );
m.setSportsMark(int.Parse(txtSM.Text));

txtTM.Text = m.getTotMarks().ToString() ;
txtAvg.Text = m.getavgMarks().ToString();
}

Similarly you can try with struct and interface.