Getting Started IoC

We strongly recommend that SqlRepo components are provided/obtained through Dependency Injection and we provide a number of packages to help you get setup with this quickly and easily see IoC or Package Installation for more information. However we do recognise that some may not be able or want to use Dependency Injection or just want to try SqlRepo out without all the prepartion. We also provide a static/singleton wrapper for the RepositoryFactory, which is effectively the entry point for SqlRepo.

This getting started guide is for those using Dependency Injection. For a similar guide that does not rely on Dependency Injection see Getting Started (Static Factory).

The code shown in this guide can be found in the source repo at $root$\Demos\GettingStarted\GettingStartedIoC

The first step to using SqlRepo with Dependency Injection is to install the correct NuGet package. We prefer Autofac for DI so will use that in this guide. To install the Autofac package execute one of the following from a command prompt with the startup project folder focused:

    Install-Package SqlRepo.SqlServer.Autofac

or

    dotnet-install-package SqlRepo.SqlServer.Autofac

Now that we have the packages installed we need to register the core SqlRepo components using the module provided. We will use the example of Program.cs for a console app, you should be able to transpose that to any other project type you choose. If not feel free to post an Issue with a question.

using System;
using Autofac;
using SqlRepo.SqlServer.Autofac;

namespace GettingStartedIoC
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var containerBuilder = new ContainerBuilder();

            containerBuilder.RegisterModule<SqlRepoSqlServerAutofacModule>();

            // ... other registrations

            var container = containerBuilder.Build();
        }
    }
}

Here we have used Autofac to create and populate our IoC container and registered the core SqlRepo components. However before we can start using SqlRepo we need to prepare and register an implementation of IConnectionProvider as our default, which will be used by SqlRepo components to obtain a connection to our database. SqlRepo provides several implementations out of the box that should cover most use cases, but you are free to create your own. For information on the connection providers included with SqlRepo take a look at Connection Providers.

using System;
using Autofac;
using SqlRepo.Abstractions;
using SqlRepo.SqlServer.Autofac;
using SqlRepo.SqlServer.ConnectionProviders;

namespace GettingStartedIoC
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var containerBuilder = new ContainerBuilder();

            containerBuilder.RegisterModule<SqlRepoSqlServerAutofacModule>();

            var connectionProvider = new AppConfigFirstConnectionProvider();
            containerBuilder.RegisterInstance(connectionProvider)
                            .As<IConnectionProvider>();

            // ... other registrations

            var container = containerBuilder.Build();
        }
    }
}

Here we have used the simplest of our built in connection providers that takes the first connection string from an [App|Web].config file.

Finally we need to create and register our getting started component and use it.

using System;
using Autofac;
using SqlRepo.Abstractions;
using SqlRepo.SqlServer.Autofac;
using SqlRepo.SqlServer.ConnectionProviders;

namespace GettingStartedIoC
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var containerBuilder = new ContainerBuilder();

            containerBuilder.RegisterModule<SqlRepoSqlServerAutofacModule>();

            var connectionProvider = new AppConfigFirstConnectionProvider();
            containerBuilder.RegisterInstance(connectionProvider)
                            .As<IConnectionProvider>();

            containerBuilder.RegisterType<GettingStarted>()
                            .As<IGettingStarted>();

            // ... other registrations

            var container = containerBuilder.Build();

            var gettingStarted = container.Resolve<IGettingStarted>();
            gettingStarted.DoIt();
        }
    }
}

The starting point for working with SqlRepo is a Repository Factory (IRepositoryFactory), so we declare a dependency on it something like this.

using System;
using SqlRepo.Abstractions;

namespace GettingStartedIoC
{
    public class GettingStarted: IGettingStarted
    {
        private readonly IRepositoryFactory repositoryFactory;

        public GettingStarted(IRepositoryFactory repositoryFactory)
        {
            this.repositoryFactory = repositoryFactory;
        }

        // ...
    }
}

Now you have the factory you can use it to create a Repository for the entity of interest

using System;
using SqlRepo.Abstractions;

