I'm writing an open source application (https://github.com/J-east/WellsFargoPDFTaxExtractor) for importing bank statement PDF's into a sql server db instance. I'd like to have an easy method for taking a transaction object and updating the transactions database. See code below:
public class Transaction {
public int TransactionID;
public long accountNumber;
public DateTime TransDate;
public string Title;
public string Summary;
public string catagory;
public string typeOfTransaction;
public double amount;
}
public static bool UpdateRow(Transaction t) {
string sql = "update TRANSACTIONS SET ???? where TransactionID = @transactionID";
// code to take t and update the rows where TransactionID = @transactionID and things are different
return true;
}
EDIT: Thanks to @Loofer I have discovered this very cool tool: DapperContrib
[Table("Transactions")]
public class TransactionContrib {
[Key]
public int TransactionID { get; set; }
public long AccountNumber { get; set; }
public DateTime TransDate { get; set; }
public string Title { get; set; }
public string Summary { get; set; }
public string Catagory { get; set; }
public string TypeOfTransaction { get; set; }
public double Amount { get; set; }
}
public static bool UpdateRow(TransactionContrib t) {
string conStr;
if (Program.Settings.SqlSettings.IntegratedSec) {
conStr = $"SERVER={Program.Settings.SqlSettings.server};DATABASE={Program.Settings.SqlSettings.database};Integrated Security = SSPI;";
}
else {
conStr = $"SERVER={Program.Settings.SqlSettings.server};DATABASE={Program.Settings.SqlSettings.database};UID={Program.Settings.SqlSettings.userID};PWD={sqlpw}";
}
using (SqlConnection connection = new SqlConnection(conStr)) {
return connection.Update(t);
}
}