.NET SQL Query Results to a DataTable Type

So, all I want to do is execute an SQL statement against oracle and have it return a recordset that I can use in my .NET code, just like what I'm used to seeing in Coldfusion. What's the best way to do that?

I spent hours experimenting with DataAdapter and DataReader (The Oracle ODAC112012 version), and I was able to get useable results using several different techniques, all with their own pros and cons. DataAdaper is huge and provides an amazing amount of extra functionality for Insert/update/delete functionality that we really don't need in this case. DataReader on the other hand seems to be optimized for forward read access of sql select results, but only while actively connected to the database. I really want a combination of the two in this case – the speed/optimization of the DataReader with the Functionality of a DataTable (which to me, is very similar to a Coldfusion recordset), which is returned as part of a DataSet by a DataAdapter.

My favorite solution so far? A DataReader filling a newly initialized DataTable. Another requirement for me is the support of SQL Parameters, which automatically prevents SQL injection attacks and improves DB server performance with query plan caching (you get that for free just by using sql parameters for ALL your variables rather than relying on string parsing! Yay!). Anywho, here is a fairly generic method that allows executing a SQL statement along with its list of OracleParameters, returning only one DataTable variable with the results of the query:

public DataTable executeSqlDataTable(string sql, OracleParameter[] sqlParameters)
{
    // Get the connection string
    ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["OracleConnectionStringCouncil"];
    string constr = connectionStringSettings.ConnectionString;
    DataTable dt = new DataTable();

    // Create an oracle connection
    using (OracleConnection conn = new OracleConnection(constr))
    {
        // Create an oracle command object so we can pass parameters to it
        OracleCommand command = new OracleCommand(sql, conn);

        // Add parameters, if any
        for (int i = 0; i < sqlParameters.Length; i++)
        {
            command.Parameters.Add(sqlParameters[i]);
        }

        // execute the sql statement
        conn.Open();
        OracleDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
        // dump the results to a DataTable type
        dt.Load(dr);
    }
    return dt;
}

And here is an example method that executes a sql statement (with oracle SQL parameters) and returns one column value from the first returned row as a result:

public string getFullName(string ntUsername)
{
    string sql = "SELECT FULL_NAME FROM MyUserTable WHERE username = :ntUsername";
    
    // Set SQL parameters
    OracleParameter[] sqlParameters = { new OracleParameter("ntUsername",OracleDbType.NVarchar2) };
    sqlParameters[0].Value = ntUsername;

    // Execute the SQL, return a DataTable
    DataTable tt = executeSqlDataTable(sql, sqlParameters );

    return tt.Rows[0]["FULL_NAME"].ToString();  
}