title: Lock database, using C#
body: <p>I use C#, EF and Microsoft SQL Server in my project. I need to lock the whole database on one of the machine to synchronize it with Active Directory. </p>
  
  <p>Many machines have access to the database, so I need to prevent reading, inserting or updating my database from another machines.</p>
  
  <p>Simple example, what I try to do now:</p>
  
  <pre><code>using (DB context = new DB())
  using (var ts = context.Database.BeginTransaction(System.Data.IsolationLevel.Serializable))
  {
      context.Database.Connection.Open();
  
      try
      {
           string sql = SQLProperties.getInstance().CurrentPropeties["User_Insert"];
           StringBuilder sb = new StringBuilder(10240);
  
           for (int chunkCount = 0; chunkCount &lt; Math.Ceiling((decimal)count / commitCount); chunkCount++)
           {
               using (SqlCommand command = new SqlCommand())
               {
                   command.Transaction = (SqlTransaction)ts.UnderlyingTransaction;
  
                   for (var i = 1; i &lt;= commitCount; i++)
                   {
                       string query = sql;
                       int ind = chunkCount * commitCount + i;
  
                       query = string.Format(query, ind);
  
                       sb.Append(query);
                       //fill command.Params
                   }
  
                   command.Connection = (SqlConnection)context.Database.Connection;
                   command.CommandText = sb.ToString();
  
                   command.ExecuteNonQuery();
              }
  
              sb.Clear();
          }
  
          ts.Commit();
      }
      catch (Exception ex)
      {
           ts.Rollback();
      }
  }
  </code></pre>
  
  <p>But when <code>ExecuteNonQuery()</code> is called, the query is sent to the database and executed. And when I prepare <code>SqlCommand</code>, another machine can read, insert or update data in database even I use single transaction. I need to disable it. </p>
  
  <p>Also I need to do many another changes with data in tables in the database, so I want <strong>lock the entire database during the synchronization</strong>. </p>
  
  <p>I know that in Server Management Studio I can use GO statement for batch SQL queries, but <code>SqlCommand</code> does not support this one statement. Also I try to send GO statement using this method:</p>
  
  <pre><code>ServerConnection svrConnection = new ServerConnection((SqlConnection)context.Database.Connection);
  Server server = new Server(svrConnection);
  int f = server.ConnectionContext.ExecuteNonQuery("LOCK TABLES dbo.User WRITE");
  </code></pre>
  
  <p>But seems it doesn't work.</p>
  
  <p>How can I do it from C# code?</p>
  
  <p>Added: How can I <strong>sync two possible transaction</strong> which were started at the same time? Now I have issue when one transaction starts inserting user in table, and, after 500 users for example, another transaction inserts user and table contains mixed data from two transactions. How can I order them?</p>