Stored Procedure - User Profile Import

I was asked to build a utility to import the user profiles from a CSV file to the FBA Database in Sharepoint. I then came up with a solution of moving the contents from the CSV to the Temporary table and then move the columns to the respective tables using INSERT...SELECT commands. I 'm enclosing the SP that does exactly what i say.

1.Create a Temp Table : To hold the contents of the CSV file temporarily

USE [MyDatabase]
GO
CREATE TABLE [dbo].[Temp]
( [EmployeeName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmployeeID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Salutation] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PhNo] [numeric](18, 0) NULL,
[EmailID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )

2.Use BULK INSERT to copy the contents from the CSV to the Temp Table

DECLARE @SQL VARCHAR(2000)
SET @SQL ='BULK INSERT Temp FROM '+@FilePath+' WITH(FIRSTROW=2,FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'
EXEC(@SQL)
Parameters used -
FilePath is the input parameter for the SP
FIRSTROW indicates that copying should start from 2nd Row
FIELDTERMINATOR indicates comma separated values
ROWTERMINATOR indicates ROW terminator for the CSV


3.So,we have the CSV contents in the Temp table. Our job is now to move the columns to the respective tables using INSERT.... SELECT command. But,we should also consider the impediments in inserting contents directly to the respective tables because of the existence of the primary keys in the table .
However,we can look at this issue in 2 ways .One is inserting columns by setting primary key as read only and the other is , inserting columns by setting IDENTITY_INSERT "ON" where we explicitly mention values for the keys.

SET IDENTITY_INSERT [EmpTbl] ON
INSERT EmpTbl(EmployeeName,EmployeeID,Salutation)
SELECT EmployeeName,EmployeeID,Salutation FROM Temp
SET IDENTITY_INSERT [CLDirectory] OFF

As,mentioned before it can also be made read-only without setting IDENTITY_INSERT key like

INSERT EmpTbl(EmployeeName,EmployeeID,Salutation)
SELECT EmployeeName,EmployeeID,Salutation FROM Temp

4.Once,we have inserted columns to the table we need to TRUNCATE and DROP the table.

TRUNCATE TABLE Temp
DROP TABLE Temp

Once,we have the SP we can then write a console/windows application for passing SQL parameters to execute the Stored procedure.

Debugging in Sharepoint

Debugging in sharepoint is the same as any other web application like ASP.NET, the only thing that we should make sure before debugging is that the debugger is attached to the process.
Now,lets see one scenario where we need to debug the code.
To configure VS debugger one can follow these steps :

Go to VS Tools >Options menu

Make sure that you have unchecked the option and then click 'OK'
Then,go to Debug > Attach to process menu to attach the code to the w3wp process as shown below

It can be noticed from the screenshot that there may be several instances of the process running, to know are you attached to the correct process, first attach to that process ,press Ctrl +Alt + U and check if your dll has been loaded or not(See below screenshot) .If its loaded then, you are attached to the correct process or else try attaching other instances till you could see your dll loaded properly.

CustomWebPart.dll is my dll that has been loaded, so my code has been attached to the right process.
Once,this is done on performing an operation your breakpoint should get hit ,something like this

This kind of technique can be used to debug the code that has its dll in GAC (like web parts and the custom aspx pages in the layouts folder )But, this technique cannot be used for the custom pages that uses the inline code approach ,since its dll won't be in the GAC.

To debug the inline code , go to the web.config file of that site, set debug = true in the compilation tag and also make sure that the code that should be hit is within the Try-Catch block.Once,these settings are done you can debug the inline code using the same technique.

Debugging inline code in sharepoint - http://blogs.msdn.com/sowmyancs/archive/2008/03/15/debugging-the-inline-code-of-custom-layout-pages-in-sharepoint.aspx

Customize the Web Part Properties in Sharepoint

Web Parts are the basic units of sharepoint,its pretty easy to create a custom web part.However,the challenge lies in its customization as per the requirements. I have been busy these days working on the web part customizations, one such feature that fascinates me is the customization of web part properties. Customizing the web part properties includes 2 steps : 1.Set the web part property attributes. 2.Create Property accessors. Now,lets see each one of them in detail 1.Set the web part property attributes - Most of the web part proerty attributes comes from the namespace "System.ComponentModel" .Here are some of the attributes that we can make use of : Category -It specifies a separate category for the custom properties to be displayed on the property pane. WebBrowsable - Specifies if the property should be visible on the pane or not. We need to set this to "true" to make it availabe to the users. WebDisplayName - Display name for the custom property. WebDescription - Description for the custom property. We can have these attributes defined something like this [Category("Name"),Personalizable(PersonalizationScope.Shared), WebBrowsable (true),WebDisplayName("UserName for Output"),WebDescription("Enter Ur name for Output")] 2.Create property accessors - Web Part requires the appropriate get and set accessors methods. public string DisplayName { get { return Name; } set { Name = value; } } Once we are done with these steps,we should be getting our custom property in the property pane as shown below

Content Migration and deployment APIs

Content Migration APIs provides a solution for migrating contents between the WSS sites.These APIs are designed on the Object model and Microsoft.Sharepoint.Deployment is the core namespace used to perform the task.
We may come across a situation where we need to move the contents from one sharepoint site to the other eg : Moving all the list items from one site to the other . I was asked to build a utility to migrate and update the data b/w the portals,with these APIs i could build a utility that does the task.

Using this model involves 2 steps :
1.Exporting the contents.
2.Importing the contents and restoring them in the import server site.

Now ,Lets see each one of these sections in detail
1.Exporting the Contents -We can export the objects at different levels,which includes :
Export the whole site collection
Export the sub site.
Export the list or its items.

