Skip to content

Instantly share code, notes, and snippets.

@kingofnull
Last active October 28, 2021 08:01
Show Gist options
  • Save kingofnull/da17e4679a63d359ab9d3a3f01378701 to your computer and use it in GitHub Desktop.
Save kingofnull/da17e4679a63d359ab9d3a3f01378701 to your computer and use it in GitHub Desktop.
A dynamic sql server query executer and result reader without using DTO for Oracle
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
namespace Toolkit.QueryExecuter
{
public class OracleQueryExecuter : BaseQueryExcuter
{
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 OracleQueryExecuter(string connectionName = "", string connectionStr = "")
{
this.connectionName = connectionName;
this.connectionStr = connectionStr;
}
public static string GenerateParsedQuery(string sql, Dictionary<string, dynamic> parameters)
{
if (parameters != null)
{
foreach (var p in parameters)
{
sql = sql.Replace($":{p.Key}", $"`{p.Value}`");
}
}
return sql;
}
public static List<Dictionary<string, string>> RunDynamicQuery(string sql, Dictionary<string, dynamic> parameters = null, int resultSet = 0, CommandType commandType = CommandType.Text, string connectionName = "ORC3")
{
// 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 OracleConnection(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();
//cmd.BindByName = true;
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 = OracleQueryExecuter.GenerateParsedQuery(sql, parameters);
throw new Exception($"{ex.Message} \n SQL: {parsedQuery}");
}
finally
{
// closes the connection
conn.Close();
}
}
return DataTableToDictionaryList(resultDataSet.Tables[0]);
}
public List<Dictionary<string, string>> Query(string sql, Dictionary<string, dynamic> parameters = null, int resultSet = 0, CommandType commandType = CommandType.Text)
{
return OracleQueryExecuter.RunDynamicQuery(sql,parameters,resultSet,commandType,connectionName:this.connectionName);
}
public string Insert(string tableName, Dictionary<string, dynamic> fieldsValue)
{
string sql = $@"INSERT INTO {tableName} ({string.Join(",", fieldsValue.Keys)}) VALUES({string.Join(",", fieldsValue.Keys.Select(f => ':' + f))})";
this.Query(sql, fieldsValue);
return sql;
}
public Int32 GetSeqNewValue(string sequenceName)
{
return Convert.ToInt32(this.Query($@"select {sequenceName}.nextval as SEQ from dual").First()["SEQ"]);
}
public string DateStrToJalaliStr(string dateStr)
{
DateTime date = Convert.ToDateTime(dateStr);
string persianDateString = date.ToString("yyyy/MM/dd", new System.Globalization.CultureInfo("fa-IR"));
return persianDateString;
}
}
}
@kingofnull
Copy link
Author

kingofnull commented Apr 4, 2021

Call example:

OracleQueryExecuter.RunDynamicQuery(@"SELECT 
                           INCNAME,
                           INCOME ,
                           START_DATE ,
                           ENDDATE ,
                           DURATION
                       FROM  Table Where ID=:id",
                new Dictionary<string, dynamic> { { "id", id } }
);

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