Microsoft Sharepoint Web Parts

SQL Query Viewer Sharepoint Web Part

The SQL Query Viewer Web Part allows to query external SQL Server databases and displays the result as a pageable list without having to resort to Sharepoint Designer. The Enterprise version adds OLE-DB and ODBC data source support (MS Access, Excel, CSV, Oracle etc.), allows to create Web Part connections (row filters etc.), sort columns and define search filters. The Web Part can be used with -  Windows Sharepoint Services V3 -  MOSS 2007 -  Sharepoint 2010 -  Sharepoint 2013 -  Sharepoint 2016.  The following parameters can be configured: Database Connection String (supports both SQL Server and Windows Integrated Security) SQL Select statement or Stored Procedure Number of rows displayed per page Display of column totals Sort Filters Column Sorting Column Formatting dynamic parameters can be passed to the SQL Query via URL parameters and web part connections Export data to Excel/CSV Product Price SQL Query Viewer Web Part for Sharepoint 2007 and 2010 30 day Evaluation Version Free SQL Query Viewer Web Part for Sharepoint 2013 30 day Evaluation Version Free SQL Query Viewer Web Part for Sharepoint 2016 30 day Evaluation Version Free SQL Query Viewer Web Part Basic Version Basic Version per Server License Key (allows to query Microsoft SQL Server databases) USD 150.00 SQL Query Viewer Web Part Enterprise Version Enterprise Version per Server License Key (adds OLE-DB/ODBC/Stored Procedure) support, Web Part connections for row filtering and Search filters). You can request a 30 day Enterprise Version Trial Key for evaluation purposes. USD 200.00 SQL Query Viewer Web Part Enterprise Version for SP2013 and SP2016 Enterprise Version per Server License Key for Sharepoint 2013 and 2016. You can request a 30 day Enterprise Version Trial Key for evaluation purposes. USD 200.00 SQL Edit Web Part for SP2007 and SP 2010 30 day Evaluation Version Free SQL Edit Web Part for SP 2013 30 day Evaluation Version Free SQL Edit Web Part for SP 2016 30 day Evaluation Version Free SQL Edit Web Part per Server License Key for Sharepoint 2007, 2010, 2013 and 2016 USD 125.00 SQL Query Viewer Web Part Installation Instructions Deployment  Instructions for SP 2010/2013/2016 SQL Edit Web Part Installation Instructions Notify me by e-mail if a new release is made available: You can also send us a PO via e-mail to [email protected]  or by fax to ++41 62 823 75 74

Installation Instructions:

1. download the SQL Viewer Web Part Installation Instructions (PDF file, see above) 
   
2. either install the web part manually or deploy the feature to your server/farm as described in the instructions.
   
   
3. Configure the following Web Part properties in the Web Part Editor "Miscellaneous" pane section as needed:
   
   
• DB Connection String (SQL Server):
  
• if you use SQL Server authentication, enter the connection string as follows:
  
  UID=uuu;Initial Catalog=database;Data Source=servername
  
  where
  uuu = SQL Server User Account
  database = the database you want to connect to
  servername = the name of the SQL Server
  
  Example:
  UID=sa;Initial Catalog=Northwind;Data Source=TestSQL;
  
  Please note that the password is entered in the Connection Password field for security reasons.
  
• if you use Windows authentication, enter the connection string as follows:
  
  Initial Catalog=database;Data Source=servername;Integrated Security=SSPI;
  
  Example:
  Initial Catalog=Northwind;Data Source=TestSQL;Integrated Security=SSPI;
  
  
• DB Connection String (OLE-DB):
  either simply enter the physical file name of the data source file (MS Access, MS Excel, CSV files) as follows:
  
  Examples:
    c:\data\northwind.mdb 
    d:\excel\somedata.xls
    c:\data\somedata.csv
  
  or alternatively enter a fully qualified OLE-DB connection string as follows:
  
  Examples:
    Provider=OraOLEDB.Oracle;OLEDB.NET=true;PLSQLRSet=true;Password=[ppp];User ID=[uuu];Data Source=[ddd]
    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data\northwind.mdb
    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data\;Extended Properties='text;FMT=Delimited'
  
  You can look up nearly every database connection string at http://connectionstrings.com
  
• DB Connection String (ODBC):
  either enter the ODBC Data Source name (as configured with the ODBC Data Source Administrator) as follows:
  
  dsn=MyDataSource
  
  or enter your fully qualified ODBC connection string as follows (Example):
  
  Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;
  
• Connection Password: if you use SQL Server authentication, enter the User account password into this field. It will be automatically encrypted for security reasons.
  Leave this field empty if you use an OLE-DB connection.
  
  
