Convert into IEnumerable<EssaiRef>

Hello everybody,

I have an aspx page which displays a gridView and on each gridView row, there is a link to open a detailView.
And there are Ajax object too : asp:UpdatePanel and ajaxToolKit:ModalPopupExtender (to display the DetailVeiw in modal mode).

This sample works fine with an Xml file containing the data but there is a bug when I try to make it work with data coming from an Oracle database.

Here is the bug when I try to display the DetailView : I don't reach to return a "IEnumerable<EssaiRef>" in the method  :

[Code]
 
 
[DataObjectMethod(DataObjectMethodType.Select)]
    public IEnumerable<EssaiRef> FindByID(string Essai_ID)
{
    OracleConnection conn = new OracleConnection();
    conn.ConnectionString = "Data Source=Test.World;User Id=test1;Password=test1;";
    conn.Open();
 
    string sql = "Select * from essai_ref where Essai_ID =" + Essai_ID;
    OracleCommand cmd = new OracleCommand(sql, conn);
 
    OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
 
    DataSet oracleDataSet = new DataSet();
    dataAdapter.Fill(oracleDataSet);
 
    DataTable dataTableTmp = oracleDataSet.Tables[0];
 
    conn.Close();
    conn = null;
 
    return (IEnumerable<EssaiRef>) dataTableTmp.AsEnumerable).ToList();    

[/Code]
 
I have the error message  : "Sys.WebForms.PageRequestManagerServerErrorException : Exception has been thrown by the target of an invocation."

I tried several casts to IEnumerable<EssaiRef> but none did work.

The following code works with an Xml file :

[Code]:
 
   [DataObjectMethod(DataObjectMethodType.Select)]
    public IEnumerable<Customer> FindByID(string id)
    {
        //  find the customer
        return (from c in this.Customers where c.ID == id select c).ToList();
    }
[/Code]

The method FindByID is used in the aspx page in a ObjectDataSource object which feed the detailView :


[Code]
 
<asp:ObjectDataSource ID="odsEssaiRefDetail" runat="server" TypeName="getOracleData" DataObjectTypeName="EssaiRef" OnSelecting="OdsCustomerDetail_Selecting" SelectMethod="FindByID" UpdateMethod="Update">
            <SelectParameters>
                <asp:Parameter Name="Essai_ID" Type="Int32" />
            </SelectParameters>
[/Code]

Do you have an idea ?

Thanks a lot in advance.

Laurent.
laurent_diepAsked:
Who is Participating?
 
Bob LearnedCommented:
You are using SELECT *, and you can't guarantee the order to the fields from the database.  I would use field names:

list.Add(new EssaiRef()
                   {
                        Essai_ID = int.Parse(rdrOracle["Essai_ID"].ToString()),
                        Essai_Name = rdrOracle["Essai_Name"].ToString(),
                    }
0
 
laurent_diepAuthor Commented:
I tried :

[Code]
return dataTableTmp.AsEnumerable().Cast<EssaiRef>();
[/Code]

in FindById but I have the following message :

Unable to cast object of type 'System.Data.Datarow' to type 'EssaiRef'  

Laurent.
0
 
Bob LearnedCommented:
It sounds like you have a confusing array of technologies, from ADO.NET classic, ObjectDataSource, LINQ.  Which one would you like to concentrate on?  I would vote for ADO.NET Entity Data Model.  Which version of .NET do you have?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
tovvenkiCommented:
Hi,
can you give the full exception details.
Also are you getting data into the datatable??

Thanks and regards,
Venki
0
 
laurent_diepAuthor Commented:
Thank you for your answers.

TheLearnedOne:
I have just adapted a project which works fine with an Xml file. So I have just modified the data retriever method in FindById and I have modified the existing ObjectDataSources and other objects parameters in the aspx files to suit the Oracle database. I didn't add any new objects. So I was expecting this new project would work.
As you like, I can use "ADO.NET Entity Data Model" : does it suit for accessing Oracle 10g database in Read/write mode ?
I am on the 3.5 framework with Visual Studio 2008.

tovvenki:
I cannot send you the exception because there is just a simple msgbox with the message below, there is no message in the Internet explorer which ask me if I want to debug in Visual Studio 2008 : when I click "Yes" I go on internal code (Incomprehensible for me).
The database returns data because I have a gridview which display the Oracle table rows.
When I click on a link in one row I should go to the DetailsView which displays the row (but it fails).

And when I return null (instead of the IEnumerable<EssaiRef>) on the FindByID method (as shown below), the Detailsview is displayed without fields in it.

Laurent.
0
 
Bob LearnedCommented:
What is "EssaiRef"? If it is a business object, you can't cast a DataTable to IEnumerable list of that business object, you would need to build instances of the business object, and fill the properties. The .NET Entity Framework is a way of providing generic data access. You would have an .edmx file that defines the data model--tables, associations (relationships). The class generator generates all the business objects for you, plus all the glue to bind to the underlying database. You can query the data using LINQ/PLINQ expressions, and update the database with generated methods (i.e. SaveChanges).

Here is a jumping off point from MSDN about the ADO.NET Entity Framework:

ADO.NET Entity Framework
http://msdn.microsoft.com/en-us/library/bb399572(VS.90).aspx
0
 
laurent_diepAuthor Commented:
EssaiRef is a class (a business object) and I have already tried to use it as you said (fill the propertiies) but it failed :

[code]

OracleConnection conn = new OracleConnection();
    conn.ConnectionString = "Data Source=Test.World;User Id=Essai1;Password=Essai1;";
    conn.Open();

    string sql = "Select * from Essai_Ref where Essai_ID =" + Essai_ID;
    OracleCommand cmd = new OracleCommand(sql, conn);

    OracleDataReader rdrOracle = cmd.ExecuteReader();

    EssaiRef EssaiRefTmp = new EssaiRef();

    rdrOracle.Read();

    EssaiRefTmp.Essai_ID = rdrOracle.GetInt32(1);
    EssaiRefTmp.Essai_Name = rdrOracle.GetString(1);

    rdrOracle.Close();
   return (IEnumerable<AssayReference>) EssaiRefTmp;

[Code]

And I had a cast error.

In fact I think the problem comes from my code in the aspx form because however the ways to return the IEnumerable<EssaiRef>, it fails.

Finally I don't think I would engage in this new technology 'ADO.Net entity framework' because of the few time I have to resolve the problem. I have just 1 week to develop 2 aspx forms.

Could you help me if I use ODT (Oracle Developer Tolls) ?

In fact my aim is to access to 2 main tables in my Oracle database, so I don't think that 'ADO.Net entity framework' is necessary.

Laurent.
0
 
Bob LearnedCommented:
You need an enumerable list of EssaiRef, which should look more like this:


            List<EssaiRef> list = new List<EssaiRef>();
            string connectionString = "Data Source=Test.World;User Id=Essai1;Password=Essai1;";
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();

                string sql = "Select * from Essai_Ref where Essai_ID = " + Essai_ID;
                using (OracleCommand cmd = new OracleCommand(sql, conn))
                {
                    using (OracleDataReader rdrOracle = cmd.ExecuteReader())
                    {
                        while (rdrOracle.Read())
                        {
                            EssaiRef EssaiRefTmp = new EssaiRef();
                         
                            list.Add(new EssaiRef() { Essai_ID = rdrOracle.GetInt32(0), Essai_Name = rdrOracle.GetString(1)});

                        }
                    }

                }
                return (IEnumerable<EssaiRef>) list;
            }

Open in new window

0
 
laurent_diepAuthor Commented:
I will test your solution as soon as I have installed Oracle in my home computer...

Laurent.
0
 
laurent_diepAuthor Commented:
I have test your solution but I have a popup error message when I click on the gridView 'Details' link :

"Sys.WebForms.PageRequestManagerServerErrorException: An exception has been thrown by a target of a call."

"Do you want to debug this web page"
 
"Use the In Internet Explorer integrated scripting debugger ?"

"Yes/No"

When I click "Yes", I go in the debugger :



I think it's a javascript error because I use some Ms Ajax Controls.

Do you have an idea ?

