Database occupied

Question

 public List<R> GetA(DateTime dataMov)
    {
        connection();
        List<R> menuList = new List<R>();

        try
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT r.nome,r.cos,r.estoque, r.bala as 'balance', movimento, movimento, (estoque + r.balance) as 'diver'  FROM R_ATI r left join DR_DIA d on d.conta = r.cos where movimento = '" + dataMov.ToString("s") + "' and movimento = '" + dataMov.ToString("s") + "' and r.estoque <> 0.0", con))
            {
                var r = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (r.HasRows)
                {
                    foreach (DbDataRecord s in r)
                    {                            
                        menuList.Add(new Renda
                        {
                            papel = Convert.ToString(s["nome"]),
                            cos = Convert.ToString(s["cos"]),
                            curva = Convert.ToDouble(s["estoque"]),
                            curvaA = Convert.ToDouble(s["balance"]),
                            divergencia = Convert.ToDouble(s["divergencia"])


                        });
                    }
                }
            }
        }
        catch (Exception ex)
        {

            con.Close();
            throw ex;
        }
        finally
        { con.Close(); }

        return menuList;
    }

To mount this grid I use more than one database, it is in my model I would like to know how to put a warning to the user, warning that when this list does not appear on the screen, it indicates that someone is using the database. Thank you

Answer 1

To find out if the database is currently in use, the most accurate way would be to find out if any session has any specific database lock, by consulting sys.dm_tran_locks. Here is an auxiliary function to return a bool if the database is in use or not:

bool BancoDeDadosEmUso(string NomeDoBancoDeDados)
{
    using (SqlConnection sqlConn = new SqlConnection("... sua string de conexão ..."))
    using (SqlCommand sqlCmd = new SqlCommand())
    {
        sqlCmd.Connection = sqlConn;
        sqlCmd.CommandText = @"select count(*) from sys.dm_tran_locks where resource_database_id = db_id(@NomeDoBancoDeDados);";

        sqlCmd.Parameters.Add(new SqlParameter("@NomeDoBancoDeDados", SqlDbType.NVarChar, 128)
        {
            Value = NomeDoBancoDeDados
        });

        sqlConn.Open();
        int ContaSessao = Convert.ToInt32(sqlCmd.ExecuteScalar());

        if (ContaSessao > 0)
            return true;
        else
            return false;
    }
}

See more in:

C# how to check if database is not Busy?