/// <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)); }