C#--SqlServer--增删改的操作

以下是学习笔记：

一，增加

1，Command对象：

 

 添加：

 

 

            //编写连接字符串
            string conString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=123";
            //创建连接对象
            SqlConnection conn = new SqlConnection(conString);
            //组合SQL语句
            string sql = "insert into Students (StudentName,Gender,DateOfBirth,StudentIdNo,Age,";
            sql += "PhoneNumber,StudentAddress,ClassId)";
            sql += " values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7})";//非值类型要加上''单引号
            sql = string.Format(sql, "王小路", "男", "1990-09-18", 120226199009181518,
                23, "022-89895566", "天津市静海县", 2);
            //创建Command对象
            //SqlCommand cmd = new SqlCommand();
            //cmd.CommandText = sql;
            //cmd.Connection = conn;
            SqlCommand cmd = new SqlCommand(sql, conn);
            //打开连接
            conn.Open();
            //执行操作
            int result = cmd.ExecuteNonQuery();
            //及时关闭连接
            conn.Close();
            if (result == 1) Console.WriteLine("插入成功！");
            else Console.WriteLine("插入失败！");
            Console.ReadLine();

　

二，修改和删除

 

 

            //编写连接字符串
            string conString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=123";
            //创建连接对象
            SqlConnection conn = new SqlConnection(conString);
            //组合SQL语句
            string sql = "update Students set StudentName='{0}' where StudentId={1}";
            sql = string.Format(sql, "王小路", 100009);
            //创建Command对象          
            SqlCommand cmd = new SqlCommand(sql, conn);
            //打开连接
            conn.Open();
            //执行操作
            int result = cmd.ExecuteNonQuery();//执行操作
            //及时关闭连接
            conn.Close();
            if (result == 1) Console.WriteLine("修改成功！");
            else Console.WriteLine("修改失败！");
            Console.ReadLine();

　　

            //编写连接字符串
            string conString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=123";
            //创建连接对象
            SqlConnection conn = new SqlConnection(conString);
            //组合SQL语句
            string sql = "delete from Students where StudentId=" + 100022;
            //创建Command对象          
            SqlCommand cmd = new SqlCommand(sql, conn);
            //打开连接
            conn.Open();
            //执行操作
            int result = cmd.ExecuteNonQuery();//执行操作
            //及时关闭连接
            conn.Close();
            if (result == 1) Console.WriteLine("删除成功！");
            else Console.WriteLine("删除失败！");
            Console.ReadLine();

　　

提交多条SQL语句

 

 

        //使用ExcuteNonQuery方法同时执行多条SQL语句
        static void Main(string[] args)
        {
            string conString = "Server=.;DataBase=StudentManageDB;Uid=sa;pwd=123";
            SqlConnection conn = new SqlConnection(conString);
            //组合SQL语句
            string sql = "insert into Students (StudentName,Gender,DateOfBirth,StudentIdNo,";
            sql += "Age,PhoneNumber,StudentAddress,ClassId)";
            sql += " values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7})";
            string sql1 = string.Format(sql, "王小路", "男", "1990-09-18", 120226199009181518,
                     23, "022-89895566", "天津市静海县", 2);
            string sql2 = string.Format(sql, "王小刚", "男", "1992-04-15", 120226199204151518,
                   23, "022-89895566", "天津市河西区", 1);
            string sql3 = "update Students set StudentName='{0}' where StudentId={1}";
            sql3 = string.Format(sql3, "王小美", 100009);
            //将不同的SQL语句使用分号连接在一起
            string manySql = sql1 + ";" + sql2 + ";" + sql3;
            SqlCommand cmd = new SqlCommand(manySql, conn);
            conn.Open();
            int result = cmd.ExecuteNonQuery();//执行操作        
            conn.Close();
            if (result == 3) Console.WriteLine("操作成功！");
            else Console.WriteLine("操作失败！");
            Console.ReadLine();
        }

　　获取标识列的值：新员工办卡后要告诉他卡号

 

 

            string connString = "Server=.;DataBase=StudentManageDB;Uid=sa;Pwd=123";
            SqlConnection conn = new SqlConnection(connString);
            //组合SQL语句，要求同时执行insert和select
            string sql = "insert into Students (StudentName,Gender,DateOfBirth,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)";
            sql += " values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7});select @@identity";
            sql = string.Format(sql, "李大国", "男", "1993-05-18", 120226199305181518,
                23, "022-89895566", "天津市红桥区", 3);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object newStuId = cmd.ExecuteScalar(); //使用Scalar方法同时执行insert和select操作     
            conn.Close();
            Console.WriteLine("新增加学员的学号：{0}", newStuId);
            Console.ReadLine();

　　增删改的操作步骤总结