考试内容汇总

Create table Student ( )

Create table Class ( )

insert into Student (ID,Name,ClassID) values ('A11201','Tom','001') insert into Student (ID,Name,ClassID) values ('A11202','Tom','001') insert into Student (ID,Name,ClassID) values ('A11203','Tom','002') insert into Student (ID,Name,ClassID) values ('A11204','Tom','003')

insert into Class (ClassID,ClassName,Quantity) values ('001','新闻',33) insert into Class (ClassID,ClassName,Quantity) values ('002','体育',35) insert into Class (ClassID,ClassName,Quantity) values ('003','科学',32)

select * from Student select * from Class

ClassID char(10) primary key, ClassName varchar(20) not null, Quantity int not null, ID char(10) primary key, Name varchar(20) not null, ClassID varchar(20) not null,

（Form设计页面如图，所有控件自上向下、自左向右添加，自己对应顺序）

在连接情况下要求完成的内容（第一题&第二题）——注意命名空间的添加

using System;

using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text;

using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Configuration;

namespace Union {

public partial class Form1 : Form {

public Form1() {

InitializeComponent(); }

//查询数据————DataReader读取数据（第一题）

private void button1_Click(object sender, EventArgs e) {

//创建连接对象

SqlConnection conn = new SqlConnection();

conn.ConnectionString = \"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDB;Data Source=.\";

//conn.ConnectionString = \"Server=506-16;Database=mydb;User ID=sa;Password=123456\";(学校机器使用该语句！！) //打开连接对象 conn.Open(); //创建命令对象

SqlCommand cmd = new SqlCommand(\"select top 1 ID,Name,ClassID from Student order by ID desc\", conn);

// cmd.Connection = conn; //在命令对象中执行SQL语句

SqlDataReader reader = cmd.ExecuteReader(); //处理数据

if (reader.HasRows) {

reader.Read();

textBox2.Text = reader[\"ID\"].ToString(); textBox3.Text = reader[\"Name\"].ToString(); textBox4.Text = reader[\"ClassID\"].ToString();

}

reader.Close(); //关闭连接对象 conn.Close(); }

//保存数据——使用连接模式（增、删、改）一条数据(第二题) private void button2_Click(object sender, EventArgs e) {

//创建连接对象

SqlConnection conn = new SqlConnection(); //利用xml后的连接语句

conn.ConnectionString = \"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDB;Data Source=.\"; //conn.ConnectionString =

ConfigurationManager.ConnectionStrings[\"connString\"].ConnectionString;(同上！！连接学校数据库注意！！) try

{

//不利用xml时，创建的连接语句： conn.ConnectionString = \"Server=connString;Database=mydb;User ID=sa;Password=123456\"; //打开连接对象 conn.Open(); //创建命令对象

SqlCommand cmd = new SqlCommand(\"insert into Student(ID,Name,ClassID) values(@ID,@Name,@ClassID)\");

cmd.Connection = conn;

cmd.Parameters.Add( cmd.Parameters[ cmd.Parameters.Add( cmd.Parameters[ cmd.Parameters.Add( cmd.Parameters[ {

textBox2.Text = textBox3.Text = textBox4.Text = } }

{

}

{

} {

{

conn.Close(); } } } } }

\"@ID\", System.Data.SqlDbType.Char); \"@ID\"].Value = this.textBox2.Text;

\"@Name\", System.Data.SqlDbType.VarChar); \"@Name\"].Value = this.textBox3.Text;

\"@ClassID\", System.Data.SqlDbType.VarChar); \"@ClassID\"].Value = this.textBox4.Text; //在命令对象中执行SQL语句 int i = cmd.ExecuteNonQuery(); if (i > 0) MessageBox.Show(\"保存成功！\"); \"\"; \"\"; \"\"; catch (SqlException ex) MessageBox.Show(\"SqlException:\" + ex.Message); catch (Exception ex) MessageBox.Show(\"Exception:\" + ex.Message); finally //关闭连接对象

if (conn.State == ConnectionState.Open)

在非连接情况下要求完成的内容（第三题&第五题）

using System;

using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text;

using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Configuration;

namespace Union {

public partial class Form1 : Form {

DataSet dsSchool; SqlDataAdapter ad; SqlCommandBuilder builder; public Form1() {

InitializeComponent();

createDataSet(); （！！！这句话别忘记写！！！） }

private void clearData() {

this.textBox2.Text = \"\"; this.textBox3.Text = \"\"; this.textBox4.Text = \"\"; }

private void createDataSet() {

//创建连接对象

SqlConnection conn = new SqlConnection();

conn.ConnectionString = \"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDB;Data Source=.\";

//conn.ConnectionString = \"Server=506-09;Database=SchoolInfo;User ID=sa;Password=123456\";

//创建命令对象 //查询

SqlCommand cmd = new SqlCommand(\"select ID,Name,ClassID from Student\", conn);

//3、建立适配器对象 ad = new SqlDataAdapter(); ad.SelectCommand = cmd;

//4、建立数据集对象； dsSchool = new DataSet();

//5、填充数据集

ad.Fill(dsSchool, \"Student\");

dsSchool.Tables[\"Student\"].PrimaryKey = new DataColumn[] { dsSchool.Tables[\"Student\"].Columns[\"ID\"] }; }

private void saveData() {

//2.3加入数据

DataRow row = dsSchool.Tables[\"Student\"].NewRow(); row[\"ID\"] = this.textBox2.Text; row[\"Name\"] = this.textBox3.Text; row[\"ClassID\"] = this.textBox4.Text; dsSchool.Tables[\"Student\"].Rows.Add(row); builder = new SqlCommandBuilder(ad); ad.Update(dsSchool,\"Student\");

}

///

private int checkData() {

if (this.textBox2.Text.Trim() == \"\") {

return 1; //数据为空

} else

{ DataRow row =

dsSchool.Tables[\"Student\"].Rows.Find(this.textBox2.Text.Trim()); if (row != null) {

return 2; //主键重复 } }

return 0; }

private void button1_Click(object sender, EventArgs e)//查询 {

DataRow row =

dsSchool.Tables[\"Student\"].Rows.Find(this.textBox1.Text.Trim()); if (row != null) {

this.textBox2.Text = row[\"ID\"].ToString(); this.textBox3.Text = row[\"Name\"].ToString(); this.textBox4.Text = row[\"ClassID\"].ToString(); } else {

MessageBox.Show(\"没有该同学\"); clearData(); } }

private void button2_Click(object sender, EventArgs e)//保存 {

//检查数据

int i = checkData(); if (i == 0) {

saveData();

MessageBox.Show(\"保存成功\"); clearData();

this.textBox2.Focus(); }

else if (i == 1) {

MessageBox.Show(\"数据为空\");

this.textBox2.Focus(); } else {

MessageBox.Show(\"主键重复\"); this.textBox2.Focus(); } }

private void button3_Click(object sender, EventArgs e)//修改 {

DataRow row = dsSchool.Tables[\"Student\"].Rows.Find(this.textBox2.Text); row[\"Name\"] = this.textBox3.Text; builder = new SqlCommandBuilder(ad); ad.Update(dsSchool, \"Student\"); MessageBox.Show(\"修改成功！\"); clearData(); }

private void button4_Click(object sender, EventArgs e)//删除 {

DataRow row =

dsSchool.Tables[\"Student\"].Rows.Find(this.textBox2.Text.Trim()); if (row != null) {

row.Delete();

MessageBox.Show(\"删除成功！\"); builder = new SqlCommandBuilder(ad); ad.Update(dsSchool, \"Student\"); clearData(); } else {

MessageBox.Show(\"没有该同学！\"); } } } }

使用DataSet建立数据库（第四题）

using System;

using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text;

using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Configuration;

namespace Union {

public partial class Form1 : Form {

DataSet dsSchool; DataTable dtStudent; SqlDataAdapter ad; SqlCommandBuilder builder; public Form1() {

InitializeComponent(); createDataSet(); }

private void clearData() {

this.textBox2.Text = \"\"; this.textBox3.Text = \"\"; this.textBox4.Text = \"\"; }

private void createDataSet() {

//1.建立一个数据集

dsSchool = new DataSet(\"mySchool\"); //2.建立一个数据表

dtStudent = new DataTable(\"Student\"); //2.1为数据表增加列（字段）

DataColumn dcID = new DataColumn(\"ID\", typeof(int)); DataColumn dcName = new DataColumn(\"Name\", typeof(string)); DataColumn dcClassID = new DataColumn(\"ClassID\", typeof(string));

dcName.MaxLength = 100; //2.2将列加入到表中

dtStudent.Columns.Add(dcID); dtStudent.Columns.Add(dcName); dtStudent.Columns.Add(dcClassID); //2.3增加表的约束

dtStudent.PrimaryKey = new DataColumn[] { dcID }; //3.将表加入到数据集中

dsSchool.Tables.Add(dtStudent); }

private void saveData() {

//为数据表增加数据

DataRow row1 = dtStudent.NewRow(); row1[\"ID\"] = this.textBox2.Text; row1[\"Name\"] = this.textBox3.Text; row1[\"ClassID\"] = this.textBox4.Text; dtStudent.Rows.Add(row1);

}

///

private int checkData() {

if (this.textBox2.Text.Trim() == \"\") {

return 1; //数据为空 } else

{ DataRow row =

dsSchool.Tables[\"Student\"].Rows.Find(this.textBox2.Text.Trim()); if (row != null) {

return 2; //主键重复 }

}

return 0; }

private void button1_Click(object sender, EventArgs e)//查询 {

DataRow row =

dsSchool.Tables[\"Student\"].Rows.Find(this.textBox1.Text.Trim()); if (row != null) {

this.textBox2.Text = row[\"ID\"].ToString(); this.textBox3.Text = row[\"Name\"].ToString(); this.textBox4.Text = row[\"ClassID\"].ToString(); } else {

MessageBox.Show(\"没有该同学\"); clearData(); } }

private void button2_Click(object sender, EventArgs e)//保存 {

//检查数据

int i = checkData(); if (i == 0) {

saveData();

MessageBox.Show(\"保存成功\"); clearData();

this.textBox2.Focus(); }

else if (i == 1) {

MessageBox.Show(\"数据为空\"); this.textBox2.Focus(); } else {

MessageBox.Show(\"主键重复\"); this.textBox2.Focus(); } }

private void button3_Click(object sender, EventArgs e)//修改 {

DataRow row = dsSchool.Tables[\"Student\"].Rows.Find(this.textBox2.Text); row[\"Name\"] = this.textBox3.Text; builder = new SqlCommandBuilder(ad); ad.Update(dsSchool, \"Student\"); MessageBox.Show(\"修改成功！\"); clearData(); }

private void button4_Click(object sender, EventArgs e)//删除 {

DataRow row =

dsSchool.Tables[\"Student\"].Rows.Find(this.textBox2.Text.Trim()); if (row != null) {

row.Delete();

MessageBox.Show(\"删除成功！\"); builder = new SqlCommandBuilder(ad); ad.Update(dsSchool, \"Student\"); clearData(); } else {

MessageBox.Show(\"没有该同学！\"); } } } }

使用事务机进行存储（第六题）

using System;

using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text;

using System.Threading.Tasks; using System.Windows.Forms;

using System.Data.SqlClient; using System.Data.SqlTypes; using System.Configuration; using System.Data.Common;

namespace Union {

public partial class Form1 : Form {

public Form1() {

InitializeComponent(); }

private void button2_Click(object sender, EventArgs e)//保存 {

//创建连接对象

SqlConnection conn = new SqlConnection(); //利用xml后的连接语句

conn.ConnectionString = \"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDB;Data Source=.\";

//conn.ConnectionString = \"Server=506-16;Database=StuInfo;User ID=sa;Password=123456\"; //打开连接对象 conn.Open(); //声明事务

SqlTransaction tran = null;

try {

//启动事务

tran = conn.BeginTransaction(); //创建命令对象

string str = this.textBox4.Text;

SqlCommand cmd1 = new SqlCommand(\"insert into Student(ID,Name,ClassID) values(@ID,@Name,@ClassID)\", conn);

SqlCommand cmd2 = new SqlCommand(\"update Class set Quantity=Quantity+1 where ClassID='\" + str + \"'\", conn);

//将命令作为事务执行 cmd1.Transaction = tran; cmd2.Transaction = tran;

cmd1.Parameters.Add(\"@ID\", System.Data.SqlDbType.Char); cmd1.Parameters[\"@ID\"].Value = this.textBox2.Text;

cmd1.Parameters.Add(\"@Name\", System.Data.SqlDbType.VarChar); cmd1.Parameters[\"@Name\"].Value = this.textBox3.Text;

cmd1.Parameters.Add(\"@ClassID\", System.Data.SqlDbType.VarChar); cmd1.Parameters[\"@ClassID\"].Value = this.textBox4.Text;

//在命令对象中执行SQL语句 {

tran.Commit();

textBox2.Text = textBox3.Text = textBox4.Text = } }

{

tran.Rollback();

}

{

} {

{

conn.Close(); } } } } }

int i = cmd1.ExecuteNonQuery(); int j = cmd2.ExecuteNonQuery(); if (i > 0 && j > 0) //提交事务 MessageBox.Show(\"保存成功！\"); \"\"; \"\"; \"\"; catch (SqlException ex) //回滚事务 MessageBox.Show(\"SqlException:\" + ex.Message); catch (Exception ex) MessageBox.Show(\"Exception:\" + ex.Message); finally //关闭连接对象

if (conn.State == ConnectionState.Open) 使用通用数据进行更新（第七题） APP.config:

providerName =\"System.Data.SqlClient\"

connectionString =\"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDB;Data Source=.\"/>

代码段：

using System;

using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text;

using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Configuration; using System.Data.Common;

namespace Union {

public partial class Form1 : Form {

public Form1() {

InitializeComponent(); }

private void button1_Click(object sender, EventArgs e)//查询 {

}

private void button2_Click(object sender, EventArgs e)//保存 { }

private void button3_Click(object sender, EventArgs e)//修改 {

//获取数据提供者 string providerName =

ConfigurationManager.ConnectionStrings[\"MyDBSQL\"].ProviderName.ToString(); //建立工厂

DbProviderFactory factory = DbProviderFactories.GetFactory(providerName); //建立连接对象

DbConnection conn = factory.CreateConnection(); conn.ConnectionString =

ConfigurationManager.ConnectionStrings[\"MyDBSQL\"].ConnectionString.ToString(); //打开连接 conn.Open(); //建立命令对象

DbCommand cmd = factory.CreateCommand();

cmd.CommandText = \"insert into student(ID,Name,ClassID) values(@ID,@Name,@ClassID)\"; cmd.Connection = conn;

//加入参数对象

DbParameter parameter = factory.CreateParameter(); parameter.ParameterName = \"@ID\"; parameter.DbType = DbType.Int32; parameter.Value = this.textBox2.Text; cmd.Parameters.Add(parameter);

DbParameter parameter1 = factory.CreateParameter(); parameter1.ParameterName = \"@Name\"; parameter1.DbType = DbType.String; parameter1.Value = this.textBox3.Text; cmd.Parameters.Add(parameter1);

DbParameter parameter2 = factory.CreateParameter(); parameter2.ParameterName = \"@ClassID\"; parameter2.DbType = DbType.String;

parameter2.Value = this.textBox4.Text; cmd.Parameters.Add(parameter2); //执行命令

int rows = cmd.ExecuteNonQuery();

if (rows > 0) {

MessageBox.Show(\"更新成功！\"); }

//关闭连接 conn.Close(); }

private void button4_Click(object sender, EventArgs e)//删除 { }

private void Form1_Load(object sender, EventArgs e) {

} } }