c#車(chē)隊(duì)管理系統(tǒng)及部分代碼

1、一、系統(tǒng)概述通過(guò)車(chē)隊(duì)運(yùn)輸系統(tǒng)數(shù)據(jù)庫(kù)平臺(tái)的搭建，實(shí)現(xiàn)以下三方面功能：1、 記錄車(chē)隊(duì)聘用司機(jī)的信息，幫助車(chē)隊(duì)管理司機(jī)。2、 記錄司機(jī)使用車(chē)輛記錄，也是司機(jī)的工作記錄，便于發(fā)薪。3、 記錄車(chē)輛信息，便于車(chē)輛管理以及維修等工作。二、系統(tǒng)ER圖設(shè)計(jì)1、車(chē)隊(duì)系統(tǒng)ER圖三、系統(tǒng)底層數(shù)據(jù)庫(kù)設(shè)計(jì)1、關(guān)系模型關(guān)系模型如下：車(chē)隊(duì)（車(chē)隊(duì)號(hào)，車(chē)隊(duì)名），即fleet_info(fleetNum，fleetName)聘用（車(chē)隊(duì)號(hào)，起聘日期，聘期，司機(jī)編號(hào)），即engage_info(fleetNum ，engageDate，engageTime，driverNum)司機(jī)（司機(jī)編號(hào)，姓名，電話(huà)），即drivers_info(

2、driverNum，driverName,driverCall)使用（司機(jī)編號(hào)，日期，公里數(shù)，車(chē)牌號(hào)），即caruse_info(driverNum ，useDate，useJourney，carNum)車(chē)輛（車(chē)牌號(hào)，廠家），即car_info(carNum，carCompany)2、數(shù)據(jù)庫(kù)創(chuàng)建及約束、索引的設(shè)置#建立數(shù)據(jù)庫(kù)fleetDROP DATABASE if exists fleet;CREATE DATABASE fleet default charset utf8 COLLATE utf8_general_ci;USE fleet;-#創(chuàng)建一個(gè)車(chē)隊(duì)表fleet_info#表名：fl

3、eet_info#作者：hhy#日期：2014.3.17#版本： 1.0#描述：保存車(chē)隊(duì)信息#具體內(nèi)容：# fleetNum車(chē)隊(duì)號(hào)# fleetName車(chē)隊(duì)名-CREATE TABLE fleet_info(fleetNum char(8) ,fleetName varchar(20) UNIQUE,CONSTRAINT pk_fleet_info PRIMARY KEY(fleetNum),CONSTRAINT ck_fleet_info CHECK (fleetNum like0-90-90-90-90-90-90-90-9);CREATE UNIQUE INDEX IX_fleet_in

4、fo ON fleet_info(fleetName);-#創(chuàng)建一個(gè)司機(jī)表drivers_info#表名：drivers_info#作者：hhy#日期：2014.3.17#版本： 1.0#描述：保存司機(jī)資料#具體內(nèi)容：# driverNum司機(jī)編號(hào)# driverName姓名# driverCall電話(huà)-CREATE TABLE drivers_info(driverNum char(8),driverName varchar(20) not null,driverCall char(8) not null,CONSTRAINT pk_drivers_info PRIMARY KEY(driv

5、erNum),CONSTRAINT ck_drivers_info1 CHECK (driverNum like0-90-90-90-90-90-90-90-9),CONSTRAINT ck_drivers_info2 CHECK (driverNum like0-90-90-90-90-90-90-90-90-90-90-9);CREATE INDEX IX_drivers_info ON drivers_info(driverNum);-#創(chuàng)建一個(gè)車(chē)輛表car_info#表名：car_info#作者：hhy#日期：2014.3.17#版本： 1.0#描述：保存車(chē)輛信息#具體內(nèi)容：# car

6、Num車(chē)牌號(hào)# carCompany廠家-CREATE TABLE car_info(carNum varchar(8),carCompany varchar(40) DEFAULT abc,CONSTRAINT pk_car_info PRIMARY KEY(carNum);-#創(chuàng)建一個(gè)使用表caruse_info#表名：caruse_info#作者：hhy#日期：2014.3.17#版本： 1.0#描述：保存使用信息#具體內(nèi)容：# driverNum司機(jī)編號(hào)# useDate日期# useJourney公里數(shù)# carNum車(chē)牌號(hào)-CREATE TABLE caruse_info(driv

7、erNum char(8) not null,useDate date not null,useJourney varchar(20) not null,carNum char(8) not null,CONSTRAINT fk_caruse1_info FOREIGN KEY (driverNum) REFERENCES drivers_info (driverNum),CONSTRAINT fk_caruse2_info FOREIGN KEY (carNum) REFERENCES car_info (carNum);-#創(chuàng)建一個(gè)聘用表engage_info#表名engage_info#

