ASP.NET

24-Mar-07 14:03

I am connecting to an Oracle database using an OleDbConnection. I am using DataReader objects to get query results. However, this limits me to only having one reader open at a time, which is a problem for one of the operations I am doing.

I have a table with hierarhical data (only 3 levels deep) that I would like to parse through in a depth-first search. Each row in the table in the database has a Name and a ParentName, and I'm doing something like this:

oCmd1 = New OleDbCommand( "Select Name from tblTree where parentName='_top'", oCxn )
oRdr1 = oCmd1.ExecuteReader()

While oRdr1.Read()
     sName1 = oRdr1("Name")
     oCmd2 = New OleDbCommand( "Select Name from tblTree where parentName='" & sName1 & "'", oCxn )
     oRdr2 = oCmd2.ExecuteReader()

     While oRdr2.Read()
         '  do the third level, you get the idea
     End While
     oRdr2.Close()

End While
oRdr1.Close()

The problem is, it won't let me use oRdr2 while oRdr1 is open; and I can't close oRdr1 because it is still in the middle of cycling through the top-level items.

SO, my question is this: What is the PREFERRED way of dealing with this?

The options seem to be:

1) create a new DB connection for each level in the hierarchy
2) move to a different kind of object (DataSet?), instead of a DataReader
3) ??????.... some other option I haven't thought of?

I don't know which approach would be best, or if there are other approaches I'm not thinking of. Any help or advice is greatly appreciated.

Also, I apologize if this question would be better suited to a different forum. Please just let me know, and I'll take it there.

Thanks!
--- Greg Stevens

kubben24-Mar-07 14:13

kubben

24-Mar-07 14:13

Or option 3 use .net 2.0 where you can have multiple result sets open. I am guessing that probably isn't an option for you. So if you are stuck in .net 1.1 then I would go with option 1. Just have multiple connections. Since web apps use application pools as long as your connection string for each of the connection objects is the same you will use connection pooling. So your connections shouldn't get too out of control. If this data isn't going to change too much and isn't too big, I would think seriously of using caching to help out.

Hope that helps.
Ben

GregStevens24-Mar-07 14:22

24-Mar-07 14:22

Thank you for your reply.

My main worry is that this process will be called every time each page is loaded by every user. Does opening and closing three simultaneous connections on every page read incur bad performance hits if there are a large number of users?

Also, you say "I would think seriously of using caching to help out" but I'm not sure that I understand what you mean. Can you give me a two-sentence summary, or point me in the direction of a website / reference where I could find out more?

Thanks again!

kubben24-Mar-07 14:36

kubben

24-Mar-07 14:36

There is a Cache object, that is similar to the Session or Application object. In this case your only options would be application or cache if you want the data to be the same across sessions (users). If the data is completely static and will not change, then you might want to just put it in the Application object. Otherwise you can put it in the Cache.

Here is a Microsoft help on cache:
http://msdn2.microsoft.com/en-us/library/system.web.caching.cache.add.aspx[^]

Hope that helps.
Ben

GregStevens24-Mar-07 15:40

24-Mar-07 15:40

That does help -- thank you very much!

ArrayList Resetting When Adding Form Controls

Jesse Squire25-Mar-07 1:35

Jesse Squire

25-Mar-07 1:35

You don't mention what version of Oracle you're using, but the newer versions (9i and 10g, for sure... I think 8i) have built-in SQL extensions for dealing with hierarchical data. If you were to take advantage of them, it looks as if you may be able to reduce your processing down to a single query. Here are a couple of resources to get started with:

Querying Hierarchical Data with CONNECT BY[^]
Oracle Database SQL Reference (10g) - Hierarchical Queries[^]
Just SQL Part VII - Hierarchical Queries[^]

You may also wish to consider using the System.Data.OracleClient[^] for your connectivity, rather then using raw OleDB. The backing technology is the same, but the Oracle Client has been optimized for Oracle. Oracle also provides its own Oracle Data Provider for .NET[^], if you are more comfortable with that.

Finally, for performance and security reasons, you may wish to consider moving your query into the database as a stored procedure. This will allow the DBMS to compile and cache the execution plan for your query, rather then having to figure it out each time you execute the SQL.

Hope that helps. Smile | :)

--Jesse
"... the internet's just a big porn library with some useful articles stuck in." - Rob Rodi

redfish3424-Mar-07 9:01

redfish34

24-Mar-07 9:01

Below is test code to replicate the problem. I trying to add form controls to an ArrayList. In LinkButton1_Click, two textbox controls are added to ArrayList. No problem. But when i break this code into two separate LinkButtons, only one textbox will ever be added to ArrayList when both link buttons are clicked. The last textbox added will overwrite the first one added. The ArrayList.Add should not behave like this. What is going on? How should i make this work?

To store they control i am using an ArrayList.