SPSite Src_Site = new SPSite(Your Export site);
SPWeb Src_Web = Src_Site.AllWebs["Test"];
SPExportObject Expobj = new SPExportObject();
Expobj.Type = SPDeploymentObjectType. * ;
SPExportSettings Exp_Settings = new SPExportSettings();
Exp_Settings.SiteUrl = Src_Web.Url;
Exp_Settings.BaseFileName = "Sample.cmp";
Exp_Settings.FileLocation = @"C:\"; //Generates Sample.cmp at the location C:\ .
SPExport Export = new SPExport(Exp_Settings);
Export.Run();

Objects Used -

SPExportObject - This object specifies the objects that could be exported, may be like entire site coll ,sub site ,list or the list items. * in the code above indicates the objects that we could export. We will get the intellisense there which is self-explanatory.
SPExportSettings -This object contains all the config settings for the export operations.

SPExport -This object performs the export operation, it calls the Run() to instantiate the export process.

On sucessfull export, the "Sample.cmp" file would be created at the location C:\(See the code above). Export process creates a CMP package called Content Migration Package(CMP) which contains all the items that needs to be restored in the import server.

2.Importing the contents - Now,lets see how to import these exported site contents and restore them in yet another WSS site. We can import the contents in either of the 2 ways,

By preserving the object identity - This means we retain the URL and GUID of the items we import as it is,which means the parent for such items remains the same even after importing. To do this we need to set the RetainObjectIdentity property of the SPImportSettings object to "true".

SPSite Dest_Site = new SPSite(Your Import site);
SPWeb Des_Web = Dest_Site.AllWebs["Sample"];

SPImportSettings Imp_Settings = new SPImportSettings();
Imp_Settings.SiteUrl = Des_Web.Url;
Imp_Settings.BaseFileName = "Sample.cmp";
Imp_Settings.FileLocation = @"C:\";
Imp_Settings.RetainObjectIdentity = true;
SPImport import = new SPImport(Imp_Settings);
import.Run();

Objects Used -

SPImportSettings -This object contains all the Config settings for the Import operations.

SPImport - This object performs the import operation, it calls the Run() to instantiate the import process.

By not preserving the Object Identity - This is yet another approach for importing the objects those have been exported already. In this approach we import object with different GUID ,which is done by setting the value "false" for the RetainObjectIdentity property of the SPImportSettings object, such objects which are imported with a different GUID needs new parent to be created otherwise,the objects will become orphaned.The import operation takes the CMP created by the export operation to restore the contents in the import server.

The new parent has to be created ,once the list items have been successfully imported from the package.To achieve this we will be using the ObjectImported event handler.

static void ObjectImported(object sender, SPDeploymentEventArgs args)

{

SPSite MySite = new SPSite("Site on the import server");

SPWeb MyWeb = MySite .RootWeb;

SPImportObjectCollection ObjColl= args.RootObjects;

foreach (SPImportObject io in ObjColl)

{

io.TargetParentUrl = MyWeb .Url;

}

MyWeb .dispose();

MySite.dispose();

}

Now,we should be able to see the contents moved between the 2 WSS Sites.This approach however,cannot export or import configuration settings,alerts,custom web parts,workflows and other customizations.

Publishing the administrator approved form templates

Infopath form services is a server technology that is available in Microsoft Office Sharepoint Server 2007 ,Enterprise editon. It enables us to run the infopath froms on the browsers.Instrestingly,to fill out these forms we need not have the client installed on our machines. One way of building such browser enabled forms is publising it to the administrator for the approval, which we will be discussing in this session.

Now,lets see how to build such infopath froms that runs on the browsers .

1.Design the infopath fom template .

2.Change the browser compatibility settings - Go to Tools Menu > Form options and choose the Compatibility tab as shown in the figure. Enter the URL of the server running infopath form services.To make the form browser enabled check the option as shown in the snapshot below.

3.Publishing a form template to the server running form services requires full trust.By default, the settings would be "Domain".To change the settings go to Tools menu >Form options >Security and trust Tab to change the secutiy level to full trust .

4.Now save the form and publish it, since we are publishing this form to the server check the option that points to the sharepoint server as shown below.

5.Choose where exactly you need to publish this template. We do have the option of publishing it to the document library,Content Type and to the admin for the approval. If at all we have the form template that contains managed code then we use the last option(Administrator approval).

6.Specify the location to save the form template. Eg : Browse to C:\Infopath Form Template and save it .


7.Now,click on the add button to make those columns available in the sharepoint site.Once,done click "Next" button and follow the wizard to complete the publishing process.


8.once,we have the form published for approval. It has to approved by the admin for that server. To approve the form template go to "Manage Form temples" under Central Admin > Application management > Infopath form services(Available only in the entreprise edition).

9.Now,click the upload form template to upload the form template.


NOTE- This has to be the template that we saved in the C:\Infopath form template.

10.Once uploaded ,the form template has to be activated to the site collection.To activate it go to the manage form template and click on the form that needs to be activated ,to get the list of menus to perform the operation.Click "Activate to a site collection " to activate it.


11. Once,the form template has been activated to the site collection, it has to be enabled for the form library.To do this go to the Advance settings for the Form library settings as shown in the snapshot below.To open the form in the browser,check the option "Display as a web page" and set "allow the management of the content type" to Yes.

12.Now you will be able to see the content type section in the form library settings. Click the Add from the existing content type as shown. Choose the site content type and click add button to add it to the form lib. The below snapshots are very much illustrative.

13.Now, go to the form library and click the new menu to open up the form in the browser.

Summary - Infopath form services enables us to build forms that runs on the browsers.However,such forms can run on the client as well.This type of deployment(Publishing to the administrator for approval) is used whenever we have to deploy the form template that contains the managed code.