CSharp

GridView Sort

Table Student

CREATE TABLE Student(
	StudentId INT NOT NULL PRIMARY KEY,
	FirstName NVARCHAR(16) NOT NULL,
	LastName NVARCHAR(32) NOT NULL,
	Gender BIT NOT NULL,
	DateOfBirth DATE NOT NULL
);
GO

Type Student

CREATE TYPE StudentType AS TABLE(
	StudentId INT,
	FirstName NVARCHAR(16),
	LastName NVARCHAR(32),
	Gender BIT,
	DateOfBirth DATE
);
GO

Procedure AddStudent

CREATE PROC AddStudent(
	@table StudentType  readonly
)
AS
	INSERT INTO Student (StudentId, FirstName, LastName, Gender, DateOfBirth) 
		SELECT * FROM @table;
GO

Procedure SaveStudent

CREATE PROC SaveStudent(
	@table StudentType  readonly
)
AS
BEGIN
	MERGE INTO Student AS des USING @table AS src ON des.StudentId = src.StudentId
	WHEN MATCHED THEN UPDATE SET des.FirstName = src.FirstName, des.LastName = src.LastName, des.Gender = src.Gender, des.DateOfBirth = src.DateOfBirth
	WHEN NOT MATCHED BY TARGET THEN INSERT (StudentId, FirstName, LastName, Gender, DateOfBirth) 
		VALUES(src.StudentId, src.FirstName, src.LastName, src.Gender, src.DateOfBirth);
		SELECT * FROM @table;
END
GO

Class StudentRepository

internal class StudentRepository : Repository{
    public int Add(DataTable table){
        using SqlConnection connnection = new SqlConnection(connectionString);
        using SqlCommand command = new SqlCommand("AddStudent", connnection);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@Table", table));
        connnection.Open();
        return command.ExecuteNonQuery();
    }
    public int Save(DataTable table){
        using SqlConnection connnection = new SqlConnection(connectionString);
        using SqlCommand command = new SqlCommand("SaveStudent", connnection);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@Table", table));
        connnection.Open();
        return command.ExecuteNonQuery();
    }
    public DataTable GetDataTable(){
        using SqlConnection connnection = new SqlConnection(connectionString);
        using SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Student", connnection);
        DataTable table = new DataTable();
        adapter.Fill(table);
        return table;
    }
}

Data GridView

public partial class FormStudent : Form{
    StudentRepository studentRepository = new StudentRepository();
    public FormStudent(){
        InitializeComponent();
    }
    private void FormStudent_Load(object sender, EventArgs e){
        gvStudent.DataSource = studentRepository.GetDataTable();
    }
    private void btnSave_Click(object sender, EventArgs e){
        if(gvStudent.DataSource != null && gvStudent.DataSource is DataTable table){
            int ret = studentRepository.Save(table);
            if (ret > 0){
                MessageBox.Show($"Insert success {ret} Rows");
            }
        }
    }
}