Example #1
0
        public CommandFooTests()
        {
            barCmd = new Command<Bar>();
            cmd = new Command<Foo>()
                .ConfigureField(o => o.Bar, c => c.IsMapped = false)
                .ConfigureField(o => o.Bars, c => c.IsMapped = false);

            con = new SqlConnection(ConfigurationManager.ConnectionStrings["Db"].ConnectionString);
            con.Open();

            con.ExecuteAsync("truncate table dbo.Foo").Wait();
            con.ExecuteAsync("truncate table dbo.Bar").Wait();
        }
Example #2
0
        public CommandSecureTests()
        {
            barCmd = new Command<Bar>();
            cmd = new Command<Secure>()
                .ConfigureAllFields(c => c.Secure(), c => c.Name != "Id")
                .ConfigureField(o => o.Bar, c => c.IsMapped = false)
                .ConfigureField(o => o.Bars, c => c.IsMapped = false);

            con = new SqlConnection(ConfigurationManager.ConnectionStrings["Db"].ConnectionString);
            con.Open();

            con.ExecuteAsync("truncate table dbo.Secure").Wait();
            con.ExecuteAsync("truncate table dbo.Bar").Wait();
        }
Example #3
0
        public ExecuteTests()
        {
            sql = new SqlConnection(ConfigurationManager.ConnectionStrings["Db"].ConnectionString);
            sql.Open();

            sql.ExecuteAsync("truncate table dbo.Foo").Wait();
        }
Example #4
0
 internal static async Task DropDatabase(string connectionString, string databaseName)
 {
     using (var conn = new SqlConnection(connectionString))
     {
         await conn.OpenAsync();
         await conn.ExecuteAsync(string.Format("DROP DATABASE [{0}]", databaseName));
     }
 }
        public async Task Insert(Guid id, ProposalEditModel item)
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    var proposal = connection.ExecuteAsync("insert into Proposals(Id, Status, CreatedBy, CreatedOn, UpdatedOn) values(@id, 0, @createdBy, @now, @now);", new { id = id, createdBy = "user", now = DateTime.UtcNow }, transaction);
                    var students = connection.ExecuteAsync("insert into Proposals_2_Students(ProposalId, StudentId) values(@proposalId, @studentId);", item.Students.Select(s => new { proposalId = id, studentId = s }), transaction);
                    var actions = connection.ExecuteAsync("insert into Proposals_2_Actions(ProposalId, ActionId, [Values]) values(@proposalId, @actionId, @values);", item.Actions.Select(a => new { proposalId = id, actionId = a.Id, values = FieldsXmlHelpers.ValuesToXml(a.FieldValues) }), transaction);
                    var reasons = connection.ExecuteAsync("insert into Proposals_2_Reasons(ProposalId, ReasonId, [Values]) values(@proposalId, @reasonId, @values);", item.Reasons.Select(r => new { proposalId = id, reasonId = r.Id, values = FieldsXmlHelpers.ValuesToXml(r.FieldValues) }), transaction);

                    await Task.WhenAll(proposal, students, actions, reasons);
                    transaction.Commit();
                }
            }
        }
 public async Task SaveDueDate(int issueId, DateTime? dueDate, int userId)
 {
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         await con.ExecuteAsync("UPDATE Issue SET [email protected],[email protected],[email protected] WHERE [email protected]",
             new { @modifiedDate = DateTime.Now, issueId, dueDate, userId });
     }
 }
 private async Task AddIssueMemberRecord(int issueId, int userId, string type)
 {
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         await con.ExecuteAsync("INSERT INTO IssueMember(IssueID,MemberID,RelationType,CreatedDate,CreatedByID) VALUES(@id,@userId,@type,@dt,@userId);",
             new { @id = issueId, @userId = userId, @type = type, @dt = DateTime.Now, });
     }
 }
 private async Task DeleteIssueMemberRecord(int issueId, int userId, string type)
 {
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         await con.ExecuteAsync("DELETE FROM IssueMember WHERE [email protected] AND [email protected] and [email protected]",
             new { @id = issueId, @userId = userId, @type = type });
     }
 }
 public async Task DeleteTeamMemberInvitation(int id)
 {
     var q = @"DELETE FROM TeamMemberRequest WHERE ID = @id";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         await con.ExecuteAsync(q, new { @id = id });
     }
 }
Example #10
0
 public async Task<string> AddSubreddit( Subreddit sub ) {
     sub.SubName = sub.SubName;
     using ( SqlConnection conn = new SqlConnection( connstring ) ) {
         string query = "insert into Subreddits (SubName,Active) values (@SubName,@Active)";
         await conn.ExecuteAsync( query, new { sub.SubName, sub.Active } );
         string result = "Success";
         return result;
     }
 }
 public async Task SetDefaultTeam(int userId, int teamId)
 {
     var q = @"UPDATE [User] SET [email protected] WHERE [email protected]";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         await con.ExecuteAsync(q, new { @userId = userId, @teamId = teamId });
     }
 }
