利用特性、泛型、反射生成sql操作语句（待修改
1、特性编写
/// <summary>
/// ExcludeFieldAttribute
///
/// 修改纪录
///
///
///
/// 版本：1.0
///
/// <author>
/// <name>Seven7</name>
/// <date></date>
/// </author>
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
[Serializable]
public class ExcludeFieldAttribute : Attribute
{
/// <summary>
/// 获取元数据的特性
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static ExcludeFieldAttribute GetAttribute(MemberInfo item)
{
var excludeFieldAttribute = (ExcludeFieldAttribute)GetCustomAttribute(item, typeof(ExcludeFieldAttribute));
return excludeFieldAttribute;
}
}
/// <summary>
/// 表信息
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
[System.Serializable]
public class TableInfoAttribute : Attribute
{
public TableInfoAttribute(string tableName)
{
TableName = tableName;
}
/// <summary>
/// 数据库中表的名称
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 获取元数据的特性
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static TableInfoAttribute GetAttribute(Type item)
{
var excludeFieldAttribute = (TableInfoAttribute)GetCustomAttribute(item, typeof(TableInfoAttribute));
return excludeFieldAttribute;
}
}
/// <summary>
/// 表示联盟在自动生成SQL语句时，不处理该字段
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
[System.Serializable]
public class UnionExcludeFieldAttribute : Attribute
{
/// <summary>
/// 获取元数据的特性
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static UnionExcludeFieldAttribute GetAttribute(MemberInfo item)
{
var UnionexcludeFieldAttribute = (UnionExcludeFieldAttribute)GetCustomAttribute(item, typeof(UnionExcludeFieldAttribute));
return UnionexcludeFieldAttribute;
}
}
/// <summary>
/// 用于，对原始字段做一些处理之后，得到新的字段
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
[System.Serializable]
public class ExtendedAttribute : Attribute
{
public ExtendedAttribute(string extendedSql)
{
ExtendedSql = extendedSql;
}
/// <summary>
/// 扩展语句
/// </summary>
public string ExtendedSql { get; set; }
private string _mysqlExtend = null;
/// <summary>
/// mysql版
/// </summary>
public string ExtendedMySql
{
get
{
return _mysqlExtend ?? ExtendedSql;
}
set { _mysqlExtend = value; }
}
public static ExtendedAttribute GetAttribute(MemberInfo item)
{
var extendedAttribute = (ExtendedAttribute)GetCustomAttribute(item, typeof(ExtendedAttribute));
return extendedAttribute;
}
}
/// <summary>
/// 标记字段为自增长类型的字段
/// </summary>
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
[System.Serializable]
public class IdentityAttribute : Attribute
{
public static IdentityAttribute GetAttribute(MemberInfo member)
{
return (IdentityAttribute)GetCustomAttribute(member, typeof(IdentityAttribute));
}
}
[AttributeUsage(AttributeTargets.Property)]
[System.Serializable]
public class RefFieldAttribute : Attribute
{
public RefFieldAttribute()
{
}
/// <summary>
/// </summary>
/// <param name="masterTableField">主表的外键</param>
/// <param name="refTableName">引用表名称</param>
/// <param name="refTableKey">引用表主键</param>
public RefFieldAttribute(string masterTableField, string refTableName, string refTableKey)
{
RefTableName = refTableName;
RefTableKey = refTableKey;
MasterTableField = masterTableField;
}
/// <summary>
/// 引用表的名称
/// </summary>
public string RefTableName { get; set; }
/// <summary>
/// 引用表的键
/// </summary>
public string RefTableKey { get; set; }
/// <summary>
/// 主表的外键
/// </summary>
public string MasterTableField { get; set; }
/// <summary>
/// 引用的字段
/// </summary>
public string RefFieldName { get; set; }
public static RefFieldAttribute GetAttribute(MemberInfo item)
{
var refFieldAttribute = (RefFieldAttribute)GetCustomAttribute(item, typeof(RefFieldAttribute));
return refFieldAttribute;
}
}
[System.Serializable]
public class FieldDescriptionAttribute : Attribute
{
public string FieldName { get; set; }
public FieldDescriptionAttribute(string fieldName)
{
FieldName = fieldName;
}
public static FieldDescriptionAttribute GetFieldDescriptionAttribute(MemberInfo element)
{
var fieldDesc = (FieldDescriptionAttribute)GetCustomAttribute(element, typeof(FieldDescriptionAttribute));
return fieldDesc;
}
}
/// <summary>
/// 缓存策略
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
public class DataCacheStrategyAttribute : Attribute
{
private EntityCacheExpirationPolicies expirationPolicy = EntityCacheExpirationPolicies.Normal;
/// <summary>
/// 是否使用缓存
/// </summary>
public bool EnableCache { get; private set; }
public bool ListCache { get; set; }
/// <summary>
/// 缓存过期策略
/// </summary>
public EntityCacheExpirationPolicies ExpirationPolicy
{
get
{
return this.expirationPolicy;
}
set
{
this.expirationPolicy = value;
}
}
public DataCacheStrategyAttribute(bool enableCache, bool listCache)
{
EnableCache = enableCache;
ListCache = listCache;
}
public static DataCacheStrategyAttribute GetStrategyAttribute(Type type)
{
var attr = (DataCacheStrategyAttribute)GetCustomAttribute(type, typeof(DataCacheStrategyAttribute));
return attr;
}
}
/// <summary>
/// 实体缓存期限类型
/// </summary>
public enum EntityCacheExpirationPolicies
{
/// <summary>
/// 稳定数据
/// </summary>
/// <remarks>
/// 例如： Area/School
/// </remarks>
Stable = 1,
/// <summary>
/// 常用的单个实体
/// </summary>
/// <remarks>
/// 例如： 用户、圈子
/// </remarks>
Usual = 3,
/// <summary>
/// 单个实体
/// </summary>
/// <remarks>
/// 例如： 博文、帖子
/// </remarks>
Normal = 5
}
2、其他辅助类
/// <summary>
/// DbOperation
/// 有关数据库操作的定义。
/// 修改纪录
///
///
///
/// 版本：1.0
///
/// <author>
/// <name>Seven7</name>
/// <date></date>
/// </author>
/// </summary>
public enum DbOperation
{
/// <summary>
/// 查询
/// </summary>
Select,
/// <summary>
/// 插入
/// </summary>
Insert,
/// <summary>
/// 更新
/// </summary>
Update,
/// <summary>
/// 删除
/// </summary>
Delete,
/// <summary>
/// 截取
/// </summary>
Truncate
}
public enum DbType
{
/// <summary>
/// 数据库类型：Oracle
/// </summary>
Oracle,
/// <summary>
/// 数据库类型：SqlServer
/// </summary>
SqlServer,
/// <summary>
/// 数据库类型：Access
/// </summary>
Access,
/// <summary>
/// 数据库类型：DB2
/// </summary>
DB2,
/// <summary>
/// 数据库类型：MySql
/// </summary>
MySql,
/// <summary>
/// 数据库类型：SQLite
/// </summary>
SQLite
}
public class ConntionObject
{
//Data Source=.;Initial Catalog=master;User ID=sa
//Data Source=.;Initial Catalog=master;Integrated Security=True
/// <summary>
/// 连接地址
/// </summary>
public string DataSource { get; set; }
/// <summary>
/// 连接数据库
/// </summary>
public string InitialCatalog { get; set; }
/// <summary>
/// 连接用户
/// </summary>
public string UserId { get; set; }
/// <summary>
/// 连接密码
/// </summary>
public string Password { get; set; }
/// <summary>
/// 连接方式（wondows/用户）
/// </summary>
public bool SSPI { get; set; }
/// <summary>
///
/// </summary>
public int Priority { get; set; }
/// <summary>
/// 是否开启连接池的限制
/// </summary>
public bool Pooling { get; set; }
/// <summary>
/// 最小连接池
/// </summary>
public int MinPoolSize { get; set; }
/// <summary>
/// 最大连接池
/// </summary>
public int MaxPoolSize { get; set; }
/// <summary>
/// 连接时间
/// </summary>
private long connectTimeout = 999;
public long ConnectTimeout
{
get { return connectTimeout; }
set { connectTimeout = value; }
}
}
public class PageHelp
{
/// <summary>
/// 当前页数
/// </summary>
private long currPage = 1;
/// <summary>
/// 总页数
/// </summary>
private long totalPage = Int32.MaxValue;
/// <summary>
/// 总行数
/// </summary>
private long totalRows=0;
/// <summary>
/// 每页显示的行数
/// </summary>
private long rows;
//private List<T> items;
/// <summary>
/// 当页的开始行
/// </summary>
private long upperLimit;
/// <summary>
/// 当页的末行
/// </summary>
private long lowerLimit;
/// <summary>
/// 返回当前页数
/// </summary>
public long CurrPage
{
get
{
this.currPage = currPage >= TotalPage ? TotalPage
: currPage;
this.currPage = currPage <= 0 ? 1 : currPage;
return currPage;
}
set { currPage = value; }
}
/// <summary>
/// 总页数
/// </summary>
public long TotalRows
{
get
{
return totalRows
}
set
{
totalPage = value < 1 ? 1 :value;
}
}
/// <summary>
/// 总行数
/// </summary>
public long TotalPage
{
get
{
return (totalRows - 1) / Rows + 1;
}
}
/// <summary>
/// 显示行数
/// </summary>
public long Rows
{
get { return rows; }
set { rows = value; }
}
/*
/// <summary>
/// 每页集合
/// </summary>
public List<T> Items
{
get { return items; }
set { items = value; }
}
*/
/// <summary>
/// 当页最大行数号
/// </summary>
public long UpperLimit
{
get
{
this.upperLimit = CurrPage * Rows;
return this.upperLimit;
}
set { upperLimit = value; }
}
/// <summary>
/// 当页最小行数号
/// </summary>
public long LowerLimit
{
get
{
this.lowerLimit = (CurrPage - 1) * Rows;
return this.lowerLimit;
}
set { lowerLimit = value; }
}
}
3、生成sql语句
public class SqlString
{
#region 字段
private static SqlString _sqlStr = new SqlString();
public static SqlString SqlStr
{
get
{
return _sqlStr;
}
}
private string _truncateSql = @"TRUNCATE TABLE {0} ";
/// <summary>
/// 分页查询的外部查询语句
/// <para>索引0：实际查询语句</para>
/// <para>索引1：排序规则</para>
/// <para>索引2：start</para>
/// <para>索引3：end</para>
/// </summary>
private string _selectSqlString = @" SELECT {4}
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {0} ) AS RowIndex , *
FROM ( {1} ) a
) v WHERE v.RowIndex BETWEEN {2} AND {3} ";
private string _deleteSqlString = @"DELETE FROM {0} ";
private string _updateSqlString = @"UPDATE {0} SET {1} WHERE {2} ";
private string _insertSqlString = @"INSERT INTO {0} ({1}) VALUES ({2})";
private string _identitySqlString = @" SELECT @@Identity AS ID ";
private string _paramPrefix = @"@";
public DbType _dbType { get; set; }
/// <summary>
/// 完整sql
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public string Recombine(string sql)
{
switch (_dbType)
{
case DbType.SqlServer:
break;
case DbType.Oracle:
sql = sql.Replace("@", _paramPrefix);
sql = sql.Replace("isnull(", "NVL(")
.Replace("ISNULL(", "NVL(")
.Replace("getDate()", "SYSDATE")
.Replace("getdate()", "SYSDATE")
.Replace("GETDATE()", "SYSDATE");
break;
case DbType.MySql:
sql = sql.Replace("isnull(", "ifnull(")
.Replace("ISNULL(", "ifnull(")
.Replace("getDate()", "now()")
.Replace("getdate()", "now()")
.Replace("GETDATE()", "now()");
break;
}
return sql;
}
#endregion
#region 删除
/// <summary>
/// 数据库截断
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public string TruncateToSql<T>()
{
Type type = typeof(T);
TableInfoAttribute tableInfoAttribute = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfoAttribute != null ? tableInfoAttribute.TableName : type.Name;
string truncateSql = String.Format(_truncateSql, tableName);
return Recombine(truncateSql);
}
/// <summary>
/// 根据sql条件删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <returns></returns>
public string DeleteToSql<T>(string where)
{
if (!string.IsNullOrEmpty(where))
{
throw new Exception("删除条件不能为空");
}
if (!where.Trim().StartsWith("where", StringComparison.InvariantCultureIgnoreCase))
{
where = " WHERE " + where;
}
Type type = typeof(T);
TableInfoAttribute tableInfoAttribute = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfoAttribute != null ? tableInfoAttribute.TableName : type.Name;
string deleteSql = _deleteSqlString + tableName + " " + where;
return Recombine(deleteSql);
}
#endregion
#region 添加
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity">参数对象</param>
/// <param name="dbType">数据库类型</param>
/// <param name="isValue">是否传递Parameters参数</param>
/// <param name="tableName">table名称</param>
/// <returns></returns>
public string AddToSql<T>(dynamic entity, DbType dbType = DbType.SqlServer, bool isValue = false, string tableName = null)
{
string insert = AddToSql(entity, dbType, isValue);
Type t = typeof(T);
Type t2 = entity.GetType();
if (t == typeof(int) && string.IsNullOrEmpty(tableName))
{
insert = insert.Replace(t2.Name, tableName);
}
else
{
insert = insert.Replace(t2.Name, t.Name);
}
return insert;
}
public string AddToSql(string tableName, dynamic entity, DbType dbType = DbType.SqlServer, bool isValue = false)
{
return AddToSql<int>(entity, dbType, isValue, tableName);
}
public string AddToSql(dynamic entity, DbType dbType = DbType.SqlServer, bool isValue = false)
{
if (entity == null) throw new Exception(entity.GetType().Name + "类型为空！");
Type type = entity.GetType();
TableInfoAttribute tableInfoAttribute = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfoAttribute != null ? tableInfoAttribute.TableName : type.Name;
int count = 0;
StringBuilder key = new StringBuilder();
StringBuilder value = new StringBuilder();
StringBuilder sql = new StringBuilder();
bool pk = false;
foreach (PropertyInfo item in type.GetProperties())
{
if (ExcludeFieldAttribute.GetAttribute(item) != null) continue;//自增属性不处理
if (IdentityAttribute.GetAttribute(item) != null)
{
pk = true;
continue;
}//自增长列不处理
object values = item.GetValue(entity, null);
if (values == null || values == DBNull.Value) continue;
//if(RequiredAttribute.GetCustomAttribute(item.Module,item.GetType())==null)
// throw new Exception("此列属性为必填项");
if (count != 0)
{
key.Append(',');
value.Append(',');
}
key.Append(item.Name);
if (isValue)
{
value.Append(item.GetValue(entity));
}
else
{
value.Append('@' + item.Name);
}
count++;
}
sql.Append(string.Format(_insertSqlString, tableName, key, value));
sql.Append(";");
if (pk)
{
switch (dbType)
{
case DbType.Oracle:
break;
case DbType.SqlServer:
sql.AppendFormat(_identitySqlString);
break;
case DbType.MySql:
sql.AppendFormat(";");
sql.AppendLine(_identitySqlString);
break;
}
}
return Recombine(sql.ToString());
}
/// <summary>
/// 删除一组对象
/// </summary>
/// <param name="entities"></param>
/// <param name="dbType"></param>
/// <param name="isValue"></param>
/// <returns></returns>
public string AddsToSql(ICollection entities, DbType dbType, bool isValue/*,List<KeyValuePair<dynamic,string>> dic*/)
{
if (entities == null)
{
throw new ArgumentNullException("entities", "列表为空");
}
StringBuilder insert = new StringBuilder();
foreach (object entity in entities)
{
if (dbType == DbType.Oracle) insert.Append("BEGIN");
if (entity == null) continue;
//dic.Add(new KeyValuePair<dynamic, string> (entity, AddToSql(entity, dbType)));
insert.Append(AddToSql(entity, dbType));
if (dbType == DbType.Oracle) insert.Append("END");
}
return Recombine(insert.ToString());
}
#endregion
/// <summary>
/// 根据不同数据库更改select语句 在构造函数中调用
/// </summary>
/// <param name="dataName"></param>
/// <param name="_dbType"></param>
internal void SetSelect(DbType _dbType)
{
this._dbType = _dbType;
switch (_dbType) {
case DbType.Oracle:
_selectSqlString = @"select * from
(
select @rownum:=@rownum+1 as RowIndex, a.* from
(
{0} order by {1}
) a ,
(
select @rownum:=0
) b
) t where t.RowIndex BETWEEN {2} AND {3}";
_identitySqlString = "select LAST_INSERT_ID() as ID;";
break;
case DbType.MySql:
_selectSqlString = @"select * from {0} where {1} limit {2},{3}";
_identitySqlString = @"SELECT LAST_INSERT_ID()";
break;
default:
break;
}
if (_dbType == DbType.Oracle)
{
_paramPrefix = ":";
}
}
private string UpdateToSql(dynamic entity, bool isValue = false)
{
if (entity == null) throw new Exception(entity.GetType().Name + "类型为空！");
Type type = entity.GetType();
TableInfoAttribute tableInfoAttribute = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfoAttribute != null ? tableInfoAttribute.TableName : type.Name;
StringBuilder set = new StringBuilder();
int count = 0;
foreach (PropertyInfo item in type.GetProperties())
{
if (ExcludeFieldAttribute.GetAttribute(item) != null) continue;//自增属性不处理
if (IdentityAttribute.GetAttribute(item) != null) continue;
if (count != 0) set.Append(",");
if (isValue)
{
set.AppendFormat("{0} = '{0}'", item.GetValue(entity));
}
else
{
set.AppendFormat("{0} = @{0}", item.Name);
}
count++;
}
return Recombine(string.Format(_updateSqlString, tableName, set));
}
public string UpdateToSql(dynamic entity, string where = "1=1", bool isValue = false)
{
//Type t = entity.GetType();
//PropertyInfo[] info = t.GetProperties();
//PropertyInfo pk;
if (!where.StartsWith("where", StringComparison.InvariantCultureIgnoreCase))
{
where = " where " + where;
}
return UpdateToSql(entity, isValue) + Recombine(where);
}
/// <summary>
/// 批量删除
/// </summary>
/// <param name="enumerable">对象：修改条件</param>
/// <param name="isValue">是否传递Parameters参数</param>
/// <returns></returns>
public string UpdatesToSql(List<KeyValuePair<dynamic, string>> enumerable, bool isValue=false)
{
StringBuilder str = new StringBuilder();
foreach (KeyValuePair<dynamic, string> k in enumerable)
{
str.Append(UpdateToSql(k.Key, k.Value, isValue) + ";");
}
return Recombine(str.ToString());
}
/// <summary>
/// 查询
/// </summary>
/// <param name="type"></param>
/// <param name="where"></param>
/// <param name="dbType"></param>
/// <returns></returns>
public string SelectToSql(Type type, string where, DbType dbType = DbType.SqlServer)
{
if (!string.IsNullOrEmpty(where))
{
if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
{
where = "Where " + where;
}
}
StringBuilder sql = new StringBuilder("SELECT ");
List<TablePrimary> tablePrimary = TablePrimary.GetTablePrimary(type);
List<TablePrimary> tables = TablePrimary.GetDistinct(tablePrimary);
foreach (TablePrimary v in tablePrimary)
{ //是否有外键的字段
if (tables.Count <= 1)
{
if (!v.IsExtend) {
sql.AppendFormat("{0},", v.FieldName);
}
}
else
{
if (string.IsNullOrEmpty(v.MasterTableField))
{
sql.AppendFormat("{0}.{1},", v.TableName, v.FieldName);
}
else
{
sql.AppendFormat("{0}.{1} {2},", v.TableName, v.FieldName, v.FieldAlias);
}
}
}
//添加的sql语句
PropertyInfo[] infos = type.GetProperties();
foreach (PropertyInfo pro in infos)
{
ExtendedAttribute extended = ExtendedAttribute.GetAttribute(pro);
if (extended != null)
{
var extSql = (dbType == DbType.MySql) ? extended.ExtendedMySql : extended.ExtendedSql;
sql.Append("(" + extSql + ") " + pro.Name + ",");
}
}
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfo == null ? type.Name : tableInfo.TableName, joinOn = "";
sql = sql.Remove(sql.Length - 1, 1);
sql.AppendLine();
sql.AppendFormat(" FROM {0} ", tableName);
//连表
foreach (TablePrimary v in tables)
{
if (!string.IsNullOrEmpty(v.MasterTableField))
{
joinOn = string.Format(" {0}.{1} = {2}.{3} ", tableName, v.MasterTableField, v.TableName, v.RelateField);
sql.AppendFormat("JOIN {0} ON ({1}) ", v.TableName, joinOn);
}
}
sql.Append(where);
return Recombine(sql.ToString());
}
/// <summary>
/// 根据主键进行查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public string SqlectByIdToSql<T>(long id) {
Type t = typeof(T);
PropertyInfo info = t.GetProperties().First(p => IdentityAttribute.GetAttribute(p)!=null);
if (info != null) {
string str = info.Name + "=" + id;
return SelectToSql(t, str);
}
throw new Exception("No Find Identity");
}
public string SelectJoinSql<T>(PageHelp page, string strWhere = "1=1", string orderBy = "", DbType dbType = DbType.SqlServer)
{
return Recombine(SelectSql(typeof(T), page, strWhere, orderBy, dbType));
}
private string SelectSql(Type t, PageHelp page, string strWhere = "1=1", string orderBy = "", DbType dbType = DbType.SqlServer,bool IsCount=false){
if (string.IsNullOrEmpty(strWhere))
strWhere = "1=1";
Type type = t;
PropertyInfo pk = type.GetProperties().FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
if (string.IsNullOrEmpty(orderBy))
{
if (pk != null)
orderBy = pk.Name + " DESC ";
else throw new Exception("未设置排序字段");
}
if (orderBy.Trim().StartsWith("ORDER BY", StringComparison.CurrentCultureIgnoreCase))
{
orderBy = orderBy.ToLower().Replace("order by", " ");
}
string dataSql = string.Format(_selectSqlString
, SelectToSql(t, strWhere)
, orderBy
, page.LowerLimit
, page.UpperLimit
, IsCount ? "Count(1)" : "*");
return Recombine(dataSql);
}
/// <summary>
/// 更新指定表的指定字段
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="fieldName">字段名称</param>
/// <param name="value">字段值</param>
/// <param name="where">更新条件</param>
/// <returns></returns>
public string UpdateField(string tableName, string fieldName, string value, string where)
{
if (!where.TrimStart().StartsWith("where", StringComparison.OrdinalIgnoreCase))
{
where = " WHERE " + where;
}
value = "'" + value + "'";
string updateSql = string.Format("UPDATE {0} SET {1}={2} {3}", tableName, fieldName, value, where);
return updateSql;
}
/// <summary>
/// 更新字段
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fieldvalues">字段名称和字段值的键值对集合</param>
/// <param name="where">更新条件</param>
/// <returns></returns>
public string UpdateFields(string tableName, List<KeyValuePair<string, string>> fieldvalues, string where)
{
if (!string.IsNullOrEmpty(where))
{
if (!where.TrimStart().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
{
where = " WHERE " + where;
}
}
StringBuilder updateSql = new StringBuilder().AppendFormat("UPDATE {0} SET ", tableName);
for (int i = 0; i < fieldvalues.Count; i++)
{
string value = fieldvalues[i].Value == null ? "NULL" : string.Format("'{0}'", fieldvalues[i].Value);
updateSql.AppendFormat("{0}={1}", fieldvalues[i].Key, value);
if (i < fieldvalues.Count - 1)
updateSql.Append(",");
updateSql.AppendLine();
}
updateSql.Append(where);
return updateSql.ToString();
}
}
4、Ado.net操作（未对sql、数据进行缓存）
/// <summary>
/// IData
///
/// 修改纪录
///
///
///
/// 版本：1.0
///
/// <author>
/// <name>Seven7</name>
/// <date></date>
/// </author>
/// </summary>
public interface IDataBase
{
/// <summary>
/// 传送sql语句，执行增删改操作
/// </summary>
/// <param name="sql">完整sql语句</param>
/// <returns>受影响行数</returns>
int ExecuteSql(string sql);
/// <summary>
/// 返回首行首列数据
/// </summary>
/// <param name="sql">完整sql语句</param>
/// <returns>受影响行数</returns>
int ExecuteScalarSql(string sql);
/// <summary>
/// 删除实体对象
/// </summary>
/// <typeparam name="T">实体对象</typeparam>
/// <param name="where">删除sql条件</param>
/// <returns>受影响行数</returns>
int DelectEntity<T>(string where);
/// <summary>
/// 删除全部数据，自增列重新排序
/// </summary>
/// <typeparam name="T">实体对象</typeparam>
/// <returns>受影响行数</returns>
int TruncateTable<T>();
/// <summary>
/// 添加单个对象
/// </summary>
/// <param name="entity">实体对象</param>
/// <returns>受影响行数</returns>
int AddEntity<T>(dynamic entity);
/// <summary>
/// 添加单个对象
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="entity">实体对象</param>
/// <returns>受影响行数</returns>
int AddEntity(string tableName, object entity);
/// <summary>
/// 添加单个对象
/// </summary>
/// <param name="entity">实体对象</param>
/// <returns>受影响行数</returns>
int AddEntity(BaseEntity entity);
/// <summary>
/// 添加一组对象
/// </summary>
/// <param name="entities">添加集合</param>
/// <returns>受影响行数</returns>
int AddEntities(ICollection entities);
/// <summary>
/// 更新单个对象
/// </summary>
/// <param name="entity"></param>
/// <param name="primaryKeyField"></param>
/// <returns></returns>
int UpdateEntity(object entity);
/// <summary>
/// 更新一组对象
/// </summary>
/// <param name="entity"></param>
/// <param name="primaryKeyField"></param>
/// <returns></returns>
int UpdatesEntity(List<KeyValuePair<dynamic, string>> enumerable);
/// <summary>
/// 获取对象的集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
List<T> GetListEntity<T>(string sql, object entity, CommandType commandType);
/// <summary>
/// 获取单个对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
T GetEntity<T>(string sql, object entity);
/// <summary>
/// 获取分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="page"></param>
/// <param name="strWhere"></param>
/// <param name="orderBy"></param>
/// <param name="entity"></param>
/// <returns></returns>
List<T> GetListEntity<T>(PageHelp page, string strWhere = "1=1", string orderBy = "", object entity = null);
/// <summary>
/// 获取赛选后的数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strWhere"></param>
/// <param name="entity"></param>
/// <returns></returns>
List<T> GetListEntity<T>(string strWhere = "1=1", object entity = null);
/// <summary>
/// 获取单个实例对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
T GetEntityByID<T>(long id);
/// <summary>
/// 获取数据的总个数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strWhere"></param>
/// <param name="orderBy"></param>
/// <param name="entity"></param>
/// <returns></returns>
long GetEntityCount<T>(string strWhere = "1=1", string orderBy = "", object entity = null);
}
/// <summary>
/// DataBase
///
/// 修改纪录
///
///
///
/// 版本：1.0
///
/// <author>
/// <name>Seven7</name>
/// <date></date>
/// </author>
/// </summary>
public class DataBase : IDataBase
{
#region 字段与构造函数
#region 字段
///数据库的命名空间名
private string _connProviderName;
/// 连接的字符串，注：在配置文件中的连接字符串要进行加密
private readonly string _connString;
///各个数据库库的抽象工厂
private DbProviderFactory _factory;
///生产Sql语句的对象
SqlString sqlstr = SqlString.SqlStr;
///数据库类型
private DbType _dbType = DbType.SqlServer;
///数据库操作类型
private DbOperation _ddlType = DbOperation.Insert;
///委托
private delegate dynamic ExecuteTranc(IDbCommand comm);
#endregion
#region 构造函数
/// <summary>
///
/// </summary>
public DataBase(DbType dbType)
{
_connString = ConfigurationManager.ConnectionStrings[Enum.GetName(typeof(DbType), dbType)].ConnectionString;
SetSql(dbType);
}
/// <summary>
///
/// </summary>
/// <param name="connSql"></param>
/// <param name="dbType"></param>
public DataBase(ConntionObject connSql, DbType dbType)
{
SetSql(dbType);
DbConnectionStringBuilder builder = _factory.CreateConnectionStringBuilder();
builder["Data Source"] = connSql.DataSource;
builder["integrated Security"] = connSql.SSPI;
builder["Initial Catalog"] = connSql.InitialCatalog;
// builder["Connect Timeout"] = connSql.ConnectTimeout;
if (connSql.Pooling)
{
builder["Pooling"] = connSql.Pooling;
builder["Min Pool Size"] = connSql.MinPoolSize;
builder["Max Pool Size"] = connSql.MaxPoolSize;
}
if (connSql.SSPI)
{
builder["Integrated Security"] = "SSPI";
}
else {
builder["User Id"] = connSql.UserId;
builder["Password"] = connSql.Password;
}
_connString = builder.ConnectionString;
}
/// <summary>
/// 参数值要与web.config保持一致
/// </summary>
public void SetSql(DbType dbType)
{
_connProviderName = GetProviderName(dbType);
_factory = DbProviderFactories.GetFactory(_connProviderName);
sqlstr.SetSelect(dbType);
if (_connProviderName.StartsWith("Oracle"))
{
ExecuteSql("ALTER SESSION SET NLS_COMP='LINGUISTIC'");
ExecuteSql("ALTER SESSION SET NLS_SORT='BINARY_CI'");
}
sqlstr._dbType = dbType;
}
/// <summary>
/// 获取命名空间字符
/// </summary>
/// <param name="dbType"></param>
/// <returns></returns>
private string GetProviderName(DbType dbType)
{
switch (dbType)
{
case DbType.SqlServer:
return "System.Data.SqlClient";
case DbType.Oracle:
return "System.Data.OracleClient;";
case DbType.MySql:
return "MySql.Data.MySqlClient;";
default:
return "";
}
}
#endregion
#endregion
#region 获取ADO对象
/// <summary>
/// 获取链接数据对象
/// </summary>
/// <returns>返回Connection对象</returns>
private IDbConnection GetConnection()
{
IDbConnection connection = _factory.CreateConnection();
connection.ConnectionString = _connString;
connection.Open();
return connection;
}
/// <summary>
/// 获取Command对象
/// </summary>
/// <param name="sqlString">传送完整的sql语句</param>
/// <param name="param">Paramter参数值，可以是匿名对象或实体对象</param>
/// <param name="commandType">执行的是sql语句还是，存储过程</param>
/// <param name="commandTimeout">链接数据的时间限制</param>
/// <returns>Command对象</returns>
private IDbCommand GetCommand(string sqlString, dynamic param = null, CommandType commandType = CommandType.Text,
int? commandTimeout = 60)
{
IDbCommand command = _factory.CreateCommand();
command.Connection = GetConnection();
command.CommandText = sqlString;
command.CommandType = commandType;
command.CommandTimeout = commandTimeout.Value;
//添加参数
SetParameters(command, param);
return command;
}
/// <summary>
/// 将对象转为paramter传给command对象
/// </summary>
/// <param name="comm">Command对象</param>
/// <param name="obj"></param>
private void SetParameters(IDbCommand comm, dynamic obj)
{
if (comm == null || obj == null) return;
Type type = obj.GetType();//typeof(t);
comm.Parameters.Clear();
//IEnumerable iEnumer = obj as IEnumerable;
//if (iEnumer != null && !(iEnumer is string)) {
//}
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo item in propertyInfos)
{
switch (_ddlType)
{
case DbOperation.Insert:
if (IdentityAttribute.GetAttribute(item) != null) continue;
break;
case DbOperation.Delete:
break;
}
DbParameter dbparameter = _factory.CreateParameter();
dbparameter.ParameterName = "@" + item.Name;
//if (item.GetValue(obj) != null)
//{
// dbparameter.Value = "";
//}
dbparameter.Value = item.GetValue(obj);
comm.Parameters.Add(dbparameter);
}
}
/// <summary>
/// 执行增删改语句命名
/// </summary>
/// <param name="comm">Command对象</param>
/// <param name="obj"></param>
private dynamic Execute(IDbCommand comm)
{
return comm.ExecuteNonQuery();
}
/// <summary>
/// 返回首行首列数据
/// </summary>
/// <param name="comm">Command对象</param>
/// <param name="obj"></param>
private dynamic ExecuteScalar(IDbCommand comm)
{
object obj = comm.ExecuteScalar();
dynamic dy = obj;
if (obj.GetType() == typeof(int)) dy = (int)obj;
if (obj.GetType() == typeof(long)) dy = (long)obj;
if (obj.GetType() == typeof(decimal)) dy = (decimal)obj;
if (obj.GetType() == typeof(bool)) dy = (bool)obj;
if (obj.GetType() == typeof(string)) dy = obj.ToString();
return dy;
}
/// <summary>
/// 执行command命令
/// </summary>
/// <param name="etFunc">调用方法名</param>
/// <param name="sqlString">传送完整的sql语句</param>
/// <param name="param">Paramter参数值，可以是匿名对象或实体对象</param>
/// <param name="commandType">执行的是sql语句还是，存储过程</param>
/// <param name="commandTimeout">链接数据的时间限制</param>
/// <returns></returns>
private dynamic ExTranc(ExecuteTranc etFunc, string sqlString, dynamic param = null,bool IsOpenTran= true, CommandType commandType = CommandType.Text, int? commandTimeout = 60)
{
using (IDbCommand comm = GetCommand(sqlString, param, commandType, commandTimeout))
{
using (comm.Connection)
{
dynamic result;
IDbTransaction tran = null;
if (IsOpenTran) {
tran = comm.Connection.BeginTransaction();
}
try
{
comm.Transaction = tran;
result = etFunc(comm);
if(tran != null ) tran.Commit();
return result;
}
catch (Exception ex)
{
try
{
if (tran != null) tran.Rollback();
}
catch (Exception)
{
string exce = "发生错误，SQL语句为：" + sqlString + (param != null ? "实体为：" + param.ToString() : "");
throw new Exception(exce, ex);
}
}
return 0;
}
}
}
#endregion
#region 数据操作与查询
#region 单行单列
/// <summary>
/// 传送sql语句，执行增删改操作
/// </summary>
/// <param name="sql">完整sql语句</param>
/// <returns>受影响行数</returns>
public int ExecuteSql(string sql)
{
return ExTranc(Execute, sql);
}
/// <summary>
/// 返回首行首列数据
/// </summary>
/// <param name="sql">完整sql语句</param>
/// <returns>受影响行数</returns>
public int ExecuteScalarSql(string sql)
{
return ExTranc(ExecuteScalar, sql);
}
#endregion
#region 删除
/// <summary>
/// 删除实体对象
/// </summary>
/// <typeparam name="T">实体对象</typeparam>
/// <param name="where">删除sql条件</param>
/// <returns>受影响行数</returns>
public int DelectEntity<T>(string where)
{
return ExTranc(Execute, sqlstr.DeleteToSql<T>(where));
}
/// <summary>
/// 删除全部数据，自增列重新排序
/// </summary>
/// <typeparam name="T">实体对象</typeparam>
/// <returns>受影响行数</returns>
public int TruncateTable<T>()
{
return ExTranc(Execute, sqlstr.TruncateToSql<T>());
}
#endregion
#region 添加
/// <summary>
/// 添加单个对象
/// </summary>
/// <param name="entity">实体对象</param>
/// <returns>受影响行数</returns>
public int AddEntity<T>(object entity)
{
return ExTranc(Execute, sqlstr.AddToSql<T>(entity, _dbType), entity);
}
/// <summary>
/// 添加单个对象
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="entity">实体对象</param>
/// <returns>受影响行数</returns>
public int AddEntity(string tableName, object entity)
{
return ExTranc(Execute, sqlstr.AddToSql(entity, _dbType), entity);
}
/// <summary>
/// 添加单个对象
/// </summary>
/// <param name="entity">实体对象</param>
/// <returns>受影响行数</returns>
public int AddEntity(BaseEntity entity)
{
return ExTranc(Execute, sqlstr.AddToSql(entity, _dbType), entity);//Execute(sqlstr.AddToSql(entity, _dbType), entity);
}
/// <summary>
/// 添加一组对象
/// </summary>
/// <param name="entities">添加集合</param>
/// <returns>受影响行数</returns>
public int AddEntities(ICollection entities)
{
//List<KeyValuePair<dynamic,string>> dic = new List<KeyValuePair<dynamic,string>>();
string insertSql = sqlstr.AddsToSql(entities, _dbType, true);
//foreach(KeyValuePair<dynamic,string> k in dic){
// Execute(k.Value, k.Key,CommandType.Text);
// count++;
//}
return ExTranc(Execute, insertSql);
}
#endregion
#region 跟新
/// <summary>
/// 更新单个对象
/// </summary>
/// <param name="entity"></param>
/// <param name="primaryKeyField"></param>
/// <returns></returns>
public int UpdateEntity(object entity)
{
return ExTranc(Execute, sqlstr.UpdateToSql(entity), entity);
}
/// <summary>
/// 更新一组对象
/// </summary>
/// <param name="entity"></param>
/// <param name="primaryKeyField"></param>
/// <returns></returns>
public int UpdatesEntity(List<KeyValuePair<dynamic, string>> enumerable)
{
return ExTranc(Execute, sqlstr.UpdatesToSql(enumerable, true));
}
#endregion
#region 查询
/// <summary>
/// 获取对象的集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public List<T> GetListEntity<T>(string sqlString, dynamic param = null, CommandType commandType = CommandType.Text)
{
List<T> result = new List<T>();
using (DbCommand comm = GetCommand(sqlstr.Recombine(sqlString), param, commandType))
{
using (comm.Connection)
{
using (IDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
result.Add(GetEntity<T>(reader));
}
}
}
}
return result;
}
/// <summary>
/// 获取单个对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public T GetEntity<T>(string sql, object entity = null)
{
List<T> list = GetListEntity<T>(sql, entity);
T result = default(T);
if (list != null && list.Count > 0)
{
result = list.FirstOrDefault<T>();
}
return result;
}
public T GetEntityByID<T>(long id)
{
string sql = sqlstr.SqlectByIdToSql<T>(id);
List<T> list = GetListEntity<T>(sql, null, CommandType.Text);
T result = default(T);
if (list != null && list.Count > 0)
{
result = list.FirstOrDefault<T>();
}
return result;
}
private T GetEntity<T>(IDataReader dr)
{
T t = Activator.CreateInstance<T>();
string name = "";
for (int i = 0; i < dr.FieldCount; i++)
{
if (!dr[i].Equals(DBNull.Value))
{
name = dr.GetName(i).Substring(0, 1).ToUpper() + dr.GetName(i).Substring(1);
if (typeof(T).GetProperty(name) != null)
typeof(T).GetProperty(name).SetValue(t, dr[i], null);
}
}
return t;
}
public List<T> GetListEntity<T>(PageHelp page, string strWhere = "1=1", string orderBy = "", object entity = null)
{
string str = sqlstr.SelectJoinSql<T>(page, strWhere, orderBy, _dbType);
return GetListEntity<T>(str, entity, CommandType.Text);
}
public List<T> GetListEntity<T>(string strWhere = "1=1", object entity = null)
{
string str = sqlstr.SelectToSql(typeof(T), strWhere, _dbType);
return GetListEntity<T>(str, entity, CommandType.Text);
}
public long GetEntityCount<T>(string strWhere = "1=1", string orderBy = "", object entity = null)
{
string sql = sqlstr.SelectJoinSql<T>(new PageHelp(), strWhere, orderBy, _dbType);
return ExTranc(ExecuteScalar, sql);
}
#endregion
#endregion
}
public class BaseEntity
{
}
浙公网安备 33010602011771号