Send shrink Command to Microsoft SQL Database file via Ado.net connection

Question

How is it possible to execute a direct SQL command to an ADO.NET connected database? I want to send a DBCC SHRINKDATABASE to the SQL server, to compress the current datafile after a big deletion process. The function ObjectContext::CreateQuery returns a parser error after the DBCC command. Is there any other way to shrink the database file, or another way to send the SQL command directly to the SQL Server?

Answer 1

I'd just send this as raw SQL:

using (SqlCommand command = new SqlCommand("DBCC SHRINKDATABASE(0)", connection))
{
    command.ExecuteNonQuery();
}

Another way is to put the DBCC SHRINKDATABASE in a stored procedure and call the stored procedure from your code.

Answer 2

thanks Mark, I just had to figure out how I can obtain the SqlConnection. Here is the complete sourcecode (ok could be written a little bit nicer with an extension method):

    public ActionResult Optimize()
    {
        using (BXPartsEntities Entities = new BXPartsEntities())
        {

            System.Data.EntityClient.EntityConnection eConnection = Entities.Connection as System.Data.EntityClient.EntityConnection;

            eConnection.Open();

            var SqlConnection = eConnection.StoreConnection as SqlConnection;

            if (SqlConnection == null)
                throw new ArgumentException("StoreConnection shall be SQL Connection");

            using (SqlCommand command = new SqlCommand("DBCC SHRINKDATABASE(0)", SqlConnection))
            {
                command.ExecuteNonQuery();
            }

            eConnection.Close();
        }
        return Content("Done");
    }