using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Collections;
using System.Reflection;
using Knet.H5.Entity.Core; namespace Knet.H5.Toolkit.Data.Core
{
public class DapperDBase<T> where T : class,new()
{
public string TableName { get; set; }
public string Primarykey { get; set; }
public List<string> CoulmnsList { get; set; }
public DapperDBase()
{
var tablenameAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
Primarykey = tablenameAttribute.PrimaryKey;
TableName = tablenameAttribute.TableName;
CoulmnsList = GetEntityProperties(typeof(T));
} /// 得到web.config里配置项的数据库连接字符串。
private static readonly string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ora9)));User Id=;Password=;"; private static DbConnection _db; private static readonly object objLocker = new object(); public static DbConnection DB
{
get
{
if (_db == null)
{
lock (objLocker)
{
if (_db == null)
{
Database Db = new OracleDatabase(connectionString);
DbConnection connection = Db.CreateConnection();
return connection;
}
}
}
return _db;
}
} /// <summary>
/// 根据ID获取model
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public T GetModelById(object Id)
{
string executeSql = @" SELECT " + string.Join(",", CoulmnsList) + " FROM " + this.TableName + " WHERE ID = :ID ";
var conditon = new { ID = Id };
return DB.Query<T>(executeSql, conditon).SingleOrDefault() ?? default(T);
} /// <summary>
/// 根据ID获取model
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public T GetModelById(object Id, params string[] selectCoumlns)
{
var selectFields = string.Empty;
if (selectCoumlns.Length > )
{
selectFields = string.Join<string>(",", selectCoumlns);
}
else
{
selectFields = string.Join(",", CoulmnsList);
}
string executeSql = @" SELECT " + selectFields + " FROM " + this.TableName + " WHERE " + this.Primarykey + " = :ID ";
var conditon = new { ID = Id };
return DB.Query<T>(executeSql, conditon).SingleOrDefault() ?? default(T);
} /// <summary>
/// 根据ID获取model
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public T GetModelByWhere(object whereObj)
{
var wherePro = whereObj.GetType().GetProperties();
var whereList= new List<string>();
foreach (var item in wherePro)
{
if (item.GetValue(whereObj) == null) continue;
whereList.Add(string.Format("{0}=:{0}", item.Name));
}
string executeSql = @" SELECT " + string.Join(",", CoulmnsList) + " FROM " + this.TableName;
if (whereList.Count > )
{
executeSql += " WHERE " + string.Join(" AND ", whereList);
}
return DB.Query<T>(executeSql, whereObj).SingleOrDefault() ?? default(T);
} /// <summary>
/// 获取列表
/// </summary>
/// <param name="whereStr">只能使用且的关系，且属于该model内的字段</param>
/// <param name="order">排序字段 create_date desc</param>
/// <returns></returns>
public List<T> GetList(object whereObj = null, string order = null)
{
var whereList = new List<string>();
if (whereObj != null)
{
var wherePro = whereObj.GetType().GetProperties();
foreach (var item in wherePro)
{
if (item.GetValue(whereObj) == null) continue;
whereList.Add(string.Format("{0}=:{0}", item.Name));
}
}
string executeSql = @" SELECT " + string.Join(",", CoulmnsList) + " FROM " + this.TableName;
if (whereList.Count > )
{
executeSql += " WHERE " + string.Join(" and ", whereList);
}
if (!string.IsNullOrEmpty(order))
{
executeSql += " ORDER BY " + order;
}
return DB.Query<T>(executeSql, whereObj).ToList();
} /// <summary>
/// 获取列表
/// </summary>
/// <param name="whereStr">只能使用且的关系，且属于该model内的字段</param>
/// <param name="order">排序字段 create_date desc</param>
/// <returns></returns>
public List<T> GetList(string whereStr = null, string order = null)
{
string executeSql = @" SELECT " + string.Join(",", CoulmnsList) + " FROM " + this.TableName;
if (!string.IsNullOrEmpty(whereStr))
{
executeSql += " where " + whereStr;
}
if (!string.IsNullOrEmpty(order))
{
executeSql += "order by " + order;
}
return DB.Query<T>(executeSql, whereStr).ToList();
} /// <summary>
/// 获取全部字段
/// </summary>
/// <param name="order">排序</param>
/// <returns></returns>
public List<T> GetAllList(string order = null)
{
string executeSql = @" SELECT " + string.Join(",", CoulmnsList) + " FROM " + this.TableName;
if (!string.IsNullOrEmpty(order))
{
executeSql += " ORDER BY " + order;
}
return DB.Query<T>(executeSql).ToList();
} /// <summary>
/// 插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Add(T model)
{
string executeSql = @" INSERT INTO " + this.TableName + " (" + string.Join(",", CoulmnsList) + " ) VALUES (" + string.Join(",:", CoulmnsList).Insert(, ":") + ") ";
return DB.Execute(executeSql, model);
} /// <summary>
/// 更新
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Update(T model)
{
var wherePro = model.GetType().GetProperties();
var whereSql = new List<string>();
foreach (var item in wherePro)
{
//if (item.GetValue(model) == null) continue;
whereSql.Add(string.Format("{0}=:{0}", item.Name));
}
string executeSql = @" UPDATE " + this.TableName + " SET " + string.Join(",", whereSql) + " WHERE " + this.Primarykey + "=:" + Primarykey;
return DB.Execute(executeSql, model) > ;
} /// <summary>
/// 根据条件更新指定的字段
/// </summary>
/// <param name="updateCoumlns"></param>
/// <param name="whereStr"></param>
/// <returns></returns>
public bool Update(object updateCoumlns, string whereStr)
{
var wherePro = updateCoumlns.GetType().GetProperties();
var whereSql = new List<string>();
foreach (var item in wherePro)
{
if (item.GetValue(updateCoumlns) == null) continue;
whereSql.Add(string.Format("{0}=:{0}", item.Name));
}
string executeSql = @" UPDATE " + this.TableName + " SET " + string.Join(",", whereSql);
if (!string.IsNullOrEmpty(whereStr))
{
executeSql += " WHERE " + whereStr;
}
return DB.Execute(executeSql, updateCoumlns) > ;
} //public bool Update(string[] fields,string value) /// <summary>
/// 获取分页数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="selectFields">查询的字段</param>
/// <param name="whereObj"></param>
/// <param name="order"></param>
/// <returns></returns>
public PagedList<T> GetPagerList(int pageIndex, int pageSize, string[] selectFields = null, object whereObj = null, string order = null)
{
var whereList = new List<string>();
if (whereObj != null)
{
var wherePro = whereObj.GetType().GetProperties();
foreach (var item in wherePro)
{
if (item.GetValue(whereObj) == null) continue;
whereList.Add(string.Format("{0}=:{0}", item.Name));
}
}
string orderSql = string.Empty, whereSql = string.Empty, fields = " row_.*";
if (!string.IsNullOrEmpty(order))
{
orderSql = " ORDER BY " + order;
}
if (whereList.Count > )
{
whereSql = " WHERE " + string.Join(" and ", whereList);
}
if (selectFields != null && selectFields.Length > )
{
fields = string.Join(",", selectFields);
}
string executeSql = @" SELECT COUNT(0) FROM " + this.TableName + whereSql;
int totalCount = DB.Query<int>(executeSql, whereObj).SingleOrDefault();
string pagerSql = "SELECT * FROM ( SELECT " + fields + ", rownum rownum_ from ( SELECT * FROM " + this.TableName + whereSql + orderSql + ") row_ where rownum <= " + pageIndex * pageSize + ") where rownum_ >" + (pageIndex - ) * pageSize + "";
var source = DB.Query<T>(pagerSql, whereObj).ToList();
return new PagedList<T>(source, pageIndex, pageSize, totalCount);
} /// <summary>
/// 删除数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool Delete(object id)
{
string executeSql = @" DELETE FROM " + this.TableName + " WHERE " + this.Primarykey + " = :ID";
var conditon = new { ID = id };
return DB.Execute(executeSql, conditon) > ;
} #region 直接执行sql /// <summary>
/// 执行sql语句，参数都以带入的形式
/// </summary>
/// <param name="executeSql"></param>
/// <param name="value"></param>
/// <returns></returns>
public bool Update(string executeSql, object value)
{
return DB.Execute(executeSql, value) > ;
} /// <summary>
/// 执行sql语句，参数都以带入的形式
/// </summary>
/// <param name="executeSql"></param>
/// <param name="value"></param>
/// <returns></returns>
public int Add(string executeSql, object value)
{
return DB.Execute(executeSql, value);
} /// <summary>
/// 执行SQL获取table
/// </summary>
/// <param name="executeSql"></param>
/// <returns></returns>
public DataTable GetTable(string executeSql)
{
return DB.Query<DataTable>(executeSql).SingleOrDefault();
} /// <summary>
/// 执行SQL获取LIST
/// </summary>
/// <param name="executeSql"></param>
/// <returns></returns>
public List<T> GetList(string executeSql)
{
return DB.Query<T>(executeSql).ToList();
}
#endregion /// <summary>
/// 对datatable进行分页
/// </summary>
/// <param name="dt"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <returns></returns>
public DataTable SplitDataTable(DataTable dt, int PageIndex, int PageSize)
{
if (PageIndex == )
return dt;
DataTable newdt = dt.Clone();
//newdt.Clear();
int rowbegin = (PageIndex - ) * PageSize;
int rowend = PageIndex * PageSize; if (rowbegin >= dt.Rows.Count)
return newdt; if (rowend > dt.Rows.Count)
rowend = dt.Rows.Count;
for (int i = rowbegin; i <= rowend - ; i++)
{
DataRow newdr = newdt.NewRow();
DataRow dr = dt.Rows[i];
foreach (DataColumn column in dt.Columns)
{
newdr[column.ColumnName] = dr[column.ColumnName];
}
newdt.Rows.Add(newdr);
} return newdt;
} /// <summary>
/// 获取实体所有属性名称
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private List<string> GetEntityProperties(Type type)
{
var list = new List<string>();
PropertyInfo[] properties = type.GetProperties();
foreach (var pro in properties)
{
var fieldsAttribute = new FieldsAttribute();
var attrmodel = pro.GetCustomAttributes<FieldsAttribute>(true).FirstOrDefault();
if (attrmodel != null )
{//controller上有标记
fieldsAttribute = attrmodel as FieldsAttribute;
}
if (!fieldsAttribute.IsSourceFields) continue;
// if ()
list.Add(pro.Name);
}
return list;
}
}
}