20
1月
2016

MSSQL的一些常用命令

MSSQL

一、查询当前数据库连接数
SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame

二 关闭读取数据库连接
Dim reader As DbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) Return reader

三 清空表数据
EXEC sp_MSforeachtable "truncate table ?"


.1 If you want the connection associated with the SqlDataReader to be closed when the reader is closed, execute it with theCommandBehavior.CloseConnection enumeration.SqlDataReader sdr = sCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

.2 Explicitly close the connection when done.

sc.Close();

 

.3 Use the connection in a Using block, this guarantees that the system disposes the connection (and closes it) when the code exits the block.

Replace the whole FOR loop in the code above with this:

for (int i = 0; i < 50; i++)

{

   using (SqlConnection sc = new SqlConnection(connString))

   {

      SqlCommand sCmd = new SqlCommand("SELECT * FROM Shippers", sc);

      sc.Open();

      Console.WriteLine("Conns opened " + i.ToString());

      SqlDataReader sdr = sCmd.ExecuteReader();

      sdr.Close();

   }

}

 

I would personally recommend the use of 3.

Categories: 博客

Overall Rating (0)

0 out of 5 stars

Leave your comments

Post comment as a guest

0 Character restriction
Your text should be more than 3 characters
  • No comments found