namespace GettingStartedIoC
{
    public class GettingStarted: IGettingStarted
    {
        private readonly IRepositoryFactory repositoryFactory;

        public GettingStarted(IRepositoryFactory repositoryFactory)
        {
            this.repositoryFactory = repositoryFactory;
        }

        public void DoIt()
        {
            var repository = this.repositoryFactory.Create<ToDo>();
            // ...

        }
    }
}

So here we have created a repository that allows us to query the ToDo table in our database.

Now we want to get a list of ToDo items.

using System;
using SqlRepo.Abstractions;

namespace GettingStartedIoC
{
    public class GettingStarted: IGettingStarted
    {
        private readonly IRepositoryFactory repositoryFactory;

        public GettingStarted(IRepositoryFactory repositoryFactory)
        {
            this.repositoryFactory = repositoryFactory;
        }

        public void DoIt()
        {
            var repository = this.repositoryFactory.Create<ToDo>();
            var results = repository.Query()
                                    .Go();

            // ...

        }
    }
}

Here we have used the 'Query' method of the repository to create a SelectStatement, which allows you to build a query using fluent methods. Like the GO batch separator in T-SQL the Go (or GoAsync) method executes the statement and maps the results to entities. In this case we have simply created the statement and executed it. This generates and executes the following SQL stattement.

SELECT * FROM [dbo].[ToDo];

We all know it is not good practice to use 'SELECT *' except for testing or troubleshooting purposes (we do don't we?) so let's limit our query to specific columns.

using System;
using SqlRepo.Abstractions;

namespace GettingStartedIoC
{
    public class GettingStarted: IGettingStarted
    {
        private readonly IRepositoryFactory repositoryFactory;

        public GettingStarted(IRepositoryFactory repositoryFactory)
        {
            this.repositoryFactory = repositoryFactory;
        }

        public void DoIt()
        {
            var repository = this.repositoryFactory.Create<ToDo>();
            var results = repository.Query()
                                    .Select(e => e.Id, e => e.Task, e => e.CreatedDate)
                                    .Go();

            // ...

        }
    }
}

Here we have specified three members of the ToDo entity that we are interested in using the Select(...) method. The Select method can be called as above with a list of member expressions or individually for each member, the result is the same. This generates and executes the following SQL stattement.

SELECT [dbo].[ToDo].[Id], [dbo].[ToDo].[Task], [dbo].[ToDo].[CreatedDate]
FROM [dbo].[ToDo];

Note that we fully qualify columns (in fact we fully qualify everything), there are ways to override this by specifying aliases, but we will leave that for the more detailed documentation.

Chances are we are only interested in the ToDo items that are not completed so lets apply a filter to limit the results.

using System;
using SqlRepo.Abstractions;

namespace GettingStartedIoC
{
    public class GettingStarted: IGettingStarted
    {
        private readonly IRepositoryFactory repositoryFactory;

        public GettingStarted(IRepositoryFactory repositoryFactory)
        {
            this.repositoryFactory = repositoryFactory;
        }

        public void DoIt()
        {
            var repository = this.repositoryFactory.Create<ToDo>();
            var results = repository.Query()
                                    .Select(e => e.Id, e => e.Task, e => e.CreatedDate)
                                    .Where(e => e.IsCompleted == false)
                                    .Go();

            // ...

        }
    }
}

We have applied the filter using the Where method. . This generates and executes the following SQL statement.

SELECT [dbo].[ToDo].[Id], [dbo].[ToDo].[Task], [dbo].[ToDo].[CreatedDate]
FROM [dbo].[ToDo]
WHERE [dbo].[ToDo].[IsCompleted] = 0;

So there you have it a quick introduction to the basics of querying data with SqlRepo. This simple example assumes your database and entity model use the same naming convention. We developed SqlRepo as we were working on a green field project so for us this was the case. However we realised it may not be so there are ways to specify table and columns names as you build your SQL statements and we will cover them in the more detailed documentation.

For examples of using Ninject or Microsoft Dependency Injection (AKA ServiceCollection) take a look at the simple demo projects for these packages in the project source