sfDatagrid c# CRUD datatable saving data to sqlserver

6 Replies
2 Participants

Created by
PA Paulo

Platform
WinForms

Platform
WinForms

Control
SfDataGrid

Created On
Nov 28, 2019 07:38 AM UTC

Last Activity On
Dec 12, 2019 12:28 PM UTC

Want to subscribe?
SIGN IN

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 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.Connection = conn;

insertcommand.Parameters.AddWithValue("@EmployeeName", item.ItemArray[0]);

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.