Example #12
0
        public QueryTests()
        {
            sql = new SqlConnection(ConfigurationManager.ConnectionStrings["Db"].ConnectionString);
            sql.Open();

            sql.ExecuteAsync("truncate table dbo.Foo").Wait();
            sql.ExecuteAsync(
                "insert into Foo (Guid, Int, DateTime2, DateTimeOffset) values (@Guid, @Int, @DateTime2, @DateTimeOffset)",
                Enumerable.Range(0, 1000).Select(i =>
                    new
                    {
                        Guid = Guid.NewGuid(),
                        Int = i,
                        DateTime2 = DateTime.UtcNow,
                        DateTimeOffset = DateTimeOffset.Now,
                    }
                )
            ).Wait();
        }
Example #13
0
        public TomSecureTests()
        {
            db = new Db("Db");

            using (var cx = new SqlConnection(db.ConnectionString))
            {
                cx.Open();
                cx.ExecuteAsync("truncate table dbo.Secure").Wait();
            }
        }
 public async Task UpdateLastLoginTime(int userId)
 {
     var q = @"UPDATE [User] SET [email protected] WHERE [email protected]";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         var com = await con.ExecuteAsync(q, new { @date = DateTime.UtcNow,userId });
         
     }
 }
Example #15
0
        public async Task ReplaceAsync(Blog blog)
        {
            var propertyContainer = SqlHelpers.ParseProperties(blog);
            var sqlPairs = string.Join(", ", propertyContainer.PairedProperties);

            var sql = $"UPDATE [Blog] SET {sqlPairs};";

            using(var connection = new SqlConnection(ConnectionString))
            {
                await connection.ExecuteAsync(sql, blog);
            }
        }
Example #16
0
 public async Task UpdateMultipleNoteTypes( NoteType[] ntypes, string uname ) {
     List<Dictionary<string, object>> ntypeParams = new List<Dictionary<string, object>>();
     foreach ( NoteType nt in ntypes ) {
         ntypeParams.Add( new Dictionary<string, object>() { { "NoteTypeID", nt.NoteTypeID }, { "SubName", nt.SubName }, { "DisplayName", nt.DisplayName }, { "ColorCode", nt.ColorCode }, { "DisplayOrder", nt.DisplayOrder }, { "Bold", nt.Bold }, { "Italic", nt.Italic }, { "uname", uname } } );
     }
     using ( SqlConnection con = new SqlConnection( constring ) ) {
         string query = "update NoteTypes set DisplayName = @DisplayName , ColorCode = @ColorCode , DisplayOrder = @DisplayOrder , Bold = @Bold , Italic = @Italic " +
             " OUTPUT GETUTCDATE() as 'HistTimestamp','U' as 'HistAction',@uname as 'HistUser',INSERTED.NoteTypeID,INSERTED.SubredditID,INSERTED.DisplayName,INSERTED.ColorCode,INSERTED.DisplayOrder,INSERTED.Bold,INSERTED.Italic,INSERTED.Disabled INTO " +
                 "NoteTypes_History(HistTimestamp,HistAction,HistUser,NoteTypeID,SubredditID,DisplayName,ColorCode,DisplayOrder,Bold,Italic,Disabled) " +
             " where NoteTypeID = @NoteTypeID";
         await con.ExecuteAsync( query, ntypeParams );
     }
 }
Example #17
0
        public async Task CreateAsync(Blog blog)
        {
            var propertyContainer = SqlHelpers.ParseProperties(blog);
            var sqlFields = string.Join(", ", propertyContainer.EscapedProperties);
            var sqlParameters = string.Join(", ", propertyContainer.ParameterizedProperties);

            var sql = $"INSERT INTO [Blog] ({sqlFields}) VALUES({sqlParameters});";

            using(var connection = new SqlConnection(ConnectionString))
            {
                await connection.ExecuteAsync(sql, blog);
            }
        }
Example #18
0
        public async Task<bool> UpdateBotSettings(DirtbagSettings settings, string subName) {
            using ( SqlConnection conn = new SqlConnection( connstring ) ) {
                string query = @"
update Subreddits
set DirtbagUrl = @DirtbagUrl,
DirtbagUsername = @DirtbagUsername,
DirtbagPassword = @DirtbagPassword
WHERE
SubName = @subName
";
                await conn.ExecuteAsync( query, new { settings.DirtbagUrl, settings.DirtbagUsername, settings.DirtbagPassword, subName } );
                return true;
            }
        }
