4

My question, which is similar to this one, is how can I use OracleDataReader to retrieve all the fields for a given record? Currently, I've been using this method, which returns only one column value at a time:

public string Select_File(string filename, string subdirectory, string envID)
{
    Data_Access da = new Data_Access();
    OracleConnection conn = da.openDB();

    OracleCommand cmd = new OracleCommand();
    cmd.Connection = conn;
    cmd.CommandText = "SELECT * FROM EIP_Deployment_Files" 
        + " WHERE Filename ='" + filename + "'"
        + " AND Subdirectory = '" + subdirectory + "'"
        + " AND Environment_ID = '" + envID + "'";
    cmd.CommandType = CommandType.Text;

    string x;
    OracleDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows) // file exists in DB
    {
        dr.Read();
        x = dr.GetString(2).ToString(); // return baseline filename (index 2)
    }
    else
    {
        x = "New File";
    }

    cmd.Dispose();
    da.CloseDB(conn);
    return x;
}

I'm sure that this method is far from perfect and ppl will be quick to point that out (I was basically given it by my supervisor since I didn't have any prior experience in ASP.NET) but all I really care about is that it works. My question is: how can it be modified to return all the fields for a given record?

The fields will be of either VARCHAR2, CHAR, or DATE datatypes, (if that makes a difference) and some of these values may be null. I'm thinking I could convert them to strings and return them as a list?

Community
  • 1
  • 1
user1985189
  • 669
  • 5
  • 16
  • 25

3 Answers3

11

if u want something like this:

List<User> lstUser = new List<User>();
            string sqlQuery = "Select * from User_T where User_Name='" + oUser.UserName + "' And Password='" +oUser.Password + "' AND IsActive='"+1+"' AND IsDelete='"+0+"'";
            string connectionString = "Data Source=ORCL;User Id=ACCOUNTS;Password=ACCOUNTS";
            using (DBManager dbManager = new DBManager(connectionString))
            {
                try
                {

                    dbManager.Open();
                    OracleDataReader dataReader = dbManager.ExecuteDataReader(sqlQuery);
                    while (dataReader.Read())
                    {
                        oUser = new User();
                        oUser.Id = Convert.ToInt32(dataReader["ID"]);
                        oUser.CompanyId = Convert.ToInt32(dataReader["Company_ID"]);
                        oUser.BranchId = Convert.ToInt32(dataReader["Branch_ID"]);
                        oUser.UserName = Convert.ToString(dataReader["User_Name"]);
                        lstUser.Add(oUser);
                    }
                    dataReader.Close();
                    dataReader.Dispose();

                }
                catch
                (Exception)
                {


                }
                finally
                {
                    dbManager.Close();
                    dbManager.Dispose();
                }
Asif Mahamud
  • 583
  • 2
  • 10
  • hmm maybe! I'll have to look into it tmw but thanks for your answer – user1985189 Apr 25 '13 at 20:29
  • 1
    This is reading each column *by name* which isn't quite the same thing as reading all the columns no matter what their names are. – Jasmine Apr 25 '13 at 22:03
  • 2
    Note: `sqlQuery` is vulnerable to injection attacks – Matt Borja Jun 16 '16 at 23:58
  • As @rdev5 pointed out, this code sample has serious security issues, also the code is not checking for DBNull which will cause exceptions when Convert.To... methods are invoked. dbManager .Dispose method need not be invoked in the finally block as it is already in a using block. – Vamshi Krishna Jan 03 '17 at 23:08
6

To read all the data from the columns of the current row in a DataReader, you can simply use GetValues(), and extract the values from the array - they will be Objects, of database types.

Object[] values;
int numColumns = dr.GetValues(values); //after "reading" a row
for (int i = 0; i < numColumns; i++) {
    //read values[i]
}

MSDN - "For most applications, the GetValues method provides an efficient means for retrieving all columns, rather than retrieving each column individually."

Jasmine
  • 4,003
  • 2
  • 29
  • 39
  • sorry I'm confused. Is GetValues() retrieving the actual value for each field of the record? – user1985189 Apr 25 '13 at 19:15
  • YES. It fills the object array you pass in, while returning the number of columns as the return value. Here's a link to the docs for it: http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledatareader.getvalues.aspx – Jasmine Apr 25 '13 at 19:17
  • Well I'm not sure how you wanted to read the values, that's why the pseudo-code. You can read the values[i] (which will be an Object) into whatever you want, a StringBuilder, an array, a struct, whatever. – Jasmine Apr 25 '13 at 19:58
4

Sorry for posting an answer to a very old question. As none of the answers are correct (either they have security issues or not checking for DBNull), I have decided to post my own.

public async Task<StringBuilder> FetchFileDetailsAsync(string filename, string subdirectory, string envId)
    {
        var sb = new StringBuilder();
        //TODO: Check the parameters

        const string connectionString = "user id=userid;password=secret;data source=" +
                                        "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.8)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))";

        const string selectQuery = "SELECT * FROM EIP_Deployment_Files"
                                + " WHERE Filename = :filename"
                                + " AND Subdirectory = :subdirectory"
                                + " AND Environment_ID = :envID"
                                + " AND rownum<=1";

        using (var connection = new OracleConnection(connectionString))
        using (var cmd = new OracleCommand(selectQuery, connection) {BindByName = true, FetchSize = 1 /*As we are expecting only one record*/})
        {

            cmd.Parameters.Add(":filename", OracleDbType.Varchar2).Value = filename;
            cmd.Parameters.Add(":subdirectory", OracleDbType.Varchar2).Value = subdirectory;
            cmd.Parameters.Add(":envID", OracleDbType.Varchar2).Value = envId;

            //TODO: Add Exception Handling
            await connection.OpenAsync();
            var dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);

            var rowValues = new object[dataReader.FieldCount];
            if (dataReader.Read())
            {
                dataReader.GetValues(rowValues);
                for (var keyValueCounter = 0; keyValueCounter < rowValues.Length; keyValueCounter++)
                {
                    sb.AppendFormat("{0}:{1}", dataReader.GetName(keyValueCounter), 
                        rowValues[keyValueCounter] is DBNull ? string.Empty : rowValues[keyValueCounter])
                      .AppendLine();
                }

            }
            else
            {
                //No records found, do something here
            }
            dataReader.Close();
            dataReader.Dispose();
        }
        return sb;
    }
Vamshi Krishna
  • 119
  • 4
  • 7