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. Image for the cookie policy date

sfDatagrid c# CRUD datatable saving data to sqlserver

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



6 Replies

GG Gowtham Gopalsamy Syncfusion Team November 29, 2019 05:09 AM

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    



PA Paulo November 30, 2019 07:42 PM

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


PA Paulo December 1, 2019 12:12 PM

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





GG Gowtham Gopalsamy Syncfusion Team December 2, 2019 09:53 PM

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    
 



PA Paulo December 11, 2019 10:44 AM

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. 


GG Gowtham Gopalsamy Syncfusion Team December 12, 2019 04:28 AM

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 


Loader.
Live Chat Icon For mobile
Up arrow icon