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.Core 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.

For every query you write that creates a new database object, check to see if that object already exists. If the object already exists, check to see if the object needs to be updated. Don't try deleting & recreating existing tables since there may be rows of data in the existing tables.
Make sure your stored procedures match your table names. For example, the table Products should have stored procedures like Product_Create, Products_GetList, Product_Update, and Product_Delete.
Note: Always use underscores between the object name and action when naming stored procedures.
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.

Query Guidelines

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

Create plural-named public static classes in the Query namespace to access your stored procedures and keep them in the Query folder within the root of your plugin folder. Also, make sure the class name reflects the table name associated with your stored procedures. For example, the static class Query.Products would be used to access stored procedures that modify the Products table.
Use singular-named public model classes in the Query.Models namespace to map stored procedure results to C# objects, and keep them in the Models folder within the root of your plugin folder. For example, Query.Models.Product would be used with Query.Sql.Populate<T> to get a List<T> of Product objects.

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).

Query.Sql.ExecuteNonQuery(string storedproc, dynamic parameters = null) will execute a stored procedure without returning any results. The first argument is the name of your stored procedure, and the second argument is a dynamic object that is mapped to stored procedure parameters.

public static void Create(Models.Product product)

{

Sql.ExecuteNonQuery("Product_Update", product);

}

The above example will update an existing product in the Products table by passing a product object of type Query.Models.Product.
Query.Sql.ExecuteScalar<T>(string storedproc, dynamic parameters = null) will execute a stored procedure and return a single result of type T. The first argument is the name of your stored procedure, and the second argument is a dynamic object that is mapped to stored procedure parameters.

public static int Quantity(int productId)

{

return Sql.ExecuteScalar<int>("Product_GetQuantity", new { productId });

}

The above example will return the quantity of an existing product in the Products table by passing a dynamic object with a productId property.

If your method needs to return a boolean, make your stored procedure return either a 1 or 0, then make your C# Query method return True if the scalar int result equals 1.
Query.Sql.Populate<T>(string storedproc, dynamic parameters = null) will execute a stored procedure and return a List<T>. The first argument is the name of your stored procedure, and the second argument is a dynamic object that is mapped to stored procedure parameters.

public static List<Models.Product> GetList(int page, int length)

{

return Sql.Populate<Models.Product>("Products_GetList", new { page, length });

}

The above example will return a list of products from the Products table by passing a dynamic object with the properties page and length in order to return a limited result set of a specific length.

Connection

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

Creating an instance of Query.Connection(string storedproc, dynamic parameters = null) will allow you to call Query.Connection.PopulateMultiple(), which will return a Dapper.SqlMapper.GridReader object which is used to read multiple record set results from an SQL stored procedure.

public static Models.ProductDetails GetDetails(int productId)

{

using (var conn = new Connection("Product_GetDetails", new { productId })

{

//load multiple record sets into readers

var readers = conn.PopulateMultiple();

var products = readers.Read<Models.Product>();

if(products == null || products.Count() == 0) { return null; }

//map record sets to return object fields

var product = new Models.ProductDetails(products.First());

product.Photos = readers.Read<Models.Photo>();

product.Reviews = readers.Read<Models.Review>();

return product;

}

}

The above example will build a Query.Models.ProductDetails object from multiple record sets. We make sure to close our database connection afterwards by wrapping our Connection object in a using statement. The stored procedure below is used to return multiple record sets for the example above.

CREATE PROCEDURE [dbo].[Product_GetDetails]

@productId int

AS

SELECT * FROM Products WHERE productId = @productId

SELECT * FROM ProductPhotos WHERE productId = @productId

SELECT TOP 10 * FROM ProductReviews WHERE productId = @productId ORDER BY datecreated DESC

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.