/// <summary>
/// 按主键删除
/// </summary>
/// <param name="keyValues">主键值</param>
/// <returns>删除是否成功</returns>
public bool Delete(string[] keyValues)
{
if (primaryKeys.Length != keyValues.Length)
{
return(false);
}
string condition = "";
List <DbParameter> pars = new List <DbParameter>();
for (int i = 0; i < keyValues.Length; i++)
{
condition += primaryKeys[i] + "=@" + primaryKeys[i] + " AND ";
pars.Add(SQLFactory.CreateParameter(primaryKeys[i], keyValues[i]));
}
string sql = String.Format(deleteSQL, tableName, condition.Substring(0, condition.LastIndexOf("AND")));
try
{
if (DBTran == null)
{
return(DBHelper.ExecuteNonQuery(System.Data.CommandType.Text, sql, pars) > 0);
}
else
{
return(DBHelper.ExecuteNonQuery(DBTran.Transaction, System.Data.CommandType.Text, sql, pars) > 0);
}
}
catch
{}
return(false);
}
/// <summary>
/// 获取UpdateSQL语句
/// </summary>
/// <param name="entity">实体对象</param>
/// <param name="allowNull">是否允许值为null的属性(true:为null的属性赋空值；false:忽略为null的属性不做处理，保持数据库原样)</param>
private string getUpdateSQL(EntityBase entity, OptType allowNull)
{
condition = "";
foreach (DataField field in EntityUtil.GetDataFields(entity))//构造SQL参数集合
{
pars.Add(SQLFactory.CreateParameter(field.FieldName, field.FieldValue));
//pars.Add(new SqlParameter(field.FieldName, field.FieldValue));
if (EntityUtil.GetPrimaryKey(tableName).Contains(field.FieldName))
{
condition += field.FieldName + "=@" + field.FieldName + " AND ";
}
else
{
if (!field.IsIdentity)
{
if (field.FieldValue == null && allowNull != OptType.UpdateAllowedNull)
{
continue;
}
else
{
fs.Append(field.FieldName + "=@" + field.FieldName + ",");
}
}
}
}
return(String.Format(updateSQL, tableName, fs.ToString().Trim(','), condition.Substring(0, condition.LastIndexOf("AND"))));//生成UPDATE语句
}
/// <summary>
/// 根据主键查询(联合主键情况)
/// </summary>
/// <param name="keyValues">主键对应的值</param>
/// <returns>实体对象</returns>
public T FindByPK(string[] keyValues)
{
if (primaryKeys.Length != keyValues.Length)
{
return(null);
}
string condition = "";
List <DbParameter> pars = new List <DbParameter>();
for (int i = 0; i < primaryKeys.Length; i++)
{
condition += primaryKeys[i] + "=@" + primaryKeys[i] + " AND ";
pars.Add(SQLFactory.CreateParameter(primaryKeys[i], keyValues[i]));
}
condition = condition.Substring(0, condition.LastIndexOf("AND"));
string sql = String.Format(selectSQL, "*", tableName, condition);
T model = default(T);
try
{
model = DataConvert <T> .ToEntity(DBHelper.Query(sql, pars).Rows[0]);
//关联对象加载
EntityUtil.SetAssociation(model);
}
catch { }
return(model);
}
/// <summary>
/// 根据Dictionary获取Update的SQL语句赋值部分（取mode中不为null的属性）
/// </summary>
protected String getSetValueSql(Dictionary <String, Object> dic, List <DbParameter> pars)
{
StringBuilder fs = new StringBuilder(); //Update赋值相关部分
foreach (String key in dic.Keys) //构造SQL参数集合
{
if (dic[key] != null && dic[key].ToString() != "")
{
fs.Append(key + "=@" + key + ",");
pars.Add(SQLFactory.CreateParameter(key, dic[key].ToString()));
}
}
return(fs.ToString().TrimEnd(','));
}
private string getDeleteSQL(EntityBase entity)
{
string pk = EntityUtil.GetPrimaryKey(tableName);
string[] pks = pk.Split(',');
pars = new List <DbParameter>();
condition = "";
for (int i = 0; i < pks.Length; i++)
{
condition += pks[i] + "=@" + pks[i] + " AND ";
pars.Add(SQLFactory.CreateParameter(pks[i], EntityUtil.GetValueByField(entity, pks[i])));
//pars.Add(new SqlParameter(pks[i], EntityUtil.GetValueByField(entity, pks[i])));
}
return(String.Format(deleteSQL, tableName, condition.Substring(0, condition.LastIndexOf(','))));
}
private string getCreateSQL(EntityBase entity)
{
string identitySql = "";
foreach (DataField field in EntityUtil.GetDataFields(entity)) //构造SQL参数集合
{
if (field.FieldValue != null && !field.IsIdentity) //字段值不为空 且 不是标识
{
pars.Add(SQLFactory.CreateParameter(field.FieldName, field.FieldValue));
fs.Append(field.FieldName + ",");
vs.Append("@" + field.FieldName + ",");
}
else if (field.FieldValue != null && !field.IsIdentity)//如果有标识字段，返回最新标识值
{
identitySql = ";";
identitySql += SQL.ResourceManager.GetString(DataConfig.DatabaseType + "Identity").Replace("{0}", EntityUtil.GetTableName(entity.GetType()));
}
}
return(String.Format(insertSQL, tableName, fs.ToString().TrimEnd(','), vs.ToString().TrimEnd(',')) + identitySql);
}
/// <summary>
/// 根据Model获取Update的SQL语句赋值部分（取mode中不为null的属性）
/// </summary>
protected String getSetValueSql(T model, List <DbParameter> pars)
{
StringBuilder fs = new StringBuilder(); //Update赋值相关部分
foreach (DataField field in EntityUtil.GetDataFields(model)) //构造SQL参数集合
{
if (!field.IsIdentity)
{
if (field.FieldValue == null || (field.FieldType == "datetime" && field.FieldValue.ToString().Contains("0001")))
{
continue;
}
else
{
fs.Append(field.FieldName + "=@" + field.FieldName + ",");
pars.Add(SQLFactory.CreateParameter(field.FieldName, field.FieldValue));
}
}
}
return(fs.ToString().TrimEnd(','));
}
/// <summary>
/// 按主键删除
/// </summary>
/// <param name="keyValue">主键值</param>
/// <returns>删除是否成功</returns>
public bool Delete(string keyValue)
{
string sql = String.Format(deleteSQL, tableName, primaryKey + "=@" + primaryKey);
List <DbParameter> pars = new List <DbParameter>();
pars.Add(SQLFactory.CreateParameter(primaryKey, keyValue));
try
{
if (DBTran == null)
{
return(DBHelper.ExecuteNonQuery(System.Data.CommandType.Text, sql, pars) > 0);
}
else
{
return(DBHelper.ExecuteNonQuery(DBTran.Transaction, System.Data.CommandType.Text, sql, pars) > 0);
}
}
catch
{}
return(false);
}
/// <summary>
/// 根据对象主键查询是否存在该对象
/// </summary>
/// <param name="keyValue">主键对应的值</param>
/// <returns>是否存在</returns>
public bool Exist(T model)
{
String[] pks = primaryKey.Split(',');
String conditions = "";
List <DbParameter> pars = new List <DbParameter>();
foreach (String pk in pks)
{
if (conditions != "")
{
conditions += (" AND " + pk + "=@" + pk);
}
else
{
conditions += (pk + "=@" + pk);
}
pars.Add(SQLFactory.CreateParameter(pk, EntityUtil.GetValueByField(model, pk)));
}
string sql = String.Format(selectSQL, "COUNT(*)", tableName, conditions);
return("0" != DBHelper.ExecuteScalar(System.Data.CommandType.Text, sql, pars).ToString());
}
/// <summary>
/// 根据主键查询
/// </summary>
/// <param name="keyValue">主键对应的值</param>
/// <returns>实体对象</returns>
public T FindByPK(string keyValue)
{
string sql = String.Format(selectSQL, "*", tableName, primaryKey + "=@" + primaryKey);
List <DbParameter> pars = new List <DbParameter>();
pars.Add(SQLFactory.CreateParameter(primaryKey, keyValue));
T model = default(T);
try
{
model = DataConvert <T> .ToEntity(DBHelper.Query(sql, pars).Rows[0]);
#region 级联对象加载
//关联对象加载
EntityUtil.SetAssociation(model);
#endregion
}
catch { }
return(model);
}