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 });
}
}
