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(); }
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(); }
public async Task <IdentityResult> UpdateAsync(User u) { //cancellationToken.ThrowIfCancellationRequested(); using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("FeedMeDB"))) { var param = new DynamicParameters(); param.Add("@UserID", u.UserID, DbType.Int32); param.Add("@FirstName", u.FirstName, DbType.String); param.Add("@LastName", u.LastName, DbType.String); param.Add("@TargetCals", u.TargetCals, DbType.Int32); param.Add("@TargetMacC", u.TargetMacC, DbType.Int32); param.Add("@TargetMacP", u.TargetMacP, DbType.Int32); param.Add("@TargetMacF", u.TargetMacF, DbType.Int32); await connection.OpenAsync(); await connection.ExecuteAsync( "dbo.UpdateUser", param, commandType : CommandType.StoredProcedure); } return(IdentityResult.Success); }
public async Task UpdatePersonInfo(PersonInfo personInfo) { try { var parameters = new DynamicParameters(); parameters.Add("PersonId", personInfo.PersonId, DbType.Int32); parameters.Add("Nickname", personInfo.Nickname, DbType.String); parameters.Add("CivilStatus", personInfo.CivilStatus, DbType.String); parameters.Add("BirthPlace", personInfo.BirthPlace, DbType.String); parameters.Add("Height", personInfo.Height, DbType.String); parameters.Add("Weight", personInfo.Weight, DbType.String); parameters.Add("Religion", personInfo.Religion, DbType.String); parameters.Add("IdentifyingMarks", personInfo.IdentifyingMarks, DbType.String); parameters.Add("DateAdded", personInfo.DateAdded, DbType.String); parameters.Add("Age", personInfo.Age, DbType.Int32); using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal(dbString))) { await connection.ExecuteAsync("spPersonInfo_Update", parameters, commandType : CommandType.StoredProcedure); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error, update person info", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public async Task UpdatePerson(Person person) { try { var parameters = new DynamicParameters(); parameters.Add("Id", person.Id, DbType.Int32); parameters.Add("FirstName", person.FirstName, DbType.String); parameters.Add("MiddleName", person.MiddleName, DbType.String); parameters.Add("LastName", person.LastName, DbType.String); parameters.Add("ExtensionName", person.ExtensionName, DbType.String); parameters.Add("DateOfBirth", person.DateOfBirth, DbType.String); parameters.Add("Gender", person.Gender, DbType.String); parameters.Add("Remarks", person.Remarks, DbType.String); parameters.Add("StringImage", person.StringImage, DbType.String); using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal(dbString))) { await connection.ExecuteAsync("spPerson_Update", parameters, commandType : CommandType.StoredProcedure); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error, update person", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public ExecuteTests() { sql = new SqlConnection(ConfigurationManager.ConnectionStrings["Db"].ConnectionString); sql.Open(); sql.ExecuteAsync("truncate table dbo.Foo").Wait(); }
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 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 }); } }
public async Task SetDefaultTeam(int userId, int teamId) { var q = @"UPDATE [User] SET DefaultTeamId=@teamId WHERE ID=@userId"; using (var con = new SqlConnection(ConnectionString)) { con.Open(); await con.ExecuteAsync(q, new { @userId = userId, @teamId = teamId }); } }
public async Task SaveDueDate(int issueId, DateTime? dueDate, int userId) { using (var con = new SqlConnection(ConnectionString)) { con.Open(); await con.ExecuteAsync("UPDATE Issue SET DueDate=@dueDate,ModifiedDate=@modifiedDate,ModifiedByID=@userId WHERE ID=@issueId", 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 IssueID=@id AND MemberID=@userId and RelationType=@type", new { @id = issueId, @userId = userId, @type = type }); } }
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 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(); }
public async Task UpdateLastLoginTime(int userId) { var q = @"UPDATE [User] SET LastLoginDate=@date WHERE Id=@userId"; using (var con = new SqlConnection(ConnectionString)) { con.Open(); var com = await con.ExecuteAsync(q, new { @date = DateTime.UtcNow,userId }); } }
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 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); } }
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 ); } }
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); } }
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; } }
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 Status=@status, UpdatedOn=@now where Id=@id 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; } } }
public async Task DeletePerson(Person person) { try { var parameters = new DynamicParameters(); parameters.Add("Id", person.Id, DbType.Int32); using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal(dbString))) { await connection.ExecuteAsync("spPerson_DeletePerson", parameters, commandType : CommandType.StoredProcedure); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error, delete person", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public async Task InsertFingerprint(Fingerprint fingerprint) { try { var parameters = new DynamicParameters(); parameters.Add("PersonId", fingerprint.PersonId, DbType.Int32); parameters.Add("FingerprintPerson", fingerprint.FingerprintPerson, DbType.Binary); using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal(dbString))) { await connection.ExecuteAsync("spFingerprint_InsertFingerprint", parameters, commandType : CommandType.StoredProcedure); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error, insert fingerprint", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public async Task UpdateEducational(Educational educational) { try { var parameters = new DynamicParameters(); parameters.Add("PersonId", educational.PersonId, DbType.Int32); parameters.Add("LevelOfEducation", educational.LevelOfEducation, DbType.String); parameters.Add("NameOfSchool", educational.NameOfSchool, DbType.String); using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal(dbString))) { await connection.ExecuteAsync("spEducational_Update", parameters, commandType : CommandType.StoredProcedure); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error, update educational", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public async Task UpdateSpouse(Spouse spouse) { try { var parameters = new DynamicParameters(); parameters.Add("PersonId", spouse.PersonId, DbType.Int32); parameters.Add("NameOfSpouse", spouse.NameOfSpouse, DbType.String); parameters.Add("NameOfChildren", spouse.NameOfChildren, DbType.String); using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal(dbString))) { await connection.ExecuteAsync("spSpouse_Update", parameters, commandType : CommandType.StoredProcedure); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error, update spouse", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public async Task UpdateContact(Contact contact) { try { var parameters = new DynamicParameters(); parameters.Add("PersonId", contact.PersonId, DbType.Int32); parameters.Add("PhoneNumber", contact.PhoneNumber, DbType.String); parameters.Add("EmailAddress", contact.EmailAddress, DbType.String); using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal(dbString))) { await connection.ExecuteAsync("spContact_Update", parameters, commandType : CommandType.StoredProcedure); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error, update contact", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public async Task InsertAccount(Account account) { try { var parameters = new DynamicParameters(); parameters.Add("FirstName", account.FirstName, DbType.String); parameters.Add("LastName", account.LastName, DbType.String); parameters.Add("Username", account.Username, DbType.String); parameters.Add("Password", account.Password, DbType.String); parameters.Add("StringImage", account.StringImage, DbType.String); using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal(dbString))) { await connection.ExecuteAsync("spAccount_InsertAccount", parameters, commandType : CommandType.StoredProcedure); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error, insert account", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
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(); }
public async Task UpdateOccupation(Occupation occupation) { try { var parameters = new DynamicParameters(); parameters.Add("PersonId", occupation.PersonId, DbType.Int32); parameters.Add("WorkPosition", occupation.WorkPosition, DbType.String); parameters.Add("WorkAddress", occupation.WorkAddress, DbType.String); parameters.Add("WorkPhoneNumber", occupation.WorkPhoneNumber, DbType.String); parameters.Add("Organization", occupation.Organization, DbType.String); parameters.Add("Designation", occupation.Designation, DbType.String); parameters.Add("OfficeName", occupation.OfficeName, DbType.String); using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal(dbString))) { await connection.ExecuteAsync("spOccupation_Update", parameters, commandType : CommandType.StoredProcedure); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error, update occupation", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public async Task UpdateAddress(Address address) { try { var parameters = new DynamicParameters(); parameters.Add("PersonId", address.PersonId, DbType.Int32); parameters.Add("HomeAddress", address.HomeAddress, DbType.String); parameters.Add("Town", address.Town, DbType.String); parameters.Add("Barangay", address.Barangay, DbType.String); parameters.Add("Sitio", address.Sitio, DbType.String); parameters.Add("Province", address.Province, DbType.String); parameters.Add("Country", address.Country, DbType.String); parameters.Add("ZipCode", address.ZipCode, DbType.String); using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal(dbString))) { await connection.ExecuteAsync("spAddress_Update", parameters, commandType : CommandType.StoredProcedure); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error, update address", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
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"); }
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 UpdatePassword(string password, int userId) { var q = @"UPDATE [User] SET Password=@password WHERE ID=@userId"; 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 FirstName=@name WHERE ID=@userId"; using (var con = new SqlConnection(ConnectionString)) { con.Open(); await con.ExecuteAsync(q, new { @userId = userProfileVm.Id, @name = userProfileVm.Name }); } }
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 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<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)) } }
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 }); } }
public async Task SaveNotificationSettings(UserEmailNotificationSettingsVM model) { //DELETE EXISTING var q = @"DELETE FROM UserNotificationSubscription WHERE UserId=@userId and TeamId=@teamId;"; 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 }); } } }
public async Task<int> Delete(Guid id) { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString)) { return await connection.ExecuteAsync("delete from Proposals where Id=@id;", new { id = id }); } }