Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

.NET multiple database connections

We used to have code that looped through our databases and detached every one that started with 'trial'.  This was just pointing to one db.  But now I've modified it so the code below runs in a loop and connects to more than one database... trouble is ds.Tables[0].Rows always contains the same databases from the first database connection :

for(int i=0; i<rgstConnection.Length; i++)
{
string stConnection = rgstConnection[i];

SqlConnection conn = new SqlConnection( stConnection );
conn.Open();
SqlDataAdapter ad = new SqlDataAdapter();
ad.SelectCommand = new SqlCommand("sp_helpdb", conn);
ad.SelectCommand.CommandTimeout = 1800; // 30 minutes

DataSet ds = new DataSet();
ad.Fill(ds);

foreach (DataRow rw in ds.Tables[0].Rows)
{
    string s = rw["name"].ToString();
    if (s.StartsWith("trial"))
    {
        SqlCommand cmd = new SqlCommand("sp_detach_db '" + s + "'", conn);
        try
        {
            cmd.ExecuteNonQuery();
            Thread.Sleep(3000);
            // for now, just detach and nothing else
        }
        catch (Exception)
        {
            // move on.
        }
    }
}
ds.Clear();
conn.Close();
}

Anyone know why?  THe second time through the loop, if I print out conn.ConnectionString it is set to the right database, but ds.Tables[0].rows still has the old data in it... which makes no sense to me because ds = new DataSet() each time through the loop...

Michael H. Pryor
Fog Creek Software
Monday, February 21, 2005

"ad.Fill(ds)" adds a *new* table to the DataSet every time you call it - ds.Tables[0], ds.Tables[1], ds.Tables[2], etc

So your next line/block of code
  foreach (DataRow rw in ds.Tables[0].Rows)
will always work against the very first table you added!

So instead - don't use the DataSet (only use DataSets when you want to do relationships etc with multiple tables)

i.e.

  DataTable databasesTable = new DataSet();
  ad.Fill(databasesTable);
  foreach (DataRow databaseRow in databasesTable.Rows)
    ...

Duncan Smart
Monday, February 21, 2005

But he's creating a new DataSet each time, rather than filling an existing dataset.

From a cursory glance the code looks fine to me. My guess in those sorts of situations is always "presume you did something stupid" - though it very well could be a framework fault, has the debugger been connected to ensure that stConnection really shows a different value on each iteration?

Dennis Forbes
Monday, February 21, 2005

Thanks Duncan and Dennis.  Dennis you were right about it recreating each time through so that couldn't have been the problem.  Then I started thinking that maybe the user could only see those 4 dbs that are in every SQL server install and thats why I kept thinking the datarows were the same.

Sure enough, that was it... It was a permissions problem.  Sorry for the lame-o question.

Michael H. Pryor
Fog Creek Software
Monday, February 21, 2005

To add an after-the-fact question, why are you using a data set as opposed to a data reader? It seems that the firehose "loop through the records" is just what the data reader was built for?

moron
Tuesday, February 22, 2005

He is using the connection for other purposes (executing a database detach), which he couldn't do if the connection is busy servicing the reader.

Dennis Forbes
Tuesday, February 22, 2005

*  Recent Topics

*  Fog Creek Home