Returning a value from a dynamic SQL Statement

There have been times when I have needed to create a dynamic SQL Statement within a stored procedure.  You need to consider permissions of the process that is executing the dynamic sql statement but that will not be addressed here.  I had a need on a recent project to be able to execute a dynamic sql statement from a stored procedure and use its result within that same procedure.  Here is simple example of how you can do this.

This stored procedure accepts two integer parameters.  It converts them into a string values as it builds the SQL statement.  The key is that the data we desire to use is inserted into a temporary table within the dynamic sql statement.  This temporary table is created within this stored procedure and is accessible by the dynamic sql statement.  This allows us to query the data from the temporary table and use it later on within the stored procedure.


CREATE PROCEDURE dbo.Test
  @a int
  , @b int
AS
BEGIN
 DECLARE @return_status int
 DECLARE @sql varchar(8000)
   
 SET @sql = 'INSERT INTO #Temp(result_value)
             SELECT ' + convert(varchar(20), @a) + ' + ' + convert(varchar(20), @b);
   
 CREATE TABLE #Temp (
      result_value int null
 )

 EXEC (@sql);

 SELECT *
 FROM #Temp

 DROP TABLE #temp

END


This is the execution and results.

exec dbo.Test 1, 2

result_value
3
(1 row(s) affected)

SharePoint Online Development experiences

These are some things that I have learned while doing SharePoint Online development that are not explicitly stated on the SharePoint online developer site (http://msdn.microsoft.com/en-us/library/hh147180.aspx). Microsoft’s basic philosophy is that you can deploy sandbox solutions. Well … that is not completely true. Here are some things you can do in sandbox solutions that you cannot do SharePoint online

1.) You do not have access to some of the most useful objects in the Microsoft.SharePoint.Publishing assembly.  This make it difficult to make adjustments to the Global Navigation in code. For example, if you try to reference the PublishingWeb object, you get this error when you attempt to load the solution into the solution gallery.

2.) You do not have access to the SPLimitedWebPartManager. This rules out adding or modifying web parts in code. The funny thing is their site says that you can deploy web parts. However, you are not allowed to add your deployed web part to a page via code.

System.TypeLoadException: Could not load type 'Microsoft.SharePoint.WebPartPages.SPLimitedWebPartManager' from assembly 'Microsoft.SharePoint, Version=14.900.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'


3.) Calendar Overlays are not functional even though they appear to be available through the SharePoint Online UI. If you try to add the overlay via code, you get this message.

System.MissingMethodException: Method not found: 'Microsoft.SharePoint.SPForm Microsoft.SharePoint.SPFormCollection.get_Item(Microsoft.SharePoint.PAGETYPE)'.

This is a documented bug. It’s not specific to SharePoint Online.  The Calendar Overlay functionality only works in the Default Zone.


4.) At one point, I added a simple webpart to my Visual Studio 2010 SharePoint project.  The web part only printed into a label the text "Hello World". I was able to deploy the solution into the Solution Gallery. However, I received the following error when I tried to insert the web part into a page.

I got around this issue by separating the control out into a different project.

Adding jQuery to NewForm and EditForms

There are a times when you need to add more functionality to your SharePoint NewForms and EditForms.  For example, the out-of-the-box SharePoint NewForm / EditForm does not provide the ability to add an input mask to a site column.  jQuery will allow you do to this as shown here.

You can accomplish this goal using either SharePoint Designer or Visual Studio.  I prefer to create solutions that are easily redeployable between development, test, and production; therefore, I am going to use Visual Studio to accomplish this.  To accomplish our goal, we will create a web scoped feature receiver that adds a Content Editor Web Part containing our jQuery commands to a lists NewForm and EditForm. 

I am not going to discuss the setup of the Visual Studio project file.  Instead, I will focus on the code required for the solution.  This is a boiler plate feature activated event handler that allows any user to activate the web scoped feature.  The real work is done within the  AddContentEditorWebPart method.

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
  SPWeb web = properties.Feature.Parent as SPWeb;
  SPSecurity.RunWithElevatedPrivileges(() =>    
  {
    using (SPSite elevatedSite = new SPSite(web.Site.ID))
    using (SPWeb elevatedWeb = elevatedSite.OpenWeb(web.ID))
    {
      AddContentEditorWebPart(elevatedWeb);
    }
  });
}

This method creates a list object, then creates a file object for the new form and edit form for this list.  Once you  have the file, you can create a web part manager object and then add in a content editor web part.  The final part of this solution is the XML representation of the web part definition.  I great way to get the base Content Editor Web Part XML is to add a Content Editor Web Part to a SharePoint web part page then select to export the defintion.

private void AddContentEditorWebPart(SPWeb elevatedWeb)

{

  string listName = "Your list title.";     

  SPList list = sysWeb.Lists[listName];

  string xmlWebPartDef = "This is the XML representing the web part (see example below)";　
 

  for (int i = 0; i < 2; i++)   

   {

    SPFile frontPage = null;   

 
      frontPage = sysWeb.GetFile(list.DefaultNewFormUrl);       

    if (i == 0)   

    {

    }

    else if (i == 1)
    {
      frontPage = sysWeb.GetFile(list.DefaultEditFormUrl);
    }


    SPLimitedWebPartManager mgr = frontPage.GetLimitedWebPartManager(PersonalizationScope.Shared);

    XmlReader xmlReader = XmlTextReader.Create(new StringReader(xmlWebPartDef));   

    string errorMessage;   

    var contentEditorWebPart = mgr.ImportWebPart(xmlReader, out errorMessage);

    mgr.AddWebPart(contentEditorWebPart, "Main", 999);

  }
}

