Clear and then disable controls in infopath

This is simple for reading, but difficult to implement in infopath forms. To disable controls we have to use “Conditional Formatting” option of a control. To clear content of a control then we have to use “Rules” option of a control. But, the sequence of execution of these two causes some problems. Take below scenario.
I have a table and each row the first column contains a checkbox and all other 4 columns are having date field, textbox, drop down and date field respectively. Now, the logic should be this.

1. The default state of checkbox is selected.
2. When user enter some data in all other 4 fields and now he thought the row should not allowed to enter data then he deselect the checkbox. When user deselects then the first thing should happen is clear the content in all the 4 controls and then all controls should be disabled.
3. When again user select the checkbox then the controls should be enabled.

This is what to be happen and the first trail when I tried to implement I did below things.

1. On all other 4 controls I added a rule that when checkbox selected state is false then set the current field to empty.
2. And then I added a conditional formatting on them to disable when the checkbox state is unchecked.

I did deploy them to SharePoint and when I tested, surprisingly they are not working as expected. The controls are going to disable state but not clearing content in them when I deselect the checkbox. And researched and found that the Rules are not executing. [Didn’t find the reason yet.] And then thought about for alternatives and came up with reverse way. That is, applying rule on the checkbox instead of other controls.
Earlier, I have applied rules on the each and every individual control which needs to be cleared based on checkbox state – Which doesn’t work. Now, I have applied the same logic but applied rule on the checkbox [As there are 4 controls, 4 times I have added set field to empty] and which is working like charm.
So the conclusion I want to tell to you is, when I apply conditional formatting and then rules something is causing problems in the sequence of execution. So, depends on what your control, rules needs to be executed apply the rules on that control only. That should work perfect.

Date validation in Infopath forms

In SharePoint infopath forms I need to implement one thing which was a bit tricky as it is not the default supported by infopath forms. The requirement is like this: The date fields on form should not allow date plus/minus 5 years from current server date. For this, so many people recommended to implement validation events in c#. But, my form contains 40+ date fields. That is not possible to implement events for each control. But, when I looked into all option there is an option called “Data Validation”. But, there is no direct option available to implement this requirement.
After thought about sometime got wonderful ideas. Below is the implementation finally came up with.

1. Select the date control and right click.
2. Select the option “data validation”.
3. Click on Add to add a new validation.
4. From the window, the first thing we have to check is “Whether the field is blank or not.” Because once it is not blank then only we go further.
5. And next thing is, validate the expression. User entered date is plus/minus 5 years from current date.

If you observe the first condition I have used is an expression as there is no direct way to validate the date according to my requirement.
Expression we have to use: msxsl:string-compare(., xdDate:AddDays(xdDate:Today(), 1825)) > 0 or msxsl:string-compare(., xdDate:AddDays(xdDate:Today(), -1825)) < 0
And second condition is just the field is not blank then only display “Invalid date” error on screen.
Note: Remember, the expression is very simple that adding/subtracting the 1825 days [5 years] to current date. Depends on your requirement please change the value accordingly.
The last reason why I have used the expression instead of the simple statements is the logical operators. The condition should be validated for this requirement is “((selected date > today + 5 years OR selected date < today – 5years) AND field cannot be blank)”. The brackets are very important, the sequence of executing conditions and logical operators are important. So, to achieve this in infopath forms the only way is implementing expressions. Then only it treats it as the way we want. Enjoy the complex and nice series of posts on infopath and sharepoint coding.

Delete event receiver from a SharePoint list

In my previous post, we saw how we added an event receiver to a list. Now, we will see how to delete the existing event receiver on a list.

private void DeleteEventReceiverFromAList(string siteUrl)
    {
        using (SPSite site = new SPSite(siteUrl))
        {
            using(SPWeb web = site.OpenWeb())
            {
                try
                {
                    SPList list = web.Lists["myList"];
                    if (list != null)
                    {
                        string className = "EventReceiverClass";
                        string asmName = "EventReceiverAssemblyName, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a865f0ecc234ea51";
                        web.AllowUnsafeUpdates = true;

                        int receivers = list.EventReceivers.Count;
                        bool isAddedReceiverExist = false;
                        bool isUpdatedReceiverExist = false;
                        for (int i = 0; i < receivers; i++)
                        {
                            SPEventReceiverDefinition eventReceiver = list.EventReceivers[i];
                            if (eventReceiver.Class == className && eventReceiver.Type == SPEventReceiverType.ItemAdded)
                            {
                                eventReceiver.Delete();
                                break;
                            }
                        }
                    }
                }
                catch { }
                finally
                {
                    web.AllowUnsafeUpdates = false;
                }
            }
        }
    }

In this code also, there is nothing to explain very detail. Please let me know if you have any questions.

Add event receiver to a SharePoint list

This is very generic and everyone knows how to add an event receiver. But, usually we attach the event receiver on a list template, site etc. This post deals with adding event receiver to a specific list.

