How to retrieve all fields for a given record using OracleDataReader?
asp.net, c#, datareader
Solution
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();
}
Problem
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?