/// <summary>
/// 获取待更新的数据库查询语句.
/// </summary>
/// <param name="entity">实体.</param>
/// <param name="tableName">数据库表名.</param>
/// <param name="fields">条件字段.</param>
/// <param name="commandParameters">输出参数.</param>
/// <param name="prefixParameter">The prefix paramter.</param>
/// <returns></returns>
/// <exception cref="System.Data.Common.DbException"></exception>
public string UpdateCommandText(IEntity entity, string tableName, string[] fields, out DbParameter[] commandParameters, string prefixParameter = "")
{
try
{
Type type = GetCacheType(entity);
PropertyInfo[] pis = type.GetProperties();
string sql = "UPDATE " + tableName + " SET ";
bool isValidateDataField = EntityClassAttribute.IsValidateField(type);
using (MySqlCommand cmd = new MySqlCommand())
{
string condition = "";
foreach (PropertyInfo pi in pis)
{
// 此类中包含有非数据库字段，需要进行排除
if (isValidateDataField)
{
if (EntityPropertyAttribute.IsNoDataField(pi))
{
continue;
}
}
string paraName = "@" + prefixParameter + entity.EntityFullName.Replace(",", "_").Replace(".", "_") + "_" + pi.Name;
//string type = pi.PropertyType.UnderlyingSystemType.ToString().ToLower();
if (fields != null && fields.Contains <string>(pi.Name, StringComparer.InvariantCultureIgnoreCase))
{
condition += (String.IsNullOrEmpty(condition) ? "" : " and ") + pi.Name + "=" + paraName;
cmd.Parameters.AddWithValue(paraName, pi.GetValue(entity, null));
}
else
{
if (pi.GetValue(entity, null) != null && pi.CanWrite && pi.Name.ToLower() != "entityfullname")
{
sql += pi.Name + "=" + paraName + ",";
cmd.Parameters.AddWithValue(paraName, pi.GetValue(entity, null));
}
}
}
sql = sql.Remove(sql.Length - 1);
if (!Utils.IsNullOrEmpty(condition))
{
sql = sql + " WHERE " + condition;
}
commandParameters = new MySqlParameter[cmd.Parameters.Count];
// 将cmd的Parameters参数集复制到discoveredParameters数组.
cmd.Parameters.CopyTo(commandParameters, 0);
return(sql + ";");
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取待更新的数据库查询语句.
/// </summary>
/// <param name="entity">实体.</param>
/// <param name="tableName">数据库表名.</param>
/// <param name="fields">条件字段.</param>
/// <returns></returns>
public string UpdateCommandText(IEntity entity, string tableName, string[] fields)
{
try
{
Type type = GetCacheType(entity);
PropertyInfo[] pis = type.GetProperties();
// 获取此类中是否包含数据字段验证
bool isValidateDataField = EntityClassAttribute.IsValidateField(type);
string sql = "UPDATE " + tableName + " SET ";
string condtion = "";
foreach (PropertyInfo pi in pis)
{
// 此类中包含有非数据库字段，需要进行排除
if (isValidateDataField)
{
if (EntityPropertyAttribute.IsNoDataField(pi))
{
continue;
}
}
//string type = pi.PropertyType.UnderlyingSystemType.ToString().ToLower();
if (fields != null && fields.Contains <string>(pi.Name, StringComparer.InvariantCultureIgnoreCase))
{
condtion += (String.IsNullOrEmpty(condtion) ? "" : " and ") + pi.Name + "=" + SqlString(pi, pi.GetValue(entity, null));
}
else
{
if (pi.GetValue(entity, null) != null && pi.CanWrite && pi.Name.ToLower() != "entityfullname")
{
sql += pi.Name + "=" + SqlString(pi, pi.GetValue(entity, null)) + ",";
}
}
}
sql = sql.Remove(sql.Length - 1);
if (!Utils.IsNullOrEmpty(condtion))
{
sql = sql + " WHERE " + condtion;
}
return(sql + ";");
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取删除数据的Sql语句.
/// </summary>
/// <param name="entity">已附值的实体.</param>
/// <param name="tableName">需要删除数据的表.</param>
/// <param name="commandParameters">输出参数.</param>
/// <param name="prefixParameter">The prefix paramter.</param>
/// <returns></returns>
/// <exception cref="System.Data.Common.DbException"></exception>
public string DeleteCommandText(IEntity entity, string tableName, out DbParameter[] commandParameters, string prefixParameter = "")
{
try
{
Type type = entity.GetType();
PropertyInfo[] pis = type.GetProperties();
bool isValidateDataField = EntityClassAttribute.IsValidateField(type);
string sql = "DELETE FROM " + tableName + " WHERE ";
using (MySqlCommand cmd = new MySqlCommand())
{
string where = "";
foreach (PropertyInfo pi in pis)
{
if (pi.GetValue(entity, null) != null && pi.Name.ToLower() != "entityfullname")
{
// 此类中包含有非数据库字段，需要进行排除
if (isValidateDataField)
{
if (EntityPropertyAttribute.IsNoDataField(pi))
{
continue;
}
}
string paraName = "@" + prefixParameter + entity.EntityFullName.Replace(",", "_").Replace(".", "_") + "_" + pi.Name;
where += (Utils.IsNullOrEmpty(where) ? "" : " AND ") + pi.Name + "=" + paraName;
cmd.Parameters.AddWithValue(paraName, pi.GetValue(entity, null));
}
}
commandParameters = new MySqlParameter[cmd.Parameters.Count];
// 将cmd的Parameters参数集复制到discoveredParameters数组.
cmd.Parameters.CopyTo(commandParameters, 0);
return(sql + where + ";");
}
}
catch (Exception ex)
{
throw ex;
}
}
private static EntityMetadata DoGetEntityType(Type entityType)
{
EntityMetadata table = new EntityMetadata(entityType);
string tableName = entityType.Name.ToLower();
object[] attrs = entityType.GetCustomAttributes(typeof(EntityClassAttribute), true);
if (attrs != null && attrs.Length > 0)
{
EntityClassAttribute tableAttribute = (EntityClassAttribute)attrs[0];
table.LowerCaseKey = tableAttribute.LowerCaseKey;
if (!string.IsNullOrEmpty(tableAttribute.TableName))
{
tableName = tableAttribute.TableName.ToLower();
}
}
table.TableName = tableName;
InitPropertieInfo(entityType, table);
return(table);
}
/// <summary>
/// 获取插入数据的sql语句.
/// </summary>
/// <param name="entity">实体.</param>
/// <param name="tableName">Name of the table.</param>
/// <returns></returns>
public string InsertCommandText(IEntity entity, string tableName)
{
try
{
Type type = GetCacheType(entity);
PropertyInfo[] pis = type.GetProperties();
// 获取此类中是否包含数据字段验证
bool isValidateDataField = EntityClassAttribute.IsValidateField(type);
string sql = "INSERT INTO " + tableName + " (";
string val = " VALUES(";
foreach (PropertyInfo pi in pis)
{
if (pi.GetValue(entity, null) != null && pi.CanWrite && pi.Name.ToLower() != "entityfullname")
{
// 此类中包含有非数据库字段，需要进行排除
if (isValidateDataField)
{
if (EntityPropertyAttribute.IsNoDataField(pi))
{
continue;
}
}
sql += pi.Name + ",";
val += SqlString(pi, pi.GetValue(entity, null)) + ",";
}
}
sql = sql.Remove(sql.Length - 1) + ") ";
val = val.Remove(val.Length - 1) + ")";
return(sql + val + ";");
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取删除数据的Sql语句.
/// </summary>
/// <param name="entity">已附值的实体.</param>
/// <param name="tableName">需要删除数据的表.</param>
/// <returns></returns>
public string DeleteCommandText(IEntity entity, string tableName)
{
try
{
Type type = entity.GetType();
PropertyInfo[] pis = type.GetProperties();
bool isValidateDataField = EntityClassAttribute.IsValidateField(type);
string sql = "DELETE FROM " + tableName + " WHERE ";
using (MySqlCommand cmd = new MySqlCommand())
{
string where = "";
foreach (PropertyInfo pi in pis)
{
if (pi.GetValue(entity, null) != null && pi.Name.ToLower() != "entityfullname")
{
// 此类中包含有非数据库字段，需要进行排除
if (isValidateDataField)
{
if (EntityPropertyAttribute.IsNoDataField(pi))
{
continue;
}
}
where += (Utils.IsNullOrEmpty(where) ? "" : " AND ") + pi.Name + "=" + SqlString(pi, pi.GetValue(entity, null));
}
}
return(sql + where + ";");
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取待更新和插入的数据库查询语句.
/// </summary>
/// <param name="entity">实体.</param>
/// <param name="tableName">数据库表名.</param>
/// <param name="fields">条件字段.</param>
/// <param name="commandParameters">输出参数.</param>
/// <param name="prefixParameter">参数前缀.</param>
/// <returns></returns>
public string UpsertCommandText(IEntity entity, string tableName, string[] fields, out DbParameter[] commandParameters, string prefixParameter = "")
{
try
{
Type type = GetCacheType(entity);
PropertyInfo[] pis = type.GetProperties();
string updateSql = string.Empty, setSql = string.Empty;
string insertSql = "INSERT INTO " + tableName + "(";
List <string> vals = new List <string>();
// (name)
//select 'test1'
//FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM test1 WHERE name = 'test1'); "
bool isValidateDataField = EntityClassAttribute.IsValidateField(type);
using (MySqlCommand cmd = new MySqlCommand())
{
string condition = "";
foreach (PropertyInfo pi in pis)
{
if (pi.GetValue(entity, null) != null && pi.CanWrite && pi.Name.ToLower() != "entityfullname")
{
// 此类中包含有非数据库字段，需要进行排除
if (isValidateDataField)
{
if (EntityPropertyAttribute.IsNoDataField(pi))
{
continue;
}
}
string paraName = "@" + prefixParameter + entity.EntityFullName.Replace(",", "_").Replace(".", "_") + "_" + pi.Name;
// 插入数据
insertSql += pi.Name + ",";
vals.Add(paraName);
if (fields != null && fields.Contains <string>(pi.Name, StringComparer.InvariantCultureIgnoreCase))
{
condition += (String.IsNullOrEmpty(condition) ? "" : " and ") + pi.Name + "=" + paraName;
cmd.Parameters.AddWithValue(paraName, pi.GetValue(entity, null));
}
else
{
setSql += pi.Name + "=" + paraName + ",";
cmd.Parameters.AddWithValue(paraName, pi.GetValue(entity, null));
}
}
}
// 组装插入语句
insertSql = insertSql.Remove(insertSql.Length - 1) + ") SELECT " + string.Join(",", vals)
+ " FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM " + tableName + " WHERE " + condition + ");";
// 组装更新语句
if (!string.IsNullOrEmpty(setSql))
{
updateSql = "UPDATE " + tableName + " SET " + setSql.Remove(setSql.Length - 1);
if (!Utils.IsNullOrEmpty(condition))
{
updateSql = updateSql + " WHERE " + condition;
}
}
commandParameters = new MySqlParameter[cmd.Parameters.Count];
// 将cmd的Parameters参数集复制到discoveredParameters数组.
cmd.Parameters.CopyTo(commandParameters, 0);
return(insertSql + updateSql + ";");
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取插入数据的sql语句.
/// </summary>
/// <param name="entity">The entity.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="commandParameters">The command parameters.</param>
/// <param name="prefixParameter">The prefix paramter.</param>
/// <returns></returns>
/// <exception cref="System.Data.Common.DbException"></exception>
public string InsertCommandText(IEntity entity, string tableName, out DbParameter[] commandParameters, string prefixParameter = "")
{
try
{
Type type = GetCacheType(entity);
PropertyInfo[] pis = type.GetProperties();
// 获取此类中是否包含数据字段验证
bool isValidateDataField = EntityClassAttribute.IsValidateField(type);
string sql = "INSERT INTO " + tableName + " (";
string val = " VALUES(";
using (MySqlCommand cmd = new MySqlCommand())
{
foreach (PropertyInfo pi in pis)
{
if (pi.GetValue(entity, null) != null && pi.CanWrite && pi.Name.ToLower() != "entityfullname")
{
// 此类中包含有非数据库字段，需要进行排除
if (isValidateDataField)
{
if (EntityPropertyAttribute.IsNoDataField(pi))
{
continue;
}
}
string paraName = "@" + prefixParameter + entity.EntityFullName.Replace(",", "_").Replace(".", "_") + "_" + pi.Name;
sql += pi.Name + ",";
val += paraName + ",";
cmd.Parameters.AddWithValue(paraName, pi.GetValue(entity, null));
}
}
sql = sql.Remove(sql.Length - 1) + ") ";
val = val.Remove(val.Length - 1) + ")";
//foreach (PropertyInfo pi in pis)
//{
// if (pi.GetValue(entity, null) != null && pi.CanWrite && pi.Name.ToLower() != "entityfullname")
// {
// sql += pi.Name + ",";
// val += "@" + pi.Name + ",";
// cmd.Parameters.AddWithValue("@" + pi.Name, pi.GetValue(entity, null));
// }
//}
//sql = sql.Remove(sql.Length - 1) + ") ";
//val = val.Remove(val.Length - 1) + ")";
commandParameters = new MySqlParameter[cmd.Parameters.Count];
// 将cmd的Parameters参数集复制到discoveredParameters数组.
cmd.Parameters.CopyTo(commandParameters, 0);
return(sql + val + ";");
}
}
catch (Exception ex)
{
throw ex;
}
}