private void AddEventReceiverToAList(string siteUrl)
{
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.OpenWeb())
{
try
{
SPList list = web.Lists["myList"];
if (list != null)
{
int receivers = list.EventReceivers.Count;
string className = "EventReceiverClass";
string asmName = "EventReceiverAssemblyName, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a865f0ecc234ea51";
web.AllowUnsafeUpdates = true;
bool isAddedReceiverExist = false;
for (int i = 0; i < receivers; i++)
{
SPEventReceiverDefinition eventReceiver = list.EventReceivers[i];
if (eventReceiver.Class == className && eventReceiver.Type == SPEventReceiverType.ItemAdded)
{
isAddedReceiverExist = true;
break;
}
}
if (!isAddedReceiverExist)
list.EventReceivers.Add(SPEventReceiverType.ItemAdded, asmName, className);
}
}
catch { }
finally
{
web.AllowUnsafeUpdates = false;
}
}
}
}

This is very straight forward code and hope you got it.

Hide content types from a SharePoint library through coding

Please read the post here.

Change content type order in NEW button of a SharePoint library

This is continuation of my previous post. After you read that post you get clear understanding of how we added the content types to a library through coding. But, what if there is a requirement we need this content type order to be shown when I select the NEW button from the list tool bar or hide some content types? Then again we need some sort of code which does that for all existing lists as we cannot change manually if there are plenty of webs in a site.

private void ChangeOrHideContentTypesInALibrary(SPList list)
{
list.ContentTypesEnabled = true;

SPFolder folder = list.RootFolder;

List<SPContentType> orderedContentTypes = new List<SPContentType>();
foreach (SPContentType ct in folder.ContentTypeOrder)
{
if (ct.Name.Contains("ContentType1") || ct.Name.Contains("ContentType2"))
orderedContentTypes.Add(ct);
}

folder.UniqueContentTypeOrder = orderedContentTypes;
folder.Update();
}

If you observe the above code, then the variable orderedContentTypes is what having the content types of which we need to show in the NEW button of the list toolbar. In which order we add the content types to this variable, that order they will be added to the list and shown on the toolbar. And second thing is out of 3 content types available in the above logic we have added only two to the variable. So the third content type will be hidden from the toolbar. And the last two lines in the above function are to update the list with the latest content types order.

Hope this gives you clear idea on how to order and hide content types on a list/library.

Add content type to a SharePoint list or library through code

In one of my SharePoint projects, there is a requirement like a SharePoint site has 140+ sub sites and each web has 2 lists which I need to update. There are 2 content types which are inheriting by each list and now I have to add another through coding. It is very difficult to go through all webs and each list in each web and manually add it. So, thought of writing a simple script which will loop through them and update them. So, here is the code I came up with.

private void AddContentTypeToLibraries(string siteUrl)
{
List<SPContentType> contentTypes = new List<SPContentType>();
using (SPSite site = new SPSite(siteUrl))
{
using (SPWeb web = site.OpenWeb())
{
contentTypes.Add(web.ContentTypes["ContentType1"]);
contentTypes.Add(web.ContentTypes["ContentType2"]);
contentTypes.Add(web.ContentTypes["ContentType3"]);
}
foreach (SPWeb web in site.AllWebs)
{
try
{
web.AllowUnsafeUpdates = true;

foreach (SPList list in web.Lists)
{
if (!list.Title.Equals("MyList", StringComparison.InvariantCultureIgnoreCase))
continue;

for (int i = 0; i < contentTypes.Count; i++)
{
AddContentTypeToList(contentTypes[i], list);
}
}
}
catch { }
finally
{
web.AllowUnsafeUpdates = false;
web.Dispose();
}
}
}
}

void AddContentTypeToList(SPContentType ct, SPList list)
{
if (list.ContentTypes[ct.Name] == null)
{
list.ContentTypes.Add(ct);
list.Update();
}
}

The first method is what we are looping through all webs and go to each list and try to add a content type. And the second method is before adding a content type to a list, we are checking whether the content type is already there or not for that list. So, we are checking for that condition and if find the content type is not already attached to the list then only we are adding to the list.

Hope you understand the logic and how we need to implement it.

UDCX files in Sharepoint Infopath and dynamic queries

Confused? Can we use dynamic queries in the infopath and UDCX combination? UDCX are meant for not writing any code to get data from database and to show it up the retrieved data on the infopath form. They allow only STATIC queries. Just straight SQL or SPROC names  and parameters to it. But, I got a requirement where I need to pass some dynamic values to the query/SPROC at runtime and gets the data and loads the data on the form. Can I achieve that with same UDCX connections and same architecture?
YES, There is a way to do this. The things to note here are:

