public void RemoveAllRolesFromUser(Guid userID)
{
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
var factory = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_UsersRoles_RemoveAllRolesFromUser]")
.WithParams()
.AddGuid("@userID", userID)
.ParamsEnd()
.ExecuteNonQuery();
}
catch (Exception)
{
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public UserCredentials GetUserCredentials(Guid userID)
{
UserCredentials credentials = null;
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
credentials = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Credentials_GetUserCredentials]")
.WithParams()
.AddGuid("@userID", userID)
.ParamsEnd()
.ExecuteReader()
.AsEnumerable()
.Select(record => new UserCredentials(record))
.FirstOrDefault();
}
catch (Exception)
{
// todo log exception
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(credentials);
}
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);
}
public void AssignRoleToUser(Guid userID, Guid roleID)
{
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
var factory = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_UsersRoles_AssignRoleToUser]")
.WithParams()
.AddGuid("@roleID", roleID)
.AddGuid("@userID", userID)
.ParamsEnd();
factory.ExecuteNonQuery();
}
catch (Exception)
{
// todo log exception
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public void DeleteLot(Guid lotID)
{
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
var factory = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Lots_DeleteLot]")
.WithParams()
.AddGuid("@lotID", lotID)
.ParamsEnd()
.ExecuteNonQuery();
}
catch (Exception)
{
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public void SetUserLock(Guid userID, bool userLockStatus)
{
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
var factory = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Users_LockUser]")
.WithParams()
.AddGuid("@userID", userID)
.AddBit("@lockUser", userLockStatus)
.ParamsEnd();
factory.ExecuteNonQuery();
}
catch (Exception)
{
// todo log exception
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public bool UserHasRole(Guid userID, Guid roleID)
{
bool userHasRole = false;
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
userHasRole = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_UsersRoles_UserHasRole]")
.WithParams()
.AddGuid("@userID", userID)
.AddGuid("@roleID", roleID)
.AddBitOut("@result")
.ParamsEnd()
.ExecuteNonQuery()
.GetParameter <Boolean>("@result");
}
catch (Exception)
{
// todo log exception
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(userHasRole);
}
public void Execute_Edge_DataSetFilled()
{
SqlClient session = new SqlClientFactory().Instantiate(ConnectionStringReader.GetLocaleSql()) as SqlClient;
var statement = Mock.Of <IQuery>(x => x.Statement == "SELECT * FROM FoF f WHERE f.label='knows'");
SqlCommandOperation commandOperation = new SqlCommandFactory().Instantiate(session, statement).Implementation as SqlCommandOperation;
var engine = new SqlExecutionEngine(session.CreateClientOperation(), commandOperation);
var ds = engine.Execute();
Assert.That(ds.Tables, Has.Count.EqualTo(1));
Assert.That(ds.Tables[0].Rows, Has.Count.EqualTo(3));
Assert.That(ds.Tables[0].Columns, Has.Count.EqualTo(12));
Assert.That(ds.Tables[0].Columns.Cast <DataColumn>().Select(x => x.ColumnName), Has.Member("id"));
Assert.That(ds.Tables[0].Columns.Cast <DataColumn>().Select(x => x.ColumnName), Has.Member("label"));
Assert.That(ds.Tables[0].Columns.Cast <DataColumn>().Select(x => x.ColumnName), Has.Member("_sink"));
Assert.That(ds.Tables[0].Columns.Cast <DataColumn>().Select(x => x.ColumnName), Has.Member("_vertexId"));
Assert.That(ds.Tables[0].Columns.Cast <DataColumn>().Select(x => x.ColumnName), Has.Member("_sinkLabel"));
Assert.That(ds.Tables[0].Columns.Cast <DataColumn>().Select(x => x.ColumnName), Has.Member("_vertexLabel"));
foreach (DataRow row in ds.Tables[0].Rows)
{
Assert.That(row["id"], Is.Not.Null.Or.Empty);
Assert.That(row["_vertexLabel"], Is.EqualTo("person"));
Assert.That(row["_sinkLabel"], Is.EqualTo("person"));
Assert.That(row["_isEdge"], Is.True);
}
}
public void UpdateStatementWithNoChanges()
{
var factory = new SqlCommandFactory(new StubAuditor());
var changes = new Dictionary <string, PropertyChange>();
Assert.Throws <InvalidOperationException>(() => factory.CreateUpdateStatement(changes, "[dbo].[Blog]", "Id", 4));
}
public Decimal GetLotPrice(Guid lotID)
{
Decimal price = default(Decimal);
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
var factory = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Lots_GetLotPrice]")
.WithParams()
.AddGuid("@lotID", lotID)
.AddMoneyOut("@lotPrice")
.ParamsEnd()
.ExecuteNonQuery();
price = factory.GetParameter <Decimal>("@lotPrice");
}
catch (Exception)
{
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(price);
}
/// <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);
}
public Auction GetAuction(Guid auctionID)
{
Auction auction = null;
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
auction = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Auctions_GetAuction]")
.WithParams()
.AddGuid("@auctionID", auctionID)
.ParamsEnd()
.ExecuteReader()
.AsEnumerable().Select(record => new Auction(record))
.FirstOrDefault();
}
catch (Exception)
{
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(auction);
}
public List <Bid> GetUserBids(Guid userID)
{
List <Bid> bids = null;
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
bids = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Bids_GetUserBids]")
.WithParams()
.AddGuid("@userID", userID)
.ParamsEnd()
.ExecuteReader()
.AsEnumerable().Select(record => new Bid(record))
.ToList();
}
catch (Exception)
{
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(bids ?? new List <Bid>());
}
public void CloseAuction(Guid auctionID, DateTime endDate)
{
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
var factory = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Auctions_CloseAuction]")
.WithParams()
.AddGuid("@auctionID", auctionID)
.AddDateTime("@endDate", endDate)
.ParamsEnd()
.ExecuteNonQuery();
}
catch (Exception)
{
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public List <Lot> GetAuctionLots(Guid auctionID)
{
List <Lot> lots = null;
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
lots = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Lots_GetAuctionLots]")
.WithParams()
.AddGuid("@auctionID", auctionID)
.ParamsEnd()
.ExecuteReader()
.AsEnumerable().Select(record => new Lot(record))
.ToList();
}
catch (Exception)
{
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(lots ?? new List <Lot>());
}
public void DeleteRole(Guid roleID)
{
if (roleID == null)
{
throw new ArgumentNullException("roleID");
}
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
var factory = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Roles_DeleteRole]")
.WithParams()
.AddGuid("@roleID", roleID)
.ParamsEnd();
factory.ExecuteNonQuery();
}
catch (Exception)
{
// todo log exception
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public Role GetRole(Guid roleID)
{
Role role = null;
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
role = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Roles_GetRole]")
.WithParams()
.AddGuid("@roleID", roleID)
.ParamsEnd()
.ExecuteReader()
.AsEnumerable().Select(record => new Role(record))
.FirstOrDefault();
}
catch (Exception)
{
// todo log exception
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(role);
}
public Lot GetLot(Guid lotID)
{
Lot lot = null;
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
lot = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Lots_GetLot]")
.WithParams()
.AddGuid("@lotID", lotID)
.ParamsEnd()
.ExecuteReader()
.AsEnumerable().Select(record => new Lot(record))
.FirstOrDefault();
}
catch (Exception)
{
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(lot);
}
public List <Auction> GetStartedAuctions()
{
List <Auction> auctions = null;
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
auctions = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Auctions_GetStartedAuctions]")
.ExecuteReader()
.AsEnumerable()
.Select(record => new Auction(record))
.ToList();
}
catch (Exception)
{
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(auctions ?? new List <Auction>());
}
public List <User> GetUsersWithRole(Guid roleID)
{
List <User> users = null;
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
users = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_UsersRoles_GetUsersWithRole]")
.WithParams()
.AddGuid("@roleID", roleID)
.ParamsEnd()
.ExecuteReader()
.AsEnumerable().Select(record => new User(record))
.ToList();
}
catch (Exception)
{
// todo log exception
#if DEBUG
throw;
#endif
}
finally
{
}
return(users ?? new List <User>());
}
/// <summary>
/// Returns existing roles in the database.
/// </summary>
/// <returns>List of existing roles.</returns>
public List <Role> GetAllRoles()
{
List <Role> roles = null;
var connection = new SqlConnection(connectionString);
try
{
connection.Open();
roles = new SqlCommandFactory(connection)
.AsStoredProcedure("[dbo].[SP_Roles_GetAllRoles]")
.ExecuteReader()
.AsEnumerable().Select(record => new Role(record))
.ToList();
}
catch (Exception)
{
// todo log exception
#if DEBUG
throw;
#endif
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(roles ?? new List <Role>());
}
public static string GetMaxLsn(SqlConnection connection)
{
using (var cmd = SqlCommandFactory.Create("SELECT CONVERT(varchar(22), sys.fn_cdc_get_max_lsn(), 1);", connection))
{
return((string)cmd.ExecuteScalar());
}
}
public void CanHandle_OtherKindOfClient_False()
{
var client = Mock.Of <IClient>();
var query = Mock.Of <IQuery>();
var factory = new SqlCommandFactory();
Assert.That(factory.CanHandle(client), Is.False);
}
public static string GetLastExecutedLsn(SqlConnection connection, string providerUrl)
{
using (var cmd = SqlCommandFactory.Create($"SELECT TOP(1) LastExecutedLsn FROM [dbo].[CdcLastExecutedLsn] WHERE ProviderUrl = {@ProviderUrl};", connection))
{
cmd.Parameters.AddWithValue(@ProviderUrl, providerUrl);
return((string)cmd.ExecuteScalar());
}
}
/// <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);
}
public void CanHandle_SqlClient_True()
{
var client = new SqlClient(new Uri("https://localhost:8081"), base64AuthKey, "db", "FoF");
var query = Mock.Of <IQuery>();
var factory = new SqlCommandFactory();
Assert.That(factory.CanHandle(client), Is.True);
}
public static void DeleteSentNotifications(SqlConnection connection)
{
using (var cmd = SqlCommandFactory.Create(DeleteSentNotificationsQuery, connection))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
public void Instantiate_SqlClientAndQuery_CommandNotNull()
{
var client = new SqlClient(new Uri("https://localhost:8081"), base64AuthKey, "db", "FoF");
var query = Mock.Of <IQuery>();
var factory = new SqlCommandFactory();
var command = factory.Instantiate(client, query);
Assert.That(command, Is.Not.Null);
}
public static void InsertNotifications(SqlConnection connection, string notificationsXml)
{
using (var cmd = SqlCommandFactory.Create(InsertNotificationsQuery, connection))
{
cmd.CommandType = CommandType.Text;
var parameter = cmd.Parameters.AddWithValue("@notifications", notificationsXml);
parameter.SqlDbType = SqlDbType.Xml;
cmd.ExecuteNonQuery();
}
}
/// <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);
}
public ProductSqlRepositoryFixure()
{
// Fixture setup
var fixture = new Fixture()
.Customize(new AutoMoqCustomization());
mockProduct = fixture.Freeze<Product>();
mockProducts = fixture.Freeze<IEnumerable<Product>>();
IFactory<IProduct> productFactory = new ProductFactory();
ISqlCommandFactory commandFactory = new SqlCommandFactory();
ISqlDbInstance db = new MockSqlDbInstance(mockProduct, mockProducts);
productRepo = new ProductSqlRepository(productFactory, db, commandFactory);
}
