How to dertiminate how many connecitons

  • Thread starter Thread starter ad
  • Start date Start date
A

ad

I am use VS2005 Profession and SqlExpress 2005 to develop WebApplication.
I use many SqlDataReader in my application.
I think there are some connecitons not be closed after reading data.

How can I moniter the status of conneciton of a Database?
 
1) Use the SQL Profiler to monitor connections
2) With the 1.1 framework (try to) use the .NET data performance counters.
These can show how many connections are pooled but they don't work very
well.
3) With the 2.0 framework there are new performance counters (that work)
4) With any version of SS you can monitor the number of user connections
using its own performance counters.

I'll be discussing these (and showing the code) in my session at VSLive in
Sydney.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I'm not recommending that you do this to
close connections in production. That
sample could be useful in helping you
figure out if you really do have a
problem with connections not
be closed or not.

--
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.masterado.net
 
I have tried the codes (below) in the link:

But what doesGetDataTableFromSql function come from?

What about ths SQL:
select spid from master..sysprocesses

The code can't compile.

I am using VS2005 and Sql2005 Express.

//--------------------------------------------------------------------------------------------------------------------
public static void KillProcesses(string DBConStr, string DBName)
{
try
{
using (SqlConnection conn = new SqlConnection(DBConStr))
{
conn.Open();
string sSql = "select spid from master..sysprocesses
where dbid=db_id('" + DBName + "')";
DataTable oTable = GetDataTableFromSql(sSql, DBConStr,
60);
foreach(DataRow oRow in oTable.Rows)
{
int Id = int.Parse(oRow[0].ToString());
SqlCommand cmd = new SqlCommand("kill " + Id, conn);
cmd.ExecuteNonQuery();
}
conn.Close();

}
}
catch{}
}
 
That is an old function of ours that just uses a sqldataadapter
to fill a DataTable. The key for you is to look at the
two different sql statements in strings and adapt it
to what you need to do.

--
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.masterado.net





ad said:
I have tried the codes (below) in the link:

But what doesGetDataTableFromSql function come from?

What about ths SQL:
select spid from master..sysprocesses

The code can't compile.

I am using VS2005 and Sql2005 Express.

//--------------------------------------------------------------------------------------------------------------------
public static void KillProcesses(string DBConStr, string DBName)
{
try
{
using (SqlConnection conn = new SqlConnection(DBConStr))
{
conn.Open();
string sSql = "select spid from master..sysprocesses
where dbid=db_id('" + DBName + "')";
DataTable oTable = GetDataTableFromSql(sSql, DBConStr,
60);
foreach(DataRow oRow in oTable.Rows)
{
int Id = int.Parse(oRow[0].ToString());
SqlCommand cmd = new SqlCommand("kill " + Id,
conn);
cmd.ExecuteNonQuery();
}
conn.Close();

}
}
catch{}
}






Robbe Morris said:
http://www.eggheadcafe.com/forums/ForumPost.asp?ID=46053&INTID=9
Click to expand...
Click to expand...
 
Back
Top