Access 사이트 맵 공급자 예제
업데이트: 2007년 11월
Access 기반 사이트 맵 공급자 전체를 보여 줍니다.
예제
설명
다음 코드 예제는 StaticSiteMapProvider 클래스를 확장하여 Microsoft Access를 사이트 맵 공급자로 사용하는 방법을 보여 줍니다. 사이트 맵 공급자는 .NET Framework Data Provider for OLE DB를 사용하여 Access 데이터베이스에 연결합니다.
코드
Imports System
Imports System.Collections
Imports System.Collections.Specialized
Imports System.Data
Imports System.Data.OleDb
Imports System.Security.Permissions
Imports System.Web
Namespace Samples.AspNet.VB.Controls
' An extremely simple AccessSiteMapProvider that only supports a
' site map node hierarchy one level deep.
<AspNetHostingPermission(SecurityAction.Demand, Level:=AspNetHostingPermissionLevel.Minimal)> _
Public Class AccessSiteMapProvider
Inherits StaticSiteMapProvider
Private aRootNode As SiteMapNode = Nothing
Private accessConnection As OleDbConnection = Nothing
' This string is case sensitive.
Private AccessConnectionStringName As String = "accessSiteMapConnectionString"
' Implement a default constructor.
Public Sub New()
End Sub 'New
' Some basic state to help track the initialization state of the provider.
Private initialized As Boolean = False
Public Overridable ReadOnly Property IsInitialized() As Boolean
Get
Return initialized
End Get
End Property
' Return the root node of the current site map.
Public Overrides ReadOnly Property RootNode() As SiteMapNode
Get
Return BuildSiteMap()
End Get
End Property
Protected Overrides Function GetRootNodeCore() As SiteMapNode
Return RootNode
End Function
' Initialize is used to initialize the properties and any state that the
' AccessProvider holds, but is not used to build the site map.
' The site map is built when the BuildSiteMap method is called.
Public Overrides Sub Initialize(ByVal name As String, ByVal attributes As NameValueCollection)
If IsInitialized Then
Return
End If
MyBase.Initialize(name, attributes)
' Create and test the connection to the Microsoft Access database.
' Retrieve the Value of the Access connection string from the
' attributes NameValueCollection.
Dim connectionString As String = attributes(AccessConnectionStringName)
If Nothing = connectionString OrElse connectionString.Length = 0 Then
Throw New Exception("The connection string was not found.")
Else
accessConnection = New OleDbConnection(connectionString)
End If
initialized = True
End Sub 'Initialize
' SiteMapProvider and StaticSiteMapProvider methods that this derived class must override.
'
' Clean up any collections or other state that an instance of this may hold.
Protected Overrides Sub Clear()
SyncLock Me
aRootNode = Nothing
MyBase.Clear()
End SyncLock
End Sub 'Clear
' Build an in-memory representation from persistent
' storage, and return the root node of the site map.
Public Overrides Function BuildSiteMap() As SiteMapNode
' Since the SiteMap class is static, make sure that it is
' not modified while the site map is built.
SyncLock Me
' If there is no initialization, this method is being
' called out of order.
If Not IsInitialized Then
Throw New Exception("BuildSiteMap called incorrectly.")
End If
' If there is no root node, then there is no site map.
If aRootNode Is Nothing Then
' Start with a clean slate
Clear()
' Select the root node of the site map from Microsoft Access.
Dim rootNodeId As Integer = -1
If accessConnection.State = ConnectionState.Closed Then
accessConnection.Open()
End If
Dim rootNodeCommand As New OleDbCommand("SELECT nodeid, url, name FROM SiteMap WHERE parentnodeid IS NULL", accessConnection)
Dim rootNodeReader As OleDbDataReader = rootNodeCommand.ExecuteReader()
If rootNodeReader.HasRows Then
rootNodeReader.Read()
rootNodeId = rootNodeReader.GetInt32(0)
' Create a SiteMapNode that references the current StaticSiteMapProvider.
aRootNode = New SiteMapNode(Me, rootNodeId.ToString(), rootNodeReader.GetString(1), rootNodeReader.GetString(2))
Else
Return Nothing
End If
rootNodeReader.Close()
' Select the child nodes of the root node.
Dim childNodesCommand As New OleDbCommand("SELECT nodeid, url, name FROM SiteMap WHERE parentnodeid = ?", accessConnection)
Dim rootParam As New OleDbParameter("parentid", OleDbType.Integer)
rootParam.Value = rootNodeId
childNodesCommand.Parameters.Add(rootParam)
Dim childNodesReader As OleDbDataReader = childNodesCommand.ExecuteReader()
If childNodesReader.HasRows Then
Dim childNode As SiteMapNode = Nothing
While childNodesReader.Read()
childNode = New SiteMapNode(Me, _
childNodesReader.GetInt32(0).ToString(), _
childNodesReader.GetString(1), _
childNodesReader.GetString(2))
' Use the SiteMapNode AddNode method to add
' the SiteMapNode to the ChildNodes collection.
AddNode(childNode, aRootNode)
End While
End If
childNodesReader.Close()
accessConnection.Close()
End If
Return aRootNode
End SyncLock
End Function 'BuildSiteMap
End Class 'AccessSiteMapProvider
End Namespace
namespace Samples.AspNet.CS.Controls {
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.Security.Permissions;
using System.Web;
/// An extremely simple AccessSiteMapProvider that only supports a
/// site map node hierarchy 1 level deep.
[AspNetHostingPermission(SecurityAction.Demand, Level=AspNetHostingPermissionLevel.Minimal)]
public class AccessSiteMapProvider : StaticSiteMapProvider
{
private SiteMapNode rootNode = null;
private OleDbConnection accessConnection = null;
// This string is case sensitive.
private string AccessConnectionStringName = "accessSiteMapConnectionString";
// Implement a default constructor.
public AccessSiteMapProvider () { }
// Some basic state to help track the initialization state of the provider.
private bool initialized = false;
public virtual bool IsInitialized {
get {
return initialized;
}
}
// Return the root node of the current site map.
public override SiteMapNode RootNode {
get {
SiteMapNode temp = null;
temp = BuildSiteMap();
return temp;
}
}
protected override SiteMapNode GetRootNodeCore() {
return RootNode;
}
// Initialize is used to initialize the properties and any state that the
// AccessProvider holds, but is not used to build the site map.
// The site map is built when the BuildSiteMap method is called.
public override void Initialize(string name, NameValueCollection attributes) {
if (IsInitialized)
return;
base.Initialize(name, attributes);
// Create and test the connection to the Microsoft Access database.
// Retrieve the Value of the Access connection string from the
// attributes NameValueCollection.
string connectionString = attributes[AccessConnectionStringName];
if (null == connectionString || connectionString.Length == 0)
throw new Exception ("The connection string was not found.");
else
accessConnection = new OleDbConnection(connectionString);
initialized = true;
}
///
/// SiteMapProvider and StaticSiteMapProvider methods that this derived class must override.
///
// Clean up any collections or other state that an instance of this may hold.
protected override void Clear() {
lock (this) {
rootNode = null;
base.Clear();
}
}
// Build an in-memory representation from persistent
// storage, and return the root node of the site map.
public override SiteMapNode BuildSiteMap() {
// Since the SiteMap class is static, make sure that it is
// not modified while the site map is built.
lock(this) {
// If there is no initialization, this method is being
// called out of order.
if (! IsInitialized) {
throw new Exception("BuildSiteMap called incorrectly.");
}
// If there is no root node, then there is no site map.
if (null == rootNode) {
// Start with a clean slate
Clear();
// Select the root node of the site map from Microsoft Access.
int rootNodeId = -1;
if (accessConnection.State == ConnectionState.Closed)
accessConnection.Open();
OleDbCommand rootNodeCommand =
new OleDbCommand("SELECT nodeid, url, name FROM SiteMap WHERE parentnodeid IS NULL",
accessConnection);
OleDbDataReader rootNodeReader = rootNodeCommand.ExecuteReader();
if(rootNodeReader.HasRows) {
rootNodeReader.Read();
rootNodeId = rootNodeReader.GetInt32(0);
// Create a SiteMapNode that references the current StaticSiteMapProvider.
rootNode = new SiteMapNode(this,
rootNodeId.ToString(),
rootNodeReader.GetString(1),
rootNodeReader.GetString(2));
}
else return null;
rootNodeReader.Close();
// Select the child nodes of the root node.
OleDbCommand childNodesCommand =
new OleDbCommand("SELECT nodeid, url, name FROM SiteMap WHERE parentnodeid = ?",
accessConnection);
OleDbParameter rootParam = new OleDbParameter("parentid", OleDbType.Integer);
rootParam.Value = rootNodeId;
childNodesCommand.Parameters.Add(rootParam);
OleDbDataReader childNodesReader = childNodesCommand.ExecuteReader();
if (childNodesReader.HasRows) {
SiteMapNode childNode = null;
while(childNodesReader.Read()) {
childNode = new SiteMapNode(this,
childNodesReader.GetInt32(0).ToString(),
childNodesReader.GetString(1),
childNodesReader.GetString(2));
// Use the SiteMapNode AddNode method to add
// the SiteMapNode to the ChildNodes collection.
AddNode(childNode, rootNode);
}
}
childNodesReader.Close();
accessConnection.Close();
}
return rootNode;
}
}
}
}
#using <System.Data.dll>
#using <System.Transactions.dll>
#using <System.EnterpriseServices.dll>
#using <System.dll>
#using <System.Web.dll>
#using <System.Configuration.dll>
using namespace System;
using namespace System::Collections;
using namespace System::Collections::Specialized;
using namespace System::Configuration;
using namespace System::Data;
using namespace System::Data::OleDb;
using namespace System::Security::Permissions;
using namespace System::Web;
/// An extremely simple AccessSiteMapProvider that only supports a
/// site map node hierarchy 1 level deep.
[AspNetHostingPermission(SecurityAction::Demand,Level=AspNetHostingPermissionLevel::Minimal)]
public ref class AccessSiteMapProvider: public StaticSiteMapProvider
{
private:
SiteMapNode ^ rootNode;
OleDbConnection^ accessConnection;
// This string is case sensitive.
String^ AccessConnectionStringName;
public:
// Implement a default constructor.
AccessSiteMapProvider()
{
initialized = false;
AccessConnectionStringName = "accessSiteMapConnectionString";
}
private:
// Some basic state to help track the initialization state of the provider.
bool initialized;
public:
property bool IsInitialized
{
virtual bool get()
{
return initialized;
}
}
property SiteMapNode ^ RootNode
{
// Return the root node of the current site map.
virtual SiteMapNode ^ get() override
{
SiteMapNode ^ temp = nullptr;
temp = BuildSiteMap();
return temp;
}
}
protected:
virtual SiteMapNode ^ GetRootNodeCore() override
{
return RootNode;
}
public:
// Initialize is used to initialize the properties and any state that the
// AccessProvider holds, but is not used to build the site map.
// The site map is built when the BuildSiteMap method is called.
virtual void Initialize( String^ name, NameValueCollection^ attributes ) override
{
if ( IsInitialized )
return;
StaticSiteMapProvider::Initialize( name, attributes );
// Create and test the connection to the Microsoft Access database.
// Retrieve the Value of the Access connection string from the
// attributes NameValueCollection.
String^ connectionString = attributes[ AccessConnectionStringName ];
if ( nullptr == connectionString || connectionString->Length == 0 )
throw gcnew Exception( "The connection string was not found." );
else
accessConnection = gcnew OleDbConnection( connectionString );
initialized = true;
}
protected:
///
/// SiteMapProvider and StaticSiteMapProvider methods that this derived class must override.
///
// Clean up any collections or other state that an instance of this may hold.
virtual void Clear() override
{
System::Threading::Monitor::Enter( this );
try
{
rootNode = nullptr;
StaticSiteMapProvider::Clear();
}
finally
{
System::Threading::Monitor::Exit( this );
}
}
public:
// Build an in-memory representation from persistent
// storage, and return the root node of the site map.
virtual SiteMapNode ^ BuildSiteMap() override
{
// Since the SiteMap class is static, make sure that it is
// not modified while the site map is built.
System::Threading::Monitor::Enter( this );
try
{
// If there is no initialization, this method is being
// called out of order.
if ( !IsInitialized )
{
throw gcnew Exception( "BuildSiteMap called incorrectly." );
}
// If there is no root node, then there is no site map.
if ( nullptr == rootNode )
{
// Start with a clean slate
Clear();
// Select the root node of the site map from Microsoft Access.
int rootNodeId = -1;
if ( accessConnection->State == ConnectionState::Closed )
accessConnection->Open();
OleDbCommand^ rootNodeCommand = gcnew OleDbCommand
("SELECT nodeid, url, name FROM SiteMap WHERE parentnodeid IS NULL", accessConnection);
OleDbDataReader^ rootNodeReader = rootNodeCommand->ExecuteReader();
if ( rootNodeReader->HasRows )
{
rootNodeReader->Read();
rootNodeId = rootNodeReader->GetInt32( 0 );
// Create a SiteMapNode that references the current StaticSiteMapProvider.
rootNode = gcnew SiteMapNode(this, rootNodeId.ToString(),
rootNodeReader->GetString( 1 ),rootNodeReader->GetString( 2 ));
}
else
return nullptr;
rootNodeReader->Close();
// Select the child nodes of the root node.
OleDbCommand^ childNodesCommand = gcnew OleDbCommand
("SELECT nodeid, url, name FROM SiteMap WHERE parentnodeid = ?", accessConnection);
OleDbParameter^ rootParam = gcnew OleDbParameter( "parentid", OleDbType::Integer);
rootParam->Value = rootNodeId;
childNodesCommand->Parameters->Add( rootParam );
OleDbDataReader^ childNodesReader = childNodesCommand->ExecuteReader();
if ( childNodesReader->HasRows )
{
SiteMapNode ^ childNode = nullptr;
while ( childNodesReader->Read() )
{
childNode = gcnew SiteMapNode( this,
System::Convert::ToString(childNodesReader->GetInt32( 0 )),
childNodesReader->GetString( 1 ),
childNodesReader->GetString( 2 )
);
// Use the SiteMapNode AddNode method to add
// the SiteMapNode to the ChildNodes collection.
AddNode( childNode, rootNode );
}
}
childNodesReader->Close();
accessConnection->Close();
}
return rootNode;
}
finally
{
System::Threading::Monitor::Exit( this );
}
}
};
package Samples.AspNet.JSL;
import System.*;
import System.Collections.*;
import System.Collections.Specialized.*;
import System.Data.*;
import System.Data.OleDb.*;
import System.Web.*;
import System.Configuration.*;
/// An extremely simple AccessSiteMapProvider that only supports a
/// site map node hierarchy 1 level deep.
public class AccessSiteMapProvider extends StaticSiteMapProvider
{
private SiteMapNode rootNode = null;
private OleDbConnection accessConnection = null;
// This string is case sensitive.
public String accessConnectionStringName = "accessSiteMapConnectionString";
// Implement a default constructor.
public AccessSiteMapProvider()
{
} //AccessSiteMapProvider
// Some basic state to help track the initialization state of the provider.
private boolean initialized = false;
/** @property
*/
public boolean get_IsInitialized()
{
return initialized;
} //get_IsInitized.
// Return the root node of the current site map.
/** @property
*/
public SiteMapNode get_RootNode() throws Exception
{
SiteMapNode temp = null;
temp = BuildSiteMap();
return temp;
} //get_RootNode
// Initialize is used to initialize the properties and any state that the
// AccessProvider holds, but is not used to build the site map.
// The site map is built when the BuildSiteMap method is called.
public void Initialize(String name, NameValueCollection attributes)
throws Exception
{
if (get_IsInitialized())
{
return;
}
super.Initialize(name, attributes);
// Create and test the connection to the Microsoft Access database.
// Retrieve the Value of the Access connection string from the
// attributes NameValueCollection.
String connectionString = attributes.get_Item(
accessConnectionStringName);
if (null == connectionString || connectionString.get_Length() == 0)
{
throw new Exception("The connection string was not found.");
}
else
{
accessConnection = new OleDbConnection(connectionString);
}
initialized = true;
} //Initialize
///
/// SiteMapProvider and StaticSiteMapProvider methods that this
/// derived class must override.
///
// Clean up any collections or other state that an instance of this may hold.
protected void Clear()
{
synchronized (this)
{
rootNode = null;
super.Clear();
}
} //Clear
// Build an in-memory representation from persistent
// storage, and return the root node of the site map.
public SiteMapNode BuildSiteMap() throws Exception
{
// Since the SiteMap class is static, make sure that it is
// not modified while the site map is built.
synchronized (this)
{
// If there is no initialization, this method is being
// called out of order.
if (!get_IsInitialized())
{
throw new Exception("BuildSiteMap called incorrectly.");
}
// If there is no root node, then there is no site map.
if (null == rootNode)
{
// Start with a clean slate
Clear();
// Select the root node of the site map from Microsoft Access.
int rootNodeId = -1;
if (accessConnection.get_State().Equals(ConnectionState.Closed))
{
accessConnection.Open();
}
OleDbCommand rootNodeCommand = new OleDbCommand(
"SELECT nodeid, url, name FROM SiteMap WHERE "
+ "parentnodeid IS NULL", accessConnection);
OleDbDataReader rootNodeReader = rootNodeCommand.ExecuteReader();
if (rootNodeReader.get_HasRows())
{
rootNodeReader.Read();
rootNodeId = rootNodeReader.GetInt32(0);
// Create a SiteMapNode that references the current
// StaticSiteMapProvider.
rootNode = new SiteMapNode(this,
((Int32)rootNodeId).ToString(), rootNodeReader.
GetString(1), rootNodeReader.GetString(2));
}
else
{
return null;
}
rootNodeReader.Close();
// Select the child nodes of the root node.
OleDbCommand childNodesCommand = new OleDbCommand(
"SELECT nodeid, url, name FROM SiteMap WHERE "
+ "parentnodeid = ?", accessConnection);
OleDbParameter rootParam = new OleDbParameter("parentid",
OleDbType.Integer);
rootParam.set_Value((Int32)rootNodeId);
childNodesCommand.get_Parameters().Add(rootParam);
OleDbDataReader childNodesReader = childNodesCommand.
ExecuteReader();
if (childNodesReader.get_HasRows())
{
SiteMapNode childNode = null;
while (childNodesReader.Read())
{
childNode = new SiteMapNode(this,
Convert.ToString(childNodesReader.GetInt32(0)),
childNodesReader.GetString(1),
childNodesReader.GetString(2));
// Use the SiteMapNode AddNode method to add
// the SiteMapNode to the ChildNodes collection.
AddNode(childNode, rootNode);
}
}
childNodesReader.Close();
accessConnection.Close();
}
return rootNode;
}
} //BuildSiteMap
protected SiteMapNode GetRootNodeCore()
{
return null;
} //GetRootNodeCore
} //AccessSiteMapProvider
주석
이전 코드 예제에서 사이트 맵 데이터 저장소로 사용된 Access 테이블을 만들려면 새 Access 데이터베이스나 기존 Access 데이터베이스에서 다음과 같은 데이터 정의 쿼리를 실행한 다음 Sitemap.mdb라는 파일 이름으로 Access 데이터베이스를 저장합니다.
CREATE TABLE SiteMap
(
URL Text (255) UNIQUE,
NAME Text (255) NOT NULL,
PARENTNODEID Int32,
CONSTRAINT NODEID PRIMARY KEY (URL, NAME)
)
다음과 같이 웹 사이트에 있는 파일이나 사용자가 만들 수 있는 파일 등의 데이터로 테이블을 채웁니다. 다음 표는 예제 파일 목록을 보여 줍니다.
NODEID |
URL |
NAME |
PARENTNODEID |
|---|---|---|---|
1 |
Default.aspx |
기본 |
null(빈 셀) |
2 |
Catalog.aspx |
카탈로그 |
1 |
3 |
Aboutus.aspx |
문의처 |
1 |
.gif "참고") 참고: |
|---|
데이터 소스는 서로 다른 SQL 구문을 포함하기 때문에 한 데이터 소스에서는 사용할 수 있지만 다른 데이터 소스에서는 사용할 수 없는 명령도 있습니다. 따라서 .NET Framework Data Provider for ODBC 또는 .NET Framework Data Provider for OLE DB를 사용하여 데이터 소스에 액세스하더라도 데이터 소스와 관련된 사이트 맵 공급자를 만드는 것이 좋습니다(예: SybaseSiteMapProvider, OracleSiteMapProvider 등). |
AccessSiteMapProvider 클래스는 StaticSiteMapProvider 클래스에서 파생되며 기본 SQL 쿼리를 비롯해 OleDbCommand 및 OleDbDataReader 개체를 사용하여 Access 데이터베이스에서 해당 정보를 검색합니다.
마지막으로 다음 코드 예제처럼 AccessSiteMapProvider가 Web.config 파일에서 기본 공급자로 구성됩니다.
<configuration>
<system.web>
<siteMap defaultProvider="AccessSiteMapProvider">
<providers>
<add
name="AccessSiteMapProvider"
type="<type name>"
accessSiteMapConnectionString="PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=<path>\sitemap.mdb "/>
</providers>
</siteMap>
</system.web>
</configuration>
이 예제를 사용자 지정하려면 <type name>을 사이트 맵 데이터 공급자를 구현하는 클래스의 정규화된 이름으로 바꿉니다. 예를 들어, 위의 C# 코드에서는 <type name>을 Samples.AspNet.CS.Controls.AccessSiteMapProvider로 바꿉니다. 사이트 맵 데이터 공급자 코드를 컴파일하여 Bin 디렉터리에 넣은 경우 <type name> 문자열에 컴파일된 파일 이름(확장명 제외)이 포함되어야 합니다. 예를 들어, 위의 C# 코드를 Samples.AspNet.dll이라는 파일로 컴파일하는 경우 <type name>을 Samples.AspNet.CS.Controls.AccessSiteMapProvider.Samples.AspNet으로 바꿉니다. 마지막으로, <path>를 사이트 맵 데이터베이스의 실제 절대 경로로 바꿉니다.
| 참고: |
|---|
StaticSiteMapProvider에서 상속하는 경우 BuildSiteMap 및 GetRootNodeCore 같은 멤버는 재지정해야 합니다. |
강력한 프로그래밍
StaticSiteMapProvider 클래스를 확장하는 경우 가장 중요한 세 가지 메서드는 GetRootNodeCore, Initialize 및 BuildSiteMap입니다. Clear 및 FindSiteMapNode 메서드는 대부분의 사용자 지정 공급자 구현에 적합한 기본 구현을 제공합니다.