MS SQL - T-SQL - tips, hints & tricks

Important concept usually forgotten when trying to optimize SQL statements.
SQL is declarative !
Read here:

http://stackoverflow.com/questions/11169550/is-there-a-speed-difference-between-cte-subquery-and-temp-tables
 

Microsoft.VisualStudio.TestTools.UnitTesting hints

My experience with Microsoft.VisualStudio.TestTools.UnitTesting.

Attributes [TestInitialize] & [TestCleanup] are executed after each test marked with [TestMethod].
So if you have test class like this:

[TestClass]
public class Test
{

[TestInitialize]
public void Initialize

[TestMethod]
public void GetIsAdAvailable_InvalidCampaignId_ShouldFail()

[TestMethod]
public void GetIsAdAvailable_ValidCampaignId_InvalidCampRange_ShouldFail()

[TestCleanup]
public void Cleanup()

}

If you run all tests here is execution flow:

Initialize

GetIsAdAvailable_InvalidCampaignId_ShouldFail

Cleanup

Initialize

GetIsAdAvailable_ValidCampaignId_InvalidCampRange_ShouldFail

Cleanup

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);