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.

Read More

Troubleshooting ORA-12505

Troubleshooting ORA-12505( SQL Developer), TNS: listener does not currently know of SID given in connect descriptor

There are a few things that can cause this problem, but before we get started,you need to be sure that you can connect to the database using SQL*Plus. SQL*Plus, it's a command-line tool for connecting to Oracle databases that has been a standard part of Oracle for a long time and it is included with Oracle XE. When connecting to an Oracle database using database connectivity API, we don't connect to the DB directly. Instead, we connect to a TNS listener, which then connects you to the database. The error means that the listener was up and you could connect to it, but it couldn't connect you to the database because it doesn't know that that database is up. There are two reasons for this: The DB has not been started up, The DB has not registered with the listener, e.g. because the database was started before the listener.  When the DB starts, it registers itself with a listener if it is already running. If the listener isn't running, the DB doesn't register itself, and if the listener starts, it doesn't go looking for databases that might register with it. ORA-12505 means that the listener knows about that database, but the listener hasn't received an acknowledgement from the DB that it is up.

  1. Start oracle database.

Start database

   2. Connect to system.

Connect System

3.     

   3. Try out command “LSNRCTL STAT”;

Ora-Listener

  

44.  Execute command

Alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope = both;

Alter System Listener

       5. Execute command

Alter system register;

Alter System Register

6  6.Execute command

Exit;

Exit

7.       

    7.Execute command

lsnrctl stat

lsnrctl stat

Now you should probably be able to connect.

Read More