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 DataContext class 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.

Linq to Sql Generated Class

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.

GUI with Labels

                                                  

 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 ElementAt method 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 Text of 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.

Download Project: LinqToSqlDemo2