NPoco tricks

NPoco is continuation of PetaPoco Micro ORM .NET solution.

As open source project is not very well documented so I'll write down few tricks picked up along the way.


1. For SQL code cosmetics :

Sql sql = Sql.Builder
sql.Append(";declare @@countResViews int ");
sql.Append("set @@countResViews = (select count(*) from MYTABLE where Expiry<=GETDATE() and                      UserId<>@0 and CampaignId =@1) ", userId, campaignId);

sql.Append("select @@countResViews ");

2.  Using SQL params inside T-SQL that should not be mapped to provided arguments:

• Place ;  on very beginning of T-SQL statement.
• Use double @ for your vars.

Example:

                Sql sql = Sql.Builder.Append(string.Concat(
                                             ";declare @@countResViews int ",
                                             "set @@countResViews = (select count(*) from MYTABLEwhere Expiry<=GETDATE() and UserId<>@0 and CampaignId =@1) ",
                                             "select @@countResViews ")
                                             , userId, campaignId);
                                         
                int count = db.FirstOrDefault<int>(sql);

3. Semicolon is needed when not starting SQL with SELECT.

4. Executing stored proc and getting result dataset is straightforward:

sql.Append(";exec dbo._TempPostboxReqAdConsumation");
List<Product> list = db.Fetch<Product>(sql); 

5. Fetch lambdas always return List, NEVER null. Hence check for Count==0 not NULL:

 List<Campaign> dbCampaigns = db.FetchWhere<Campaign>(c => c.Name == campaign.Name);
                Campaign dbCampaign = dbCampaigns.Count==0 ? new Campaign() : dbCampaigns.FirstOrDefault();

6. Composite Primary key syntax
 If you have Primary key consisting of two columns use this syntax:

[TableName("UserCampaigns")]
    [PrimaryKey("UserId,CampaignId", AutoIncrement = false)]
    public partial class UserCampaign
    {
        public int UserId { get; set; }
        public int CampaignId { get; set; }
        public int StatusId { get; set; }
    }

Obtaining record by PK when composite PK:
      UserCampaign userCampaignDB = db.SingleOrDefaultById<UserCampaign>(
                                                                       new { UserId = 4,CampaignId = 3 }
                                                                );
7. Executing stored proc and returning output var & result rowset:

    var totalRecords = new SqlParameter("4", System.Data.SqlDbType.Int);
   totalRecords.Direction = System.Data.ParameterDirection.Output;
sql.Append(";EXEC GetActiveCampaigns @0, @1, @2, @3, @4 OUTPUT", user.Id,     UserCampaignStatus.New, page, itemsPerPage, totalRecords);

Not related strictly to NPoco but I keep forgetting it :
connection string ...   providerName="System.Data.SQLClient"

8. One to many join:

var sql = Sql.Builder.Append("select a.*,r.* from ApiIdentifier a")
.Append("left join ApiIdentifierRole ar on ar.ApiIdentifierId = a.Id")
.Append("left join ApiRole r on r.Id = ar.RoleId"); 

var adIdents = db.FetchOneToMany<ApiIdentifier, ApiRole>(apid => apid.Id, sql);