/// <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>
/// 用户自定义sql语句查询
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="parms">查询参数列表</param>
/// <returns>查询对象集合</returns>
public static List <T> QueryEx(String sql, List <DbParameter> parms = null)
{
DataTable dt = SQLFactory.CreateDBHelper().Query(sql, parms);
List <T> entities = NDolls.Data.Util.DataConvert <T> .ToEntities(dt);
return(entities);
}
/// <summary>
/// 用户自定义sql语句批量执行（非查询语句）
/// </summary>
/// <param name="sql">非查询sql语句集合</param>
/// <returns>是否成功</returns>
public static bool Excute(List <String> sqls)
{
DbConnection conn = SQLFactory.CreateDBConnection();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
DbTransaction tran = conn.BeginTransaction();
try
{
foreach (String sql in sqls)
{
SQLFactory.CreateDBHelper().ExecuteNonQuery(tran, System.Data.CommandType.Text, sql, null);
}
tran.Commit();
return(true);
}
catch
{
return(false);
}
}
/// <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(','))));
}
/// <summary>
/// 开启事务处理
/// </summary>
public void TransactionOpen()
{
Guid sid = Guid.NewGuid();
ts.SID = sid;
if (ts.SConnection == null)
{
ts.SConnection = SQLFactory.CreateDBConnection();
}
if (ts.SConnection.State != System.Data.ConnectionState.Open)
{
ts.SConnection.Open();
}
ts.STransaction = ts.SConnection.BeginTransaction();
tranConnectionDic.Add(sid, ts);//开启批量事务操作，将TranSession存入字典
}
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>
/// 按主键删除
/// </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>
/// 根据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 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);
}
/// <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>
/// 用户自定义sql语句查询（返回一行一列）
/// </summary>
/// <param name="sql">查询sql语句</param>
/// <returns>查询结果</returns>
public static object Single(String sql, List <DbParameter> parms = null)
{
return(SQLFactory.CreateDBHelper().ExecuteScalar(System.Data.CommandType.Text, sql, parms));
}
/// <summary>
/// 构造函数
/// </summary>
public DBTransaction()
{
this.dbHelper = SQLFactory.CreateDBHelper();
}
/// <summary>
/// 用户自定义sql语句查询
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="parms">查询参数列表</param>
/// <returns>查询结果集</returns>
public static DataTable Query(String sql, List <DbParameter> parms = null)
{
return(SQLFactory.CreateDBHelper().Query(sql, parms));
}
/// <summary>
/// 事务执行
/// </summary>
/// <returns>事务执行结果</returns>
public bool Excute()
{
DbConnection conn = null;
DbTransaction tran = null;
if (!tranConnectionDic.ContainsKey(ts.SID))//若未开启全局事务处理
{
conn = SQLFactory.CreateDBConnection();
conn.Open();
tran = conn.BeginTransaction();
}
else//若开启全局事务处理
{
conn = ts.SConnection;
tran = ts.STransaction;
}
//数据处理
try
{
foreach (OptEntity item in entities)
{
tableName = EntityUtil.GetTableName(item.Entity.GetType());
resetVars();//重置参数(新对象重新赋值)
switch (item.Type)
{
case OptType.Create: //新增
sql = getCreateSQL(item.Entity);
break;
case OptType.UpdateAllowedNull: //修改
sql = getUpdateSQL(item.Entity, OptType.UpdateAllowedNull);
break;
case OptType.UpdateIgnoreNull: //修改
sql = getUpdateSQL(item.Entity, OptType.UpdateIgnoreNull);
break;
case OptType.Save:
break;
case OptType.Delete: //删除
sql = getDeleteSQL(item.Entity);
break;
}
if (!String.IsNullOrEmpty(sql))
{
dbHelper.ExecuteNonQuery(tran, System.Data.CommandType.Text, sql, pars);
}
}
//字典中存在SID：事务操作（多个对象批量操作）；字典中不存在SID：非事务操作（单个对象操作）
if (!tranConnectionDic.ContainsKey(ts.SID))
{
tran.Commit();
conn.Close();
}
return(true);
}
catch (Exception ex)
{
if (!tranConnectionDic.ContainsKey(ts.SID))//若未开启全局事务处理
{
tran.Rollback();
conn.Close();
}
throw (ex);
}
}
/// <summary>
/// 用户自定义sql语句执行（非查询语句）
/// </summary>
/// <param name="sql">非查询sql语句</param>
/// <returns>执行成功的数据行数</returns>
public static int Excute(String sql)
{
return(SQLFactory.CreateDBHelper().ExecuteNonQuery(System.Data.CommandType.Text, sql, null));
}