Example #19
0
        public async Task<int> Update(Guid id, ProposalEditModel item)
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    var result = await connection.ExecuteAsync(@"update Proposals set [email protected], [email protected] where [email protected]
delete from Proposals_2_Students where ProposalId = @id
delete from Proposals_2_Actions where ProposalId = @id
delete from Proposals_2_Reasons where ProposalId = @id;", new { id = id, status = 0, now = DateTime.UtcNow }, transaction);

                    var students = connection.ExecuteAsync("insert into Proposals_2_Students(ProposalId, StudentId) values(@proposalId, @studentId);", item.Students.Select(s => new { proposalId = id, studentId = s }), transaction);
                    var actions = connection.ExecuteAsync("insert into Proposals_2_Actions(ProposalId, ActionId, [Values]) values(@proposalId, @actionId, @values);", item.Actions.Select(a => new { proposalId = id, actionId = a.Id, values = FieldsXmlHelpers.ValuesToXml(a.FieldValues) }), transaction);
                    var reasons = connection.ExecuteAsync("insert into Proposals_2_Reasons(ProposalId, ReasonId, [Values]) values(@proposalId, @reasonId, @values);", item.Reasons.Select(r => new { proposalId = id, reasonId = r.Id, values = FieldsXmlHelpers.ValuesToXml(r.FieldValues) }), transaction);

                    await Task.WhenAll(students, actions, reasons);
                    transaction.Commit();

                    return result;
                }

            }
        }
Example #20
0
        public TomFooReadTests()
        {
            db = new Db("Db");

            using (var cx = new SqlConnection(db.ConnectionString))
            {
                cx.Open();
                cx.ExecuteAsync("truncate table dbo.Foo").Wait();
            }

            originals = Enumerable.Range(0, 500).Select(i => new Foo
            {
                Id = Guid.NewGuid(),
                Int = i,
                IntNull = i,
                DateTime2 = DateTime.UtcNow,
                DateTime2Null = DateTime.UtcNow,
                DateTimeOffset = DateTimeOffset.Now,
                DateTimeOffsetNull = DateTimeOffset.Now,
                Bit = true,
                BitNull = true,
                Float = 1,
                FloatNull = 1,
                Decimal = 1,
                DecimalNull = 1,
                Guid = Guid.NewGuid(),
                GuidNull = Guid.NewGuid(),
                Nvarchar = "Created",
                Varbinary = new byte[] { 0, 1, 2, 3 },
                Fi = new Fi { Name = "I'm a fi you're a foo.", Number = 1 },
                Fees = new[]
                    {
                        new Fee { Name = "I'm a fee you're a foo.", Number = 2 },
                        new Fee { Name = "I'm another fee you're a foo.", Number = 3 }
                    },
            }).OrderBy(o => o.Id).ToList();
            db.Foos.AddRangeAsync(originals).Wait();
            db.Commit();
        }
Example #21
0
        internal static async Task CreateDatabase(string connectionString, string databaseName)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                await conn.OpenAsync();
                await conn.ExecuteAsync(string.Format("CREATE DATABASE [{0}]", databaseName));
            }

            byte state;
            do
            {
                using (var conn = new SqlConnection(connectionString))
                {
                    await conn.OpenAsync();
                    var results = await conn.QueryAsync<byte>("SELECT [state] FROM sys.databases WHERE name = @databaseName", new { databaseName });
                    state = results.First();
                    if (state != 0)
                    {
                        Thread.Sleep(3000);
                    }
                }
            }
            while (state != 0);
        }
        public async Task Initialize()
        {
            await _log.Debug("Initializing Event Store");

            string script;
            using(var stream = Assembly
                .GetAssembly(typeof(DapperEventStore))
                .GetManifestResourceStream("Aenima.Dapper.Scripts.CreateStore.sql")) {
                if(stream == null) {
                    throw new Exception("Failed to find embedded script resource to initialize store!");
                }

                using(var reader = new StreamReader(stream))
                    script = reader.ReadToEnd();
            }

            using(var connection = new SqlConnection(_settings.ConnectionString)) {
                await connection
                    .ExecuteAsync(script)
                    .ConfigureAwait(false);
            }

            await _log.Debug("Event Store initialized");
        }
 public async Task SavePasswordResetRequest(PasswordResetRequest passwordResetRequest)
 {
     const string q = @"INSERT INTO [dbo].[PasswordResetRequest](UserId,ActivationCode,CreatedDate) VALUES(@UserId,@ActivationCode,@CreatedDate)";
     passwordResetRequest.CreatedDate = DateTime.UtcNow;
     using (var con = new SqlConnection(ConnectionString))
     {
         await con.ExecuteAsync(q,passwordResetRequest);
     }
 }
 public async Task UpdatePassword(string password, int userId)
 {
     var q = @"UPDATE [User] SET [email protected] WHERE [email protected]";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         await con.ExecuteAsync(q, new { password, userId });
     }
 }
 public async Task SaveUserProfile(EditProfileVm userProfileVm)
 {
     var q = @"UPDATE [User] SET [email protected] WHERE [email protected]";
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         await con.ExecuteAsync(q, new { @userId = userProfileVm.Id, @name = userProfileVm.Name });
     }
 }
