Nice way to query many databases in ASP.NET
What I am trying to do is run the same SQL selection on many Oracle databases (at least a dozen) and display the output in a Gridview.
I have hacked something that works, but unfortunately it is very slow. I think this is compounded by the fact that at least one of dozens of databases will be invariably unreachable or otherwise in an error state.
As well as being slow, I can't help but think that this is not the best way to do it, and not very ".NET".
I wrote something similar in the past as a simple loop in PHP that in turn connects to each db, runs sql and writes another <tr>
, and it runs at least twice as fast for a given query, but I'm not very happy with that, I wanted to improve your knowledge!
I am 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);
}
Thank!
UPDATE: The SQL code is changing, I used very simple queries like select sysdate from dual
or for testing select name from v$database
. It will end up being much more difficult, the idea is that I should be able to run almost anything, soBindData(TextBox1.Text)
UPDATE. The reason for connecting to many databases from ASP.NET code rather than a stored procedure on one or all dbs or replicating to one db is twofold. First, the dbs in question are frequently updated with replicas of several similar production environments (typically development, test and support for each client), so whatever needs to be done for the actual dbs has to be updated or re-updated as they reboot anyway ... Secondly, I don't know in advance what kind of query can be run, this form allows me to just type, for example. select count (name) from dbusers
against a dozen databases without thinking about replicating the dbusers table in the master db.
source to share
If you run the DataAdapter.Fill method on the DataTable object, the table is updated with the query results. So instead of creating new DataTable and DataSet objects and then manually copying the DataRows, you can simply 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();
}
We made the following changes:
- Created one data adapter and assigned a select command to it using your mySQL query
- Connect empty connection string
- Created a dataset object and deleted datasets (you only need them if your query returns multiple rows)
- Changed the loop to just set the SelectCommand connection string (you may have to change this to replace the SelectCommand with a new one)
- Removed calls to connection.Close (). The DataAdapter does this automatically.
And here it is. 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 reduce this.
source to share
there can be many factors causing it to slow down. What is the execution of a slow SQL command?
If anyone reads this uses sql server, Scott Mitchell wrote a nice article to help solve this issue in sql server: Performing the same query Against multiple databases
source to share
It sounds like you might be more interested in getting an answer to this more general question: How do I complete a long running task without hanging the UI (ASP or WinForms)?
The answer to this question is using multiple Threads . I would do such a lengthy task on a separate thread and show the user a page with the current results (either automatically refreshed, or via ajax, etc.). You can even get fancy and create tasks for every available processor to get the most out of your machine (using something like Parallel Extensions ); however, this increases complexity significantly and can be difficult to obtain.
If you haven't worked with Threads in .Net you can find a great tutorial here (one at a time and Jon Skeet only )
source to share