ADO.NET

Connecting to the database.

.NET to Oracle:

I am not an Oracle Pro so I will not go into details here, but let’s start with creating a simple stored procedure in oracle 11g. (Don’t know why but it’s very different to connect to Oracle than to Sql server database.)Here ParcelId is the input parameter that returns the tax history from tax_table and order it by tax year in descending order.

CREATE OR REPLACE PROCEDURE usp_GetTaxHistory

(

   P_PARCELID IN VARCHAR2,

   p_recordset OUT types.cursorType

)

AS

BEGIN

  OPEN p_recordset FOR

  SELECT * from Tax_Table

 where  parid = P_PARCELID

    Order By TAXYR Desc;

END;

Now I will create a function to connect to Oracle database and then I will use it to populate the data using gridview.

Public Shared Function GetOracleConnection(ByVal Parid As String) As OracleDataReader

        Dim conn As New OracleConnection("Data Source=TESTDB; User TEST_USER;Password=Strong_password;")

        Dim cmd As OracleCommand = New OracleCommand

        cmd.CommandText = "usp_GetTaxHistory"

        cmd.Connection = conn

        cmd.CommandType = CommandType.StoredProcedure

        Dim Parameter0 As OracleParameter = New OracleParameter("P_PARCELID", OracleType.VarChar, 15)

        Dim Parameter1 As OracleParameter = New OracleParameter("p_recordset", OracleType.Cursor)

        Parameter0.Direction = ParameterDirection.Input

        Parameter1.Direction = ParameterDirection.Output

        Parameter0.Value = Parid

        cmd.Parameters.Add(Parameter0)

        cmd.Parameters.Add(Parameter1)

        conn.Open()

        Dim dr As OracleDataReader = cmd.ExecuteReader()

        Return dr

    End Function

Next we create a gridview named “gvtaxes” by dragging the gridview control to the page and renaming it to "gvtaxes" and bind it to the datasource that uses the data returned from above function.  

Protected Sub GetDataFromOracle()

        Dim ParId As String

        ParId = gvTaxes.Rows(gvTaxes.SelectedIndex).Cells(2).Text.ToString()

        Dim dr As OracleDataReader

        dr = GetOracleConnection(ParId)

        Dim myDataTable As New DataTable

        myDataTable.Load(dr)

        GridView1.DataSource = myDataTable

        GridView1.DataBind()

        dr.Close()

End Sub

Note: OracleDataClient is depreciated for the newer versions of asp.net

Please feel free to leave and comments/suggestions