Walkthrough: Editing an Access Database with ADO.NET

原创 2004年07月23日 14:47:00

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

 

 

Walkthrough: Editing an Access Database with ADO.NET

Martin Tracy
Visual Studio Team
Microsoft Corporation

June 2003

Applies to:
   Microsoft? ADO.NET
   Microsoft? ASP.NET
   Microsoft? Visual C#? .NET
   Microsoft? Visual Studio? .NET

Summary: This walkthrough demonstrates how to add, delete, and edit records in a Microsoft? Access database with ADO.NET using a simple Visual C#? code-behind Web application. (11 printed pages)

Download the sample code ADONETAccessDB.exe. (130KB)

Contents

Introduction
Requirements
Walkthrough
References

Introduction

The Jet database engine is provided by Microsoft? Internet Information Services (IIS) for Microsoft Access databases. This database engine is a popular alternative to Microsoft SQL Server and is useful for low-volume Web applications. The Jet database engine is OLEDB-compliant and is supported by classes in the Microsoft .NET Framework System.Data.OleDb namespace.

The main objects you will use in this walkthrough are the OleDbConnection, OleDbCommand, and OleDbDataReader objects, and the DataGrid server control.

Figure 1   The ADO.NET data access pipeline

The OleDbConnection object handles the connection to the Jet database engine. The OleDbCommand contains the Microsoft SQL statement that tells the database engine what to do. The OleDbDataReader is a fast read-only forward-only database cursor that reads records selected by the SQL statement. The DataGrid displays the records and fires events when you add, delete, or edit a record. These events create new SQL statements that alter the database and display the resulting records.

Requirements

Before you start, you must have the following software installed on your computer:

·                     Windows? XP with SP1 and all hotfixes

·                     Microsoft Internet Information Services (IIS)

·                     Microsoft Access 2002

·                     Visual Studio .NET

Be sure you can create a simple Visual C# ASP.NET Web application before attempting the walkthrough.

Walkthrough

In this walkthrough, you will create a database using Microsoft Access and will create, add, edit, and delete records using ADO.NET.

To create the database

1.                  Open Microsoft Access and create a blank database named Pets.mdb in the new folder C:/Pets.

2.                  Create a new table in Designer view.

3.                  Add an AutoNumber field named ID and two Text fields named PetName and PetType. Make ID the primary key and accept all defaults.

Figure 2   Designing the data table in Access

4.                  Switch to datasheet view and save the table as PetTable.

5.                  Add a few entries to the table.

Figure 3   Adding test data

6.                  Save the table and close Access.

To display the database records

The OleDbConnection object holds the connection string that connects the Jet database engine to the Pets.mdb database. When constructing the connection string, the location of the Pets.mdb file is given relative to the root of the application. The relative path is changed to a physical path by the Server.MapPath method. This lets you publish the Web application to a remote server while still making it available to the Jet database engine.

The OleDbCommand object holds the SQL statement SELECT * FROM PetTable, which selects all the records in PetTable. The OleDbCommand.ExecuteReader method creates an OleDbDataReader object to read these records. The DataGrid is connected to the data reader through its DataGrid.DataSource property.

When the DataGrid.DataBind method executes, database records are moved from the database to the DataGrid, which displays them one record per row.

1.                  Open Visual Studio .NET.

2.                  Create a new Visual C# ASP.NET Web application at http://localhost/Pets.

3.                  Rename the file WebForm1.aspx to PetForm.aspx.

4.                  From the Solution Explorer, right-click the Pets project root and select Add/New Folder. Name the folder Pets. Leave the folder selected.

5.                  Right-click the Pets project and add the existing item C:/Pets/Pets.mdb to the Pets folder. You will have to select All Files in the Files of Type dropdown list to see the Pets.mdb file in the Existing Item browser window.

6.                  From the toolbox, drag a DataGrid to the Web form, renaming it to datagrid.

7.                  Switch to code view by double-clicking the form.

8.                  Add this line to the using statements at the beginning of WebForm1.aspx.cs

10.               Insert this code into the Page_Load method:

15.               Add the ReadRecords method to the PetForm class just after the Page_Load method:

45.               Press F5 to launch the Web application under the debugger. The contents of the database should appear in the browser.

Figure 4   The database as seen in the browser

46.               Close the application, and then close Visual Studio.

Note   The catch statement is commented out in the code above. By default, Visual Studio creates a C# Web application project with the Generate Debugging Information property set to true. This creates a Pets.pdb file in the bin directory. The default ASP.NET error handler uses this information to create a detailed error page, which shows the source line where the error occurred, as well as a stack trace and other error information.

