华南理工大学软件学院_3181026
1.构建数据库连接类DB
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace SCUT
{
class DB : IDisposable
{
private SqlConnection sqlConnection;
//与打开的SQL服务建立连接
public DB()
{
sqlConnection = new SqlConnection(@"server=.\SQLEXPRESS;database=SCUT_2018;Trusted_Connection=SSPI;");
sqlConnection.Open();
}
//返回参数控制CMD
public SqlCommand getCmdBySql(string sql)
{
return new SqlCommand(sql, sqlConnection);
}
//返回DataTable类型语句
public DataTable getDataByCmd(SqlCommand cmd)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
return dataTable;
}
//执行无需返回值的插入和删除语句
public void setDataByCmd(SqlCommand cmd)
{
cmd.ExecuteNonQuery();
}
public void Dispose()
{
sqlConnection.Close();
}
}
}
- 构建不同form之间传递值的Intent类
using System;
using System.Collections.Generic;
using System.Text;
namespace SCUT
{
class Intent
{
public static Dictionary<string, object> dict = new Dictionary<string, Object>();
}
}
- Form类中常见要求的函数构建
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace SCUT
{
public partial class Form1 : Form
{
private DB db;
//构造函数，建立一个数据库连接
public Form1()
{
InitializeComponent();
db = new DB();
}
//统计功能函数-可根据需求进行改写
public void Fun2()
{
listView1.Clear();
listView1.BeginUpdate();
listView1.Columns.Add("所在城市", listView1.Width / 2 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("订单总金额", listView1.Width / 2 - 1, HorizontalAlignment.Left);
//按照地方统计订单金额
string sql = "SELECT E1.EmpCity, SUM(O1.OderMoney) " +
"FROM EMPLOYER E1, ORDERS O1 " +
"WHERE E1.EmpNo = O1.EmpNo " +
"GROUP BY E1.EmpCity ";
SqlCommand cmd = db.getCmdBySql(sql);
DataTable table = db.getDataByCmd(cmd);
for (int i = 0; i < table.Rows.Count; i++)
{
ListViewItem item = new ListViewItem();
for (int j = 0; j < table.Columns.Count; j++)
{
if (j == 0) item.Text = table.Rows[i][j] + "";
else item.SubItems.Add(table.Rows[i][j] + "");
}
listView1.Items.Add(item);
}
listView1.EndUpdate();
}
//查询功能函数-可根据需求进行改写
public void Fun2()
{
listView1.Clear();
listView1.BeginUpdate();
listView1.Columns.Add("经销商号", listView1.Width / 7 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("订单号", listView1.Width / 7 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("商品名称", listView1.Width / 7 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("经销商名称", listView1.Width / 7 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("所在城市", listView1.Width / 7 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("订购日期", listView1.Width / 7 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("金额", listView1.Width / 7 - 1, HorizontalAlignment.Left);
//查询符合调价的订单信息
string sql = "SELECT E.EmpNo,O.OrdNo,O.OrdName,E.EmpCity,O.OrdDate,O.OderMoney " +
"FROM EMPLOYER E, ORDERS O " +
"WHERE E.EmpNo = O.EmpNo AND O.OrdName LIKE @OrdName AND " +
"(E.EmpNo = @EmpNo OR E.EmpName = @EmpName) "+
"ORDER BY E.EmpName DESC, O.OrdName ASC, O.OrdDate DESC, O.OderMoney DESC";
SqlCommand cmd = db.getCmdBySql(sql);
cmd.Parameters.AddWithValue("@OrdName","%"+textBox1.Text+"%");
cmd.Parameters.AddWithValue("@EmpNo",comboBox1.Text);
cmd.Parameters.AddWithValue("@EmpName",comboBox2.Text);
DataTable table = db.getDataByCmd(cmd);
for (int i = 0; i < table.Rows.Count; i++)
{
ListViewItem item = new ListViewItem();
for (int j = 0; j < table.Columns.Count; j++)
{
if (j == 0) item.Text = table.Rows[i][j] + "";
else item.SubItems.Add(table.Rows[i][j] + "");
}
listView1.Items.Add(item);
}
listView1.EndUpdate();
}
//新增部分函数-可根据需求进行改写
private void button2_Click(object sender, EventArgs e)
{
Form2 form2 = new Form2();
//对于新增部分form_flag设置为 0，修改部分设置为 1
Intent.dict["form1_flag"] = 0;
if (form2.ShowDialog() == DialogResult.OK)
{
foreach(ListViewItem item in this.listView2.Items)
{
if (Intent.dict["form2_textbox1_text"] + "" == item.SubItems[0].Text)
{
MessageBox.Show("已存在该员工号", this.Text);
return；
}
}
//判断日期是否符合格式
Regex regex = new Regex(@"^(?:(?!0000)[0-9]{4}-(?:(?:0[1-9]|1[0-2])-(?:0[1-9]|1[0-9]|2[0-8])|(?:0[13-9]|1[0-2])-(?:29|30)|(?:0[13578]|1[02])-31)|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])|(?:0[48]|[2468][048]|[13579][26])00)-02-29)$");
if (!regex.IsMatch(Intent.dict["form2_textbox3_text"] + ""))
{
MessageBox.Show("日期格式不正确,应为yyyy-MM-dd", this.Text);
return；
}
//判断外键是否存在
bool forign_key = false;
foreach (ListViewItem item in this.listView5.Items)
{
if (Intent.dict["form2_textbox4_text"] + "" == item.SubItems[0].Text)
{
forign_key = true;
break;
}
}
if (forign_key == false)
{
MessageBox.Show("不存在该小科室", this.Text);
return；
}
string sql = "INSERT INTO [Patient] VALUES(@Pid,@Pname,@Date,@GroupNo)";
SqlCommand cmd = db.getComBySql(sql);
cmd.Parameters.AddWithValue("@Pid", Intent.dict["form2_textbox1_text"] + "");
cmd.Parameters.AddWithValue("@Pname", Intent.dict["form2_textbox2_text"] + "");
cmd.Parameters.AddWithValue("@Date", Intent.dict["form2_textbox3_text"] + "");
cmd.Parameters.AddWithValue("@GroupNo", Intent.dict["form2_textbox4_text"] + "");
db.setBySql(cmd);
Fun2();
}
}
//修改部分函数-可根据需求进行改写
private void button3_Click(object sender, EventArgs e)
{
Form2 form2 = new Form2();
Intent.dict["form1_text"] = this.Text;
//form1_flag = 0 为增加，1 为修改，2为删除
Intent.dict["form1_flag"] = 1;
//必须先判断是否选中
try
{
string a = listView2.SelectedItems[0].SubItems[0].Text + "";
}
catch (Exception)
{
MessageBox.Show("必须先选中要修改的病人信息", this.Text);
return;
}
//记录选中的信息
Intent.dict["form1_selectedItem0"] = listView2.SelectedItems[0].SubItems[0].Text;
Intent.dict["form1_selectedItem1"] = listView2.SelectedItems[0].SubItems[1].Text;
Intent.dict["form1_selectedItem2"] = listView2.SelectedItems[0].SubItems[2].Text;
Intent.dict["form1_selectedItem3"] = listView2.SelectedItems[0].SubItems[3].Text;
if (form2.ShowDialog() == DialogResult.OK)
{
bool canUpdate = true;
//如果修改过病人号，判断是否一致
if(!(Intent.dict["form2_textbox1_text"] + "" == Intent.dict["form1_selectedItem0"]+""))
{
foreach (ListViewItem item in this.listView2.Items)
{
if (Intent.dict["form2_textbox1_text"] + "" == item.SubItems[0].Text)
{
MessageBox.Show("已存在该员工号", this.Text);
return;
}
}
}
//判断日期是否符合格式
Regex regex = new Regex(@"^(?:(?!0000)[0-9]{4}-(?:(?:0[1-9]|1[0-2])-(?:0[1-9]|1[0-9]|2[0-8])|(?:0[13-9]|1[0-2])-(?:29|30)|(?:0[13578]|1[02])-31)|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])|(?:0[48]|[2468][048]|[13579][26])00)-02-29)$");
if (!regex.IsMatch(Intent.dict["form2_textbox3_text"] + ""))
{
MessageBox.Show("日期格式不正确,应为yyyy-MM-dd", this.Text);
return;
}
string sql = "UPDATE Patient" +
" SET PID = @NewPid, PName = @PName, [Date] = @Date, GroupNo = @GroupNo" +
" where PID = @OldPid";
SqlCommand cmd = db.getComBySql(sql);
cmd.Parameters.AddWithValue("@NewPid", Intent.dict["form2_textbox1_text"] + "");
cmd.Parameters.AddWithValue("@PName", Intent.dict["form2_textbox2_text"] + "");
cmd.Parameters.AddWithValue("@Date", Intent.dict["form2_textbox3_text"] + "");
cmd.Parameters.AddWithValue("@GroupNo", Intent.dict["form2_textbox4_text"] + "");
cmd.Parameters.AddWithValue("@OldPid", Intent.dict["form1_selectedItem0"] + "");
db.setBySql(cmd);
Fun2();
}
//删除部分函数-可根据需求进行改写
private void button4_Click(object sender, EventArgs e)
{
//判断是否选中
try
{
string a = listView2.SelectedItems[0].SubItems[0].Text;
}
catch
{
MessageBox.Show("必须先选中要删除的病人信息", this.Text);
return;
}
//删除操作无需用到Form2，只需获得该列的ID即可
string sql = "Delete FROM Patient where Pid = @Pid";
SqlCommand cmd = db.getComBySql(sql);
cmd.Parameters.AddWithValue("@Pid", listView2.SelectedItems[0].SubItems[0].Text);
db.setBySql(cmd);
Fun2();
}
}
}
}
- Form类修改框的常见函数构造
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace SCUT
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "")
{
MessageBox.Show("任意一项没有完成填写！", this.Text);
}
else
{
//关闭form2之间，将要传给form1的值压入Intent中的dict
Intent.dict["form2_textbox1_text"] = textBox1.Text;
Intent.dict["form2_textbox2_text"] = textBox2.Text;
Intent.dict["form2_textbox3_text"] = textBox3.Text;
Intent.dict["form2_textbox4_text"] = textBox4.Text;
this.DialogResult = DialogResult.OK;//同时设置返回值为OK，不设置的话，默认返回Cancel
this.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
private void Form2_Load(object sender, EventArgs e)
{
if ((int)Intent.dict["form1_flag"] == 1)
{
textBox1.Text = Intent.dict["form1_selectedItem0"] + "";
textBox2.Text = Intent.dict["form1_selectedItem1"] + "";
textBox3.Text = Intent.dict["form1_selectedItem2"] + "";
textBox4.Text = Intent.dict["form1_selectedItem3"] + "";
}
else
{
textBox1.Focus();
}
}
}
}
5.特殊情况正则表达式的应用
- 判断是否为yyyy-MM-dd格式的日期
Regex regex = new Regex(@"^(?:(?!0000)[0-9]{4}-(?:(?:0[1-9]|1[0-2])-(?:0[1-9]|1[0-9]|2[0-8])|(?:0[13-9]|1[0-2])-(?:29|30)|(?:0[13578]|1[02])-31)|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])|(?:0[48]|[2468][048]|[13579][26])00)-02-29)$");
if (!regex.IsMatch(Intent.dict["form2_textbox3_text"] + ""))
{
MessageBox.Show("日期格式不正确,应为yyyy-MM-dd", this.Text);
return;
}
- 判断月份是否符合规范
Regex regex_month = new Regex(@"^(0?[1-9]|1[0-2])$");
if (!regex_month.IsMatch(Intent.dict["form3_textBox4"]+""))
{
MessageBox.Show("请输入正确的月份", this.Text);
return;
}
- 判断人数是否为正整数
Regex regex_num = new Regex(@"^[0-9]*[1-9][0-9]*$");
if (!regex_num.IsMatch(Intent.dict["form3_textBox5"] + ""))
{
MessageBox.Show("请输入正确的人数", this.Text);
return;
}
- 判断是否输入的是数字
Regex regex = new Regex("^[0-9]*$");
if (!regex.IsMatch(Intent.dict["form2_textbox3_text"] + ""))//
{
canUpdate = false;
MessageBox.Show("年龄不为正数！", this.Text);
return;
}
6.特殊组件的使用
- 下拉选框comboxBox
for (int i = 0; i < table.Rows.Count; i++)
{
comboBox2.Items.Add(table.Rows[i][0] + "");
}
- 互斥单选框
if (Intent.dict["form1_selectedItems2"] + "" == "男")
{
radioButton1.Checked = true;
}
else
{
radioButton2.Checked = true;
}
7.常见组件的设置
- Form组件
- FormBorderStyle FixedSingle
- MaximizeBox False
- MinimizeBox False
- StartPosition CenterScreen
- ListView组件
- MulitiSelect False
- FullRowSelect True
- GirdLines True
- View Details
- 增加列属性名 listView1.Columns.Add("经销商号", listView1.Width / 7 - 1, HorizontalAlignment.Left);
- 增加列元素 listView1.Items.Add(item);
- ListViewItem组件
- 增加行元素头 item.Text = table.Rows[i][j] + "";
- 增加行元素 item.SubItems.Add(table.Rows[i][j] + "");
- comboBox组件
- DropDownStyle DropDownList
浙公网安备 33010602011771号