ASP.Net Connection to Oracle and SQL database and Hyperlink on output

AnandSahoo
AnandSahoo used Ask the Experts™
on
Dear Team,

Need your help on the following. I have one ASP.net webpage. It just works as a dashboard for us to show the numbers from oracle database.

using System;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace TOT_COUNT
{
   
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            try
            {  
                         
                string connectionstring = "PROVIDER=MSDAORA;Data source=OPSMGR;User ID=OPSMGR ;password=OPSMGR;";

                OleDbConnection oracleConn = new OleDbConnection(connectionstring);
                OleDbCommand oraclecomTOT = new OleDbCommand("select assignment,distinct count(number) as TOTAL from workorder where status <> 'closed'", oracleConn);

oracleConn.Open();
OleDbDataReader oracleread = oraclecomTOT.ExecuteReader(System.Data.CommandBehavior.Default);
GridView_data.DataSource = oracleread;
                GridView_data.DataBind();

}


This works fine. if you check the connection string its string connectionstring = "PROVIDER=MSDAORA;Data source=OPSMGR;User ID=OPSMGR ;password=OPSMGR;";
The odbc is setup in the pc.

My problem is (1st one is important)
1. It takes ages to load the page. When run this query using some query analyser it just takes 1 second.
Please could you suggest me the best possible way to do this so that it will run faster.Also suggest me for SQL database as well.

2. I get the data in following format in gridview
assignment TOTAL
IT 40
HR 50
finance 60
Now I want to have the assignment groups (IT,HR etc) in the form of hyperlink in gridview, so when I click on that it will run the query for that particular assignment and give me the details in a separate page.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Is it slow on first load or slow everytime you run the page?

Author

Commented:
its slow everytime i load it
Most Valuable Expert 2012
Top Expert 2014

Commented:
Is it ok when connection to SQL? It could be a database issue. Usually websites are slow to come up first time you (recompilation etc) but ok afterwards.

Author

Commented:
The speed is almost same in SQL.

Is the connection String mentioned is correct or you have some better suggestion ?

Let me also check the database as well.

Can You suggest for th e2nd point in the meantime plz
Most Valuable Expert 2012
Top Expert 2014

Commented:
Connection string looks fine. Do a demo in a winform app and see if the speed is same.

For 2nd point, you can render select links in grid and then redirect to a page which takes an querystring parameter to show details related to selected row.

Author

Commented:
Request to explain in brief as how to pass that hyperlink string as parameter to a different pageto use the same in a different query ?
Most Valuable Expert 2012
Top Expert 2014
Commented:

Author

Commented:
Hi,

Was trying the Examples given by you above. I also found some links by searching in google.
Problem I am facing now is

Earlier Autocolumn generation was true in my grid view and Iam just using
GridView_data.DataSource = oracleread;
                GridView_data.DataBind();
to show the columns.

Now That I am creating columns manually, I am not able to map the data return in the query to the gridview. Grid view loads but with just column names.

Plz can you help in mapping the query return columns in the grid view columns created ?

Author

Commented:
Got it done with the help of the forum. Thanks a ton for your time and sharing this.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial