功能展示
开始界面
菜单栏选择
赛程添加功能界面
主队跟客队下拉菜单
赛程查询功能界面
这就是基本上的功能展示了
技术
这里面用到的技术也非常简单,就是SQL Server的增删改查,跟基础的C#语法,和一些简单winform的控件
数据库表
数据库名称:FootballScheduleManager
表名 | ScheduleInfo | 中文表名称 | 赛程信息 | ||
序号 | 字段名称 | 字段说明 | 类型 | 长度 | 备注 |
1 | id | 序号 | int | 自动编号,主键 | |
2 | count | 场次 | int | 非空 | |
3 | hostTeamId | 主队编号 | int | 非空,外键 | |
4 | awayTeamId | 客队编号 | int | 非空,外键 | |
5 | competitionTime | 比赛时间 | datetime | 非空 | |
6 | competitionPosition | 比赛地点 | varchar | 50 | 非空 |
7 | score | 最终比分 | varchar | 50 | 可以为空 |
8 | entryPerson | 录入人 | varchar | 20 | 可以为空 |
9 | entryTime | 录入时间 | datetime | 默认值:getDate() |
表名 | TeamInfo | 中文表名称 | 球队信息 | ||
序号 | 字段名称 | 字段说明 | 类型 | 长度 | 备注 |
1 | id | 球队编号 | int | 自动编号,主键 | |
2 | teamName | 球队名称 | varchar | 50 | 非空 |
SQL语句
use FootballScheduleManager
--查询赛程信息表完整信息
select [count],z.teamName,k.teamName,competitionTime,competitionPosition,score,entryPerson,entryTime
from ScheduleInfo as s
inner join TeamInfo as z on s.hostTeamId = z.id
inner join TeamInfo as k on s.awayTeamId = k.id
select [count],h.teamName as hostTeamId,a.teamName as awayTeamId,competitionTime,competitionPosition,score,entryPerson from ScheduleInfo,TeamInfo as h,TeamInfo as a where ScheduleInfo.hostTeamId = h.id and ScheduleInfo.awayTeamId = a.id
--加中文列名
select [count] as '场次',z.teamName as '主队',k.teamName as '客队',competitionTime as '比赛时间',competitionPosition as '比赛地点',score as '最终比分',entryPerson as '录入人',entryTime as '录入时间'
from ScheduleInfo as s
inner join TeamInfo as z on s.hostTeamId = z.id
inner join TeamInfo as k on s.awayTeamId = k.id
--查询赛程信息表
select * from ScheduleInfo
select [count],hostTeamId,awayTeamId,competitionTime,competitionPosition,score,entryPerson from ScheduleInfo
--赛程信息表插入数据
insert into ScheduleInfo ([count],hostTeamId,awayTeamId,competitionTime,competitionPosition,score,entryPerson)
values (1,1,2,'2014-7-22','上海虹口足球场','1比1','李季'),
(2,1,3,'2014-7-24','天津秦达足球场','1比4','徐军'),
(3,4,5,'2014-7-28','成都龙泉足球场','3比2','李季'),
(4,6,5,'2014-7-29','上海金山足球场','2比2','徐军'),
(5,6,7,'2014-7-30','上海虹口足球场','4比1','李季'),
(6,4,2,'2014-7-31','天津秦达足球场','1比1','徐军')
--查询球队信息表
select * from TeamInfo
select id,teamName from TeamInfo
--球队信息表插入数据
insert into TeamInfo (teamName)
values ('北京国安'),('天津泰达'),('山东鲁能'),('贵州人和'),('长春亚泰'),('广州恒大'),('杭州绿城')
代码
主窗体
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace FootballManagementSystem
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
private void MainForm_Load(object sender, EventArgs e)
{
}
/// <summary>
/// 赛程添加选项
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 赛程添加ToolStripMenuItem_Click(object sender, EventArgs e)
{
AddSchedule addSchedule = new AddSchedule();
addSchedule.MdiParent = this;
addSchedule.Show();
}
/// <summary>
/// 退出系统选项
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 退出系统ToolStripMenuItem_Click(object sender, EventArgs e)
{
this.Close();
}
/// <summary>
/// 赛程查询信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void 赛程查询ToolStripMenuItem_Click(object sender, EventArgs e)
{
QuerySchedule querySchedule = new QuerySchedule();
querySchedule.MdiParent = this;
querySchedule.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 FootballManagementSystem
{
public partial class AddSchedule : Form
{
public AddSchedule()
{
InitializeComponent();
}
private void AddSchedule_Load(object sender, EventArgs e)
{
//窗体加载时运行主队下拉菜单
//BindHostTeamIdDataSet();
//this.cbo_hostTeamId.Items.Add("请选择区域");
//this.cbo_hostTeamId.SelectedIndex = 0;
BindHostTeamId();
BindAwayTeamId();
}
/// <summary>
/// 主队下拉菜单
/// </summary>
private void BindHostTeamIdDataSet()
{
try
{
//创建数据集
DataSet ds = new DataSet();
//获取连接对象
SqlConnection conn = DBHelper.GetConnection();
string sql = "select id,teamName from TeamInfo";
//SqlDataAdepter
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
//将数据填充到DataSet
adapter.Fill(ds, "TeamInfo");
//将dataset里的数据绑定到下拉菜单控件上
//在数据集的表中添加一行
DataRow row = ds.Tables["Teaminfo"].NewRow();
//row[0] = -1;
//row[1] = "";
ds.Tables["TeamInfo"].Rows.InsertAt(row, 0);
//将表中的数据添加到组件
cbo_hostTeamId.DataSource = ds.Tables["TeamInfo"];
cbo_hostTeamId.DisplayMember = "teamName";
cbo_hostTeamId.ValueMember = "id";
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBHelper.Close();
}
}
/// <summary>
/// 主队下拉菜单
/// </summary>
private void BindHostTeamId()
{
SqlDataReader reader = null;
try
{
SqlConnection conn = DBHelper.GetConnection();
DBHelper.Open();
string sql = "select id,teamName from TeamInfo";
SqlCommand cmd = new SqlCommand(sql, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
//将查询的结果渲染至下拉菜单
string proName = reader["teamName"].ToString();
this.cbo_hostTeamId.Items.Add(proName);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
reader.Close();
DBHelper.Close();
}
}
/// <summary>
/// 客队下拉菜单
/// </summary>
private void BindAwayTeamId()
{
SqlDataReader reader = null;
try
{
SqlConnection conn = DBHelper.GetConnection();
DBHelper.Open();
string sql = "select id,teamName from TeamInfo";
SqlCommand cmd = new SqlCommand(sql, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
//将查询的结果渲染至下拉菜单
string proName = reader["teamName"].ToString();
this.cbo_awayTeamId.Items.Add(proName);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
reader.Close();
DBHelper.Close();
}
}
/// <summary>
/// 根据队伍名查询队伍编号id
/// </summary>
/// <param name="team"></param>
/// <returns></returns>
private int QueryIdTeamName(string team)
{
int id = -1;
try
{
SqlConnection conn = DBHelper.GetConnection();
DBHelper.Open();
string sql = $"select id from TeamInfo where teamName = '{team}'";
SqlCommand cmd = new SqlCommand(sql, conn);
id = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBHelper.Close();
}
return id;
}
/// <summary>
/// 提交按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Submit_Click(object sender, EventArgs e)
{
//比赛时间
string competitionTime = txt_competitionTime.Text;
//比赛地点
string competitionPosition = txt_competitionPosition.Text;
//最终比分
string score = txt_score.Text;
//录入人
string entryPerson = txt_entryPerson.Text;
//判断信息输入是否完整
if (txt_count.Text.Equals(string.Empty) || cbo_hostTeamId.Text.Equals(string.Empty) || cbo_awayTeamId.Text.Equals(string.Empty) || competitionTime.Equals(string.Empty) || competitionPosition.Equals(string.Empty) || score.Equals(string.Empty) || entryPerson.Equals(string.Empty))
{
MessageBox.Show("赛程信息输入不完整,请完善!", "提示!", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//判断主队和客队是否是同一个队伍
if (cbo_hostTeamId.Text == cbo_awayTeamId.Text)
{
MessageBox.Show("主队和客队不能是同一个队伍!", "提示!", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//场次
int count = Convert.ToInt32(txt_count.Text);
//主队
int hostTeamId = QueryIdTeamName(cbo_hostTeamId.Text);
//客队
int awayTeamId = QueryIdTeamName(cbo_awayTeamId.Text);
//将数据存入数据库
try
{
SqlConnection conn = DBHelper.GetConnection();
DBHelper.Open();
string sql = $"insert into ScheduleInfo ([count],hostTeamId,awayTeamId,competitionTime,competitionPosition,score,entryPerson) values({count}, {hostTeamId}, {awayTeamId}, '{competitionTime}', '{competitionPosition}', '{score}', '{entryPerson}')";
SqlCommand cmd = new SqlCommand(sql, conn);
int row = cmd.ExecuteNonQuery();
if (row > 0)
{
MessageBox.Show("赛程信息保存成功!");
this.Close();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBHelper.Close();
}
}
/// <summary>
/// 全部清空按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_CleanOut_Click(object sender, EventArgs e)
{
//清空场次
txt_count.Clear();
//清空主队
cbo_hostTeamId.SelectedIndex = -1;
//清空客队
cbo_awayTeamId.SelectedIndex = -1;
//清空比赛时间
txt_competitionTime.Clear();
//清空比赛地点
txt_competitionPosition.Clear();
//清空最终比分
txt_score.Clear();
//清空录入人
txt_entryPerson.Clear();
}
/// <summary>
/// 关闭按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Close_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
赛程查询窗体
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 FootballManagementSystem
{
public partial class QuerySchedule : Form
{
DataSet ds = new DataSet();
SqlDataAdapter adapter = null;
public QuerySchedule()
{
InitializeComponent();
}
/// <summary>
/// 查询所有赛程信息
/// </summary>
private void QueryAll()
{
try
{
SqlConnection conn = DBHelper.GetConnection();
string sql = "select [count],h.teamName as hostTeamId,a.teamName as awayTeamId,competitionTime,competitionPosition,score,entryPerson from ScheduleInfo,TeamInfo as h,TeamInfo as a where ScheduleInfo.hostTeamId = h.id and ScheduleInfo.awayTeamId = a.id";
adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(ds, "ScheduleInfo");
//将数据集 ds 与 DataGridView 控件的 DataSoure 绑定
dataGridView1.DataSource = ds.Tables["ScheduleInfo"];
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBHelper.Close();
}
}
private void QuerySchedule_Load(object sender, EventArgs e)
{
QueryAll();
}
/// <summary>
/// 关闭按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Close_Click(object sender, EventArgs e)
{
this.Close();
}
/// <summary>
/// 查询按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Query_Click(object sender, EventArgs e)
{
//dataGridView1.Rows.Clear();
//DataTable dt = new DataTable();
//dataGridView1.DataSource = dt;
DataTable dt = this.dataGridView1.DataSource as DataTable;
dt.Rows.Clear();
this.dataGridView1.DataSource = dt;
string sql = $"select [count],h.teamName as hostTeamId,a.teamName as awayTeamId,competitionTime,competitionPosition,score,entryPerson from ScheduleInfo,TeamInfo as h,TeamInfo as a where ScheduleInfo.hostTeamId = h.id and ScheduleInfo.awayTeamId = a.id";
if (!txt_HostTeamName.Text.Equals(string.Empty))
{
//获取主队名称
string zd = txt_HostTeamName.Text;
sql += $" and h.teamName like '%{zd}%'";
}
try
{
SqlConnection conn = DBHelper.GetConnection();
adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(ds, "ScheduleInfo");
//将数据集 ds 与 DataGridView 控件的 DataSoure 绑定
dataGridView1.DataSource = ds.Tables["ScheduleInfo"];
}
catch (Exception ex)
{
throw ex;
}
finally
{
DBHelper.Close();
}
}
}
}
连接数据库封装代码
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace FootballManagementSystem
{
class DBHelper
{
public static SqlConnection conn = null;
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns></returns>
public static SqlConnection GetConnection()
{
string connStr = @"Data Source=.;Initial Catalog=FootballScheduleManager;User ID=sa;Password=root";
if (conn == null || conn.State == ConnectionState.Closed)
{
conn = new SqlConnection(connStr);
return conn;
}
else
{
return conn;
}
}
/// <summary>
/// 数据库连接打开
/// </summary>
public static void Open()
{
if (conn != null)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
else if (conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
}
}
/// <summary>
/// 数据库连接关闭
/// </summary>
public static void Close()
{
if (conn != null)
{
conn.Close();
}
}
}
}