Database question

Close releases the connection back to the connection pool, if connection pooling is enabled (and it probably is). You might want to try calling YourConnection.Dispose, if you're really done with the Connection object and don't need to reopen it.

Thanks for the replies - I tried calling dispose on my connection object, still getting the same error.

Yes, this error only occurs when my program is running. I can create databases using other programs when my program is closed just fine. But when I have my program running, I can't create a database in either my program or another. There shouldn't be any connections open in my program except for at startup, and during any transactions initiated by the user. In all cases, I close the connection as soon as I am done with them.

I am using SqlDataReader in a few places, and I close it when I am done, do I need to dispose of that too (how would you do that without a Dispose() method?)?

Thanks for the info - that is good stuff to be aware of. I am opening the connection, passing it to the datareader and then closing and disposing of it myself.

Well, I'm still having the same problem: "Exclusive access could not be obtained because the database is in use." I have (I think) narrowed it down to two functions that cause the problem. If I don't run either of the two functions, everything works fine, but if one or both is run I have the problems. The two functions are almost identical. They take in a string for the name of a database, and will return an ArrayList of the Tables in that database, or the Views in that database (depending on which function is called) Here is the code for the GetTables function:

public ArrayList GetTables(string database)
{
string sql = "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES" +
" WHERE (TABLE_TYPE = \'BASE TABLE\')" +
" ORDER BY TABLE_NAME";

SqlConnection conn = null;
SqlDataReader reader = null;
ArrayList tableList = new ArrayList();

try
{
//set connection
conn = new SqlConnection(connection.GetConnectionForDatabase(database));

SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
reader = cmd.ExecuteReader();

while(reader.Read())
{
tableList.Add(reader.GetString(0));
}
}
catch(SqlException sqlErr)
{
throw sqlErr;
}
catch(Exception err)
{
throw err;
}
finally
{
if(reader != null)
{
reader.Close();
reader = null;
}

conn.Close();
conn.Dispose();
}

return tableList;
}

Regular queries work just fine, but if I try to create a new one, or try to restore a database then it will give me exclusive lock errors.