Business Data Catalog

Generating our Application Definition File.

Open up BDC Meta Man on the PC you want to work on (it doesn’t necessarily need to be the MOSS server). To connect to SQL Server click the drop down arrow on the ‘Connect to data source’ button in the top right of the screen and choose SQL Server.

In the ‘Connect To Data Source’ box enter the name of your Server and SQL Server instance if necessary, and select the Authentication mode. If you are using SQL Authentication you will also have to enter your SQL user account details.

Upon successfully connecting to SQL Server you’ll see the top window of BDC Meta Man populate with a list of databases. You can expand each database to see the list of tables by clicking the plus sign next to each database name

Now we can see listed all the tables for this particular database. With BDC Meta Man we think of each table as being its own entity. We create an entity by dragging and dropping a table onto our design surface which is the large grey area to the right.

Upon dragging and dropping the table, the entity is created with the Finder and SpecificFinder methods. You can see a view of it on the design surface with the fields listed that are returned by the Finder method. For this walk through drag the dbo.Department table across

When you have two entities that have a primary key->foreign key relationship between them you can create this relationship as a BDC association. This will allow us to make use of the BDC Related Data web part. To add an association, drag and drop the dbo.Employee table onto our design surface to create an Employee entity. You can create an association simply by dragging and dropping between the fields that are related. With our two entities it is the DepartmentID of the Department entity, and DepartmentID of the Employee entity. Drag and drop from the Department entity DepartmentID to the Employee entity DepartmentID

Doing this drag and drop creates the association for us, and any methods required to bring data back for the BDC Related Data Web Part.

Now all we need to do is generate our application definition file. First we need to set the path to save the file. We set this location by going Configuration -> XML OutPut File from the main options. In the Filename textbox you can either manually type the location and the filename or use the browse button

Once you have saved the filename and path to create your application definition file to simply click the green play button and your application definition file will get generated.

Once generated you get the option to open the XML file in your registered XML editor. If you click yes it’ll probably open it up in Internet Explorer for you to view. Look how much XML it has generated for you!

Importing our Application Definition File.

Remember the Business Data Catalog is only part of MOSS 2007 Enterprise Edition. You can download a trial of MOSS 2007 Enterprise Edition here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=2E6E5A9C-EBF6-4F7F-8467-F4DE6BD6B831&displaylang=en

Once installed and correctly configured we need to import our application definition file.

Open up MOSS 2007 Central Administration by either browsing to it or through the start menu on the server running MOSS 2007 – Start -> All Programs -> Microsoft Office Server -> SharePoint 3.0 Central Administration.

Navigate to the Shared Service Provider Administration (SSP Admin) page from the left quick launch menu

In the SSP Admin page you’ll see a whole section devoted to the BDC. Click on ‘Import application definition’.

Use the browser button to locate our application definition file, or type in the path and filename yourself. Leave all the other options as they are for the time being, and click ‘Import’

MOSS 2007 will now go through and validate your application definition file is correct, once it is done you will get a success page. Clicking past that you will see the administration page for the BDC application we just imported

Using our BDC application in MOSS 2007

Browse to the page where you want to add the Business Data Web Part and edit the page. Click ‘Add a Web Part’ button to display the web part gallery.

Scroll down to the ‘Business Data’ section and click the checkboxes for the ‘Business Data List’ web part and the ‘Business Data Related List’ web part. Click the ‘Add’ button to add the web parts to our web part zone. Once the Web Part Gallery form disappears you’ll see the two BDC web parts added to our web part zone. Now we need to configure them. First the Business Data list, click the ‘Open the tool pane’ link to open the web part tool pane down the right hand side of the screen.

We need to select what BDC entity our BDC data list is going to display, to do this click on the Browse button.

From the pop up window select our dbo.Department Entity and click OK. Then Click OK at the bottom of the Web Part Tool Pane to configuring this web part.

Now we need to configure the BDC Related List web part. Again click on the ‘Open the tool pane’ and select the dbo.Employee entity from the Browse button as you did for the Business Data List. You’ll see when you have selected the Employee entity the relationship drop down is populated and selected with our relationship method. If our entity had multiple association methods this is where we’d select which one to use.

Click ok to close the web part tool pane. The final configuration step we need to do is to connect the web parts together so when a Department is selected, only the Employees for that department are listed. To do this click on the small edit button for the Business Data Related List web part and go:

Edit -> Connections -> Get Related Item From -> dbo.Departments

And there we go. We should now be able to bring back all the Departments by clicking on the ‘Retrieve Data’ button on our Business Data List. Upon selecting a department from the returned values the Business Data Related List will bring back the employees for the selected department.

InfoPath 2007 Top 10 benefits

1) Deploy InfoPath forms as Microsoft Office Outlook e-mail messages.

With Office InfoPath 2007, you can deploy forms as Outlook e-mail messages, so colleagues can complete forms without leaving the familiar Outlook environment. After you’ve collected the information in Office Outlook 2007, you can export it to a Microsoft Office Excel spreadsheet or merge the data back to a single InfoPath form.
2) Extend business processes beyond the firewall.

