ADO.NET基本操作（CRUD、Procedure、Transaction）
模型沿用上篇博客所提到的学生、教师、课程，以详细的代码进行演示。
增删改查
添加学生、教师、课程
using System.Data.SqlClient; namespace Test { class Program { static void Main(string[] args) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = "server=.;database=school;uid=sa;pwd=123456;"; conn.Open(); string insertStudent = "insert into 学生表(studentname,gender,birthday) values('张三','" + (int)Gender.Male + "','" + DateTime.Parse("1989-6-1") + "')"; SqlCommand cmd = new SqlCommand(insertStudent, conn); cmd.ExecuteNonQuery(); Console.WriteLine("添加学生成功"); string insertTeacher = "insert into 教师表(teachername,gender,birthday) values('教师1','" + (int)Gender.Female + "','" + DateTime.Parse("1974-8-1") + "')"; cmd = new SqlCommand(insertTeacher, conn); cmd.ExecuteNonQuery(); Console.WriteLine("添加教师成功"); string[] insertSubjects = new string[] { "insert into 课程表(subjectname) values('语文')", "insert into 课程表(subjectname) values('数学')", "insert into 课程表(subjectname) values('英语')", }; for (int i = 0; i < insertSubjects.Length; i++) { cmd = new SqlCommand(insertSubjects[i], conn); cmd.ExecuteNonQuery(); } Console.WriteLine("添加课程成功"); } } } public enum Gender { Female, Male } }
修改学生、教师
using System.Data.SqlClient; namespace Test { class Program { static void Main(string[] args) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = "server=.;database=school;uid=sa;pwd=123456;"; conn.Open(); string modifyStudent = "update 学生表 set birthday='" + DateTime.Parse("1989-01-01") + "' where studentname = '张三'"; SqlCommand cmd = new SqlCommand(modifyStudent, conn); cmd.ExecuteNonQuery(); Console.WriteLine("修改学生成功"); string modifyTeacher = "update 教师表 set teachername = '教师' where teachername = '教师1'"; cmd = new SqlCommand(modifyTeacher, conn); cmd.ExecuteNonQuery(); Console.WriteLine("修改教师成功"); } } } }
删除学生、教师
using System.Data.SqlClient; namespace Test { class Program { static void Main(string[] args) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = "server=.;database=school;uid=sa;pwd=123456;"; conn.Open(); string deleteStudent = "delete from 学生表 where studentname = '张三'"; SqlCommand cmd = new SqlCommand(deleteStudent, conn); cmd.ExecuteNonQuery(); Console.WriteLine("删除学生成功"); string deleteTeacher = "delete from 教师表 where teachername = '教师'"; cmd = new SqlCommand(deleteTeacher, conn); cmd.ExecuteNonQuery(); Console.WriteLine("删除教师成功"); } } } }
添加教师授课信息
namespace Test { class Program { static void Main(string[] args) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = "server=.;database=school;uid=sa;pwd=123456;"; conn.Open(); string teachername = "教师1", subjectname = "英语"; string insert = @"declare @teacherid int,@subjectid int set @teacherid = (select teacherid from 教师表 where teachername = '" + teachername + "')" + "set @subjectid = (select subjectid from 课程表 where subjectname = '" + subjectname + "')" + "insert into 教师课程表(teacherid, subjectid)" + "values(@teacherid, @subjectid)"; SqlCommand cmd = new SqlCommand(insert, conn); cmd.ExecuteNonQuery(); Console.WriteLine("添加教师课程成功"); } } } public enum Gender { Female, Male } }
可以看到，代码中应用了嵌入T-SQL的方式，实现了给教师1添加英语教课的信息。
如果想查看教师教哪些课程，可以使用如下代码：
namespace Test { class Program { static void Main(string[] args) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = "server=.;database=school;uid=sa;pwd=123456;"; conn.Open(); string query = @"select b.teachername,c.subjectname from 教师课程表 a inner join 教师表 b on a.teacherid = b.teacherid inner join 课程表 c on a.subjectid = c.subjectid"; SqlCommand cmd = new SqlCommand(query, conn); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); sda.Fill(ds); if (ds != null && ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { Console.WriteLine("{0}授课课程名：{1}",dr["teachername"],dr["subjectname"]); } } } } } }
添加学生选课信息
原理与添加教师授课信息一样，代码不再重复。不过这里稍微讲述一下原理。
ADO.NET执行的就是SQL，这个SQL可以是T-SQL，也可以是普通的SQL语句。添加教师授课信息执行的就是T-SQL。实际上，我们在实际开发当中，一般都会先在SQL Server
Management Studio开发工具中执行一下T-SQL，执行通过后再复制到ADO.NET程序中。
declare @studentid int,@subjectid int set @studentid = (select studentid from 学生表 where studentname = '张三') set @subjectid = (select subjectid from 课程表 where subjectname = '数学') insert into 学生课程表(subjectid, studentid) values(@subjectid,@studentid)
存储过程
存储过程的优点至少有下面两点：
1、 安全性高
相比C#后台代码被反编译，甚至浏览器端完全暴露的JS代码来说，数据库存储过程代码在数据库中，不容易被SQL注入（除非本身存储过程代码写的有问题），安全性高。
2、 执行效率高
首先节约了网络传输的时间（传输的是存储过程名，而不是长长的SQL语句）。其次存储过程代码都是经过数据库预编译的，节约了数据库编译SQL代码的时间，直接执行存储过程。
示例：通过存储过程来添加课程信息
if exists (select * from sysobjects where name = 'add_subjects') drop procedure add_subjects go create procedure add_subjects @subjectname varchar(20),--输入参数 @flag int output --输出参数 as begin insert into 课程表(subjectname) values(@subjectname); select @flag=@@ROWCOUNT --输出参数的值 return @flag--存储过程返回值 end
需要说明的是，这个存储过程add_subjects有两个参数，分别为输入参数subjectname、输出参数flag。最后存储过程还有一个return @flag，也就是存储过程本身有一个返回值。
然后在C#代码中调用这个存储过程：
namespace Test { class Program { static void Main(string[] args) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = "server=.;database=school;uid=sa;pwd=123456;"; conn.Open(); SqlCommand cmd = new SqlCommand("add_subjects", conn); SqlParameter[] parameters = new SqlParameter[] { new SqlParameter() { ParameterName="@subjectname", Value="历史", Size=20, Direction = ParameterDirection.Input }, new SqlParameter() { ParameterName="@flag", Size = 4, Direction = ParameterDirection.Output } }; cmd.Parameters.AddRange(parameters); cmd.CommandType = CommandType.StoredProcedure; int effectRows = cmd.ExecuteNonQuery(); if (effectRows > 0) Console.WriteLine("课程添加成功，输出参数结果：" + parameters[1].Value); } } } }
Parameters[1].Value对应存储过程输出参数@flag的值，effectRows对应存储过程的返回值。
事务
如果在执行数据库SQL操作时，不能保证多条SQL语句要么一起执行，要么不一起执行，就会造成数据不一致的情况。典型的例子就是银行转账，A转账给B，如果A少了100元钱，但这时程序发生了错误，导致B并没有收到100元钱，显然这时候就需要一种机制保证这个转账的操作是一个整体。数据库提出了事务这一概念来解决这一问题。
示例：一次性添加多门课程以及教师的信息
namespace Test { class Program { static void Main(string[] args) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = "server=.;database=school;uid=sa;pwd=123456;"; conn.Open(); using (SqlCommand cmd = new SqlCommand() { Connection = conn }) { using (SqlTransaction trans = conn.BeginTransaction()) { cmd.Transaction = trans; try { string[] strs = new string[] { "insert into 课程表(subjectname) values('C#')", "insert into 课程表(subjectname) values('.NET')", "insert into 课程表(subjectname) values('ADO.NET')", "insert into 教师表(teachername,gender,birthday) values('教师2','"+(int)Gender.Male+"','"+DateTime.Parse("1978-4-12")+"')", }; foreach (string str in strs) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } trans.Commit(); Console.WriteLine("事务执行成功"); } catch (Exception ex) { trans.Rollback(); Console.WriteLine("事务执行失败，错误信息：" + ex.Message); } } } } } } public enum Gender { Female, Male } }
