sql,access,excel,odbc,dns 連線字串

一、SQL Server的聯接語句：
string sqlConnStr = "data source=(local);initial catalog=Northwind;persist security info=False;user id=sa;pwd=sa;workstation id=myWork;packet size=4096;Min Pool Size=0;Max Pool Size=200";
SqlConnection sqlConn = new SqlConnection(sqlConnStr);

其中：
「data source」是數據庫服務器地址，可用IP或計算機名替換。
「initial catalog」是數據庫名。
「user id」登錄數據庫是用戶名。
「pwd」登錄數據庫的密碼。

二、Access數據庫的聯接語句：
string accConnStr = "provider=microsoft.jet.oledb.4.0;data source=C:\\Program Files\\Microsoft Office\\OFFICE11\\SAMPLES\\Northwind.mdb";
OleDbConnection accConn = new OleDbConnection(accConnStr);

三、帶密碼的Access數據庫的聯接語句：
string accConnStr = "provider=microsoft.jet.oledb.4.0;data source=C:\\Program Files\\Microsoft Office\\OFFICE11\\SAMPLES\\Northwind.mdb;Jet OLEDB:Database Password=123";
OleDbConnection accConn = new OleDbConnection(accConnStr);

四、Excel的聯接語句：
string xlsConnStr = "provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("Northwind.xls")+";Extended Properties='Excel 8.0;IMEX=1'";
OleDbConnection xlsConn = new OleDbConnection(xlsConnStr);
strSql = "select top 10 * from [訂單$]";
無論excel版本是什麼，在Extended Properties裡都要寫Excel 8.0，注意空格
HDR=yes：說明excel文件的第一行是列字，而不是數據。如果第一行也是數據的話，用HDR=no。默認值為yes
IMEX=1：就是將混合型的列轉換成文本列
注意查詢語句：工作表名要用[$]框起來

五、通過DNS聯接數據庫的方法
string odbcConnStr = "DSN=odbcNorthwind;Uid=sa;Pwd=sa;";
OdbcConnection odbcConn = new OdbcConnection(odbcConnStr);

六、通過文件odbc聯接數據庫的方法
string odbcConnStr1 = "FILEDSN="+Server.MapPath("odbcfile.dsn")+";Uid=sa;Pwd=sa;";
OdbcConnection odbcConn1 = new OdbcConnection(odbcConnStr1);



以下是示例：

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Data.OleDb;

using System.Data.Odbc;



namespace test

{

///

/// conn 的摘要說明。

///

public class conn : System.Web.UI.Page

{

protected System.Web.UI.WebControls.DataGrid DataGrid2;

protected System.Web.UI.WebControls.DataGrid DataGrid3;

protected System.Web.UI.WebControls.DataGrid DataGrid4;

protected System.Web.UI.WebControls.DataGrid DataGrid5;

protected System.Web.UI.WebControls.DataGrid DataGrid6;

protected System.Web.UI.WebControls.DataGrid DataGrid1;



private void Page_Load(object sender, System.EventArgs e)

{

string strSql = "";

DataSet ds = new DataSet();



//Sql Server 數據庫的聯接方法

string sqlConnStr = "data source=(local);initial catalog=Northwind;persist security info=False;user id=sa;pwd=sa;workstation id=myWork;packet size=4096;Min Pool Size=0;Max Pool Size=200";

SqlConnection sqlConn = new SqlConnection(sqlConnStr);

strSql = "SELECT top 10 * FROM Products";

sqlConn.Open();

SqlDataAdapter sqlAdapter = new SqlDataAdapter(strSql,sqlConn);

sqlAdapter.Fill(ds,"sql");

sqlConn.Close();



this.DataGrid1.DataSource = ds.Tables["sql"].DefaultView;

this.DataGrid1.DataBind();



//Access數據庫的聯接方法（無密碼）

string accConnStr = "provider=microsoft.jet.oledb.4.0;data source=C:\\Program Files\\Microsoft Office\\OFFICE11\\SAMPLES\\Northwind.mdb";

OleDbConnection accConn = new OleDbConnection(accConnStr);

strSql = "select top 10 * from 訂單";



accConn.Open();

OleDbDataAdapter accAdapter = new OleDbDataAdapter(strSql,accConn);

ds = new DataSet();

accAdapter.Fill(ds,"acc");

accConn.Close();



this.DataGrid2.DataSource = ds.Tables["acc"].DefaultView;

this.DataGrid2.DataBind();



//Access數據庫的聯接方法（有密碼）

string accConnStr1 = "provider=microsoft.jet.oledb.4.0;data source="+Server.MapPath("Northwind.mdb")+";Jet OLEDB:Database Password=123";

OleDbConnection accConn1 = new OleDbConnection(accConnStr1);

strSql = "select top 10 * from 訂單";



accConn1.Open();

OleDbDataAdapter accAdapter1 = new OleDbDataAdapter(strSql,accConn1);

ds = new DataSet();

accAdapter1.Fill(ds,"acc1");

accConn1.Close();



this.DataGrid3.DataSource = ds.Tables["acc1"].DefaultView;

this.DataGrid3.DataBind();



//Excel的聯接方法

string xlsConnStr = "provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("Northwind.xls")+";Extended Properties='Excel 8.0;IMEX=1'";

//無論excel版本是什麼，在Extended Properties裡都要寫Excel 8.0，注意空格

//HDR=yes：說明excel文件的第一行是列字，而不是數據。如果第一行也是數據的話，用HDR=no。默認值為yes

//IMEX=1：就是將混合型的列轉換成文本列

OleDbConnection xlsConn = new OleDbConnection(xlsConnStr);

//注意查詢語句：工作表名要用[$]框起來

strSql = "select top 10 * from [訂單$]";



xlsConn.Open();

OleDbDataAdapter xlsAdapter = new OleDbDataAdapter(strSql,xlsConn);

ds = new DataSet();

xlsAdapter.Fill(ds,"xls");

xlsConn.Close();



this.DataGrid4.DataSource = ds.Tables["xls"].DefaultView;

this.DataGrid4.DataBind();





//通過DNS聯接數據庫的方法

string odbcConnStr = "DSN=odbcNorthwind;Uid=sa;Pwd=sa;";

OdbcConnection odbcConn = new OdbcConnection(odbcConnStr);

strSql = "SELECT top 10 * FROM Products";



odbcConn.Open();

OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(strSql,odbcConn);

ds = new DataSet();

odbcAdapter.Fill(ds,"odbc");

odbcConn.Close();



this.DataGrid5.DataSource = ds.Tables["odbc"].DefaultView;

this.DataGrid5.DataBind();





//通過文件odbc聯接數據庫的方法

string odbcConnStr1 = "FILEDSN="+Server.MapPath("odbcfile.dsn")+";Uid=sa;Pwd=sa;";

OdbcConnection odbcConn1 = new OdbcConnection(odbcConnStr1);

strSql = "SELECT top 10 * FROM Products";



odbcConn1.Open();

OdbcDataAdapter odbcAdapter1 = new OdbcDataAdapter(strSql,odbcConn1);

ds = new DataSet();

odbcAdapter1.Fill(ds,"odbc1");

odbcConn1.Close();



this.DataGrid6.DataSource = ds.Tables["odbc1"].DefaultView;

this.DataGrid6.DataBind();

}
}
}