• SQL Query: Enter the desired SQL Select or Stored Procedure statement into this field.
  
  Example (using the Microsoft Northwind database):
  SELECT CompanyName, City, Phone, Homepage FROM Suppliers ORDER BY CompanyName
  
  Query Placeholders:
  
  - Use the {1} placeholder to pass a Filter value to the query (via a connected Filter web part)
     Range: {1} for Field Value connections, {1} - {12} for Filter Values connections
  
  - Use the {uid} placeholder to pass the current logged on user name
  
  - Use the {uid@} placeholder to pass the current logged on user name including the "@domain.com" postfix
  
  - Use the {url} placeholder to pass the current page URL
  
  - Use the {xxx} placeholders (where xxx is an URL querystring parameter) to pass any number of URL paramater
   
    Example assuming that the URL parameter "id" is contained in the page URL:
    SELECT Lastname,Firstname,Title FROM Employees WHERE ID={id}
  
    Dynamic placeholder to filter as provided by a connected multivalue choice Filter Web Part:
    Configured query: SELECT CompanyName, City FROM Suppliers WHERE City IN ('{3}')
    Chosen in filter web part: Toronto, Chicago, Paris
    Resulting query: SELECT CompanyName, City FROM Suppliers WHERE City IN ('Toronto','Chicago','Paris')
  
  
  - Use the {more=nn} placeholder in front of a selected SQL column to truncate long text fields
    (where nn= maximum nbr. of characters to display).
    The full text is displayed as a tooltip.
   
    Example: SELECT Lastname,Firstname,Title,{more=20}Notes FROM Employees
  
  
  
  - selected values starting with "http://" are automatically converted into hyperlinks
  
  - selected E-Mail address values are automatically converted into "a href" mail URL's:
  
  
  
  Example using a dynamic filter placeholder (as provided by a connected Filter Web Part):
  SELECT CompanyName, City, Phone FROM Suppliers WHERE City LIKE '{1}%'
  
  Example using an URL querystring placeholder:
  SELECT CompanyName, City, Phone FROM Suppliers WHERE City='{city}'
  
  
  Stored Procedures:
  Enter the name of the Stored Procedure, followed by optional parameters.
  Add each parameter as a "name=value" pair and separate multiple parameters by semicolons.
  
  Example 1 (refers to Microsoft Northwind sample database):
  SalesByCategory;CategoryName=Seafood;OrdYear=1997
  
  Example 2 (refers to Microsoft Northwind sample SQL Server database accessed via ODBC):
  CALL SalesByCategory(?);@CategoryName=Condiments
  
  Example 3
  Using filter values (Search Filter(s)) for stored procedures parameters:
  SQL Query:
  YourStoredProcedure;ProfileCode={f:!ProfileCode};ProfileVersion={f:!ProfileVersion};Amount={f:!Amount}
  Search Filter(s):
  !ProfileCode;!ProfileVersion;!Amount
  
  
• Column Totals: optionally enter the name of the column(s) for which you want to display the column total(s).
  Separate multiple columns by a semicolon.
  
  
  
• Search Filter(s): (Enterprise version only)
  Enter one or more Column names (separated by semicolons) to allow for interactive searching.
  
  Example: CustomerID;@LastName;Firstname;@City;
  
  
  
  
  
• If you place an "@" ampersand character on front of the filter name, a combo box is used (as opposed to a text input box) which is automatically populated with the available column values.
  
• If you place an "#" character on front of the filter name, a date picker is used (as opposed to a text input box).
  
• If you place an "!" exclamation mark character in front of the filter name, the filter is using an exact match (as opposed to the default which is applying a wildcard filter)
• You can also pass Querystring parameters via the page URL to the Search box(es) by adding the corresponding table column names and the desired values as name=value pairs.
  
  Example:
  ..yourpage.aspx?City=Redmond&LastName=Davolio
  
• Each dropdown filter can optionally be preset by adding the preset value as follows:
  @Month=April
  
• You can optionally override the default size of the search boxes by appending the desired size in pixels (separated by a colon):
  @Month:200;Last Name:150
  
• You can optionally specify a "friendly" search filter label by prefixing the column name with the desired friendly name, separated by a "|" pipe character.
  
  Example:
  Job Title|@Title
  
• You can also use the defined filters for replacing placeholders in the SQL query:
  
  Example:
  SELECT Lastname,Firstname,Title FROM Employees WHERE Firstname='{f:!FirstName}'
  Search Filter(s): !FirstName
  After entering "George" in the search textbox the following replacement occurs:
  SELECT Lastname,Firstname,Title FROM Employees WHERE Firstname='George'
  This way only the records which satisfy the condition are queried from the database.
  
  
• Search Filter Button: Enter the text of the Search Filter button (leave this field empty to suppress the button)
  
• Search Filter Reset Button: Enter the text of the Search Filter Reset button (leave this field empty to suppress the button)
  
  Example: Clear Filter
  
  
  
  
