Skip to content

Instantly share code, notes, and snippets.

Last active October 28, 2021 08:02
Show Gist options
  • Save kingofnull/9963bfebedf6937522a95b3d2c515246 to your computer and use it in GitHub Desktop.
Save kingofnull/9963bfebedf6937522a95b3d2c515246 to your computer and use it in GitHub Desktop.
A dynamic sql server query executer and result reader without using DTO for SQL Server
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
namespace Toolkit.QueryExecuter
public class SqlSrvQueryExecuter
public string connectionStr;
public string connectionName;
protected static List<Dictionary<string, string>> DataTableToDictionaryList(DataTable table)
List<Dictionary<string, string>> parentRow = new List<Dictionary<string, string>>();
Dictionary<string, string> childRow;
foreach (DataRow row in table.Rows)
childRow = new Dictionary<string, string>();
foreach (DataColumn col in table.Columns)
childRow.Add(col.ColumnName, row[col] == null ? null : row[col].ToString());
return (parentRow);
public static List<Dictionary<string, string>> RunDynamicQuery(string sql, Dictionary<string, dynamic> parameters = null, int resultSet = 0, CommandType commandType = CommandType.Text, string connectionName = "default")
// creates resulting dataset
var resultDataSet = new DataSet();
// creates a data access context (DbContext descendant)
//using (var context = new DataDbContext())
// creates a Command
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionName].ConnectionString);//context.Database.Connection;
var cmd = conn.CreateCommand();
cmd.CommandType = commandType;
cmd.CommandText = sql;
if (parameters != null)
// adds all parameters
foreach (var pr in parameters)
var p = cmd.CreateParameter();
p.ParameterName = pr.Key;
p.Value = pr.Value;
// executes
if (conn.State != ConnectionState.Open)
var reader = cmd.ExecuteReader();
// loop through all resultsets (considering that it's possible to have more than one)
int currentResultSet = -1;
//skip lower resultsets
if (resultSet > currentResultSet)
// loads the DataTable (schema will be fetch automatically)
var tb = new DataTable();
//make sure to get only one result set
} while (!reader.IsClosed);
catch (Exception ex) {
var parsedQuery = sql;
throw new Exception($"{ex.Message} \n SQL: {parsedQuery}");
// closes the connection
return DataTableToDictionaryList(resultDataSet.Tables[0]);
Copy link

kingofnull commented Apr 4, 2021

Call example:

var rs=QueryExecuter.RunDynamicQuery("SELECT date FROM checkup_appointment WHERE personnel_id=@PersonelId AND date>=@Date"
, new Dictionary<string, dynamic>() {

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment