Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL WPF LINQ DataGrid
Hello everyone, I am trying to create a Datagrid in my WPF test project that will update existing data and add new data.
 
My Sql table has 2 columns, ID (int) and Test1 (nvchar 50).
 
Here is my xaml:
 
 
><Window x:Class="Testing.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <DataGrid x:Name="dgTest" DisplayMemberPath="Testing" SelectedValue="{Binding ElementName=Test1, Path=SelectedItem.ID, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" SelectedValuePath="ID" HorizontalAlignment="Left" Margin="10,10,0,0" VerticalAlignment="Top" Height="268" Width="498"/>
        <Button x:Name="btnSave" Content="Save" HorizontalAlignment="Left" Margin="222,291,0,0" VerticalAlignment="Top" Width="75" Click="btnSave_Click"/>
 
    </Grid>
</Window></
 
 
Here is my Code behind:
 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Collections.ObjectModel;
 
namespace Testing
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            // Initializes all the objects.

            InitializeComponent();
 
            // Runs my Loadtest method.

            LoadTest();
        }
 
        #region Variables
 

        #endregion
 
        #region Constructors
 
        // Creates a new instance of my data context
        
        dcTestDataContext dcTest = new dcTestDataContext();
 
        #endregion
 
        #region Classes
 
        public class ObservableTest : ObservableCollection<Test>
        {
            public ObservableTest(dcTestDataContext dcTest)
            {
                //Creates an ObservableCollection class
                //Then adds the Test1 field and ID field from the ling to sql table.

                foreach (Test Test1 in dcTest.Tests)
                {
                   this.Add(Test1);
                }
 
            }
        }
 
        #endregion
 
        #region Methods
 
        private void LoadTest()
        {  
  
            //Creates an object of my ObservableTest class
            //Assigns the data within my class to the item source of the datagrid

            ObservableTest oTest = new ObservableTest(dcTest);
            dgTest.ItemsSource = oTest.ToList();
 
        }
 

        private void SaveTest()
        {
            // Create a new instance of my ObservableTest class.
            // Create a new instance of Test and assign it the value of the selected item.
            // Create a variable i and assign it the ID of the selected item.

            // Creates a new instance of Test and iterates through the table to match the ID of the
            // selected item and the ID in the dcTest (datacontext).
            // Sets the Test1 field in the table with the Test1 field from the dcTest.

            // Submits the changes from dgTest to the dcTest and then creates a message box
            // to tell the user the update was succesful, before refreshing the datagrid.

            // Creates a messagebox to catch any exceptions, which gives the details of the exception.

                try
                {
 
                    ObservableTest ocTest = new ObservableTest(dcTest);
 
                    Test testRow = dgTest.SelectedItem as Test;
 
                    int i = testRow.ID;
 
                    Test test = (from p in dcTest.Tests
 
                                 where p.ID == testRow.ID
 
                                 select p).Single();
 
                    test.Test1 = testRow.Test1;
 
                    dcTest.SubmitChanges();
 
                    MessageBox.Show("Row Updated Successfully.");
                    LoadTest();
 
                }
 
                catch (Exception Ex)
                {
 
                    MessageBox.Show(Ex.Message);
 
                    return;
                }
            }
 

 
        #endregion
 
        #region Events
 
        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            // Runs my Savetest method, when the user clicks the save button.

            SaveTest();
        }
 
        #endregion       
 
      
    }
}
 

So my outcome is that my sql table will not update, but everything within my project works fine. I have tried stepping through my code numerous times, but to no availe.
 
Any help would be greatly appreciated.
 

 
Kind regards
 
Dean
Posted 21-Mar-13 20:32pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

well, i think this may answer your question http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/a29ae103-f516-4252-9c90-1aa4c5874a43[^]and here is an implementaion of the sugested workaround http://www.dotnetcurry.com/ShowArticle.aspx?ID=563[^]. by the way http://msdn.microsoft.com/en-us/library/bb546190.aspx[^] says
Quote:
LINQ to SQL translates LINQ queries to SQL for execution on a database. The results are strongly typed IEnumerable. Because these objects are ordinary common language runtime (CLR) objects, ordinary object data binding can be used to display the results. On the other hand, change operations (inserts, updates, and deletes) require additional steps.
see also
Quote:
Object States and Change-Tracking

debugging: enable DataContext logging like
db.Log = Console.Out;
and, i read this
Quote:
LinqToSql doesn’t work on the tables which do not have primary key defined, you can read the data of a table using LinqToSql but you cannot update the table data with out having primary key defined, because it doesnt implement INotifyChanged interface for the table which do not have primary key.
  Permalink  
v4
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

It has a primary key.
 
I solved the issue, with this.
 
private void NewSaveTest()
{
 
try
{
ObservableTest ocTest = new ObservableTest(dcTest);
 
Test testRow = dgTest.SelectedItem as Test;
 
dcTest.Tests.InsertOnSubmit(testRow);
 
ocTest.Add(testRow);
 
dcTest.SubmitChanges();
 
MessageBox.Show("Row Updated Successfully.");
LoadTest();
 
}
 
catch (Exception Ex)
{
 
MessageBox.Show(Ex.Message);
 
return;
}
 
}
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 555
1 Kornfeld Eliyahu Peter 409
2 Maciej Los 359
3 DamithSL 196
4 OriginalGriff 188
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,466
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,897


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100