Copy this code into your project
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace DBHelper
{
public static class MSSQL
{
public static DataSet toDataSet(in string connectionString, in string query, in SqlParameter parameter) => toDataSet(connectionString, query, new List<SqlParameter> { parameter });
}
}
Change the namespace, or whatever to fit your code
using Microsoft.Data.SqlClient;
using System.Data;
using System.Text;
namespace YOURNAMESPACE
{
public static class Utilities
{
public static void AddParameter(this List<SqlParameter> d, in string parameterName, in object value) { d.Add(new(parameterName, value)); }
public static string Map(this string d, in SqlDataReader dr, in string columnName) { return dr[columnName].ToString(); }
public static int Map(this int d, in SqlDataReader dr, in string columnName) { return Convert.ToInt32(dr[columnName]); }
public static int? Map(this int? d, in SqlDataReader dr, in string columnName) { return Convert.ToInt32(dr[columnName]); }
public static uint Map(this uint d, in SqlDataReader dr, in string columnName) { return Convert.ToUInt32(dr[columnName]); }
public static uint? Map(this uint? d, in SqlDataReader dr, in string columnName) { return Convert.ToUInt32(dr[columnName]); }
public static long Map(this long d, in SqlDataReader dr, in string columnName) { return Convert.ToInt64(dr[columnName]); }
public static long? Map(this long? d, in SqlDataReader dr, in string columnName) { return Convert.ToInt64(dr[columnName]); }
public static ulong Map(this ulong d, in SqlDataReader dr, in string columnName) { return Convert.ToUInt64(dr[columnName]); }
public static ulong? Map(this ulong? d, in SqlDataReader dr, in string columnName) { return Convert.ToUInt64(dr[columnName]); }
public static float Map(this float d, in SqlDataReader dr, in string columnName) { return Convert.ToSingle(dr[columnName]); }
public static float? Map(this float? d, in SqlDataReader dr, in string columnName) { return Convert.ToSingle(dr[columnName]); }
public static double Map(this double d, in SqlDataReader dr, in string columnName) { return Convert.ToDouble(dr[columnName]); }
public static double? Map(this double? d, in SqlDataReader dr, in string columnName) { return Convert.ToDouble(dr[columnName]); }
public static decimal Map(this decimal d, in SqlDataReader dr, in string columnName) { return Convert.ToDecimal(dr[columnName]); }
public static decimal? Map(this decimal? d, in SqlDataReader dr, in string columnName) { return Convert.ToDecimal(dr[columnName]); }
public static DateTime Map(this DateTime d, in SqlDataReader dr, in string columnName) { return Convert.ToDateTime(dr[columnName]); }
public static DateTime? Map(this DateTime? d, in SqlDataReader dr, in string columnName) { return Convert.ToDateTime(dr[columnName]); }
public static bool Map(this bool d, in SqlDataReader dr, in string columnName)
{
var val = dr[columnName].ToString();
return val == "True" || val == "1";
}
public static bool? Map(this bool? d, in SqlDataReader dr, in string columnName)
{
var val = dr[columnName].ToString();
return val == "True" || val == "1";
}
public static async Task<DataTable> SQLQuery(this string query, string connectionString, IEnumerable<SqlParameter> parameters = null)
{
DataTable dt = new();
using (SqlConnection sc = new(connectionString))
{
using SqlCommand cmd = new(query, sc);
if (parameters != null) foreach (var p in parameters) cmd.Parameters.Add(p);
await sc.OpenAsync();
using (SqlDataAdapter da = new(cmd)) da.Fill(dt);
await sc.CloseAsync();
}
return dt;
}
public static async Task<DataTable> SQLQuery(this string query, string connectionString, SqlParameter parameters)
{
DataTable dt = new();
using (SqlConnection sc = new(connectionString))
{
using SqlCommand cmd = new(query, sc);
cmd.Parameters.Add(parameters);
await sc.OpenAsync();
using (SqlDataAdapter da = new(cmd)) da.Fill(dt);
await sc.CloseAsync();
}
return dt;
}
public static async Task<DataSet> SQLQueryDataSet(this string query, string connectionString, IEnumerable<SqlParameter> parameters = null)
{
DataSet ds = new();
using (SqlConnection sc = new(connectionString))
{
using SqlCommand cmd = new(query, sc);
if (parameters != null) foreach (var p in parameters) cmd.Parameters.Add(p);
await sc.OpenAsync();
using (SqlDataAdapter da = new(cmd)) da.Fill(ds);
await sc.CloseAsync();
}
return ds;
}
public static async Task<List<T>> SQLQuery<T>(this string query, string connectionString, Func<SqlDataReader, T> mapper, IEnumerable<SqlParameter> parameters = null)
{
List<T> oL = new();
using (SqlConnection sc = new(connectionString))
{
using SqlCommand cmd = new(query, sc);
if (parameters != null) foreach (var p in parameters) cmd.Parameters.Add(p);
await sc.OpenAsync();
using var er = cmd.ExecuteReader();
while (er.Read()) oL.Add(mapper(er));
await sc.CloseAsync();
}
return oL;
}
public static async Task<List<T>> SQLQuery<T>(this string query, string connectionString, Func<SqlDataReader, T> mapper, SqlParameter parameters)
{
List<T> oL = new();
using (SqlConnection sc = new(connectionString))
{
using var cmd = new SqlCommand(query, sc);
cmd.Parameters.Add(parameters);
await sc.OpenAsync();
using var er = cmd.ExecuteReader();
while (er.Read()) oL.Add(mapper(er));
await sc.CloseAsync();
}
return oL;
}
public static async Task SQLQuery<T>(this string query, string connectionString, IEnumerable<SqlParameter> parameters = null)
{
using SqlConnection sc = new(connectionString);
using SqlCommand cmd = new(query, sc);
if (parameters != null) foreach (var p in parameters) cmd.Parameters.Add(p);
await sc.OpenAsync();
await sc.CloseAsync();
}
public static string ToBase64(this string s) => Convert.ToBase64String(Encoding.UTF8.GetBytes(s));
public static string FromBase64(this string b) => Encoding.Default.GetString(Convert.FromBase64String(b));
}
}