Skip to content

AuthorDan

Blog owner.
Posted Databases

ORM vs TSQL (Benefits / Drawbacks)

I think this is a subjective matter, as there’s strong arguments for both. In my experience, I prefer using ORMs for simple applications in a small team. For enterprise apps with many developers, nothing beats the simplicity and performance of pure TSQL (or SQL for that matter). Yes, there could be a lot of boiler plate code, but that part can be automated.

And of course, doing both is still an option.

ORM

Benefits

  • Potentially less coding for accessing database data and basic CRUD operations.
  • Create a class and it writes out your database schema
  • Caching facilities are available for often-accessed data
  • Entity Framework is available to script out Database tabes to c# objects.
  • ORM syntax may generate database-independent code

Drawbacks

  • Does not use SQL, so new syntax has to be used for CRUD/maintenance operations.
  • Could be harder to debug since the SQL is abstracted. Have to run SQL profiler to view what gets actually generated.
  • May not be able to do everything that TSQL can do.
  • Have to know SQL anyways for things that ORM cannot do.
  • Because of its implicit caching nature, when dealing with huge amounts of data, caching may not be ideal (too much unnecessary caching)

TSQL

Benefits

  • Know exactly what you’re getting, since there’s no middle layer writing out your SQL
  • Potentially faster SQL and no need to go through the translation engine.
  • Every developer knows SQL – it’s most widespread
  • SQL is cross-platform (unless using specific Transact SQL commands)
  • No barriers to optimize TSQL
  • Instantly/efficiently troubleshoot with SQL Sever’s native tools (Management Studio/Query Analyzer)
  • SQL Server objects could be used cross-platform. A TSQL UDF, View, Stored Procedure could be easily used by any application, regardless of the platform: e.g. a PHP/Java/Ruby/CF app can use the same SQL Server Stored Procedure that a C# app is using.
  • Compiled stored procedures (written in SQL) are faster than ORM syntax.

Drawbacks

  • May not implicitly cache data like ORM layer, but can be cached with traditional C# code.
  • More TSQL/C# code may have to be potentially written using traditional TSQL and C#.
  • More advanced TSQL code may be heavily dependent on SQL Server, and may make database migration more challenging.
Posted Business Analysis

Requirements Analysis: Asking the Right Questions

Many times when you’re eliciting requirements, you may be asked the question:

“We need to add a textbox on this page. How long will it take?”

Well… what’s your answer? How do you follow-up? To aid your requirements analysis journey, here’s a checklist that may help you predict the effort involved.

To start off, let’s take a simple task, such as “add a textbox on this page” and see what it could potentially involve. These requirements may apply to other web components of a web page.

Usability Requirements

  • Should the TextBox have a label? Above it? To the left of it?
  • Is it a required field?
  • Should it be pre-populated?
  • Does it require AJAX functionality? Autocomplete? Retrieved from a defined set of data?
  • Is the TextBox already conveyed somewhere else in the form? (I’ve had times where the stakeholder didn’t know that the field was already in there. )
  • Where on the form should it be? (This is particularly important if you’re dealing with a form wizard. )
  • Should the data be tracked via some analytics tool?
  • How many characters are allowed?
  • Inline validation or validation after form submission? Error message at the top of the form?
  • JavaScript validation or server-side validation? Both?
  • Should the TextBox have inline help?
  • How prominent should it be? Above the fold?

Business Requirements

  • Does this TextBox show up to all users or just Administrators? (Depending if there’s a user privilege hierarchy in a CMS. ) What user roles are allowed to see this?
  • Does the data gathered in this TextBox go against the business rules of another product in the company? (For example, the TextBox could be prompting for your social security number, while Finance’s policy strictly forbids it. The stakeholder you may be dealing with may not know this.)
  • Does this have to be conveyed through reporting?

Content Requirements

  • What should the microcopy be for the label, for the error message, or text in the TextBox if it’s prepopulated?
  • What’s the translation in another language? (Ideally, find a native speaker; Google Translator may not be sufficient. )

Engineering Requirements

  • Do you need to modify application code to fit this TextBox in? Which class/file? New method? New property?
  • Do you need to add a new database column for this TextBox? Is there a column that you can reuse? Which table should you put it in?
  • Does this database column need an index? A new table? Referential integrity?
  • What data type is it? Varchar? NVarchar? Text?
  • Does it need to be indexed via a Full-Text Search Engine?
  • Does the data from the TextBox need to be in the user’s session?
  • Will you need to create new language tokens in your resource file to add the copy for the TextBox and label? Can you repurpose (or should you) existing ones?
  • Do you need to use JavaScript framework to validate it? Use an existing JavaScript validation function or roll your own?
  • Use CSS3 for styling? HTML5?
  • Should any API’s/Web Services be updated because of this?
  • Because this is a new field that was not introduced at the birth of the system, what should existing records that now have this new field, be set by default? Null? Empty string? Default text value? ID?
  • Does it impact any unit tests? Create new ones?

Testing Requirements

  • Are the Usability, Content, and Business requirements (shown above) met?
  • How does it display in various web browsers?
  • How well does it do in usability testing?

