private void 除ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (iss())
{
string name = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
DialogResult dr = MessageBox.Show("该操作不可逆！确认删除" + name + "的相关信息吗？", "警告", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
if (dr.Equals(DialogResult.OK))
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("delete from [Dinfo] where [ddrivingno]='{0}'", name);
sb.AppendLine();
sb.AppendFormat("delete from [Vinfo] where [vdrivingno]='{0}'", name);
sb.AppendLine();
sb.AppendFormat("delete from [Pinfo] where [pdrivingno]='{0}'", name);
dbsc d0 = new dbsc();
d0.OpenConnection();
SqlCommand cmd = new SqlCommand(sb.ToString(), d0.Connection);
if (cmd.ExecuteNonQuery() != 0)
{
MessageBox.Show("删除成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.load();
}
d0.CloseConnection();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string li = textBox1.Text;
dbsc d0 = new dbsc();
d0.OpenConnection();
string sql = "select * from [Linfo] where [llicenseno]='" + li + "'";
SqlCommand cmd = new SqlCommand(sql, d0.Connection);
if (!cmd.ExecuteScalar().ToString().Equals(string.Empty))
{
MessageBox.Show("该牌号已存在", "提示", MessageBoxButtons.OK);
}
else
{
string type = textBox2.Text;
string st = textBox3.Text;
string own = comboBox1.SelectedItem.ToString();
string ifm = "0";
if (own.Length != 0)
{
ifm = "1";
}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("insert [Linfo] values ('{0}',{1},'{2}',{3},'{4}')", li, ifm, type, st, own);
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
MessageBox.Show("添加成功", "提示", MessageBoxButtons.OK);
d0.CloseConnection();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (check())
{
string id = textBox1.Text;
string name = textBox2.Text;
string age = textBox3.Text;
string contact = textBox4.Text;
string date = textBox5.Text;
string address = textBox6.Text;
string idcard = textBox7.Text;
StringBuilder sb = new StringBuilder();
if (b == true)
{
sb.AppendFormat("insert [Pinfo] values ('{0}','{1}',{2},'{3}',{4},'{5}','{6}',null)", id, name, age, contact, date, address, idcard);
}
else
{
sb.AppendFormat("update [Pinfo] set [name]='{0}',[age]={1},[contact]='{2}',[address]='{3}' where [pdrivingno]='{4}'", name, age, contact, address, id);
}
dbsc d0 = new dbsc();
d0.OpenConnection();
SqlCommand cmd = new SqlCommand(sb.ToString(), d0.Connection);
cmd.ExecuteNonQuery();
d0.CloseConnection();
MessageBox.Show("更新成功", "提示", MessageBoxButtons.OK);
f1.Show();
this.Dispose();
}
}
public SqlDataAdapter adapter; //定义适配器
public void insert()
{
//语句多表查询
string sql = "select unitno,unitname,unitphone from Uinfo";
this.dc = new DataSet(); //创建数据集对象
adapter = new SqlDataAdapter(); //创建sqldataadapter对象
dbsc db0 = new dbsc();
SqlCommand com = new SqlCommand(sql.ToString(), db0.Connection);
adapter.SelectCommand = com; //装载数据
adapter.Fill(dc, "temp"); //填充
this.dataGridView1.DataSource = dc.Tables["temp"]; //绑定数据源
}
private void load()
{
dbsc d0 = new dbsc();
d0.OpenConnection();
string sql = "select * from [Pinfo]";
SqlCommand cmd = new SqlCommand(sql, d0.Connection);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
d0.CloseConnection();
DataSet ds = new DataSet();
sda.Fill(ds, "p");
this.dataGridView1.DataSource = ds.Tables["p"];
}
private void 驾照查询ToolStripMenuItem_Click(object sender, EventArgs e)
{
dbsc db0 = new dbsc();
db0.OpenConnection();
string sql = "select * from [Dinfo] where [ddrivingno]='" + did + "'";
SqlCommand cmd = new SqlCommand(sql, db0.Connection);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
textBox1.Text = "驾驶证编号：" + did + "\r\n";
textBox1.Text += "驾驶证类型：" + dr["dtype"].ToString() + "\r\n";
textBox1.Text += "发证日期：" + dr["date"].ToString() + "\r\n";
textBox1.Text += "记分：" + dr["recorf"].ToString() + "\r\n";
textBox1.Text += "状态：" + dr["stat"].ToString();
}
db0.CloseConnection();
}
private void 现有抽取ToolStripMenuItem_Click(object sender, EventArgs e)
{
label1.Visible = false;
button1.Visible = false;
dbsc d0 = new dbsc();
d0.OpenConnection();
string sql = "select [llicenseno] from [Linfo] where [ifmaster]=0";
SqlCommand cmd = new SqlCommand(sql, d0.Connection);
SqlDataReader sdr = cmd.ExecuteReader();
List <string> lid = new List <string>();
while (sdr.Read())
{
lid.Add(sdr["llicenseno"].ToString());
}
d0.CloseConnection();
Random rd = new Random();
int num = rd.Next(lid.Count - 1);
textBox1.Text = "恭喜抽中号牌：" + lid[num];
}
private void alog()
{
if (textBox1.Text.ToString().Length == 7)
{
try {
int id = Convert.ToInt32(textBox1.Text.ToString());
dbsc d0 = new dbsc();
d0.OpenConnection();
string sql = "select [pwd] from [admin] where [ID]=" + id;
SqlCommand cmd = new SqlCommand(sql, d0.Connection);
string pwd = cmd.ExecuteScalar().ToString();
d0.CloseConnection();
if (pwd.Equals(textBox2.Text.ToString()))
{
MessageBox.Show("登录成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
admin ad1 = new admin(this.FindForm(), id.ToString());
this.textBox1.Text = "";
this.textBox2.Text = "";
this.Hide();
ad1.Show();
}
else
{
MessageBox.Show("用户名或密码错误！", "登录失败", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception)
{
MessageBox.Show("应输入7位数字账号", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
textBox1.Focus();
}
}
else
{
MessageBox.Show("应输入7位账号", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
textBox1.Focus();
}
}
private void dlog()
{
if (textBox1.Text.ToString().Length == 12)
{
string id = textBox1.Text.ToString();
string pwd = textBox2.Text.ToString();
dbsc d0 = new dbsc();
d0.OpenConnection();
StringBuilder sb = new StringBuilder();
sb.AppendLine("declare @pwd0 varchar");
sb.AppendFormat("select @pwd0=[pwd] from [Pinfo] where [pdrivingno]='{0}'", id);
sb.AppendLine();
sb.AppendLine("if @pwd0 is null");
sb.AppendFormat("select [idcard] from [Pinfo] where [pdrivingno]='{0}'", id);
sb.AppendLine();
sb.AppendLine("else");
sb.AppendFormat("select [pwd] from [Pinfo] where [pdrivingno]='{0}'", id);
SqlCommand cmd = new SqlCommand(sb.ToString(), d0.Connection);
string pwd0 = cmd.ExecuteScalar().ToString();
d0.CloseConnection();
if (pwd.Equals(pwd0) || pwd.Equals(pwd0.Substring(pwd0.Length - 6, 6)))
{
MessageBox.Show("登录成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
owner o1 = new owner(this.FindForm(), id);
o1.Show();
this.Hide();
}
else
{
MessageBox.Show("用户名或密码错误！", "登录失败", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("账号为12位驾驶证号", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
textBox1.Focus();
}
}
private void button1_Click(object sender, EventArgs e)
{
string id = textBox1.Text;
if (id.Length == 12)
{
dbsc d0 = new dbsc();
d0.OpenConnection();
switch (comboBox1.SelectedIndex)
{
case 0:
int score = 6;
string t;
switch (comboBox2.SelectedIndex)
{
case 0:
score = 1;
break;
case 1:
score = 2;
break;
case 2:
score = 3;
break;
}
StringBuilder sb = new StringBuilder();
sb.AppendLine("declare @s int");
sb.AppendFormat("select @s=[recorf]+{1} from [Dinfo] where [ddrivingno]='{0}'", id, score);
sb.AppendLine("if @s>=12");
sb.AppendLine("begin");
sb.AppendFormat("update [Dinfo] set [recorf]=12,[stat]='暂扣' where [ddrivingno]='{0}'", id);
sb.AppendLine();
sb.AppendLine("end");
sb.AppendLine("else");
sb.AppendFormat("update [Dinfo] set [recorf]=@s where [ddrivingno]='{0}'", id);
sb.AppendLine();
sb.AppendFormat("select [recorf] from [Dinfo] where [ddrivingno]='{0}'", id);
SqlCommand cmd = new SqlCommand(sb.ToString(), d0.Connection);
int t0 = Convert.ToInt32(cmd.ExecuteScalar());
if (t0 == 12)
{
t = "编号为" + id + "的驾照记分已满12分，执行暂扣";
}
else if (t0 != 0)
{
t = "登记成功，编号为" + id + "的驾照记分为" + t0;
}
else
{
t = "查无此照\n";
}
textBox2.Text += t + "\n";
break;
case 1:
string sql = "update [Dinfo] set [recorf]=12,[stat]='暂扣' where [ddrivingno]='" + id + "'";
SqlCommand cmd0 = new SqlCommand(sql, d0.Connection);
if (cmd0.ExecuteNonQuery() != 0)
{
textBox2.Text += "登记成功，编号为" + id + "的驾照已暂扣\n";
}
else
{
textBox2.Text += "查无此照\n";
}
break;
case 2:
string sql0 = "update [Dinfo] set [recorf]=12,[stat]='吊销' where [ddrivingno]='" + id + "'";
SqlCommand cmd1 = new SqlCommand(sql0, d0.Connection);
if (cmd1.ExecuteNonQuery() != 0)
{
textBox2.Text += "登记成功，编号为" + id + "的驾照已吊销\n";
}
else
{
textBox2.Text += "查无此照\n";
}
break;
}
d0.CloseConnection();
}
else
{
MessageBox.Show("请输入12位驾驶证号", "提示", MessageBoxButtons.OK);
textBox1.Focus();
}
}
private void button1_Click(object sender, EventArgs e)
{
string lno = check(textBox1.Text, "vlicenseno");
string cl = check(textBox2.Text, "color");
string br = check(textBox3.Text, "brand");
string on = check(textBox4.Text, "name");
string ty = check(comboBox1.SelectedItem.ToString(), "motorcycle");
StringBuilder sb = new StringBuilder();
sb.AppendLine("select v.* from [Vinfo] v inner join [Pinfo] p ");
sb.AppendLine("on (v.vdrivingno=p.pdrivingno)");
sb.Append("where ");
if (lno.Length != 0)
{
sb.Append(lno);
k--;
if (k > 0)
{
sb.Append(" and ");
}
}
if (cl.Length != 0)
{
sb.Append(cl);
k--;
if (k > 0)
{
sb.Append(" and ");
}
}
if (br.Length != 0)
{
sb.Append(br);
k--;
if (k > 0)
{
sb.Append(" and ");
}
}
if (on.Length != 0)
{
sb.Append(on);
k--;
if (k > 0)
{
sb.Append(" and ");
}
}
if (ty.Length != 0)
{
sb.Append(ty);
k--;
}
dbsc d0 = new dbsc();
d0.OpenConnection();
SqlCommand cmd = new SqlCommand(sb.ToString(), d0.Connection);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds, "p");
d0.CloseConnection();
dataGridView1.DataSource = ds.Tables["p"];
}
