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; } }