• Column Formatting: Column Formatting allows to optionally format the column values individually as follows:
  Enter each column name to be formatted followed by a ":" colon and the formatting expression.
  The actual column value as returned by the database query is represented by the {v} placeholder.
  You can specify other columns by using the {ColumnName} placeholder (where "ColumnName" is the name of the desired column)
  
  Examples:
  
  display the "City" column with a yellow background:
  City:<p style="background-color:yellow">{v}</p>
  
  display the "LastName" column in boldface:
  LastName:<B>{v}</B>
  
  append the "FirstName" column value to the "LastName" column:
  LastName:{v},{FirstName}
  
  display the "OrderID" column as a hyperlink:
  OrderID:<a href="http://www.someplace.com/orders/orderDetail.aspx?id={v}">{v}</a>
  
  display the "EMail" column as an icon using the "mail" placeholder:
  EMail:mail
  
  display the "State" column as an icon:
  State:<img src="/Sitename/PictureLibrary/{v}.gif">
  
  embed the current Page URL into the column:
  OrderID:<a href="{url}?id={v}">Postback with dynamic URL parameter</a>
  
  embed the {more=nn} placeholder to truncate long text fields 
  (where nn= maximum nbr. of characters to display).
  The full text is displayed as a tooltip.
  Notes:{more=20}
  
  display the numeric "Salary" column without a decimal fraction using the {i} placeholder:
  Salary:{i}
  
  display the numeric "Product Code" column without a decimal fraction and without thousands separators using the {I} placeholder:
  Product Code:{I}
  
  display the numeric "Salary" column with 2 decimal places using the {M} placeholder:
  Salary:{M}
  
  display the numeric "Sales" column as a currency with 2 decimal places using the {C} placeholder:
  Sales:{C}
  
  display the numeric "PercentCompleted" column as a percentage using the {%} placeholder:
  PercentCompleted:{%}
  
  display a datetime column using the {date=xxx} custom date format option (where xxx is a format template as described below):
  Created:{date=MM/dd/yyyy}
  
  
  The following formatting options are available:
  
  d - Numeric day of the month without a leading zero.
  dd - Numeric day of the month with a leading zero.
  ddd - Abbreviated name of the day of the week.
  dddd - Full name of the day of the week.
  
  h - 12 Hour clock, no leading zero.
  hh - 12 Hour clock with leading zero.
  H - 24 Hour clock, no leading zero.
  HH - 24 Hour clock with leading zero.
  
  m - Minutes with no leading zero.
  mm - Minutes with leading zero.
  
  M - Numeric month with no leading zero.
  MM - Numeric month with a leading zero.
  MMM - Abbreviated name of month.
  MMMM - Full month name.
  
  t - AM/PM but only the first letter.
  tt - AM/PM ( a.m. / p.m.)
  
  y - Year with out century and leading zero.
  yy - Year with out century, with leading zero.
  yyyy - Year with century.
  
  
  
  
  Separate multiple column formattings by semicolons.
  Example:
  City:{v};Company:{v}
  
  
• Column Widths: allows to optionally set the desired width in pixels for specific columns:
  Enter each column name for which you want to set a specific width, followed by a ":" colon and the width in pixels.
  Separate multiple column width settings by semicolons. You can suppress the display of a column by setting its width to zero.
  
  Example:
  LastName:200;FirstName:150
  
  You also can use this setting to specify the column heading alignment:
  
  Examples:
  Lastname:> (right-adjust)
  Phone:<> (centered)
  Salary:< (left-adjust)
  Salary:<150 (left-adjust, 150px wide)
  
• Rows per page: The web part supports paging and lets you specify the desired number of rows per page. 
  
• Show Nbr. of Records: Enable/disable the display of the total number of selected database rows at the bottom of the list.
  
• Show Column Headers: turn on/off the column headers (for example if your query returns a single value which you do not want to display as a table).
  
• Show all entries: either show all selected rows or none when no search filter is active. This setting can be used to not return any rows when one or more search filters are displayed but no filter has been applied by the user.
  
• Allow Column Sorting: enable/disable interactive column sorting.
  
• Enable Grouping: if enabled, alternates the row color when the value in the leftmost column changes.
  
• Grid Lines: choose one of the following options:
  - none
  - Horizontal
  - Vertical
  - Both
  
• Alternating Row Color: enter the optional color of the alternating row background (leave blank to use default).
  Enter either the HTML color names (as eg. "red" etc.) or use hexadecimal RRGGBB coding (as eg. "#CCFFCC")
  
• Header CSS Style: enter the optional custom CSS style attribute(s) to customize the appearance of the table header.
  Example:
  background-color:orange; font-size:14px; color:white
  
  
  
• Export to CSV: Show/hide the "Export" button for Excel CSV File Export
  
• CSV Separator: Enter the desired CSV field separator character (Default=Comma). Use a semicolon in countries which use the commas as a decimal separator.
  
• Export Button Text: define the display name of the "Export" link
  
• Show 'Print' Button: Show/hide the "Print" button for printing the results
  
• Header Area Text: enter an optional text to be displayed in the web part's header area.
  
• Page Refresh Interval: enter the optional page refresh interval in minutes (0 = no page refresh)
  
• Data Cache Retention Time: optionally enter the data cache retention time in minutes (enter "0" to turn off the cache feature) to speed up consecutive page visits.
  
• License Key: enter your Product License Key (as supplied after purchase of the Enterprise license) to enable the additional Enterprise version features.
  Leave this field empty if you are using the free version.