6. SQL数据库增删改查方法封装

SQL数据库增删改查方法封装

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

namespace Sql1
{
    /// <summary>
    /// SQL助手
    /// 
    /// 封装：抽取变化的 封装不变的
    /// </summary>
    public class SQLHelper
    {
        //连接字符串--连接数据库用
        private static readonly string connString = @"Data Source=DESKTOP-9JEHCP6;Initial Catalog=StudentManage;User ID=**;Password=**;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
//增、删、改(insert、delete、update)
        #region 增、删、改(insert、delete、update)


        /// <summary>
        /// 增、删、改(insert、delete、update)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static int GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                //这部分是写需要检测的语句
                conn.Open();
                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (Exception ex)
            {
                //处理异常
                //会直接显示或者可以写入系统日志
                Console.WriteLine("执行GetSingleResult(string sql)方法出错" + ex.Message);
                throw ex;
            }
            finally
            {
                //最后需要处理的问题
                conn.Close();
            }
        }
        #endregion


//读取单个对象
        #region 读取单个对象
        /// <summary>
        /// 读取单一结果
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static object GetSingleObject(string sql)
        {
            //连接ADO.net做数据查询
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql,conn);
            try
            {
                conn.Open();
                //using System.Data;
                //CommandBehavior.CloseConnection 检测并自动关闭数据库连接
                //object reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                object obj = cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception ex)
            {
                //处理异常
                //会直接显示或者可以写入系统日志
                Console.WriteLine("执行GetSingleObject(string sql)方法出错" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        #endregion

//读取多个对象
        #region 读取多个对象
        /// <summary>
        /// 读取多个对象
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static SqlDataReader GetDataReader(String sql)
        {
            //连接ADO.NET做数据查询
            SqlConnection conn = new SqlConnection(connString);//连接数据库(连接数据库实例)
            SqlCommand cmd = new SqlCommand(sql, conn); // 发送sql命令
            try
            {
                conn.Open(); //打开数据库连接
                //CommandBehavior.CloseConnection 检测并自动关闭数据库连接
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (Exception ex)
            {
                //处理异常
                //会直接显示或者可以写入系统日志
                Console.WriteLine("执行GetDataReader(string sql)方法出错" + ex.Message);
                throw ex;
            }

        }
        #endregion
    }
}

SQL数据库增删改查方法封装后的调用

        static void Main(string[] Args)
        {
//添加对象
            #region 添加对象
            /*
            //1. 准备好SQL语句
            string sql = "insert into StudentClass (ClassName) values(N'C#web班')";

            //2. 调用我们的SQLHelper类
            int result = SQLHelper.GetSingleResult(sql);

            //返回结果
            if (result >= 1)
            {
                Console.WriteLine("添加成功");
            }
            else
            {
                Console.WriteLine("添加失败");
            }
            Console.ReadKey();


            */
            #endregion
//修改对象  
            #region 修改对象
            /*
            string sql = "update StudentClass set ClassName=N'c#进阶版' where ClassId=10005";
            int result = SQLHelper.GetSingleResult(sql);
            //返回结果
            if (result >= 1)
            {
                Console.WriteLine("修改成功");
            }
            else
            {
                Console.WriteLine("修改失败");
            }
            Console.ReadKey();
            */
            #endregion
//删除对象
            #region 删除对象
            /*
            string sql = "Delete from StudentClass where ClassId=10005";
            int result = SQLHelper.GetSingleResult(sql);
            //返回结果
            if (result >= 1)
            {
                Console.WriteLine("删除成功");
            }
            else
            {
                Console.WriteLine("删除失败");
            }
            Console.ReadKey();
            */
            #endregion
//读取单一结果
            #region 读取单一结果
            /*
            //1.准备好SQL语句
            string sql = "select ClassId, ClassName from StudentClass where ClassId=10004";

            //2. 调用我们的SQLHelper
            object stuObject = SQLHelper.GetSingleObject(sql);
            //3. 解析对象
            int classId = (int)stuObject;
            //4. 展示数据
            Console.WriteLine("班级ID: " + classId);
            Console.Read();
            */
            #endregion
//读取多个对象
            #region 读取多个对象
            //1.准备好SQL语句
            string sql = "select ClassId, ClassName from StudentClass where ClassId>=10004";

            //2. 调用我们的SQLHelper
            SqlDataReader reader = SQLHelper.GetDataReader(sql);
            //3. 解析对象
            List<StudentClass> stuList = new List<StudentClass>();
            while (reader.Read())
            {
                stuList.Add(new StudentClass()
                {
                    ClassId = Convert.ToInt32(reader["ClassId"]),
                    ClassName = reader["ClassName"].ToString()
                });

            }
            //4. 展示数据
            foreach (StudentClass item in stuList)
            {
                Console.WriteLine("班级ID: " + item.ClassId + "\t班级Name: " + item.ClassName);
            }
            Console.Read();
            #endregion
        }