Overview:
This document talks about how to connect ASP.NET project with Oracle Database.
Technologies and Tools Used:
The following technologies has been used to achieve the expected output.
- Visual Studio 2019
- Oracle Client
- Oracle SQL Developer
- Oracle Developer Tools for Visual Studio 2019
Use Case:
Assume that there is a requirement to use Oracle database instead of SQL server for an ASP.net project. There is no pre-installed packages available in visual studio for oracle connection, so straight away we cannot connect oracle database with asp.net project, so we have to install Oracle Developer Tools for Visual Studio.
This document explains how to achieve this requirement.
Architecture:
Following steps explains in detail,
Step 1: As a first step, we need to install oracle client in our system.
Step 2: Then we need to install Oracle Developer Tools for Visual Studio, it is available in Oracle website.
Step 3: Then open your respective asp.net project in visual studio. Go to the server explorer, then right click on the Data Connections then click Add connection, select ODP.NET from Data Provider dropdown list.
Step 4: A new connection window will appear, and then we have to select the path for data source for the respective Oracle database connection. After this, we need to give the credentials for the respective database. We can check the connectivity by clicking test connection. Once successfully connected click ok.
Sample:
Step 5: After completing above process, we need to do some required changes in ASP.NET. First, we need to add NuGet package for Oracle.ManagedDataAccess, go to add NuGet package and search for latest version of Oracle.ManagedDataAccess package then install it to the visual studio.
Sample:
Step 6: Then we need set the connection string in the Web.config page in ASP.NET project.
Sample Code:
<connectionStrings>
<add name=”TRAINING.APEXNEW” connectionString=”Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.x.xx)(PORT = xxxx))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = xxxx)));User Id=training; Password=training;” providerName=”Oracle.ManagedDataAccess.Client”/>
</connectionStrings>
Step 7: Then we need to do required changes in ADO.NET to connect the database. Go to the DAL page in the project do required changes in all the method in the pages for Oracle Connection.
Sample Code:
public static empBO emplogin(empBO empbo)
{
Try
{
OracleConnection orclCon = new OracleConnection(“Data Source=(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.x.xx)(PORT = xxxx))(CONNECT_DATA
=(SERVER = DEDICATED)(SERVICE_NAME =xxxxxxx)));User Id=
training; Password=training;”);
orclCon.Open();
OracleCommand objCmd = new OracleCommand(“PR_ASP_LOGIN_TEST1”);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = orclCon;
objCmd.Parameters.Add(“p_user_name”, OracleDbType.Varchar2).Value = empbo.Email;
objCmd.Parameters.Add(“p_password”, OracleDbType.Varchar2).Value = empbo.Password;
objCmd.Parameters.Add(“o_count”, OracleDbType.RefCursor).Direction =
ParameterDirection.Output;
OracleDataReader dr = objCmd.ExecuteReader();
if (dr.Read())
{
empbo.count = dr.GetInt32(0);
}
}
Catch (Exception ex)
{
System.Console.WriteLine(“Exception: {0}”, ex.ToString());
}
return empbo;
}
Screen Shot:
Output:
Here I have created a logic for login page. The calling method from DAL page will connect the oracle database and respective procedure for login in the database. Based on the procedure condition login operation will happen.
Sample 1:
protected void btnLogin_Click(object sender, EventArgs e)
{
empBO empbo = new empBO();
empbo.Email = txtUsername.Text;
empbo.Password = txtPassword.Text;
empBLL.emplogin(empbo);
if (empbo.count == 1)
{
Session[“username”] = txtUsername.Text;
Response.Redirect(“DashBoard.aspx”);
}
else
{
ltrError.Text = “Invalid Username and Password”;
}
}
Sample 2:
After successfully connected to the database if the credentials matched successfully then the screen will redirected to the after login dashboard page.
