SQL Server ADO.NET SQLHelper类封装

首先创建一个新的类叫做SQLHelper.cs,将前面增删改查进行封装。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace ConsoleApp
{
    /// <summary>
    /// SQL助手
    /// 封装：抽取变化的 封装不变的
    /// </summary>
    class SQLHelper
    {
        private static readonly string connString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=StudentManger;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        #region 增、删、改

        /// <summary>
        /// 增 删 改（insert/delete/update）
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static int GetSingleResult(string sql)
        {
            //链接ADO.NETZUO数据查询

            SqlConnection conn = new SqlConnection(connString);//链接数据库(链接数据库实例)
            SqlCommand cmd = new SqlCommand(sql, conn);//发送sql命令

            try
            {
                //这部分是写需要检测的语句

                conn.Open();//打开链接
                int result = cmd.ExecuteNonQuery();
                return result;

            }
            catch (Exception ex)
            {
                //处理异常
                //直接显示或者可以写入系统日志
                Console.WriteLine("执行GetSingleResult(string sql)方法出错");
                throw ex;

            }
            finally
            {
                //最后需要处理的问题
                conn.Close();
            }



        }
        #endregion

        #region  读取单一结果
        /// <summary>
        /// 读取单一结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleObject(string sql)
        {
            //链接ADO.NETZUO数据查询

            SqlConnection conn = new SqlConnection(connString);//链接数据库(链接数据库实例)
            SqlCommand cmd = new SqlCommand(sql, conn);//发送sql命令
            try
            {
                conn.Open();//打开链接
                            //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.NETZUO数据查询

            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


    }
}