CODEWITHSUNDEEP
c#sql-serverdatabaseentity-frameworkasp.net-mvc-4
vlad horodnii
vlad horodnii

Reputation: 11

Lock database, using C#

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.

Many machines have access to the database, so I need to prevent reading, inserting or updating my database from another machines.

Simple example, what I try to do now:

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 < Math.Ceiling((decimal)count / commitCount); chunkCount++)
         {
             using (SqlCommand command = new SqlCommand())
             {
                 command.Transaction = (SqlTransaction)ts.UnderlyingTransaction;

                 for (var i = 1; i <= 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();
    }
}

But when ExecuteNonQuery() is called, the query is sent to the database and executed. And when I prepare SqlCommand, another machine can read, insert or update data in database even I use single transaction. I need to disable it.

Also I need to do many another changes with data in tables in the database, so I want lock the entire database during the synchronization.

I know that in Server Management Studio I can use GO statement for batch SQL queries, but SqlCommand does not support this one statement. Also I try to send GO statement using this method:

ServerConnection svrConnection = new ServerConnection((SqlConnection)context.Database.Connection);
Server server = new Server(svrConnection);
int f = server.ConnectionContext.ExecuteNonQuery("LOCK TABLES dbo.User WRITE");

But seems it doesn't work.

How can I do it from C# code?

Added: How can I sync two possible transaction 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?

Upvotes: 1

Views: 3612

Answers (1)

SpaceUser7448
SpaceUser7448

Reputation: 189

If you really need to lock the database you could use:

ALTER DATABASE [xyz] SET SINGLE_USER

This will restrict the ability of other users to connect to the database.

Upvotes: 2

Related Questions