public void DeletePerson(string lastName)
{
using (IDbConnection cnx = new System.Data.SqlClient.SqlConnection(Helper.CnxVal("SampleDB")))
{
cnx.ExecuteAsync("dbo.People_Delete @LastName", new { LastName = lastName });
}
}
public async Task CheckOutBook(T obj, string StoredProcedureParams)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Connection.ConnectionString))
{
await connection.ExecuteAsync(StoredProcedureParams, obj);
}
}
/// <summary>
/// Insert a Category for a person's Todo List.
/// </summary>
/// <param name="personID"></param>
/// <param name="title"></param>
/// <param name="desc">optional</param>
public Task InsertCategory(int personID, string title, string desc = null)
{
//This method returns a task
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(_config.GetConnectionString("TodoList")))
{
List <Category> categories = new List <Category>();
return(connection.ExecuteAsync("dbo.INSERT_CATEGORY @PersonID, @Title, @Description", categories));
}
}
public async Task <int> Update(BuyerInfo model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(_options.Value.DefaultConnection))
{
var sql = "dbo.UpdatePerson";
var param = new DynamicParameters();
param.Add("@Id", model.PersonId);
param.Add("@Name", model.Name);
param.Add("@Address", model.Address);
var sql2 = $"dbo.UpdateUnit";
var param2 = new DynamicParameters();
param2.Add("@Id", model.UnitId);
param2.Add("@ProjectName", model.ProjectName);
param2.Add("@CondoUnit", model.CondoUnit);
param2.Add("@LoanAmount", model.LoanAmount);
param2.Add("@Term", model.Term);
param2.Add("@StartOfPayment", model.StartOfPayment);
param2.Add("@InterestRate", model.InterestRate);
connection.Open();
using (var trans = connection.BeginTransaction())
{
try
{
await connection.ExecuteAsync(sql, param, trans, commandType : CommandType.StoredProcedure).ConfigureAwait(false);
await connection.ExecuteAsync(sql2, param2, trans, commandType : CommandType.StoredProcedure).ConfigureAwait(false);
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
return(0);
}
}
return(1);
}
}
public async Task InsertData(T obj, string StoredProcedureParams)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Connection.ConnectionString))
{
//Person newPerson = new Person { FirstName = firstName, LastName = lastName, EmailAddress = emailAddress, PhoneNumber = phoneNumber };
//List<Person> people = new List<Person>();
//people.Add(new Person { FirstName = firstName, LastName = lastName, EmailAddress = emailAddress, PhoneNumber = phoneNumber });
await connection.ExecuteAsync(StoredProcedureParams, obj);
}
}
public async Task <bool> Remove(int id)
{
using (var conn = new System.Data.SqlClient.SqlConnection(_cs))
{
conn.Open();
await conn.ExecuteAsync(@"
DELETE FROM dbo.SensorConfiguration
WHERE DeviceId = @id", new { id = id });
return(true);
}
}
/// <summary>
/// Inserts a Todo Item for a given person
/// </summary>
/// <param name="personID"></param>
/// <param name="categoryID">optional</param>
/// <param name="title"></param>
/// <param name="desc"></param>
public Task InsertTodoItem(int personID, string title, int categoryID = 0, string desc = null)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(_config.GetConnectionString("TodoList")))
{
List <TodoItem> todoItems = new List <TodoItem>();
todoItems.Add(new TodoItem {
PersonID = personID, CategoryID = categoryID, Title = title, Description = desc
});
return(connection.ExecuteAsync("dbo.INSERT_TODOITEM @PersonID, CategoryID, @Title, @Description", todoItems));
}
}
public async Task <bool> DeleteByBuyerInfoId(Guid buyerInfoId)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(_options.Value.DefaultConnection))
{
var sql = "delete from AmortizationSchedule where PersonUnitId = @Id";
var param = new { Id = buyerInfoId };
var result = await connection.ExecuteAsync(sql, param).ConfigureAwait(false);
return(result > 0); // return if any row has been affected
}
}
public async Task <bool> Add(DeviceMetadata deviceMetadata)
{
using (var conn = new System.Data.SqlClient.SqlConnection(_cs))
{
conn.Open();
await conn.ExecuteAsync(@"
INSERT INTO dbo.SensorConfiguration
(DeviceId, Name, Location, Description)
VALUES (@DeviceId, @Name, @Location, @Description)", deviceMetadata);
return(true);
}
}
public void InsertPerson(string firstName, string lastName, string emailAddress, string phoneNumber)
{
using (IDbConnection cnx = new System.Data.SqlClient.SqlConnection(Helper.CnxVal("SampleDB")))
{
List <Person> people = new List <Person>
{
new Person {
LastName = lastName, FirstName = firstName, EmailAddress = emailAddress, PhoneNumber = phoneNumber
}
};
cnx.ExecuteAsync("dbo.People_Insert @FirstName, @LastName, @EmailAddress, @PhoneNumber", people);
}
}
public async Task <bool> Exists(string email)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-2PQP7CF\SQL;Initial Catalog=TechProposalDB;Integrated Security=SSPI;User ID = sa; Password = Oracle1!;"))
{
const string storedProcedure = "dbo.userExists";
var p = new DynamicParameters();
p.Add("@pEmail", email);
p.Add("responseMessage", dbType: DbType.Boolean, direction: ParameterDirection.Output);
await connection.ExecuteAsync(storedProcedure, p, commandType : CommandType.StoredProcedure);
return(p.Get <bool>("responseMessage"));
}
}
public async Task <int> InsertSet(List <AmortizationSchedule> amortizations)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(_options.Value.DefaultConnection))
{
var sql = "dbo.InsertAmortizationScheduleSet";
var param = new
{
set = MapToDataTable(amortizations).AsTableValuedParameter("AmortizationScheduleUDT")
};
// return number of records affected
return(await connection.ExecuteAsync(sql, param, commandType : CommandType.StoredProcedure).ConfigureAwait(false));
}
}
/// <summary>
/// Inserts a new row into the Person table with the supplied parameters.
/// </summary>
/// <param name="lastName"></param>
/// <param name="firstName"></param>
/// <param name="emailAddress">optional</param>
/// <param name="birthDate">optional</param>
public Task InsertPerson(string lastName, string firstName, string emailAddress = null, string birthDate = null)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConfigurationManagerHelper.DBConn("Person")))
{
List <Person> people = new List <Person>();
//Todo: figure out the mumbo jumbo about converting a string to a date or just f*****g make the prop a date?
people.Add(new Person {
LastName = lastName, FirstName = firstName, EmailAddress = emailAddress, BirthDate = birthDate
});
//the parameters are filled in by the properties in the class if the names match up.
//returning this instead of awaiting it frees up this singleton to handle other requests.
return(connection.ExecuteAsync("dbo.PERSON_INSERTPERSON @LastName, @FirstName, @emailAddress, @BirthDate", people));
}
}
public async Task <bool> Update(DeviceMetadata deviceMetadata)
{
using (var conn = new System.Data.SqlClient.SqlConnection(_cs))
{
conn.Open();
await conn.ExecuteAsync(@"
UPDATE dbo.SensorConfiguration
SET Name = @Name,
Location = @Location,
Description = @Description
WHERE
DeviceId = @DeviceId", deviceMetadata);
return(true);
}
}
public async Task <bool> Delete(Guid id)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(_options.Value.DefaultConnection))
{
var query = $"select * from dbo.PersonUnit where Id = @Id";
var param = new { Id = id };
connection.Open();
using (var trans = connection.BeginTransaction())
{
try
{
var buyerInfo = await connection.QueryAsync <Entities.PersonUnit>(query, param, trans).ConfigureAwait(false);
var toBeDeleted = buyerInfo.FirstOrDefault();
var sql = $@"delete from dbo.PersonUnit where Id = @Id;
delete from dbo.Person where Id = @PersonId;
delete from dbo.Unit where Id = @UnitId";
var ids = new { Id = id, PersonId = toBeDeleted.PersonId, UnitId = toBeDeleted.UnitId };
var result = await connection.ExecuteAsync(sql, ids, trans).ConfigureAwait(false);
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
return(false);
}
}
return(true);
}
}
public async Task <Guid> Insert(BuyerInfo model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(_options.Value.DefaultConnection))
{
var sql = "dbo.InsertPerson";
var param = new DynamicParameters();
param.Add("@Name", model.Name);
param.Add("@Address", model.Address);
param.Add("@Id", null, DbType.Guid, ParameterDirection.Output);
var sql2 = $"dbo.InsertUnit";
var param2 = new DynamicParameters();
param2.Add("@ProjectName", model.ProjectName);
param2.Add("@CondoUnit", model.CondoUnit);
param2.Add("@LoanAmount", model.LoanAmount);
param2.Add("@Term", model.Term);
param2.Add("@StartOfPayment", model.StartOfPayment);
param2.Add("@InterestRate", model.InterestRate);
param2.Add("@Id", null, DbType.Guid, ParameterDirection.Output);
connection.Open();
using (var trans = connection.BeginTransaction())
{
try
{
// insert Person
await connection.ExecuteAsync(sql, param, trans, commandType : CommandType.StoredProcedure).ConfigureAwait(false);
var personId = param.Get <Guid>("@Id");
// insert Unit
await connection.ExecuteAsync(sql2, param2, trans, commandType : CommandType.StoredProcedure).ConfigureAwait(false);
var unitId = param2.Get <Guid>("@Id");
// setup PersonUnit
var sql3 = $"dbo.InsertPersonUnit";
var param3 = new DynamicParameters();
param3.Add("@PersonId", personId);
param3.Add("@UnitId", unitId);
param3.Add("@Id", null, DbType.Guid, ParameterDirection.Output);
// insert PersonUnit
await connection.ExecuteAsync(sql3, param3, trans, commandType : CommandType.StoredProcedure).ConfigureAwait(false);
var result = param3.Get <Guid>("@Id");
trans.Commit();
return(result);
}
catch (Exception)
{
trans.Rollback();
}
}
return(Guid.Empty);
}
}
