Getting Started Static Factory

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.

This getting started guide is for those using the static RepoFactory. For a similar guide that relies on Dependency Injection see Getting Started (IoC).

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

The first step to using SqlRep with the static factory is install the correct NuGet package. To install the package execute one of the followoing from a command prompt with the startup project folder focused.

    Install-Package SqlRepo.SqlServer.Static

or

    dotnet-install-package SqlRepo.SqlServer.Static

Before we can be start using the SqlRepo components we must prepare and configure the static factory with 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 SqlRepo.SqlServer.ConnectionProviders;
using SqlRepo.SqlServer.Static;

namespace GettingStartedStatic
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var connectionProvider = new AppConfigFirstConnectionProvider();
            RepoFactory.UseConnectionProvider(connectionProvider);

            // ...
        }
    }
}

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

Now anywhere in your system you can use the following to create a repository:

    RepoFactory.Create<ToDo>();

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

using System;
using SqlRepo.SqlServer.Static;

namespace GettingStartedStatic
{
    public class GettingStarted
    {
        public void DoIt()
        {
            var repository = RepoFactory.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.SqlServer.Static;

namespace GettingStartedStatic
{
    public class GettingStarted
    {
        public void DoIt()
        {
            var repository = RepoFactory.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 SqlRepo;
using SqlRepo.SqlServer.Static;

public class GettingStartedStatic
{
    public void DoIt()
    {
         var repository = RepoFactory.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 SqlRepo;
using SqlRepo.SqlServer.Static;

public class GettingStartedStatic
{
    public void DoIt()
    {
         var repository = RepoFactory.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 using the static RepoFactory.