Brad.Wickett_Sql2LINQ 2.0.2

dotnet add package Brad.Wickett_Sql2LINQ --version 2.0.2                
NuGet\Install-Package Brad.Wickett_Sql2LINQ -Version 2.0.2                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Brad.Wickett_Sql2LINQ" Version="2.0.2" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Brad.Wickett_Sql2LINQ --version 2.0.2                
#r "nuget: Brad.Wickett_Sql2LINQ, 2.0.2"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Brad.Wickett_Sql2LINQ as a Cake Addin
#addin nuget:?package=Brad.Wickett_Sql2LINQ&version=2.0.2

// Install Brad.Wickett_Sql2LINQ as a Cake Tool
#tool nuget:?package=Brad.Wickett_Sql2LINQ&version=2.0.2                

Sql2LINQ

Sql2LINQ is a set of tools for running standard SQL queries using the SqlDataReader and returning the results as a collection of standard C# objects. 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.

Requirements

As of version 2.0.0 this library is written in C# for .NET 7 or higher and only supports connecting to SQL Server. If you need an older version of this utility that can work with OLEDb connections, download version 1.0.16.

Getting Started

Install the nuget package in your project.

Code Example

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

public class User {
	public Guid UserId { get; set; }
	public string? UserName { get; set; }
	public string? FirstName { get; set; }
	public string? LastName { get; set; }
	public string? Email { 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, Email, 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;";

using (Sql2LINQ.Sql2LINQ data = new Sql2LINQ.Sql2LINQ(sqlConnectionString)) {
	var users = data.RunQuery<User>(query);
		
	if(users != null && users.Any()){
		foreach(var user in users){
			// Do something here with your user records.
		}
	}
}

In addition to objects, if your result is only going to return a single column you can use system types to return a collection of those values. The types supported are: boolean, DateTime, decimal, double, int, Guid, and string. Example:

var emails = data.RunQuery<string>("SELECT DISTINCT(Email) FROM Users");
if(emails != null && emails.Any()){
	foreach(var email in emails){
		// Do something with all these email addresses.
	}
}

You can also pass a collection of SqlParameter objects and use parameters in your queries.

var results = data.RunQuery<User>("SELECT * FROM Users WHERE Email = @email", new SqlParameter[] {
	new SqlParameter("email", "john.doe@domain"),
});

if(results != null && results.Any()){
	// Do something with your results.
}

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.

Contact

For support and information contact Brad Wickett at brad@wickett.net.

Product Compatible and additional computed target framework versions.
.NET net7.0 is compatible.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (1)

Showing the top 1 NuGet packages that depend on Brad.Wickett_Sql2LINQ:

Package Downloads
WSU_EIT_WorkdayClient

Tools for interacting with our Workday data.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
2.0.2 669 6/21/2023
1.0.16 1,797 4/19/2019