This is a simple Personal Address Program that returns results from a database table, writes the output to textboxes, and uses buttons (First, Previous, Next, Last) to navigate through the records.
You also can manipulate the data by clicking the toolbar buttons, namely Add/New record, Save the new record, Delete the current record, and Edit/Update the records.
I chose Microsoft Access database (as reflected in the ADO.NET OleDb objects) because it's easy to use and you don't need to have Microsoft SQL Server running. But ADO.NET is highly optimized if you are working with Microsoft SQL Server databases (as reflected in the ADO.NET Sql objects).
After getting connected to the MS Access database, all the records will be displayed in the textboxes. In order to navigate through the records, you can use buttons Next, Previous, First, Last. You can find a detailed explanation about the use of the navigation buttons in the first part: Using ADO.NET for beginners
If you click New button in the toolbar, the following methods are invoked:
fnEnableToolbarButtons(true, "Save")
to enable the "Save" button.
fnEnableToolbarButtons(false, "Delete")
to disable the "Delete" button.
fnEnableToolbarButtons(false, "Edit")
to disable the "Edit" button.
fnEnableButtonsNextPreviousLastFirst(false)
to disable the navigation buttons.
fnClearAllTextBox()
to clear all the textboxes.
fnEnableDisableTextBox(true) to enable the textboxes for
editing.
Here are the code snippets of the methods I used for the New button:
Depending on the two parameters of the method called, we enable or disable the
ToolbarButton. For example: fnEnableToolbarButtons(true,
"Save"); means we check the parameters are "Save" and "true"
and enable the "Save" button. If we use "false" instead
of "true", we disable the "Save" button.
private void fnEnableToolbarButtons(bool b, string s1)
{
if (s1=="New")
this.toolBarButtonNew.Enabled=b;
else if (s1=="Save")
this.toolBarButtonSave.Enabled=b;
else if (s1=="Delete")
this.toolBarButtonDelete.Enabled=b;
else if (s1=="Edit")
this.toolBarButtonEdit.Enabled=b;
else if (s1=="Refresh")
this.toolBarButtonRefresh.Enabled=b;
}
The method "fnEnableButtonsNextPreviousLastFirst" has only one
bool parameter. In a foreach loop, we iterate
through all the controls on the form and check if it's a Button,
then enable or disable it with the bool parameter.
public void fnEnableButtonsNextPreviousLastFirst(bool flag)
{
string str;
foreach(Control ctrl in this.Controls)
{
str = Convert.ToString(ctrl.GetType());
if(str == "System.Windows.Forms.Button")
ctrl.Enabled = flag;
}
}
In the method "fnClearAllTextBox()", we iterate through all the
controls on the form and check if it is a TextBox, then we clear
the contents of the TextBox.
private void fnClearAllTextBox()
{
string str;
foreach(Control ctrl in this.Controls)
{
str=Convert.ToString(ctrl.GetType());
if(str=="System.Windows.Forms.TextBox")
ctrl.Text = "";
}
}
public void fnEnableDisableTextBox(bool flag)
{
string str;
foreach(Control ctrl in this.Controls)
{
str = Convert.ToString(ctrl.GetType());
if(str == "System.Windows.Forms.TextBox")
ctrl.Enabled = flag;
}
}
If you click the New button to insert a new record, the Save button will be enabled so that you can save the new record.
The code snippet of the method for the Save a new record is shown here:
private void fnSaveNewRecord()
{
try
{
string strInsert;
strInsert = "insert into PersonTable" +
"(FirstName, LastName, Title, City,Country)"
+ " values('" + this.textboxFirstname.Text + "', '"
+ this.textboxLastname.Text + "', '"
+ this.textboxTitle.Text + "', '"
+ this.textboxCity.Text + "', '"
+ this.textboxCountry.Text + "')";
if (this.textboxFirstname.Text !="" &&
this.textboxLastname.Text !=""&&
this.textboxTitle.Text !="" &&
this.textboxCity.Text !=""
&& this.textboxCountry.Text !="")
{
this.oleDbDataAdapter1.InsertCommand.CommandText = strInsert;
//do the insert
this.oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();
//Get the last PersonID from the table and display it in TextBox
fnGetLastPersonID();
fnEnableButtonsNextPreviousLastFirst(true);
fnEnableToolbarButtons(true, "Edit");
fnEnableToolbarButtons(true, "Delete");
fnEnableToolbarButtons(false, "Save");
this.fnEnableDisableTextBox(false);
}else
{
MessageBox.Show("You have to fill the TextBoxes...",
"WARNING", MessageBoxButtons.OK,MessageBoxIcon.Warning);
this.textboxFirstname.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show("Error in inserting new record : " +
ex.Message, "Insert Error", MessageBoxButtons.OK,
MessageBoxIcon.Information);
fnRefreshDataSet();
} //try-catch
}
As you can see, there are some methods to be invoked in the method "fnSaveNewRecord()".
One of them is "fnGetLastPersonID()" which gets the last PersonID
from the table and displays it in the TextBox "textboxPersonID".
After a new record is inserted and the insert command
executed, I get the new PersonID of the new inserted record.
Here, you should pay attention to how the DataType AutoNumber
problem in the table "PersonTable" was solved. I chose a different way for the
AutoNumber issue.
First of all, you have to create the select command
only for the PersonID column. And then:
DataReader and the command with the connection and
string command (strCom).
while loop and get all values (contents) of the
column 0 (GetValue(0)).
object variable, convert it to integer
until the end of the column (0) values.
When the while-loop is finished, you will get the last
value in the first column (0) GetValue(0) outside while-loop.
Increment the PersonID by 1 and display it in the TextBox
"textboxPersonID".
Here is the code of the method "fnGetLastPersonID()":
private void fnGetLastPersonID()
{
string strCom = "Select Max(PersonID) from PersonTable" ;
OleDbCommand cmd =new OleDbCommand(strCom,this.oleDbConnection1);
OleDbDataReader reader;
reader =cmd.ExecuteReader() ;
int i=0 ; //how many records in the table?, only for test
int iPersonid=0; //the integer value of the first column(0) contents
while(reader.Read())
{
i++ ;
// GetValue(0) means: The contents
// of the first column(0)(PersonID) in the table
object obValue = reader.GetValue(0);
iPersonid = Convert.ToInt32(obValue.ToString());
}
i++ ;
this.textboxPersonID.Text=iPersonid.ToString(); //display it in TextBox
reader.Close(); //close the DataReader otherwise error
MessageBox.Show("Record with new PersonID: "+iPersonid.ToString()+
" inserted successfully", "Save New", MessageBoxButtons.OK,
MessageBoxIcon.Information); //inform the user
}
The current record is deleted with the help of the method "fnDeleteCurrentRecord()".
If the Delete button is clicked, you will be asked whether you want to delete
the record or not. If the answer is Yes, the delete command
will be executed and the method "fnRefreshDataSet()" is invoked.
The code snippet is as follows:
private void fnDeleteCurrentRecord
{
DialogResult dr=MessageBox.Show("Are you sure you want to delete" +
" this record ? ", "Confirm deleting",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr ==DialogResult.Yes)
{
string strDel="DELETE FROM PersonTable WHERE FirstName= '" +
this.textboxFirstname.Text.ToString() +"'";
fnExecuteQuery(strDel);
fnRefreshDataSet();
MessageBox.Show("Record deleted...");
}
else
MessageBox.Show("Record NOT deleted...");
}
If you navigate through the records, the current record is disabled to edit in the textboxes. As soon as you click the Edit button, all the textboxes will be enabled to edit, and the Update button will appear in the toolbar so that you can save the current updated record.
this.toolBarButtonNew.Enabled=false;
this.fnEnableDisableTextBox(true);
fnSetEditUpdateText("Update");
Here is the method "fnSetEditUpdateText()" which gets a
string as parameter:
private void fnSetEditUpdateText(string str)
{
this.toolBarButtonEdit.Text=str;
}
Here are the methods called if you click the Update button:
fnUpdateRecord();
fnSetEditUpdateText("Edit");
fnEnableToolbarButtons(true, "New");
this.fnEnableDisableTextBox(false);
First, the method "fnUpdateRecord()" is invoked to update the
record. Second, the text of the Update button will be changed to "Edit" with
the fnSetEditUpdateText("Edit") method. And then the method "fnEnableToolbarButtons(true,
"New")" is called to set the "New" button enabled. Lastly, the method
"fnEnableDisableTextBox(false)" will be called to
disable all the textboxes with the help of parameter "false".
There you have the code snippet of the method "fnUpdateRecord()":
private void fnUpdateRecord()
{
try
{
string strUpdateQuery = "update PersonTable set FirstName='"
+this.textboxFirstname.Text+"',LastName='"
+this.textboxLastname.Text+"' ,Title='"
+this.textboxTitle.Text+"' ,City='"
+this.textboxCity.Text+"' ,Country='"
+this.textboxCountry.Text +"' WHERE PersonID= "+this.textboxPersonID.Text;
fnExecuteQuery(strUpdateQuery);
fnRefreshDataSet();
MessageBox.Show("Record updated...");
}catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
With the Refresh button, all the textboxes are set to be disabled to edit. The
text of the Update button will be changed to "Edit", Save button is disabled (false),
Edit, Delete, and New buttons are enabled (true). With
the method "fnEnableButtonsNextPreviousLastFirst(true)",
all the navigation buttons (Next, Previous, Last, First) will be enabled. And
lastly, the method "fnRefreshDataSet()" is invoked to
refresh rows in the DataSet. In other words, the first
record is going to be displayed.
Here is the code snippet of the method fnRefreshDataSet():
private void fnRefreshDataSet()
{
this.dataSet11.Clear(); //clear the contents of dataset
this.oleDbDataAdapter1.Fill(this.dataSet11,"PersonTable");
}
Download Source
I hope this is a useful program for your ADO.NET database projects. If you wish, you can add new textboxes (Phone, Fax, Zip code etc.) on the Form to extend the program. I'd be glad to receive any suggestions or comments.
Good coding
Hüseyin Altindag