EF Database First with ASP.NET MVC 5 and Oracle Database 12c

NuGet Package Manager

There is a great article called “EF Database First with ASP.NET MVC” by Tom FitzMacken that is part of Microsoft’s ASP.NET MVC tutorial.

I wanted to create a website using the steps outlined here, except instead of using a SQL Server database (as was presented in the article), I wanted to use an Oracle 12c database. Unfortunately, Oracle was not an option in the list of Data Sources as in the image below:

no Oracle option available
Oracle DB was not an option.

I had the basic Oracle setup on my development server, however, I found that I did not have Oracle Developer Tools for Visual Studio. I installed the 32-bit version and rebooted the server; I have not tested the 64-bit version for this particular project. Also, I changed the .NET Framework from 4.5 to 4.6. (From what I have read, 4.52 is the minimum that will work with the EF 6 / Oracle setup.) Lastly, I installed several NuGet packages:

  1. Official Oracle ODP.NET, Managed Driver
  2. Official Oracle ODP.NET, Managed Entity Framework Driver
  3. Oracle Data Provider for .NET (ODP.NET) Managed Driver
  4. Oracle Rdb Entity Framework Provider
NuGet Package Manager
NuGet Package Manager

I closed and reopened Visual Studio 2013, and reopened the solution I had created. Now, when adding a Model as in step 2 of the article, I had Oracle options in the Data Source list!

an Oracle option is available
Oracle is now an option!

After selecting the Oracle option, you can complete setting up the connection as below. TNS is available as an option; if you use this, the connection string to Oracle must bu set up in your TNSnames file.

Connection Properties dialog box
Select your Oracle schema.

After this is done, you can continue with the linked tutorial above!

Converting a SQL Table to JavaScript Objects Using the ASP.NET MVC Web API


Happy Independence Day!

Here I will describe an alternative to an earlier post that described using a SQL stored procedure to convert a SQL table into a JavaScript object.

One of the comments left on that post made me wonder why someone would not want to use the stored procedure method. After querying a large table with this method, I do understand why it’s not ideal. It takes quite a long time to pull back large quantities of data.

The method described below, using the Newtonsoft JsonWriter class in an ASP.NET MVC Web API, is much faster and does not require any SQL stored procs. The code I used was modified from Hristo‘s answer on Stack Overflow.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using System.Text;
using System.IO;
using Newtonsoft.Json;

namespace RESTfulAPI.Services
    public class JsonRepository
        public string GetAllJsons(string tableName)
            SqlDataReader rdr = null;
            SqlConnection conn = null;
            SqlCommand command = null;
            HttpContext context = null;
            String connectionString = string.Empty;
            String json = string.Empty;
            connectionString = ConfigurationManager.ConnectionStrings["/* (your named connection string in web.config) */"].ConnectionString;
            conn = new SqlConnection(connectionString);
            command = new SqlCommand("SELECT * FROM " + tableName, conn);
            context = HttpContext.Current;

            StringBuilder allJSONs = new StringBuilder();
            // ... SQL connection and command set up
            command.CommandTimeout = 3600;
            rdr = command.ExecuteReader();

            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            JsonWriter jsonWriter = new JsonTextWriter(sw);
            while (rdr.Read()) {
                int fieldcount = rdr.FieldCount; // count how many columns are in the row
                object[] values = new object[fieldcount]; // storage for column values
                rdr.GetValues(values); // extract the values in each column
                for (int index = 0; index < fieldcount; index++)
                { // iterate through all columns
                    jsonWriter.WritePropertyName(rdr.GetName(index)); // column name
                    jsonWriter.WriteValue(values[index]); // value in column
            json = sb.ToString();
            //End of method
        catch (SqlException sqlException)
        { // exception
            json = "Connection Exception: " + sqlException.ToString() + "n";
            conn.Close(); // close the connection

        return json;