How to read from SQL Server table with RhinoETL framework?

Question

Many sample codes on RhinoETL talk about loading from csv files and insert into database. How to perform a read from SQL Server database and output its rows to console ? Do you have a sample code to perform simple dbread and batch read ?

The author's blog showed some sample code, but it appears to be incomplete, or I am missing something.

My non-working code

public class ReadBuildInfos : ConventionInputCommandOperation
{
    public ReadBuildInfos(ConnectionStringSettings csSettings) : base(csSettings)
    {
        Command = "SELECT Key, Value FROM dbo.BuildInfos WITH (NOLOCK);";
    }

    public override IEnumerable<Row> Execute(IEnumerable<Row> rows)
    {
        foreach (Row row in rows)
        {
            yield return row;
        }
    }
}

Jeson Martajaya · Accepted Answer · 2018-06-23T13:09:57.490

After debugging directly with Rhino.Etl, turns out I did 2 mistakes:

The ConnectionStringSettings should include providerName . RhinoETL threw an exception on this but somehow I am not able to see it

    var csString = new ConnectionStringSettings("myConnection2",
        "Server=yourdatabasehere.database.windows.net;Database=MyDbName;User ID=youruser;Password=yourpassword",
        "System.Data.SqlClient"); //I was missing this.
    Register(new ReadBuildInfosConvention(csString));

The author's blog is complete. Execute method should not be overriden. A SQL Reader is as simple as the following:

public class ReadBuildInfosConvention : ConventionInputCommandOperation
{
    public ReadBuildInfosConvention(ConnectionStringSettings csSettings) : base(csSettings)
    {
        Command = "SELECT [Key], [Value] FROM BuildInfos WITH (NOLOCK);";
    }
}

because the base Execute method already handles the DB Read appropriately

    public override IEnumerable<Row> Execute(IEnumerable<Row> rows)
    {
        using (IDbConnection connection = Use.Connection(ConnectionStringSettings))
        using (IDbTransaction transaction = BeginTransaction(connection))
        {
            using (currentCommand = connection.CreateCommand())
            {
                currentCommand.Transaction = transaction;
                PrepareCommand(currentCommand);
                using (IDataReader reader = currentCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return CreateRowFromReader(reader);
                    }
                }
            }

            if (transaction != null) transaction.Commit();
        }
    }

Here is a working VS solution with logging.

How to read from SQL Server table with RhinoETL framework?

1 Answers1