c# winform部门管理系统
数据库SQL语句脚本代码
CREATE TABLE Department(
DepartmentID INT PRIMARY KEY IDENTITY ( 1 , 1 ) ,
Name NVARCHAR( 50 ) NOT NULL
) ;
SELECT * FROM Department
INSERT INTO Department ( Name) VALUES ( '人力资源部' ) ;
INSERT INTO Department ( Name) VALUES ( '技术部' ) ;
INSERT INTO Department ( Name) VALUES ( '市场部' ) ;
INSERT INTO Department ( Name) VALUES ( '销售部' ) ;
CREATE TABLE Employees_Lists(
EmployeeID INT PRIMARY KEY IDENTITY ( 1 , 1 ) ,
Name NVARCHAR( 50 ) NOT NULL ,
DepartmentID INT ,
FOREIGN KEY ( DepartmentID) REFERENCES Department( DepartmentID)
) ;
INSERT INTO Employees_Lists ( Name, DepartmentID) VALUES ( '张三' , 1 ) ;
INSERT INTO Employees_Lists ( Name, DepartmentID) VALUES ( '李四' , 1 ) ;
INSERT INTO Employees_Lists ( Name, DepartmentID) VALUES ( '王五' , 2 ) ;
INSERT INTO Employees_Lists ( Name, DepartmentID) VALUES ( '赵六' , 2 ) ;
INSERT INTO Employees_Lists ( Name, DepartmentID) VALUES ( '孙七' , 3 ) ;
INSERT INTO Employees_Lists ( Name, DepartmentID) VALUES ( '周八' , 4 ) ;
SELECT a. Name AS 员工, b. Name AS 部门 FROM Employees_Lists a RIGHT JOIN Department b
ON a. DepartmentID = b. DepartmentID
C#代码实现
添加部门窗口
using System ;
using System. Data. SqlClient ;
using System. Windows. Forms ;
namespace WindowsFormsApp1
{
public partial class 添加部门 : Form
{
private string connectionString = "server=172.0.0.1;uid=sa1;pwd=xyz@x123456;database=test" ;
enum Mode
{
Add,
Edit
}
private Mode currentMode;
private string currentDepartmentName;
public 添加部门( )
{
InitializeComponent ( ) ;
ConfigureForm ( Mode. Add, null ) ;
}
public 添加部门( string departmentName)
{
InitializeComponent ( ) ;
ConfigureForm ( Mode. Edit, departmentName) ;
}
private void ConfigureForm ( Mode mode, string departmentName)
{
this . MaximizeBox = false ;
this . FormBorderStyle = FormBorderStyle. FixedSingle;
currentMode = mode;
currentDepartmentName = departmentName;
if ( mode == Mode. Add)
{
this . Text = "添加部门" ;
}
else if ( mode == Mode. Edit)
{
this . Text = "修改部门 - " + departmentName;
departmentNameTextBox. Text = departmentName;
btn_adddepartmentName. Text = "修改" ;
}
}
private void btn_adddepartmentName_Click ( object sender, EventArgs e)
{
string departmentName = departmentNameTextBox. Text. Trim ( ) ;
if ( ! string . IsNullOrEmpty ( departmentName) )
{
using ( SqlConnection conn = new SqlConnection ( connectionString) )
{
string checkQuery = "SELECT COUNT(*) FROM Department WHERE Name = @Name" ;
using ( SqlCommand checkCmd = new SqlCommand ( checkQuery, conn) )
{
checkCmd. Parameters. AddWithValue ( "@Name" , departmentName) ;
conn. Open ( ) ;
int count = ( int ) checkCmd. ExecuteScalar ( ) ;
if ( count > 0 && currentMode == Mode. Add)
{
MessageBox. Show ( "该部门已存在!" + departmentName) ;
}
else
{
if ( currentMode == Mode. Add)
{
string insertQuery = "INSERT INTO Department (Name) VALUES (@Name)" ;
using ( SqlCommand insertCmd = new SqlCommand ( insertQuery, conn) )
{
insertCmd. Parameters. AddWithValue ( "@Name" , departmentName) ;
insertCmd. ExecuteNonQuery ( ) ;
MessageBox. Show ( "部门添加成功!" ) ;
}
}
else if ( currentMode == Mode. Edit)
{
string updateQuery = "UPDATE Department SET Name = @Name WHERE Name = @OldName" ;
using ( SqlCommand updateCmd = new SqlCommand ( updateQuery, conn) )
{
updateCmd. Parameters. AddWithValue ( "@Name" , departmentName) ;
updateCmd. Parameters. AddWithValue ( "@OldName" , currentDepartmentName) ;
updateCmd. ExecuteNonQuery ( ) ;
MessageBox. Show ( "部门修改成功!" ) ;
}
}
}
conn. Close ( ) ;
}
}
}
else
{
MessageBox. Show ( "请输入部门名称!" ) ;
}
}
}
}
添加员工窗口
using System ;
using System. Collections. Generic ;
using System. ComponentModel ;
using System. Data ;
using System. Data. SqlClient ;
using System. Drawing ;
using System. Linq ;
using System. Text ;
using System. Threading. Tasks ;
using System. Windows. Forms ;
namespace WindowsFormsApp1
{
public partial class 添加员工数据 : Form
{
private string connectionString = "server=172.0.0.1;uid=sa1;pwd=xyz@x123456;database=test" ;
public 添加员工数据( )
{
InitializeComponent ( ) ;
this . MaximizeBox = false ;
this . FormBorderStyle = FormBorderStyle. FixedSingle;
}
private void 添加员工数据_Load ( object sender, EventArgs e)
{
departmentComboBox. DropDownStyle = ComboBoxStyle. DropDownList;
using ( SqlConnection conn = new SqlConnection ( connectionString) )
{
string query = "SELECT DepartmentID, Name FROM Department" ;
using ( SqlCommand cmd = new SqlCommand ( query, conn) )
{
SqlDataAdapter da = new SqlDataAdapter ( cmd) ;
DataTable dt = new DataTable ( ) ;
da. Fill ( dt) ;
departmentComboBox. DisplayMember = "Name" ;
departmentComboBox. ValueMember = "DepartmentID" ;
departmentComboBox. DataSource = dt;
}
}
}
private void addButton_Click ( object sender, EventArgs e)
{
string employeeName = employeeNameTextBox. Text. Trim ( ) ;
var selectedDepartment = departmentComboBox. SelectedValue;
if ( ! string . IsNullOrEmpty ( employeeName) && selectedDepartment != null )
{
using ( SqlConnection conn = new SqlConnection ( connectionString) )
{
string query = "INSERT INTO Employees_Lists (Name, DepartmentID) VALUES (@Name, @DepartmentID)" ;
using ( SqlCommand cmd = new SqlCommand ( query, conn) )
{
cmd. Parameters. AddWithValue ( "@Name" , employeeName) ;
cmd. Parameters. AddWithValue ( "@DepartmentID" , selectedDepartment) ;
conn. Open ( ) ;
cmd. ExecuteNonQuery ( ) ;
conn. Close ( ) ;
}
}
MessageBox. Show ( "员工添加成功!" ) ;
}
else
{
MessageBox. Show ( "请输入员工信息并选择部门!" ) ;
}
}
}
}
主窗口
using System ;
using System. Collections. Generic ;
using System. Data ;
using System. Data. SqlClient ;
using System. Threading. Tasks ;
using System. Windows. Forms ;
namespace WindowsFormsApp1
{
public partial class 树结构 : Form
{
private string connectionString = "server=172.0.0.1;uid=sa1;pwd=xyz@x123456;database=test" ;
public 树结构( )
{
InitializeComponent ( ) ;
this . treeView1. NodeMouseClick += new TreeNodeMouseClickEventHandler ( this . treeView1_NodeMouseClick) ;
LoadDepartmentsAsync ( ) ;
}
private async void LoadDepartmentsAsync ( )
{
List< Department> departments = await GetDepartmentsAsync ( ) ;
if ( this . dgvDepartments. InvokeRequired)
{
this . dgvDepartments. Invoke ( new Action ( ( ) => {
SetupDepartmentsDataGridView ( departments) ;
} ) ) ;
}
else
{
SetupDepartmentsDataGridView ( departments) ;
}
}
private void SetupDepartmentsDataGridView ( List< Department> departments)
{
this . dgvDepartments. DataSource = departments;
this . dgvDepartments. ReadOnly = true ;
this . dgvDepartments. AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode. Fill;
this . dgvDepartments. AutoSizeRowsMode = DataGridViewAutoSizeRowsMode. DisplayedCells;
AddEditButtonColumn ( ) ;
}
private void AddEditButtonColumn ( )
{
if ( ! dgvDepartments. Columns. Contains ( "EditButton" ) )
{
DataGridViewButtonColumn editButtonColumn = new DataGridViewButtonColumn ( ) ;
editButtonColumn. HeaderText = "操作" ;
editButtonColumn. Name = "EditButton" ;
editButtonColumn. Text = "修改" ;
editButtonColumn. UseColumnTextForButtonValue = true ;
dgvDepartments. Columns. Add ( editButtonColumn) ;
dgvDepartments. CellClick += dgvDepartments_CellClick;
}
}
private void dgvDepartments_CellClick ( object sender, DataGridViewCellEventArgs e)
{
if ( e. ColumnIndex == dgvDepartments. Columns[ "EditButton" ] . Index && e. RowIndex >= 0 )
{
DataGridViewRow row = dgvDepartments. Rows[ e. RowIndex] ;
row. ReadOnly = false ;
string departmentName = row. Cells[ "Name" ] . Value. ToString ( ) ;
添加部门 editDepartmentForm = new 添加部门( departmentName) ;
editDepartmentForm. ShowDialog ( ) ;
LoadDepartmentsAsync ( ) ;
}
}
private Task< List< Department> > GetDepartmentsAsync ( )
{
return Task. Run ( ( ) =>
{
List< Department> departments = new List< Department> ( ) ;
using ( var conn = new SqlConnection ( connectionString) )
{
conn. Open ( ) ;
var cmd = new SqlCommand ( "SELECT * FROM Department" , conn) ;
using ( var reader = cmd. ExecuteReader ( ) )
{
while ( reader. Read ( ) )
{
departments. Add ( new Department
{
DepartmentID = reader. GetInt32 ( 0 ) ,
Name = reader. GetString ( 1 ) ,
} ) ;
}
}
}
return departments;
} ) ;
}
public class Department
{
public int DepartmentID { get ; set ; }
public string Name { get ; set ; }
}
private void LoadDataButton_Click ( object sender, EventArgs e)
{
treeView1. Nodes. Clear ( ) ;
using ( SqlConnection conn = new SqlConnection ( connectionString) )
{
conn. Open ( ) ;
using ( SqlCommand cmd = new SqlCommand ( "SELECT DepartmentID, Name FROM Department" , conn) )
{
using ( SqlDataReader reader = cmd. ExecuteReader ( ) )
{
while ( reader. Read ( ) )
{
TreeNode deptNode = new TreeNode ( reader[ "Name" ] . ToString ( ) ) ;
deptNode. Tag = reader[ "DepartmentID" ] ;
treeView1. Nodes. Add ( deptNode) ;
LoadEmployeesForDepartment ( deptNode) ;
}
}
}
}
}
private void LoadEmployeesForDepartment ( TreeNode deptNode)
{
int departmentId = ( int ) deptNode. Tag;
using ( SqlConnection conn = new SqlConnection ( connectionString) )
{
conn. Open ( ) ;
using ( SqlCommand cmd = new SqlCommand ( "SELECT Name FROM Employees_Lists WHERE DepartmentID = @DepartmentID" , conn) )
{
cmd. Parameters. AddWithValue ( "@DepartmentID" , departmentId) ;
using ( SqlDataReader reader = cmd. ExecuteReader ( ) )
{
while ( reader. Read ( ) )
{
TreeNode empNode = new TreeNode ( reader[ "Name" ] . ToString ( ) ) ;
deptNode. Nodes. Add ( empNode) ;
}
}
}
}
}
private void treeView1_NodeMouseClick ( object sender, TreeNodeMouseClickEventArgs e)
{
dataGridView1. DataSource = null ;
dataGridView1. Rows. Clear ( ) ;
dataGridView1. ReadOnly = true ;
dataGridView1. AllowUserToAddRows = false ;
if ( e. Node. Nodes. Count == 0 && e. Node. Parent != null )
{
string employeeName = e. Node. Text;
DataTable dataTable = new DataTable ( ) ;
using ( SqlConnection conn = new SqlConnection ( connectionString) )
{
string query = "SELECT Name AS 员工, DepartmentID AS 部门Id FROM Employees_Lists WHERE Name = @EmployeeName" ;
using ( SqlCommand cmd = new SqlCommand ( query, conn) )
{
cmd. Parameters. AddWithValue ( "@EmployeeName" , employeeName) ;
using ( SqlDataAdapter adapter = new SqlDataAdapter ( cmd) )
{
adapter. Fill ( dataTable) ;
}
}
}
dataGridView1. DataSource = dataTable;
dataGridView1. DataSource = dataTable;
}
else if ( e. Node. Tag != null && int . TryParse ( e. Node. Tag. ToString ( ) , out int departmentId) )
{
DataTable dataTable = new DataTable ( ) ;
using ( SqlConnection conn = new SqlConnection ( connectionString) )
{
string query = "SELECT a.Name AS 员工, b.Name AS 部门 FROM Employees_Lists a LEFT JOIN Department b ON a.DepartmentID = b.DepartmentID WHERE a.DepartmentID = @DepartmentID" ;
using ( SqlCommand cmd = new SqlCommand ( query, conn) )
{
cmd. Parameters. AddWithValue ( "@DepartmentID" , departmentId) ;
using ( SqlDataAdapter adapter = new SqlDataAdapter ( cmd) )
{
adapter. Fill ( dataTable) ;
}
}
}
dataGridView1. DataSource = dataTable;
}
}
private void Btn_AddDepartment_Click ( object sender, EventArgs e)
{
Form AddDepartment = new 添加部门( ) ;
AddDepartment. StartPosition = FormStartPosition. CenterScreen;
AddDepartment. ShowDialog ( ) ;
}
private void Btn_AddEmployee_Click ( object sender, EventArgs e)
{
Form AddEmployee = new 添加员工数据( ) ;
AddEmployee. StartPosition = FormStartPosition. CenterScreen;
AddEmployee. ShowDialog ( ) ;
}
}
}