We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

sfDatagrid c# CRUD datatable saving data to sqlserver

Thread ID:

Created:

Updated:

Platform:

Replies:

149506 Nov 28,2019 07:38 AM UTC Dec 12,2019 12:28 PM UTC WinForms 6
loading
Tags: SfDataGrid
Paulo
Asked On November 28, 2019 07:38 AM UTC

Can you provide me with a sample Winforms c# application with :

1) using a sfDatagrid
2) 4 or 5 columns, both numeric and text
2) datasource is a datatable generated from an sqlserver table
3) pls provide sample AutoGeneratingColumn method
4) please provide RowValidating, RowValidated, Record deleting,Record Deleted methods that validate and perform CRUD operations on the sqlserver table

Thank you

PS: haven't found a sample complete and clear, though I've googled around extensively



Gowtham Gopalsamy [Syncfusion]
Replied On November 29, 2019 01:09 PM UTC

Hi Paulo 
  
Thank you for using Syncfusion controls.   
 
We suspect that you are performing the add, update and delete the rows in SQL server table. We have attached the sample for your reference. 
 
AddRows: 
 
Please refer the below code snippet, 
  
this.sfDataGrid1.View.Records.CollectionChanged += Records_CollectionChanged; 
 
private void Records_CollectionChanged(object sender, System.Collections.Specialized.NotifyCollectionChangedEventArgs e) 
{ 
    if (e.Action == System.Collections.Specialized.NotifyCollectionChangedAction.Add) 
    { 
        var record = e.NewItems[0] as RecordEntry; 
        var data = record.Data as DataRowView; 
        var item = data.Row; 
        conn.Open(); 
        OleDbCommand insertcommand = new OleDbCommand("INSERT INTO Employee (ID,EmployeeName,EmployeeArea,EmployeeDesignation,EmployeeAge) VALUES (@ID,@EmployeeName,@EmployeeArea,@EmployeeDesignation,@EmployeeAge )"); 
        insertcommand.Parameters.AddWithValue("@EmployeeArea", item.ItemArray[1]); 
        insertcommand.Parameters.AddWithValue("@EmployeeDesignation", item.ItemArray[2]); 
        insertcommand.Parameters.AddWithValue("@EmployeeAge", item.ItemArray[3]); 
        insertcommand.Parameters.AddWithValue("@ID", item.ItemArray[4]); 
        int result = insertcommand.ExecuteNonQuery(); 
        myDataAdapter.InsertCommand = insertcommand; 
        conn.Close(); 
 
    } 
} 
 
UpdateRows: 
 
Please refer the below code snippet, 
  
OleDbCommand command = new OleDbCommand( 
                "UPDATE Employee SET EmployeeName = @EmployeeName, EmployeeArea = @EmployeeArea, EmployeeDesignation = @EmployeeDesignation, EmployeeAge = @EmployeeAge " + 
                "WHERE ID = @ID", conn); 
 
            // Add the parameters for the UpdateCommand. 
            command.Parameters.Add("@EmployeeName", OleDbType.WChar, 255, "EmployeeName"); 
            command.Parameters.Add("@EmployeeArea", OleDbType.WChar, 255, "EmployeeArea"); 
            command.Parameters.Add("@EmployeeDesignation", OleDbType.WChar, 255, "EmployeeDesignation"); 
            command.Parameters.Add("@EmployeeAge", OleDbType.Integer, 10, "EmployeeAge"); 
            command.Parameters.Add("@ID", OleDbType.Integer, 10, "ID"); 
            myDataAdapter.UpdateCommand = command; 
            conn.Close(); 
            this.sfDataGrid1.DataSource = myDataTable; 
 
 
sfDataGrid1.CurrentCellEndEdit += SfDataGrid1_CurrentCellEndEdit; 
 
private void SfDataGrid1_CurrentCellEndEdit(object sender, Syncfusion.WinForms.DataGrid.Events.CurrentCellEndEditEventArgs e) 
{ 
    if (!sfDataGrid1.IsAddNewRowIndex(sfDataGrid1.CurrentCell.RowIndex)) 
        myDataAdapter.Update(myDataTable); 
 
} 
 
DeleteRows: 
 
Please refer the below code snippet, 
  
sfDataGrid1.RecordDeleting += SfDataGrid1_RecordDeleting; 
 
