萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

ASP.NET 資料庫連接完整實作指南

一、使用 ODBC 連接 Access 資料庫 (VB.NET)

Sub LoadCommandList()
    Dim objConn As New Odbc.OdbcConnection
    Dim objCmd As New Odbc.OdbcCommand
    Dim objDataReader As Odbc.OdbcDataReader
    
    CommandList.Items.Clear()
    
    objConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & Application.StartupPath & "database.mdb"
    Debug.Print(Application.StartupPath & "database.mdb")
    
    objConn.Open() ' 連接資料庫
    objCmd.Connection = objConn
    objCmd.CommandText = "select * from Commands"
    objDataReader = objCmd.ExecuteReader() ' 執行 SQL 語句
    
    While objDataReader.Read() ' 讀取資料
        CommandList.Items.Add(objDataReader.GetString(0) & "," & objDataReader.GetString(1))
    End While
    
    objConn.Close()
End Sub

二、ASP.NET 連接 SQL Server 2000 完整範例

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
    Dim myConnection As SqlConnection
    Dim myCommand As SqlCommand
    Dim ds As DataSet
    
    ' 1. 連接資料庫
    myConnection = New SqlConnection("server=localhost;database=Pubs;uid=ueytjdf;pwd=doekdf")
    myConnection.Open()
    la1.Text = "Connection Opened!"
    
    ' 2. 建立新資料表
    myCommand = New SqlCommand("CREATE TABLE [test] ([id] [int] IDENTITY (1,1) NOT NULL, [name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL, [sex] [char] (10) COLLATE Chinese_PRC_CI_AS NULL)", myConnection)
    myCommand.ExecuteNonQuery()
    la2.Text = "New table created!"
    
    ' 3. 新增記錄
    myCommand = New SqlCommand("INSERT INTO [test] (name, sex) VALUES ('黃志文', '男')", myConnection)
    myCommand.ExecuteNonQuery()
    la3.Text = "New Record Inserted!"
    
    ' 4. 更新資料
    myCommand = New SqlCommand("UPDATE [test] SET name='Smith' WHERE name='李明'", myConnection)
    myCommand.ExecuteNonQuery()
    la4.Text = "Record Updated!"
    
    ' 5. 刪除資料
    myCommand = New SqlCommand("DELETE FROM [test] WHERE name='Smith'", myConnection)
    myCommand.ExecuteNonQuery()
    la5.Text = "Record Deleted!"
    
    ' 6. 使用 DataGrid 顯示資料
    myCommand = New SqlCommand("SELECT * FROM [test]", myConnection)
    MyDataGrid.DataSource = myCommand.ExecuteReader()
    MyDataGrid.DataBind()
End Sub
</script>
<html>
<body>
<asp:Label ID="la1" runat="server" /><br/>
<asp:Label ID="la2" runat="server" /><br/>
<asp:Label ID="la3" runat="server" /><br/>
<asp:Label ID="la4" runat="server" /><br/>
<asp:Label ID="la5" runat="server" /><br/>
<asp:DataGrid ID="MyDataGrid" runat="server"
    BorderColor="black" BorderWidth="1" GridLines="Both"
    CellPadding="3" CellSpacing="0"
    Font-Name="Verdana" Font-Size="10pt"
    HeaderStyle-BackColor="#aaaadd"
    AlternatingItemStyle-BackColor="#eeeeee">
</asp:DataGrid>
</body>
</html>

三、ASP.NET 連接 Access 資料庫範例 (VB.NET)

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand

Sub Page_Load(sender As Object, e As EventArgs)
    ' 1. 連接資料庫
    Dim dbName As String = Server.MapPath("authors.mdb")
    myConnection = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & dbName)
    myConnection.Open()
    la1.Text = "Connection Opened!"
    
    ' 2. 新增記錄
    myCommand = New OleDbCommand("INSERT INTO Authors (Authors, country) VALUES ('Simson', 'usa')", myConnection)
    myCommand.ExecuteNonQuery()
    la2.Text = "New Record Inserted!"
    
    ' 3. 更新資料
    myCommand = New OleDbCommand("UPDATE Authors SET Authors='Bennett' WHERE Authors='Simson'", myConnection)
    myCommand.ExecuteNonQuery()
    la3.Text = "Record Updated!"
    
    ' 4. 刪除資料
    myCommand = New OleDbCommand("DELETE FROM Authors WHERE Authors='David'", myConnection)
    myCommand.ExecuteNonQuery()
    la4.Text = "Record Deleted!"
    
    ' 5. 使用 DataGrid 顯示資料
    myCommand = New OleDbCommand("SELECT * FROM Authors", myConnection)
    MyDataGrid.DataSource = myCommand.ExecuteReader()
    MyDataGrid.DataBind()
End Sub
</script>
<html>
<body>
<asp:Label ID="la1" runat="server" /><br/>
<asp:Label ID="la2" runat="server" /><br/>
<asp:Label ID="la3" runat="server" /><br/>
<asp:Label ID="la4" runat="server" /><br/>
<asp:DataGrid ID="MyDataGrid" runat="server"
    BorderColor="black" BorderWidth="1" GridLines="Both"
    CellPadding="3" CellSpacing="0"
    Font-Name="Verdana" Font-Size="10pt"
    HeaderStyle-BackColor="#aaaadd"
    AlternatingItemStyle-BackColor="#eeeeee">
</asp:DataGrid>
</body>
</html>

四、C# 連接 Access 資料庫

using System.Data;
using System.Data.OleDb;

// ...

string strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
strConnection += @"Data Source=C:\BegASPNET\Northwind.mdb";

OleDbConnection objConnection = new OleDbConnection(strConnection);
// ...
objConnection.Open();
objConnection.Close();

程式碼解析

  • 命名空間匯入:連接 Access 需匯入 System.Data.OleDb,這是必備步驟。
  • 連接字串:Provider=Microsoft.Jet.OleDb.4.0 指定使用 Microsoft Jet 引擎(Access 核心);Data Source 指定資料庫檔案路徑,標準格式為 Data Source=MyDrive:MyPath\MyFile.MDB。
  • @ 符號:防止字串中的反斜線被解析為跳脫字元。
  • 相對路徑技巧:若資料庫與目前檔案同目錄,可用 strConnection += "Data Source=" + MapPath("Northwind.mdb") 簡化路徑。
  • 分號分隔:連接字串參數間必須以分號分隔。
copyright © 萬盛學電腦網 all rights reserved