Developer Center

Query

If you are developing a plugin for Saber, please take the time to read and understand all of the documentation on this page. Query is a namespace within the Saber.Vendor NuGet package that allows developers to securely execute stored procedures within Saber's SQL Server database.

This documentation will guide you through the process of creating & publishing SQL Server tables and stored procedures for your plugin. You can also learn how to set up migration scripts for users who want to update existing plugins to newer versions, and also setting up scripts that are used when uninstalling your plugin from Saber.

NOTE: Do not, under any circumstances, write scripts that modify any existing database objects or data owned by Saber or other vendor plugins. Only modify database objects that are created for your plugin.


Installation Script

When a user installs your plugin into Saber for the first time, Saber will search for Sql/install.sql within your plugin folder and execute the script if it is found. Follow the scripting guidelines when writing your installation script.


Migration Scripts

When a user updates a plugin that was previously installed on their website, Saber will search for any migration scripts that have a version number greater than the previously installed version of that plugin and then execute each script in order starting with the oldest version. The path to the migration scripts within your vendor plugin folder should look similar to Sql/migrate-1.1.6.0.sql, Sql/migrate-1.1.7.0.sql, and Sql/migrate-1.2.0.0.sql. The filename must start with migrate- followed by the current Assembly version of your plugin x.x.x.x, and must use four numbers for the Assembly version.

NOTE: You should always keep your installation script up to date because Saber will not run any migration scripts when your plugin is first installed.


Uninstall Script

When a user wants to uninstall your plugin from Saber, Saber will search for Sql/uninstall.sql within your plugin folder and execute the script if it is found. Make sure to only delete database objects that were created by your plugin. Follow the scripting guidelines when writing your uninstall script.


Scripting Guidelines

Always follow the guidelines below when writing database scripts for your vendor plugin.


Query Guidelines

Always follow the guidelines below when building classes in the Query namespace.


Sql

The static class Query.Sql is the secure way to execute stored procedures within Saber's SQL Server database using Dapper as an object relational mapper (ORM).


Connection

The class Query.Connection is the secure way to execute stored procedures that return multiple record sets.


Serializer

The method Query.Common.Serializer.ToXmlDocument(object input) is used to generate an XmlDocument that can then be sent to your SQL stored procedures if you need to pass an SQL parameter of type XML to your stored procedures.

using System.Xml.Serialization;
namespace Query.Models.Xml
{
[Serializable]
[XmlRoot("photos")]
public class Photos
{
[XmlElement("photo")]
public Photo[] Photos { get; set; }
}
public class Photo
{
[XmlAttribute("filename")]
public string Filename;
[XmlAttribute("alt")]
public string Alt { get; set; }
}
}

The example above creates a model used for building a list of photos that is sent as an SQL stored procedure parameter.

public static void AddBulk(Models.Xml.Photos[] photos, int productId)
{
Sql.ExecuteNonQuery("Product_AddBulkPhotos", new { productId, photos = Common.Serializer.ToXmlDocument(photos).OuterXml });
}

The example above uses an array of Query.Models.Xml.Photos, serializes the array, and sends the OuterXml string as an SQL parameter to the Product_AddBulkPhotos stored procedure.

CREATE PROCEDURE [dbo].[Product_AddBulkPhotos]
@productId int,
@photos XML
AS
EXEC sp_xml_preparedocument @hdoc OUTPUT, @photolist
INSERT INTO ProductPhotos
SELECT @productId AS productId, x.filename, x.alt
FROM (
SELECT * FROM OPENXML( @hdoc, '//photo', 2)
WITH (
[filename] nvarchar(255) '@filename',
[alt] nvarchar(255) '@alt'
)
) AS x

The example above creates a stored procedure used to pass the array of Query.Models.Xml.Photos as a serialized XML SQL parameter.