◎ 資料庫連接

 

● 連接 Access

using System.Data.OleDb

String strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\TestDbData.mdb;";
OleDbConnection AccessCon = new OleDbConnection(strCon);
AccessCon.Open();

...

 AccessCon.Close()           //關閉對資料庫的連接

using System.Data.OleDb

//OleDbConnectionStringBuilder 類別
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder["Provider"] = "Microsoft.Jet.OLEDB.4.0";
builder["Data Source"] = "C:\\TestDbData.mdb";
builder["User Id"] = "Admin;NewValue=Bad";

...

builder.Cear();

 

● 連接 Excel

using System.Data.OleDb

String strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\TestDbData.xls;ExtendedProperties=Excel 8.0;"
OleDbConnection ExcelCon = new OleDbConnection(strCon);
ExcelCon.Open();

...

 ExcelCon.Dispose();                //釋放 Component 所使用的資源

using System.Data.OleDb

//OleDbConnectionStringBuilder 類別
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.ConnectionString = @"Data Source=C:\Sample.mdb";
builder.Add("Provider", "Microsoft.Jet.OLEDB.4.0");
builder.Add("Extended Properties", "Excel 8.0");

 

● 連接 SQL Server

using System.Data.SqlClient

//用 Windows 身份驗證
String strCon = "Data Source=(local);Initial Catalog=TestDb;Integrated Security=SSPI;";

//用 SQL Server 身份驗證
String strCon = @"Data Source=.\SQLExpress;Database=TestDb;Uid=sa;Pwd=1234;";

SqlConnection SqlSvrCon = new SqlConnection(strCon);
SqlSvrCon.Open();

...

SqlSvrCon.Close();

using System.Data.SqlClient

//SqlConnectionStringBuilder 類別
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder["Data Source"] = "(local)";
builder["integrated Security"] = true;
builder["Initial Catalog"] = "TestDb;NewValue=Bad";

SqlConnection SqlSvrCon = new SqlConnection(builder.ConnectionString);
SqlSvrCon.Open();

...

SqlSvrCon.Close();

 

※ 列出區域網路中所有的SQL伺服器 (SqlDataSourceEnumerator 類別)

using System.Data.Sql;                         //引用 SqlDataSourceEnumerator 類別
using System.Data;                               //引用 ADO.NET 基礎物件(DataTable)

SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
DataTable table = instance.GetDataSources();

foreach (DataRow row in table.Rows)
{
    foreach (DataColumn col in table.Columns)
    {
        lstSqlSvr.Items.Add(col.ColumnName + " = " + row[col]);
    }
}

※ 呼叫 GetDataSources 方法所傳回之伺服器相關資訊

資料行
說明
ServerName
伺服器名稱。
InstanceName
伺服器執行個體的名稱。如果伺服器做為預設執行個體執行,則此處為空白。
IsClustered
表示伺服器是否為叢集的一部分。
Version
伺服器版本 (SQL Server 2000 為 8.00.x,SQL Server 2005 為 9.00.x)。

請參考:[msdn] 列舉 SQL Server 執行個體

 

※ 列出某一個 SQL Server 執行個體中的各個資料庫

using (SqlConnection SqlSvrCon = new SqlConnection(strCon))
{
 

    SqlSvrCon.Open();
    SqlDataAdapter
da=new SqlDataAdapter("select name from sysdatabases",SqlSvrCon);
    DataTable dt=new DataTable("sysdatabases");
    da.Fill(dt);
    lstData.DataSource=dt;
    lstData.DisplayMember = "name";
    lstData.ValueMember = "name";

}

 

◎ 卸載 SQL Server 資料庫

...//連接資料庫

string strSql = "exec sp_detach_db @dbname=TestDB";
string strExc = "alter database TestDB set single_user with rollback immediate "+strSql;
//請先將資料庫設為 SINGLE_USER 模式以取得獨佔存取權,才能進行卸離

SqlCommand cmd=new SqlCommand();
cmd.Connection=SqlSvrCon;
cmd.CommandText = strExc;
cmd.ExecuteNonQuery();

Transact-SQL 語法:

sp_detach_db [ @dbname = ] 'dbname' [ , [ @skipchecks = ] 'skipchecks' ]

 

◎ 附加 SQL Server 資料庫

using System.Text;                       //引用 StringBuilder 類別

...//連接資料庫

StringBuilder sb = new StringBuilder();
sb.Append("sp_attach_single_file_db @dbname=TestDB,");
sb.Append("@physname=\"C:\\TestDb.MDF\"");
SqlCommand cmd=new SqlCommand();
cmd.Connection=SqlSvrCon;
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();

Transact-SQL 語法:

sp_attach_single_file_db [ @dbname = ] 'dbname] , [@physname = ] 'physname'

using System.Data.SqlClient

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder["Data Source"] = "(local)";
builder.IntegratedSecurity = true;            
builder.AttachDBFilename = @"E:\Linpo\DB\TestDB.MDF";
builder.UserInstance = false;
builder.InitialCatalog = "TestDB";

 

 

創作者介紹
創作者 tsuozoe 的頭像
tsuozoe

隨便寫寫的新天地

tsuozoe 發表在 痞客邦 留言(0) 人氣()