How to read multiple columns from database into array list

Reading multiple columns from database

Most of the application development isn't possible without using a Database Management System since our aim is never to build a normal application, our aim is to build a sophisticated application. So database applications must be included within our developing environment. While fetching information or values from our database we might come across retrieving values from multiple columns, usually can be done if we have same query structure for data retrieval i.e. conditions applied on data are similar. Here we'll be using oracle DBMS but you can use any for the same. Hence instead of using multiple queries to retrieve data from multiple columns, we'll use a single query to read multiple columns.

In order to fetch data from database we'll use Oracle data reader and loop the data table until the last record. Before proceeding to source code we must know how reader works?

Reader is basically used to retrieve row values. For explanation purpose let us imagine the data table as a matrix[i x j].

 reader[0] will read the value at [1,1]

reader[1] will read value at [1,2]

.

.

.

.

.

reader[j] will read value at [1,j]

When the full row is read then the reader will jump to the next row and read till the full row is fetched.

Reading Multiple Columns from database c# source code

Now assuming that we all have basic knowledge for database, function of oracle data reader and basics to retrieve values from database in c#, we'll jump to the source code.

                               // Variable Declaration

                List<string> AN = new List<string>();

                List<string> BN = new List<string>();

                List<string> CN = new List<string>();

                List<string> DN = new List<string>();

                List<string> EN = new List<string>();

                List<string> FN = new List<string>();

                List<string> GN = new List<string>();

                List<string> HN = new List<string>();

   using (OracleConnection connection = new OracleConnection(sConnectionString))

                {

              connection.Open();

                        using (OracleCommand command = new OracleCommand("SELECT NVL(AName,0),NVL(BName,0),NVL(CName,0),NVL(DName,0),NVL(EName,0),NVL(FName,0),NVL(GName,0),NVL(HName,0) FROM T_CSP where REP_DATE BETWEEN :d1 AND :d2 order by REP_DATE", connection))

                        {

                            command.Parameters.Add(new OracleParameter(":d1", dt1));

                            command.Parameters.Add(new OracleParameter(":d2", dt2));

                            command.BindByName = true;

                            using (OracleDataReader reader = command.ExecuteReader())

                            {

                                while (reader.Read())

                                {   

                                    AN.Add(reader[0].ToString());

                                    BN.Add(reader[1].ToString());

                                    CN.Add(reader[2].ToString());

                                    DN.Add(reader[3].ToString());

                                    EN.Add(reader[4].ToString());

                                    FN.Add(reader[5].ToString());

                                    GN.Add(reader[6].ToString());

                                    HN.Add(reader[7].ToString());

                                }

                            }

                            connection.Close();

                       }

                  }

Points at a Glance

• sConnectionString is your connection string which can be set dynamically or statically or in your application's configuration file.

Click here to know How to set connection string dynamically

• Any valid SQL Query can be used.
• d1 and d2 are the parameters, more precisely date parameters. If dynamic fiscal date is to be set check out the link below.

Logic to Set dynamic Financial year date

• AN is a list string that will contain all the fields of column AName in table T_CSP between date d1 and d2 order by rep_date. Similarly BN is a list string that will contain all the fields of column BName in table T_CSP between date d1 and d2 order by rep_date. 
• Rest all the list strings will have fields of their respective columns.

This is how we can read multiple columns in different list strings.