using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.ComponentModel.DataAnnotations;
using MODEL;
using Dapper;
namespace DAL
{
public class ORMHelper: ConfigurationBaseDal
{
#region 批量查询
/// <summary>
/// 数据库链接字符串
/// </summary>
private static string SQLStr = "Data Source=.;Initial Catalog=Students;Persist Security Info=True;User ID=sa;Password=root;";
public List<T> GetAll<T>() where T : class, new()//定义一个泛型，和泛型约束
{
string sqlFormat = "select {0} from {1}";//定义基结构
Type type = typeof(T);//反射实体类
var pi = type.GetProperties();
string csStr = string.Join(",", pi.Select(o => o.Name));
sqlFormat = string.Format(sqlFormat, csStr, type.Name);
SqlConnection conn = new SqlConnection(SQLStr);
DataTable dt = new DataTable();
try
{
SqlDataAdapter dap = new SqlDataAdapter(sqlFormat, conn);//一次性加载数据库中所有属性，可以脱离链接进行操作，返回一个dataset对象
dap.Fill(dt);
List<T> list = new List<T>();
; i < dt.Rows.Count; i++)
{
T t = new T();
foreach (var pro in pi)
{
if (dt.Columns.Contains(pro.Name))
{
if (!(dt.Rows[i][pro.Name] is DBNull))//判断是否为空
{
pro.SetValue(t, dt.Rows[i][pro.Name]);
}
}
}
list.Add(t);
}
return list;
}
catch (Exception ex)
{
throw ex ;
}
}
#endregion
#region 条件查询
/// <summary>
/// 根据条件查询，也许有问题，讲师返回的是泛型T，而我经过改进返回的数值类型是datatable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public List<OrmsTable> GetModel<T>(OrmsTable model) where T : class, new()
{
string whereStr = "";
string sqlStr = "select {0} from {1} where 1=1 {2}";
Type typeT = typeof(T);
PropertyInfo[] pi = typeT.GetProperties();
string[] pNames = pi.Select(o => o.Name).ToArray();
string fieldStr = string.Join(",", pNames);//ID,name,chinese,enume"
pNames = pi.Where(p => p.GetCustomAttribute(typeof(KeyAttribute), false) != null).Select(o => o.Name).ToArray();
)
{
whereStr = ],model.Zhuangtai);//例如:and id=1;
}
sqlStr = string.Format(sqlStr, fieldStr, typeT.Name, whereStr);//select ID,name,chinese,enume from Class1 where 1=1 and ID = 1
var dt =CurrentDbConn.Query<OrmsTable>(sqlStr)?.ToList();
return dt;
}
#endregion
#region 添加数据
/// <summary>
/// 添加数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
//public static int Insert<T>(T model)
//{
// string sqlStr = "insert into {0}({1}) values({2})";
// string tbNameStr = string.Empty;
// string feildNameStr = string.Empty;
// string valStr = string.Empty;
// Type typeT = typeof(T);
// tbNameStr = typeT.Name;
// PropertyInfo[] pis = typeT.GetProperties();
// feildNameStr = string.Join(",", pis.Where(p => p.GetCustomAttribute(typeof(KeyAttribute), false) == null).Select(o => o.Name));
// valStr = string.Join(",", pis.Where(p => p.GetCustomAttribute(typeof(KeyAttribute), false) == null).Select(o => "@" + o.Name));
// sqlStr = string.Format(sqlStr, tbNameStr, feildNameStr, valStr);
// List<SqlParameter> pList = new List<SqlParameter>();
// foreach (PropertyInfo p in pis)
// {
// object obj = p.GetValue(model);
// string pName = "@" + p.Name;
// SqlParameter sp = new SqlParameter(pName, obj);
// pList.Add(sp);
// }
// return DBHelper.ExecuteNonQuery(sqlStr, pList.ToArray());
//}
public int Insert<T>(T Param) where T : class, new()
{
var t = typeof(T);
string sql = "insert into " + t.Name + " values('";
foreach (var item in t.GetProperties())
{
if(item.PropertyType.Name!="Int32")
{
sql += item.GetValue(Param) + "','";
}
else
{
sql += item.GetValue(Param) + ",";
}
}
,(sql.Length)-);
st += "')";
return CurrentDbConn.Execute(st);
}
#endregion
#region 删除
public int Delete<T>(T model)
{
string sqlStr = "delete from {0} where 1=1 {1}";
string strWhere = "";
Type typeT = typeof(T);
string tbName = typeT.Name;//声明一个变量用来存贮表的名字
var pi = typeT.GetProperties();
var pKey = pi.Where(p => p.GetCustomAttribute(typeof(KeyAttribute), false) != null);
List<SqlParameter> pList = new List<SqlParameter>();
)
{
PropertyInfo pK = pKey.First();
strWhere = string.Format(" and {0} = @{0}", pK.Name);
pList.Add(new SqlParameter("@" + pK.Name, pK.GetValue(model)));
}
sqlStr = string.Format(sqlStr, tbName, strWhere);
return CurrentDbConn.Execute(sqlStr, pList.ToArray());
}
#endregion
public bool Upt<T>(T t) where T : class, new()
{
bool ret = false;
try
{
string str = "";
string str1 = "";
string sql = "update {0} set {1} where {2}";
Type tp = typeof(T);
foreach (var i in tp.GetProperties())
{
var ty = i.PropertyType;
var tu = i.GetCustomAttributes(false);
)
{
str1 += " " + i.Name + "='" + i.GetValue(t) + "'";
}
else
{
switch (ty.Name)
{
case "Int32":
str += " " + i.Name + " ='" + i.GetValue(t) + "' ,";
break;
case "String":
str += " " + i.Name + " ='" + i.GetValue(t).ToString() + "' ,";
break;
}
}
}
str = str.Substring(, str.Length - );
sql = string.Format(sql, tp.Name, str, str1);
using (SqlConnection conn = new SqlConnection(SQLStr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
ret = true;
}
}
}
catch (Exception ex)
{
ret = false;
}
return ret;
}
}
}
