Methods - Configuration

There are a number of options available to customize each individual Sql call made using Tycho.Sql.

SetTimeout - Define a per-call timeout which overrides the global default. A value of 0 disables the timeout feature allowing the sql command to run until finished. The value passed to this method represents seconds of time.

sql.Statement("some very long-running sql")
    .SetTimeout(300);       // 5 minutes

OnException - Define a per-call exception handler which overrides the global default. The rules for defining the exception handler are identical to those defined in the Sql Constructor.

sql.Statement("some sql which may cause a deadlock")
    .OnException(async (ex) => {
        // when killed by deadlock, sleep 1/2 second and re-try
        bool result = ex is SqlException && ((SqlException)ex).Number == 1205;

        if (result)
            await Task.Delay(500);

        return result;
    });

OnBeforeCommandExecute - Define a delegate which is executed at the same time as the sql command. This method was designed to give you access to inspect the raw SqlCommand object in parallel with execution. It could be used, for example, to write log records containing details from the raw SqlCommand during the time the sql server is busy executing the command itself. The action can be defined as sync or async:

OnBeforeCommandExecute( Action<SqlCommand> Handler )
...
OnBeforeCommandExecute( Func<SqlCommand, Task> Handler )

While it is technically possible to modify the properties of the SqlCommand in this delegate, we can only speculate as to what effect that would have at execution time. The delegate you define will be run in parallel regardless of whether you define it as sync or async. If you attempt to modify the SqlCommand, there is no guarantee it will take place before ADO.NET passes the command to the Sql Server for processing. In short, just don't do this.

Methods - Parameter Passing

AddParameter - Pass a parameter into the sql call. To do this, you need the name of the variable in sql, the value of the object from your application, and the Sql Native data type to be used.

AddParameter(string Name, object Value, System.Data.SqlDbType Type)

ADO.NET handles conversion of your object Value to the appropriate Sql Native Type.

sql.Statement("select @integerValue 'int', @stringValue 'string'")
    .AddParameter("@integerValue", 45, SqlDbType.Int)
    .AddParameter("@stringValue", "a string", SqlDbType.VarChar)
    .Execute();

AddOutputParameter - Output parameters are handled through the use of callback delegates. Three overloads are provided which, one of which should handle nearly every use case necessary. In each you will see an identical "Action<T> OutputDelegate" parameter which allows you to define a custom handler on which you can receive the data.

AddOutputParameter<T>(
    string Name, 
    SqlDbType Type, 
    Action<T> OutputDelegate, 
    T InitialValue = default)

AddOutputParameter<T>(
    string Name, 
    SqlDbType Type, 
    int Size, 
    Action<T> OutputDelegate, 
    T InitialValue = default)

AddOutputParameter<T>(
    string Name, 
    SqlDbType Type, 
    byte Precision, 
    byte Scale, 
    Action<T> OutputDelegate, 
    T InitialValue = default)

The first overload is for numeric integer values.

// setup
int? initialValue = 2;
int? expectedValue = 3;
int? actualValue = null;

var sql = new Sql("TychoSqlTests");

// act
sql.Statement(@"SET @notNullValue = @notNullValue + 1")
    .AddOutputParameter(
        Name: "@notNullValue",
        Type: SqlDbType.Int,
        OutputCallback: (t) => actualValue = t,
        InitialValue: initialValue)
    .Execute();

// assert
Assert.AreEqual(expectedValue, actualValue);

The second overload is for STRING output. It adds a "Size" parameter which informs ADO.NET what the expected maximum length of the output will be from Sql Server:

// setup
string initialValue = "value";
string expectedValue = "value output";
string actualValue = null;

var sql = new Sql("TychoSqlTests");

// act
sql.Statement(@"SET @notNullValue = CONCAT(@notNullValue, ' output')")
    .AddOutputParameter(
        Name: "@notNullValue",
        Type: SqlDbType.VarChar,
        Size: 20,
        OutputCallback: (t) => actualValue = t,
        InitialValue: initialValue)
    .Execute();

// assert
Assert.AreEqual(expectedValue, actualValue);

The third overload is for floating point OUTPUT parameters. It defines "Precision" and "Scale" parameters which allow you to tune the data retrieval and prevent rounding errors:

// setup
double? initialValue = 2;
double? expectedValue = 3;
double? actualValue = null;

var sql = new Sql("TychoSqlTests");

// act
sql.Statement(@"SET @notNullValue = @notNullValue + 1")
    .AddOutputParameter(
        Name: "@notNullValue", 
        Type: SqlDbType.Float, 
        Precision: 20, 
        Scale: 20, 
        OutputCallback: (t) => actualValue = t, 
        InitialValue: initialValue)
    .Execute();

// assert
Assert.AreEqual(expectedValue, actualValue);

AddTableParameter - Table Parameters can be amazingly powerful when you need to move bulk data into a Sql context. They can, however, be a little tricky to setup and get operational. Traditional ADO.NET calls provide this functinoality, but it is a bit difficult to use. Tycho.Sql has attempted to simplify their usage in an effort to "lower the bar" on the technical knowledge necessary to use this powerful feature.

AddTableParameter<T>(string Name, IEnumerable<T> TableData)