8、作者：hhy#日期：2014.3.17#版本： 1.0#描述：保存聘用信息#具體內(nèi)容：# fleetNum車(chē)隊(duì)號(hào)# engageDate起聘日期# engageTime聘期# driverNum司機(jī)編號(hào)-CREATE TABLE engage_info(fleetNum char(8) not null,engageDate date not null,engageTime date not null,driverNum char(8) not null,CONSTRAINT engage_info1 FOREIGN KEY (fleetNum) REFERENCES fleet_info (

9、fleetNum),CONSTRAINT engage_info2 FOREIGN KEY (driverNum) REFERENCES drivers_info (driverNum);#增加一張用戶(hù)名密碼表CREATE TABLE USERT(username char(10) not null,psd char(10) not null);四、數(shù)據(jù)庫(kù)記錄的插入#向插入fleet_info三個(gè)車(chē)隊(duì)信息INSERT INTO fleet_info VALUES(31202257,baofeng);INSERT INTO fleet_info VALUES(31202255,tianxia);

10、INSERT INTO fleet_info VALUES(31202259,tianhuang);#插入四個(gè)司機(jī)的信息INSERT INTO drivers_info VALUES(00000001,Tom;INSERT INTO drivers_info VALUES(00000002,Jim;INSERT INTO drivers_info VALUES(00000003,Amy;INSERT INTO drivers_info VALUES(00000004,Jone;#插入三輛車(chē)的

11、信息INSERT INTO car_info VALUES(C199,Tianlai);INSERT INTO car_info VALUES(C128,Tianlai);INSERT INTO car_info VALUES(C155,Tianlai);#插入聘用信息INSERT INTO engage_info VALUES(31202257,1994-04-18,0001-00-00,00000001);INSERT INTO engage_info VALUES(31202255,1994-08-26,0001-00-00,00000004);INSERT INTO engage_in

12、fo VALUES(31202259,1994-08-22,0001-00-00,00000003);#插入使用信息INSERT INTO caruse_info VALUES(00000001,2014-04-18,11.2,C199);INSERT INTO caruse_info VALUES(00000002,2014-04-18,11.2,C128);INSERT INTO caruse_info VALUES(00000003,2014-04-18,11.2,C155);#插入管理員信息INSERT INTO USERT VALUES(admin,admin);四、 窗體設(shè)計(jì)及關(guān)鍵

13、代碼描述準(zhǔn)備工作：1. 添加引用導(dǎo)入dll2. 建立數(shù)據(jù)庫(kù)操作工具類(lèi)DBHelperusing System;using System.Collections.Generic;using System.Linq;using System.Text;using MySql.Data.MySqlClient;/導(dǎo)入用MySql的包using System.Data;/引用DataTablenamespace WindowsFormsApplication1 class DBHelper string fleet = fleet;/數(shù)據(jù)庫(kù)名 string localhost = localhost;

14、/主機(jī) string root = root;/數(shù)據(jù)庫(kù)用戶(hù)名 string psw = 123456;/數(shù)據(jù)庫(kù)密碼 MySqlConnection mysqlconn = null; MySqlDataAdapter sda = null; DataTable dt = null; MySqlCommand cmd = null; DataSet ds = null; public MySqlConnection GetConn()/獲得一個(gè)連接 mysqlconn = new MySqlConnection(Database= + fleet + ;Data Source= + localh

15、ost + ;User Id= + root + ;Password= + psw + ;allow zero datetime = true;charset=utf8); return mysqlconn; public DataSet GetDs(string sql)/獲得DataSet mysqlconn = this.GetConn(); sda = new MySqlDataAdapter(sql, mysqlconn); ds = new DataSet(); return ds; public DataTable Selectinfo(string sql)/執(zhí)行查詢(xún)操作 tr

16、y ds = this.GetDs(sql); sda.Fill(ds,pro); dt = ds.Tablespro; return dt; catch (Exception) throw; public int AddDelUpdate(string sql)/執(zhí)行添加刪除更新操作 try mysqlconn = this.GetConn(); mysqlconn.Open(); cmd = new MySqlCommand(sql, mysqlconn); int i = cmd.ExecuteNonQuery(); return i; catch (Exception) return

17、-1; public void Close()/關(guān)閉數(shù)據(jù)庫(kù) if (this.mysqlconn != null) this.mysqlconn.Close(); 登錄界面：登錄按鈕監(jiān)聽(tīng)代碼private void BtnLogin_Click(object sender, EventArgs e) if (TxtName.Text.Trim() = | TxtPsw.Text.Trim() = ) MessageBox.Show(請(qǐng)輸入用戶(hù)名或密碼, 提示); TxtName.Focus(); else string str = CboShen.SelectedItem.ToString()

