none
how to update datagrid values to database in wpf

    Question

  • Hi,

       I has a editable datagrid and i pass the values from sql database then i did some change in datagrid values .....now i want how to save the editable datagrid values to database ....please help me..

    my XAML code....

    <Window.Resources> <local:RawDataCollection x:Key="RawData"/> <Style x:Key="errorStyle" TargetType="{x:Type TextBox}"> <Setter Property="Padding" Value="-2"/> <Style.Triggers> <Trigger Property="Validation.HasError" Value="True"> <Setter Property="Background" Value="Red"/> <Setter Property="ToolTip" Value="{Binding RelativeSource={RelativeSource Self},Path=(Validation.Errors)[0].ErrorContent}"/> </Trigger> </Style.Triggers> </Style> </Window.Resources> <Grid> <DataGrid Name="dataGrid1" FontSize="20" RowHeaderWidth="27" ItemsSource="{StaticResource RawData}" AutoGenerateColumns="False"> <DataGrid.Columns> <DataGridTextColumn Header="ID" Width="100" Binding="{Binding ID}"/> <DataGridTextColumn Header="OpenCL" Width="100" EditingElementStyle="{StaticResource errorStyle}" Binding="{Binding OpenCL, ValidatesOnExceptions=True, StringFormat=d}"/> <DataGridTextColumn Header="UsedCL" Width="150" EditingElementStyle="{StaticResource errorStyle}" Binding="{Binding UsedCL, ValidatesOnExceptions=True, StringFormat=d}"/> </DataGrid.Columns> <DataGrid.RowValidationRules> <local:OpenCLValidationRule ValidationStep="UpdatedValue"/> </DataGrid.RowValidationRules> <DataGrid.RowValidationErrorTemplate> <ControlTemplate> <Grid Margin="0,-2,0,-2" ToolTip="{Binding RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type DataGridRow}}, Path=(Validation.Errors)[0].ErrorContent}"> <Ellipse StrokeThickness="0" Fill="Red" Width="{TemplateBinding FontSize}" Height="{TemplateBinding FontSize}" /> <TextBlock Text="!" FontSize="{TemplateBinding FontSize}" FontWeight="Bold" Foreground="White" HorizontalAlignment="Center" /> </Grid> </ControlTemplate> </DataGrid.RowValidationErrorTemplate> </DataGrid> </Grid>

    and C# code....

    public class RawDataCollection : ObservableCollection<RawData>
    { 
    	public RawDataCollection()
    	{
    		//Create Sql Conncetion
    		SqlConnection connection = new SqlConnection("Data Source=localhost\\SQLExpress;Initial Catalog=MySampleDB;Persist Security Info=True;User ID=sa;Password=LX9e@423");
    		connection.Open();
    		//Filter Rawdata
    		SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, OpenCL,UsedCL FROM RawData", connection);
    		DataTable dtRawData = new DataTable();
    		adapter.Fill(dtRawData);
    		connection.Close();
    		foreach(DataRow item in dtRawData.Rows)
    		{
    			RawData raw = new RawData() { ID=Convert.ToInt32(item[0].ToString()), OpenCL = Convert.ToInt32(item[1].ToString()),
    				UsedCL = Convert.ToInt32(item[2].ToString()) };
    			this.Add(raw);
    		}
    	}
    }
    
    public class RawData
    {
    	public int ID { get; set; }
    	public int OpenCL { get; set; }
    	public int UsedCL { get; set; }
    }
    
    public class OpenCLValidationRule : ValidationRule
    {
    	public override ValidationResult Validate(object value,
    		System.Globalization.CultureInfo cultureInfo)
    	{
    		RawData rawdata = (value as BindingGroup).Items[0] as RawData;
    		if (rawdata.UsedCL > rawdata.OpenCL)
    		{
    			return new ValidationResult(false,
    				"UsedCL must be smaller than OpenCL.");
    		}
    		else
    		{
    			return ValidationResult.ValidResult;
    		}
    	}
    }



    Tuesday, October 15, 2013 4:37 AM

