|
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
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
That does help -- thank you very much!
|
|
|
|
|
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: 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. 
--Jesse
"... the internet's just a big porn library with some useful articles stuck in." - Rob Rodi
|
|
|
|
|
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);
}
}
|
|
|
|
|
Hi,can anyone suggest me the simplest way of sorting a datagrid
|
|
|
|
|
|
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... "
|
|
|
|
|
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
|
|
|
|
|
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..
|
|
|
|
|
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 )
|
|
|
|
|
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
|
|
|
|
|
The application is running fine in the asp server.
i.e when i debug it from the VS environment.
Please help;
|
|
|
|
|
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
|
|
|
|
|
Hi thanks for the help,
But I have no idea, How we check the configuration of IIS.
I would really appreciate you help.
|
|
|
|
|
Hi,
I have hosted two application sites on two different servers. both the sites are based on windows authentication.
Is there any method by which i can send the credentials used by one site to the other site? so that it will not prompt for the windows useid and password while accessing thr second site from the same window.
( both the sites are using same domain for authenticating users)
need help from an expert...
Regards
Vaibhav Vartak
-- modified at 5:38 Sunday 25th March, 2007
|
|
|
|
|
I have a page which has some frames. After processing one
of the pages I want to redirect to another frameset.
However, when I use Response.Redirect I end up with a
frameset with the frameset rather than just the new
frameset.
I have had to resort to using Javascript to do this but
I'd prefer to use the server.
Any ideas ho to do this?
|
|
|
|
|
Hi,
I have a datagrid which displays few records on page load itself!
Now, there is an add record option where the user could add a new record!
The fucntionality is written in the btn event where the new data should be updated in the database!
This task is succesffully completed, however the datagrid doesnt show the new record, it shows the new records only after the next page load!
Is there a way that on btn click itself the datagrid shows the new inserted records
Gautham
|
|
|
|
|
You actually should be adding records to the datatable, so after you have acceptedchanges you need to rebind the datagrid to the datatable or dataview.
Hope that helps.
Ben
|
|
|
|
|
Thanks!
Apart from that is there any other way ?
Like re-loading only the datagrid with the latest records!!!
Gautham
|
|
|
|
|
There isn't any way I know of doing that. This issue is that the pageload event happens before your button click event. So you don't need to do the same code as you do in the page load you just need a datagrid.databind()
Ben
|
|
|
|
|
Is the issue simply that you don't want to have the ugliness of re-loading the entire page? If so, I can think of two options:
1. Put just the datagrid, all by itself, as a document that gets loaded into a little IFRAME with no scrollbars. Then, have whatever event adds the recordset also reload the page inside the IFRAME, and it will re-read the data from the database and therefore refresh with the new record.
2. You could use AJAX to accomplish basically the same thing as above, with no IFRAME. If you know the basics of the AJAX approach: use the XMLHTTP object to call a document that both adds a record to your database table and returns the HTML for a new datagrid object, then when the responsetext is returned, replace the existing datagrid HTML with the responseText.
|
|
|
|
|
This is Only Problem with Binding your Data with DataGrid..
After adding a new Record Simply call DataGrid binding method..
Then u will not get this problem again..
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
