使用linq-to-sql中的命令在SQL Server数据库上执行收缩 [英] Executing Shrink on SQL Server database using command from linq-to-sql

问题描述

我正在寻找清除交易日志的方法；特别是我想缩小日志.我知道有这样做的不好理由，但是在这种情况下，这是有充分理由的.

I'm looking for a way to clear transaction logs; in particular, I want to shrink the logs. I know there are bad reasons for wanting to do it but in this instance, it's for a good reason.

我已经在SQL Server管理中运行了它:

I've run this in SQL Server Management:

DBCC SHRINKFILE(DBName_log)
DBCC SHRINKFILE(DBName)

那是我需要的.现在，我想使用Linq-To-SQL从代码执行此命令，如下所示:

That does what I need. Now I want to execute this command from code using Linq-To-SQL, something like this:

using (MyDC TheDC = new MyDC())
{
   TheDC.ExecuteCommand(....);
}

我需要发送什么命令来使这两个动作在数据库中执行?

What command do I need to send to get both these actions executed in the database?

推荐答案

您的DbContext公开了System.Data.Entity.Database提供的方法ExecuteSqlCommand()具有多个重载.

Your DbContext exposes a System.Data.Entity.Database offering a method ExecuteSqlCommand() that has a couple of overloads.

这是MSDN文章中的文档.

Here's the documentation from the MSDN article.

对数据库执行给定的DDL/DML命令.与任何接受SQL的API一样，对任何用户输入进行参数化以防止SQL注入攻击也很重要.您可以在SQL查询字符串中包含参数占位符，然后提供参数值作为附加参数.您提供的任何参数值都将自动转换为DbParameter.

Executes the given DDL/DML command against the database. As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter.

根据您的需要，我将使用以下内容:

According to your needs, I would use the following:

context.Database.ExecuteSqlCommand("DBCC SHRINKFILE(DBName_log)" ... ); 

该文档还继续说明了如何绑定参数，出于性能考虑，我强烈建议在执行针对SQL Server或Oracle的匿名查询时应执行的操作.

The document also goes on to explain how to bind a parameter which, for performance, I strongly recommend you do whenever you're executing anonymous queries against SQL Server or Oracle.

或者，您也可以构造一个DbParameter并将其提供给SqlQuery.这使您可以在SQL查询字符串中使用命名参数.

Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named parameters in the SQL query string.

再次，根据您的要求:

context.Database.ExecuteSqlCommand(
    "DBCC SHRINKFILE(@file)", 
    new SqlParameter("@file", DBName_log)
);

这篇关于使用linq-to-sql中的命令在SQL Server数据库上执行收缩的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！