Skip to content

Instantly share code, notes, and snippets.

@kingofnull
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());
}
parentRow.Add(childRow);
}
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;
cmd.Parameters.Add(p);
}
}
try
{
// executes
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var reader = cmd.ExecuteReader();
// loop through all resultsets (considering that it's possible to have more than one)
int currentResultSet = -1;
do
{
currentResultSet++;
//skip lower resultsets
if (resultSet > currentResultSet)
{
continue;
}
// loads the DataTable (schema will be fetch automatically)
var tb = new DataTable();
tb.Load(reader);
resultDataSet.Tables.Add(tb);
//make sure to get only one result set
break;
} while (!reader.IsClosed);
}
catch (Exception ex) {
var parsedQuery = sql;
throw new Exception($"{ex.Message} \n SQL: {parsedQuery}");
}
finally
{
// closes the connection
conn.Close();
}
}
return DataTableToDictionaryList(resultDataSet.Tables[0]);
}
}
}
@kingofnull
Copy link
Author

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>() {
    {"PersonelId",PersonelId},
    {"Date",todayDate},
});

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