首页 > 代码库 > ADO.NET基本操作(CRUD、Procedure、Transaction)

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 intset @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.teacheridinner 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 intset @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_subjectsgocreate procedure add_subjects@subjectname varchar(20),--输入参数@flag int output --输出参数asbegin    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=http://www.mamicode.com/"历史", 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 }}

 

ADO.NET基本操作(CRUD、Procedure、Transaction)