Example #26
0
 public async Task<bool> UpdateSubredditSettings( Subreddit sub ) {
     using ( SqlConnection conn = new SqlConnection( connstring ) ) {
         string query = "update ss " +
                         "set ss.AccessMask = @AccessMask " +
                         ", ss.PermBanID = @PermBanID " +
                         ", ss.TempBanID = @TempBanID " +
                         "from SubredditSettings ss inner join Subreddits s on s.SubRedditID = ss.SubRedditID " +
                         "where s.subname = @SubName";
         int rows = await conn.ExecuteAsync( query, new { sub.Settings.AccessMask, sub.Settings.PermBanID, sub.Settings.TempBanID, sub.SubName } );
         if ( rows <= 0 ) {
             string insert = "insert into SubredditSettings(SubRedditID,AccessMask,PermBanID,TempBanID) " +
                             "(select SubRedditID, @AccessMask ,@PermBanID,@TempBanID from Subreddits where SubName = @SubName)";
             conn.Execute( insert, new { sub.Settings.AccessMask, sub.Settings.PermBanID, sub.Settings.TempBanID, sub.SubName } );
         }
     }
     return true;
 }
 public async Task SaveIssueMember(int issueId, int memberId, int createdById, string relationShipType)
 {
     using (var con = new SqlConnection(ConnectionString))
     {
         con.Open();
         await con.ExecuteAsync("INSERT INTO IssueMember (IssueID,MemberID,RelationType,CreatedDate,CreatedByID) VALUES(@issueId,@memberId,@rtype,@dt,@userId)",
             new { @issueId = issueId, @memberId = memberId, @rtype = relationShipType, @dt = DateTime.UtcNow, @userId = createdById });
     }
 }
Example #28
0
        public async Task<int> AddNewToolBoxNotes( List<Note> tbNotes ) {
            using ( SqlConnection conn = new SqlConnection( connstring ) ) {
                string query = "insert into Notes(NoteTypeID,SubredditID,Submitter,Message,AppliesToUsername, Url, Timestamp) "
                    + "select @NoteTypeID,(select SubredditID from Subreddits where SubName = @SubName),@Submitter,@Message,@AppliesToUsername, @Url, @Timestamp "
                    //+ "where not exists(select * from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID where s.SubName = @SubName and n.AppliesToUsername = @AppliesToUsername and HASHBYTES('SHA2_256',Lower(s.SubName + n.Submitter + n.AppliesToUsername + CONVERT(VARCHAR,n.Timestamp,120) + n.Url)) = HASHBYTES('SHA2_256',Lower(@SubName + @Submitter + @AppliesToUsername + CONVERT(VARCHAR,@TimeStamp,120) + @Url)))";
                    + "where not exists (select * from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID where s.SubName = @SubName and n.AppliesToUsername = @AppliesToUsername and n.Submitter = @Submitter and n.Url = @Url and CONVERT(VARCHAR,n.Timestamp,120) = CONVERT(VARCHAR,@TimeStamp,120))";
                int rowsEffected = await conn.ExecuteAsync( query, tbNotes );
                return rowsEffected;

                //HASHBYTES('SHA2_256',Lower(s.SubName + n.Submitter + n.AppliesToUsername + CONVERT(VARCHAR,n.Timestamp,120) + n.Url))
            }
        }
Example #29
0
 public async Task<int> Delete(Guid id)
 {
     using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
     {
         return await connection.ExecuteAsync("delete from Proposals where [email protected];", new { id = id });
     }
 }
        public async Task SaveNotificationSettings(UserEmailNotificationSettingsVM model)
        {
            //DELETE EXISTING 
            var q = @"DELETE FROM UserNotificationSubscription WHERE [email protected] and [email protected];";
            using (var con = new SqlConnection(ConnectionString))
            {
                con.Open();
                await con.ExecuteAsync(q, new { @userId = model.UserId, @teamId = model.TeamId });

            }
            //Insert new
            foreach (var setting in model.EmailSubscriptions.Where(s => s.IsSelected))
            {
                var q2 = @"INSERT INTO
                    UserNotificationSubscription(UserID,NotificationTypeID,TeamId,Subscribed,ModifiedDate) VALUES
                    (@userId,@notificationTypeId,@teamId,@subscibed,@dt)";
                using (var con = new SqlConnection(ConnectionString))
                {
                    con.Open();
                    await con.ExecuteAsync(q2, new
                    {
                        @userId = model.UserId,
                        @subscibed = true,
                        @notificationTypeId = setting.NotificationTypeId,
                        @dt = DateTime.Now,
                        @teamId = model.TeamId
                    });

                }
            }

        }