SQLHelper

SQLHelper is a simple class to help with running queries against a database.

Basic Usage

In order to use the system, you do need register it with your ServiceCollection:

serviceCollection.AddCanisterModules();

This is required prior to using the SQLHelper class for the first time. Once Canister is set up, you can use the SQLHelper class:

var Configuration = new ConfigurationBuilder() .AddInMemoryCollection() .Build(); var Instance = new SQLHelper(Configuration, SqlClientFactory.Instance, "ConnectionString");

Or simply ask for an instance using dependency injection:

public MyClass(SQLHelper helper) { ... }

The SQLHelper class takes in a IConfiguration class, a DbProviderFactory class, and the connection string. The connection string can just be the name of a connection string in your configuration object. Once an instance is set up, you can create a batch, add queries, and then execute them.

var Results = Instance.CreateBatch() .AddQuery(CommandType.Text,"SELECT * FROM [TestDatabase].[dbo].[TestTable]") .AddQuery(CommandType.Text,"SELECT * FROM [TestDatabase].[dbo].[TestTable2]") .AddQuery(CommandType.Text,"SELECT * FROM [TestDatabase].[dbo].[TestTable3]") .Execute();

The Results object then holds the results for all 3 queries and is returned as IList<IList>. So in order to get the results from the queries:

var FirstQueryResults = Results[0]; var SecondQueryResults = Results[1]; var ThirdQueryResults = Results[2];

It is also possible to convert the results from the dynamic type to a class type that you specify:

var TestTableClasses = FirstQueryResults.Select(x => (TestTableClass)x).ToList();

The type will be converted automatically for you with no special type conversion required. SQLHelper also has an ExecuteScalar function: