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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
tovvenkiCommented:
Hi,
can you give the full exception details.
Also are you getting data into the datatable??

Thanks and regards,
Venki
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.