1. Using UDCX connections, we have saved all the connection data, query data and credentials data on a single file in SharePoint list/library.
2. Query should be correct and executing without any issues [Otherwise infopath cannot download the resulted schema]. For example, you want to get the user by user name then you may created SPROC with name "GetUserByUserName" and in your UDCX file you give the query tag as "<udc:Query>EXEC "dbo.GetUserByUserName" 'DEFAULT'</udc:Query>". We know there is no record in database with the name "DEFAULT". But, this is what we have to give as default query. [This is what we will change in the c# code dynamically.]
3. Read this connection, query in the infopath c# code.
4. Change the query information in which way you want in code.
5. Execute the query.
6. Reset the UDCX connection information back to original.
7. It will automatically refresh the control data depends on the latest result set after we executed from the C# logic.

So, I believe you got complete picture of what we are going to do. This is very simple but difficult to get the idea. With this implementation I solved the big problems what I had.
To execute the below code I am assuming there is a UDCX connection file available in a SharePoint library and your infopath form is allowing c# code.

//Get the connection details by connection name 
AdoQueryConnection adoConnection = (AdoQueryConnection)DataConnections["Get_User_Details"];
if (adoConnection != null)
{
string orgCommand = adoConnection.Command; //To read original command 
int index = orgCommand.IndexOf("DEFAULT"); //Find where the keyword "DEFAULT" in the command string 
string SPROC = string.Empty;
if (index > -1)
{
try
{
SPROC = orgCommand.Substring(0, index); //Get only the SPROC name. 
adoConnection.Command = string.Format(SPROC + userName + "'"); //Append user name to the query. 
adoConnection.Execute(); //Execute the final query. This is what the command which contains actual parameter value instead of DEFAULT string. 
}
catch { }
finally
{
adoConnection.Command = orgCommand; //Should not forget to write this. We have to do this. 
}
}
}

Things to note:

1. The connection name "Get_User_Details" is the connection name from infopath form [Managed Data Connections option].
2. As we are reading from existing UDCX connection file, we are not hard-coded any of the connection strings or queries.
3. userName is the string variable which holds the user name which comes at run time. You have to write some logic to get these in your code.
4. Read the query and replace the dummy parameter values with the original values.
5. Execute the connection.
6. In finally block, we are resetting the command back to original.

That's it!!! If you are binding this information to the textbox then you should do one final thing as shown in below figure.

The checkbox in above figure "Update this value when the result of formula is recalculated" and applies to only if you use the formula.

We are done. The data now comes from database and passed the parameters to database dynamically, used UDCX connection file and did not hard-coded any of the connection, query information in code. Very clean right?

Hope you understood it well and liked it.

Using Variables in TOP clause in T-SQL

This is what I want to post as I heard so many people are facing this issue. We can use variables in T-SQL queries but, there are requirements that we may select only the top 5 rows or 10 rows or n rows from the result set and use them wherever needed. So, below is the implementation we have to use to achieve that.

1. One way could be using a variable, which saves the complete t-sql query in it and execute that total query.
2. Second way will be pass the variable dynamically to TOP clause.

I do not think the first way is feasible. So, I prefer to go to second way and below are the implementation details.

SQL SERVER 2005:

DECLARE @TOP INT;
SET @TOP = 10;
SELECT TOP (@TOP) * FROM [User]

If you observe the variable is what storing the value of how many values we need to select. from the table And the last statement is what the final query which does what we needed. Remember the brackets () around the @TOP variable are what must and should. I also failed to write the query very first time as I forgot to place the brackets around the @TOP variable.

Earlier Versions: SQL SERVER 2000:

DECLARE @TOP INT;
SET @TOP = 5;

SET ROWCOUNT @TOP;
SELECT * FROM [User];
SET ROWCOUNT 0; --DO NOT FORGET TO WRITE THIS. VERY IMPORTANT

There is nothing tricky here. Just setting the ROWCOUNT internal variable to the required value is what works for us. And the very important thing is do not forget to reset the value of ROWCOUNT back to 0. Otherwise it effects the other result sets which comes after these T-SQL statements.

I think you got what I am trying to say here and enjoy the nice tips and posts.

Visual Studio get pubic key token through external tools

This is very needful post. Most of the times in general development we work on class library projects which outputs DLL at the end. To use them we may need to know the Public Key Token of the DLL and use it wherever needed. We can know the Public key token either through the command prompt SN.EXE or by deploy it to GAC. But, what if you have some tool available and displays the Public key token within Visual Studio?

1. In Visual Studio, go to Tools, and chose External Tools option as shown.'
2. You will be open up with a new window and enter the details as shown below.
   1. Title as “Get Public Key Token”
   2. Command as “C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\sn.exe”. Remember, if you installed Windows SDK then the above location works, otherwise please chose the location where SN.EXE presents.
   3. Arguments as “-Tp "$(TargetPath)"”
   4. And uncheck all checkboxes at bottom and check only the option “Use Output Window”.
3. At the end the settings should look as shown below.
   
4. Now, click OK button and go to tools, you will see there is a new menu item added in the list as shown below.
5. So, we are all set to use the tool now. Once you build the class library project, then just click on this “Get Public Key Token” option.  From the output window, you will see the BLOB of the DLL and public key token as shown below.

That’s it. This is very helping one to me. Hope you also like it. Reference