Thursday, April 20, 2006

HOWTO - Greg McAllister - Sharepoint List Access

Sharepoint List Access



As promised, I will share a few pointers on accessing Sharepoint lists.


As a little background, we have created our own Document control process. This system has grown over the many years from a mere text list of controlled documents, to a small piece of VB software at one point and an MS Access DB project not to long ago. I have finally moved the product into Sharepoint as a Infopath project. For all intents and purposes it works fine for the most part. The 2 issues that seem to plague it are Users ability to follow the steps necessary to operate the form and its process and the occasional loss of data due to network timing issues. The second problem is specifically related to retrieving and incrementing a number on the SQL database.


Without going into detail about the problems, let it be known that they are causing undue stress to the users and need to be fixed. The users have also requested some further work flow automation. Analyzing the situation made it apparent that th users needed more guidance and as little interaction with the process as possible. This could be accomplished in several ways but the thought of creating a wizard really got stuck in my mind. I needed a tool that would allow me to do this because a wizard is considered more user friendly than a form that won't let you do something unless you fill it in step by step. This is a psychological thing  am sure but it is oh so painfully true.


My tool of choice ended up being VS 2003. Reason being is that I am an avid VB scripter and programmer. By using the Infopath toolkit for VS 2003, I will open the door to all the features that I am used to having in the full blown VB environment. Data Connections and dialogs and manipulation to a greater extent that the Infopath software provides directly. Basically  I want control and this is the way to do it.  VS 2003 solves both my problems. Because of the direct access to the SQL database - I can access the document number to be used on the form, increment it in my code, and write back the new number for the next user, while locking the database until the process is finished. This prevents any issues with SQL having to run the process and the confusion it seemed to find itself in on a rare occasion where the same number might be used twice. The other feature is that the users will be guided through filling out the form in dialog boxes. Once that is completed then they are presented with their form which they can change or file as they determine. They will not be in control of deleting, saving or printing at the Infopath software level, which has caused us a whole slew of problems in the past. The additional feature is that any and all changes are dialog driven. This means that no one can sneak into a form and change its contents without a dialog being presented for the change and the change being recorded. No more sneaky stuff.


The last and probably most advantageous benefit is the ability to look at data on the Sharepoint sites. This enables us to generate lookup lists on the Site, such as who should sign, types of documents and types of related information. Sure this information could be added pragmatically, but it changes and sometimes it changes frequently. Sometimes the change is normal but so infrequently used that it is easily forgotten as that once in a while exception. Because this information can now be matrix into Sharepoint lists - It can be maintained easily on the fly by the Document control team. The programmer is no longer needed to modify the form or the program. This particular feature is going to become an even greater tool in the future as we make this ability known to our product programmers. Lists that have been stored in proprietary systems can be moved to Sharepoint and accessed directly by .Net applications. There will be an explosion of excitement here in the near future!


 


The Source


Back to the point of Sharepoint lists. How do I get the information? The following assumes that you have used VS before and that you use tools more than code. It saves a lot of time that way. By the way - this works for ASP.NET as well as VB.NET and C#.NET.



  1. First we must find out list and its contents by using the VS 2003 Server explorer
  2. Locate the Lists data table and right click selecting Retrieve Data from Table
  3. All the data is returned so to find our particular list click on the SQL button on the menu tool bar
  4. In the SQL area enter the line WHERE tp_Title = N'myListName' (replace the myListName with the list name that you need for the look up such as Authors
  5. Click on the exclamation point to run the query
  6. One row should be returned where you then need to copy the tp_ID field

    1. To verify you have the correct List ID you can close this view and then open the UserData Table by right clicking and selecting the Retrieve Data From Table selection
    2. Once again click on the SQL button on the menu tool bar
    3. Enter the where clause as follows: WHERE tp_ListID = N'{78878585858955858958587958585879}' replacing the bracket number with the one copied from from the previous table
    4. Click again on the exclamation point menu tool bar item and a list of the list's contents will appear
    5. Verify the contents match the data that you expected. If it doesn't go back and try to find the right list again
    6. Maintain the list ID in your clipboard for future use

  7. Now lets drag a SQLConnection object to our form
  8. Name the connection conSharepoint
  9. Set the connection to your Sharepoint SQL server using the SITE database as the source (ex: workstation id=MySSServer;packet size=4096;integrated security=SSPI;data source=MySSServer;persist security info=False;initial catalog=MyInfo1_SITE
  10. Drag an SQLAdaptor to your form
  11. Follow along with the wizard

    1. select the data connection
    2. select use SQL statements
    3. click on advanced options
    4. deselect the first option (we do not use this particular for updating the list's contents. The lists are maintained through Sharepoint by Sharepoint users) which will deselect all the others for you
    5. click on query builder
    6. select UserData from the tables listing and click on Add then close
    7. Select the fields you want to use remembering that the tp_ListID is required.
    8. Note that you will select the appropriate fields that contain data necessary for your lookup. The easiest thing to remember is that text fields will be stored in the nvarchar fields, integers in the int fields, etc... The easiest way to figure this out is to select several from each type which will then display those fields on your initial test of the query (the next steps)
    9. in the criteria section of the builder type in = and then paste the tp_ID you previously copied from the list table. The result should look like = {5585-6576-79679-8986}
    10. Right click in the statement area and click on Run. You should see a listing of the Sharepoint list contents
    11. If everything is correct then click on OK, Next (a message appears showing that the Select statement was successfully built) and Finish

  12.  Name the Adapter after your list such as daDCAAuthors
  13. Rename the SQLSelectCommand to something like selAuthors
  14. Click on the Generate Data Set link at the bottom of the data adapter properties
  15. Click on New and enter something like dsAuthors in the text name field
  16. Make sure that the daAuthors adapter is selected
  17. Click on OK

You can now use the Sharepoint list as a data source for a loop list, grid or other bound control as necessary. Below is an example of what you need for a list on your form:



  1. Modify the select statement to meet your list criteria. In my case I need specific authors based on the type of book they write:

    1. The original sqlAuthors looked like this:

    2. SELECT tp_ListId, nvarchar1, float1 FROM UserData WHERE (tp_ListId = N'{35ED7787-F11F-4F16-92C0-E93A72A11608}')



    3. I change it using the Command object to read


      SELECT tp_ListId, nvarchar1, float1 FROM UserData WHERE (tp_ListId = N'{35ED7787-F11F-4F16-92C0-E93A72A11608}' AND NVARCHAR1 = N'Fiction')



  2. Then fill your data set with code similar to:


    daAuthor.Fill(DsAuthor1)



  3. Now you need to loop through the data set adding each member to your list:


    For x = 0 To DsAuthor1.UserData.Count - 1


    CheckedListBox1.Items.Add(DsDocType1.UserData.Item(x).nvarchar1, False)


    tmpTitle = DsDocType1.UserData.Item(x).nvarchar1


    Next


That is it in a nutshell. In the case of some lists you might need to make adjustments based on on items that are repeated and you only want distinct items to appear but that goes into greater detail than we want here.


Summary


Before we all loose track here - this is important to Sharepoint users for 2 reasons. First - you can now empower your users with the ability to maintain critical data that is used outside of Sharepoint. Secondly, you can use Sharepoint to be a repository of Critical program Information.


While I only use it currently to retrieve simple lists, in the future our users can maintain test data and other important structures that are used in our parts manufacturing, testing and programming. These lists will be come easier to maintain by the programmers and testers. All told - pretty powerfull stuff for the collaboration of entire departments and groups.


See you all next month with latest on the Document control project (after I make it to the Sharepoint Developers Bootcamp!).


Greggers

0 Comments:

Post a Comment

<< Home