Sql二刷(2)

作者: 山猪打不过家猪 | 来源:发表于2022-09-21 09:33 被阅读0次

Sql二刷(2)
SQL基础二刷
sql刷题笔记（二）
三，MySQL基础应用
经典SQL50题
sql语句们
小迪16期-20170226
sql刷题
SQL 刷时间
SQL 刷题

4. 通用数据访问类SqlHelper

4.1 封装单一结果查询

namespace SqlDemo
{
    class SqlHelper
    {
        private string connString = "server=127.0.0.1;uid=sa;pwd=shangxi;database=new_test";

        /// <summary>
        /// 返回单一结果查询 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object result = cmd.ExecuteScalar();
            conn.Close();
            return result;
        }
    }
}

改成静态方法，可以直接类名.方法，所以，使用的字段也要改成静态

namespace SqlDemo
{
    class SqlHelper
    {
        private static string connString = "server=127.0.0.1;uid=sa;pwd=shangxi;database=new_test";

        /// <summary>
        /// 返回单一结果查询 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object result = cmd.ExecuteScalar();
            conn.Close();
            return result;
        }

    }
}

调用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string sql = "select Count(1) from oprHotshop";
            object result = SqlHelper.GetSingleResult(sql);
            Console.WriteLine(result);
            Console.ReadLine(); 
    
        }
    }
}

4.2 封装插，改，删ExecuteNonQuery()

        public static int UpdateData(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            return result;

        }

4.3 封装一个结果集

        /// <summary>
        /// 返回单一结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);

        }

调用

namespace SqlDemo 
{
    class Program
    {
        static void Main(string[] args)
        {
            string sql = "select shopId,cateId from oprHotshop";
            SqlDataReader objReader = SqlHelper.GetReader(sql);
            while (objReader.Read())
            {
                Console.WriteLine(objReader["shopId"]);
                Console.WriteLine(objReader["cateId"]);
            }
            objReader.Close(); 
            Console.ReadLine();
        }
    }
}

4.3 DAL层编写

数据sql的语句处理，不用暴露在业务层

StudentService.cs

namespace SqlDemo
{
    class StudentService
    {
        public int AddStudent(string stuName, int stuId, string birthDay, int age)
        {
            string sql = "insert into Students(stuName,stuId,birthDay,age)";
            sql += "values('{0}','{1}','{2}','{3}')";

            sql = string.Format(sql, stuName, stuId, birthDay, age);

            return SqlHelper.UpdateData(sql);

        }
 
    }
}

调用


namespace SqlDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string stuName = "gjj";
            int stuId = 1;
            DateTime birthDay = Convert.ToDateTime("2022-07-1");
            int age = 12;
            StudentService objStu = new StudentService();
            int result = objStu.AddStudent(stuName, stuId, birthDay, age);
            if (result ==1)
            {
                Console.WriteLine("写入成功");
            }
            else
            {
                Console.WriteLine("写入失败");
            }
            Console.ReadLine();
        }
    }
}

4.4 实体层的应用（参数多问题解决）

定义：只包含属性和构造方法的类成为实体类，实体类的属性和数据库实体属性的字段一一对应
作用：使用实体类作为方法参数，稳定接口

image.png

Student.cs

namespace SqlDemo
{
    class Student
    {
        public string stuName { get; set; }
        public int stuId { get; set; }
        public string birthDay { get; set; }
        public int age { get; set; }
    }
}

StudentService.cs

namespace SqlDemo
{
    class StudentService
    {
        public int AddStudent(Student ojbStu)
        {
            string sql = "insert into Students(stuName,stuId,birthDay,age)";
            sql += "values('{0}','{1}','{2}','{3}')";
            sql = string.Format(sql, ojbStu.stuName, ojbStu.stuId, ojbStu.birthDay, ojbStu.age);
            return SqlHelper.UpdateData(sql);
        }
    }
}

5. 数据查询与对象封装

5.1使用简单实体封装单一对象

StuService.cs

        public Student GetStudentById(int stuId)
        {
            string sql = "select * from Students where stuId={0} ";
            sql = string.Format(sql, stuId);
            //执行查询
            SqlDataReader objReader = SqlHelper.GetReader(sql);
            //读取封装对象
            Student objStudent = null;
            //方法一:对象.属性 赋值
            //if (objReader.Read())
            //{
            //    objStudent = new Student();
            //    objStudent.stuName = objReader["stuName"].ToString();
            //    objStudent.stuId = Convert.ToInt32(objReader["stuId"]);
            //    objStudent.birthDay = objReader["birthDay"].ToString();
            //    objStudent.age = Convert.ToInt32(objReader["age"]);
            //}

            //使用对象初始化器
            if (objReader.Read())
            {
                objStudent = new Student()
                {
                    stuName = objReader["stuName"].ToString(),
                    stuId = Convert.ToInt32(objReader["stuId"]),
                    birthDay = objReader["birthDay"].ToString(),
                    age = Convert.ToInt32(objReader["age"]),

                };
            }
            objReader.Close();
            return objStudent;

        }

调用

namespace SqlDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            //调用数据访问方法
            StudentService objStuService = new StudentService();
            Student stu1 = objStuService.GetStudentById(1);
            //解析对象
            Console.WriteLine(stu1.stuName + "\t" + stu1.birthDay);
            Console.ReadLine();
        }
    }
}

5.2 使用List集合封装多个同类型的对象

StudentService.cs

        public List<Student> GetAllStudents()
        {
            string sql = "select * from Student";
            SqlDataReader objReader = SqlHelper.GetReader(sql);
            List<Student> stuList = new List<Student>();
            //方法一：普通方法
            //while (objReader.Read())
            //{
            //    Student ojbStu = new Student()
            //    {
            //        stuName = objReader["stuName"].ToString(),
            //        stuId = Convert.ToInt32(objReader["stuId"]),
            //        birthDay = objReader["birthDay"].ToString(),
            //        age = Convert.ToInt32(objReader["age"]),
            //    };
            //    stuList.Add(ojbStu);
            //}
            //方法二：集合初始化器
            while (objReader.Read())
            {
                stuList.Add(
                   new Student()
                   {
                       stuName = objReader["stuName"].ToString(),
                       stuId = Convert.ToInt32(objReader["stuId"]),
                       birthDay = objReader["birthDay"].ToString(),
                       age = Convert.ToInt32(objReader["age"]),
                   }
                    );
            }
            objReader.Close();
            return stuList;
        }

解析

namespace SqlDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            StudentService sutService = new StudentService();
            List<Student> stuList1 = sutService.GetAllStudents();
            if (stuList1.Count !=0)
            {
                foreach (Student stu in stuList1)
                {
                    Console.WriteLine(stu.stuName+"\t"+stu.stuId + "\t" + stu.birthDay);
                }
            }
            Console.ReadLine();
        }
    }
}

6. DataSet

主要用于大数据查询

image.png

6.1SqlHelper添加查询方法

SqlHelper.cs

        public static DataSet GetDataSet(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);//创建数据适配器对象
            DataSet ds = new DataSet();//创建一个内存数据集
            try
            {
                conn.Open();
                da.Fill(ds);//使用数据适配器填充数据集
                return ds;
            }
            catch (Exception ex)
            {
                //将错误信息写入日志...
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

DAL层StudentClassService.cs

        /// <summary>
        /// 获取所有的班级（存放在数据集里面）
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllClass()
        {
            string sql = "select ClassId,ClassName from StudentClass";
            return SQLHelper.GetDataSet(sql);
        }