Adding and Updating Records in an SQL Database Using LINQ
If the same window will be used for the form to update a record and add a record,
then the Accept button will be the single button to start the task. With this in
mind, the single button will need to have a condition to understand if the record
is new, or if it exists. I use an Update boolean variable to tell me if the information
on the WPF window is to update, or to add. If the update is true, then the information
in the WPF window is to be updated. Initially, the update was set when the information
was passed to the window to get the information inthe fields. If the table object
was null, the I set the Update flag to false.
The accept button will have code to direct the appropriate call like this:
private void
AcceptButton_Click(object sender, RoutedEventArgs e)
{
LINQTableName TableFields = new
LINQTableName();
TableFields = FillTableFieldsFromScreenfields(TableFields);
if (Update == true)
DatabaseCommands.UpdateARecord(TableFields);
else
DatabaseCommands.AddARecord(TableFields);
DialogResult = true;
}
The DatabaseCommands methods are located in my App.xaml.cs file as this is a good
place to put my general code (personal preference). You could just have easily created
a new folder in the project and put the classes there. Here is what the Add and
Update methods look like. You will notice how an exception is catched. If there
is an SQL error of some sort, the program will open a messagebox and explain the
error. The exception may be caused be an illegal constraint. In the update
class DatabaseCommands
{
public static void AddARecord(
LINQTableName TableObjectToPassIn)
{
using (
DataContextName DatabaseConnection = new
DataContextName())
{
DatabaseConnection.
LINQTableName.InsertOnSubmit(
TableObjectToPassIn);
try
{
DatabaseConnection.SubmitChanges();
}
catch (Exception e)
{
MessageBox.Show(
"This action is not possible. Please press OK to continue.\n\n" + e.Message,
"Delete Error",
MessageBoxButton.OK,
MessageBoxImage.Exclamation);
}
}
}
public static void UpdateARecord(
LINQTableName TableObjectToPassIn)
{
DataContextName DatabaseConnection = new
DataContextName();
LINQTableName ExistingRecord = (
from s in DatabaseConnection.LINQTable
where s.AUniqueID == TableObjectToPassIn.AUniqueID
select s).FirstOrDefault();
ExistingRecord.Filed1 =
TableObjectToPassIn.
Filed1;
ExistingRecord.Field2 =
TableObjectToPassIn.
Field2;
ExistingRecord.Field3 =
TableObjectToPassIn.
Field3;
...
try
{
DBconnexion.SubmitChanges();
}
catch (Exception e)
{
MessageBox.Show(
"This action is not possible. Please press OK to continue.\n\n" + e.Message,
"Delete Error",
MessageBoxButton.OK,
MessageBoxImage.Exclamation);
}
}
}
All of the bold and underlined coded text are variables that would need to match
according to your contect, objects, variables, etc.