Brad.Wickett_Sql2LINQ 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.