Once your project is debugged, you can set Generate Debugging Information to false, and Pets.pdb will no longer be built. At this point, you can uncomment the catch statement and substitute your own error handler.

To configure the database

The ASP.NET user, by default, does not have permission to write a record to a database or create a locking file (.ldb) in the folder containing the database. You must give the ASP.NET user these permissions. Normally, this is done in one of three ways:

·                     You can add the ASP.NET user to the Administrators group.

·                     You can enable impersonation for the application in the web.config file.

·                     You can add ASP.NET write permission to both the database file and the folder that contains it.

In this walkthrough you will use the third (and safest) method to grant write permission.

1.                  From the File Explorer, find the new Pets folder, normally located at C:/Inetpub/wwwroot/Pets/Pets.

2.                  Right-click the Pets folder, and select Properties.

3.                  Select the Security tab, and click the Add button.

4.                  Add the object name <YOURMACHINE>/ASPNET where <YOURMACHINE> is the name of your machine. Click OK to return to the Security tab.

5.                  Select the ASP.NET account, and add Write permission. This account is named aspnet_wp account, ASP.NET MACHINE Account, or something similar.

6.                  From the File Explorer, right-click the file Pets.mdb, and select Properties.

7.                  Select the Security tab, and click the Advanced button.

8.                  Check "Inherit from parent the permissions entries that apply to child objects". Click OK to accept the change.

For more information on ASP.NET security issues, see Authentication in ASP.NET: .NET Security Guidance.

To create the DataGrid edit column

You can use the Visual Studio Property Builder to add columns to a DataGrid. The Property Builder has a choice of formats that can add color and style to the DataGrid.

1.                  From Visual Studio Designer view, select the DataGrid. If you don't have the Property window open, open it from the View menu.

2.                  At the very bottom of the Properties window you will see two links: Auto Format and Property Builder. Select Property Builder.

3.                  Select Columns view.

4.                  Uncheck "Create columns automatically at run time."

5.                  In the Available Columns list, expand the Button Column. Select the Edit, Update, Cancel option. Click the ">" button to add it to the Selected Columns list.

6.                  In the Available Columns list, select Bound Column. Click the ">" button to add it to the Selected Columns list. Give it the Header text "Pet Name" and the Data Field "PetName."

7.                  Repeat the last step to add another Bound Column, giving it the Header text "Pet Type" and the Data Field "PetType."

8.                  Click OK to return to Designer view. The DataGrid will reflect the changes.

9.                  At the very bottom of the Properties window, select Auto Format.

10.               Select a format, such as "Colorful 1."

11.               Click OK to return to Designer view. The DataGrid will reflect the changes.

Figure 5   The DataGrid with the Edit links added

12.               Press F5 to launch the Web application under the debugger. The contents of the database should appear in the browser.

Figure 6   Edit links and data in the browser

To edit the DataGrid

The DataGrid.EditItemIndex property selects a row for editing. When a row is selected for editing, textboxes appear in each cell. The text in each textbox is set to the value of the corresponding field in the data record.

You must connect the Edit link to an event handler that selects the row containing the link for editing. You should also connect the Cancel link (not yet visible) to an event handler that restores the DataGrid row without changing the corresponding record.

1.                  From Visual Studio Designer view, select the DataGrid, and click the Events tab in the Properties window (the lightning bolt). Double-click CancelCommand to create the datagrid_CancelCommand event handler. Return to the Events tab and double-click EditCommand to create the datagrid_EditCommand event handler.

2.                  Insert the following code into the two event handlers:

13.               Press F5 to launch the Web application.

14.               Click the Edit link to the left of the second row. The DataGrid displays the row with the values for the fields PetName and PetType in textboxes. The Edit link changes to the Update and Cancel links.

Figure 7   Editing data in the browser, having clicked the Edit link

15.               Click Cancel to return to the default DataGrid display.

To update the DataGrid

When a row is selected for editing, the Update and Cancel links appear. Once you have used the textboxes to enter new values for the fields in a database record, you must move these changes back to the database.

You must connect the Update link to an event handler that reads each textbox and updates the fields in the corresponding record. The DataGrid.DataKeyField uses the key field of the database table to associate each row with its corresponding record. You build a SQL statement to update the record with the new field values, then call OleDbCommand.ExecuteNonQuery to perform the update.

1.                  From Visual Studio Designer view, select the DataGrid. In the Properties window, set the DataKeyField property to ID.

2.                  Click the Events tab in the Properties view (the lightning bolt). Double-click UpdateCommand to create the datagrid_UpdateCommand event handler.