Deployment Requirements

  • Are the changes for this TextBox small enough so that it can be rolled out with the normal scheduled builds?
  • If not, when can it be deployed?
  • Does the build script or process need to be modified to roll out these changes?

Project Resource Requirements

  • Do you have the human resources (developer, designer, usability tester, etc.) to work on this?
  • Do you have to update documents (requirements document, project plan, etc.)?
  • What is the priority and risk for this project?
  • How much will it cost?

 

That’s just a high-level summary. I didn’t even mention SEM/SEO, branding or design requirements (if applicable). I’m sure you can go to a QA engineer, a content strategist, a designer, or a DBA, and get a break-down of even more details. No doubt that you can dig deeper, but this should put you on the right track.

Posted JavaScript

Getting Intellisense from JavaScript File

Let’s say you have main.js and you want intellisense from objects defined in BankAccount.js. You would have to do the following in main.js:

/// <reference path="BankAccount.js" />
function run() {
  var ba = new BankAccount();
}

and BankAccount.js is just:

function BankAccount() {
  var total = 0;
  this.GetTotal = function () {
    return total;
  }
  this.Deposit = function (n) {
    this.total = n;
  }
}

This is provided that both .js files are in the same directory – but they don’t have to be – use relative path?

Learn more about it here: http://msdn.microsoft.com/en-us/library/bb385682.aspx

Posted SQL Server

Better SSMS Objects Management with SQLTreeo

This is so cool and handy. You can hide and organize database objects (procs, tables, views, databases) in folders. This organization is saved locally to your machine, on a the server level, via extended properties. If you decide to save the info on the server, someone else who has the extension will view it organized accordingly.

Check it out.

Posted SQL Server

Importing Excel Data into SQL Server

Sometimes it can be a pain. Why? Because SSMS (or should I say SSIS) uses the Windows Excel driver, and it infers data types of the columns incorrectly, and will choke when you import. Sometimes it will work, sometimes not. So you have to import it as text. However, first you have to re-save the Excel file as a CSV (which will by default use commas as delimiters, and quotes for qualifiers).

So…

Step 1:
In Excel, save the file as a CSV, in this case, file.csv.

Step 2:
In SSMS, under the Import wizard, choose “Flat File Source:

Step 3:
Leave everything default, except use a one quote in the qualifier textbox:

STEP 4:
In the Advanced section, select all the columns and make sure the OutputColumnWidth is something big enough, larger than the size of the largest column. In this case, we’ll make it 150. Make sure the DataType is string [DT_STR]

Step 5:
Leave the rest of the settings except the target database/table where you’re importing, and Finish the import.

Step 6:
Done.

Posted Web Development

Immediate Window in VB.NET

If you’re in debugging a VB.NET execution in the Immediate Window, to view the value you use the question mark:

? strpassword

Otherwise you’ll get :

Property access must assign to the property or use its value.

Posted AWS

List All EC2 Instances

Been playing around a lot with the AWS API. Going to post a few handy snippets every once in a while.

Typically, you start EC2 instances by ID. In this case, we’re doing it by tags (e.g. the name of the instance).

namespace EC2_Instance
{
  class Program
  {
    public static void StartInstance(AmazonEC2Client c, string InstanceID)
    {
      StartInstancesRequest r = new StartInstancesRequest();
      r.InstanceId.Add(InstanceID);
      c.StartInstances(r);
    }

    public static void StopInstance(AmazonEC2Client c, string InstanceID)
    {
      StopInstancesRequest r = new StopInstancesRequest();
      r.InstanceId.Add(InstanceID);
      c.StopInstances(r);
    }


    public static void Main(string[] args)
    {    

      AmazonEC2Client ec2Client = new AmazonEC2Client();   
      var instancesRequest = new DescribeInstancesRequest();
     
      instancesRequest.Filter.Add(new Filter() { Name = "tag:Name", Value = new List { "*" } });

      DescribeInstancesResponse statusResponse = ec2Client.DescribeInstances(instancesRequest);

      foreach (var item in statusResponse.DescribeInstancesResult.Reservation)
      {
        Console.WriteLine(item.RunningInstance.ElementAt(0).Tag.Where(x => x.Key == "Name").First().Value);
        Console.WriteLine(item.RunningInstance.ElementAt(0).InstanceId);
        Console.WriteLine("\n");
      }     

      Console.ReadLine();
      Console.WriteLine("\n\nDone.");
    }
  }
}
Posted ASP.NET MVC

Invalidate (re-cache) Content on AWS CloudFront

This is a handy script I use to invalidate (re-cache) content on AWS’s CloudFront.

using System;
using System.Collections.Generic;
using System.Configuration;
using Amazon;
using Amazon.CloudFront.Model;
using Amazon.S3.Model;

namespace AwsConsoleApp1
{
  internal class Program
  {
    /// <summary>
    /// Invalidates only one file. e.g. /images/profile.gif
    /// </summary>
    /// 
    public static void InvalidateFiles(string file)
    {
      InvalidateFiles(new List() { file });
    }

