1 / 13

2. Data Connection ( 資料連結 )

2. Data Connection ( 資料連結 ). Name Space ( 命名空間）. 不同資料庫的連結有不同的命名空間 建立資料連結前需要先匯入命名空間 SQL server: System.Data and System.Data.SqlClient ODBC: System.Data and System.DataOdbc OLE: System.Data and System.Data.OleDb Oracle: System.Data and System.Data.OracleClient. Example ( 範例 ).

flint
Download Presentation

2. Data Connection ( 資料連結 )

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 2. Data Connection (資料連結)

  2. Name Space (命名空間） • 不同資料庫的連結有不同的命名空間 • 建立資料連結前需要先匯入命名空間 • SQL server: • System.Data and System.Data.SqlClient • ODBC: • System.Data and System.DataOdbc • OLE: • System.Data and System.Data.OleDb • Oracle: • System.Data and System.Data.OracleClient

  3. Example (範例) \Ch17\Connection_01_Access.aspx 01:<%@ Page Language="VB" %> 02:<%@ Import Namespace="System.Data" %> 03:<%@ Import Namespace="System.Data.OleDb" %> 04:<script runat="server"> 05: Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) 06: Dim objConn As New OleDbConnection() 07: objConn.ConnectionString = "Provider=Microsoft.Jet. OLEDB.4.0;" & "Data Source=" & _ Server.MapPath("~/App_Data/Friend.mdb") 08: objConn.Open() 09: objConn.Close() 10: End Sub 11:</script>

  4. Establish connection (建立資料連結） • 兩種語法，以 OLE DB連接法為例 • Dim objConn As New OleDbConnection() • Dim objConn As New OleDbConnection(connectionString) • ConnectionString

  5. Example (範例) – OLE DB <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script runat="server"> Sub Page_Load(sender As Object, e As System.EventArgs) Dim objConn As New OleDbConnection() objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath("~/App_Data/Friend.mdb") objConn.Open() objConn.Close() End Sub </script>

  6. Example (範例) – SQL Server <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat="server"> Sub Page_Load(sender As Object, e As System.EventArgs) Dim objConn As New SqlConnection() objConn.ConnectionString = "Data Source=localhost;" & _ "Integrated Security=SSPI;Initial Catalog=Friend" objConn.Open() objConn.Close() End Sub </script>

  7. web.config • 連結資料庫100次,要寫100次連結程式? • 把連結方式寫在web.config即可 • Web.config (課本CH11.7) • 在程式所在的資料夾,放入web.config,程式中所需的資訊就可以從web.config中抓取

  8. Example (範例) – web.config <?xml version="1.0" encoding="utf-8"?> <configuration> <connectionStrings> <add name="OLEDB_Friend" providerName="System.Data. OleDb" connectionString="Provider=Microsoft.Jet.OLEDB. 4.0;Data Source=C:\Inetpub\wwwroot\App_Data\ Friend.mdb" /> <add name="OLEDB_Price" providerName="System.Data. OleDb" connectionString="Provider=Microsoft.Jet.OLEDB. 4.0;Data Source=C:\Inetpub\wwwroot\App_Data\ Price.mdb" /> </connectionStrings> </configuration>

  9. Example (範例) <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script runat="server"> Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) '取得 web.config 組態檔的資料連接設定 Dim setting As ConnectionStringSettings = _ConfigurationManager.ConnectionStrings("OLEDB_Friend") '建立 Connection 物件 Dim objConn As New OleDbConnection() objConn.ConnectionString = setting.ConnectionString '開啟資料連接 objConn.Open() '關閉資料連接 objConn.Close() End Sub </script>

  10. Tips (提示) • 如何讓範例在VS 2005環境下執行 • Copy範例檔到d: (eg: d:\ch17) • Copy web.config到 範例檔目錄 (eg: copy web.config  d:\ch17) • Copy 資料庫到d: (eg: d:\app_data) • 修改web.config的資料庫聯結 (eg: c:\Inetpub\wwwroot  d:\app_data)

  11. Command • Command物件 • 用來執行SQL 命令 • Insert, update, select, delete • 宣告方式 (以OLEDB連結法為例) • Dim objCmd As New OleDbCommand() • Dim objCmd As New OleDbCommand(sql) • Dim objCmd As New OleDbCommand(sql, connection) • Dim objCmd As New OleDbCommand(sql, connection, transaction)

  12. Example (範例) <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script runat="server"> Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) '取得 web.config 組態檔的資料連接設定 Dim setting As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("OLEDB_Friend") '建立資料連接 Dim objConn As New OleDbConnection() objConn.ConnectionString = setting.ConnectionString '開啟資料連接 objConn.Open() '使用 Command 物件執行 SQL 命令 Dim strSQL As String = "Delete From 擇友俱樂部 Where 姓名='幻影小雨貓'" Dim objCmd As New OleDbCommand(strSQL, objConn) objCmd.ExecuteNonQuery() '關閉資料連接 objConn.Close() End Sub </script>

  13. Command 物件的屬性 • CommandText="…" • 取得想要執行的SQL 命令 • Eg: objCmd.CommandText=“delete from 擇友俱樂部”where 姓名=‘xxx’” • CommandTimeout=n • 設定逾期時間 • CommandType="{StoredProcedure|TableDirect|Text}" • 設定commandText所代表的意義 • Connection=… • 設定執行所需的資料連結 • Parameters • 傳遞參數 • Transaction=… • 設定”交易”

More Related