good way to query many databases in ASP.NET

What I'm trying to do is run the same SQL select on many Oracle databases (at least a dozen), and display the output in a Gridview.

I've hacked together something that works but unfortunately it's very slow. I think its exacerbated by the fact that at least 1 of the dozen databases will invariably be unreachable or otherwise in an error state.

As well as being slow I can't help thinking it's not the best way of doing it, nor very '.NET' like.

I've written something similar in the past as a simple loop in PHP that just connects to each db in turn, runs the sql and writes another <tr>, and it works at least twice as fast, for a given query. But I'm not really happy with that, I'd like to improve my knowledge!

I'm learning C# and ASP.NET so please excuse the horrible code :)

public void BindData(string mySQL)
    {
        OracleConnection myConnection;
        OracleDataAdapter TempDataAdapter;
        DataSet MainDataSet = new DataSet();
        DataTable MainDataTable = new DataTable();
        DataSet TempDataSet;
        DataTable TempDataTable;
        string connectionString = "";
        Label1.Visible = false;
        Label1.Text = "";

        foreach (ListItem li in CheckBoxList1.Items)
        {
            if (li.Selected)
            {
                connectionString = "Data Source=" + li.Text + "";
                connectionString += ";Persist Security Info=True;User ID=user;Password=pass;Unicode=True";
                myConnection = new OracleConnection(connectionString);
                try
                {
                    TempDataAdapter = new OracleDataAdapter(mySQL, myConnection);
                    TempDataSet = new DataSet();
                    TempDataTable = new DataTable();
                    TempDataAdapter.Fill(TempDataSet);
                    TempDataTable = TempDataSet.Tables[0].Copy();
                    /* If the main dataset is empty, create a table by cloning from temp dataset, otherwise
                     copy all rows to existing table.*/
                    if (MainDataSet.Tables.Count == 0)
                    {
                        MainDataSet.Tables.Add(TempDataTable);
                        MainDataTable = MainDataSet.Tables[0];
                    }
                    else
                    {
                        foreach (DataRow dr in TempDataTable.Rows)
                        {
                            MainDataTable.ImportRow(dr);
                        }
                    }
                }
                catch (OracleException e)
                {
                    Label1.Visible = true;
                    Label1.Text = Label1.Text + e.Message + " on " + li.Text + "<br>";

                }
                finally
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                        myConnection = null;
                    }
                    TempDataSet = null;
                    TempDataAdapter = null;
                    TempDataTable = null;

                }
            }
        }
        GridView1.DataSourceID = String.Empty;
        if (MainDataSet.Tables.Count != 0)
        {
        GridView1.DataSource = MainDataSet;
            if (GridView1.DataSource != null)
            {
                GridView1.DataBind();
            }
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        BindData(TextBox1.Text);
    }

Thanks!

UPDATE: The SQL code varies, for testing I have used very simple queries such as select sysdate from dual or select name from v$database. In eventual use, it will be much more complicated, the idea is that I should be able to run pretty much anything, hence the BindData(TextBox1.Text)

UPDATE: The reason for connecting to many databases from the ASP.NET code rather than a stored proc on one or all dbs, or replicating to one db, is twofold. Firstly, the dbs in question are frequently updated replicas of several similar production environments (typically development, testing and support for each client), so anything done to the actual dbs would have to be updated or redone regularly as they are reloaded anyway. Secondly, I don't know in advance what kind of query might be run, this form lets me just type e.g. select count (name) from dbusers against a dozen databases without having to first think about replicating the dbusers table to a master db.

Answers


If you run the DataAdapter.Fill method on a DataTable object the table will be updated with the results from the query. So instead of creating new DataTable and DataSet objects and then copying the DataRows manually you can just add rows to the same table.

Try something like this (in untested C# code):

public void BindData(string mySQL)
{
  OracleConnection myConnection;
  // Empty connection string for now
  OracleDataAdapter MainDataAdapter = new OracleDataAdapter(mySQL, ""); 
  DataTable MainDataTable = new DataTable();
  string connectionString = "";
  Label1.Visible = false;
  Label1.Text = "";

  foreach (ListItem li in CheckBoxList1.Items)
  {
    if (li.Selected)
    {
      connectionString = "Data Source=" + li.Text + "";
      connectionString += ";Persist Security Info=True;User ID=user;Password=pass;Unicode=True";
      MainDataAdapter.SelectCommand.Connection.ConnectionString = connectionString
      try
      {
        MainDataAdapter.Fill(MainDataTable);
      }
      catch (OracleException e)
      {
        Label1.Visible = true;
        Label1.Text = Label1.Text + e.Message + " on " + li.Text + "<br>";
      }
    }
  }
  GridView1.DataSourceID = String.Empty;
  GridView1.DataSource = MainDataTable;
  GridView1.DataBind();
}

I did the following changes:

  • Created one data adapter and assigned it a select command using your mySQL query
  • Gave the connection an empty connection string
  • Created a data table object and removed the data sets (you only need them if your query returns several rows)
  • Changed you loop to just set the connection string of the SelectCommand (you may have to change this to replacing the SelectCommand with a new one)
  • Removed the connection.Close() calls. The DataAdapter does this automatically.

And thats it. If your databases are offline you will still experience slowdowns, but at least the code is simpler and faster since you don't have to copy all the rows between your tables.

One more thing. You can probably set a timeout for the connection in the connection string. Try to lower this one.


Need Your Help

Objective-C: int can´t be NAN?

objective-c ios int double nan

It seems that a int in Objective-C can't be NAN.

jabber-net chat conversation

c# chat xmpp

I'm making simple XMPP client using Jabber-Net. To send simple message i'm using a way from example code, i.e.:

how to draw custom gallery in android

android

I want to develop an app based on the image in which thumbnails can move from top to bottom and left to right simultaneously.