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;
}
}
}