Sql2LINQ

NuGet package at https://www.nuget.org/packages/Brad.Wickett_Sql2LINQ/.

Sql2LINQ is a set of tools for running standard SQL queries using the SqlDataReader (or the OleDbDataReader in the .NET Framework version) and returning the results as a collection of standard C# objects. The .NET Framework version can connect to SQL database or OLEDB connections. The .NET Standard version can only connect to SQL database. Just create a C# object with property names that match your query fields and property types that match the column type in your database and you can use standard SQL queries and this utility will load the results into your object.

For example, assuming you have a Users table in your database with fields such as UserId, UserName, FirstName, LastName, EmailAddress, etc., you could create a C# object that matches your table design as follows:

public class User {
     public int UserId { get; set; }
     public string UserName { get; set; }
     public string FirstName { get; set; }
     public string LastName { get; set; }
     public string EmailAddress { get; set; }
     public DateTime? LastLogin { get; set; }
}

You could then use this library to connect to your database and run a query to get your users and return a list of User objects as follows:

string query = "SELECT UserId, UserName, FirstName, LastName, EmailAddress, LastLogin FROM Users";

// Sample SQL Server Connection String
string sqlConnectionString = @"Data Source=(local); Initial Catalog=MyDatabase; Persist Security Info=True;" +
     "User ID=sa; Password=saPassword; MultipleActiveResultSets=true;";

// Sample Microsoft Access OLEDB Connection String
string accessConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\PathToAccess.mdb;";

using(Sql2LINQ.Sql2LINQ data = new Sql2LINQ.Sql2LINQ(sqlConnectionString)){
    List<User> users = data.RunQuery<User>(query);
}

You can also pass a collection of SqlParameter objects and use parameters in your queries (or OleDbParameters for OLEDB connections). There is also a function to save your C# object to the table, and optionally create a new record if the record does not yet exist. Use the following function to do so:

public Tuple<BooleanResponse, T> SaveRecord(T Record, string TableName, string IdColumn, bool CreateIfMissing = false)

This function takes your C# object T, the name of the table, the name of the ID column (eg: UserId), and an optional boolean to CreateIfMissing which will add a new record instead of updating an existing record if the UserId does not currently exist. The function returns a Tuple with both a BooleanResponse and an updated object T. If the column in the database was an Identity column then the returned object will have the new auto-generated Id.