public partial class Builder_Generator : System.Web.UI.Page
{
ArrayList _controls = new ArrayList();
TextBox _textBox = null;

protected void LinkButton1_Click(object sender, EventArgs e)
{
_textBox = new TextBox();
_textBox.Text = "first";
_textBox.ID = "first_Textbox";
Form_PlaceHolder.Controls.Add(_textBox);

_textBox = new TextBox();
_textBox.Text = "second";
_textBox.ID = "second_Textbox";
Form_PlaceHolder.Controls.Add(_textBox);
}

protected void LinkButton2_Click(object sender, EventArgs e)
{
_textBox = new TextBox();
_textBox.Text = "first";
_textBox.ID = "first_Textbox";
_controls.Add(_textBox);

}
protected void LinkButton3_Click(object sender, EventArgs e)
{
_textBox = new TextBox();
_textBox.Text = "second";
_textBox.ID = "second_Textbox";
_controls.Add(_textBox);
}
}

datagrid sorting

vsk84200524-Mar-07 8:24

vsk842005

24-Mar-07 8:24

Hi,can anyone suggest me the simplest way of sorting a datagrid

Re: datagrid sorting

kubben24-Mar-07 13:59

kubben

24-Mar-07 13:59

You have to use a DataView and use the Sort property on the dataview. You can set the AllowSorting on the datagrid to true and have it set the Sort on the DataView.

Here's a link to Microsoft help:
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.datagrid.allowsorting.aspx[^]

Hope that helps.
Ben

Re: datagrid sorting

Rahithi24-Mar-07 19:52

Rahithi

24-Mar-07 19:52

Hi,

we can sort the data grid on column. i saw this few days back in one article.

you have tp set the AllowSorting property of the DataGrid to True. This property changes all of the column header text to hyperlinks. A click on the header text hyperlink causes a post-back and a call to the OnSortCommand event handler.
In datagrid control creation u have to set these properties:

AllowSorting as "True" and OnsortCommand as "SortCommand_OnClick"

In the OnSortCommand event handler you simply need to specify how the data should be sorted, recreate the data source and bind the data to the DataGrid. Currently creating the data source and the data binding are done in the BindData() method, so really all you need to do is specify how the data should be sorted before calling the BindData() method. In other words, redefine the SQL statement. Since the SQL statement is a page-level variable you have access to it in the OnSortCommand event handler.

u can write the OnSortCommand event handler.
\\ Sub SortCommand_OnClick(Source As Object, E As DataGridSortCommandEventArgs)
_sqlStmt = _sqlStmt & " ORDER BY " & E.SortExpression
BindData()
End Sub \\

hope this will give you some idea.

Thanks,
Rahithi Sharma

If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "

Re: datagrid sorting

siddisagar25-Mar-07 18:40

siddisagar

25-Mar-07 18:40

1.Hi pls place u r connection string in place of my con string.
2.<asp:datagrid id="DataGrid1" runat="server" allowsorting="True" pagesize="5">
<pagerstyle mode="NumericPages">

3.paste this code in codebehind..

imports system.data.sqlclient

Dim conObj As New SqlConnection
Dim adpObj As New SqlDataAdapter
Dim dsObj As New DataSet
Dim sort As String

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
bindGrid()
If sort = "" Then
sort = "name"
End If
End If
End Sub
Sub bindGrid()
Try
conObj = New SqlConnection("Data Source=B4B-2F-323-CK26;user id=sa;password=sa;Database=TEST")
conObj.Open()
adpObj = New SqlDataAdapter("SELECT * FROM Login", conObj)
adpObj.Fill(dsObj, "Login")
Dim dt As DataTable = dsObj.Tables("Login")
Dim dv As DataView = New DataView(dt)
dv.Sort = sort
DataGrid1.DataSource = dv
DataGrid1.DataBind()
Catch ex As Exception
Response.Write(ex.ToString())
End Try
End Sub

Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles DataGrid1.SortCommand
sort = e.SortExpression
bindGrid()
End Sub
End Class
Laugh | :laugh:

Deployment of asp pages on IIS

MinhajuddinK24-Mar-07 7:34

24-Mar-07 7:34

Hi there
I have developed a web application. But I don't know how to deploy it on server.
If i try publishing it or copy the files and pasting it in the wwwroot It' is giving an error.
|
|
V

XML Parsing Error: not well-formed
Location: http://localhost/test/Default.aspx
Line Number 1, Column 2:<%@ page language="C#" autoeventwireup="true" inherits="_Default, App_Web_qvmriy24" %>
-^

Please help me out .
And thanx for the help in advance..

Re: Deployment of asp pages on IIS [modified]

Christian Graus24-Mar-07 7:40

Christian Graus

24-Mar-07 7:40

Does the server have .NET installed ?

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

MinhajuddinK24-Mar-07 7:46

24-Mar-07 7:46

Yeah!
I have Visual Studio 2005 Installed on my system.
So that means i have the .Net runtime too.

-- modified at 13:58 Saturday 24th March, 2007

MinhajuddinK24-Mar-07 8:09

24-Mar-07 8:09

The application is running fine in the asp server.
i.e when i debug it from the VS environment.
Please help;

GregStevens25-Mar-07 16:42

25-Mar-07 16:42

It looks to me like it is trying to interpret the .ASPX file extension as an XML file type.

If I were you, I would check the IIS configuration to make sure that it has the .ASPX file extension assigned so that it is handled by ASP.NET.

Do you know how to check the document types assigned to file extensions on IIS?

----Greg Stevens

MinhajuddinK25-Mar-07 17:05