Docs / Parameters with MooParams

Parameters with MooParams

What this page covers#

This page explains how to build and read SQL Server parameters with MooParams.

What MooParams is#

MooParams is a fluent, strongly typed parameter builder.

It implements IReadOnlyList<SqlParameter>, which means you can pass it anywhere MooDb expects a parameter list.

Why it exists#

Working with SqlParameter directly is sometimes repetitive and easy to get wrong, especially for:

  • SQL types
  • parameter direction
  • string sizes
  • decimal precision and scale
  • table-valued parameters

MooParams keeps those choices visible.

Basic input parameters#

</> C# 
var parameters = new MooParams()
    .AddInt("@UserId", 42)
    .AddNVarChar("@Email", "ada@example.com", 256);

Output parameters#

</> C# 
using System.Data;

var parameters = new MooParams()
    .AddInt("@UserId", 42)
    .AddInt("@RowsAffected", null, ParameterDirection.Output);

await db.ExecuteAsync("dbo.usp_User_Update", parameters);

var rowsAffected = parameters.GetInt("@RowsAffected");

Input/output parameters#

</> C# 
using System.Data;

var parameters = new MooParams()
    .AddInt("@Counter", 10, ParameterDirection.InputOutput);

Table-valued parameters#

</> C# 
using System.Data;

var table = new DataTable();
table.Columns.Add("UserId", typeof(int));
table.Rows.Add(42);
table.Rows.Add(43);

var parameters = new MooParams()
    .AddTableValuedParameter("@Users", table, "dbo.udt_UserIds");

Main Add... groups#

MooParams includes methods for common SQL Server types, including:

  • AddBit
  • AddTinyInt
  • AddSmallInt
  • AddInt
  • AddBigInt
  • AddReal
  • AddFloat
  • AddUniqueIdentifier
  • AddDate
  • AddDateTime
  • AddSmallDateTime
  • AddDateTime2
  • AddDateTimeOffset
  • AddTime
  • AddDecimal
  • AddMoney
  • AddSmallMoney
  • AddChar
  • AddVarChar
  • AddNChar
  • AddNVarChar
  • AddBinary
  • AddVarBinary
  • AddXml
  • AddVariant
  • AddTableValuedParameter
  • Add(SqlParameter) as an escape hatch

Reading values back#

MooParams also has typed getters for output and input/output values, such as:

  • GetInt
  • GetNullableInt
  • GetString
  • GetNullableString
  • GetDateTime
  • GetNullableDateTime
  • GetDecimal
  • GetNullableDecimal
  • GetBinary
  • GetNullableBinary
  • GetValue
  • GetNullableValue

Important notes#

  • parameter names must be unique within the collection
  • string and binary methods require you to supply sizes where that matters
  • decimal methods require precision and scale
  • Get... methods throw if the value cannot be read as the requested type
  • GetNullable... methods return null when the underlying value is DBNull