Answers

  • Hi,

    I created a project on my end to achieve this function. In this scenario, I think we can use LINQ to SQL to set up the application’s Data Layer. NOTICE: You’d like to change the Data Layer to yours. Something looks like,

    XAML Code:

    <Window x:Class="WpfDataGridCRUD.MainWindow"
            xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
            xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
            xmlns:local="clr-namespace:WpfDataGridCRUD"
            Title="CRUD In DataGrid" Height="300" Width="300" ResizeMode="CanMinimize">
        <Window.Resources>
            <Style x:Key="errorStyle" TargetType="{x:Type TextBox}">
                <Setter Property="Padding" Value="-2"/>
                <Style.Triggers>
                    <Trigger Property="Validation.HasError" Value="True">
                        <Setter Property="Background" Value="Red"/>
                        <Setter Property="ToolTip" Value="{Binding RelativeSource={RelativeSource Self},Path=(Validation.Errors)[0].ErrorContent}"/>
                    </Trigger>
                </Style.Triggers>
            </Style>
        </Window.Resources>
        <Grid>
            <Grid.RowDefinitions>
                <RowDefinition Height="0.9*"/>
                <RowDefinition Height="0.1*"/>
            </Grid.RowDefinitions>
            <DataGrid x:Name="dgRawData" Margin="0" AutoGenerateColumns="False" RowEditEnding="dgRawData_RowEditEnding"
                             CanUserAddRows="True" CanUserDeleteRows="True">
                <DataGrid.Columns>
                    <DataGridTextColumn Binding="{Binding ID}" Width="50" Header="ID" IsReadOnly="True"/>
                    <DataGridTextColumn Binding="{Binding Path=OpenCL,UpdateSourceTrigger=PropertyChanged, ValidatesOnExceptions=True, 
                        StringFormat=d}" EditingElementStyle="{StaticResource errorStyle}" Width="100" Header="OpenCL"/>
                    <DataGridTextColumn Binding="{Binding Path=UsedCL,UpdateSourceTrigger=PropertyChanged, ValidatesOnExceptions=True, 
                        StringFormat=d}" EditingElementStyle="{StaticResource errorStyle}" Width="100" Header="UsedCL"/>
                </DataGrid.Columns>
    
                <DataGrid.RowValidationRules>
                    <local:OpenCLValidationRule ValidationStep="UpdatedValue"/>
                </DataGrid.RowValidationRules>
    
                <DataGrid.RowValidationErrorTemplate>
                    <ControlTemplate>
                        <Grid Margin="0,-2,0,-2" ToolTip="{Binding RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type DataGridRow}},
    					Path=(Validation.Errors)[0].ErrorContent}">
                            <Ellipse StrokeThickness="0" Fill="Red"  Width="{TemplateBinding FontSize}" Height="{TemplateBinding FontSize}" />
                            <TextBlock Text="!" FontSize="{TemplateBinding FontSize}" FontWeight="Bold" Foreground="White" HorizontalAlignment="Center" />
                        </Grid>
                    </ControlTemplate>
                </DataGrid.RowValidationErrorTemplate>
            </DataGrid>
            <TextBlock x:Name="txtStatus" HorizontalAlignment="Left" Margin="8,0,0,0" Grid.Row="1" 
                       TextWrapping="Wrap" VerticalAlignment="Center"/>
        </Grid>
    </Window>

    C# Code:

    public partial class MainWindow : Window
    {
    	public MainWindow()
    	{
    		InitializeComponent();
    		this.Loaded += MainWindow_Loaded;
    	}
    
    	private void MainWindow_Loaded(object sender, RoutedEventArgs e)
    	{
    		RawDataDataContext context = new RawDataDataContext();
    		var result = from rawdata in context.RawDatas
    					 select rawdata;
    		if (result.ToList().Count > 0)
    		{
    			txtStatus.Text = "Success: Read Operation";
    		}
    		dgRawData.ItemsSource = result.ToList();      
    	}
    
    	private void dgRawData_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
    	{
    		if (e.EditAction == DataGridEditAction.Commit)
    		{
    			RawDataDataContext context = new RawDataDataContext();
    
    			RawData rawdata = e.Row.DataContext as RawData;
    			//Condition: UsedCL > OpenCL
    			if (rawdata.UsedCL > rawdata.OpenCL)
    			{
    				txtStatus.Text = "UsedCL must be smaller than OpenCL. We cannot update!";
    				return;
    			}
    
    			var matchedData = (from raw in context.GetTable<RawData>()
    							   where raw.ID == rawdata.ID
    							   select raw).SingleOrDefault();
    
    			if (matchedData != null)
    			{
    				matchedData.UsedCL = rawdata.UsedCL;
    				matchedData.OpenCL = rawdata.OpenCL;
    				context.SubmitChanges();
    
    				txtStatus.Text = "Success: Data Updated";
    			}
    		}
    	}
    }
    
    public class OpenCLValidationRule : ValidationRule
    {
    	public override ValidationResult Validate(object value,
    		System.Globalization.CultureInfo cultureInfo)
    	{
    		RawData rawdata = (value as BindingGroup).Items[0] as RawData;
    		if (rawdata.UsedCL > rawdata.OpenCL)
    		{
    			return new ValidationResult(false,
    				"UsedCL must be smaller than OpenCL.");
    		}
    		else
    		{
    			return ValidationResult.ValidResult;
    		}
    	}
    }

    Something about how to add LINQ to SQL to our project, please refer to the link below,

    #CRUD Operation in Data Grid in WPF

    http://www.c-sharpcorner.com/uploadfile/dpatra/crud-operation-in-datagrid-in-wpf/

    For more references about LINQ, You can refer to the following links,

    http://msdn.microsoft.com/en-us/library/bb384428.aspx

    The result is shown in the following figure,

    BTW, I uploaded the source code to SkyDrive, You can download from here.

    Here is a useful reference, I highly recommended you to have a look at the link below,

    #Inserting, Updating, and Deleting from a WPF Data Grid

    http://blogs.u2u.be/diederik/post/2009/09/29/Inserting-Updating-and-Deleting-from-a-WPF-DataGrid.aspx

    Have a nice time!

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 16, 2013 2:11 AM