DEV Community

Oleksandr Viktor
Oleksandr Viktor

Posted on • Edited on

Params Guide in UkrGuru.Sql

Guide to Using Parameters in UkrGuru.Sql

In this article, we'll walk through creating a sample console application using the UkrGuru.Sql library. This library simplifies database operations in .NET applications. We'll demonstrate how to execute various SQL queries and handle different data types.

Setting Up the Project

First, ensure you have the UkrGuru.Sql library installed. You can add it to your project via NuGet Package Manager.

dotnet add package UkrGuru.Sql
Enter fullscreen mode Exit fullscreen mode

Configuring the Connection String

Set up your database connection string. For this example, we'll use a local database.

DbHelper.ConnectionString = "Server=(localdb)\\mssqllocaldb;Integrated Security=true";
Enter fullscreen mode Exit fullscreen mode

Executing SQL Queries

The UkrGuru.Sql library uses @Data as the default name for a single parameter. Below are examples of executing SQL queries with different data types.

Boolean Result

To execute a query that returns a boolean result:

var boolResult = DbHelper.Exec<bool>("SELECT @Data", true);
Console.WriteLine($"Boolean Result: {boolResult}");
Enter fullscreen mode Exit fullscreen mode

Result: Boolean Result: True

Integer Result

For queries that return an integer:

var intResult = DbHelper.Exec<int>("SELECT @Data", 123);
Console.WriteLine($"Integer Result: {intResult}");
Enter fullscreen mode Exit fullscreen mode

Result: Integer Result: 123

String Result

To handle string results:

var stringResult = DbHelper.Exec<string>("SELECT @Data", "Hello, World!");
Console.WriteLine($"String Result: {stringResult}");
Enter fullscreen mode Exit fullscreen mode

Result: String Result: Hello, World!

Field Result

When you need to work with named parameters:

var fieldResult = DbHelper.Exec<string>("SELECT @Name", new { Name = "John" });
Console.WriteLine($"Field Result: {fieldResult}");
Enter fullscreen mode Exit fullscreen mode

Result: Field Result: John

DateOnly Result

For date-only results:

var dateResult = DbHelper.Exec<DateOnly>("SELECT @Data", DateTime.Today);
Console.WriteLine($"DateOnly Result: {dateResult}");
Enter fullscreen mode Exit fullscreen mode

Result: DateOnly Result: 05/01/2025

TimeOnly Result

To handle time-only results:

var timeResult = DbHelper.Exec<TimeOnly>("SELECT @Data", new TimeSpan(23, 59, 0));
Console.WriteLine($"TimeOnly Result: {timeResult}");
Enter fullscreen mode Exit fullscreen mode

Result: TimeOnly Result: 23:59

Decimal Result

For queries that return a decimal:

var decimalResult = DbHelper.Exec<decimal>("SELECT @Data", 123.45m);
Console.WriteLine($"Decimal Result: {decimalResult}");
Enter fullscreen mode Exit fullscreen mode

Result: Decimal Result: 123.45

Guid Result

To handle GUID results:

var guidResult = DbHelper.Exec<Guid>("SELECT @Data", Guid.NewGuid());
Console.WriteLine($"Guid Result: {guidResult}");
Enter fullscreen mode Exit fullscreen mode

Result: Guid Result: b69df053-cf79-4689-a6b2-93f390dd705d

Enum Result

When working with enums:

var enumResult = DbHelper.Exec<UserType>("SELECT @Data", UserType.Admin);
Console.WriteLine($"Enum Result: {enumResult}");
Enter fullscreen mode Exit fullscreen mode

Result: Enum Result: Admin

Handling JSON and Custom Types

You can also handle JSON and custom types with UkrGuru.Sql.

// JSON Object Result
var jsonObject = DbHelper.Exec<NamedType>("SELECT @Id Id, @Name Name FOR JSON PATH, WITHOUT_ARRAY_WRAPPER", new { Id = 1, Name = "Test" });
Console.WriteLine($"Json Object Result: Id = {jsonObject?.Id}, Name = {jsonObject?.Name}");
Enter fullscreen mode Exit fullscreen mode

Result: Json Object Result: Id = 1, Name = Test

// Named Object Result
var namedObject = DbHelper.Read<NamedType>("SELECT @Id Id, @Name Name", new { Id = 1, Name = "Test" }).FirstOrDefault();
Console.WriteLine($"Named Object Result: Id = {namedObject?.Id}, Name = {namedObject?.Name}");
Enter fullscreen mode Exit fullscreen mode

Result: Named Object Result: Id = 1, Name = Test

Using SqlParameter and SqlParameter[]

In addition to the default parameter handling, you can also use SqlParameter and SqlParameter[] for more complex scenarios.

Using SqlParameter

You can create individual SqlParameter objects to pass parameters to your SQL queries.

var sqlBooleanParam = new SqlParameter("@Data", true);
var booleanResult = DbHelper.Exec<bool>("SELECT @Data", sqlBooleanParam);
Console.WriteLine($"Sql Boolean Result: {booleanResult}");
Enter fullscreen mode Exit fullscreen mode

Result: Sql Boolean Result: True

Using SqlParameter[]

For queries that require multiple parameters, you can use an array of SqlParameter objects.

var sqlNamedParameters = new SqlParameter[]
{
    new SqlParameter("@Id", 1),
    new SqlParameter("@Name", "John")
};
var sqlNamedResult = DbHelper.Read<NamedType>("SELECT @Id Id, @Name Name", sqlNamedParameters).FirstOrDefault(); 
Console.WriteLine($"Sql Named Result: Id = {sqlNamedResult?.Id}, Name = {sqlNamedResult?.Name}");
Enter fullscreen mode Exit fullscreen mode

Result: Sql Named Result: Id = 1, Name = John

Enum and Custom Class Definitions

Define your enums and custom classes as needed.

enum UserType
{
    Guest,
    User,
    Manager,
    Admin,
    SysAdmin
}

class NamedType
{
    public int Id { get; set; }
    public string? Name { get; set; }
}
Enter fullscreen mode Exit fullscreen mode

Conclusion

This sample console application demonstrates how to use the UkrGuru.Sql library to execute SQL queries and handle various data types in a .NET application. With this library, you can simplify your database operations and focus on building your application's core features.

Feel free to expand this example to suit your specific needs. Happy coding! 🚀

For more details, you can check the source code here.
https://github.com/UkrGuru/Sql/blob/main/demos/InOutSqlDemo/Program.cs

UkrGuru.Sql package here
https://www.nuget.org/packages/UkrGuru.Sql/

Reinvent your career. Join DEV.

It takes one minute and is worth it for your career.

Get started

Top comments (1)

Collapse
 
ukrguru profile image
Oleksandr Viktor

NuGet Gallery | UkrGuru.Sql 1.0.0 go live now

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay