一、【实验目的】通过编写完整的程序，掌握SqlServer各种语句的使用，添加，删除，更新等，学习程序的编写，学会分析问题，解决问题。
二、【实验内容】通过学过的S qlS erv er知识，编写手机号卡销售系统，实现具体功能：1、实现管理员的登录操作2、创建数据库，建立管理员账号表，手机号码表，客户表3、输入一个号码段，比如13882368，生成手机号码最后四位4、将生成的手机号码进行分类，A类卡，比如后四位为1234,4567等，B类卡，比如后四位为1111,4444等，C类卡，比如出去A,B类卡末尾为6或者8的号码，D类卡，出去A,B,C类卡号类型的卡5、对所选择的卡号类型进行显示，选择，查询已出售卡的信息6、对所出售的卡号进行状态修改，将“未出售”改为“已出售”，并将客户的信息写入表中三、【实验步骤】1、E-R图2、登陆程序编写①登陆窗口界面②登陆窗口代码using System;using System.Collections.Generic; using ponentModel; using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Data.SqlClient; namespace WindowsApplication3 {public partial class Form1 : Form{public Form1(){ //这段代码的功能是在SqlServer中创建数据库和生产表，包括管理人员表，号码表和销售表，当程序运行时，它会自动进行创建SqlConnection conn = new SqlConnection("server=.;user id=sa;password=;Trusted_Connecti on=yes;");conn.Open();SqlCommand myCommand = conn.CreateCommand();mandText = " select * from master.dbo.sysdatabases where name = '手机号码生成系统'"; SqlDataReader sdr=myCommand.ExecuteReader();if (sdr.Read()){sdr .Close ();}else{sdr.Close();mandText = "create database 手机号码生成系统";myCommand.ExecuteNonQuery();mandText = "use 手机号码生成系统";myCommand.ExecuteNonQuery();mandText = "create table worker(ID int identity primary key,name varchar(50) not null,pwd varchar(50) not null)";myCommand.ExecuteNonQuery();mandText = "insert into workervalues('wang','wang')";myCommand.ExecuteNonQuery();mandText = "create table card (telenumber varchar(30) primary key,sort varchar(30) not null,sta varchar(30) not null)";myCommand.ExecuteNonQuery();mandText = "create table client (telenumbervarchar(30) primary key,name varchar(50) not null,sexvarchar(10) not null,address varchar(50) not null,IDcard varchar(30) not null)";myCommand.ExecuteNonQuery();}conn.Close();InitializeComponent();}private voidbut2Close_Click(object sender, EventArgs e){//弹出提示信息，如果点击确认退出，则退出应用程序if (MessageBox.Show("你确定要退出系统吗？", "提示信息!",MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) == DialogResult.OK){Application.Exit();} }private voidlogin_Click(object sender, EventArgs e){//登陆代码，判断用户名和密码是否和数据库中的用户名和密码是否相同，如果登陆成功就弹出FORM2窗口SqlConnection conn = newSqlConnection("server=.;database =手机号码生成系统;uid=sa;pwd=;");SqlCommand cmd = new SqlCommand("select * from worker where name='" +textBox1.Text.Trim() + "' and pwd='" + textBox2.Text.Trim() + "'", conn);conn .Open() ;SqlDataReader sdr=cmd.ExecuteReader();if (sdr.Read ()){Form2 form2 = new Form2();form2.ShowDialog();Close();}else{MessageBox.Show("登陆失败!");this.textBox1.Text = "";this.textBox2.Text = "";}conn.Close();}}}3、手机号码生成程序编写①手机号码生成窗口界面（1）号码生成（2）号码选择（3）出售信息②手机号码生成代码using System;using System.Collections.Generic; using ponentModel; using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Data.SqlClient; namespace WindowsApplication3 {public partial class Form2 : Form{public Form2(){InitializeComponent();}public static string A = "A 类卡";public static string B = "B 类卡"; public static string C = "C 类卡";public static string D = "D 类卡";public static string F = "未出售";private voidForm2_Load(object sender, EventArgs e){}private voidradioButton1_Click(object sender, EventArgs e){//点击选择A类卡按钮后，查找出类型为'A类卡'并且销售状态为‘未出售’SqlConnection conn = newSqlConnection("server=.;database =手机号码生成系统;uid=sa;pwd=;");SqlDataAdapter sda= new SqlDataAdapter("select * from card where sort='"+A+"' andsta='"+F+"'", conn);DataTable dt = new DataTable();sda.Fill(dt);BindingSource bs = new BindingSource();bs.DataSource = dt;dataGridView1.DataSource = bs;bs.PositionChanged+=newEventHandler(bs_PositionChanged) ;}private voidbs_PositionChanged(object sender, EventArgs e){//对所选择的卡号进行显示BindingSource bsc = (BindingSource)dataGridView1.Dat aSource;DataRowView div = (DataRowView)bsc.Current;label3.Text =div["telenumber"].ToString();}private voidradioButton2_Click(object sender, EventArgs e){//点击选择B类卡按钮后，查找出类型为'B类卡'并且销售状态为‘未出售’SqlConnection conn = newSqlConnection("server=.;database =手机号码生成系统;uid=sa;pwd=;");SqlDataAdapter sda = new SqlDataAdapter("select * from card where sort='"+B+"' andsta='"+F+"'", conn);DataTable dt = new DataTable();sda.Fill(dt);BindingSource bs = new BindingSource();bs.DataSource = dt;dataGridView1.DataSource = bs;bs.PositionChanged += newEventHandler(bs_PositionChanged) ;}private voidradioButton3_Click(object sender, EventArgs e){//点击选择C类卡按钮后，查找出类型为'C类卡'并且销售状态为‘未出售’SqlConnection conn = newSqlConnection("server=.;database =手机号码生成系统;uid=sa;pwd=;");SqlDataAdapter sda = new SqlDataAdapter("select * from card where sort='"+C+"' andsta='"+F+"'", conn);DataTable dt = new DataTable();sda.Fill(dt);BindingSource bs = new BindingSource();bs.DataSource = dt;dataGridView1.DataSource = bs;bs.PositionChanged += newEventHandler(bs_PositionChanged) ;}private voidradioButton4_Click(object sender, EventArgs e){//点击选择D类卡按钮后，查找出类型为'D类卡'并且销售状态为‘未出售’SqlConnection conn = newSqlConnection("server=.;database =手机号码生成系统;uid=sa;pwd=;");SqlDataAdapter sda = new SqlDataAdapter("select * from card where sort='"+D+"' andsta='"+F+"'", conn);DataTable dt = new DataTable();sda.Fill(dt);BindingSource bs = new BindingSource();bs.DataSource = dt;dataGridView1.DataSource = bs;bs.PositionChanged += newEventHandler(bs_PositionChanged) ;}private voidbutton1_Click(object sender, EventArgs e){Form3 f3 = newForm3(bel3.Text.Trim()); f3.ShowDialog();}private voidbutton2_Click(object sender, EventArgs e){Close();}private voidbutton3_Click(object sender, EventArgs e){//此处为手机号码最后四位的生成代码，生产后，会自动将所生成的电话号码插入到表中try{SqlConnection conn = newSqlConnection("server=.;database=手机号码生成系统;uid=sa;pwd=;");SqlCommand cmd = conn.CreateCommand();string tel=textBox1.Text.Trim ();conn.Open();mandText = "truncate table card";cmd.ExecuteNonQuery();for (int i = 10000;i <= 19999; i++){int temp1, temp2, temp3, temp4;string strsub = Convert.ToString(i).Substring(1, 4);string str = tel + strsub;temp1 = Convert.ToInt32(str.Substring(7, 1));temp2 = Convert.ToInt32(str.Substring(8, 1));temp3 = Convert.ToInt32(str.Substring(9, 1));temp4 = Convert.ToInt32(str.Substring(10, 1));if ((temp1 + 1 == temp2 && temp2 + 1 == temp3 && temp3 + 1 == temp4) || (temp1 - 1== temp2 && temp2 - 1 == temp3 &&temp3 - 1 == temp4)){mandText = "insert into card(telenumber,sort,sta)values('" + str +"','"+A+"','"+F+"')";cmd.ExecuteNonQuery();}else if (temp1 == temp2 && temp3 == temp4 && temp2 == temp3){mandText = "insert into card(telenumber,sort,sta)values('" + str + "','"+B+"','" + F + "')";cmd.ExecuteNonQuery();}else if (temp4 == 6 || temp4 == 8){mandText = "insert into card(telenumber,sort,sta)values('" + str + "','" + C + "','" + F + "')";cmd.ExecuteNonQuery();}else{mandText = "insert into card(telenumber,sort,sta)values('" + str + "','" + D + "','" + F + "')";cmd.ExecuteNonQuery();}}conn.Close(); MessageBox.Show("号码生成成功！", "提示信息!", MessageBoxButtons.OK, rmation);}catch{MessageBox.Show("号码生成失败！", "提示信息!", MessageBoxButtons.OK, rmation);}}private voiddataGridView1_CellContentClick(o bject sender, DataGridViewCellEventArgs e){}private voidtextBox1_TextChanged(object sender, EventArgs e){}private voidbutton4_Click(object sender, EventArgs e){SqlConnection conn = newSqlConnection("server=.;database=手机号码生成系统;uid=sa;pwd=;");SqlDataAdapter sda = new SqlDataAdapter("select * from client", conn);DataTable dt = new DataTable();sda.Fill(dt);BindingSource bs = new BindingSource();bs.DataSource = dt;dataGridView1.DataSource = bs; } } }3、客户信息程序编写①客户信息窗口界面②客户信息代码using System;using System.Collections.Generic; using ponentModel; using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;namespace WindowsApplication3 {public partial class Form3 : Form {public Form3(){InitializeComponent();}public Form3(string strlable3Text){InitializeComponent();bel7.Text= strlable3Text;}private voidbutton2_Click(object sender, EventArgs e){Close();}private voidbutton1_Click(object sender, EventArgs e){//添加客户信息SqlConnection conn = newSqlConnection("server=.;database =手机号码生成系统;uid=sa;pwd=;");conn.Open();string T = "已出售"; SqlCommand cmd = conn.CreateCommand();if (MessageBox.Show("确定出售？", "提示信息", MessageBoxButtons.YesNo, rmation) == DialogResult.Yes){mandText = "insert intoclient(telenumber,name,sex,addre ss,IDcard) values('"+label7.Text.Trim () + "','" + textBox1.Text.Trim() + "','" + textBox2.Text.Trim() + "','" + textBox3.Text.Trim() + "','"+textBox4.Text.Trim () + "')";cmd.ExecuteNonQuery();mandText = "update card set sta='"+T+"' where telenumber='" + label7.Text.Trim() + "'";cmd.ExecuteNonQuery();this.Close();}else{this.Close();}conn.Close();}private voidForm3_Load(object sender, EventArgs e){}}}四、【实验总结】通过这次实验，我掌握了很多以前没有掌握的知识，不仅更加深入了解了SqlServer的一些知识和各种语句的使用，也同时学习了C#编程的技巧，虽然这次实验花了很多时间来做，但是这次综合实验使我在各方面都有所提高。
