常用的MS SQL資料庫連線範例

using System.Data.SqlClient;

public void UpdateDBData()
{

string serverName = "10.56.70.168\\SQLEXPRESS"; //設定目標資料庫伺服器的IP，如果是要連線Express版本則在IP後面要加上'\\SQLEXPRESS'

string userID = "sa"; //登入資料庫的使用者名稱

string pwd = "1"; //登入資料庫的密碼

string dataBaseName = "DATABASE1"; //資料庫名稱

string connStr = "server=" + serverName + ";user id=" + userID + ";pwd=" + pwd + ";database=" + dataBaseName + ";MultipleActiveResultSets=True;Connection Timeout=5"; //建立連線字串

SqlConnection conn = new SqlConnection(connStr); //建立連線

string sqlStr = "Update CurrentPlayingList Set IsPlaying=true Where DivideNum=0"; //建立SQL Query

SqlCommand cmd = new SqlCommand(sqlStr, conn); //產生SqlCommand 物件

cmd.ExecuteNonQuery(); //執行SqlCommand

conn.Close(); //完成後，關閉連線

}



public void GetDBData()
{

string serverName = "10.56.70.168\\SQLEXPRESS"; //設定目標資料庫伺服器的IP，如果是要連線Express版本則在IP後面要加上'\\SQLEXPRESS'

string userID = "sa"; //登入資料庫的使用者名稱

string pwd = "1"; //登入資料庫的密碼

string dataBaseName = "DATABASE1"; //資料庫名稱

string connStr = "server=" + serverName + ";user id=" + userID + ";pwd=" + pwd + ";database=" + dataBaseName + ";MultipleActiveResultSets=True;Connection Timeout=5"; //建立連線字串

SqlConnection conn = new SqlConnection(connStr); //建立連線

string sqlStr = "Select IsPlaying From CurrentPlayingList Where DivideNum=0"; //建立SQL Query

SqlCommand cmd = new SqlCommand(sqlStr, conn); //產生SqlCommand 物件

SqlDataReader= dr = cmd.ExecuteReader(); //執行SqlCommand

string str_IsPlaying="";

while (dr.Read())
{
str_IsPlaying= dr[0].ToString();
}

dr.Close(); //完成後，關閉SqlDataReader

dr = null;

conn.Close(); //完成後，關閉連線

}

public void UpdateDBDataWithTransaction() //讓多個查詢子句一次完成，避免程式邏輯錯誤
{

string serverName = "10.56.70.168\\SQLEXPRESS"; //設定目標資料庫伺服器的IP，如果是要連線Express版本則在IP後面要加上'\\SQLEXPRESS'

string userID = "sa"; //登入資料庫的使用者名稱

string pwd = "1"; //登入資料庫的密碼

string dataBaseName = "DATABASE1"; //資料庫名稱

string connStr = "server=" + serverName + ";user id=" + userID + ";pwd=" + pwd + ";database=" + dataBaseName + ";MultipleActiveResultSets=True;Connection Timeout=5"; //建立連線字串

SqlConnection conn = new SqlConnection(connStr); //建立連線

SqlTransaction sqlTransaction = conn.BeginTransaction();

string sqlStr = "Update CurrentPlayingList Set IsPlaying=true Where DivideNum=0"; //建立SQL Query

SqlCommand cmd = new SqlCommand(sqlStr, conn,sqlTransaction ); //產生SqlCommand 物件

cmd.ExecuteNonQuery(); //執行SqlCommand ，實際上並未真正執行

sqlStr = @"Update CurrentPlayingList Set LastModifiedDateTime='2008/06/01 00:00:00' Where DivideNum=0"; //建立SQL Query

cmd = new SqlCommand(sqlStr, conn,sqlTransaction ); //產生SqlCommand 物件

cmd.ExecuteNonQuery(); //執行SqlCommand ，實際上並未真正執行

sqlTransaction.Commit(); //執行所有關於sqlTransaction下的SqlCommand

conn.Close(); //完成後，關閉連線

}

public DataSet GetDBDataUseDataSet(string tableName)
{

string serverName = "10.56.70.168\\SQLEXPRESS"; //設定目標資料庫伺服器的IP，如果是要連線Express版本則在IP後面要加上'\\SQLEXPRESS'

string userID = "sa"; //登入資料庫的使用者名稱

string pwd = "1"; //登入資料庫的密碼

string dataBaseName = "DATABASE1"; //資料庫名稱

string connStr = "server=" + serverName + ";user id=" + userID + ";pwd=" + pwd + ";database=" + dataBaseName + ";MultipleActiveResultSets=True;Connection Timeout=5"; //建立連線字串

SqlConnection conn = new SqlConnection(connStr); //建立連線

string sqlStr = "Select IsPlaying,LastModifiedDateTime From CurrentPlayingList"; //建立SQL Query

SqlDataAdapter adapter = new SqlDataAdapter(sqlStr, conn );

DataSet dataSet = new DataSet();

adapter.Fill(dataSet, tableName);

conn.Close(); //完成後，關閉連線

return dataSet ;

}