Visual Studio .NET 2002.NET 1.0DBAVisual Studio .NET 2003.NET 1.1ADO.NETWindows 2000Windows XPIntermediateDevVisual StudioWindows.NETC#
如何将 Access 数据库链接到 C# 应用程序
3.24/5 (51投票s)
2004年9月13日
3分钟阅读
436696
18309
一篇解释如何在 C# 应用程序中链接家庭数据库的文章。
引言
ADO.NET 对象模型提供了一个 API，用于以编程方式访问数据库系统。命名空间 System.Data 是 ADO.NET API 的根命名空间，是 ADO.NET 的主要命名空间。System.Data.OleDb 和 System.Data.SqlClient 包含允许程序员连接和修改数据源的类。
一些关键字
DataSet：它在断开连接的缓存中存储数据。DataAdapter：DataAdapter类表示一组数据库命令和一个数据库连接，您可以使用它们来填充DataSet并更新数据源。它充当DataSet和数据源之间的桥梁。- Connection：它充当数据源和应用程序之间的桥梁。
- DataSource：是应用程序将连接到的数据库。
背景
我搜索了很多网站，希望能找到一段代码，[借助它] 我可以使用 ADO.NET 将 Access 数据库与 C# 应用程序连接起来。在查阅 C# 书籍后，我找到了一些不错的代码，这些代码帮助我创建了这个简单的应用程序。希望它能作为基本架构。
使用代码
首先，您应该简单地打开 VS.NET，然后在 File 菜单中，单击 New，然后单击 Project。在“新建项目”对话框中，选择“Windows 应用程序”模板项目，并将其命名为 WindowsApplication1，如下图所示
创建窗口后，向其中添加四个按钮 Add、Update、Delete、Find，以及四个文本框 ISBN、Title、Author、Edition Number，还有一个表示已执行查询的标签。应用程序的外观将是
现在，在您的应用程序中添加 OleDbDataAdapter 来控制数据库。应用程序连接的 Access 数据库将是
添加 OleDbDataAdapter 时，将出现一个配置向导，如下所示
现在，您应该创建一个连接来连接到您的数据库。为此，请单击“New Connection”按钮。
选择“Microsoft Jet 4.0 OLE DB Provider”。此提供程序允许连接与 Access 数据库通信。之后，单击“Next”按钮。
在此页面中，您应该确定应用程序将连接到的数据源的路径。完成此操作后，您可以测试连接以确保连接没有问题。
最后，编写数据适配器将在 DataSet 中加载的查询；您可以手动编写查询，也可以使用查询生成器。
但在那之前，您应该生成数据集，为此，请转到其属性表，然后在表底部选择“Generate dataset”。
完成这些步骤后，您的应用程序现在已准备好控制数据库。
现在，让我们开始为“Add”按钮编写代码。
private void Add_btn_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbDataAdapter1.InsertCommand.CommandText =
"INSERT INTO BookDb (author, EditionNumber, ISBN, Title)" +
"VALUES ('"+ this.author_textBox.Text
+"','"+this.ed_textBox.Text+
"' , "+ this.isbn_textBox.Text +",'"+
this.title_textBox.Text+"')";
//open the bridge between the application and the datasource
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.InsertCommand.Connection = oleDbConnection1;
//execute the qurey
this.oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();
//close the connection
this.oleDbConnection1.Close();
MessageBox.Show("Record insedted Successfully"); //inform the user
//clear the form
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
//show the qurey
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.InsertCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
//close the connection
this.oleDbConnection1.Close();
MessageBox.Show(exp.ToString());
}
如果您注意到，在 insert 查询中，所有参数都包含在 ‘ ’（单引号）中，除了 ISBN。原因是数据库中的 ISBN 字段被声明为数字，而其他字段被声明为字符串，因此它们包含在 ‘’（单引号）中。
现在，转到 Update 按钮的单击函数并编写以下代码
private void up_btn_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbDataAdapter1.UpdateCommand.CommandText =
"UPDATE BookDb SET "+
"author ='"+ this.author_textBox.Text +
"', EditionNumber ='"+this.ed_textBox.Text +
"', ISBN ="+this.isbn_textBox.Text+", Title ='"+
this.title_textBox.Text+"'" +
" WHERE ISBN ="+ this.isbn_textBox.Text;
//open the bridge between the application and the datasource
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.UpdateCommand.Connection = oleDbConnection1;
//execute the qurey
this.oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();
//close the connection
this.oleDbConnection1.Close();
MessageBox.Show("Record updated Successfully"); //inform the user
//clear the form
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
//show the qurey
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.UpdateCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
//close the connection
this.oleDbConnection1.Close();
MessageBox.Show(exp.ToString());
}
}
然后是 Delete 按钮的单击函数的代码
private void del_btn_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbDataAdapter1.DeleteCommand.CommandText =
"DELETE FROM BookDb WHERE ISBN =" + this.isbn_textBox.Text;
//open the bridge between the application and the datasource
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.DeleteCommand.Connection = oleDbConnection1;
//execute the qurey
this.oleDbDataAdapter1.DeleteCommand.ExecuteNonQuery();
//close the connection
this.oleDbConnection1.Close();
MessageBox.Show("Record deleted Successfully"); //inform the user
//clear the form
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
//show the qurey
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.DeleteCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
//close the connection
this.oleDbConnection1.Close();
MessageBox.Show(exp.ToString());
}
}
最后，是 Find 按钮的单击函数的代码
private void find_btn_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbDataAdapter1.SelectCommand.CommandText =
"SELECT * FROM BookDb WHERE ISBN = "+ this.isbn_textBox.Text;
// clear the DataSet from the last operation
dataSet11.Clear();
this.oleDbDataAdapter1.Fill(this.dataSet11.Tables["BookDb"]);
}
catch(System.Data.OleDb.OleDbException exp)
{
MessageBox.Show(exp.ToString());
}
//copy the dataset in datatable object
DataTable dataTable = dataSet11.Tables[ 0 ];
//if the row count = 0 then the qurey return nothing
if ( dataTable.Rows.Count == 0 )
MessageBox.Show("the Record not founded");
}
在此函数中，我们使用 DataTable 类型的对象。DataTable 表示内存中关系数据的一张表；数据是本地的，存在于其所在的基于 .NET 的应用程序中，但可以使用 DataAdapter 从 Microsoft® SQL Server 等数据源填充。
完整代码
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
namespace WindowsApplication1
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.TextBox isbn_textBox;
private System.Windows.Forms.TextBox title_textBox;
private System.Windows.Forms.TextBox author_textBox;
private System.Windows.Forms.TextBox ed_textBox;
private System.Windows.Forms.Button Add_btn;
private System.Windows.Forms.Button up_btn;
private System.Windows.Forms.Button del_btn;
private System.Windows.Forms.Button find_btn;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
private WindowsApplication1.DataSet1 dataSet11;
private System.Windows.Forms.Label qurey_label;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.isbn_textBox = new System.Windows.Forms.TextBox();
this.dataSet11 = new WindowsApplication1.DataSet1();
this.title_textBox = new System.Windows.Forms.TextBox();
this.author_textBox = new System.Windows.Forms.TextBox();
this.ed_textBox = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.Add_btn = new System.Windows.Forms.Button();
this.up_btn = new System.Windows.Forms.Button();
this.del_btn = new System.Windows.Forms.Button();
this.find_btn = new System.Windows.Forms.Button();
this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
this.qurey_label = new System.Windows.Forms.Label();
((System.ComponentModel.ISupportInitialize)
(this.dataSet11)).BeginInit();
this.SuspendLayout();
//
// isbn_textBox
//
this.isbn_textBox.DataBindings.Add(new
System.Windows.Forms.Binding("Text",
this.dataSet11, "BookDb.ISBN"));
this.isbn_textBox.Location = new System.Drawing.Point(104, 64);
this.isbn_textBox.Name = "isbn_textBox";
this.isbn_textBox.Size = new System.Drawing.Size(216, 20);
this.isbn_textBox.TabIndex = 0;
this.isbn_textBox.Text = "";
//
// dataSet11
//
this.dataSet11.DataSetName = "DataSet1";
this.dataSet11.Locale = new
System.Globalization.CultureInfo("en-US");
this.dataSet11.Namespace = "http://www.tempuri.org/DataSet1.xsd";
//
// title_textBox
//
this.title_textBox.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dataSet11,
"BookDb.Title"));
this.title_textBox.Location = new System.Drawing.Point(104, 104);
this.title_textBox.Name = "title_textBox";
this.title_textBox.Size = new System.Drawing.Size(216, 20);
this.title_textBox.TabIndex = 1;
this.title_textBox.Text = "";
//
// author_textBox
//
this.author_textBox.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dataSet11,
"BookDb.author"));
this.author_textBox.Location = new System.Drawing.Point(104, 144);
this.author_textBox.Name = "author_textBox";
this.author_textBox.Size = new System.Drawing.Size(216, 20);
this.author_textBox.TabIndex = 2;
this.author_textBox.Text = "";
//
// ed_textBox
//
this.ed_textBox.DataBindings.Add(new
System.Windows.Forms.Binding("Text", this.dataSet11,
"BookDb.EditionNumber"));
this.ed_textBox.Location = new System.Drawing.Point(104, 184);
this.ed_textBox.Name = "ed_textBox";
this.ed_textBox.Size = new System.Drawing.Size(216, 20);
this.ed_textBox.TabIndex = 3;
this.ed_textBox.Text = "";
//
// label1
//
this.label1.Location = new System.Drawing.Point(16, 64);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(72, 23);
this.label1.TabIndex = 4;
this.label1.Text = "ISBN";
//
// label2
//
this.label2.Location = new System.Drawing.Point(16, 104);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(72, 23);
this.label2.TabIndex = 5;
this.label2.Text = "Title";
//
// label3
//
this.label3.Location = new System.Drawing.Point(16, 144);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(72, 23);
this.label3.TabIndex = 6;
this.label3.Text = "Author";
//
// label4
//
this.label4.Location = new System.Drawing.Point(16, 184);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(80, 23);
this.label4.TabIndex = 7;
this.label4.Text = "Edtion Number";
//
// Add_btn
//
this.Add_btn.Location = new System.Drawing.Point(40, 16);
this.Add_btn.Name = "Add_btn";
this.Add_btn.TabIndex = 8;
this.Add_btn.Text = "Add";
this.Add_btn.Click += new System.EventHandler(this.Add_btn_Click);
//
// up_btn
//
this.up_btn.Location = new System.Drawing.Point(136, 16);
this.up_btn.Name = "up_btn";
this.up_btn.TabIndex = 9;
this.up_btn.Text = "Update";
this.up_btn.Click += new System.EventHandler(this.up_btn_Click);
//
// del_btn
//
this.del_btn.Location = new System.Drawing.Point(240, 16);
this.del_btn.Name = "del_btn";
this.del_btn.TabIndex = 10;
this.del_btn.Text = "Delete";
this.del_btn.Click += new System.EventHandler(this.del_btn_Click);
//
// find_btn
//
this.find_btn.Location = new System.Drawing.Point(16, 312);
this.find_btn.Name = "find_btn";
this.find_btn.Size = new System.Drawing.Size(320, 24);
this.find_btn.TabIndex = 11;
this.find_btn.Text = "Find";
this.find_btn.Click += new System.EventHandler(this.find_btn_Click);
//
// oleDbDataAdapter1
//
this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "BookDb",
new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("ISBN", "ISBN"),
new System.Data.Common.DataColumnMapping("Title", "Title"),
new System.Data.Common.DataColumnMapping("EditionNumber",
"EditionNumber"),
new System.Data.Common.DataColumnMapping("author", "author")
})
});
this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
//
// oleDbDeleteCommand1
//
this.oleDbDeleteCommand1.CommandText =
"DELETE FROM BookDb WHERE (ISBN = ?) AND " +
"(EditionNumber = ? OR ? IS NULL AND Editi" +
"onNumber IS NULL) AND (Title = ? OR ? IS NULL " +
"AND Title IS NULL) AND (author = ?" +
" OR ? IS NULL AND author IS NULL)";
this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_ISBN",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false,
((System.Byte)(10)), ((System.Byte)(0)), "ISBN",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_EditionNumber",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "EditionNumber",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_EditionNumber1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "EditionNumber",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Title",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "Title",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Title1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "Title",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_author",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "author",
System.Data.DataRowVersion.Original, null));
this.oleDbDeleteCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_author1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "author",
System.Data.DataRowVersion.Original, null));
//
// oleDbConnection1
//
this.oleDbConnection1.ConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Password="""";User ID=Admin;Data Source=D:\MY Db\Ahmed" +
@" Article\WindowsApplication1\bookDb.mdb;Mode=Share" +
@" Deny None;Extended Properties="""";Jet OLEDB:" +
@"System database="""";Jet OLEDB:Registry " +
@"Path="""";Jet OLEDB:Database Password="""";Jet " +
@"OLEDB:Engine Type=5;Jet OLEDB:Database " +
@"Locking Mode=1;Jet OLEDB:Global Partial Bulk " +
@"Ops=2;Jet OLEDB:Global Bulk Transactions=1;" +
@"Jet OLEDB:New Database Password="""";Jet OLEDB:" +
@"Create System Database=False;Jet OLEDB:Encrypt " +
@"Database=False;Jet OLEDB:Don't Copy Locale " +
@"on Compact=False;Jet OLEDB:Compact Without " +
@"Replica Repair=False;Jet OLEDB:SFP=False";
//
// oleDbInsertCommand1
//
this.oleDbInsertCommand1.CommandText =
"INSERT INTO BookDb(author, EditionNumber, " +
"ISBN, Title) VALUES (?, ?, ?, ?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("author",
System.Data.OleDb.OleDbType.VarWChar, 50, "author"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("EditionNumber",
System.Data.OleDb.OleDbType.VarWChar, 50, "EditionNumber"));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("ISBN",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false,
((System.Byte)(10)), ((System.Byte)(0)), "ISBN",
System.Data.DataRowVersion.Current, null));
this.oleDbInsertCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Title",
System.Data.OleDb.OleDbType.VarWChar, 50, "Title"));
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.CommandText =
"SELECT author, EditionNumber, ISBN, Title FROM BookDb";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
//
// oleDbUpdateCommand1
//
this.oleDbUpdateCommand1.CommandText =
"UPDATE BookDb SET author = ?, EditionNumber = ?, " +
"ISBN = ?, Title = ? WHERE (ISBN " +
"= ?) AND (EditionNumber = ? OR ? IS NULL AND " +
"EditionNumber IS NULL) AND (Title =" +
" ? OR ? IS NULL AND Title IS NULL) AND (author " +
"= ? OR ? IS NULL AND author IS NU" + "LL)";
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("author",
System.Data.OleDb.OleDbType.VarWChar, 50, "author"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("EditionNumber",
System.Data.OleDb.OleDbType.VarWChar, 50, "EditionNumber"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("ISBN",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false,
((System.Byte)(10)), ((System.Byte)(0)), "ISBN",
System.Data.DataRowVersion.Current, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Title",
System.Data.OleDb.OleDbType.VarWChar, 50, "Title"));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_ISBN",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, false,
((System.Byte)(10)), ((System.Byte)(0)), "ISBN",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_EditionNumber",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "EditionNumber",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_EditionNumber1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "EditionNumber",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Title",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "Title",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_Title1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "Title",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_author",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "author",
System.Data.DataRowVersion.Original, null));
this.oleDbUpdateCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Original_author1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, false,
((System.Byte)(0)), ((System.Byte)(0)), "author",
System.Data.DataRowVersion.Original, null));
//
// qurey_label
//
this.qurey_label.Location = new System.Drawing.Point(40, 224);
this.qurey_label.Name = "qurey_label";
this.qurey_label.Size = new System.Drawing.Size(288, 72);
this.qurey_label.TabIndex = 12;
this.qurey_label.Text = "Qurey";
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(360, 366);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.qurey_label,
this.find_btn,
this.del_btn,
this.up_btn,
this.Add_btn,
this.label4,
this.label3,
this.label2,
this.label1,
this.ed_textBox,
this.author_textBox,
this.title_textBox,
this.isbn_textBox});
this.MaximizeBox = false;
this.MinimizeBox = false;
this.Name = "Form1";
this.StartPosition =
System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "Form1";
((System.ComponentModel.ISupportInitialize)
(this.dataSet11)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void find_btn_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbDataAdapter1.SelectCommand.CommandText =
"SELECT * FROM BookDb WHERE ISBN = "+
this.isbn_textBox.Text;
// clear the DataSet from the last operation
dataSet11.Clear();
this.oleDbDataAdapter1.Fill(this.dataSet11.Tables["BookDb"]);
}
catch(System.Data.OleDb.OleDbException exp)
{
MessageBox.Show(exp.ToString());
}
//copy the dataset in datatable object
DataTable dataTable = dataSet11.Tables[ 0 ];
//if the row count = 0 then the qurey return nothing
if ( dataTable.Rows.Count == 0 )
MessageBox.Show("the Record not founded");
}
private void Add_btn_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbDataAdapter1.InsertCommand.CommandText =
"INSERT INTO BookDb (author, EditionNumber, ISBN, Title)" +
"VALUES ('"+ this.author_textBox.Text +
"','"+this.ed_textBox.Text+
"' , "+ this.isbn_textBox.Text +",'"+
this.title_textBox.Text+ "')";
//open the bridge between the application and the datasource
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.InsertCommand.Connection = oleDbConnection1;
//execute the qurey
this.oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();
//close the connection
this.oleDbConnection1.Close();
//inform the user
MessageBox.Show("Record insedted Successfully");
//clear the form
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
//show the qurey
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.InsertCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
//close the connection
this.oleDbConnection1.Close();
MessageBox.Show(exp.ToString());
}
}
private void up_btn_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbDataAdapter1.UpdateCommand.CommandText =
"UPDATE BookDb SET "+
"author ='"+ this.author_textBox.Text +
"', EditionNumber ='"+this.ed_textBox.Text +
"', ISBN ="+this.isbn_textBox.Text+", Title ='"+
this.title_textBox.Text+"'" +
" WHERE ISBN ="+ this.isbn_textBox.Text;
//open the bridge between the application and the datasource
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.UpdateCommand.Connection = oleDbConnection1;
//execute the qurey
this.oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();
//close the connection
this.oleDbConnection1.Close();
//inform the user
MessageBox.Show("Record updated Successfully");
//clear the form
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
//show the qurey
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.UpdateCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
//close the connection
this.oleDbConnection1.Close();
MessageBox.Show(exp.ToString());
}
}
private void del_btn_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbDataAdapter1.DeleteCommand.CommandText =
"DELETE FROM BookDb WHERE ISBN =" + this.isbn_textBox.Text;
//open the bridge between the application and the datasource
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.DeleteCommand.Connection = oleDbConnection1;
//execute the qurey
this.oleDbDataAdapter1.DeleteCommand.ExecuteNonQuery();
//close the connection
this.oleDbConnection1.Close();
//inform the user
MessageBox.Show("Record deleted Successfully");
//clear the form
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
//show the qurey
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.DeleteCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
//close the connection
this.oleDbConnection1.Close();
MessageBox.Show(exp.ToString());
}
}
}
}
提示
此应用程序将不起作用，除非您修改数据源的路径。为此，请转到 OleDbConnection 属性：ConnectionString，然后用您 PC 上 Access 文件的路径修改该路径。
此应用程序的演示文件期望在“C:\”中找到 Access 文件。