private void SfDataGrid1_RecordDeleting(object sender, Syncfusion.WinForms.DataGrid.Events.RecordDeletingEventArgs e) 
{ 
    conn.Open(); 
    OleDbCommand deletecommand = new OleDbCommand("DELETE FROM Employee WHERE ID = @ID"); 
    deletecommand.Connection = conn; 
    var id = (e.Items[0] as DataRowView).Row.ItemArray[0]; 
    deletecommand.Parameters.AddWithValue("@ID", id); 
    myDataAdapter.DeleteCommand = deletecommand; 
    myDataAdapter.DeleteCommand.ExecuteNonQuery(); 
    conn.Close(); 
} 
 
AutoGeneratingColumn: 
 
By default, AutoGeneratingColumn is enabled. It will generate the columns based on the underlying collection. 
 
Please refer the below UG link, 
 
 
RowValidating: 
 
Please refer the below UG link, 
 
 
RowValidated: 
 
Please refer the below UG link, 
 
 
Record deleting: 
 
Please refer the below UG link, 
 
 
Record Deleted: 
 
Please refer the below UG link, 
 
 
Please refer the below sample link, 
 
 
Please let us know, If you require further assistance on this. 
 
Regards,     
Gowtham    


Paulo
Replied On December 1, 2019 03:42 AM UTC

Thank you very much, your sample was just what I needed to get me on the right track.

Have one problem with your sample though :

If you click "click here to add new row"  then double click to enter edit mode and then press enter (to test validation of blank input),
you get an error : NullReferenceException: Object reference not set to an instance of an object.
issued by : SfDataGrid1_RowValidating

please refer to attached screen capture image.

The same happens if you try to set the default column values for AddNewRow while initiating using method AddNewRowInitiating:
(I know it's not in your sample, but I tryed to add this feature because I need it)

var data = e.DataRow.RowData as DataRowView;   ...after this, data is assigned a null value and of course you get
 NullReferenceException: Object reference not set to an instance of an object.

with Records_CollectionChanged method everithing was ok, no null problems

How can I solve these problems? 

Thanks in advance





Attachment: Capturar_fb361e9e.zip

Paulo
Replied On December 1, 2019 08:12 PM UTC

Update :

Found this post:
https://www.syncfusion.com/forums/146978/canceling-row-edit-and-checking-empty-columns

but when you fill one cell and then commit  =>   the validation method "RowValidating" produces the same NullReferenceException




Gowtham Gopalsamy [Syncfusion]
Replied On December 3, 2019 05:53 AM UTC

Hi Paulo 
  
Thank you for your patience. 
 
You can achieve your requirement to check the add new row as the DataRow and you can cast the add new row as DataRow in RowValidating event. 
 
Please refer the below code snippet, 
  
this.sfDataGrid1.RowValidating += SfDataGrid1_RowValidating; 
 
private void SfDataGrid1_RowValidating(object sender, Syncfusion.WinForms.DataGrid.Events.RowValidatingEventArgs e) 
{ 
    if (!this.sfDataGrid1.IsAddNewRowIndex(e.DataRow.RowIndex)) 
    { 
        var data = e.DataRow.RowData as DataRowView; 
        if (data.Row.ItemArray[1].Equals("Jon")) 
        { 
            e.IsValid = false; 
            e.ErrorMessage = "EmployeeName Jon cannot be passed"; 
        } 
    } 
    else 
    { 
        var data = e.DataRow.RowData as System.Data.DataRow; 
        if (data.ItemArray[1].Equals("Jon")) 
        { 
            e.IsValid = false; 
            e.ErrorMessage = "EmployeeName Jon cannot be passed"; 
        } 
    } 
} 
 
Please refer the below sample link, 
 
 
Please let us know, If you require further assistance on this. 
 
Regards,     
Gowtham    
 


Paulo
Replied On December 11, 2019 06:44 PM UTC

Thanks for your help !!
Using a datatable turned up being too bug prone, ended up switching to an observable collection and after
having to deal with some difficuties I have my sfDataGrids running satisfactorily.

Hint: If you would provide complete running sample applications with CRUD, that would lead programmers to the right path to use your products, saving everybody a
huge amount of time. 

Gowtham Gopalsamy [Syncfusion]
Replied On December 12, 2019 12:28 PM UTC

Hi Paulo, 
 
Thank you for your patience. 
 
We have prepared sample for the functions of CRUD operations with observable collection. Please refer the sample in below link. 
 
Sample link: 
 
Also please refer to the below UG links to know more details of CRUD operations  
 
Please let us know, if you require further assistance on this. 
  
Regards, 
Gowtham 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon