10 DIY SharePoint Web Parts #3

Comments | Share

This post is the third in a series of postings, containing examples of SharePoint WebParts that anybody can build all by themselves.  To read all posts in this series, or to get started with the RSSBus WebPart, go here.

#3 – Custom SQL Queries

This web part will perform a custom SQL query, and allow you to display them however you like.  Unlike previous examples, this one won’t be just cut and paste, since you’ll have to provide your own SQL connection string and query, and make replacements inside the template itself to make it match the results of your query.

Step one is to make sure you have the RSSBus Web Part installed.  See here for instructions.

Step two, make sure you have a valid SQL connection string and query.  If you’re using some other type of database, thats fine too – there are many different database connectors in the RSSBus Connector library.

Step three, add the RSSBus WebPart to your page and paste the following template into its source editor, but do not click “Apply” yet.

<rsb:info>
   <input name="conn" default="server=***;database=***;User ID=***;Password=***" />
   <input name="query" default="SELECT TOP 20 * YourTable ORDER BY YourDateColumn" />
</rsb:info>

<rsb:import lib="RSSBus.SqlOps"/>

<table width="100%">
  <tr> 
    <th style="background-color:LightGrey; white-space:nowrap">Name</th> 
    <th style="background-color:LightGrey; white-space:nowrap">Company</th> 
    <th style="background-color:LightGrey; white-space:nowrap">Product</th> 
    <th style="background-color:LightGrey; white-space:nowrap">Date/Time</th> 
  </tr> 
  <rsb:call op="sqlQuery">  
    <tr> 
      <td><a href="mailto:[[sql:customeremail]]">[[sql:customername]]</a></td>  
      <td>[[sql:company]]</td> 
      <td>[[sql:productcode]]</td> 
      <td>[[sql:time]]</td> 
    </tr> 
  </rsb:call>
</table>

Step four, we need to modify this template so that it works for your SQL server, and not mine!  There are four things you’ll need to change:

1.  On line 2 and 3, conn and query.  This is where you will provide the connection string and query for your SQL query.
2.  On lines 10–13, you’ll want to modify the column header names to match the values coming back from your query.  You can add others if you want as well.
3.  On lines 18-21, change the outputs to match the outputs coming out of your query – these will match the column names.  In my example I am outputting the name, company, part, and time column values for each query result.

As always, you can customize the html in the template if you want, or leave it as is.

And now - an Advanced Tip!  Did you know that the RSSBus Web Part has YUI formatting capabilities built-in?  Yep.  Try the example below to format the output of the rsb:call as a YUI Table (again, modify the YUI script to match your own connection string, query, and column names).

<rsb:info> 
  <input name="conn" default="server=***;database=***;User ID=***;Password=***" /> 
  <input name="query" default="SELECT TOP 20 * FROM YourTable ORDER BY YourDateColumn DESC" /> 
</rsb:info> 

<rsb:import lib="RSSBus.SqlOps"/>

<rsb:set attr='_meta.YUIDataColDefs' value='DataDefsDown'/> 
<rsb:set attr='_meta.YUIDataFields' value='DataFieldsDown'/> 
<rsb:call op="sqlQuery" format=ToYuiTable/>  

<script> var DataDefsDown = [
  {key:"sql:customername",label:"Name",sortable:false,resizeable:true},
  {key:"sql:productcode",label:"Part#",sortable:false,resizeable:true},
  {key:"sql:time",label:"Date",sortable:false,resizeable:true},
  {key:"sql:company",label:"Company",sortable:false,resizeable:true},
  {key:"sql:title",label:"JobTitle",sortable:false,resizeable:true}
];
 
var DataFieldsDown = [
  {key:"sql:customername"},
  {key:"sql:productcode"},
  {key:"sql:time"},
  {key:"sql:company"},
  {key:"sql:title"}
];
</script>

See all posts in this series:  10 DIY SharePoint Web Parts.
Browse RSSBus Connectors.
Basic RSSBus Scripting Cheat Sheet.