ASP.net how to execute query to Oracle using Button click

July 22, 2013

I would like to share a simple code which currently I am doing. This is a very basic application you may design for your website and require a database connection to Oracle databases.
First, lets go to the design of it. This is truely very useful for beginner like me especially you do not know where to start with. I am using Microsoft Visual Studio 2012 for Web to design my application website.

Second, talk about the Oracle connection string. I have quite sometime searching for correct oracle connection string that works (on my machine at least). You may copied it over, hopefully it will works on you too. Please change where necessary in the string follow your server configuration and setting.

"Data Source=(DESCRIPTION=" _

+ "(ADDRESS_LIST=" _

+ "(LOAD_BALANCE=YES)" _

+ "(FAILOVER=YES)" _

+ "(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))" _

+ "(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))" _

+ ")" _

+ "(CONNECT_DATA=" _

+ "(SERVICE_NAME=YOUR_SERVICE_NAME)" _

+ ")" _

+ ");" _

+ "User ID=YOUR_USER_ID;Password=YOUR_PASSWORD;" 

Now, when you double click on Button from the project, it will bring you to the new coding page. This file is normally ended with *.vb filename. Here will be the location to paste the Oracle connection string as pasted above. Below is how the code will be looks alike:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        'Your Oracle connection Setting

        Dim strConnString As String = "Data Source=(DESCRIPTION=" _

                                    + "(ADDRESS_LIST=" _

                                    + "(LOAD_BALANCE=YES)" _

                                    + "(FAILOVER=YES)" _

                                    + "(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))" _

                                    + "(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST_NAME)(PORT=YOUR_PORT))" _

                                    + ")" _

                                    + "(CONNECT_DATA=" _

                                    + "(SERVICE_NAME=YOUR_SERVICE_NAME)" _

                                    + ")" _

                                    + ");" _

                                    + "User ID=YOUR_USER_ID;Password=YOUR_PASSWORD;" 

        'Your sql statement goes here

        Dim strQuery As String = "SELECT cus_name from  tbl_cus where cus_ID = '" + TextBox1.Text + "'"

        Dim con As New OracleConnection(strConnString)

        Dim cmd As New OracleCommand()

        cmd.CommandType = CommandType.Text

        cmd.CommandText = strQuery

        cmd.Connection = con

        Try

            con.Open()

            Dim sdr As OracleDataReader = cmd.ExecuteReader()

            While sdr.Read()

                'Here will be your output result

                Label1.Text = "Item"

            End While

        Catch ex As Exception

            Throw ex

        Finally

            con.Close()

            con.Dispose()

        End Try

    End Sub

Above code working just fine when I debug the project and click the button. This may help you to explore more on query to Oracle database for your project.