Get records from a stored procedure with Dapper while handing in a user-defined table type as a variable.

using (IDbConnection db = new SqlConnection(connectionString))
{
   DataTable scope = new DataTable("scope");
   scope.Columns.Add(new DataColumn("BusinessUnitID", typeof(int)));
   var row = scope.NewRow();
   row["BusinessUnitID"] = 13;
   scope.Rows.Add(row);
   return db.Query<BusinessUnitRecord>("UI.GetBusinessUnit", new { BusinessUnitId =
         scope.AsTableValuedParameter("dbo.BusinessUnitIDs") }, commandType:
         CommandType.StoredProcedure);
}

 
 

In this example dbo.BusinessUnitIDs may be found under "User-Defined Table Types" under "Types" under "Programmability" under the database in the "Object Explorer" in SSMS. The old school way of doing what is above in C# while merely returning a dataset is:

using (IDbConnection db = new SqlConnection(connectionString))
{
   DataTable scope = new DataTable("scope");
   scope.Columns.Add(new DataColumn("BusinessUnitID", typeof(int)));
   var row = scope.NewRow();
   row["BusinessUnitID"] = 13;
   scope.Rows.Add(row);
   db.Open();
   return db.Query("UI.GetBusinessUnit", new { BusinessUnitId =
         scope.AsTableValuedParameter("dbo.BusinessUnitIDs") }, commandType:
         CommandType.StoredProcedure);
}

 
 

In closing, as a non sequitur, I offer this picture that I took in the Mall of America today. I guess you can see the reflection of my feet. That's kinda weak.

 
 

Addendum 7/30/2019: I just today goofed off with the old school way above and could not get it to work. Perhaps something like so is better. dataTable below is what it is above.

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
   using(SqlCommand sqlCommand = new SqlCommand())
   {
      DataSet dataSet = new DataSet();
      using(SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
      {
         sqlCommand.CommandText = "UI.GetDivision";
         sqlCommand.CommandType = CommandType.StoredProcedure;
         sqlCommand.Parameters.Add(dataTable);
         sqlCommand.Connection = sqlConnection;
         sqlDataAdapter.SelectCommand = sqlCommand;
         sqlDataAdapter.Fill(dataSet);
      }
   }
}