-2

In My Application I have so Many Connection that open and Close as Needed, but maybe some of them not close completely ! if i need exclusive access to database for reasons like restoring Data base or other Goals

if i want to restore my bak file, because of active connection an exception occure that it is not possible to have exlusive access to database for restoring. So I should kill Connection for restoring !

update 1: All Of connection in my code handle by using code block

using (SqlConnection con = new SqlConnection(BaseModule.ConnectionString))
                {
                   //Code
                }

but already i have opened connection

update 2: my problem Solved by This Code :

ALTER DATABASE [dbName]
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [dbName]
SET ONLINE

but Is it good Way To kill Active Connection ? it can be caused losing data in programs that working in network ?

update 3:

Restoring bak File Code :

        try
        {
            Restore objRestore = new Restore();
            ServerConnection con;
            con = new ServerConnection("(local)", BaseModule.dbUserName, BaseModule.dbPassWord);
            Server srv = new Server(con);
            objRestore.Database = BaseModule.dbName;
            objRestore.Action = RestoreActionType.Database;
            objRestore.Devices.AddDevice(dialogOpen.FileName, DeviceType.File);
            this.progressRestore.Value = 0;
            this.progressRestore.Maximum = 100;
            this.progressRestore.Value = 100;
            objRestore.PercentCompleteNotification = 10;
            objRestore.ReplaceDatabase = true;
            objRestore.PercentComplete += new PercentCompleteEventHandler(objRestore_PercentComplete);
            objRestore.SqlRestore(srv);
            MessageBox.Show("Completed");

        }
        catch (Exception exp)
        {
            MessageBox.Show("Error Occured" + exp.InnerException.ToString());
        }
Moslem7026
  • 3,290
  • 6
  • 40
  • 51
  • 1
    How is anybody supposed to answer this? – LukeHennerley Nov 09 '12 at 12:44
  • Handle your connection within its own thread and implement a lock condition. Of course it sounds like how your handling the connection is the wrong way if your opening and closing the connection yourself instead of using the pool already built in to .NET – Security Hound Nov 09 '12 at 12:44
  • I handle my connection by `using` code block , but i dont know why they are still open ! – Moslem7026 Nov 09 '12 at 13:02
  • @Moslem7026 - You need to figure out that reason. The code you provided isn't enough to help you. – Security Hound Nov 09 '12 at 13:16
  • okey , Question Edited and Updated – Moslem7026 Nov 09 '12 at 13:25
  • possible duplicate of [How do you kill all current connections to a SQL Server 2005 database?](http://stackoverflow.com/questions/11620/how-do-you-kill-all-current-connections-to-a-sql-server-2005-database) – ChrisF Nov 10 '12 at 11:18

1 Answers1

5

Try this:

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--do you stuff here 

ALTER DATABASE YourDatabase SET MULTI_USER

Also, have a look here:

How do you kill all current connections to a SQL Server 2005 database?

Community
  • 1
  • 1
Damian
  • 107
  • 1
  • 5