Primary Key & Identity Fields

In the secnarios where you need to build Update,Delete, or Upsert Statements. Attributes are use to generate the where clause.

Decorating Properties As Primary Key

// USING SqlColumn Attribute
public class Employee {
      [SqlColumn(SetPrimaryKey = true)]
      public int PrimaryKey {get; set;}
      public string FirstName { get; set; }
      public string LastName  { get; set; }
}

OR

// USING DataAnnotation Attribute
public class Employee {
      [Key]
      public int PrimaryKey {get; set;}
      public string FirstName { get; set; }
      public string LastName  { get; set; }
}

using either version of the model above I can now generate update,delete, & upsert statment by doing the following

   var actionType = ActionType.Update; // A enum with the values Insert,Update,Delete,Upsert
   var sqlServerObjectToSql = new ObjectToSql(DataBaseType.SqlServer);
   var updateSql = sqlServerObjectToSql.BuildQuery<Employee>(actionType);
   var upsertSql = sqlServerObjectToSql.BuildQuery<Employee>(ActionType.Upsert,"Employee");
   var deleteSql = sqlServerObjectToSql.BuildQuery<Employee>(ActionType.Delete,"TableName");

   Console.WriteLine(updateSql);
   Console.WriteLine(upsertSql);
   Console.WriteLine(deleteSql);

running the code above will produces the following sql statments

UPDATE Employee SET [FirstName]=@FirstName,[LastName]=@LastName WHERE [PrimaryKey]=@PrimaryKey

IF EXISTS ( SELECT TOP 1 * FROM Employee WHERE [PrimaryKey]=@PrimaryKey ) BEGIN UPDATE Employee SET [FirstName]=@FirstName,[LastName]=@LastName WHERE [PrimaryKey]=@PrimaryKey END ELSE BEGIN INSERT INTO Employee ([FirstName],[LastName],[PrimaryKey]) VALUES (@FirstName,@LastName,@PrimaryKey) END

DELETE FROM TableName WHERE [PrimaryKey]=@PrimaryKey

Warning

Executing the a update,upsert, or delete query with an object that doesn't have any key attributes will lead to an InvalidOperationException being thrown.

Creating DB Parameters From Object

var parameters = sqlServerObjectToSql.BuildDbParameterList(new Employee(), (s, o) => new SqlParameter(s, o));

[Tip] The method BuildDBParameterList has an overload that accepts Func<object, string> to allow for columns to be serialize for those senarios where your storing properties as json,csv or xml