3.                  Insert the following code into the event handler:

21.               Add the ExecuteNonQuery method to the PetForm class:

46.               Press F5 to launch the Web application.

47.               Click the Edit link to the left of the second row.

48.               Change the text in the PetType textbox from "dog" to "dawg."

49.               Click the Update link to see the updated DataGrid.

To add a record to the DataGrid

The DataGrid doesn't support an Add column, but you can use a button click event handler to add a record to the database. Create a SQL statement to add the record, then call OleDbCommand.ExecuteNonQuery to update the database.

1.                  From Visual Studio Designer view, drag a button from the toolbar to the form and name it btnAddPet. Change its text to "Add Pet."

Figure 8   Adding an Add button to the DataGrid

2.                  Double-click the button and insert this code in the btnAddPet_Click method:

10.               Press F5 to launch the Web application.

11.               Click the Add button. A new row is added to the database.

Figure 9   The new row added

To delete a row from the DataGrid

You can use the Property Builder to add a Delete column to the DataGrid. You must connect the Delete link to an event handler that deletes the corresponding record in the database. You can use the DataGrid.DataKeyField property to associate the row to be deleted with its corresponding record. Create a SQL statement to delete the record, then call OleDbCommand.ExecuteNonQuery to update the database.

1.                  From Visual Studio Designer view, select the DataGrid.

2.                  At the very bottom of the Properties window, select Property Builder.

3.                  Select Columns view.

4.                  In the Available Columns list, expand the Button column. Select the Delete option. Click the ">" button to add it to the Selected Columns list. Click OK to return to Designer view.

5.                  Select the DataGrid, and click the Events tab in the Properties window (the lightning bolt). Double-click the DeleteCommand to create the datagrid_DeleteCommand event handler.

6.                  Insert the following code into the event handler:

15.               Press F5 to launch the Web application.

16.               Click the Delete button to the right of the last row. The row is deleted from the database.

References

For more information, see the following topics:

·                     Using ADO.NET

·                     ASP.NET QuickStart Tutorial

·                     In-Depth: Unlocking Microsoft Access Data with ADO

 

iOS中 iOS10 权限崩溃问题 韩俊强的CSDN博客

今天 手机升级了 iOS10 Beta,然后用正在开发的项目 装了个ipa包,发现点击有关 权限访问 直接Crash了,并在控制台输出了一些信息:...
  • qq_31810357
  • qq_31810357
  • 2016年06月18日 10:32
  • 17386

CDH5.3.3安装(hadoop集群)

Cloudera Manager提供两种软件包安装源,Package 和 Parcel: Package就是一个个rpm文件,以yum的方式组织起来。 Parcel是rpm包的压缩格式,以.parce...
  • hualiu163
  • hualiu163
  • 2015年06月27日 10:21
  • 11649

VS C# ADO.NET代码连接Access数据库

在Visual Studio2013 C#中以编程方式操作Access数据库,一般使用OleDb进行连接: 1.首先添加两个引用(使用using): using System.Data.OleDb; ...
  • c1481118216
  • c1481118216
  • 2016年05月31日 00:50
  • 3583

Database Programming with VB.Net &amp; Ado.Net

  • 2007年09月05日 19:27
  • 5.85MB
  • 下载

An Introduction to ADO.Net

  • 2008年07月09日 20:59
  • 254KB
  • 下载

EF Provider for Access/ODBC 以及ADO.Net Entity Framework 与Linq to SQL的比较和适用场景

1.我的环境VS2008 SP1.Entity Data Model  Wizard里选DataSource时不能选择Acess的数据库.是否目前不能建Acess的Entity Data Model....
  • educast
  • educast
  • 2012年05月22日 12:47
  • 2832

VS C# ADO.NET代码连接Access数据库

在Visual Studio2013 C#中以编程方式操作Access数据库,一般使用OleDb进行连接: 1.首先添加两个引用(使用using): using System.Data.OleDb; ...
  • c1481118216
  • c1481118216
  • 2016年05月31日 00:50
  • 3583

在C#中使用ADO.Net部件来访问Access数据库

  • 2011年09月19日 19:00
  • 18KB
  • 下载

ado.net读写access数据库的代码

  • 2014年05月06日 17:11
  • 157KB
  • 下载

Apress - Pro ADO.NET Data Services Working with RESTful Data

  • 2009年02月02日 10:40
  • 3.6MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Walkthrough: Editing an Access Database with ADO.NET
举报原因:
原因补充:

(最多只允许输入30个字)