SqlProcBinder

Calling stored procedure from C# is quite verbose with ADO.NET. This library lets you call stored procedure like calling a general C# method.

Here is a stored procedure Sum that program need to call.

CREATE PROCEDURE [dbo].[Sum] (@a int, @b int, @ans int OUTPUT) AS BEGIN
    SET @ans = @a + @b
END

SqlProcBinder.CodeGenerator reads stored procedures and generates stub classes like:

public class Sum {
 public static async Task<Result> ExecuteAsync(IDbContext dc, int a, int b) {
   var ctx = dc.CreateCommand();
   ...
 }
}

With this generated class, it becomes easy to call stored procedures from C#.

var r = await Sum.ExecuteAsync(dc, 1, 2);
Console.WriteLine(r.ans); // 3

Where can I get it?

PM> Install-Package SqlProcBinder
PM> Install-Package SqlProcBinder.CodeGenerator

How to use

Generate stub class

The first thing to do is generating stub code. Stored procedure files are required in this process. For example, Sum.sql is provided, following command generates Sum.cs. (CodeGenerator.exe might be found at Packages/SqlProcBinder.CodeGenerator.0.3.1/tools directory.)

CodeGenerator.exe -s Sum.sql -t Sum.cs

When there're plenty of SQL files or elaborate control is required, external option files can be used like:

CodeGenerator.exe -i SqlProc.json -t Sum.cs

The example of external option is here. Also you can see generated class here by this option.

Using stub class

Let generated Sum.Sql file to be included in your project. And make sure that SqlProcBinder package is also installed.

var r = await Sum.ExecuteAsync(dc, 1, 2);
Console.WriteLine(r.ans); // 3

dc is an instance of IDbContext type. It is simply ok with creating SimpleDbContext and passing it. But if you want to do something before and after calling stored procedure, it is better to write own class implementing IDbContext.

Features

Output parameter

Output parameter can be used. Following procedure specifies @ans parameter as an output one.

CREATE PROCEDURE [dbo].[Sum] (@a int, @b int, @ans int OUTPUT) AS BEGIN
    SET @ans = @a + @b
END

Matched C# feature is out but C# async function doesn't allow out parameter so return variable is used to fetch the output values.

public class Sum {
    public struct Result {
        public int AffectedRowCount;
        public int ans;
    }
    public static async Task<Result> ExecuteAsync(IDbContext dc, int a, int b)  {
       ...

var r = await Sum.ExecuteAsync(dc, 1, 2);
Console.WriteLine(r.ans); // 3

Return value

Return value of stored procedure can be understood as a special output value. So you can use it like output parameter.

CREATE PROCEDURE [dbo].[Sum] (@a int, @b int) AS BEGIN
    RETURN @ans = @a + @b
END

public class Sum {
    public struct Result {
        public int AffectedRowCount;
        public int Return;
    }
    public static async Task<Result> ExecuteAsync(IDbContext dc, int a, int b)
        ...

var r = await SumAndReturn.ExecuteAsync(dc, 1, 2);
Console.WriteLine(ret.Return); // 3

Nullable

By default, all paramters are non-nullable because null is not easy to tame. When you don't pass output value in stored procedures, zero value of T would be fetched for that variable.

But when Nullable property is set in Procs, null value can be used.

{ "Path": "Sum.sql", "Nullable": true }

Generated class would be like following and null will be around the corner.

public class Sum {
    public struct Result {
        public int AffectedRowCount;
        public int? answer;
    }
    public static async Task<Result> ExecuteAsync(IDbContext dc, int? a, int? b)
        ...

var r = await SumAndReturn.ExecuteAsync(dc, 1, null);
Console.WriteLine(ret.Return); // null

RaiseError

SQL has RAISERROR statement for raising error like throw of C#. When error is raised in stored procedure, this error can be propagated into calling C# method.

CREATE PROCEDURE [dbo].[Error] @msg as nvarchar(100) AS BEGIN
    RAISERROR (@message, 16, 1)
END

Raised error is propagated as a SqlException.

try {
    await Error.ExecuteAsync(dc, "Test");
}
catch (SqlException ex) {
    Console.WriteLine(ex.Message); // "Test"
}

Return Rowset

Following stored procedure returns rowset [1..@count] values.

CREATE PROCEDURE [dbo].[GenerateInt] (@count as int) AS BEGIN
    SELECT TOP (@count) n = ROW_NUMBER() OVER (ORDER BY number)
    FROM [master]..spt_values ORDER BY n
END

To receive rowset, Rowset property should be set in Procs.

{ "Path": "GenerateInt.sql", "Rowset": "DbDataReader" },

With generated class, you can fetch rowset via Result.Rowset variable. And don't forget that Rowset instance should be disposed.

var ret = await GenerateInt.ExecuteAsync(dc, 10);
using (ret.Rowset)
{
    while (await ret.Rowset.ReadAsync())
        Console.WriteLine(ret.Rowset.GetInt32(0)); // 1 2 3 ... 10
}

Typed Rowset

DbDataReader doesn't provide compile time type safety. To handle this limitation typed rowset is provided.

For receiving a typed rowset, DrInt is configured in Procs and Rowsets. Fields property defines fields of Rowset and format mimicks C# struct member declaration.

{
  "Procs": [ { "Path": "GenerateInt.sql", "Rowset": "DrInt" } ],
  "Rowsets": [ { "Name": "DrInt", "Fields": ["int Value" ] } ]
}

With typed rowset, you can use it as List<Row>.

var ret = await GenerateInt.ExecuteAsync(dc, 10);
foreach (var row in await ret.Rowset.FetchAllRowsAndDisposeAsync())
    Console.WriteLine(row.Value); // 1 2 3 ... 10

Return Rowset as List<Row>

If you want to use typed rowset more simply, RowsetFetch can be an option.

{ "Path": "GenerateInt.sql", "Rowset": "DbDataReader", "RowsetFetch": true }

Returned Rows is List<Row> which is a way easier to use.

var ret = await GenerateInt.ExecuteAsync(dc, 10);
foreach (var row in ret.Rows) // Rows is type of List<Row>
    Console.WriteLine(row.Value); // 1 2 3 ... 10
}

User Table Type

MSSQL provides a way passing table data to stored procedures.

CREATE TYPE [dbo].[Vector3List] AS TABLE(
    [X] [float] NOT NULL,
    [Y] [float] NOT NULL,
    [Z] [float] NOT NULL
)

CREATE PROCEDURE [dbo].[Vector3ListSum]
    @values Vector3List READONLY,
    @ans float OUTPUT
AS
BEGIN
    SELECT @ans = SUM(X) + SUM(Y) + SUM(Z) FROM @values
END

Instance of DataTable is used for passing data. For type-safety, table-type class can be generated.

"TableTypes": [ { "Path": "Vector3List.sql" } ]

Previous option generates Vector3List class.

public class Vector3List {
    public DataTable Table { get; set; }
    public void Add(double X, double Y, double Z) { ... }
}

With generated Vector3List, table data can be passed to stored procedures safely.

var list = new Sql.Vector3List();
list.Add(1, 2, 3);
list.Add(4, 5, 6);

var ret = await Vector3ListSum.ExecuteAsync(_db.DbContext, list.Table);
Console.WriteLine(ret.ans); // 21