You will notice that this method accepts an enumerable of a generic object type. It is easiest to think of this as a collection of DTOs (Data Transfer Objects). In Tycho.Sql, a Table Parameter DTO needs to be defined using some very specific Attributes:

[TableParameter("dbo.MyTableType")]
public class TableTypeWithParameters
{
    [TableParameterColumn("FirstColumn", MaxLength = 20)]
    public string Id { get; set; }
    [TableParameterColumn("SecondColumn", IsNullable = false)]
    public bool BooleanValue { get; set; }
    [TableParameterColumn("ThirdColumn", IsNullable = false)]
    public int ThirdValue { get; set; }
}

The DTO class itself needs to be decorated with a "TableParameter" attribute which defines the underlying Sql Table Type:

CREATE TYPE [dbo].[MyTableType] AS TABLE ( 
    [FirstColumn] [VARCHAR](100) NULL,
    [SecondColumn] [BIT] NOT NULL,
    [ThirdColumn] [INT] NOT NULL 
)

When Sql Server receives this bulk data in the form of a table parameter, it binds that data directly to your defined table type, at which point it is treated just like any other data source and can be used in any normal Sql statement (SELECT, INSERT, DELETE, MERGE, ...)

Each property of the DTO that you want to bind to a column of the Sql Table Type will need a "TableParameterColumn" attribute. This "TableParameterColumn" attribute requires a ColumnName to bind to, and allows you to specify 2 optional properties:

• IsNullable - Boolean indicating if the column accepts null values.
• MaxLength - For string values, this is the maximum length for the column defined in the table type.

It is really that easy to use full power of Table Parameters!

1. Define a Sql Table Type
2. Define a DTO in your application
3. Add data to any IEnumerable<YourDTO>
4. Pass it as a Table Parameter

Table Parameters work with both Statements and Procedures in Tycho.Sql. The question often arises about how much data you can send with a single table parameter. There is really no easy way to answer this as it depends on many factors. My personal preference is usually somewhere between 1,000 - 10,000 records per batch of inserts/updates, and 50-100 records per batch of deletes.

Methods - Execution

Both sync and async execution methods are available for your application to use.

Execute / ExecuteAsync - Execute the defined action returning the number of records affected.

int Execute()
async Task<int> ExecuteAsync()

It should be noted that Tycho.Sql is not doing anything nefarious to get the number of records affected. It is a standard output of ADO.NET unless you have specifically enabled the "NOCOUNT" feature of Sql in your Statement or Procedure (in which case it will return 0).

ExecuteScalar / ExecuteScalarAsync - Execute the defined action returning the value from the 1st column of the 1st record.

T ExecuteScalar<T>()
async Task<T> ExecuteScalarAsync<T>()

Tycho.Sql does its best to cast the output data to your application specified generic type. It is best to not push your luck too much here. If Sql is returning a VARCHAR, cast that as a string ... casting it as a double or a boolean may have unexpected results.

ExecuteReader / ExecuteReaderAsync - Execute the defined action returning data to a SqlReader for your application to handle.

Take note there are a LOT of overloads of these methods (about 20 each at last count). To shorten this conversation, we will discuss the "sync" versions of the ExecuteReader method, so just keep in mind that everything we discuss has an "async" alternative also.

First lets discuss processing output on a record-by-record basis. ExecuteReader can accept an Action delegate which is used to process each record of the result set sequentially:

void ExecuteReader(Action<SqlReader> Action)
---
sql.Statement("SELECT * FROM [MyTable]")
    .ExecuteReader(dr => {
        // do something with each row
    });

We will touch on the SqlReader itself in another section, for now just take note that it is available as an Action parameter.

Some Sql returns multiple data sets. Tycho.Sql handles this by natively providing support for up to 10 result sets, each of which can define a custom Action with its own SqlReader tailored to that result set:

void ExecuteReader(
    Action<SqlReader> Action1,
    Action<SqlReader> Action2,
    ...
    Action<SqlReader> Action10)

If your result data sets are small enough (or your system resources are large enough), you may want to marshall all the data back into an IEnumerable to simplify your processing code. Tycho.Sql provides Function Delegate overloads of ExecuteReader to serve this purpose:

IEnumerable<T> ExecuteReader<T>(Func<SqlReader, T> Delegate)
---
var myOutput = sql.Statement("SELECT * FROM [MyTable]")
    .ExecuteReader(dr => new 
    {
        v1 = dr.GetString("Column1"),
        v2 = dr.GetInt32("Column2"),
    });

In this example we have retrieved the full result set into an IEnumerable of Anonymous Type objects. It could have just as easily created a set of DTOs:

var myOutput = sql.Statement("SELECT * FROM [MyTable]")
    .ExecuteReader(dr => new MyDTO(dr));

Now that DTO constructor can define what data points it wants to extract from the SqlReader.

Just like the Action delegates above, Tycho.Sql provides support for up to 10 result sets:

MultipleResultSets<T1, T2, ... T10> ExecuteReader<T1, T2, ... T10>(
    Func<SqlReader, T1> Delegate1,
    Func<SqlReader, T2> Delegate2,
    ...
    Func<SqlReader, T10> Delegate10)

Next

SqlReader