示例【1】
1、创建SQLHelper类
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Reflection;
namespace Db
{
public class SQLHelper<T> where T : class, new()
{
/// <summary>
/// 链接数据库字符串
/// </summary>
public static string strConnectionString = ConfigurationManager.ConnectionStrings["DataConnectionString"].ToString();
/// <summary>
/// 数据库连接
/// </summary>
public SqlConnection Connection = null;
public SQLHelper()
{
OpenConnect();
}
/// <summary>
/// 打开数据库连接
/// </summary>
public void OpenConnect()
{
if (Connection == null || Connection.State != System.Data.ConnectionState.Open)
{
Connection = new SqlConnection(strConnectionString);
Connection.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void CloseConnect()
{
if (Connection != null && Connection.State != System.Data.ConnectionState.Closed)
{
Connection.Close();
}
}
#region 对数据库进行读写操作
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="obQuery">SQL参数的值</param>
/// <returns></returns>
public SqlDataReader ExecReader(string strSQL, object obQuery)
{
SqlCommand command = new SqlCommand(strSQL, Connection);
if (obQuery != null)
{
PropertyInfo[] pis = obQuery.GetType().GetProperties();
foreach (var p in pis)
{
command.Parameters.Add(new SqlParameter(p.Name, p.GetValue(obQuery, null)));
}
}
SqlDataReader reader = command.ExecuteReader();
return reader;
}
/// <summary>
/// 执行返回单值的查询语句
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="obQuery">SQL参数的值</param>
/// <returns></returns>
public object ExecSingleValue(string strSQL, object obQuery)
{
SqlCommand command = new SqlCommand(strSQL, Connection);
if (obQuery != null)
{
PropertyInfo[] pis = obQuery.GetType().GetProperties();
foreach (var p in pis)
{
command.Parameters.Add(new SqlParameter(p.Name, p.GetValue(obQuery, null)));
}
}
return command.ExecuteScalar();
}
/// <summary>
/// 执行非查询语句
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="obQuery">SQL参数的值</param>
/// <returns></returns>
public int ExecNoQuery(string strSQL, object obQuery)
{
SqlCommand command = new SqlCommand(strSQL, Connection);
if (obQuery != null)
{
PropertyInfo[] pis = obQuery.GetType().GetProperties();
foreach (var p in pis)
{
command.Parameters.Add(new SqlParameter(p.Name, p.GetValue(obQuery, null)));
}
}
return command.ExecuteNonQuery();
}
#endregion
#region 返回列表List<T>，利用"泛型+反射"
/// <summary>
/// 获取列表
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="obQuery">SQL参数的值</param>
/// <returns></returns>
public List<T> GetList(string strSQL, object obQuery)
{
//调用执行查询语句函数，返回SqlDataReader
SqlDataReader reader = ExecReader(strSQL, obQuery);
//定义返回的列表
List<T> list = new List<T>();
//定义T类型的实体
T model = new T();
//获取T类型实体的属性类型和值
PropertyInfo[] pis = model.GetType().GetProperties();
//获取数据库返回的列数
int intColCount = reader.FieldCount;
//遍历SqlDataReader
while (reader.Read())
{
//定义
int value_number = 0;
//重新实例化T
model = new T();
//从数据库拿出一条数据后，循环遍历T类型的属性类型和值
for (int i = 0; i < intColCount; i++)
{
//判断第一列是否为row_number，此为分页使用
if (reader.GetName(i) == "row_number") value_number++;
//设置T对应属性的值
pis[i].SetValue(model, reader.GetValue(value_number), null);
value_number++;
}
//将T添加到列表中
list.Add(model);
}
return list;
}
#endregion
/// <summary>
/// 获取分页
/// </summary>
/// <param name="strTotalSQL">总共个数的SQL</param>
/// <param name="obTotalQuery">总共个数的SQL参数的值</param>
/// <param name="strSQL">分页的SQL</param>
/// <param name="obQuery">分页SQL参数的值</param>
/// <param name="intPageIndex">分页编号</param>
/// <param name="intPageSize">分页大小</param>
/// <returns>返回PagesList<T></returns>
public PagedList<T> GetPageList(string strTotalSQL, object obTotalQuery, string strSQL, object obQuery, int intPageIndex, int intPageSize)
{
//定义分页对象的编号和大小
PagedList<T> pageList = new PagedList<T>(intPageIndex, intPageSize);
//执行获取单个值的函数，设置分页对象的总元素
pageList.intTotalCount = (int)ExecSingleValue(strTotalSQL, obTotalQuery);
//设置分页对象的分页数
if (pageList.intTotalCount % intPageSize == 0) pageList.intPages = pageList.intTotalCount / intPageSize;
else pageList.intPages = pageList.intTotalCount / intPageSize + 1;
//定义列表，调用获取列表的函数获取此分页的元素
List<T> list = GetList(strSQL, obQuery);
//将列表元素添加到分页对象当中
pageList.AddRange(list);
//设置分页对象是否有上一页和下一页
pageList.HasNextPage = pageList.intPageIndex < pageList.intPages ? true : false;
pageList.HasPrPage = pageList.intPageIndex > 1 ? true : false;
return pageList;
}
/// <summary>
/// 获取单个实体
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="obQuery">SQL参数的值</param>
/// <returns></returns>
public T GetTM(string strSQL, object obQuery)
{
//调用执行查询语句，返回SqlDataReader
SqlDataReader reader = ExecReader(strSQL, obQuery);
//新建一个T类型
T model = new T();
//获取T类型的属性类型和值
PropertyInfo[] pis = model.GetType().GetProperties();
//获取数据库返回数据的列数
int intColCount = reader.FieldCount;
//读取数据，填充T
if (reader.Read())
{
int value_number = 0;
for (int i = 0; i < intColCount; i++)
{
pis[i].SetValue(model, reader.GetValue(value_number), null);
value_number++;
}
}
return model;
}
}
/// <summary>
/// 分页实体类
/// </summary>
/// <typeparam name="T"></typeparam>
public class PagedList<T> : List<T>
{
/// <summary>
/// 分页编号
/// </summary>
public int intPageIndex { get; set; }
/// <summary>
/// 分页大小
/// </summary>
public int intPageSize { get; set; }
/// <summary>
/// 分页数
/// </summary>
public int intPages { get; set; }
/// <summary>
/// 总元素的个数
/// </summary>
public int intTotalCount { get; set; }
/// <summary>
/// 此分页元素的个数
/// </summary>
public int intCount { get; set; }
/// <summary>
/// 是否有下一页
/// </summary>
public bool HasNextPage { get; set; }
/// <summary>
/// 是否有上一页
/// </summary>
public bool HasPrPage { get; set; }
public PagedList(int intPageIndex, int intPageSize)
{
this.intPageIndex = intPageIndex;
this.intPageSize = intPageSize;
}
}
}
2、调用方法
// 调用方法：ResumeTM 为数据库表的实体类
public List<ResumeTM> GetResumeByJobInfoId(int intJobInfoId)
{
SQLHelper<ResumeTM> helper = new SQLHelper<ResumeTM>();
string strSQL = @"select * from Resume where FirstJobId=@JobInfoId or SecondJobId=@JobInfoId order by CreateTime desc";
object obQuery = new { JobInfoId = intJobInfoId };
List<ResumeTM> list = helper.GetList(strSQL, obQuery);
helper.CloseConnect();
return list;
}
public ResumeTM GetResumeById(int intId)
{
SQLHelper<ResumeTM> helper = new SQLHelper<ResumeTM>();
string strSQL = @"select * from Resume where Id=@Id";
object obQuery = new { Id = intId };
ResumeTM tm = helper.GetTM(strSQL, obQuery);
helper.CloseConnect();
return tm;
}
public PagedList<ResumeTM> GetResume(int intPageIndex, int intPaegSize)
{
SQLHelper<ResumeTM> helper = new SQLHelper<ResumeTM>();
string strTotalCount = @"select count(*) from Resume";
string strSQL = @"select * from
(select row_number() over(order by CreateTime desc) as row_number,* from Resume) as t0
where t0.row_number between @intPageSize*(@intPageIndex-1)+1 and @ingPageSize*@intPageIndex";
object obQuery = new { intPageSize = intPaegSize, intPageIndex = intPageIndex };
PagedList<ResumeTM> list = helper.GetPageList(strTotalCount, null, strSQL, obQuery, intPageIndex, intPaegSize);
helper.CloseConnect();
return list;
}
public void Delete(int intId)
{
SQLHelper<ResumeTM> helper = new SQLHelper<ResumeTM>();
string strSQL = @"delete from Resume where Id=@Id";
object obQuery = new { Id = intId };
helper.ExecNoQuery(strSQL, obQuery);
helper.CloseConnect();
}
示例【2】
1、定义实体类
public class BaseModel
{
public int Id { set; get; }
}
public class Company : BaseModel
{
public string Name { get; set; }
public System.DateTime CreateTime { get; set; }
public int CreatorId { get; set; }
public int? LastModifierId { get; set; }
public DateTime? LastModifyTime { get; set; }
}
public class User : BaseModel
{
public string Name { get; set; }
public string Account { get; set; }
public string Password { get; set; }
public string Email { get; set; }
public string Mobile { get; set; }
public int? CompanyId { get; set; }
public string CompanyName { get; set; }
public int State { get; set; }
public int UserType { get; set; }
public DateTime? LastLoginTime { get; set; }
public DateTime CreateTime { get; set; }
public int CreatorId { get; set; }
public int? LastModifierId { get; set; }
}
2、编写通用接口
public interface IBaseDAL
{
T FindT<T>(int id) where T : BaseModel;
List<T> FindAll<T>() where T : BaseModel;
bool Add<T>(T t) where T : BaseModel;
bool Update<T>(T t) where T : BaseModel;
bool Delete<T>(T t) where T : BaseModel;
}
3、使用"泛型+反射"实现接口
public class BaseDAL : IBaseDAL
{
public T FindT<T>(int id) where T : BaseModel
{
Type type = typeof(T);
string sql = $"SELECT {string.Join(",", type.GetProperties().Select(p => $"[{p.Name}]"))} FROM [{type.Name}] WHERE ID={id}";
using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
{
SqlCommand command = new SqlCommand(sql, conn);
conn.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
return this.Trans<T>(type, reader);
}
else
{
return null;
}
}
}
private T Trans<T>(Type type, SqlDataReader reader)
{
object oObject = Activator.CreateInstance(type);
foreach (var prop in type.GetProperties())
{
prop.SetValue(oObject, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
}
}
public List<T> FindAll<T>() where T : BaseModel
{
Type type = typeof(T);
string sql = $"SELECT {string.Join(",", type.GetProperties().Select(p => $"[{p.Name}]"))} FROM [{type.Name}]";
using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
{
SqlCommand command = new SqlCommand(sql, conn);
conn.Open();
var reader = command.ExecuteReader();
List<T> tList = new List<T>();
while (reader.Read())
{
tList.Add(this.Trans<T>(type, reader));
}
return tList;
}
}
public bool Add<T>(T t) where T : BaseModel
{
//id是自增的 所以不能新增
Type type = t.GetType();
string columnString = string.Join(",", type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)
.Select(p => $"[{p.Name}]"));
string valueColumn = string.Join(",", type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)
.Select(p => $"@{p.Name}"));
var parameterList = type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)
.Select(p => new SqlParameter($"@{p.Name}", p.GetValue(t) ?? DBNull.Value));//注意可空类型
string sql = $"Insert [{type.Name}] ({columnString}) values({valueColumn})";
using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(parameterList.ToArray());
conn.Open();
return command.ExecuteNonQuery() == 1;
//新增后把id拿出来？ 可以的，在sql后面增加个 Select @@Identity; ExecuteScalar
}
}
}
