微型软件设计
工具使用
vs（C#windows窗口）+ Sql server （sql）
步骤
数据库
需要完成：数据库文件 + 数据库日志 + 数据库备份文件
步骤① 创建数据库，更改数据库文件以及日志文件的路径
步骤② 创建表以及插入数据
create table [表名] #建表
(
[属性名1] varchar(8) not null,
[属性名2] varchar(50) not null,
[属性名3] varchar(2) check([EmpSex] = '男'or[EmpSex] = '女'), #check使用方式，以及constraint使用方式
[属性名4] int check([EmpAge]>0),
constraint 约束名 primary key ([属性5]) #外键
constraint FK_WORKS_EMPLOYEE foreign key([属性6]) references [EMPLOYEE]([EmpNo]) # 外键
)
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = 1000000005;
DELETE FROM Customers
WHERE cust_id = 1000000006;
insert into [EMPLOYEE] values('E01','张三','女','32') #插入语句
UNIQUE #唯一约束
DEFAULT '0' #默认值
upper 改大写
lower 改小写
游标
声明：
declare <name> cursor
for
select 语句
打开
open <name>
抓取
fetch ... from 游标名 into ...
关闭
close <name>
释放
deallocate <name>
@@fetch_status(大写）
局部变量@ 全局变量@@
case when then：
(CASE education
--当education等于大专时，学历等级就为2
WHEN '大专' THEN 2
WHEN '中专' THEN 3
WHEN '本科' THEN 1
--否则学历等级都为4
ELSE 4
END)
if (表达式,值1，值2)
③备份数据库，数据库->任务->备份 命名为backupInfo.bak
C#
1.前提
①数据库相关文件：
因为数据库数据不属于本地的 ，所以要继承IDisposable，这样才能关闭数据库
初始化：使用链接字符串连接数据库，并打开连接
创建两个方法一个是查询一个更改
class DB : IDisposable //no
{
private SqlConnection sqlConnection;
public DB()
{
sqlConnection = new SqlConnection(@"server=.;database=SCNT;integrated security=SSPI;");//no
sqlConnection.Open();
}
public DataTable getBySql(string sql)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(new SqlCommand(sql, sqlConnection));//no
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable); // no
return dataTable;
}
public void setBySql(string sql)
{
new SqlCommand(sql, sqlConnection).ExecuteNonQuery();//要用ExecuteNonQuery方法
}
public void Dispose()
{
sqlConnection.Close();
}
}
②字典，窗口值传输
class Intent
{
public static Dictionary<string, Object> dict = new Dictionary<string, object>();//
}
③窗口属性设置
Form1.cs
Load：所属函数
listView:
MultiSelect 关闭（不可以同时选择多个）
view Detail
GridLine显示下划线
FullRowSelect true 选择一行可得到整行数据
comBox ：
DropDownStyle：DropDownList 窗口下拉方向
④代码实现
DB db;
public Form1()
{
InitializeComponent();
db = new DB();
}
private void Form1_Load(object sender, EventArgs e)
{
//员工表 增删改查：查
//生成表头
listView1.Columns.Add("员工号", listView1.Width / 4 - 1, HorizontalAlignment.Left);//HorizontalAlignment的作用
listView1.Columns.Add("员工姓名", listView1.Width / 4 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("性别", listView1.Width / 4 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("年龄", listView1.Width / 4 - 1, HorizontalAlignment.Left);
//表的内容
DataTable table = db.getBySql(@"select [EmpNo],[EmpName],[EmpSex],[EmpSex] from [EMPLOYEE]");
listView1.BeginUpdate();//no
for (int i = 0; i < table.Rows.Count; i++)
{
ListViewItem listViewItem = new ListViewItem(); //no
for (int j = 0; j < table.Columns.Count; j++)
{
if (j == 0)
{
listViewItem.Text = table.Rows[i][j] + "";//
}
else
{
listViewItem.SubItems.Add(table.Rows[i][j] + "");
}
}
listView1.Items.Add(listViewItem);
}
listView1.EndUpdate();
//查询3-1
//加载现有员工号
table = db.getBySql(@"select [EmpNo] from [EMPLOYEE]");
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
comboBox1.Items.Add(table.Rows[i][j] + "");//comboBox可以屏蔽表的第一空项
}
}
comboBox1.SelectedIndex = 0;//no
//员工名
table = db.getBySql(@"select [EmpName] from [EMPLOYEE]");
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
comboBox2.Items.Add(table.Rows[i][j] + "");
}
}
comboBox2.SelectedIndex = 0;//初始选择项
listView3.Columns.Add("员工号", listView1.Width / 5 - 1, HorizontalAlignment.Left);//HorizontalAlignment的作用
listView3.Columns.Add("员工姓名", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView3.Columns.Add("性别", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView3.Columns.Add("年龄", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView3.Columns.Add("总工资", listView1.Width / 5 - 1, HorizontalAlignment.Left);
table = db.getBySql(@"select [EMPLOYEE].[EmpNo],[EMPLOYEE].[EmpName],[EMPLOYEE].[EmpSex],[EMPLOYEE].[EmpAge],sum([WORKS].[Salary]) as '总工资' " +
" from [EMPLOYEE],[WORKS]"+
" where [EMPLOYEE].[EmpAge]>=40"+
" and [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo]"+
" group by [EMPLOYEE].[EmpNo],[EMPLOYEE].[EmpName],[EMPLOYEE].[EmpSex],[EMPLOYEE].[EmpAge]"+
" order by '总工资' desc");
listView3.BeginUpdate();
for (int i = 0; i < table.Rows.Count; i++)
{
ListViewItem listViewItem = new ListViewItem(); //no
for (int j = 0; j < table.Columns.Count; j++)
{
if (j <= 0)
{
listViewItem.Text = table.Rows[i][j] + "";
}
else
{
listViewItem.SubItems.Add(table.Rows[i][j] + "");
}
}
listView3.Items.Add(listViewItem);
}
listView3.EndUpdate();
//3-3
listView4.Columns.Add("员工姓名", listView1.Width / 2 - 2, HorizontalAlignment.Left);
listView4.Columns.Add("公司名", listView1.Width / 2 - 2, HorizontalAlignment.Left);
table = db.getBySql(@"select [EMPLOYEE].[EmpName],[COMPANY].[CmpName] from [EMPLOYEE],[COMPANY],[WORKS],(" +
" select [EmpName],count([CmpName]) as 'CmpNum'"+
" from [EMPLOYEE],[WORKS],[COMPANY]"+
" where [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo]"+
" and [COMPANY].[CmpNo]=[WORKS].[CmpNo]"+
" group by [EmpName]"+
" having count([CmpName])>1"+
" ) as t1" +
" where [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo]"+
" and [COMPANY].[CmpNo]=[WORKS].[CmpNo]" +
" and [COMPANY].[CmpNo]=[WORKS].[CmpNo]");
listView4.BeginUpdate();
for (int i = 0; i < table.Rows.Count; i++)
{
ListViewItem listViewItem = new ListViewItem(); //no
for (int j = 0; j < table.Columns.Count; j++)
{
if (j <= 0)
{
listViewItem.Text = table.Rows[i][j] + "";
}
else
{
listViewItem.SubItems.Add(table.Rows[i][j] + "");//SubItems
}
}
listView4.Items.Add(listViewItem);
}
listView4.EndUpdate();
}
private void Button1_Click(object sender, EventArgs e)
{
Form2 form2 = new Form2();//no
Intent.dict["form1_text"] = this.Text;
Intent.dict["form1_flag"] = 0;
if(form2.ShowDialog() == DialogResult.OK)//no
{
bool canAdd = true;
foreach(ListViewItem item in this.listView1.Items)//no
{
if(Intent.dict["form2_textbox1_text"] + "" == item.SubItems[0].Text)
{
canAdd = false;
MessageBox.Show("已存在该员工号！", this.Text);
break;
}
}
Regex regex = new Regex("^[0-9]*$"); //正则表达式
if (!regex.IsMatch(Intent.dict["form2_textbox3_text"] + ""))
{
canAdd = false;
MessageBox.Show("年龄不为正数!",this.Text);
}
if (canAdd)
{
ListViewItem listViewItem = new ListViewItem();
listViewItem.Text = Intent.dict["form2_textbox1_text"] + "";
listViewItem.SubItems.Add(Intent.dict["form2_textbox2_text"] + "");
listViewItem.SubItems.Add(Intent.dict["form2_radioButton"] + "");
listViewItem.SubItems.Add(Intent.dict["form2_textbox3_text"] + "");
listView1.Items.Add(listViewItem);
db.setBySql("insert into [EMPLOYEE] values('" + Intent.dict["form2_textbox1_text"] + "','" + Intent.dict["form2_textbox2_text"] + "','"+ Intent.dict["form2_radioButton"] + "',"+ Intent.dict["form2_textbox3_text"] + ")");
}
}
}
private void Button2_Click(object sender, EventArgs e)
{
Form2 form2 = new Form2();//no
Intent.dict["form1_text"] = this.Text;
Intent.dict["form1_flag"] = 1;
Intent.dict["form1_selectedItems0"] = listView1.SelectedItems[0].SubItems[0].Text;
Intent.dict["form1_selectedItems1"] = listView1.SelectedItems[0].SubItems[1].Text;
Intent.dict["form1_selectedItems2"] = listView1.SelectedItems[0].SubItems[2].Text;
Intent.dict["form1_selectedItems3"] = listView1.SelectedItems[0].SubItems[3].Text;
if (form2.ShowDialog() == DialogResult.OK)
{
bool canUpdate = true;
if (!((Intent.dict["form1_selectedItems0"] + "") == (Intent.dict["form2_textbox1_text"] + ""))){
foreach (ListViewItem item in this.listView1.Items)//no
{
if (Intent.dict["form2_textbox1_text"] + "" == item.SubItems[0].Text)
{
canUpdate = false;
MessageBox.Show("已存在该员工号！", this.Text);
break;
}
}
}
Regex regex = new Regex("^[0-9]*$"); //正则表达式
if (!regex.IsMatch(Intent.dict["form2_textbox3_text"] + ""))
{
canUpdate = false;
MessageBox.Show("年龄不为正数");
}
if (canUpdate)
{
ListViewItem listViewItem = new ListViewItem();
listViewItem.Text = Intent.dict["form2_textbox1_text"] + "";
listViewItem.SubItems.Add(Intent.dict["form2_textbox2_text"] + "");
listViewItem.SubItems.Add(Intent.dict["form2_radioButton"] + "");
listViewItem.SubItems.Add(Intent.dict["form2_textbox3_text"] + "");
db.setBySql("update [EMPLOYEE] set [EmpNo] = '" + Intent.dict["form2_textbox1_text"] + "'where [EmpNo] = '"+ Intent.dict["form1_selectedItems0"] + "'; ");
db.setBySql("update [EMPLOYEE] set [EmpName] = '" + Intent.dict["form2_textbox2_text"] + "'where [EmpName] = '" + Intent.dict["form1_selectedItems1"] + "'; ");
db.setBySql("update [EMPLOYEE] set [EmpSex] = '" + Intent.dict["form2_radioButton"] + "'where [EmpSex] = '" + Intent.dict["form1_selectedItems2"] + "'; ");
db.setBySql("update [EMPLOYEE] set [EmpAge] = '" + Intent.dict["form2_textbox3_text"] + "'where [EmpAge] = '" + Intent.dict["form1_selectedItems3"] + "'; ");
listView1.Clear();
listView1.Items.Add(listViewItem);
}
}
}
private void Button3_Click(object sender, EventArgs e)
{
db.setBySql("delete from [EMPLOYEE] where [EmpNo]='" + listView1.SelectedItems[0].Text + "';");
listView1.SelectedItems[0].Remove();
}
private void Button4_Click(Object sender,EventArgs e)
{
listView2.Clear();
listView2.Columns.Add("员工号", listView1.Width / 4 - 1, HorizontalAlignment.Left);//HorizontalAlignment的作用
listView2.Columns.Add("员工姓名", listView1.Width / 4 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("公司名", listView1.Width / 4 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("薪水", listView1.Width / 4 - 1, HorizontalAlignment.Left);
DataTable table = db.getBySql(@"select [EMPLOYEE].[EmpNo],[EMPLOYEE].[EmpName],[COMPANY].[CmpName],[WORKS].[Salary] from [EMPLOYEE],
[COMPANY],[WORKS]" +
" where [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo]" +
" and [COMPANY].[CmpNo]=[WORKS].[CmpNo]" +
" and [EMPLOYEE].[EmpNo]='" + comboBox1.Text+ "'");
listView2.BeginUpdate();
for (int i = 0; i < table.Rows.Count; i++)
{
ListViewItem listViewItem = new ListViewItem(); //no
for (int j = 0; j < table.Columns.Count; j++)
{
if (j <= 0)
{
listViewItem.Text = table.Rows[i][j] + "";
}
else
{
listViewItem.SubItems.Add(table.Rows[i][j] + "");
}
}
listView2.Items.Add(listViewItem);
}
listView2.EndUpdate();
}
private void Button5_Click(object sender, EventArgs e)
{
listView2.Clear();
listView2.Columns.Add("员工号", listView1.Width / 4 - 1, HorizontalAlignment.Left);//HorizontalAlignment的作用
listView2.Columns.Add("员工姓名", listView1.Width / 4 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("公司名", listView1.Width / 4 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("薪水", listView1.Width / 4 - 1, HorizontalAlignment.Left);
DataTable table = db.getBySql(@"select [EMPLOYEE].[EmpNo],[EMPLOYEE].[EmpName],[COMPANY].[CmpName],[WORKS].[Salary] from [EMPLOYEE],
[COMPANY],[WORKS]" +
" where [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo]" +
" and [COMPANY].[CmpNo]=[WORKS].[CmpNo]" +
" and [EMPLOYEE].[EmpName]='" + comboBox2.Text + "'");
listView2.BeginUpdate();
for (int i = 0; i < table.Rows.Count; i++)
{
ListViewItem listViewItem = new ListViewItem(); //no
for (int j = 0; j < table.Columns.Count; j++)
{
if (j <= 0)
{
listViewItem.Text = table.Rows[i][j] + "";
}
else
{
listViewItem.SubItems.Add(table.Rows[i][j] + "");
}
}
listView2.Items.Add(listViewItem);
}
listView2.EndUpdate();
}
public Form2()
{
InitializeComponent();
}
private void Form2_Load(Object sender,EventArgs e)
{
if ((int)Intent.dict["form1_flag"] == 0)
{
this.Text = Intent.dict["form1_text"] + "";
textBox1.Focus();
}
else
{
this.Text = Intent.dict["form1_text"] + "";
textBox1.Text = Intent.dict["form1_selectedItems0"]+"";
textBox2.Text = Intent.dict["form1_selectedItems1"] + "";
if(Intent.dict["form1_selectedItems2"] + "" == "男")
{
radioButton1.Checked = true;
}
else
{
radioButton2.Checked = true;
}
textBox3.Text = Intent.dict["form1_selectedItems3"] + "";
textBox1.Focus();
textBox1.SelectAll();
}
}
private void Button1_Click(object sender, EventArgs e)
{
if(textBox1.Text == "" || textBox2.Text == ""|| textBox3.Text == ""||(!radioButton1.Checked && !radioButton2.Checked))
{
MessageBox.Show("任意一项没有完成填写", this.Text);
}
else
{
Intent.dict["form2_textbox1_text"] = textBox1.Text;
Intent.dict["form2_textbox2_text"] = textBox2.Text;
if (radioButton1.Checked)
{
Intent.dict["form2_radioButton"] = "男";
}
else
{
Intent.dict["form2_radioButton"] = "女";
}
Intent.dict["form2_textbox3_text"] = textBox3.Text;
this.DialogResult = DialogResult.OK;
this.Close();
}
}
3664
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
