SqlServer的那些常用操作（一）

一、判斷資料庫的串連狀態：
（1）Using方法：
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        using (SqlConnection connection = new SqlConnection(""))
        {
            connection.Open();
            if (connection.State == ConnectionState.Open)
            {
                //已經開啟
            }
        }
    }
}

（2）“Try{}  Catch{}”方法
try
{
SqlConnection conn = new SqlConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;");
  conn.Open();
}
catch
{
//串連失敗
}

（3）永遠返回正確的資料連線

  private SqlConnection connection;
  public SqlConnection Connection
  {
  get
  {
  string connectionString = "server=.;database=DB;uid=SA;pwd=123456";
  if (connection == null)
  {
  connection = new SqlConnection(connectionString);
  connection.Open();//這裡就是開啟了，說明串連上了
  }
  else if (connection.State == System.Data.ConnectionState.Closed)
  {
  connection.Open(); //這裡就是開啟了，說明串連上了
  }
  else if (connection.State == System.Data.ConnectionState.Broken)
  {
  connection.Close();
  connection.Open();//這裡就是開啟了，說明串連上了
  }
  return connection; //不管什麼情況，這裡總是返回一個開啟的串連
  }
  }

二、返回本機電腦的所有資料庫名稱
USE master
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases
ORDER BY dbid
GO

三、返回指定資料庫中所有的表名稱
SELECT name FROM sys.sysobjects WHERE type='U'

四、返回指定表中的所有欄位
SELECT *  FROM syscolumns  WHERE id in( SELECT id  FROM sysobjects   WHERE (name = '" + tbname + "'))