Modifying Database with LINQ to SQL
Mapping the database tables and its records to their corresponding LINQ to SQL classes makes it even easier to manipulate databases. Once LINQ to SQL classes is generated, you can do the modification directly to objects of those classes. For adding, the DataContextclass offers the InsertOnSubmit and pass the new object of the row class to add. When deleting, we can use the DeleteOnSubmit and pass the specified object to delete. We can directly modify the properties of an object representing a record if we want to update it. All of this operations will not immediately affect the actual tables and records in the database. We need to call the SubmitChanges method of the DataContext class first. To access an element of the property representing the Table, we can use the ElementAt method which accepts an integer index and returns the corresponding record object.
Our example application will allow the user to check details of every record, and allows you to add, delete and update records using LINQ to SQL and the method offered by the DataContext and Table<TEntity> classes. For the following example we will create, we will be using a sample database containing a single table named Persons which contains some records.
Download Sample Database
Once you downloaded the rar file, open it and extract the database file inside it to a location that you can easily find. We will create an application that queries one person at a time and allows us to move through every record using navigation buttons. The application will also allow the user to add, delete, or update records. You will see how easy this can be done using LINQ to SQL classes.
Create a new Windows Forms Application and name the project LinqToSqlDemo2. Create a LINQ to SQL and name it Sample.dbml.. Go to the Database Explorer and click the Connect to Database button. Choose Microsoft SQL Server Database file and click OK then browse for the location of the Sample.mdf file you have downloaded. It will now show up in the Database Explorer as a separate node. Open the nodes and inside the Tables node, drag the Person table to the Object Relational Designer. Click Yes to accept the option to copy the database file to your project folder.
You will now be presented with a class inside the Object Relational Designer named Person.
As you can see, it only has several properties. We will now create the GUI that we will use to present details of each record and also to add or delete records. Add the necessary controls and their corresponding text as shown in the GUI below. The numbers will indicate the corresponding names to be used by the controls.
| Number | Name |
|---|---|
| 1 | firstButton |
| 2 | prevButton |
| 3 | nextButton |
| 4 | lastButton |
| 5 | idTextBox |
| 6 | firstNameTextBox |
| 7 | lastNameTextBox |
| 8 | ageTextBox |
| 9 | addButton |
| 10 | deleteButton |
| 11 | updateButton |
Set the idTextBox‘s ReadOnly property to true so it can’t be modified as it will show a primary key value. You can also set the StartPosition property of the form to CenterScreen.
The buttons above will be used to move to the first, previous, next, or last record in the Person table. The text boxes will be used to display the values of every field of the current person. The buttons below are used to Add, Delete, and Update records. Clicking the addButton will clear the textboxes so it can accept new values from the user to be added to the table. Clicking the deleteButton will delete the current record being shown. Clicking updateButton will update the record being shown if some of its details were modified.
We will be using the following code for our application:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace LinqToSqlDemo2
{
public partial class Form1 : Form
{
private int currentIndex;
private int minIndex;
private int maxIndex;
private bool addPending;
private SampleDataContext database;
private IEnumerable<Person> persons;
public Form1()
{
InitializeComponent();
database = new SampleDataContext();
persons = from p in database.Persons
select p;
currentIndex = 0;
minIndex = 0;
maxIndex = persons.Count() - 1;
DisableButtons();
addPending = false;
}
private void Form1_Load(object sender, EventArgs e)
{
ShowPersonInfo(currentIndex);
}
private void firstButton_Click(object sender, EventArgs e)
{
ShowPersonInfo(minIndex);
currentIndex = minIndex;
DisableButtons();
}
private void lastButton_Click(object sender, EventArgs e)
{
ShowPersonInfo(maxIndex);
currentIndex = maxIndex;
DisableButtons();
}
private void prevButton_Click(object sender, EventArgs e)
{
ShowPersonInfo(--currentIndex);
DisableButtons();
}
private void nextButton_Click(object sender, EventArgs e)
{
ShowPersonInfo(++currentIndex);
DisableButtons();
}
private void addButton_Click(object sender, EventArgs e)
{
if (addPending == false)
{
ClearFields();
int newIDnewID = persons.Count() == 0 ? 1 : persons.Last().PersonID + 1;
idTextBox.Text = newIDnewID.ToString();
addButton.Text = "Done";
addPending = true;
}
else
{
try
{
//Create new person
Person newPersonnewPerson = new Person();
newPersonnewPerson.PersonID = maxIndex + 1;
newPersonnewPerson.FirstName = firstNameTextBox.Text;
newPersonnewPerson.LastName = lastNameTextBox.Text;
newPersonnewPerson.Age = Int32.Parse(ageTextBox.Text);
//Add new Person
database.Persons.InsertOnSubmit(newPersonnewPerson);
database.SubmitChanges();
maxIndex++;
currentIndex = maxIndex;
DisableButtons();
MessageBox.Show("Successfully added to database.", "Success",
MessageBoxButtons.OK, MessageBoxIcon.Information);
addButton.Text = "Add";
addPending = false;
}
catch
{
MessageBox.Show("Failed to add new record to database. Make sure " +
"that every field is not empty and in a correct " +
"format", "Failed",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void deleteButton_Click(object sender, EventArgs e)
{
try
{
database.Persons.DeleteOnSubmit(persons.ElementAt(currentIndex));
database.SubmitChanges();
maxIndex--;
if (currentIndex > maxIndex)
currentIndex--;
MessageBox.Show("Successfully removed from the database.", "Success",
MessageBoxButtons.OK, MessageBoxIcon.Information);
ShowPersonInfo(currentIndex);
DisableButtons();
}
catch
{
MessageBox.Show("Unable to delete.", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void updateButton_Click(object sender, EventArgs e)
{
try
{
Person modifiedPerson = persons.ElementAt(currentIndex);
modifiedPerson.FirstName = firstNameTextBox.Text;
modifiedPerson.LastName = lastNameTextBox.Text;
modifiedPerson.Age = Int32.Parse(ageTextBox.Text);
database.SubmitChanges();
MessageBox.Show("Update success!", "Success",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch
{
MessageBox.Show("Error on updating.", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void ShowPersonInfo(int index)
{
if (persons.Count() == 0)
{
ClearFields();
MessageBox.Show("Nothing to show.", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Person currentPerson = persons.ElementAt(index);
idTextBox.Text = currentPerson.PersonID.ToString();
firstNameTextBox.Text = currentPerson.FirstName;
lastNameTextBox.Text = currentPerson.LastName;
ageTextBox.Text = currentPerson.Age.ToString();
}
private void DisableButtons()
{
if (persons.Count() <= 1)
{
firstButton.Enabled = false;
prevButton.Enabled = false;
nextButton.Enabled = false;
lastButton.Enabled = false;
return;
}
if (currentIndex == minIndex)
{
firstButton.Enabled = false;
prevButton.Enabled = false;
nextButton.Enabled = true;
lastButton.Enabled = true;
}
else if (currentIndex == maxIndex)
{
firstButton.Enabled = true;
prevButton.Enabled = true;
nextButton.Enabled = false;
lastButton.Enabled = false;
}
else if (currentIndex > minIndex && currentIndex < maxIndex)
{
firstButton.Enabled = true;
prevButton.Enabled = true;
nextButton.Enabled = true;
lastButton.Enabled = true;
}
}
private void ClearFields()
{
idTextBox.Text = String.Empty;
firstNameTextBox.Text = String.Empty;
lastNameTextBox.Text = String.Empty;
ageTextBox.Text = String.Empty;
firstNameTextBox.Focus();
}
}
}Example 1
Lines 10-15 declares some required variables that we will use throughout our program. Line 10 declares a variable that will hold the current index of the person to show. Line 11-12 declares variables that will be used to hold the minimum and maximum possible indices so we can avoid IndexOutOfRangeExceptions and disable specific navigation buttons. Line 13 will be used by the addButton later as we will see. Line 14 declares a SampleDataContext object which is the corresponding DataContext of the Sample database. We will use this object to call methods for adding, deleting, updating, and retrieving records from the tables of the Sample database. Line 15 declares an object of type IEnumerable<Person> which will hold all the person records queried from the database. Recall the results of a LINQ query implements IEnumerable<T> so we can simply use this type in the declaration of the object in line 15. This is so we don’t have to query all the records everytime we need to use them. We can simply use this object throughout our program.
We will first discuss the utility methods that will be used by the application. The ClearFields method (line 205-212) simply clears every text field and sets the focus to the firstNameTextBox. Method DisableButtons (line 172-203) will be used to disable navigation buttons once the currentIndex reached the minimum or maximum bounds. This is to prevent the user to move when no more elements are available to show. It also checks if there are 1 or 0 records left so it can disable all the navigation buttons to prevent the user from moving. The ShowPersonInfo method (153-169) accepts an index and retrieve a Person object using the specified index. Lines 155-161 first checks if the number of elements or records in the Person property is empty using the Count method. If so, we print an error message and return to the caller to prevent the other codes from of the method from executing. In line 163, we used the ElementAtmethod of the Person property to retrieve the right object using the index as an argument to the ElementAt method. We then displayed the properties of the retrieved Person object to their corresponding text boxes.
Now let’s go inside the Form1‘s constructor (Line 17 – 33). Line 21 creates an instance of the SampleDataContext so it can now be used to perform operations to the database. Lines 22-23 is a simple LINQ query that selects all the person from the Person property of the SampleDataContext instance which contains every record of a person. We then set the currentIndex to 0 to indicate the program should initially show the first record. Lines 27-28 sets the minIndex and maxIndex which holds the minimum and maximum indices respectively. We simply assign the value 0 to the minIndex. The maxIndex was calculated by obtaining the number of person records in the Person property and subtracting by 1 because indices are 0-based. We called the DisableButtons method that we created to disable the buttons that the user won’t need as of now. We also set the addPending to false. This will be used by the handler of the addButton later.
Go back to the desinger and double click the form’s title bar to generate an event handler for its Load event (lines 35-38). Inside the handler, we called the ShowPersonInfo and passed the current value of the currentIndex which is 0 to show the first record in the text boxes.
We will now add the Click event handlers for the navigation buttons. In the Designer, double click the firstButton. Use the codes in lines 42-44 for the event handler. The first line calls the ShowPersonInfo and passing the minIndex value to show the very first record. The value of currentIndex is then set back to the value of minIndex. We called the DisableButtons to disable the firstButton and prevButton. The event handler for lastButton (47-52) is the same as the firstButton‘s only that it shows the details of the last record using the maxIndex variable. The prevButton and nextButton‘s Click event handlers (54-64) are also nearly identical. The both call the ShowPersonInfo method and pass the currentIndex to show the records at that specified index. We also increment or decrement the currentIndex right inside the method call to adjust value of the currentIndex.
The event handler for addButton (66-106) has the following functionality. The addbutton will have two states. The first state is when addPending is set to false. Clicking the button in this state will first clear the text boxes(line 70). It will then calculate the next possible PersonID to assign for the soon to be added new reacord (71). The calculated new id is displayed to the appropriate text box. The Textof the addButton is changed to “Done” to indicate the it is waiting for the user to finish providing values for each fields of the new person to add. The addPending variable is then set to true to transfer the button to its second state. The second state of the addButton is when it is waiting for the user to finish providing values. When the user hits again the addButton while in this state, it will now execute commands to add the new record to the database. Note that everything that will be used to add the new record to the database is enclosed in a try block so we can catch exceptions that might occur including FormatExceptions or ChangeConflictExceptions which is thrown by the SubmitChanges method when it encounters an error. Lines 81-85 creates a new Person object and assign its properties to values of the text boxes. Line 88 uses the Table<TEntity>.InsertOnSubmit method and passes the newly created Person object so it will be added to the database when the DataContext.SubmitChanges method is executed. The SubmitChanges method is called in line 89 to submit that new change that was made, that is, that adding of a new record to the list of Persons in the Persons table. Line 90 adjusts the value of maxIndex by incrementing it by one since the number of records was increased by 1. We set the currentIndex to the value of the maxIndex, called DisableButtons method, and print a success message telling that the adding of the new record to the database was successful. We changed back the button’s caption to “Add” and set the addPending to false so it can accept again new records once clicked. The catch block simply shows an error message telling the user that problems occur while adding the new record to the database.
The handler for the deleteButton (108-131) also encloses it’s code in a try block to prevent uncaught exceptions that might occur. Line 112 uses the DeleteOnSubmit method which accepts the object to delete from table. To retrieve the right Person object to delete, we used the ElementAt method and passed the currentIndex to it. Since a change was made, we called the SubmitChanges method to send the change to the actual database table. We decrement the maxIndex by 1. We also adjust the currentIndex to an appropriate value. Decreasing the maxIndex while while the currentIndex will make currentIndex greater than the maxIndex. Therefore, we also decrement the value of the currentIndex to match the maxIndex. A person right before the deleted person will then be displayed.
The updateButton will be used to update the values of the currently displayed person. When a person’s details is displayed, you can change the values in the text boxes, and press the updateButton to update the corresponding record in the database. The handler for the updateButton (133-151) creates a Person that will hold a reference to the currently displayed Person. The properties of this Person is then changed using the new values that the user may have provided. We then immediately called the SubmitChanges method to send the changes and update the corresponding record in the database table. Note that there is no method such as UpdateOnSubmit which could have been similar to the two methods we have seen. You simply modify the values of the properties and call the SubmitChanges method.