Once you have that XML, you can add in your jQuery call.  For our original example, we are trying to add a mask to the Home Phone field.  I added my jQuery logic to the CONTENT node near the bottom of this example.

<WebPart xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/WebPart/v2">
  <Title>AddMaskedControls</Title>
  <FrameType>None</FrameType>
  <ChromeType>None</ChromeType>
  <Description>Allows authors to enter rich text content.</Description>
  <IsIncluded>true</IsIncluded>
  <ZoneID>Main</ZoneID>
  <PartOrder>99</PartOrder>
  <FrameState>Normal</FrameState>
  <Height />
  <Width />
  <AllowRemove>true</AllowRemove>
  <AllowZoneChange>true</AllowZoneChange>
  <AllowMinimize>true</AllowMinimize>
  <AllowConnect>true</AllowConnect>
  <AllowEdit>true</AllowEdit>
  <AllowHide>true</AllowHide>
  <IsVisible>true</IsVisible>
  <DetailLink />
  <HelpLink />
  <HelpMode>Modeless</HelpMode>
  <Dir>Default</Dir>
  <PartImageSmall />
  <MissingAssembly>Cannot import this Web Part.</MissingAssembly>
  <PartImageLarge>/_layouts/images/mscontl.gif</PartImageLarge>
  <IsIncludedFilter />
  <Assembly>Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Assembly>
  <TypeName>Microsoft.SharePoint.WebPartPages.ContentEditorWebPart</TypeName>
  <ContentLink xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor" />
  <Content xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor"><![CDATA[
   <script type="text/javascript" src="/_layouts/1033/jquery.maskedinput-1.2.2.js"></script>
   <script type="text/javascript" language="javascript">
     $(document).ready(function() {
                 $("input[title='Home Phone']").mask("999-999-9999");
     });
      </script>

]]></Content>
  <PartStorage xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor" />
</WebPart>

There are a couple notes that should be touched upon regarding the data in the  CONTENT node.  The jquery.maskedinput-1.2.2.js file is not part of the SharePoint installation so I copied that file in the SharePoint hive.  The jQuery reference is locating the field by the controls DISPLAY NAME.  The mask will be saved as well as the data, so our home phone data will look like 555-123-4567.  This content editor web part XML was pulled from a SharePoint 2010 site.  The same example will work with a SharePoint 2007 site with one change.  The assembly reference should be Microsoft.SharePoint, Version=12.0.0.0 instead.

This is a simple and powerful way of getting jQuery into your SharePoint NewForm / EditForms. 

Migrating Access 2010 Database to SharePoint 2010 Access Service Application

I have worked at a number of companies that have Microsoft Access databases that were created by non-IT departments. Eventually, IT is called upon to make these Access databases production systems. Typically, this involves converting the Access application into some from of a .Net application with a SQL Server backend. Now with SharePoint 2010, there is another vehicle that can be used to take an Access database and expose it to a larger audience. One major benefit is that people do not need to have Access installed on their computer to be able to have access to this system.

The first thing we need is an existing Access database. Years ago, I created a simple time tracking Access database that I will use for this example. The TimeTracking database contains 2 tables.

This is the Task table schema



This is the TimeTracker table schema.



For the TaskID properties. Click on the Lookup tab, change the Display Control to Combo Box and set the remaining values as shown here.



Save the access database with these two tables. We now have our very basic sample database to represent the source Access database we wish to migrate to SharePoint 2010.




We need to have an Access Web Database to load into SharePoint. I found that the easiest way to covert an existing Access database into a new Access Web Database was to import the tables, queries, etc. Therefore, we need to use Access 2010 to create our new Access Web Database as shown here.



The next step is to Import the tables, forms, etc. into the new Web Database. To import the Access objects, you will need to click on External Data on the top ribbon, then click on the Access button.


You will now see the Get External Data wizard. Select the Access database and objects you wish to import. For our example, select the Task and TimeTracker tables and click on the OK button to perform the import. You may see errors caused by Web Compatibility Issues. These issues need to be corrected before the objects can be imported. Once all of the compatibility issues are resolved, you should see the tables listed in our new Access Web Database.


The next thing we want to do is create a web form to give user access to the tables. Do this by clicking on the Create link on the ribbon, then click on the Form button.


Access automatically creates a form like the image shown. It is interesting to note that the table import logic automatically added fields to our table schema. These fields are not for user input so just remove them from this form.


Once the fields have been removed the web form should look like this


Next you will need to set the default web form. This is under File -> Help -> Options. This opens the Access Options window. Select a default Web Display Form to the TimeTracker1 form we created


We now have a very basic Access Web Database. To publish the Access 2010 web database to SharePoint 2010 Access Services goto File -> Save & Publish -> Publish to Access Services. You will need to provide the Site Collection URL plus the site name for the site that will be created when the Access Web Database is published to SharePoint. In this case, I am calling the site TimeTracker


Once the Access Web Database is published you will receive this message.



 
Now open the site collection and click on the All Site Content link.


The published Access Web Database appears as a site within this Site Collection.



Go into the site the default web form appears

What I went through was a very basic Access database migration. I took that example and added a form for the task table, a report and a navigational form. I then re-published the site and this is the results.

Although this is a simplistic example, it does show the potential to leverage Access data via SharePoint especially with simple Access databases.