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");
}
}
}
}