PMS Tutorial 2: Insert, Update, and Delete Data From Local Database in C#

Introduction

In PMS Tutorial1, the method of connecting to a local database in C# is introduced, here the code of PMS Tutorial1 will be updated. So before beginning you should refer to PMS Tutorial1 to understand PMS Tutorial2. In this tutorial we will learn the basics of inserting, updating and deleting records from a table in a local database.

2.1 Update the Drugs table schema by adding more fields and more records

Update the schema of the drugs table to add more fields. Right-click on the Drugs Table then select "Edit Table Schema".



From the Edit Table window, do the following:

1. Add a field called “Expiry Date” that represents the expiry date of the drug.
2. Change the Data Type of the Expiry Date to be a Datetime
3. Add two more fields
4. Change their corresponding Data Type as preferred
5. Click OK to close the form and accept the changes.

Now, explore the contents of the Drugs table and add the missing data. For example, the expiry date field is empty since it is recently added to the table. To make the database alteration operations more interesting add more records as shown in the following figure.



2.2 Drugs Form Design

Open Form1 in design view. Make sure you selected Form1 by clicking into an empty area of the form or simply by clicking its title bar, from the properties window change Text to be Drugs. Then choose GroupBox from Toolbox's container tab as shown in the figure.



The group box is a container that holds any type of control as needed in the application. From the group box properties change its Name to gbDrugs, and change Text to “Drug Detail”. From the toolbox add a Label, Text Box and DateTimePicker as shown in the figure.



Select the Label and the TextBox together then copy once and paste 3 times. Rearrange all the resulting controls as shown in the following figure.



Change the properties of each control, if the properties window doesn't appear, right-click on the control and select properties from the context window, the properties should be changed as follows:

1. Change the label1 Name to "lblID", and Text to “Drug ID”
2. Change TextBox1 (more specifically the TextBox beside "lblID"), change its Name to be "txtDrugID"
3. Change the label2 Name to "lblName" and Text to “Name”.
4. Change TextBox2 (more specifically the TextBox beside "lblName"), change its Name to be "txtDrqugName".
5. Change the label3 Name to be "lblExpiryDate" and Text to “Expiry Date”.
6. Change the DateTimePickers Name to be "dtpExpiryDate"
7. Change the label4 Name to be "lblCompany" and text to “Company”.
8. Change Label5 Name to be "lblType" and text to “Drug Type”.

The final arrangements of the controls should be as shown in the following figure:



Add 3 more controls. This time add 3 Buttons, from the toolbox select a Button and drag it to the form, then copy once and paste it 2 times. The three button properties should be changed as follows:

1. Button1 Name should be changed to “btnInsert”, and its Text to "Insert"
2. Button2 Name should be changed to “btnUpdate”, and its Text to "Update"
3. Button3 Name should be changed to “btnDelete”, and its Text to "Delete"

The final arrangements of the controls on the form should be as shown in the following figure:



2.3 Display the Selected Record from the DataGridView

The objective is to display any record when selected in the DataGridView. So if the user clicks on any record in the DataGridView the corresponding data will be displayed on the controls in the Drug Detail container. The selection by default is done cell by cell. For accurate selection, the selection scheme of the DataGridView must be changed to Full Row Selection, this is done by selecting FullRowSelect from SelectionMode property of the dgvDrugs as shown in the following figure.



The event of clicking the DataGridView must be added to the list of its events. This is done by the following procedure:

1. Click on any place inside the dgvDrugs
2. Click the events button
3. Double-click on the blank area beside the CellClick event.

The code editor must be opened.



Add the following code for the CellClick procedure:

private void dgvDrugs_CellClick(object sender, DataGridViewCellEventArgs e)
{
txtID.Text = dgvDrugs.Rows[e.RowIndex].Cells["ID"].Value.ToString();
txtName.Text = dgvDrugs.CurrentRow.Cells["Name"].Value.ToString();
if(dgvDrugs.CurrentRow.Cells["ExpiryDate"].Value.ToString().Length > 0)
dtpExpiryDate.Value = Convert.ToDateTime(dgvDrugs.CurrentRow.Cells["ExpiryDate"].Value.ToString());
txtCompany.Text = dgvDrugs.CurrentRow.Cells["Company"].Value.ToString();
txtType.Text = dgvDrugs.CurrentRow.Cells["Type"].Value.ToString();
}

In the preceding procedure, e.RowIndex holds the current position of the selected row; this also can be changed by CurrentRow that holds a pointer to the current row. To call a specific column, Cells [Column Name] is used.

The if statement tests the database field (ExpiryDate), if the user leaves it blank or it is retrieved from the database with a blank value then the date will not be displayed on the dtpExpiryDate. Notice the use of Convert.ToDateTime to convert a string to date time.

Run the program now to ensure everything is working well. When a cell is clicked, the corresponding record is displayed in the Drug Detail groupbox.

2.4 Update the DBConnection Class to Hold Insert, Update and Delete Functions

The DBConnection class is updated by including three functions. The first function is drugInsert() that takes the drug info and inserts it into the database. The second function is drugUpdate that takes the new info of the record and updates it. The third function is drugDelete that takes the drug ID and deletes its record from the database. The following code represents the functions inside the DBConnection Class.

2.4.1 Insert Record

The insertion in C# is done by creating a new ADO.Net object called SqlCeCommand. It holds the SQL command that will be applied onto the database. The SqlCeCommand is initialized by the SQL statement, the current connection, and the command type (Text or Stored Procedure). The following function is added to the DBConnection class:

public int drugInsert(int ID,string Name,DateTime expDate,string company,string type)
{
try
{
string strCommand = "INSERT INTO Drugs(ID,Name,ExpiryDate,Company,Type) VALUES(@ID,@Name,@ExpiryDate,@Company,@Type)";
SqlCeCommand cmdInsert = new SqlCeCommand();
cmdInsert.Connection = conn;
cmdInsert.CommandType = CommandType.Text;
cmdInsert.CommandText = strCommand;
cmdInsert.Parameters.AddWithValue("@ID", ID);
cmdInsert.Parameters.AddWithValue("@Name", Name);
cmdInsert.Parameters.AddWithValue("@ExpiryDate",expDate );
cmdInsert.Parameters.AddWithValue("@Company", company);
cmdInsert.Parameters.AddWithValue("@Type", type);
return cmdInsert.ExecuteNonQuery();
}
catch (SqlCeException e)
{
MessageBox.Show(e.Source + "\n" + e.Message + "\n" + e.StackTrace);
return -1;
}
}

The next step is to connect this command with the form. This can be done by opening Form1 and double-clicking on the "Insert" button. The code editor will be opened for the following code to be written, that is the Click procedure of the button

private void btnInsert_Click(object sender, EventArgs e)
{
if (txtID.Text.Length > 0 && txtName.Text.Length > 0)
{
int k = DBConn.drugInsert(Int32.Parse(txtID.Text), txtName.Text, dtpExpiryDate.Value.Date, txtCompany.Text, txtType.Text);
if (k > 0)
{
dgvDrugs.DataSource = DBConn.getAllDrugs();
}
else
{
MessageBox.Show("No record inserted");
}
}
else
{
MessageBox.Show("Please fill in ID and Name of the drug");
}
}