Introduction
The ADO.NET object model provides an API for accessing database systems programmatically. Namespace System.Data is the root namespace for the ADO.NET API, the primary namespace for ADO.NET. System.Data.OleDb and System.Data.SqlClient contain classes that enable programmers to connect with and modify the data sources.
Some keywords:
DataSet: it stores the data in disconnected cache.
DataAdapter: the DataAdapter class represents a set of database commands and a database connection that you use to fill the DataSet and update the data source. It serves as a bridge between a DataSet and the data source.
- Connection: it serves as a bridge between a data source and the application.
- DataSource: is the database that the application will connect to.
Background
I�ve searched well so many sites about a code that I can [with the help of it] use ADO.NET to connect the Access database with a C# application. After searching the C# books, I�ve found some nice code that helped me to create this simple application. Hope it can help as a basic architecture.
Using the code
At first, you should simply open VS.NET and then at the File menu, click on New, Project. From the New Project dialog box, choose the Windows Application template project and name it WindowsApplication1, like shown below:
After creating a window, add to it, four buttons Add, Update, Delete, Find, and four text boxes ISBN, Title, Author, Edition Number, and a label that represents the query that executed. The application will look like:
Now, add OleDbDataAdapter in your application to control the database. The Access database that the application connects with will look like:
When you add the OleDbDataAdapter, a configuration wizard will appear like:
Now, you should create a connection that will connect to your database. To do this, click on �New Connection� button.
Select �Microsoft Jet 4.0 OLE DB Provider�. This provider enables the connection to communicate with the Access database. After that, click on �Next� button.
In this page, you should determine the path of the data source that your application will connect with. After you do this, you can test the connection to ensure that there is no problem in the connection.
Finally, write the query that the data adapter will load in the DataSet; you can write the query manually or by the query builder.
But before that you should generate the database , to do this go into its property sheet and on the bottom of the sheet, select "Generate dataset".
After finishing these steps, your application is now ready to control the database.
Now, let's begin to write the code for the Add button.
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+"')";
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.InsertCommand.Connection = oleDbConnection1;
this.oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();
this.oleDbConnection1.Close();
MessageBox.Show("Record insedted Successfully");
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.InsertCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
this.oleDbConnection1.Close();
MessageBox.Show(exp.ToString());
}
If you notice, in the insert query, all parameters come between � � (single quotation) except the ISBN. The reason for this is the ISBN field in the database is declared as number while the other fields are declared as string, so they comes in �� (single quotation).
Now, go to the Update button click function and write the following code:
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;
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.UpdateCommand.Connection = oleDbConnection1;
this.oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();
this.oleDbConnection1.Close();
MessageBox.Show("Record updated Successfully");
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.UpdateCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
this.oleDbConnection1.Close();
MessageBox.Show(exp.ToString());
}
}
And then the code for the Delete button click function:
private void del_btn_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbDataAdapter1.DeleteCommand.CommandText =
"DELETE FROM BookDb WHERE ISBN =" + this.isbn_textBox.Text;
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.DeleteCommand.Connection = oleDbConnection1;
this.oleDbDataAdapter1.DeleteCommand.ExecuteNonQuery();
this.oleDbConnection1.Close();
MessageBox.Show("Record deleted Successfully");
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.DeleteCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
this.oleDbConnection1.Close();
MessageBox.Show(exp.ToString());
}
}
Finally, the code for the find button click function:
private void find_btn_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbDataAdapter1.SelectCommand.CommandText =
"SELECT * FROM BookDb WHERE ISBN = "+ this.isbn_textBox.Text;
dataSet11.Clear();
this.oleDbDataAdapter1.Fill(this.dataSet11.Tables["BookDb"]);
}
catch(System.Data.OleDb.OleDbException exp)
{
MessageBox.Show(exp.ToString());
}
DataTable dataTable = dataSet11.Tables[ 0 ];
if ( dataTable.Rows.Count == 0 )
MessageBox.Show("the Record not founded");
}
In this function, we use an object of type DataTable. A DataTable represents one table of in-memory relational data; the data is local to the .NET-based application in which it resides, but can be populated from a data source such as Microsoft� SQL Server, using a DataAdapter.
Full code
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
namespace WindowsApplication1
{
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;
private System.ComponentModel.Container components = null;
public Form1()
{
InitializeComponent();
}
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
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();
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 = "";
this.dataSet11.DataSetName = "DataSet1";
this.dataSet11.Locale = new
System.Globalization.CultureInfo("en-US");
this.dataSet11.Namespace = "http://www.tempuri.org/DataSet1.xsd";
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 = "";
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 = "";
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 = "";
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";
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";
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";
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";
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);
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);
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);
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);
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;
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));
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";
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"));
this.oleDbSelectCommand1.CommandText =
"SELECT author, EditionNumber, ISBN, Title FROM BookDb";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
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));
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";
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
[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;
dataSet11.Clear();
this.oleDbDataAdapter1.Fill(this.dataSet11.Tables["BookDb"]);
}
catch(System.Data.OleDb.OleDbException exp)
{
MessageBox.Show(exp.ToString());
}
DataTable dataTable = dataSet11.Tables[ 0 ];
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+ "')";
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.InsertCommand.Connection = oleDbConnection1;
this.oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();
this.oleDbConnection1.Close();
MessageBox.Show("Record insedted Successfully");
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.InsertCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
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;
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.UpdateCommand.Connection = oleDbConnection1;
this.oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();
this.oleDbConnection1.Close();
MessageBox.Show("Record updated Successfully");
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.UpdateCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
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;
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.DeleteCommand.Connection = oleDbConnection1;
this.oleDbDataAdapter1.DeleteCommand.ExecuteNonQuery();
this.oleDbConnection1.Close();
MessageBox.Show("Record deleted Successfully");
this.title_textBox.Text = "";
this.isbn_textBox.Text = "";
this.author_textBox.Text = "";
this.ed_textBox.Text = "";
this.qurey_label.Text = "";
this.qurey_label.Text =
oleDbDataAdapter1.DeleteCommand.CommandText.ToString();
}
catch(System.Data.OleDb.OleDbException exp)
{
this.oleDbConnection1.Close();
MessageBox.Show(exp.ToString());
}
}
}
}
Tip
This application will not work unless you modify the path for the data source. To do this, go to the OleDbConnection property: ConnectionString, and modify the path by the path of the Access file on your PC.
The demo file for this application expects to find the Access file in �C:\�.
