Checking SQL Server for active database connections in C#?

[gazy2k4]

I'm writing a program that connects to MS SQL Server, on start up it clears some tables in one database, and repopulates them from another database. I would like this to only happen if there are no other connections to the database I'm clearing. Is there any way of checking the amount of active connections on a specfic database within code?

Checking active connections by user is also an option if thats the only way to do it as all connections to the database should be a specific user. If it's an admin I'm assuming it is via SQL Server Manager and want everything to behave as if they weren't connected. Google hasn't provided me with any answers as of yet, which is probably my fault, but i'm giving myself the benefit of the doubt and blaming Google. So I'll keep looking.

Thanks.

[kenobewan]

I believe that you may run a stored procedure that uses exec sp_who, to give you a count of how many active connections. HTH.

[gazy2k4]

thanks for the reply, it's lead me to the solution.

In the sp_who procedure it selects from the "sysprocesses" table in the master database. I took that query a modified it slightly so that it tailored to the database I wanted, and now whenever there is an open connection this select statement will tell me how many, which user and on what PC they are connected:

select spid, ecid, status, loginame=rtrim(loginame), hostname, blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end , cmd, request_id
from master.dbo.sysprocesses
where db_name(dbid) = '*Database Name*' AND dbid <> 0

[Plater]

I like sp_who2 better because of its extended details/columns, and it appears to be what sql management studio uses