C# – How to update a DB table using a POCO class bound in a DataGridView

centity-framework

I try to explain better using an example:

I create the class Product with attibutes like that:

private string _ProductCode = "";
[DisplayName("ProductCode")]
public String ProductCode
{
   get { return _ProductCode; }
   set { _ProductCode = value; OnPropertyChanged("ProductCode"); }
}

the class Product implements the interfaces: INotifyPropertyChanged, IEditableObject
I create the class ProductList that has the following property:
```
public List<Product> Products { get; set; }
```
I bind the class Product in a DataGridView control using the DataSource property
```
MyGridView.DataSource = ProductList.Products;
```
I have the table ProductListTable on db SqlServer that has more column than the class Product
When I push the button "Save" on my form I'd would like to save the ProductList on ProductListTable

I'm really confusing about the way to follow … I have to use Link 2 Sql, Entity Framework? I'm searching for something simple for a small project, but using POCO classes for rapresenting the data layer.

Now how I should proceed for linking class to DB table?

Best Answer

You can use Dapper. It is really simple, it boils down to having right sql. Here is example. PS: Use NuGet in VS and search for Dapper.

using(var con = new SqlConnection(connectionString))
{
    con.Open();
    con.Execute(
        @"insert ProductListTable(ProductCode, ...) values (@ProductCode, ...)",
        ProductList.Products
    );
}

Related Solutions

C# – When to Use a 2-Property Class Over KeyValuePair

I'd generally use an object rather than a KeyValuePair or Tuple in most cases. First, when you come in 6 months later to make changes, it is alot easier to figure out what your intent was earlier rather than wondering what Tuple t is and why it has those funny values. Second, as things grow and change you can easily give your simple data transfer objects behavior as required. Need to have two name formats? Easy, just add appropriate ToString() overloads. Need it to implement some interface? No problem. Finally, there really is nearly zero overhead to creating a simple object, especially with automatic properties and code completion.

Bonus Protip: if you want to keep these objects from polluting your namespace, making private classes inside of classes is a great way to keep things under wraps and prevent strange dependencies.

Encapsulation in Domain Driven Design models

There is a fundamental difference between your Data Access Object (DAO) and your Business Object (BO). Your DAO is a one-to-one mapping of what your data store looks like. In this case, for your AccountDAO object, the AccountNo and Balance properties will have getters and setters. Your Account object - the business layer objects - will not look the same as your DAO.

// This is a DAO
public class AccountDAO {
   public string AccountNo { get; set; }
   public decimal Balance { get; set; }
}

// This is a business object
public class Account {
   private readonly string accountNo;
   private readonly decimal openingBalance;
   private readonly List<decimal> adjustments; // This should probably be a list of transactions.

   public class Account(string accountNo, decimal openingBalance) {
      this.accountNo = accountNo;
      this.openingBalance = openingBalance;
      this.adjustments = new List<decimal>();
   }

   public string AccountNo {
      get { return accountNo; }
   }

   public decimal Balance {
      get { return openingBalance + SumOfAdjustments(); }
   }

   public void Deposit(decimal amount) {
      adjustments.Add(amount);
   }

   public void Withdraw(decimal amount) {
      decimal testBalance = Balance - amount;
      if (testBalance < 0.0m)
         throw new AccountBalanceException("Account Balance for " + accountNo + " is less than $0.00.");

      adjustments.Add(-1.0m * amount);
   }

   private decimal SumOfAdjustments() {
      decimal sum = 0.0m;
      foreach (var adjustment in adjustments) {
         sum += adjustment;
      }
      return sum;
   }
}

// Let's come up with a good way of performing actions on an account.    
public class CashDepositActivity {
   private readonly Account account;
   private readonly decimal amount;

   public CashDepositAction(Account account, decimal amount) {
      this.account = account;
      this.amount = amount;
   }

   public void ExecuteAction() {
      // You should probably have some logging here.
      account.Deposit(amount);
   }
}

public class BalanceTransferActivity {
   private readonly Account sourceAccount;
   private readonly Account targetAccount;
   private readonly decimal amount;

   public BalanceTransferActivity(Account sourceAccount, Account targetAccount, decimal amount) {
      this.sourceAccount = sourceAccount;
      this.targetAccount = targetAccount;
      this.amount = amount;
   }

   public void ExecuteAction() {
      sourceAccount.Withdraw(amount);
      targetAccount.Deposit(amount);
   }  
}

This is just an example, of course, and it is by no means an end-all solution. I know tools like EF have myriad examples where the data object, business object, and view/edit object are all the same object, passed from the database to view. In my experience, that works great on small projects where there is not a lot of business logic. However, it breaks down as your business rules and access restrictions grow in complexity.

Best Answer

Related Solutions

C# – When to Use a 2-Property Class Over KeyValuePair

Encapsulation in Domain Driven Design models

Related Topic