    /// <summary>
    /// Invalidates only one file. Enter bucket name and key (file in the bucket).
    /// </summary>
    /// 
    /// 
    public static void InvalidateFiles(string bucket, string key)
    {
      InvalidateFiles(new List() { bucket + "/" + key });
    }

    /// <summary>
    /// Invalidates a list of files. e.g. /images/profile.gif, /home/index.html
    /// </summary>
    /// 
    public static void InvalidateFiles(List files)
    {
      // AWS client. You always create one.
      var cfClient = AWSClientFactory.CreateAmazonCloudFrontClient();

      // Create paths to files we want to invalidate
      Paths invalidationPaths = new Paths();
      // invalidationPaths.Items = new List() { "/images/dan1g.zip" };
      invalidationPaths.Items = files;
      invalidationPaths.Quantity = invalidationPaths.Items.Count;

      // Now... let's build the request...
      CreateInvalidationRequest invalidationRequest = new CreateInvalidationRequest();
      invalidationRequest.DistributionId = ConfigurationManager.AppSettings["DistributionID"];

      // ... and other required paramaters
      invalidationRequest.InvalidationBatch = new InvalidationBatch();
      invalidationRequest.InvalidationBatch.Paths = invalidationPaths;
      invalidationRequest.InvalidationBatch.CallerReference = Guid.NewGuid().ToString();

      // Execute the request and get back a response object.
      CreateInvalidationResponse response = cfClient.CreateInvalidation(invalidationRequest);

      // Starting
      System.Console.WriteLine("Initiated On: " + response.CreateInvalidationResult.Invalidation.CreateTime);
      System.Console.WriteLine("InvalidationID: " + response.CreateInvalidationResult.Invalidation.Id);
      System.Console.WriteLine("Status: " + response.CreateInvalidationResult.Invalidation.Status);
    }

    /// <summary>
    /// Check if a file exists on S3.
    /// </summary>
    /// 
    /// 
    /// 
    public static bool IsFileInBucket(string bucket = "", string key = "")
    {
      bool exist = true;
      var s3Client = AWSClientFactory.CreateAmazonS3Client();
      try
      {
        GetObjectResponse oResponse = s3Client.GetObject(new GetObjectRequest() { BucketName = bucket, Key = key });       
      }
      catch (Exception e)
      {
        Console.WriteLine("\n" + e.Message);
        exist = false;
      }

      return exist;
    }

    /// <summary>
    /// Main Caller.
    /// </summary>
    /// 
    public static void Main(string[] args)
    {
      if (args.Length == 2)
      {
        string bucketName = args[0];
        string key = args[1];

        if (args != null &amp;&amp; IsFileInBucket(bucketName, key))
        {
          InvalidateFiles(bucketName, key);
        }
        else
        {
          Console.WriteLine("\n\nDone.");
        }
      }
      else
      {
        Console.WriteLine("\nUse:\n   S3-Invalidate BUCKETNAME KEY\n\ne.g.\n   S3-Invalidate images photo.gif\n");
      }

      // Console.ReadKey();
    }
  }
}
Posted Visual Studio

Rename Computer Name in Case TFS Gives you Workspace Problems

When you are setting up TFS through Visual Studio, and are trying to map a drive, TFS may give you an error saying that you can’t and that profile exists on the TFS server. It may be because the account+computername+workspace (they’re all bound) is already on the server.

The way around this is to rename the machine. This may have happened because the virtual machine was copied.

Be aware: IF YOU DO THIS, other programs that have the computer name saved to it under privileges, like SQL Server, may have to be changed. In SQL Server, you may have to login via the sa account or through single-user mode.

Posted SQL Server

Who’s Locking The Table?

This is the script I use to view who/what is locking the table in SQL Server:

SELECT  l.request_session_id AS SessionID,
        o.Name AS 'Table Locked',
        DATEDIFF(second, at.Transaction_begin_time, GETDATE()) AS 'Duration (Seconds)',
        FORMAT(at.Transaction_begin_time, 'yyyy-mm-dd h:mm:ss tt') 'Started On',
        es.login_name 'User',
        es.[program_name] 'Program',
        COUNT(*) AS Locks
FROM    sys.dm_tran_locks l
        JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
        JOIN sys.objects o ON o.object_id = p.object_id
        JOIN sys.dm_exec_sessions es ON es.session_id = l.request_session_id
        JOIN sys.dm_tran_session_transactions st ON es.session_id = st.session_id
        JOIN sys.dm_tran_active_transactions at ON st.transaction_id = at.transaction_id
WHERE   resource_database_id = DB_ID()
        AND o.Type = 'U'
GROUP BY at.Transaction_begin_time,
        l.request_session_id,
        o.Name,
        es.login_name,
        [program_name]
Posted SQL Server

Restoring an MDF/LDF from Another Machine

If you need to restore an MDF/LDF from one machine (source) into another machine (target), you copy the source’s MDF/LDF file to the target machine. Then start the target server in single user mode. Then run SSMS as Administrator on the target machine and restore the files. If you don’t run it as Admin, you’ll get the error listed here:

http://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database

http://stackoverflow.com/questions/4661013/sql-server-attaching-database-access-denied