Send Compression Command to Microsoft SQL Database File via Ado.net Connection

Question

How can I execute a direct SQL command for a mounted ADO.NET database? I want to send DBCC SHRINKDATABASEto a SQL server to compress the current data file after a long delete process. The function ObjectContext::CreateQueryreturns a parser error after the DBCC command. Is there any other way to collapse the database file or another way to send the SQL command directly to SQL Server?

Answer 1

I will just send this as raw SQL:

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

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

Answer 2

Mark, , SqlConnection. (ok ):

    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");
    }