public DataTable InvokeSqlQuery(string connectionName, string query, InvokeSqlQueryOptions options = null)
{
var conn = ConnectionFactory.CreateFromString(connectionName);
try
{
var result = InvokeSqlQuery(GetDbConnection(connectionName), query, options);
return(result);
}
finally
{
conn.Close();
}
}
public DataTable InvokeSqlQuery(DbConnection connection, string query, InvokeSqlQueryOptions options = null)
{
var reader = InvokeSqlQueryAsDataReader(connection, query, options);
if (reader == null)
{
return(null);
}
var table = new DataTable("Query");
table.Load(reader);
return(table);
}
#pragma warning disable CA1822 // Mark members as static
public DbDataReader InvokeSqlQueryAsDataReader(DbConnection connection, string query, InvokeSqlQueryOptions options = null)
#pragma warning restore CA1822 // Mark members as static
{
options ??= new InvokeSqlQueryOptions();
if (string.IsNullOrWhiteSpace(query))
{
throw new Exception("Cannot load script text.");
}
if ((connection?.State ?? ConnectionState.Closed) != ConnectionState.Open)
{
connection.Open();
}
var cmd = connection.CreateCommand();
cmd.CommandText = query;
if (options.CommandTimeout.HasValue)
{
cmd.CommandTimeout = options.CommandTimeout.Value;
}
DbDataReader reader = null;
try
{
if (options.Parameters != null)
{
foreach (DictionaryEntry keyPair in options.Parameters)
{
var parameterType = keyPair.Value?.GetType() ?? typeof(string);
(var dbType, var len) = ScriptRunParameter.GetColumnDbType(parameterType, int.MaxValue / 2);
var param = cmd.CreateParameter();
param.ParameterName = Convert.ToString(keyPair.Key);
param.DbType = dbType;
param.Value = keyPair.Value;
if (len > 0)
{
param.Size = len;
}
cmd.Parameters.Add(param);
}
}
var readerParameters = new DataReaderWrapper.DataReaderWrapperParameters()
{
Columns = options.SelectColumns,
SkipColumns = options.SkipColumns,
CloseAction = () =>
{
cmd.Dispose();
}
};
reader = new DataReaderWrapper(cmd.ExecuteReader(), readerParameters);
return(reader);
}
catch (Exception)
{
reader?.Dispose();
cmd?.Dispose();
throw;
}
}