Office InfoPath 2007 and InfoPath Forms Services make it easy to extend forms solutions beyond your firewall because you can complete forms using many different Web browsers and mobile devices.

3) Easily convert Microsoft Office Word documents and Excel spreadsheets to InfoPath forms.

By converting Word documents and Excel spreadsheets to InfoPath form templates, you can build data integrity, improve version control, and add structure to information gathering by converting legacy documents to rich InfoPath form templates.

4) Make complex form design easier.

Office InfoPath 2007 helps people build forms with a simple drag-and-drop interface and provides support for prebuilt template parts and shared data connections.

5) Create PDF or XPS records of your form data.

With the addition of a free plug-in, you can create a Portable Document Format file (PDF) or XML Paper Specification (XPS) version of your completed InfoPath form for archival and records management.¹

6) Master your forms workflow using Office InfoPath 2007 and Microsoft Office SharePoint Server 2007.

Use the integrated workflow management tools in Office SharePoint Server 2007 to drive information-gathering processes efficiently.

7) Design a single form for Web browsers and Office InfoPath 2007 clients.

Office InfoPath 2007 includes a design checker to help ensure consistency for forms deployed to InfoPath Forms Services.

8) Enhance protection of important information in InfoPath form templates.

Office InfoPath 2007 includes support for information rights management to help protect forms from inappropriate usage and distribution.

9) Centralize forms management for the entire organization.

InfoPath Forms Services of Office SharePoint Server 2007 enables organizations to centrally manage electronic forms by providing access to only those users who have a secure connection.

10) Develop advanced forms solutions with Office InfoPath 2007 and Microsoft Visual Studio 2005.

Build InfoPath forms solutions by creating InfoPath projects hosted within Visual Studio 2005, so developers can build solutions using managed code and the Microsoft .NET Framework.

Inserting Data from Info path to database through object model

Let me tell how to start the Infopath from the scratch so that beginners will can also cope up with us

Go to Start->All Programs->Microsoft Office->Click Microsoft Office Infopath 2007.Now you will be able to see the form as below

Now click the ‘Design a Form Template’ under ‘Design a form’ section.Now you will get the another form as below

Before clicking OK confirm whether ‘Enable browser-compatible features only’ option has checked or not.After clicking OK now you will be able to see a blank form as shown below

Now click the ‘Layout’ option under Tasks section.i have dragged and droped ‘Table with Title’ as per my requirement

Now to get the controls click ‘Design Tasks’ and from ‘Controls’ section utlilize the controls which ever you want.Finally I designed my Info path form as below

Now if we insert any data from the infopath it should get updated in the back end database.Now we need to check some settings before writing the code in VS2005.In your info path form go to Tools Menu->Form Options.In the Form options wizard go to ‘Security and Trust’ option and uncheck that ‘Automatically determine security level(recommended)’.Now check the ‘Full Trust’ option as shown below

Click OK

Now I have gone to the ‘button properties’ by right click of that button.In the Label you can enter the button text that you wish .In my case I have entered ‘Submit’ and I have clicked ‘Edit from code’ and it has displayed the following message

Click OK to save your form. After saving again go to ‘Edit from code’ of the Submit button control. Now it opens in Visual Studio mode first thing is we need to add the using System.Data.SqlClient;

Now in the button click event write the code as

XmlNamespaceManager ns = this.NamespaceManager;

XPathNavigator xnDoc = this.MainDataSource.CreateNavigator();

XPathNavigator xnEmpId = xnDoc.SelectSingleNode("/my:myFields/my:field1", ns);

XPathNavigator xnEmpName = xnDoc.SelectSingleNode("/my:myFields/my:field2", ns);

XPathNavigator xnDeptName = xnDoc.SelectSingleNode("/my:myFields/my:field3", ns);

//connection string

string strConnection = "Data Source=Servername\\SQLEXPRESS;Initial Catalog=Sudheer;Integrated Security=SSPI";

SqlConnection conn = new SqlConnection(strConnection);

conn.Open();

SqlCommand cmd = new SqlCommand("insert into Test values('" + xnEmpId + "','" + xnEmpName + "','" + xnDeptName + "')", conn);

cmd.ExecuteNonQuery();

conn.Close();

I hope no need to explain regarding XmlNamespaceManager,XPathNavigator as you would be aware about XML concepts.Now as we need to capture the data of the fields through SelectSingleNode and there we need give the exact XPath of the field.For that what we have to do is

Go to your Info Path in the Design Tasks of right pane click Data Source there you will be able to see the fields.Right click on the field and click Copy XPath and paste in the SelectSingleNode as follows

XPathNavigator xnEmpId = xnDoc.SelectSingleNode("/my:myFields/my:field1", ns);

Finally we need to write the code to connect to the database in the Data Source.We need to write the sql quey to insert the data and finally close the sql connection.Now Run debug your application and check it out in the database.Now you can check it in your database which ever you have inserted in the Info path.