Getting data from view from oracle to c#

Question

I have a view in oracle having following fields from a table CustomerOrders 

STUDENT_NO	NOT NULL NUMBER(13)
STUDENT_ACCOUNT_TYPE	NOT NULL NUMBER(3)
_STUDENT_ACTIVITY_TYPE	NOT NULL NUMBER(3)
STUDENT_CREDEIT_CARD_NUMBER	NUMBER
STUDENT_TRANSACTION_	NOT NULL NUMBER(3)

I have placed this view as model in entityDatamodel edmx and when i am trying to access it 

var v=(from tt in db.myview where tt.STUDENT_NO ==studentID select tt).firstOrDefault();
in above code studentID is parameter which works fine but when I tried to add the rest of the columns I am getting an error 
"VALUE WAS EITHER TOO LARGE OR TOO SMALL FOR AN UNSIGNED BYTE" 
remember m parameter studentId i first convert this into long like 

long studentID=

studentID=long.Parse(studentID{this is parameter);

Why when i add more columns then it shows the above error If I chose only first column it did not gives any error 

Answer 1

Hey Pakeeza, there might be an issue with the mapping of the Oracle NUMBER(3) data type to the corresponding .NET data type in your Entity Framework model.

You should make sure that the data type mapping in your Entity Framework model matches the Oracle data types correctly. In this case, if Oracle NUMBER(3) corresponds to a small integer , you might want to use an int or short in your class.

For example:

public class MyViewModel
{
    public long STUDENT_NO { get; set; }
    public int STUDENT_ACCOUNT_TYPE { get; set; }
    public int STUDENT_ACTIVITY_TYPE { get; set; }
    public int? STUDENT_CREDEIT_CARD_NUMBER { get; set; }
    public int STUDENT_TRANSACTION { get; set; }
}


var result = (from tt in db.myview
              where tt.STUDENT_NO == studentID
              select new MyViewModel
              {
                  STUDENT_NO = tt.STUDENT_NO,
                  STUDENT_ACCOUNT_TYPE = tt.STUDENT_ACCOUNT_TYPE,
                  STUDENT_ACTIVITY_TYPE = tt.STUDENT_ACTIVITY_TYPE,
                  STUDENT_CREDEIT_CARD_NUMBER = tt.STUDENT_CREDEIT_CARD_NUMBER,
                  STUDENT_TRANSACTION = tt.STUDENT_TRANSACTION
              }).FirstOrDefault();

Answer 2

Jayraj Chhaya thanks for the response there is nothing wrong with studentID parameter and its conversion the issue is from other columns when I add them then the exception fires up , as var can hold any type of veriable so the rest of the variable type does not matter what data type they have ? 

Answer 3

Problem

The error message "VALUE WAS EITHER TOO LARGE OR TOO SMALL FOR AN UNSIGNED BYTE" suggests that there is a data type mismatch or an overflow issue when retrieving the additional columns from the Oracle view.

Cause

The cause of the error is likely related to the data type conversion of the studentID parameter. In the code snippet, the studentID parameter is initially declared as a long and then parsed from a string. However, it seems that the conversion is not handled correctly, leading to the error when retrieving additional columns from the view.

Solution

To resolve the error and retrieve the additional columns from the Oracle view, you need to ensure that the data type conversion of the studentID parameter is handled correctly.

Here's an updated version of the code snippet with the necessary modifications:

long studentID = long.Parse(studentIDParameter);

var result = (from tt in db.myview
              where tt.STUDENT_NO == studentID
              select new
              {
                  tt.STUDENT_NO,
                  tt.STUDENT_ACCOUNT_TYPE,
                  tt._STUDENT_ACTIVITY_TYPE,
                  tt.STUDENT_CREDEIT_CARD_NUMBER,
                  tt.STUDENT_TRANSACTION_
              }).FirstOrDefault();

In the modified code, the studentID parameter is parsed directly from the studentIDParameter without unnecessary conversions. Additionally, the query selects the desired columns from the view and assigns the result to the result variable.

And also check below steps

1. Check the data types of the columns in the Oracle view and compare them with the corresponding properties in the Entity Framework model. Ensure that the data types match correctly.

2. Verify that the data types used in the C# code are compatible with the data types in the Oracle view. For example, if a column in the Oracle view is of type NUMBER, make sure that the corresponding property in the Entity Framework model is of a compatible numeric type in C#, such as int or decimal.

3. If the data types are correct, check if there are any constraints or validations on the columns in the Oracle view that could cause the error. For example, if a column has a constraint that restricts the range of values it can hold, it could result in the error message.

4. Ensure that the data being passed as the parameter studentID is within the valid range for the data type of the STUDENT_NO column. If the studentID parameter is a string, make sure to convert it to the appropriate numeric type before using it in the query.

Here's an example of how you can modify the code to handle the conversion of the studentID parameter to a long:

long studentID = long.Parse(studentID);
var v = (from tt in db.myview where tt.STUDENT_NO == studentID select tt).FirstOrDefault();

Answer 4

when I check the oracle the data type appears as NUMBER(3) but when I check the model there is byte