Laurent.
Sys.Net.XMLHttpExecutor = function Sys$Net$XMLHttpExecutor() {
    /// <summary locid="M:J#Sys.Net.XMLHttpExecutor.#ctor" />
    if (arguments.length !== 0) throw Error.parameterCount();
    Sys.Net.XMLHttpExecutor.initializeBase(this);
    var _this = this;
    this._xmlHttpRequest = null;
    this._webRequest = null;
    this._responseAvailable = false;
    this._timedOut = false;
    this._timer = null;
    this._aborted = false;
    this._started = false;
    this._onReadyStateChange = (function () {
        
        if (_this._xmlHttpRequest.readyState === 4 ) {
            try {
                if (typeof(_this._xmlHttpRequest.status) === "undefined") {
                    return;
                }
            }
            catch(ex) {
                return;
            }
            
            _this._clearTimer();
            _this._responseAvailable = true;
            try {
                _this._webRequest.completed(Sys.EventArgs.Empty);
            }
            finally {
                if (_this._xmlHttpRequest != null) {
                    _this._xmlHttpRequest.onreadystatechange = Function.emptyMethod;
                    _this._xmlHttpRequest = null;
                }
            }
        }
    });
    this._clearTimer = (function() {
        if (_this._timer != null) {
            window.clearTimeout(_this._timer);
            _this._timer = null;
        }
    });
    this._onTimeout = (function() {
        if (!_this._responseAvailable) {
            _this._clearTimer();
            _this._timedOut = true;
            _this._xmlHttpRequest.onreadystatechange = Function.emptyMethod;
            _this._xmlHttpRequest.abort();
            _this._webRequest.completed(Sys.EventArgs.Empty);
            _this._xmlHttpRequest = null;
        }
    });

Open in new window

0
 
laurent_diepAuthor Commented:
In the aspx page, I have removed all the Ajax controls and Ajax code.

Now there are simply a GridView and a DetailsView.

And when I click on the 'details' link, I have an error with your solution :

System.InvalidCastException: The specified cast is not valid.

at the line :

list.Add(new EssaiRef() { Essai_ID = rdrOracle.GetFloat(0),
                                                    Essai_Name = rdrOracle.GetString(1)});

I have changed the data type of Essai_ID to float to see if it would give a better result.
0
 
Bob LearnedCommented:
I have no idea why you would be getting a Javascript error, and I can't debug for you, so you would have to give me more information to go on.

What is the data type defined in the database for those two fields?
0
 
laurent_diepAuthor Commented:
I removed the Ajax codes from my project so there are no more Javascript errors.

I just have this error (in the IE) :
System.InvalidCastException: The specified cast is not valid.

at the line :

               list.Add(new EssaiRef()
                    {
                        Essai_ID = rdrOracle.GetInt32(0),
                        Essai_Name = rdrOracle.GetString(1)
}
)

If you want I can attch the entire stack trace.

In Oracle, the data types are  :
Essai_ID is a Number
Essai_Name is a Varchar2(100)
0
 
laurent_diepAuthor Commented:
I have created a smaller business object, with only a string and it worked well.

I will test your solution and keep you informed.
0
 
laurent_diepAuthor Commented:
Hello TheLearnedOne,

You are right : it is better when I precise all the columns in the Select clause.

And I used : Decimal.ToInt32(rdrOracle.GetDecimal(0)) to retrieve Number type columns

Here is the code that works :

Thanks again and here are your points !

Laurent.
public IEnumerable<Essai_Ref> FindByEssaiRefID(string Essai_ID)
{
 
    List<Essai_Ref> list = new List<Essai_Ref>();
 
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Essai_Reference"].ConnectionString;
 
    using (OracleConnection conn = new OracleConnection(connectionString))
    {
        conn.Open();
 
        string sql = "Select Essai_ID,Essai_NAME from Essai_Ref
            where Essai_ID = " + Essai_ID;
        
        using (OracleCommand cmd = new OracleCommand(sql, conn))
        {
            using (OracleDataReader rdrOracle = cmd.ExecuteReader())
            {
                while (rdrOracle.Read())
                {
                    Essai_Ref EssaiRefTmp = new Essai_Ref();
 
                    list.Add(new Essai_Ref() 
                    {
 
                        Essai_ID = Decimal.ToInt32(rdrOracle.GetDecimal(0)),
                        Essai_Name = rdrOracle.GetString(1)
                                                 
                    });
 
                }
            }
 
        }
        conn.Close();
        conn.Dispose();
 
        return (IEnumerable<Essai_Ref>)list;
 
    }
    
}

Open in new window

0
 
Bob LearnedCommented:
With the 'using' blocks, you don't need to explicitly close and dispose of the connections.  The .NET runtime will generate those for you.  You can remove these two lines:

    conn.Close();
    conn.Dispose();
0
 
laurent_diepAuthor Commented:
It's noted !

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.