SqlHelper

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

namespace SQLClass
{
    /// <summary>
    /// SqlServer操作类
    /// </summary>
    public class SqlHelper
    {
        private string P_addr;

        #region 构造函数
        /// <summary>
        /// 类的构造函数
        /// </summary>
        /// <param name="addr">数据库连接字符串</param>
        public SqlHelper(string addr)
        {
            P_addr = addr;
        }
        #endregion

        #region 执行“增、删、改”的非查询操作
        /// <summary>
        /// 对数据库进行“增、删、改”的非查询操作
        /// </summary>
        /// <param name="sqlStr">需要执行的SQL语句</param>
        /// <param name="sqlPar">传入的Parameters查询参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlStr, params SqlParameter[] sqlPar)
        {
            try
            {
                using (SqlConnection sqlcon = new SqlConnection(P_addr))
                {
                    sqlcon.Open();
                    using (SqlCommand sqlcom = sqlcon.CreateCommand())
                    {
                        sqlcom.CommandText = sqlStr;
                        sqlcom.Parameters.AddRange(sqlPar);
                        return sqlcom.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion

        #region 执行查询,返回结果中的第一行第一列
        /// <summary>
        /// 执行查询,返回结果中的第一行第一列
        /// </summary>
        /// <param name="sqlStr">需要执行的SQL语句</param>
        /// <param name="sqlPar">传入的Parameters查询参数</param>
        /// <returns>返回Object类型数据</returns>
        public object ExecuteScalar(string sqlStr, params SqlParameter[] sqlPar)
        {
            using (SqlConnection sqlcon = new SqlConnection(P_addr))
            {
                sqlcon.Open();
                using (SqlCommand sqlcom = sqlcon.CreateCommand())
                {
                    sqlcom.CommandText = sqlStr;
                    sqlcom.Parameters.AddRange(sqlPar);
                    return sqlcom.ExecuteScalar();
                }
            }
        }
        #endregion

        #region DataTable读取数据库内容

        /// <summary>
        /// 数据池读取存放数据表内容
        /// </summary>
        /// <param name="sqlStr">需要执行的SQL语句</param>
        /// <param name="sqlPar">传入的Parameters查询参数</param>
        /// <returns>数据池存放的内容</returns>
        public DataTable ExecuteDataTable(string sqlStr, params SqlParameter[] sqlPar)
        {
            using (SqlConnection sqlcon = new SqlConnection(P_addr))
            {
                sqlcon.Open();
                using (SqlCommand sqlcom = sqlcon.CreateCommand())
                {
                    sqlcom.CommandText = sqlStr;
                    sqlcom.Parameters.AddRange(sqlPar);
                    SqlDataAdapter sqlda = new SqlDataAdapter(sqlcom);
                    DataSet ds = new DataSet();
                    sqlda.FillSchema(ds, SchemaType.Source);//获得表信息,加不加对于平时没有影响,但是要获取表信息时一定要写
                    sqlda.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }


        #endregion

      



        #region null <--> DBNull转换
        /// <summary>
        /// 将值传入数据库时,传入值如果是null就转化为DBNull
        /// </summary>
        /// <param name="value">传入值</param>
        /// <returns></returns>
        public object ToDbValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }

        /// <summary>
        /// 将数据库中的值取出,如果取出值为DBNull就转化为null
        /// </summary>
        /// <param name="value">取出值</param>
        /// <returns></returns>
        public object FromDbValue(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return value;
            }
        }
        #endregion

    }
}

DLL调用SQLHelper

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using OnlineExamination.Model;

namespace  OnlineExamination.DAL
{
    public partial class ClassesDAL
    {
        SQLClass.SqlHelper sh = new SQLClass.SqlHelper(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);

        /// <summary>
        /// 构造方法
        /// </summary>
        public ClassesDAL()
        { }

        /// <summary>
        /// 添加记录
        /// </summary>
        public void InsertRecord(Classes md)
        {
            sh.ExecuteNonQuery(@"INSERT INTO Classes (ClassName, MajorId, CreateTime )
VALUES   (@ClassName, @MajorId, @CreateTime )",
            new SqlParameter("@ClassName", sh.ToDbValue( md.ClassName))
,             new SqlParameter("@MajorId", sh.ToDbValue( md.MajorId))
,             new SqlParameter("@CreateTime", sh.ToDbValue( md.CreateTime))

            );
        }

        /// <summary>
        /// 添加返回记录
        /// </summary>
        public object InsertBackRecord(Classes md)
        {
            object obj = sh.ExecuteScalar(@"INSERT INTO Classes (ClassName, MajorId, CreateTime ) output inserted.Id
VALUES   (@ClassName, @MajorId, @CreateTime )",
            new SqlParameter("@ClassName", sh.ToDbValue( md.ClassName))
,             new SqlParameter("@MajorId", sh.ToDbValue( md.MajorId))
,             new SqlParameter("@CreateTime", sh.ToDbValue( md.CreateTime))

            );
            return obj;
        }

        /// <summary>
        /// 删除所有记录
        /// </summary>
        public void DeleteRecord(){
            sh.ExecuteNonQuery(@"DELETE FROM Classes");
        }

        /// <summary>
        /// 根据Id删除记录
        /// </summary>
        public void DeleteRecordById(int Id){
            sh.ExecuteNonQuery(@"DELETE FROM Classes WHERE Id =@Id",
            new SqlParameter("@Id",Id));        }

        /// <summary>
        /// 根据Guid删除记录
        /// </summary>
        public void DeleteRecordByGuid(Guid Id){
            sh.ExecuteNonQuery(@"DELETE FROM Classes WHERE Id =@Id",
            new SqlParameter("@Id",Id));        }

        /// <summary>
        /// 修改记录
        /// </summary>
        public void UpdateRecord(Classes md)
        {
            sh.ExecuteNonQuery(@"UPDATE Classes SET ClassName=@ClassName, MajorId=@MajorId, CreateTime=@CreateTime WHERE Id =@Id",
            new SqlParameter("@Id", sh.ToDbValue( md.Id))
,             new SqlParameter("@ClassName", sh.ToDbValue( md.ClassName))
,             new SqlParameter("@MajorId", sh.ToDbValue( md.MajorId))
,             new SqlParameter("@CreateTime", sh.ToDbValue( md.CreateTime))

            );
        }

        /// <summary>
        /// 根据Id查询记录
        /// </summary>
        public DataTable GetById(int Id)
        {
            DataTable dt = sh.ExecuteDataTable(@"SELECT Id, ClassName, MajorId, CreateTime FROM Classes WHERE Id =@Id",
            new SqlParameter("@Id",Id));
            return dt;
        }

        /// <summary>
        /// 根据Guid查询记录
        /// </summary>
        public DataTable GetByGuid(Guid Id)
        {
            DataTable dt = sh.ExecuteDataTable(@"SELECT Id, ClassName, MajorId, CreateTime FROM Classes WHERE Id =@Id",
            new SqlParameter("@Id",Id));
            return dt;
        }

        /// <summary>
        /// 按照自定义要求查询记录
        /// </summary>
        public DataTable GetByCustom(string SearchType, string SearchContent )
        {
            DataTable dt = sh.ExecuteDataTable(@"SELECT Id, ClassName, MajorId, CreateTime FROM Classes WHERE " + SearchType + " =@SearchContent",
            new SqlParameter("@SearchContent",SearchContent));
            return dt;
        }

        /// <summary>
        /// 查询所有记录
        /// </summary>
        public DataTable GetAll()
        {
            return sh.ExecuteDataTable(@"SELECT Id, ClassName, MajorId, CreateTime FROM Classes");
        }

        /// <summary>
        /// 查询数据条数
        /// </summary>
        public Object GetDataCount()
        {
            return sh.ExecuteScalar(@"SELECT COUNT(Id)  FROM Classes");
        }

        /// <summary>
        /// 查询所有记录并分页
        /// </summary>
        public DataTable GetAllOfPage(int? StartPage, int? EndPage)
        {
            return sh.ExecuteDataTable(@"SELECT Id, ClassName, MajorId, CreateTime FROM ( SELECT ClassName, MajorId, CreateTime , ROW_NUMBER() OVER(ORDER BY Id) AS num  FROM Classes ) AS PageTable WHERE PageTable.num BETWEEN @StartPage AND @EndPage",
            new SqlParameter("@StartPage",StartPage),
            new SqlParameter("@EndPage",EndPage));
        }

        /// <summary>
        /// 查询所有不重复记录
        /// </summary>
        public DataTable GetAllOfDistinct(string tbColumn)
        {
            return sh.ExecuteDataTable(@"SELECT DISTINCT "+ tbColumn +" FROM Classes");
        }

        /// <summary>
        /// 查询所有记录给数组
        /// </summary>
        public Classes[] GetAllToArray()        {
            DataTable dt = sh.ExecuteDataTable(@"SELECT Id, ClassName, MajorId, CreateTime FROM Classes");
            Classes[] arrayMod = new Classes[dt.Rows.Count];
                for(int i = 0 ; i < dt.Rows.Count ; i++ )
                {
                DataRow dr = dt.Rows[i];
                Classes mod = new Classes();
                mod.Id = (System.Int32)dr["Id"];
                mod.ClassName = (System.String)dr["ClassName"];
                mod.MajorId = (System.Int32?)dr["MajorId"];
                mod.CreateTime = (System.DateTime?)dr["CreateTime"];
                arrayMod[i] = mod;
            }
            return arrayMod;
        }

        /// <summary>
        /// 查询所有记录到序列
        /// </summary>
        public IEnumerable<Classes> ListAll()
        {
            List<Classes> list = new List<Classes>();
            DataTable dt = sh.ExecuteDataTable("SELECT Id, ClassName, MajorId, CreateTime FROM Classes");
            foreach (DataRow dr in dt.Rows)
            {
                Classes md = ToModel(dr);
                list.Add(md);
            }
            return list;
        }

        /// <summary>
        /// 查询所有记录到序列并分页
        /// </summary>
        public IEnumerable<Classes> ListAllOfPage(int? StartPage, int? EndPage)
        {
            List<Classes> list = new List<Classes>();
            DataTable dt = sh.ExecuteDataTable("SELECT Id, ClassName, MajorId, CreateTime FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY Id) AS num FROM Classes ) AS PageTable WHERE PageTable.num BETWEEN @StartPage AND @EndPage",
            new SqlParameter("@StartPage",StartPage),
            new SqlParameter("@EndPage",EndPage));
            foreach (DataRow dr in dt.Rows)
            {
                Classes md = ToModel(dr);
                list.Add(md);
            }
            return list;
        }

        /// <summary>
        /// 根据Id查询记录到序列
        /// </summary>
        public IEnumerable<Classes> ListById(int Id)
        {
            List<Classes> list = new List<Classes>();
            DataTable dt = sh.ExecuteDataTable("SELECT Id, ClassName, MajorId, CreateTime FROM Classes WHERE Id=@Id",
            new SqlParameter("@Id", Id));
            foreach (DataRow dr in dt.Rows)
            {
                Classes md = ToModel(dr);
                list.Add(md);
            }
            return list;
        }

        /// <summary>
        /// 根据Guid查询记录到序列
        /// </summary>
        public IEnumerable<Classes> ListByGuid(Guid Id)
        {
            List<Classes> list = new List<Classes>();
            DataTable dt = sh.ExecuteDataTable("SELECT Id, ClassName, MajorId, CreateTime FROM Classes WHERE Id=@Id",
            new SqlParameter("@Id", Id));
            foreach (DataRow dr in dt.Rows)
            {
                Classes md = ToModel(dr);
                list.Add(md);
            }
            return list;
        }

        /// <summary>
        /// 按照自定义要求查询记录到序列
        /// </summary>
        public IEnumerable<Classes> ListByCustom(string SearchType, string SearchContent)
        {
            List<Classes> list = new List<Classes>();
            DataTable dt = sh.ExecuteDataTable("SELECT Id, ClassName, MajorId, CreateTime FROM Classes WHERE " + SearchType + " = @SearchContent",
            new SqlParameter("@SearchContent", SearchContent));
            foreach (DataRow dr in dt.Rows)
            {
                Classes md = ToModel(dr);
                list.Add(md);
            }
            return list;
        }

        /// <summary>
        /// 按照自定义要求使用WHERE IN查询记录到序列
        /// </summary>
        public IEnumerable<Classes> ListByCustomUseWhereIn(string SearchType, string[] SearchContents)
        {
            List<Classes> list = new List<Classes>();
             StringBuilder sb = new StringBuilder();
             SqlParameter[] sqlParameters = new SqlParameter[SearchContents.Length];
             sb.Append("SELECT Id, ClassName, MajorId, CreateTime FROM Classes WHERE " ).Append(SearchType).Append(" IN(");
            for (int i = 0; i < SearchContents.Length; i++){
                 sb.Append("@SearchContents").Append(i);
                 if (i < SearchContents.Length - 1) sb.Append(", ");
                 sqlParameters[i] = new SqlParameter("@SearchContents" + i, SearchContents[i]);
             }
             sb.Append(")");
            DataTable dt = sh.ExecuteDataTable(sb.ToString(), sqlParameters);
            
            foreach (DataRow dr in dt.Rows)
            {
                Classes md = ToModel(dr);
                list.Add(md);
            }
            return list;
        }

        /// <summary>
        /// 根据Id查询并给所有属性赋值
        /// </summary>
        public Classes GetByIdToAttribute(int Id)
        {
            DataTable dt = sh.ExecuteDataTable(@"SELECT Id, ClassName, MajorId, CreateTime  FROM Classes WHERE Id =@Id",
            new SqlParameter("@Id",Id));
            return ToModel(dt.Rows[0]);
        }

        /// <summary>
        /// 根据Guid查询并给所有属性赋值
        /// </summary>
        public Classes GetByGuidToAttribute(Guid Id)
        {
            DataTable dt = sh.ExecuteDataTable(@"SELECT Id, ClassName, MajorId, CreateTime  FROM Classes WHERE Id =@Id",
            new SqlParameter("@Id",Id));
            return ToModel(dt.Rows[0]);
        }

        /// <summary>
        /// ToModel
        /// </summary>
        private Classes ToModel(DataRow dr)
        {
            Classes md = new Classes();
            md.Id=(System.Int32)sh.FromDbValue(dr["Id"]);
            md.ClassName=(System.String)sh.FromDbValue(dr["ClassName"]);
            md.MajorId=(System.Int32?)sh.FromDbValue(dr["MajorId"]);
            md.CreateTime=(System.DateTime?)sh.FromDbValue(dr["CreateTime"]);
            return md;
        }

    }
}

BLL调用DLL

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using OnlineExamination.DAL;
using OnlineExamination.Model;

namespace  OnlineExamination.BLL
{
    [System.ComponentModel.DataObject]
    public partial  class ClassesBLL
    {
        ClassesDAL mdd = new ClassesDAL();
        /// <summary>
        /// 构造方法
        /// </summary>
        public ClassesBLL()
        { }

        /// <summary>
        /// 添加记录
        /// </summary>
        public void InsertRecord(Classes md)
        {
            mdd.InsertRecord(md);
        }

        /// <summary>
        /// 添加返回记录
        /// </summary>
        public object InsertBackRecord(Classes md)
        {
            return mdd.InsertBackRecord(md);
        }

        /// <summary>
        /// 删除所有记录
        /// </summary>
        public void DeleteRecord(){
            mdd.DeleteRecord();
        }

        /// <summary>
        /// 根据Id删除记录
        /// </summary>
        public void DeleteRecordById(int Id)
        {
            mdd.DeleteRecordById(Id);
        }

        /// <summary>
        /// 根据Guid删除记录
        /// </summary>
        public void DeleteRecordByGuid(Guid Id)
        {
            mdd.DeleteRecordByGuid(Id);
        }

        /// <summary>
        /// 修改记录
        /// </summary>
        public void UpdateRecord(Classes md)
        {
            mdd.UpdateRecord(md);
        }

        /// <summary>
        /// 根据Id查询记录
        /// </summary>
        public DataTable GetById(int Id)
        {
            return mdd.GetById(Id);
        }

        /// <summary>
        /// 根据Guid查询记录
        /// </summary>
        public DataTable GetByGuid(Guid Id)
        {
            return mdd.GetByGuid(Id);
        }

        /// <summary>
        /// 按照自定义要求查询记录
        /// </summary>
        public DataTable GetByCustom(string SearchType, string SearchContent )
        {
            return mdd.GetByCustom(SearchType, SearchContent);
        }

        /// <summary>
        /// 查询所有记录
        /// </summary>
        public DataTable GetAll()
        {
            return mdd.GetAll();
        }

        /// <summary>
        /// 查询数据条数
        /// </summary>
        public Object GetDataCount()
        {
            return mdd.GetDataCount();
        }

        /// <summary>
        /// 查询所有记录并分页
        /// </summary>
        public DataTable GetAllOfPage(int? pageIndex, int? pageSize)
        {
            int? StartPage = (pageIndex * pageSize) - (pageSize - 1);
            int? EndPage = (pageIndex * pageSize);
            return mdd.GetAllOfPage(StartPage, EndPage);
        }

        /// <summary>
        /// 查询所有不重复记录
        /// </summary>
        public DataTable GetAllOfDistinct(string tbColumn)
        {
            return mdd.GetAllOfDistinct(tbColumn);
        }

        /// <summary>
        /// 查询所有记录给数组
        /// </summary>
        public Classes[] GetAllToArray()
        {
            return mdd.GetAllToArray();
        }

        /// <summary>
        /// 查询所有记录到序列
        /// </summary>
        public IEnumerable<Classes> ListAll()
        {
            return mdd.ListAll();
        }
        /// <summary>
        /// 查询所有记录到序列并分页
        /// </summary>
        /// <param name="pageIndex">第几页</param>
        /// <param name="pageSize">每页多少数据</param>
        /// <returns></returns>
        public IEnumerable<Classes> ListAllOfPage(int? pageIndex, int? pageSize)
        {
            int? StartPage = (pageIndex * pageSize) - (pageSize - 1);
            int? EndPage = (pageIndex * pageSize);
            return mdd.ListAllOfPage(StartPage, EndPage);
        }
        /// <summary>
        /// 根据Id查询记录到序列
        /// </summary>
        public IEnumerable<Classes> ListById(int Id)
        {
            return mdd.ListById(Id);
        }
        /// <summary>
        /// 根据Guid查询记录到序列
        /// </summary>
        public IEnumerable<Classes> ListByGuid(Guid Id)
        {
            return mdd.ListByGuid(Id);
        }
        /// <summary>
        /// 按照自定义要求查询记录到序列
        /// </summary>
        public IEnumerable<Classes> ListByCustom(string SearchType, string SearchContent)
        {
            return mdd.ListByCustom(SearchType,SearchContent);
        }
        /// <summary>
        /// 按照自定义要求使用WHERE IN查询记录到序列
        /// </summary>
        public IEnumerable<Classes> ListByCustomUseWhereIn(string SearchType, string[] SearchContent)
        {
            return mdd.ListByCustomUseWhereIn(SearchType,SearchContent);
        }
        /// <summary>
        /// 根据Id查询并给所有属性赋值
        /// </summary>
        public Classes GetByIdToAttribute(int Id)
        {
            Classes mod = new ClassesDAL().GetByIdToAttribute(Id);
            return mod;
        }

        /// <summary>
        /// 根据Guid查询并给所有属性赋值
        /// </summary>
        public Classes GetByGuidToAttribute(Guid Id)
        {
            Classes mod = new ClassesDAL().GetByGuidToAttribute(Id);
            return mod;
        }


    }
}

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值