Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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();
                }
            }
        }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 8
0
        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);
            }
        }
Ejemplo n.º 9
0
        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);
        }
Ejemplo n.º 11
0
            /// <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>());
        }
Ejemplo n.º 16
0
        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();
                }
            }
        }
Ejemplo n.º 17
0
        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>());
        }
Ejemplo n.º 20
0
        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>());
        }
Ejemplo n.º 21
0
        /// <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>());
        }
Ejemplo n.º 22
0
 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());
     }
 }
Ejemplo n.º 23
0
        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);
        }
Ejemplo n.º 24
0
 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());
     }
 }
Ejemplo n.º 25
0
            /// <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);
            }
Ejemplo n.º 26
0
        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();
     }
 }
Ejemplo n.º 28
0
        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();
     }
 }
Ejemplo n.º 30
0
            /// <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);
        }