18、; switch (str) case 司機(jī): try string username = TxtName.Text.Trim().ToString(); string sql = select driverPsd,driverNum from drivers_info where driverName = + username + ; DBHelper db = new DBHelper(); db.GetConn(); DataTable dt = db.Selectinfo(sql); string psd = dt.Rows0dt.Columns0.ToString(); string

19、 id = dt.Rows0dt.Columns1.ToString(); db.Close(); if (psd.Equals(TxtPsw.Text.Trim().ToString() Moddriver fr = new Moddriver(); fr.id = id; fr.Show(); this.Close(); else MessageBox.Show(用戶(hù)名或密碼錯(cuò)誤, 提示); TxtName.Focus(); catch (Exception ) MessageBox.Show(找不到用戶(hù)名 , 提示); break; case 管理員: try string userna

20、me = TxtName.Text.Trim().ToString(); string sql = select psd from usert where username = + username + ; DBHelper db = new DBHelper(); db.GetConn(); DataTable dt = db.Selectinfo(sql); string psd =dt.Rows0dt.Columns0.ToString(); if (psd.Equals(TxtPsw.Text.Trim().ToString() FrmMain myFrm = new FrmMain(

21、); myFrm.Show(); this.Close(); else MessageBox.Show(用戶(hù)名或密碼錯(cuò)誤, 提示); TxtName.Focus(); catch (Exception) MessageBox.Show(用戶(hù)名或密碼錯(cuò)誤, 提示); break; 注冊(cè)界面（添加操作）：添加監(jiān)聽(tīng)代碼如下：private void button1_Click(object sender, EventArgs e) try drivers d = new drivers(); if (textBox1.Text.Trim() = | textBox3.Text.Trim() = |

22、textBox2.Text.Trim() = | textBox4.Text.Trim() = ) MessageBox.Show(請(qǐng)輸入完整信息！, 錯(cuò)誤, MessageBoxButtons.OK, MessageBoxIcon.Error); else if (d.doit(textBox1.Text.Trim() != null) MessageBox.Show(d.doit(textBox1.Text.Trim(), 提示); else DBHelper db = new DBHelper(); MySqlConnection mysqlconn = db.GetConn(); st

23、ring num = textBox1.Text.Trim(); string name = textBox2.Text.Trim(); string call = textBox3.Text.Trim(); string psd = textBox4.Text.Trim(); string sql = insert into drivers_info values( + num + , + name + , + call + , + psd + ); int i = db.AddDelUpdate(sql); if (i != -1) MessageBox.Show(添加成功, 提示); t

24、his.Close(); else MessageBox.Show(司機(jī)編號(hào)已經(jīng)被使用, 提示); db.Close(); catch (Exception) MessageBox.Show(添加失敗, 提示); 車(chē)隊(duì)管理界面：查詢(xún)按鈕監(jiān)聽(tīng)代碼：private void button5_Click(object sender, EventArgs e)/查詢(xún) string sql = select fleet_info.fleetNum as 車(chē)隊(duì)號(hào),fleetName as 車(chē)隊(duì)名,count(engage_info.fleetNum) as 車(chē)隊(duì)成員人數(shù),40-count(engage_i

25、nfo.fleetNum) as 車(chē)隊(duì)成員余量 from fleet_info left join engage_info on fleet_info.fleetNum=engage_info.fleetNum where fleetName= + this.textBox1.Text.Trim() + group by fleet_info.fleetNum; DBHelper db = new DBHelper(); MySqlConnection mysqlconn = db.GetConn(); DataSet ds = db.GetDs(sql); MySqlDataAdapter

26、sda = new MySqlDataAdapter(sql, mysqlconn); sda.Fill(ds, 車(chē)隊(duì)信息); this.dataGridView1.DataSource = ds; this.dataGridView1.DataMember = 車(chē)隊(duì)信息; this.dataGridView1.AutoGenerateColumns = true; for (int i = 1; i this.dataGridView1.ColumnCount; i+) this.dataGridView1.Columnsi.DefaultCellStyle.SelectionBackCol

27、or = Color.White; this.dataGridView1.Columnsi.DefaultCellStyle.SelectionForeColor = Color.Black; this.dataGridView1.Columnsi.ReadOnly = true; db.Close(); 刪除按鈕監(jiān)聽(tīng)代碼： private void button3_Click(object sender, EventArgs e)/刪除車(chē)隊(duì) if (this.dataGridView1.SelectedCells = null) MessageBox.Show(請(qǐng)選擇要?jiǎng)h除的項(xiàng)！, 錯(cuò)誤, MessageBoxButtons.OK, MessageBoxIcon.Error); else if (this.dataGridView1.CurrentCell.ColumnIndex = 0) string st = this.dataGri