private IDbCommand CreateCommand(string sqlScript, IDictionary <string, object> @params)
{
//SqlConnection connection = GetConnection();
//var command = new SqlCommand();
//command.Connection = connection;
ReliableSqlConnection connection = GetConnection();
var command = SqlCommandFactory.CreateCommand(connection);
command.CommandText = sqlScript;
command.CommandType = CommandType.Text;
if (@params != null)
{
foreach (var param in @params)
{
var dbParam = command.CreateParameter();
dbParam.ParameterName = param.Key;
dbParam.Value = param.Value;
command.Parameters.Add(dbParam);
}
}
command.Connection.Open();
return(command);
}
/// <summary>
/// Returns a SQL command describing the database operation for retrieving XML data from the specified queue item in the Persistence Queue database.
/// </summary>
/// <param name="connection">The database connection object to be associated with the new command.</param>
/// <param name="itemId">The unique ID of the queue item.</param>
/// <param name="headerXPath">A collection of XPath expressions referencing the header part in the XML payload associated with the queue item.</param>
/// <param name="bodyXPath">A collection of XPath expressions referencing the body part in the XML payload associated with the queue item.</param>
/// <param name="footerXPath">A collection of XPath expressions referencing the footer part in the XML payload associated with the queue item.</param>
/// <param name="nsManager">The <see cref="System.Xml.XmlNamespaceManager"/> object providing the mechanism for resolving namespace prefixes to their respective namespaces.</param>
/// <returns>A new SQL command initialized with the respective command text, parameters and their initial values.</returns>
public static IDbCommand CreateDequeueXmlDataCommand(IDbConnection connection, Guid itemId, string[] headerXPath, string[] bodyXPath, string[] footerXPath, XmlNamespaceManager nsManager)
{
Guard.ArgumentNotNull(connection, "connection");
IDbCommand command = SqlCommandFactory.CreateCommand(connection, SqlCommandResources.SqlAzurePersistenceQueueDequeueXmlData);
command.Parameters.Add(SqlParameterUtility.CreateParameter(SqlObjectResources.ColumnQueueItemID, SqlDbType.UniqueIdentifier, itemId));
for (int i = 0; headerXPath != null && headerXPath.Length > 0 && i < headerXPath.Length; i++)
{
command.Parameters.Add(SqlParameterUtility.CreateParameter(String.Format(CultureInfo.CurrentCulture, SqlObjectResources.CommandParamTemplateHeaderXPath, i + 1), SqlDbType.VarChar, headerXPath[i]));
}
for (int i = 0; bodyXPath != null && bodyXPath.Length > 0 && i < bodyXPath.Length; i++)
{
command.Parameters.Add(SqlParameterUtility.CreateParameter(String.Format(CultureInfo.CurrentCulture, SqlObjectResources.CommandParamTemplateBodyXPath, i + 1), SqlDbType.VarChar, bodyXPath[i]));
}
for (int i = 0; footerXPath != null && footerXPath.Length > 0 && i < footerXPath.Length; i++)
{
command.Parameters.Add(SqlParameterUtility.CreateParameter(String.Format(CultureInfo.CurrentCulture, SqlObjectResources.CommandParamTemplateFooterXPath, i + 1), SqlDbType.VarChar, footerXPath[i]));
}
AddNamespaces(command, nsManager);
return(command);
}
/// <summary>
/// Returns a SQL command describing the database operation for removing a queue item from the Persistence Queue database.
/// </summary>
/// <param name="connection">The database connection object to be associated with the new command.</param>
/// <param name="itemId">The unique ID of the queue item.</param>
/// <returns>A new SQL command initialized with the respective command text, parameters and their initial values.</returns>
public static IDbCommand CreateRemoveCommand(IDbConnection connection, Guid itemId)
{
IDbCommand command = SqlCommandFactory.CreateCommand(connection, SqlCommandResources.SqlAzurePersistenceQueueRemove);
command.Parameters.Add(SqlParameterUtility.CreateParameter(SqlObjectResources.ColumnQueueItemID, SqlDbType.UniqueIdentifier, itemId));
return(command);
}
/// <summary>
/// Returns a SQL command describing the database operation for adding a queue item with the specified unique ID.
/// </summary>
/// <param name="connection">The database connection object to be associated with the new command.</param>
/// <param name="itemId">The unique ID of the queue item.</param>
/// <returns>A new SQL command initialized with the respective command text, parameters and their initial values.</returns>
public static IDbCommand CreateEnqueueCommand(IDbConnection connection, Guid itemId)
{
IDbCommand command = SqlCommandFactory.CreateCommand(connection, SqlCommandResources.SqlAzurePersistenceQueueEnqueue);
command.Parameters.Add(SqlParameterUtility.CreateParameter(SqlObjectResources.ColumnQueueItemID, SqlDbType.UniqueIdentifier, itemId));
command.Parameters.Add(SqlParameterUtility.CreateOutputParameter(SqlObjectResources.ColumnQueueItemSize, SqlDbType.BigInt));
command.Parameters.Add(SqlParameterUtility.CreateOutputParameter(SqlObjectResources.ColumnQueueItemType, SqlDbType.VarChar, 255));
return(command);
}
/// <summary>
/// Returns a SQL command describing the database operation for applying XPath expressions against the XML queue item data in the Persistence Queue database.
/// </summary>
/// <param name="connection">The database connection object to be associated with the new command.</param>
/// <param name="itemId">The unique ID of the queue item.</param>
/// <param name="xPathCollection">A collection of XPath expressions that will be applied against the XML payload associated with the queue item.</param>
/// <param name="nsManager">The <see cref="System.Xml.XmlNamespaceManager"/> object providing the mechanism for resolving namespace prefixes to their respective namespaces.</param>
/// <returns>A new SQL command initialized with the respective command text, parameters and their initial values.</returns>
public static IDbCommand CreateQueryXmlDataCommand(IDbConnection connection, Guid itemId, string[] xPathCollection, XmlNamespaceManager nsManager)
{
Guard.ArgumentNotNull(connection, "connection");
IDbCommand command = SqlCommandFactory.CreateCommand(connection, SqlCommandResources.SqlAzurePersistenceQueueQueryXmlData);
command.Parameters.Add(SqlParameterUtility.CreateParameter(SqlObjectResources.ColumnQueueItemID, SqlDbType.UniqueIdentifier, itemId));
for (int i = 0; xPathCollection != null && xPathCollection.Length > 0 && i < xPathCollection.Length; i++)
{
command.Parameters.Add(SqlParameterUtility.CreateParameter(String.Format(CultureInfo.CurrentCulture, SqlObjectResources.CommandParamTemplateXPath, i + 1), SqlDbType.VarChar, xPathCollection[i]));
}
AddNamespaces(command, nsManager);
return(command);
}
/// <summary>
/// Returns a SQL command describing the database operation for creating a new item in the Persistence Queue database.
/// </summary>
/// <param name="connection">The database connection object to be associated with the new command.</param>
/// <returns>A new SQL command initialized with the respective command text, parameters and their initial values.</returns>
public static IDbCommand CreateNewCommand(IDbConnection connection)
{
return(SqlCommandFactory.CreateCommand(connection, SqlCommandResources.SqlAzurePersistenceQueueNew));
}